# 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 train data")

mydata = read.csv("../input/train.csv", colClasses=c("NULL",	NA,	"NULL",	"NULL",	NA, NA,	"NULL",	"NULL",	"NULL",	"NULL",	NA))

attach(mydata)

mydata$Cliente_ID = as.factor(mydata$Cliente_ID)
mydata$Producto_ID = as.factor(mydata$Producto_ID)
mydata$Agencia_ID = as.factor(mydata$Agencia_ID)

sapply(mydata, class)
setDT(mydata)

print("Calculating mean")

mydata$Demand_log = log1p(mydata$Demanda_uni_equil+1) 

median_total <- median(mydata$Demanda_uni_equil) #overall log mean

mean_P <- mydata[, .(MP = mean(Demand_log)), by = .(Producto_ID)]
mean_PC <- mydata[, .(MPC = mean(Demand_log)), by = .(Producto_ID, Cliente_ID)] 
mean_PCA <- mydata[, .(MPCA = mean(Demand_log)), by = .(Producto_ID, Cliente_ID, Agencia_ID)]

rm(mydata)

print("Reading test data")

validateData = read.csv("../input/test.csv", colClasses=c(NA, "NULL",	NA,	"NULL",	"NULL", NA,	NA))

attach(validateData)

validateData$Cliente_ID = as.factor(validateData$Cliente_ID)
validateData$Producto_ID = as.factor(validateData$Producto_ID)
validateData$Agencia_ID = as.factor(validateData$Agencia_ID)

sapply(validateData, class)
setDT(validateData)

print("merging mean")

submit <- merge(validateData, mean_P, all.x = TRUE, by = c("Producto_ID"))
submit <- merge(validateData, mean_PC, all.x = TRUE, by = c("Producto_ID", "Cliente_ID"))
submit <- merge(validateData, mean_PCA, all.x = TRUE, by = c("Producto_ID", "Cliente_ID", "Agencia_ID"))

rm(validateData)
rm(mean_PC)

submit$Prediction <- expm1(submit$MPCA)
submit[is.na(Prediction)]$Prediction <- expm1(submit[is.na(Prediction)]$MPC)
submit[is.na(Prediction)]$Prediction <- expm1(submit[is.na(Prediction)]$MP)
submit[is.na(Prediction)]$Prediction <- expm1(median_total)

submit$Prediction <- round(submit$Prediction,5)

setnames(submit,"Prediction","Demanda_uni_equil")

newdata = submit[order(id),] 

print("write file")

write.csv(newdata[,.(id,Demanda_uni_equil)],"result_log_mean_neww.csv", row.names = FALSE)