Широкие или длинные данные

Опубликовано Опубликовано в рубрике Рецепты

Один из страшных снов аналитика это система отчетов на Excel, укоренившаяся в компании, которую на самом деле надо перевести на современные рельсы.
Пример – много отчетов, которые рассылаются по е-мейлу, представляющие из себя сводные таблицы Excel, причем исходные данные для построения стерты.
Отчеты эти иной раз бывают ну очень широкие, очень много показателей, отделов отражено в столбцах. А по строкам отложены некие аналитические разрезы – клиенты, товары, филиалы, менеджеры…
Цель – перевести эти отчеты на хранение в реляционную БД.
Проблема/задача – необходимо нормировать эти отчеты.
Для примера рассмотрим такую таблицу (цифры в ячейках, ну допустим измерения каких-нибудь показателей):

Менеджер Цех 1 Цех 2 Цех 3
1 Даша 6 5 4
2 Маша 2 3 7
3 Саша 8 1 12
4 Таша 3 5 6

Правильно будет ее укладывать в базу данных на самом деле в другом формате:

Менеджер Цех Показатель
Даша Цех 1 6
Даша Цех 2 5
Даша Цех 3 4
Маша Цех 1 2
Маша Цех 2 3
Маша Цех 3 7
Саша Цех 1 8
Саша Цех 2 1
Саша Цех 3 12
Таша Цех 1 3
Таша Цех 2 5
Таша Цех 3 6

Если данные будут лежать в таком, нормированном виде — в будущем нам это даст огромную гибкость и удобство. Хотя бы можно будет внедрить даже справочники по разрезам и показателям.

Так вот — в Экселе такое сделать — это просто огромная боль. Руками — пару раз, конечно можно. Можно написать скрипт на VBA. Но тут писанины много, а если поменяется схема данных — уже переписывать. Да и скорость работы на по настоящему немаленьких отчетах будет ужасающей.

Но нам на помощь снова приходит волшебный язык R, и снова решение «одной строкой».

Создадим DataFrame как в нашем примере:

df <- data.frame(
name=c('Маша','Даша','Саша','Таша'),
ceh1=c(2,6,8,3),
ceh2=c(3,5,1, 5),
ceh3=c(7,4,12, 6)
)

Посмотрим что получилось:

name ceh1 ceh2 ceh3
1 Маша 2 3 7
2 Даша 6 5 4
3 Саша 8 1 12
4 Таша 3 5 6

Подключим библиотеку:

library(reshape2)

А теперь, та самая «одна строка»:

df1 <- melt(df, id='name')

Можно обратное преобразование "длинный-широкий" тоже сделать.

df2 <- dcast(df1, name~variable)

Уж не буду здесь выкладывать содержимое новых DataFrame-ов - проверьте или поверьте - они то что надо!

Вот так - всего пара строк - а сколько головной боли ушло! Да если бы мне от языка надо было лишь только обрабатывать данные в Excel файлах - да это все что мне от него надо было бы! А как много еще он умеет !

Добавить комментарий