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 medians")
#transform target variable to log(1 + demand) - this makes sense since we're 
#trying to minimize rmsle and the median minimizes rmse:
train$log_demand = log1p(train$Demanda_uni_equil) 
median_total <- median(train$log_demand) #overall median
#median by product
median_P <-  train[, .(MP = median(log_demand)), by = .(Producto_ID)]
#median by product and ruta
median_PR <- train[, .(MPR = median(log_demand)), by = .(Producto_ID, Ruta_SAK)] 
#median by product, client, agencia
median_PCA <- train[, .(MPCA = median(log_demand)), by = .(Producto_ID, Cliente_ID, Agencia_ID)]

print("Merging medians with test set")
submit <- merge(test, median_PCA, all.x = TRUE, by = c("Producto_ID", "Cliente_ID", "Agencia_ID"))
submit <- merge(submit, median_PR, all.x = TRUE, by = c("Producto_ID", "Ruta_SAK"))
submit <- merge(submit, median_P, all.x = TRUE, by = "Producto_ID")

# Now create Predictions column;
submit$Pred <- expm1(submit$MPCA)*0.826+0.45
submit[is.na(Pred)]$Pred <- expm1(submit[is.na(Pred)]$MPR)*0.79+0.09
submit[is.na(Pred)]$Pred <- expm1(submit[is.na(Pred)]$MP)*1.05+0.6
submit[is.na(Pred)]$Pred <- expm1(median_total) + 1.15

print("Write out submission file")
# now relabel columns ready for creating 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_median_by_Agency_Ruta_Client.csv", row.names = FALSE)
print("Done!")