library(data.table)


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"))


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"))


gc()

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

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

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

#calculate the agent and product  mean; call it M3
mean_Agent_Prod <- train[, mean(Demanda_uni_equil), by = .(Producto_ID,Agencia_ID,Cliente_ID)]
setnames(mean_Agent_Prod,"V1","M3")


# Create table called submit that joins medians (in field M3) by Product and Agent to test data set
test <- merge(test, mean_Agent_Prod, by = c("Producto_ID","Agencia_ID","Cliente_ID"), all.x = TRUE)

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

# Now create Predictions column; intially set to be M3 which contains mean by product and agent
test$Pred <- test$M3

# where mean by product and agent is null use mean by product (M2)
test[is.na(test$M3),]$Pred <- round(test[is.na(test$M3),]$M2)

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

# now relabel columns ready for creating submission

sqrt(mean((log1p(test$Pred+1)-log1p(test$Demanda_uni_equil+1))^2))