library(data.table)
library(dplyr)

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

train$Demanda_uni_equil = log1p(train$Demanda_uni_equil) 

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

train[,.(mean_prod_client = mean(Demanda_uni_equil)), by = .(Producto_ID, Cliente_ID, Agencia_ID)] %>% 
    merge(test, all.y =TRUE, by = c("Producto_ID", "Cliente_ID", "Agencia_ID")) -> test
train[,.(mean_prod = mean(Demanda_uni_equil)), by = .(Producto_ID, Ruta_SAK)] %>% 
    merge(test, all.y =TRUE, by = c("Producto_ID", "Ruta_SAK")) -> test
train[,.(mean_client = mean(Demanda_uni_equil)), by = .(Cliente_ID)] %>% 
    merge(test, all.y =TRUE, by = c("Cliente_ID")) -> test
    
#taking a weighted average of the averages.
test %>% select(mean_client, mean_prod, mean_prod_client) %>% 
    apply(MARGIN = 1, function(x) weighted.mean(x, w = c(1,3,13), na.rm = TRUE)) -> test$preds

test$preds[is.na(test$preds)] = mean(train$Demanda_uni_equil) 
preds = expm1(test$preds)
submission = data.frame(id = test$id, Demanda_uni_equil = preds)
write.csv(submission, "submission.csv", row.names = FALSE)