library(ggplot2) # Data visualization
library(readr) # CSV file I/O, e.g. the read_csv function
library(data.table)

# Any results you write to the current directory are saved as output.

train <- fread('../input/train.csv', 
               select = c( 'Agencia_ID', 'Producto_ID', 'Cliente_ID', 'Demanda_uni_equil'))
test <- fread('../input/test.csv', 
              select = c('id', 'Agencia_ID', 'Producto_ID', 'Cliente_ID'))

#get log(1+demnd)
train$log_demnda = log1p(train$Demanda_uni_equil)
mean_total <- mean(train$log_demnda)
print("calulated mean total")

#get mean by P,A,C
mean_byPAC <-  train[, .(MPAC = weighted.mean(log_demnda)), by = .(Producto_ID, Agencia_ID,Cliente_ID)]
#get mean by P,C 
mean_byAC <-  train[, .(MAC = weighted.mean(log_demnda)), by = .(Producto_ID,Cliente_ID)]

print(" mean grup by calulated")



              
submit <- merge(test, mean_byPAC, all.x = TRUE, by = c("Agencia_ID", "Cliente_ID", "Producto_ID"))
submit <- merge(submit, mean_byAC, all.x = TRUE, by = c("Producto_ID", "Cliente_ID"))

submit$logclear <- expm1(submit$MPAC)
submit[is.na(logclear)]$logclear <- expm1(submit[is.na(logclear)]$MAC)
submit[is.na(logclear)]$logclear <- expm1(mean_total)

print("writing submission file")

setnames(submit,"logclear","Demanda_uni_equil")

write.csv(submit[,.(id,Demanda_uni_equil)],"submit_mean_by_log1p_Agncy_Prod_Cli.csv", row.names = FALSE)
print("Done!")