Доброго времени дня.
В реальной практике довольно часто сталкиваешься с задачами, далекими от сложных ML алгоритмов, но при этом являющихся не менее важными и насущными для бизнеса.
Поговорим об одной из них.
Задача сводится к тому что бы распределить (распилить, рассплитовать — жаргон бизнеса неиссякаем) данные какой-нибудь целевой таблицы с агрегатами (совокупные значения) на таблицу более детальной гранулярности.
Например коммерческому департаменту надо разбить годовой план, согласованный на уровне брендов — детально до продукции, маркетологам разбить годовой бюджет маркетинга по территориям страны, планово-экономическому департаменту разбить общехозяйственные издержки по центрам финансовой ответственности, и т.д. и т.п.
Если вы почувствовали что задачи подобные этой уже маячат перед вами на горизонте или уже относитесь к пострадавшим от таких задач, то прошу под кат.
Рассмотрим реальный пример:
Спускают в качестве задачи план продаж как на картинке ниже (я намеренно сделал пример упрощенным, в реальности — полотнище в экселе на 100-200 мгб).
Пояснение по заголовкам:
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)
}
Сумма(План_изначальный)-Сумма(План_распределенный_в_накопленном результате)-Сумма(План_нераспределенный_после_тек.итерации)=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()
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)," записей")
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)," записей")
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)," записей")
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)," записей")
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)," записей")
К сожалению, не доступен сервер mySQL