# This R environment comes with all of CRAN preinstalled, as well as many other helpful packages
# The environment is defined by the kaggle/rstats docker image: https://github.com/kaggle/docker-rstats
# For example, here's several helpful packages to load in 

library(ggplot2) # Data visualization
library(readr) # CSV file I/O, e.g. the read_csv function

# Input data files are available in the "../input/" directory.
# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory

system("ls ../input")

# Any results you write to the current directory are saved as output.

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'))

print("Computing means")

train$log_demand = log1p(train$Demanda_uni_equil)
median_total <- median(train$log_demand) #overall median

#mean by product
mean_P <-  train[, .(MP = mean(log_demand)), by = .(Producto_ID)]
#mean by cliente
mean_C <-  train[, .(MC = mean(log_demand)), by = .(Cliente_ID)]
#mean by product and agencia
mean_PA <- train[, .(MPA = mean(log_demand)), by = .(Producto_ID, Agencia_ID)] 
#mean by product and ruta
mean_PR <- train[, .(MPR = mean(log_demand)), by = .(Producto_ID, Ruta_SAK)] 
#mean by product, client, agencia
mean_PCA <- train[, .(MPCA = mean(log_demand)), by = .(Producto_ID, Cliente_ID, Agencia_ID)]

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_C, all.x = TRUE, by = "Cliente_ID")
submit <- merge(submit, mean_P, all.x = TRUE, by = "Producto_ID")

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

submit$Pred <- round(submit$Pred,4)

print("Write file")
setnames(submit,"Pred","Demanda_uni_equil")

newdata = submit[order(id),] 

newdata[Demanda_uni_equil > 1000,]$Demanda_uni_equil <- 1000

write.csv(newdata[,.(id,Demanda_uni_equil)],"submit_mean.csv", row.names = FALSE)
print("Done!")