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_clean <- mean(train[Demanda_uni_equil < 100]$Demanda_uni_equil) #cleaned mean
#mean_total <- mean(train$Demanda_uni_equil) #overall mean
#median_total <- median(train$Demanda_uni_equil)
#print(mean_clean)
#print(mean_total)
#print(median_total)
train$Demanda_uni_equil <- as.double(train$Demanda_uni_equil)
train[Demanda_uni_equil > 200]$Demanda_uni_equil <- mean_clean



print("Computing means")
#transform target variable to log(1 + demand) - this makes sense since we're 
#trying to minimize rmsle and the mean minimizes rmse:
train$log_demand = log1p(train$Demanda_uni_equil)
#mean_total <- mean(train$log_demand) #overall mean
median_total <- median(train$log_demand) #overall median

#mean by product
mean_P <-  train[, list(MP = mean(log_demand)), by = list(Producto_ID)]
#mean by cliente
mean_C <-  train[, list(MC = mean(log_demand)), by = list(Cliente_ID)]
#mean by product and agencia
mean_PA <- train[, list(MPA = mean(log_demand)), by = list(Producto_ID, Agencia_ID)] 
#mean by product and ruta
mean_PR <- train[, list(MPR = mean(log_demand)), by = list(Producto_ID, Ruta_SAK)] 
#mean by product, client, agencia
mean_PCA <- train[, list(MPCA = mean(log_demand)), by = list(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")

# Now create Predictions column;
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 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)],"submit_mean_by_Agency_Ruta_Client.csv", row.names = FALSE)
print("Done!")