Пилим данные с комфортом +7


image

Доброго времени дня.

В реальной практике довольно часто сталкиваешься с задачами, далекими от сложных ML алгоритмов, но при этом являющихся не менее важными и насущными для бизнеса.
Поговорим об одной из них.

Задача сводится к тому что бы распределить (распилить, рассплитовать — жаргон бизнеса неиссякаем) данные какой-нибудь целевой таблицы с агрегатами (совокупные значения) на таблицу более детальной гранулярности.

Например коммерческому департаменту надо разбить годовой план, согласованный на уровне брендов — детально до продукции, маркетологам разбить годовой бюджет маркетинга по территориям страны, планово-экономическому департаменту разбить общехозяйственные издержки по центрам финансовой ответственности, и т.д. и т.п.

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

Рассмотрим реальный пример:

Спускают в качестве задачи план продаж как на картинке ниже (я намеренно сделал пример упрощенным, в реальности — полотнище в экселе на 100-200 мгб).

Пояснение по заголовкам:

  • pos_terr-территория(область) торговой точки
  • pos_fo — федеральный округ торговой точки (например ЦФО-Центральный федеральный округ)
  • product_brend — бренд продукции
  • product_class- класс продукции
  • plan.sales- план продаж в чем-либо.

image

И просят к примеру разбить свою мега-таблицу (в рамках нашего детского примера она конечно поскромнее) — до канала продаж. На вопрос — по какой логике разбивать получаю ответ: «а возьмите статистику фактических продаж за 4-й квартал такого-то года, получите фактические доли каналов в % для каждой строки плана и по этим долям строки плана и разбивайте».
На самом деле это самый частый ответ в таких задачах…

Пока все кажется достаточно простым.

Получаю этот факт (см.картинку ниже):

  • pos_channell — канал продаж (целевой атрибут для плана)
  • fact.sales — фактические продажи чего-то.

image

Исходя из полученного подхода к «распиливанию» на примере первой строчки плана мы ее разобьем на основе факта как-то так:

image

Однако если сопоставить факт с планом для всей таблички для того чтобы понять — все ли строки плана получится адекватно «распилить» по долям то получим следующую картину: (зеленый цвет — все атрибуты строки плана совпали с фактом, желтые ячейки — не совпали).

image

  • В 1й строке плана все поля полностью найдены в факте.
  • Во 2й строке плана не найдена в факте соответствующая территория
  • В 3й строке плана не хватает в факте бренда
  • В 4й строке плана не хватает в факте территории и федерального округа
  • В 5й строке плана не хватает в факте бренда и класса.

Как говорил Паниковский: «Пилите Шура, пилите--они золотые...»

image

Иду к бизнес-заказчику и уточняюсь на примере 2й строки, какой он видит подход для таких ситуаций?

Получаю ответ: «для случаев когда не получается посчитать долю каналов для бренда № 2 в Смоленской области (с учетом того что Смоленская область у нас в Центральном федеральном округе-ЦФО) — разбейте тогда эту строку по структуре каналов внутри всего ЦФО!»

То есть для {Смоленской области+бренд_2} агрегируем факт на уровне ЦФО и разбиваем Смоленскую область как-то так:

image

Возвращаюсь и переваривая услышанное пытаюсь обобщить в более универсальную эвристику:
Если данных нет на текущем уровне детализации таблицы фактов то перед расчетом долей для целевого поля (канал продаж) агрегируем таблицу фактов до атрибута иерархии выше.

То есть если нет для территории то агрегируем факт на уровень иерархии выше — доли для того же ЦФО что и в плане. Если нет для бренда то по иерархии выше есть класс продукта — соответственно пересчитываем доли на тот же класс и так далее.

Т.е. мы объединяем план и факт по полям сцепки для которых считаем доли в факте и на каждой итерации по оставшемуся нераспрделенному плану последовательно уменьшаем состав полей сцепки.

Тут уже вырисовывается определенный шаблон распределения данных:

  1. Распределяем план по факту на основе полного совпадения соответствующих полей
  2. Получаем разбитый план (его накапливаем в промежуточный результат) и неразбитый план (не все строки совпали)
  3. Берем неразбитый план и разбиваем его по факту на уровень иерархии выше (т.е. отказываемся от определенного поля сцепки этих 2-х таблиц и агрегируем факт без этого поля для расчета долей)
  4. Получаем разбитый план (его добавляем в промежуточный результат) и неразбитый план (не все строки совпали)
  5. И те же шаги повторяем пока не останется «нераспиленного» плана.

Вообще нас никто не обязывает последовательно удалять поля сцепки только в рамках иерархии. Например мы уже удалили из полей сцепки бренд и территорию и оставшийся план распределили по: класс_продукции (иерархия выше бренда)+Фед.округ (иерархия выше территории). И все равно получили какой-то не распределенный остаток плана.

Далее мы можем удалить из полей сцепки либо класс продукции либо федеральный округ т.к. они уже не вкладываются в иерархию друг-друга.

Учитывая что полей в таких таблицах десятки и строк — до миллиона делать руками такие манипуляции — задача не самая приятная.

А если учесть что задачи такого рода прилетают ко мне регулярно в конце каждого года (утверждение бюджетов след.года на совете директоров) — то пришлось переводить этот процесс на какой-то гибкий универсальный шаблон.

А так как большую часть времени я работаю с данными через R — реализация соответственно то же на нем.

Для начала нам понадобится написать универсальную магическую функцию, которая будет принимать базовую таблицу(basetab) с данными для разбивки (в нашем примере-план) и таблицу для расчета долей (sharetab) на основе которых и будем «пилить» данные (в нашем примере-факт). Но функция должна еще понимать что с этими объектами надо делать, поэтому функция будет еще принимать вектор имен полей сцепки (merge.vrs) — т.е. те поля которые одинаково называются в обеих таблицах и позволят нам по этим полям соединить одну таблицу с другой там где это получится (т.е. right join). Так же функция должна понимать-какой столбец базовой таблицы надо брать в распределение (basetab.value) и на основе какого поля считать доли (sharetab.value). Ну и самая главное — что брать за результирующее поле (sharetab.targetvars), в нашем случае мы хотим план детализировать по каналу продаж из факта.

Кстати эта переменная sharetab.targetvars не случайно у меня во множественном числе — это может быть не одно поле а векктор имен полей, для случаев когда надо в базовую таблицу добавить из таблицы долей не одно поле а сразу несколько (например на основе факта разбить план не только по каналу продаж но и по наименованию продукции, входящей в бренд).

Да, и еще одно условие:) моя функция должна быть максимально локоничной и удобочитаемой, без всякой многоэтажности на 2 экрана (очень не люблю большие функции).

В последнее условие максимально комфортно вписался популярный пакет dplyr а учитывая что его pipeline операторы должны понимать спущенные в функцию текстовые названия полей — не обошлось без Standart evaluation.

Вот эта крошка (не считая внутренних комментов):

fn_distr <- function(sharetab,  sharetab.value, sharetab.targetvars,  basetab, basetab.value, merge.vrs,level.txt=NA) {
  # sharetab - объект=таблица драйвер распределения 
  # sharetab.value -  название поля с числами по которому будет пересчет в доли из таблицы-драйвер
  # sharetab.targetvars -  название целевого текстового поля из таблицы-драйвер по которому будет дробится базовая таблица на основе долей 
  # basetab - объект=таблица с базовыми показателями к распределению
  # basetab.value - название поля с числами которые должны быть распределены
  # merge.vrs - название полей объединения 2-х таблиц
  # level.txt - примечание пользователя для тек.итерации чтобы можно было обосновать строку результата (если пользователь не указал то merge.vrs)
  require(dplyr)
  sharetab.value <- as.name(sharetab.value)
  basetab.value <- as.name(basetab.value)
  if(is.na(level.txt )){level.txt <- paste0(merge.vrs,collapse = ",")}
  result <- sharetab %>% group_by(.dots = c(merge.vrs, sharetab.targetvars)) %>% summarise(sharetab.sum = sum(!!sharetab.value)) %>% ungroup %>%
    group_by(.dots = merge.vrs) %>% mutate(sharetab.share = sharetab.sum / sum(sharetab.sum)) %>% ungroup %>%
    right_join(y = basetab, by = merge.vrs) %>% mutate(distributed.result = !!basetab.value * sharetab.share, level = level.txt) %>% 
    select(-sharetab.sum,-sharetab.share)
  return(result)
}

На выходе функция должна возвращать data.frame объединения двух таблиц с теми строками плана+факта где удалось разбить на текущей версии полей сцепки, и с оригинальными строками плана (и пустым фактом) в строках, где на текущей итерации разбить план не удалось.

Т.е результат возвращаемый функцией после первой итерации (разбивка первой строки плана для ярославской области) будет выглядеть так:

image

Далее этот результат можно забрать по непустым distributed.result в накопительный результат а по пустым (NA) distributed.result — отправить на следующую такую же типовую итерацию но с разбивкой по долям на более высоком уровне иерархии.

Вся прелесть и все удобство в том что работа идет однотипными блоками и одной универсальной функцией, все что надо на каждом шаге (итерации) — это подкорректировать вектор merge.vrs и наблюдать как магия делает всю эту нудную работу за тебя:

image

Да, чуть не забыл маленький нюанс: если что-то пойдет не так и в самом конце мы получим разбитый план который суммарно не будет равен плану до разбивки — будет сложно отследить на какой итерации все пошло не так.

Поэтому каждую итерацию снабжаем контрольной суммой:

Сумма(План_изначальный)-Сумма(План_распределенный_в_накопленном результате)-Сумма(План_нераспределенный_после_тек.итерации)=0

Теперь попробуем прогнать наш пример через шаблон распрделения и посмотреть что получим на выходе.

Для начала получим исходные данные:

library(dplyr)
plan <- data_frame(pos_terr = c("ярославская область", "смоленская область", 
                                "краснодарский край", "свердловская область", "томская область"), 
                   pos_fo = c("ЦФО", "ЦФО", "ЮФО", "УФО", "СФО"),
                   product_brend = c("brend_1", "brend_2", "brend_3", "brend_4", "brend_5"), 
                   product_class = c("class_1",  "class_1", "class_2", "class_2", "class_3"), 
                   plan.sales = c(100, 200, 300, 400, 500))

fact <- data_frame(pos_terr = c("ярославская область", "ярославская область", 
                                "калужская область", "тверская область", "тверская область","краснодарский край", "краснодарский край", 
                                "томская область", "томская область", "томская область"), 
                   pos_fo = c("ЦФО", "ЦФО","ЦФО","ЦФО", "ЦФО", "ЮФО", "ЮФО", "СФО", "СФО", "СФО"),
                   product_brend = c("brend_1",  "brend_1", "brend_2", "brend_2","brend_2", "brend_4", "brend_4", "brend_1", "brend_2",  "brend_4"), 
                   product_class = c("class_1", "class_1", "class_1","class_1","class_1",  "class_2", "class_2", "class_1", "class_1", "class_2"), 
                   pos_channell = c("розница",  "опт", "хорека","опт", "хорека", "розница", "опт", "хорека", "розница", "хорека"), 
                   fact.sales = c(16.38, 11.64, 30.73,60, 20, 6.40, 26.49, 46.63, 65.96,  98.81))
</soure> 
Затем зарезервируем остаток нераспрделенного плана (пока что равен исходному) и пустой фрейм для результата.

<source>
plan.remain <- plan  
result.total <- data_frame() 

1. Распределяем по терр, фо (фед.округу), бренд, класс

merge.fields <- c("pos_terr","pos_fo","product_brend", "product_class")
result.current <- fn_distr(sharetab = fact,sharetab.value = "fact.sales",sharetab.targetvars = "pos_channell",
                           basetab = plan.remain,basetab.value = "plan.sales",merge.vrs = merge.fields)

result.total <- result.current %>% filter(!is.na(distributed.result)) %>% select(-plan.sales) %>% bind_rows(result.total)
# ниже получаем остаток плана - нераспределенные записи для следующих итераций
plan.remain <- result.current %>% filter(is.na(distributed.result)) %>% select(colnames(plan))
# на каждой итерации проверяем что сумма оставшегося плана и накопительное распрделение = сумме исходного плана
cat("Расхождение :",sum(plan.remain$plan.sales)+sum(result.total$distributed.result)-sum(plan$plan.sales),"\n",
    "Осталось распределить:",nrow(plan.remain)," записей")

image

2. Распределяем по фо, бренд, класс (т.е. отказываемся от территории в факте)

Единственное отличие от первого блока — слегка укоротили merge.fields удалив в нем pos_terr

merge.fields <- c("pos_fo","product_brend", "product_class")
result.current <- fn_distr(sharetab = fact,sharetab.value = "fact.sales",sharetab.targetvars = "pos_channell",
                           basetab = plan.remain,basetab.value = "plan.sales",merge.vrs = merge.fields)
result.total <- result.current %>% filter(!is.na(distributed.result)) %>% select(-plan.sales) %>% bind_rows(result.total)
plan.remain <- result.current %>% filter(is.na(distributed.result)) %>% select(colnames(plan))
cat("Расхождение :",sum(plan.remain$plan.sales)+sum(result.total$distributed.result)-sum(plan$plan.sales),"\n",
    "Осталось распределить:",nrow(plan.remain)," записей")

3. Распределяем по фо, класс

merge.fields <- c("pos_fo", "product_class")
result.current <- fn_distr(sharetab = fact,sharetab.value = "fact.sales",sharetab.targetvars = "pos_channell",
                           basetab = plan.remain,basetab.value = "plan.sales",merge.vrs = merge.fields)
result.total <- result.current %>% filter(!is.na(distributed.result)) %>% select(-plan.sales) %>% bind_rows(result.total)
plan.remain <- result.current %>% filter(is.na(distributed.result)) %>% select(colnames(plan))
cat("Расхождение :",sum(plan.remain$plan.sales)+sum(result.total$distributed.result)-sum(plan$plan.sales),"\n",
    "Осталось распределить:",nrow(plan.remain)," записей")

4. Распределяем по класс

merge.fields <- c( "product_class")
result.current <- fn_distr(sharetab = fact,sharetab.value = "fact.sales",sharetab.targetvars = "pos_channell",
                           basetab = plan.remain,basetab.value = "plan.sales",merge.vrs = merge.fields)
result.total <- result.current %>% filter(!is.na(distributed.result)) %>% select(-plan.sales) %>% bind_rows(result.total)
plan.remain <- result.current %>% filter(is.na(distributed.result)) %>% select(colnames(plan))
cat("Расхождение :",sum(plan.remain$plan.sales)+sum(result.total$distributed.result)-sum(plan$plan.sales),"\n",
    "Осталось распределить:",nrow(plan.remain)," записей")

image

5. Распределяем по ФО

merge.fields <- c( "pos_fo")
result.current <- fn_distr(sharetab = fact,sharetab.value = "fact.sales",sharetab.targetvars = "pos_channell",
                           basetab = plan.remain,basetab.value = "plan.sales",merge.vrs = merge.fields)
result.total <- result.current %>% filter(!is.na(distributed.result)) %>% select(-plan.sales) %>% bind_rows(result.total)
plan.remain <- result.current %>% filter(is.na(distributed.result)) %>% select(colnames(plan))
cat("Расхождение :",sum(plan.remain$plan.sales)+sum(result.total$distributed.result)-sum(plan$plan.sales),"\n",
    "Осталось распределить:",nrow(plan.remain)," записей")

Как видим «нераспиленного» плана не осталось и арифметика распрделенного плана равна исходному.

image

А вот и результат c каналами продаж (в правом столбце функция выводит -по каким полям шла сцепка/агрегация что бы потом понять откуда такое распределение):

image

Вот собственно и все. Статья получилась не очень маленькая, но тут больше поясняющего текста чем самого кода.

Надеюсь этот гибкий подход будет экономить время и нервы не только мне :-)

Спасибо за внимание.




К сожалению, не доступен сервер mySQL