# 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.

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

# # # # # # # # # # 
#
# version 1 borrowing elements of paulorzp's script: https://www.kaggle.com/paulorzp/grupo-bimbo-inventory-demand/r-medians/run/269821
# version 2 updated for file generation and making test submission, add summary to log
# version 3 https://www.kaggle.com/jesseburstrom/grupo-bimbo-inventory-demand/median-product-0-68749-on-lb/code
#
# # # # # # # # # # 

# Read in only required columns and force to numeric to ensure that subsequent 
# aggregation when calculating medians works
print(paste("Load Train Data", Sys.time()))
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"))

#Print first 6 rows and show that conversion from integer has been successful
print("Head")
head(train)

print(paste("CLASSES:"))
sapply(train, class)
summary(train)

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

#calculate the overall median
median <- train[, median(Demanda_uni_equil)]

#calculate the product overall median; call it M2
median_Prod <- train[, median(Demanda_uni_equil), by = Producto_ID]
setnames(median_Prod,"V1","M2")

#calculate the client and product  median; call it M3
median_Client_Prod <- train[, median(Demanda_uni_equil),by = .(Producto_ID,Cliente_ID)]
setnames(median_Client_Prod,"V1","M3")

###################################################################################################
#  
# 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
print(paste("Load Test Data",Sys.time()))
test <- fread('../input/test.csv', 
               select = c('id','Agencia_ID', 'Producto_ID', 'Cliente_ID'),
               colClasses=c(Agencia_ID="numeric",Producto_ID="numeric", Cliente_ID="numeric"))

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

# Create table called submit that joins medians (in field M3) by Product and Client to test data set
submit <- merge(test, median_Client_Prod, all.x = TRUE)

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

# Now create Predictions column; intially set to be M3 which contains median by product and client
submit$Pred <- submit$M3

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

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

# now relabel columns ready for creating submission
setnames(submit, "Pred", "Demanda_uni_equil")

# check all looks OK
head(submit)

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