---
title: "Tidy up the data"
author: "Mark Edmondson"
date: "9/15/2018"
output: html_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```
Tidying the dataset and putting it into the right R classes

## Load and flatten JSON

As per this [kernel](https://www.kaggle.com/marked/using-r-tidyeval-to-expand-json-into-columns)

```{r message=TRUE, warning=TRUE}
library(readr)
library(tidyverse)
library(jsonlite)

train <- read_csv("../input/train.csv")
test  <- read_csv("../input/test.csv")

json_to_columns <- function(df, column){
  column <- enquo(column)
  
  json_df <- df %>% 
    pull(!!column) %>% 
    paste(collapse = ",") %>% 
    paste("[",.,"]") %>% 
    fromJSON(flatten = TRUE)
  
  df %>% 
    select(-!!column) %>% 
    bind_cols(json_df)
}

train <- train %>% 
  json_to_columns(device) %>% 
  json_to_columns(geoNetwork) %>% 
  json_to_columns(totals) %>% 
  json_to_columns(trafficSource)
  
test <- test %>% 
  json_to_columns(device) %>% 
  json_to_columns(geoNetwork) %>% 
  json_to_columns(totals) %>% 
  json_to_columns(trafficSource)
```

## Data processing

Select columns that have more than one distinct varaible, put variables into convenient R classes. 

The transactionRevenue is in reality *10^6 the real $ value, but is left as it is as its the target for the competition. 

```{r}
is_not_unique <- function(x) length(unique(x))!=1
numeric_to_date <- function(x) as.Date(as.character(x), format = "%Y%m%d")

format_data <- function(x, test_data = FALSE){
  o <- x %>% 
    mutate(date = numeric_to_date(date),
           dow  = weekdays(date),
           visitId = as.character(visitId),
           visitStartTime = as.POSIXct(visitStartTime, origin="1970-01-01"),
           visits = as.numeric(visits),
           hits = as.numeric(hits),
           pageviews = as.numeric(pageviews),
           bounces = as.numeric(bounces),
           newVisits = as.numeric(newVisits),

           ) %>% 
    select_if(is_not_unique)
  
  if(!test_data){
    o <- o %>% mutate(transactionRevenue = as.numeric(transactionRevenue))
  }
  
  o
}
train_processed <- train %>% format_data()
test_processed <- test %>% format_data(test_data = TRUE)
```

## Summary

Some overview on what the data looks like now:

```{r}
str(train_processed)
str(test_processed)

summary(train_processed)
summary(test_processed)
```