# 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.
###################################################################################################
#  
# Conversion of Abhishek Malali's Python script and concepts to R 
# https://www.kaggle.com/armalali/grupo-bimbo-inventory-demand/benchmark-means/discussion
# 
# Scores 0.50758 on Public leaderboard
# Shows how R can work with large datsets at speed and within memory constraints
#
# Runs quickly on my home machine which has the benefit of SSD storage
#
#
###################################################################################################

#Load data.table for fast reads and aggreagtions
library(data.table)

# Input data files are available in the "../input/" directory.

# Read in only required columns and force to numeric to ensure that subsequent 
# aggregation when calculating means works

train <- fread('../input/train.csv', 
               select = c('Agencia_ID','Cliente_ID', 'Producto_ID', 'Demanda_uni_equil'),
               colClasses=c(Agencia_ID="numeric",Cliente_ID="numeric",Producto_ID="numeric",Demanda_uni_equil="numeric"))


# In case the pair of product/client pair has a mean available, we use that as the predicted value.
# If not, then the product mean is checked. If a value is not found, then the global mean is used.

# set a table key to enable fast aggregations
setkey(train, Agencia_ID,Producto_ID, Cliente_ID)

#calculate the overall mean
mean <- train[, exp(mean(log(Demanda_uni_equil+1)))*0.80]

#calculate the product overall mean; call it M2
mean_Prod <- train[, exp(mean(log(Demanda_uni_equil+1)))*0.80, by = Producto_ID]

setnames(mean_Prod,"V1","M2")
#calculate the product overall mean; call it M3
mean_Client_Prod <- train[, exp(mean(log(Demanda_uni_equil+1)))*0.80,by = .(Producto_ID,Cliente_ID)]
setnames(mean_Client_Prod,"V1","M3")

#calculate the product, Client and Route overall mean; call it M4
mean_Client_Prod_Agen <- train[, exp(mean(log(Demanda_uni_equil+1)))*0.80,by = .(Producto_ID,Cliente_ID,Agencia_ID)]
setnames(mean_Client_Prod_Agen,"V1","M4")
remove(train)
###################################################################################################
#  
# That's the 'modeling' done now need to apply scoring to test set
# 
###################################################################################################

# Read in Test data 
# Read in only required columns and force to numeric

test <- fread('../input/test.csv', 
              select = c('id','Cliente_ID', 'Producto_ID','Agencia_ID'),
              colClasses=c(Cliente_ID="numeric",Producto_ID="numeric",Agencia_ID="numeric"))


# set a table key to enable fast joins to predictions
setkey(test, Producto_ID, Cliente_ID,Agencia_ID)

# Create table called submit that joins means by Product, Agencia_ID and Client to test data set
submit <- merge(test, mean_Client_Prod_Agen, by=c("Producto_ID","Cliente_ID","Agencia_ID"),all.x = TRUE)

# add column M3 that contains mean by Product and Client
submit$M3 <- merge(test, mean_Client_Prod, by = c("Producto_ID","Cliente_ID"), all.x = TRUE)$M3

# add column M2 that contains mean by Product
submit$M2 <- merge(test, mean_Prod, by = "Producto_ID", all.x = TRUE)$M2

remove(test)

submit$pred<-submit$M4
# where mean by product and client is null use mean by product (M2)
submit[is.na(submit$M4)]$pred<- submit[is.na(M4)]$M3

# where mean by product and client is null use mean by product (M2)
submit[is.na(submit$M3)]$pred<- submit[is.na(M3)]$M2

# where mean by product is null use overall mean
submit[is.na(submit$pred)]$pred <- mean

submit[submit$pred<0]$pred <- 0
# check all looks OK
setnames(submit,"pred","Demanda_uni_equil")
head(submit)

# Write out submission file.
# Any results you write to the current directory are saved as output.
write.csv(submit[,.(id,Demanda_uni_equil)],"submit.csv", row.names = FALSE)