Работа с таблицами в R: Data.table и OLAP

Таблицы это один из основных способов работы со структурированными данными. В языке R для работы с таблицами существует множество библиотек. Data.table одна из самых быстрых R библиотек для работы с большими массивами табличных данных, работает на основе цепочек. Результат из одного звена передается в следующее звено в виде готового результата. По факту, это улучшенная наследуемая версия data.frames, который является стандартной структурой данных для хранения в базе R.

userb2b = data.table(
    ID = c("max","dim","max","sveta","dim","sveta"),
    a = 1:11,
    b = 7:5,
    c = 11:23
)

В результате мы получим самую простую таблицу со столбцами ID, a, b, c и случайными диапазонами значений из кода выше для каждого столбца. Я понимаю, что для названий столбцов выбирать цифры или одну букву вместо нормальных имен это плохая практика, но благодаря этому примеру я могу упомянуть эту плохую практику, и вы не будете так делать в будущем. Также, если для работы вы решите импортировать данные из CSV, то используйте для импорта данных библиотеку readr или функции fwrite и fread из data.table. Базовые функции работают медленно.

По структуре полученной таблицы видно, что символ : отделяет номер строки от значения первого столбца. Существование данных можно проверить командой tables(), и полезно запомнить команду head(userb2b) для демонстрации первых 6 элементов одной таблицы. Если этого недостаточно, то команда print(userb2b, nrows = Inf) покажет куда больше данных. Посмотреть структуру таблицы можно командой str(userb2b).

Сортировка: Для сортировки таблицы используйте команду userb2b[, sort(unique(c))]. Можно сортировать таблицу сначала по возрастанию для определенного столбца, а затем по убыванию для другого: с помощью хорошо оптимизированной функции order(), например, newtav <- userb2b[order(c, -b)]. Символ - означает убывание. Я не зря сделал акцент на оптимизированности этой функции, так как order использует внутренний метод fastx order forder (), который настолько лучше чем стандартный base :: order R, что алгоритм из data.table для сортировки был принят по умолчанию в 2016 году для R 3.3.0.

Выделить данные в виде вектора можно командой userb2b[["ID"]], а показать только определенные столбцы поможет userb2b[ , c("a","b","c")].

Все это имеет практическое применение. Допустим, в нашей таблице столбец «ID» отвечает за имя пользователя, а столбец «с» за ARPU diary. Командой userb2b[,.(ID), by="c"] можно легко отсортировать имена пользователей по метрике ARPU diary.

userb2b[, c := NULL]
userb2b[, 2 := NULL]

userb2b[, num_night := match(c, sort(unique(c))), by = "ID"]
userb2b[, num:= (speed = c / (c - b))]

Объединение таблиц: работа с одной таблицей не является чем-то сложным и наверняка первая мысль читателя: "я лучше буду это делать в Excel". Так как в первую очередь мы будем работать с несколькими таблицами, то давайте создадим две таблицы:

ID <- c(54,45," ",46,78,53,32,51,95)
Formidable <- c(0.44,0.34,"",0.31,0.86,0.87,0.27,0.412,0.5)
Sober <- c("True","True","True","","True","False","True","False","False")
afterCorporateParty <- data.frame(Subject=ID, pt=Formidable, Event=Sober)
DT_data <- as.data.table(afterCorporateParty)
 
Errands <- c("Can","Can't","","","Can","Can","Can","Can","Can")
ID <- c(34,68," ",46,"",53,"",51,55)
resultResearch <- data.frame(Subject=ID, ready=Errands)
DT_data_2 <- as.data.table(resultResearch)

У нас есть две таблицы с общим столбцом "Subject", его и будем использовать для объединения двух таблиц. Принципы объединения данных одинаковы и для Excel, и для SQL, R не исключение. В data.table для этого существует функция Merge, по аналогии с SQL: merge(behavb2b, userb2b, by = "ID"). На этом примере прослеживается основа синтаксиса: DT[i, j, by], но в отличии от SQL, в R можно использовать для работы с таблицами любые функции из любого пакета, не ограничиваясь возможностями SQL. Таблица data.table может быть передана любому пакету, который умеет работать с data.frame. Существует 4 классических видов JOIN:

INNER JOIN получает все записи, которые являются общими для обеих таблиц на основе внешнего ключа. В результате получаем таблицу с записями, общими для левой и правой таблиц. Основное применение это получение среза данных по двум колонкам. Например, выцепить данные из первой таблицы обо всех клиентах, которые заплатили и посмотреть во второй таблице, сколько заплатили. Inner Join создан как раз для этого, возвращает строки из обеих таблиц, которые удовлетворяют заданным условиям.

newtav <- data.table(merge(x = DT_data, y = DT_data_2, by.x = 'Subject', by.y = 'Subject'))
newtav <- data.table(merge(x = DT_data, y = DT_data_2, by = 'Subject'))

LEFT JOIN получает все записи из таблицы, указанной на стороне LEFT. Не берутся записи из правой таблицы. Основное применение: посмотреть все записи по определенному столбцу. Если для строки из левой таблицы не будет найдена соответствующая запись в правой, то значение будет N/A.

newtav <- merge(x = DT_data, y = DT_data_2, by = "Subject", all.x = TRUE)

RIGHT JOIN работает аналогично примеру выше, но получает все записи в таблице, указанной в RIGHT. Не трудно догадаться, что Left join легко превращается в Right join при изменении порядка таблиц для объединения.

newtav <- merge(x = DT_data, y = DT_data_2, by = "Subject", all.y = TRUE)

FULL JOIN получает все записи из обеих таблиц и помещает NULL в столбцы, где соответствующие записи не существуют в противоположной таблице. Многие могут испытать соблазн сразу объединить все имеющиеся таблицы и в дальнейшем работать с одной таблицей. Это не всегда удачная идея, рассмотрим на примере: у клиента бизнес по дизайну сайтов, в штате есть 10 дизайнеров, каждый закреплен за определенным набором проектов. За 10 лет работы всей командой было сделано 10 000 сайтов, и все сайты переделываются раз в 2 года. Получаем 10 лет * 10 дизайнеров * 10 000 сайтов * 2 = минимум 2 000 000 записей в одной таблице, а если разбить 10 лет на месяцы/дни/итерации, то количество записей будет стремиться к сотням миллионов. А теперь попробуйте по аналогии подсчитать количество данных среднего e-commerce, с детализацией записей по каждому заказу до уровня размера упаковки. С такой таблицей будет неудобно работать, поэтому принято хранить записи в разных таблицах.

newtav <- merge(DT_data, DT_data_2, by = "Subject", all = TRUE)

Полученную таблицу можно красиво оформить, экспортировать и формировать гипотезы на основе полученных данных:

library(formattable)
formattable(userb2b, align =c("l","r","r","r"), preproc = NULL, postproc = NULL)

coolColor_one = "#27CF9F"
coolCOlor_weak = "#A5E2E3"
levelColor = "#E1BC50"
 
formattable(newtav, align =c("l","r","r","r"), list(
    "Subject" = formatter("span", style = ~ style(color = ifelse(Subject == "53", "red", "gray"), font.weight = "bold")), 
    "pt" = color_bar(levelColor),
    "Event" = color_tile(coolColor_one, coolCOlor_weak),
    "ready" = color_tile(coolColor_one, coolCOlor_weak)
))

Но что делать, когда данных слишком много, количество таблиц стремится к десяткам, и объединять их поштучно становится слишком долго, дорого и сложно? Когда количество собранных пользовательских данных переваливает за терабайт, одно из решений это многомерные базы данных. Очень удобны для работы, у таких баз данных есть несколько измерений, больше чем 2 (классическая таблица). В классических базах данных есть колонки и ряды, как в Excel или в таблице, что мы создали выше. Многомерные базы данных состоят из минимум трех измерений, формируя куб.

Посмотрим на примере: есть три измерения: продажи, года и каналы. Из такого куба мы можем узнать, например, что было сделано 566 продажи через канал прямых продаж в 2011 году. Эти данные нам дает пересечении трех измерений в одной ячейке.

Но одна ячейка это слишком просто и бесполезно, OLAP же позволяет получить весь плоский квадрат со всеми данными по одному каналу. Если вы работаете на аутсорсе, то в современных CRM без OLAP никуда.

OLAP позволяет избежать помощи программиста, скорость доступа к данным очень высокая. И непрогнозируемая нагрузка на сервера, когда цепочка дата — дата — дата — факт — факт — факт становится слишком длинной. Но главное, можно анализировать бесконечное количество вариантов работы пользователей, крутя куб и линкуя его к MS Office. Отдаленно эта работа похожа на сводные таблицы в Excel. Давайте создадим новые данные и построим на их основе OLAP-куб, в котором будет информация месячном APPRU в диапазоне 24-х месяцев по трем маркам телефонов.

V <- 4000
models <- c("iPhone 8 Plus", "iPhone XS Max", "Galaxy Note 9")
ARPPU_month <- c("47$",  "24$", "72$", "35$", "85$", "70$", "26$", "82$", "123$", "100$", "13$", "42$")
timeframes <- 12:36
 
dataFrameBig <- data.frame(
    models = sample(models, V, TRUE),
    ARPPU_month = sample(ARPPU_month, V, TRUE),
    timeframes = sample(timeframes, V, TRUE),
    values = rnorm(V)^2 / rnorm(V))
 
myResult <- with(dataFrameBig, tapply(values, list(ARPPU_month, models, timeframes), mean))

Полученный результат существует в трех измерениях, теперь простой командой myResult[, , ] можно получить все данные в виде множества таблиц (так как таблица многомерная). Или командой myResult[5,-456,] сделать срез по двум измерениям и получить простую двумерную таблицу. Если посмотреть на структуру данных str(myResult), то увидим, что dimnames это список из трех списков. При этом списки весьма разношерстные, хоть они и хранятся в одном месте:

ggplot(dataFrameBig, aes(ARPPU_month)) + geom_bar()
 
ggplot(dataFrameBig, aes(x = values)) + geom_density(alpha = .15) + scale_x_log10(breaks = c(50, 150, 400, 750) * 1000, labels = scales::dollar)
 
ggplot(dataFrameBig, aes(x = timeframes, y = values)) + geom_point(alpha = .3) + scale_x_log10() + theme_bw()