---
title: "A Dad Beer OR, Bread EDA?"
author: "JR"
output: html_document
---

```{r, helpers, echo = FALSE, eval = TRUE}
top_10 <- function(df, x) {
    kable(setNames(as.data.frame(
        sort(table(df[,x]), decreasing = TRUE)[1:10]
    ), c(paste(x), "Frequency"))) # top 10
}

count_unique <- function(x) {
    kable(setNames(as.data.frame(
        table(table(x))
    ), c("Unique_Occurences", "Frequency"))) # unique counts
}
```

## Init
This R environment comes with all of CRAN preinstalled, as well as many other helpful packages
The environment is defined by the kaggle/rstats docker [image](https://github.com/kaggle/docker-rstats).

Let's check out what's going on here.

```{r, pkgs, echo = FALSE, eval = TRUE, warning = FALSE, message = FALSE}
# init
pkgs <- c("ggplot2", "readr", "dplyr", "stringr", "data.table", "ggmap")
sapply(pkgs, library, character.only = TRUE, quietly = TRUE)
```
```{r, dir, echo = TRUE}
# check data
list.files("../input")
```

All of our inputs exist in `../input`.
Any outputs we write will exist in the working directory, or ***`r getwd()`***.

Now that we know where our data is, let's go to work with examining and preparing the data.

We'll start with the data extraneous to the training set.

### Clients
```{r, clientHead, echo = TRUE}
client <- readr::read_csv("../input/cliente_tabla.csv")
dim(client)
head(client)
```
A very quick glance, and it appears that there are some serious issues with this lookup table.
* `Cliente_ID` is not unique, according to indicies 5-6.
* `Cliente_ID` does not represent unique `NombreCliente` records. Indices 1,3 should be identical records, both "Nameless"

Let's see what else is going on.

**Top 10**![dave](http://i.ebayimg.com/00/s/NzAxWDcwMQ==/z/8g4AAOSwX~dWm6me/$_14.JPG)
```{r, clientTab1, echo = FALSE, results = "asis"}
top_10(client, "Cliente_ID")
```

**Count of Counts**![a count](http://icons.iconarchive.com/icons/pino/sesame-street/32/The-Count-icon.png)
```{r, clientTab2, echo = FALSE, results = "asis"}
count_unique(client$Cliente_ID)
```

That's a pretty diverse clientele! But hardly a fully cleaned lookup table.
Perhaps we should build a feature of like client reduction.
This would be appropriate after finding some manner to join this table to the rest of the sets.

For now, let's do some basic cleaning.
I'll quickly build a feature to make sense of the dirty client ID.

```{r, clientCleaning1, echo = TRUE}
# clean string
client$clean_client <- stringr::str_replace_all(toupper(client$NombreCliente), "\\W", "")

# make seq key by group
client <- transform(client, clean_client_id = match(clean_client, unique(clean_client)))
```
```{r, clientCleaning2, echo = FALSE, results = "asis"}
top_10(client, "clean_client_id")
```

Much better!
We should note that if there is some hidden variable linked to `Cliente_ID`, this new cleaned ID will not be suitable.
For instance, if ID = 1 and Name = SinNombre, but that ID was linked to a hidden client location,
then there would be no reason to use that new ID.

We'll discover if this is the case later.

### Products
```{r, productHead, echo = TRUE}
product <- readr::read_csv("../input/producto_tabla.csv")
dim(product)
head(product)
```

```{r, productTab, echo = FALSE, results = "asis"}
top_10(product, "Producto_ID")
count_unique(product$Producto_ID)
```
There's no immediate indication of duplicated products. We may need to return to this.

Additionally, we might also want to parse the product details from the name, which appears to contain
details such as the product ("Burritos Sincro"), the unit weight ("170g"), and the brand ("LON", or Lonchibon)

Let's do this now.
```{r, productEng1, echo = TRUE, warning = FALSE, message = FALSE}
# compile regex
first_half <- regex(".*[0-9]+g", ignore_case = TRUE)
second_half <- regex("[0-9]+g.*[0-9]+")
unit_name <- regex("[a-z ]+", ignore_case = TRUE)
unit_price <- regex("[0-9]+p", ignore_case = TRUE)
unit_weight <- regex("[0-9]+g", ignore_case = TRUE)
unit_brand <- regex("[A-Z ]+[0-9]+")

# extract from first half of string representation
first_half <- str_extract(product$NombreProducto, first_half)
product$unit_name <- str_extract(first_half, unit_name)
product$unit_price <- as.numeric(
    str_replace( str_extract(first_half, unit_price), "p", "")
)

# now do second
second_half <- str_extract(product$NombreProducto, second_half)
product$unit_weight <- as.numeric(
    str_replace( str_extract(second_half, unit_weight), "g", "")
)
product$unit_brand <- str_replace( str_extract(second_half, unit_brand), "[0-9]+", "")
```
```{r, productEng2, echo = FALSE, results = "asis"}
top_10(product, "unit_name")
top_10(product, "unit_brand")
```
```{r, productEng3, echo = TRUE, warning = FALSE}
qplot(data = product, x = unit_price)
qplot(data = product, x = unit_weight)
```


Finally, because the nutritional data is easily available through Bimbo's site, 
we may want to isolate parts of the product name to build these new features. 

### Town
```{r, townHead, echo = TRUE}
town <- readr::read_csv("../input/town_state.csv")
dim(town)
head(town)
```
```{r, townTab, echo = FALSE, results = "asis"}
top_10(town, "Agencia_ID")
count_unique(town$Agencia_ID)
```

This appears to be similar to the client data set, with duplicated IDs per Town.
Same caveats apply. Let's clean.


```{r, townCleaning1, echo = TRUE}
# clean string
town$clean_place <- stringr::str_replace_all(toupper(paste0(town$Town, town$State)), "\\W", "")

# make seq key by group
town <- transform(town, clean_agency_id = match(clean_place, unique(clean_place)))
```
```{r, townCleaning2, echo = FALSE, results = "asis"}
top_10(town, "Agencia_ID")
```

Geocoding is also possible, with the right input.

```{r. townEng1, echo = TRUE, message = FALSE}
town$searchable_place <- paste(
    str_replace_all( str_extract(town$Town, "\\. .*"), "\\.|[0-9]", "" ), 
    "mexico" 
)
town <- cbind(town, ggmap::geocode(town$searchable_place))
ggmap::qmplot(data = town, x = lon, y = lat)
```

### Training set
```{r, trainHead, echo = TRUE}
#train <- readr::read_csv("../input/train.csv")
#dim(train)
#head(train)
```

Pretty big data set! Over 1 billion data elements. Let's reiterate the data dict:
 
1. `Semana` — Week number (From Thursday to Wednesday)
2. `Agencia_ID` — Sales Depot ID
3. `Canal_ID` — Sales Channel ID
4. `Ruta_SAK` — Route ID (Several routes = Sales Depot)
5. `Cliente_ID` — Client ID
6. `Producto_ID` — Product ID
7. `Venta_uni_hoy` — Sales unit this week (integer)
8. `Venta_hoy` — Sales this week (unit: pesos)
9. `Dev_uni_proxima` — Returns unit next week (integer)
10. `Dev_proxima` — Returns next week (unit: pesos) - this may be negative due to adjustments
11. `Demanda_uni_equil` — Adjusted Demand (integer) **DV**

The channel, route, agency, client, and product IDs are are PKs here, and we have normalized tables to bring new features over for the latter three.

This begs the question: what do the channel and route IDs represent, and what do these permutations mean to us?

Some naive top ten tables:
```{r, testExplore1, echo = FALSE, results = "asis"}
#top_10(train, "Canal_ID")
#top_10(train, "Ruta_SAK")

# train$canalXroute <- paste(train$Canal_ID, train$Ruta_SAK, sep = "::")
# train$canalXclient <- paste(train$Canal_ID, train$Cliente_ID, sep = "::")
# top_10( train, "canalXroute")
# top_10( train, "canalXclient")
```
```{r, testExplore2, echo = TRUE}

```