library(data.table)
library(dplyr)

train <- fread('../input/train.csv', 
               select = c('Cliente_ID', 'Producto_ID', 'Agencia_ID', 'Ruta_SAK', 'Demanda_uni_equil', 'Canal_ID'))
test <- fread('../input/test.csv', 
              select = c('id', 'Cliente_ID', 'Producto_ID', 'Agencia_ID', 'Ruta_SAK', 'Canal_ID'))
train$log_demand = log1p(train$Demanda_uni_equil) 

setkey(train, Producto_ID, Cliente_ID, Ruta_SAK, Canal_ID)
setkey(test, Producto_ID, Cliente_ID, Ruta_SAK, Canal_ID)

mean_total <- mean(train$log_demand) #overall mean

mean_Prod <-  train[, .(mean_prod = mean(log_demand)), by = .(Producto_ID)]

mean_Prod_Client <- train[, .(mean_prod_client = mean(log_demand)),
                        by = .(Producto_ID, Cliente_ID)] #mean by product and client ID

mean_Prod_Client_Ruta_Canal <- train[, .(mean_prod_client_ruta_canal = mean(log_demand)),
                          by = .(Producto_ID, Cliente_ID,Ruta_SAK, Canal_ID)] #mean by product, client IDm ruta ID, and canal ID


mean_Client_Prod_Agencia_Ruta <- train[, .(mean_client_prod_agencia_ruta = mean(log_demand)),
                                  by = .(Producto_ID, Cliente_ID, Agencia_ID,Ruta_SAK )] #mean by product, client, agencia, and Route


submit <- merge(test, mean_Client_Prod_Agencia_Ruta, all.x = TRUE, by = c("Producto_ID", "Cliente_ID", "Agencia_ID", "Ruta_SAK"))

submit <- merge(submit, mean_Prod_Client_Ruta_Canal, all.x = TRUE, by = c("Producto_ID", "Cliente_ID", "Ruta_SAK", "Canal_ID"))


submit <- merge(submit, mean_Prod_Client, all.x = TRUE, by = c("Cliente_ID", "Producto_ID"))

submit <- merge(submit, mean_Prod, all.x = TRUE, by ="Producto_ID")

preds = submit$mean_client_prod_agencia_ruta
preds[is.na(preds)] = submit$mean_prod_client_ruta_canal[is.na(preds)]
preds[is.na(preds)] = submit$mean_prod_client[is.na(preds)]

preds[is.na(preds)] = submit$mean_prod[is.na(preds)]
preds[is.na(preds)] = mean_total

preds = expm1(preds)

submission = data.frame(id = submit$id, Demanda_uni_equil = preds)
write.csv(submission, "submission.csv", row.names = FALSE)