library(data.table)

print("Reading data")
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'))
              
mean_global <- expm1(mean(log1p(train$Demanda_uni_equil)))

mean_PCA <- train[, .(MPCA = expm1(mean(log1p(Demanda_uni_equil)))), by = .(Producto_ID, Cliente_ID, Agencia_ID)]

mean_P <-  train[, .(MP =expm1(mean(log1p(Demanda_uni_equil)))), by = .(Producto_ID)]

mean_PA <- train[, .(MPA = expm1(mean(log1p(Demanda_uni_equil)))), by = .(Producto_ID, Agencia_ID)] 

mean_PR <- train[, .(MPR = expm1(mean(log1p(Demanda_uni_equil)))), by = .(Producto_ID, Ruta_SAK)] 

print("Merging means with test set")
submit <- merge(test, mean_PCA, all.x = TRUE, by = c("Producto_ID", "Cliente_ID", "Agencia_ID"))
submit <- merge(submit, mean_PR, all.x = TRUE, by = c("Producto_ID", "Ruta_SAK"))
submit <- merge(submit, mean_PA, all.x = TRUE, by = c("Producto_ID", "Agencia_ID"))
submit <- merge(submit, mean_P, all.x = TRUE, by = "Producto_ID")

submit$Pred <- submit$MPCA
submit[is.na(Pred)]$Pred <- submit[is.na(Pred)]$MPR
submit[is.na(Pred)]$Pred <- submit[is.na(Pred)]$MPA
submit[is.na(Pred)]$Pred <- submit[is.na(Pred)]$MP
submit[is.na(Pred)]$Pred <- mean_global

print("Write out submission file")
# now relabel columns ready for creatig submission
setnames(submit,"Pred","Demanda_uni_equil")
# Any results you write to the current directory are saved as output.
write.csv(submit[,.(id,Demanda_uni_equil)],"submission.csv", row.names = FALSE)
print("Done!")