---
title: "EDA WSDM"
author: "Bukun"
output:
  html_document:
    number_sections: true
    toc: true
    fig_width: 10
    code_folding: hide
    fig_height: 4.5
    theme: cosmo
    highlight: tango
---


#Introduction

We explore the data as outlined in the Table Of Contents.

This is a Work in Progress.                 

**Please Upvote if you like it.**

#Read the Data

Read the Members , Train , Transactions and **20 Million Rows** of Users Logs data.         
```{r, include=FALSE}
library(tidyverse)
library(data.table)
library(lubridate)
library(DT)



rm(list=ls())

fillColor = "#FFA07A"
fillColor2 = "#F1C40F"

members = read_csv("../input/members.csv")
train = read_csv("../input/train.csv")
transactions = read_csv("../input/transactions.csv", 
                        col_types = cols(
                          msno = col_character(),
                          payment_method_id = col_integer(),
                          payment_plan_days = col_integer(),
                          plan_list_price = col_integer(),
                          actual_amount_paid = col_integer(),
                          is_auto_renew = col_integer(),
                          transaction_date = col_character(),
                          membership_expire_date = col_character(),
                          is_cancel = col_integer()
                        ) )


userLogs = fread("../input/user_logs.csv",nrows=20e6)

```


#Peek into members data

```{r, message=FALSE, warning = FALSE}

datatable(head(members), style="bootstrap", class="table-condensed", options = list(dom = 'tp',scrollX = TRUE))

```

#Peek into train data

```{r, message=FALSE, warning = FALSE}

datatable(head(train), style="bootstrap", class="table-condensed", options = list(dom = 'tp',scrollX = TRUE))

```

#Peek into transactions data

```{r, message=FALSE, warning = FALSE}

datatable(head(transactions), style="bootstrap", class="table-condensed", options = list(dom = 'tp',scrollX = TRUE))

```

#Peek into users Logs data

```{r, message=FALSE, warning = FALSE}

datatable(head(userLogs), style="bootstrap", class="table-condensed", options = list(dom = 'tp',scrollX = TRUE))

```




#Get the Latest Transactions Data

Get the latest Transaction Data for each user.              


```{r, message=FALSE,warning= FALSE}

transactions = transactions %>%
  mutate(transaction_date2 =  ymd(transaction_date))

transactions_date = transactions %>%
  group_by(msno) %>%
  summarise(MaxDate = max(transaction_date2,na.rm = TRUE))

transactions2 = inner_join(transactions,transactions_date,by = c("transaction_date2"="MaxDate",                                                                 "msno" = "msno"))


rm(transactions)
rm(transactions_date)


train_transactions2 = left_join(train,transactions2)

```



#Actual Amount Paid Analysis{.tabset}


##Actual Amount Paid Analysis (Median)

There is **No Difference** in the median of the Amount Paid for the Churned and the Non Churned Transactions.

```{r, message= FALSE,warning=FALSE}

train_transactions2$is_churn = as.factor(train_transactions2$is_churn)

train_transactions2 %>%
  group_by(is_churn) %>%
  summarise(PaymentMed = median(actual_amount_paid)) %>%
  
  ggplot(aes(x = is_churn,y = PaymentMed,fill =is_churn)) +
  scale_x_discrete("is_churn") +
  geom_bar(stat='identity') +
  
  labs(x = 'isChurn', y = 'Actual Amount Paid Median', 
       title = 'isChurn and Median of Actual Amount Paid ') +
  theme_bw()

```


##Actual Amount Paid Analysis (Mean)

We observe that there is a  **Significant Difference** in the mean of the Amount Paid for the Churned and the Non Churned Transactions.


```{r, message= FALSE,warning=FALSE}

train_transactions2 %>%
  group_by(is_churn) %>%
  summarise(PaymentMean = mean(actual_amount_paid)) %>%
  
  ggplot(aes(x = is_churn,y = PaymentMean,fill =is_churn)) +
  scale_x_discrete("is_churn") +
  geom_bar(stat='identity') +
  
  labs(x = 'isChurn', y = 'Actual Amount Paid Mean', 
       title = 'isChurn and Mean of Actual Amount Paid ') +
  theme_bw()

```


#Payment Method{.tabset}          


**Observations**

* Payment Method **41** occurs highest among **Non Churned Transactions**                

* Payment Method **38** occurs highest among **Churned Transactions**   


##Payment method for Non Churned Transactions

```{r ,message= FALSE,warning=FALSE}

train_transactions2$payment_method_id = as.factor(train_transactions2$payment_method_id)

train_transactions2 %>%
  group_by(payment_method_id,is_churn) %>%
  filter(is_churn == 0) %>%
  summarise(CountPaymentMethod = n()) %>%
  arrange(desc(CountPaymentMethod)) %>%
  ungroup() %>%
  mutate(payment_method_id =  reorder(payment_method_id,CountPaymentMethod)) %>%
  head(15) %>%
  
  ggplot(aes(x = payment_method_id,y = CountPaymentMethod,fill =payment_method_id)) +
  geom_bar(stat='identity') +
  geom_text(aes(x = payment_method_id, y = 1, label = paste0("(",CountPaymentMethod,")",sep="")),
            hjust=0, vjust=.5, size = 4, colour = 'black',
            fontface = 'bold') +
  labs(x = 'Payment Method', y = 'Count', 
       title = 'Payment Method Distribution') +
  coord_flip() + 
  theme_bw()
  

```


##Payment method for Churned Transactions

```{r ,message= FALSE,warning=FALSE}

train_transactions2$payment_method_id = as.factor(train_transactions2$payment_method_id)

train_transactions2 %>%
  group_by(payment_method_id,is_churn) %>%
  filter(is_churn == 1) %>%
  summarise(CountPaymentMethod = n()) %>%
  arrange(desc(CountPaymentMethod)) %>%
  ungroup() %>%
  mutate(payment_method_id =  reorder(payment_method_id,CountPaymentMethod)) %>%
  head(15) %>%
  
  ggplot(aes(x = payment_method_id,y = CountPaymentMethod,fill =payment_method_id)) +
  geom_bar(stat='identity') +
  geom_text(aes(x = payment_method_id, y = 1, label = paste0("(",CountPaymentMethod,")",sep="")),
            hjust=0, vjust=.5, size = 4, colour = 'black',
            fontface = 'bold') +
  labs(x = 'Payment Method', y = 'Count', 
       title = 'Payment Method Distribution') +
  coord_flip() + 
  theme_bw()

```
                          
                          


   


#Plan List Price   

We examine the distribution of the Plan List Price for the transactions 

```{r , message=FALSE, warning=FALSE}

breaks = seq(0,300,25)

train_transactions2 %>%
  
  ggplot(aes(plan_list_price)) +
  geom_histogram(binwidth = 25,fill = c("red")) +
  facet_wrap(~ is_churn) +
  scale_x_continuous(limits = c(75, 200),breaks=breaks ) +
  labs(x = 'plan_list_price', y = 'Count', 
       title = 'Plan List Price and Churn') +
  theme_bw()



```



#Membership Expire Date{.tabset}


**Observations**

* Month of **Feb** for the membership_expire_date has the *Most Churned Transactions*                          

* Month of **March* for the membership_expire_date has the *Most Churned Transactions*

* **Thursday** has the *Most Churned Transactions*  


```{r message=FALSE,warning=FALSE}

train_transactions2$membership_expire_date_year = year(ymd(train_transactions2$membership_expire_date))

train_transactions2$membership_expire_date_month = month(ymd(train_transactions2$membership_expire_date))

train_transactions2$membership_expire_date_dayOfWeek = wday(ymd(train_transactions2$membership_expire_date),label = TRUE)

```


## Year

```{r message=FALSE,warning=FALSE}

train_transactions2 %>%
  group_by(membership_expire_date_year,is_churn) %>%
  filter(is_churn == 1) %>%
  summarise(Count = n()) %>%
  arrange(desc(Count)) %>%
  ungroup() %>%
  mutate(membership_expire_date_year =  reorder(membership_expire_date_year,Count)) %>%
  
  
  ggplot(aes(x = membership_expire_date_year,y = Count,fill =membership_expire_date_year)) +
  geom_bar(stat='identity') +
  geom_text(aes(x = membership_expire_date_year, y = 1, label = paste0("(",Count,")",sep="")),
            hjust=0, vjust=.5, size = 4, colour = 'black',
            fontface = 'bold') +
  labs(x = 'membership_expire_date_year', y = 'Count', 
       title = 'membership_expire_date_year Distribution for Churned Transactions') +
  coord_flip() + 
  theme_bw()

train_transactions2 %>%
  group_by(membership_expire_date_year,is_churn) %>%
  filter(is_churn == 0) %>%
  summarise(Count = n()) %>%
  arrange(desc(Count)) %>%
  ungroup() %>%
  mutate(membership_expire_date_year =  reorder(membership_expire_date_year,Count)) %>%
  
  
  ggplot(aes(x = membership_expire_date_year,y = Count,fill =membership_expire_date_year)) +
  geom_bar(stat='identity') +
  geom_text(aes(x = membership_expire_date_year, y = 1, label = paste0("(",Count,")",sep="")),
            hjust=0, vjust=.5, size = 4, colour = 'black',
            fontface = 'bold') +
  labs(x = 'membership_expire_date_year', y = 'Count', 
       title = 'membership_expire_date_year Distribution for Non Churned Transactions') +
  coord_flip() + 
  theme_bw()

```


## Month

```{r message=FALSE,warning=FALSE}

train_transactions2 %>%
  group_by(membership_expire_date_month,is_churn) %>%
  filter(is_churn == 1) %>%
  summarise(Count = n()) %>%
  arrange(desc(Count)) %>%
  ungroup() %>%
  mutate(membership_expire_date_month =  reorder(membership_expire_date_month,Count)) %>%
  
  
  ggplot(aes(x = membership_expire_date_month,y = Count,fill =membership_expire_date_month)) +
  geom_bar(stat='identity') +
  geom_text(aes(x = membership_expire_date_month, y = 1, label = paste0("(",Count,")",sep="")),
            hjust=0, vjust=.5, size = 4, colour = 'black',
            fontface = 'bold') +
  labs(x = 'membership_expire_date_month', y = 'Count', 
       title = 'membership_expire_date_month Distribution for Churned Transactions') +
  coord_flip() + 
  theme_bw()

train_transactions2 %>%
  group_by(membership_expire_date_month,is_churn) %>%
  filter(is_churn == 0) %>%
  summarise(Count = n()) %>%
  arrange(desc(Count)) %>%
  ungroup() %>%
  mutate(membership_expire_date_month =  reorder(membership_expire_date_month,Count)) %>%
  
  
  ggplot(aes(x = membership_expire_date_month,y = Count,fill =membership_expire_date_month)) +
  geom_bar(stat='identity') +
  geom_text(aes(x = membership_expire_date_month, y = 1, label = paste0("(",Count,")",sep="")),
            hjust=0, vjust=.5, size = 4, colour = 'black',
            fontface = 'bold') +
  labs(x = 'membership_expire_date_month', y = 'Count', 
       title = 'membership_expire_date_month Distribution for Non Churned Transactions') +
  coord_flip() + 
  theme_bw()

```


## Day

```{r message=FALSE,warning=FALSE}

train_transactions2$membership_expire_date_dayOfWeek = 
  as.factor(train_transactions2$membership_expire_date_dayOfWeek)

train_transactions2 %>%
  group_by(membership_expire_date_dayOfWeek,is_churn) %>%
  filter(is_churn == 1) %>%
  summarise(Count = n()) %>%
  arrange(desc(Count)) %>%
  ungroup() %>%
  mutate(membership_expire_date_dayOfWeek =  reorder(membership_expire_date_dayOfWeek,Count)) %>%
  
  
  ggplot(aes(x = membership_expire_date_dayOfWeek,y = Count,fill =membership_expire_date_dayOfWeek)) +
  geom_bar(stat='identity') +
  geom_text(aes(x = membership_expire_date_dayOfWeek, y = 1, label = paste0("(",Count,")",sep="")),
            hjust=0, vjust=.5, size = 4, colour = 'black',
            fontface = 'bold') +
  labs(x = 'membership_expire_date_dayOfWeek', y = 'Count', 
       title = 'membership_expire_date_dayOfWeek Distribution for Churned Transactions') +
  coord_flip() + 
  theme_bw()

train_transactions2 %>%
  group_by(membership_expire_date_dayOfWeek,is_churn) %>%
  filter(is_churn == 0) %>%
  summarise(Count = n()) %>%
  arrange(desc(Count)) %>%
  ungroup() %>%
  mutate(membership_expire_date_dayOfWeek =  reorder(membership_expire_date_dayOfWeek,Count)) %>%
  
  
  ggplot(aes(x = membership_expire_date_dayOfWeek,y = Count,fill =membership_expire_date_dayOfWeek)) +
  geom_bar(stat='identity') +
  geom_text(aes(x = membership_expire_date_dayOfWeek, y = 1, label = paste0("(",Count,")",sep="")),
            hjust=0, vjust=.5, size = 4, colour = 'black',
            fontface = 'bold') +
  labs(x = 'membership_expire_date_dayOfWeek', y = 'Count', 
       title = 'membership_expire_date_dayOfWeek Distribution for Non Churned Transactions') +
  coord_flip() + 
  theme_bw()

```

                  


#City

We examine the City of the various members and create Two Bar plots based on the Churn and the Non Churn Members.          


```{r message = FALSE, warning = FALSE}

train_members = left_join(train, members)

train_members %>%
  group_by(city,is_churn) %>%
  filter(!is.na(city)) %>%
  summarise(Count = n()) %>%
  arrange(desc(Count)) %>%
  ungroup() %>%
  mutate(city =  reorder(city,Count)) %>%
 
  ggplot(aes(x = city,y = Count,fill =city)) +
  geom_bar(stat='identity') +
  facet_wrap(~ is_churn) +
  geom_text(aes(x = city, y = 1, label = paste0("(",Count,")",sep="")),
            hjust=0, vjust=.5, size = 4, colour = 'black',
            fontface = 'bold') +
  labs(x = 'city', y = 'Count', 
       title = 'City Distribution for Churned and Non Churned Members') +
  coord_flip() + 
  theme_bw()


```

#Age

```{r message =FALSE, warning = FALSE}

train_members$is_churn = as.factor(train_members$is_churn)

breaks = seq(0,100,5)

train_members %>%
  ggplot(aes(bd,fill = is_churn)) +
  geom_histogram(binwidth = 5) +
  facet_wrap(~ is_churn) +
  scale_x_continuous(limits = c(10, 70),breaks=breaks ) +
  labs(x = 'age', y = 'Count', 
       title = 'age and Churn') +
  theme_bw()


```


**Observations**

* 25 is the most popular age of subscriptions                 

#Gender

```{r message = FALSE, warning = FALSE}


train_members %>%
  group_by(gender,is_churn) %>%
  filter(!is.na(gender)) %>%
  summarise(Count = n()) %>%
  arrange(desc(Count)) %>%
  ungroup() %>%
  mutate(gender =  reorder(gender,Count)) %>%
 
  ggplot(aes(x = gender,y = Count,fill =gender)) +
  geom_bar(stat='identity') +
  facet_wrap(~ is_churn) +
  geom_text(aes(x = gender, y = 1, label = paste0("(",Count,")",sep="")),
            hjust=0, vjust=.5, size = 4, colour = 'black',
            fontface = 'bold') +
  labs(x = 'gender', y = 'Count', 
       title = 'Gender Distribution for Churned and Non Churned Members') +
  coord_flip() + 
  theme_bw()


```

#User Logs Total Seconds

```{r , message=FALSE,warning=FALSE}


userLogs = as.data.frame(userLogs)

train_userlogs = left_join(train,userLogs)

```

##Calculate Median of Total Secs

```{r}
 train_userlogs_total_secs = train_userlogs %>%
  filter(!is.na(total_secs)) %>%
  filter(total_secs > 0) %>%
  group_by(msno,is_churn) %>%
  summarise(NumMed = median(total_secs))
```

##Summary Statistics of the Total Secs Median for UnChurned members

```{r}
 train_userlogs_total_secs %>%
  filter(is_churn == 0) %>%
  summary(NumMed)
```

##Summary Statistics of the Total Secs Median for Churned members

```{r}
 train_userlogs_total_secs %>%
  filter(is_churn == 1) %>%
  summary(NumMed)
```

##Boxplot of Total Secs Median

```{r}
 train_userlogs_total_secs %>%
   ungroup() %>%
  filter(NumMed < 10e3) %>%
   mutate(is_churn = as.factor(is_churn)) %>%
  ggplot(aes(x= is_churn,y = NumMed,fill = is_churn)) +
  geom_boxplot()+ theme_bw() 

```

**Observations**

* The Median of the Total Secs Median for the UnChurned is 4026.69             
* The Median of the Total Secs Median for the Churned is 4200.77            
* The Mean of the Total Secs Median for the UnChurned is 5687.00                  
* The Mean of the Total Secs Median for the Churned is 6975.69            

**Hypothesis**

The **Churned** members have listened **More** than the **UnChurned** Members

#Unique Songs

##Calculate Median of Unique Songs

```{r}
 train_userlogs_num_unq = train_userlogs %>%
  filter(!is.na(num_unq)) %>%
  filter(num_unq > 0) %>%
  group_by(msno,is_churn) %>%
  summarise(NumMed = median(num_unq))
```


##Summary Statistics of the Unique Songs Median for UnChurned members

```{r}
 train_userlogs_num_unq %>%
  filter(is_churn == 0) %>%
  summary(NumMed)
```

##Summary Statistics of the Unique Songs Median for Churned members

```{r}
 train_userlogs_num_unq %>%
  filter(is_churn == 1) %>%
  summary(NumMed)
```

##Boxplot of Unique Songs Median

```{r}
 train_userlogs_num_unq %>%
   ungroup() %>%
  filter(NumMed < 50) %>%
   mutate(is_churn = as.factor(is_churn)) %>%
  ggplot(aes(x= is_churn,y = NumMed,fill = is_churn)) +
  geom_boxplot()+ theme_bw() 

```