### Grupo Bimbo Kaggle Competition
#
### Patrick Coady. August 8, 2016. (patcoady@msn.com)
#
# Desc: Collect data into standard machine learning format. Each row of DF contains 
#   historical store data plus labels (i.e. features and labels for each training 
#   example in 1 row).
#
# _L1 through _L5 are data from same store and product from previous weeks.
#   (L1 is week 7 data, L2 is week 6 data, ...)
#
# _p1 and _p2 are weeks to be predicted (assumed to be week 8 and 9, respectively,
#   for the training set).
#
# Note: I've changed names to English. Helped to avoid a small bit of confusion as 
#   I was first digesting data set

library(dplyr)
library(data.table)

## low memory version of data load - only keeps adjusted demand
train <- fread('../input/train.csv', 
         select = c('Semana', 'Cliente_ID', 'Producto_ID', 
                    'Agencia_ID', 'Ruta_SAK', 'Demanda_uni_equil'),
         header = T, sep = ',', data.table = F)
train <- rename(train, week = Semana, client_ID = Cliente_ID, 
              product_ID = Producto_ID, depot_ID = Agencia_ID, 
              route_ID = Ruta_SAK, adjDemx = Demanda_uni_equil)

## use this section if you have more memory and want ALL the variables
# train <- fread('../input/train.csv', header = T, sep = ',', data.table = F)
# names(train) <- c('week', 'depot_ID', 'channel_ID', 'route_ID', 'client_ID',
#                    'product_ID', 'salesx', 'salesPesosx', 'returnsx',
#                    'returnsPesosx', 'adjDemx')

wk <- 3
for (lagName in c('_L5', '_L4', '_L3', '_L2', '_L1', '_p1', '_p2')) { # time lagged column names
  addDF <- filter(train, week == wk) %>% select(-week)
  train <- filter(train, week != wk) # don't need this week now (save memory) 
  colnames(addDF) <- gsub('x$', lagName, colnames(addDF)) # change colnames to "lagged" names
  if (wk == 3) {
    featDF <- addDF
  } else {
    featDF <- full_join(featDF, addDF) 
  }
  wk <- wk + 1
}

### join to keep only rows needed for submission DF

cNames = colnames(featDF)
cNames <- gsub('L5', 'L7', cNames)
cNames <- gsub('L4', 'L6', cNames)
cNames <- gsub('L3', 'L5', cNames)
cNames <- gsub('L2', 'L4', cNames)
cNames <- gsub('L1', 'L3', cNames)
cNames <- gsub('p1', 'L2', cNames)
cNames <- gsub('p2', 'L1', cNames)
colnames(featDF) <- cNames

### load test data set

test <-  fread('../input/test.csv', header = T, sep = ',', data.table = F)
names(test) <- c("id", "week", "depot_ID", "channel_ID", "route_ID", "client_ID",
                 "product_ID") 

### grab just the rows from prediction set

testRows <- group_by(test, depot_ID, channel_ID, route_ID, client_ID, product_ID) %>%
  summarise(count = n(), sumWeeks = sum(week)) %>%
  ungroup() %>% select(-count)
testRows$adjDem_p1 = NA
testRows$adjDem_p2 = NA
testRows$adjDem_p1[(testRows$sumWeeks == 10) | (testRows$sumWeeks == 21)] = 0 # 0 if week10 prediction needed
testRows$adjDem_p2[(testRows$sumWeeks == 11) | (testRows$sumWeeks == 21)] = 0 # 0 if week11 prediction needed
testRows$sumWeeks = NULL # remove column - no longer needed
submitDF <- left_join(testRows, featDF)