---
title: "Elo Merchant Category Recommendation EDA"
output:
  html_document:
    fig_height: 4
    fig_width: 8
    theme: cosmo
    highlight: tango
    number_sections: true
    fig_caption: true
    toc: true
---

# Introduction

Here is an Exploratory Data Analysis for the Elo Merchant Category Recommendation competition 
within the R environment. For this EDA we will use the [tidyverse](https://www.tidyverse.org/packages/) packages. 
Our task is to build an algorithm that helps to identify and serve the most relevant opportunities to individuals,
by uncovering signal in customer loyalty. 

Submissions are scored on the root mean squared error, which is defined as:

$$RMSE = \sqrt{\frac{1}{n} \sum_{i=1}^n (y_i - \widehat{y_i})^2},$$

where $\widehat{y}$ is the predicted loyalty score for each **card_id**, and y is the actual loyalty score 
assigned to a **card_id**.

Let's prepare and have a look at the dataset.

# Preparations {.tabset .tabset-fade}
## Load libraries
```{r, message=FALSE, warning=FALSE, results='hide'}
library(Rmisc)
library(lubridate)
library(scales)
library(lightgbm)
library(xgboost)
library(keras)
library(magrittr)
library(tidyverse)
library(data.table)
library(tidyverse)
library(dplyr)
library(ggplot2)
library(Matrix)
library(anytime)
library(plotly)
library(janitor)
library(corrplot)
```

## Load data
```{r load, message=FALSE, warning=FALSE, results='hide'}
set.seed(0)
train <- fread("../input/train.csv") 
test <- fread("../input/test.csv")
histdata <- fread("../input/historical_transactions.csv") 
newdata <- fread("../input/new_merchant_transactions.csv")
merchants <-fread("../input/merchants.csv")
sample <- fread("../input/sample_submission.csv")
```

# Data loading

## Train
```{r result='asis', echo=FALSE}
cat("File size:", file.size("../input/train.csv"), "bytes")
cat("Dimensions:", dim(train))
cat("Missing values:",  sum(is.na(train)))
glimpse(train)
```
## Test
```{r result='asis', echo=FALSE}
cat("File size:", file.size("../input/test.csv"), "bytes")
cat("Dimensions:", dim(test))
cat("Missing values:",  sum(is.na(test)))
glimpse(test)
```
## Historical TX
```{r result='asis', echo=FALSE}
cat("File size:", file.size("../input/historical_transactions.csv"), "bytes")
cat("Dimensions:", dim(histdata))
cat("Missing values:",  sum(is.na(histdata)))
glimpse(histdata)
```
## New merchant TX
```{r result='asis', echo=FALSE}
cat("File size:", file.size("../input/new_merchant_transactions.csv"), "bytes")
cat("Dimensions:", dim(newdata))
cat("Missing values:",  sum(is.na(newdata)))
glimpse(newdata)
```
## Merchants
```{r result='asis', echo=FALSE}
cat("File size:", file.size("../input/merchants.csv"), "bytes")
cat("Dimensions:", dim(merchants))
cat("Missing values:",  sum(is.na(merchants)))
glimpse(merchants)
```
## Sample Submission
```{r result='asis', echo=FALSE}
cat("File size:", file.size("../input/sample_submission.csv"), "bytes")
cat("Dimensions:", dim(sample))
cat("Missing values:",  sum(is.na(sample)))
glimpse(sample)
```


# EDA 

```{r}
##train
active_count.df_train=melt(table(train$first_active_month))
colnames(active_count.df_train)=c('date','count')
active_count.df_train$date= anydate(active_count.df_train$date)
active_count.df_train$category='train'
```


```{r}
##test
active_count.df_test=melt(table(test$first_active_month))
colnames(active_count.df_test)=c('date','count')
active_count.df_test$date= anydate(active_count.df_test$date)
active_count.df_test$category='test'
active_count.df=rbind(active_count.df_train,active_count.df_test)
```
## first active month comparison

```{r}
p_t_first_active=ggplot(active_count.df, aes(date, count, group=category, colour=category)) + geom_line() + ggtitle('First Active Month Count by Date')+xlab('Date') +ylab("count of first active month")+theme_bw()
p_t_first_active <- ggplotly(p_t_first_active)
p_t_first_active
```



## target distribution 

```{r}
p_target <- ggplot(train, aes(x = target)) +stat_density(position="identity",geom="line")+ ggtitle('The distribution of loyalty score')
p_target <- ggplotly(p_target)
p_target
```

## outliers 
```{r}
train %>% 
  filter(target < -30) %>% 
  summarize(count=n())
```

## comparison between good loyal score people and bad score people 

```{r}
bad_loyal_score_people= train %>% filter(target < -30) 
good_loyal_score_people= train %>% filter(target > -30) 
```

```{r}
bad_loyal_feature=cbind(melt(bad_loyal_score_people[,3:5]),"bad");colnames(bad_loyal_feature)=c('feature','value','group')
good_loyal_feature=cbind(melt(good_loyal_score_people[,3:5]),"good");colnames(good_loyal_feature)=c('feature','value','group')

feature_compare=rbind(bad_loyal_feature,good_loyal_feature)

loyal_feature_comparison= ggplot(feature_compare, aes(x=feature, y=value, fill=group)) +geom_boxplot()
loyal_feature_comparison 
```



## Feature Analysis

```{r}
apply(train[,3:5],2,tabyl)

feature_1=cbind(melt(table(train[,3])/sum(table(train[,3]))),"feature_1",stringsAsFactors=F);colnames(feature_1)=c('value','percent','feature_group')
feature_2=cbind(melt(table(train[,4])/sum(table(train[,4]))),"feature_2",stringsAsFactors=F);colnames(feature_2)=c('value','percent','feature_group')
feature_3=cbind(melt(table(train[,5])/sum(table(train[,5]))),"feature_3",stringsAsFactors=F);colnames(feature_3)=c('value','percent','feature_group')

train_feature.df=rbind(feature_1,feature_2,feature_3)

p_feature_train <- ggplot(train_feature.df, aes(x = feature_group , y = percent, fill = as.factor(value))) + ggtitle("Percent by values by feature groups")
p_feature_train <- p_feature_train + geom_bar(stat = "identity", position = "stack")+  scale_fill_brewer(palette="Set1")
p_feature_train <- ggplotly(p_feature_train)
p_feature_train

```

## Historical Transcation Dataset EDA


### Purchase Amount
```{r}
##converting data type
histdata$purchase_date= anydate(histdata$purchase_date)
newdata$purchase_date= anydate(newdata$purchase_date)

purchase_hist_data.df= histdata  %>% group_by(purchase_date) %>% summarize(purchase_amount_sum=sum(purchase_amount))
purchase_hist_data.df$category="hist_data"
purchase_new_data.df= newdata %>% group_by(purchase_date) %>% summarize(purchase_amount_sum=sum(purchase_amount))
purchase_new_data.df$category="new_data"

purchase.df=rbind(purchase_hist_data.df,purchase_new_data.df)

ggplot(purchase.df, aes(purchase_date, purchase_amount_sum,group=category, colour=category)) + geom_line() + ggtitle('First Active Month Count by Date')+xlab('Date') +ylab("count of first active month")+theme_bw()
```

### Feature groups

```{r}
hist_table=melt(apply(histdata[,-c(2,3,7,8,10,11,13,14)],2,tabyl))
hist_table=hist_table[hist_table$variable=="percent",]
colnames(hist_table)=c("value","percent","percent_value","variable_name")

new_table=melt(apply(newdata[,-c(2,3,7,8,10,11,13,14)],2,tabyl))
new_table=new_table[new_table$variable=="percent",]
colnames(new_table)=c("value","percent","percent_value","variable_name")
```

```{r}
hist_table_plot <- ggplot(hist_table, aes(x = variable_name , y = percent_value, fill = value)) + ggtitle("Percent by values by feature groups") + geom_bar(stat = "identity", position = "stack")+ theme(axis.text.x = element_text(angle = 45, hjust = 1)) 
hist_table_plot <- ggplotly(hist_table_plot)
hist_table_plot
```


## New Merchant Dataset
```{r}
new_table_plot <- ggplot(new_table, aes(x = variable_name , y = percent_value, fill = value)) + ggtitle("Percent by values by feature groups") + geom_bar(stat = "identity", position = "stack")+ theme(axis.text.x = element_text(angle = 45, hjust = 1)) 
new_table_plot <- ggplotly(hist_table_plot)
new_table_plot
```


## Merging data 
```{r}

train_new_hist=train %>%
  left_join(histdata, by = "card_id") 

train_new_merchant=train %>%
  left_join(newdata, by = "card_id") 


fn <- funs(mean, .args = list(na.rm = TRUE))

histdata_new <-  train_new_hist %>%
  mutate_if(is.character, funs(factor(.) %>% as.integer)) %>% 
  group_by(card_id ) %>% 
  summarise_all(fn) 


newdata_new  <- train_new_merchant %>%
  left_join(merchants, by = "merchant_id") %>% 
  mutate_if(is.character, funs(factor(.) %>% as.integer)) %>% 
  group_by(card_id ) %>% 
  summarise_all(fn) 

```


# Summary of Histdata by Features 

```{r}
list_whole_hist= histdata_new%>% group_by(feature_1,feature_2,feature_3) %>% summarise_all(fn) %>% data.frame()
list_whole_hist[order(list_whole_hist$purchase_amount,decreasing = T),]
```

```{r}
##order by loyal score
list_whole_hist[order(list_whole_hist$target,decreasing = T),]
```


```{r}
corrplot(cor(list_whole_hist[,-16]),method = "number",type = "upper",number.cex = .6) 
```

```{r}
sort(cor(list_whole_hist[,-c(16)])['purchase_amount',],decreasing = T)
```

```{r}
##order by loyal score
sort(cor(list_whole_hist[,-c(16)])['target',],decreasing = T)
```




# Summary of Newdata by Features 

```{r}
list_whole_new= newdata_new%>% group_by(feature_1,feature_2,feature_3) %>% summarise_all(fn) %>% data.frame()
list_whole_new[order(list_whole_new$purchase_amount,decreasing = T),]
```

```{r}
##order by loyal score
list_whole_new[order(list_whole_new$target,decreasing = T),]
```

```{r}
corrplot(cor(list_whole_new[,-c(7,16)]),method = "color",type = "upper") 
```

```{r}
sort(cor(list_whole_new[,-c(7,16)])['purchase_amount',],decreasing = T)
```

```{r}
##order by loyal score
sort(cor(list_whole_new[,-c(7,16)])['target',],decreasing = T)
```

#EDA Loyal score (hist data)

```{r}
bad_loyal_score_people_hist=histdata_new %>% dplyr::filter(target < -30)
good_loyal_score_people_hist=histdata_new %>% dplyr::filter(target >= -30)
```

```{r}
summary(bad_loyal_score_people_hist)
```

```{r}
summary(good_loyal_score_people_hist)
```


# EDA Loyal score (new merchant data)

```{r}
bad_loyal_score_people_new_merchant= newdata_new %>% dplyr::filter(target < -30)
good_loyal_score_people_new_merchant= newdata_new %>% dplyr::filter(target >= -30)
```



```{r}
summary(bad_loyal_score_people_new_merchant)
```

```{r}
summary(good_loyal_score_people_new_merchant)
```