Phase 2.Rmd

---
title: "Phase II"
author: "Shreya and Francis"
date: "November 11, 2017"
output: html_document
---

Content of the RMarkdown
- Hierarchical clustering analysis
- All Data: Login deposited vs not deposited
- All Data: Messages vs Login - deposited
- All Data: Messages vs Login - not deposited


```{r}
install.packages("readxl")
install.packages("ggplot2")
install.packages("dplyr")
install.packages("tidyr")
```

# Francis
```{r}
install.packages("dplyr")
install.packages("tidyr")
library("dplyr")
library("tidyr")
library("rpart")
```
read data

```{r}
A1<-dplyr::select(ApplicationBase,identity,EverDeposited,Scholarship_app,permanent_country)
A2<--dplyr::select(PersonGRE,identity,Total)
A3<-dplyr::select(PersonSchools,identity,gpa,earned_degree)
A4<-dplyr::select(ApplicationDecisions,identity,decision_name)
A5<-dplyr::full_join(A1,A2,by="identity", all=TRUE)
A6<-dplyr::full_join(A3,A4,by="identity", all=TRUE???
DF<-dplyr::full_join(A6,A5,by="identity", all=TRUE???
A5<-na.omit(A5)
```

```{r}
A5$earned_degree<-ifelse(A5$earned_degree=="Y",1,0)
View(A5)
A5$EverDeposited<-ifelse(A5$EverDeposited=="Y",1,0)
View(A5)
```

```{r}
set.seed(1)
sub<-sample(1:nrow(A5),round(nrow(A5)*2/3))
length(sub) 
data_train<-A5[sub,]
data_test<-A5[-sub,]
dim(data_train)
dim(data_test)
table(data_train$data1) 
table(data_test$data2)
tree.both<-rpart(as.factor(data_test$EverDeposited)~data_train$earned_degree .,data=data_train,method='class',minsplit=20,minbucket=150,cp=0.00017)
summary(tree.both)
tree.both$variable.importance
printcp(tree.both)
plotcp(tree.both,lwd=2) 
```
#plot tree
```{r}
fit <- rpart(e ~ a + b+ c, method="class", data=DATA)
printcp(fit) # display the results 
plotcp(fit) # visualize cross-validation results 
summary(fit) # detailed summary of splits
plot(fit, uniform=TRUE, main="Tree")
text(fit, use.n=TRUE, all=TRUE, cex=.8)
post(fit)

#cluster analysis(ggplot,ggplot2)
```{r}
y1=lm(data1~data2,data=A)
plot(A$data1,B$data2)
abline(y1)
```
```{r}
ggplot(data, aes(x=a, y=b, colour=c)) + geom_point()#color
ggplot(data, aes(x=a, y=b, shape=c)) + geom_point()#shape
```
```{r}
ggplot(data, aes(x=a, y=b))+ stat_bin2d()
ggplot(data, aes(x=a, y=b))+ geom_point(alpha=.1)
ggplot(data, aes(x=a, y=b)+ geom_point(alpha=.01)
ggplot(data, aes(x=a, y=b)+ stat_bin2d()
```
#K means clustering analysis
-> Read data
```{r}
a1<-dplyr::select()
a2<-read_excel("xlsx")
a3<-dplyr::full_join(a1,a3,by="m", all=TRUE)
```
-> clean
```{r}
a3<-na.omit(a3)
a3$ear<-ifelse(a5$ear=="Y",1,0)
View(a5)
a3$Ever<-ifelse(a5$ear=="Y",1,0)
```
-> plots
```{r}
kmeans.cluster <- kmeans(data, centers=3) 
kmeans.cluster$withinss
table(kmeans.cluster$cluster, A$data)  
require(factoextra)
fviz_cluster(kmeans.cluster,          
             data = data,              
             geom = c("point","text"),
             frame.type = "norm") 
```
```{r}
fviz_nbclust(data, 
             FUNcluster = kmeans,
             method = "wss",     
             k.max = 12          # max number of clusters to consider
             ) +
labs(title="Elbow Method for K-Means") +
geom_vline(xintercept = 3,       
           linetype = 2)                  
```
#K-Medoid clustering analysis
```{r}
require(cluster)
kmedoid.cluster <- pam(data, k=3) require(factoextra)
kmedoid.cluster$objective   
table(kmedoid.cluster$clustering, A$data) 
require(factoextra)
fviz_cluster(kmedoid.cluster,      
             data = data,           
             geom = c("point"),    
             frame.type = "norm")                                
```
```{r}
 fviz_nbclust(data, 
             FUNcluster = pam,  
             method = "wss",    
             k.max = 12         
             ) +    
labs(title="Elbow Method for K-Medoid") +
geom_vline(xintercept = 3,      
           linetype = 2)   
```
```{r}
require(factoextra)
fviz_nbclust(data, 
             FUNcluster = kmeans,   # K-Means
             method = "silhouette", # Avg. Silhouette
             k.max = 12             # max number of clusters
             ) +
    
labs(title="Avg.Silhouette Method for K-Means")           
```





# Shreya 
Login and Message Clustering on time to differentiate between deposited and not deposited
All Data
1. Login deposited vs not deposited
2. Messages vs Login - deposited
3. Messages vs Login - not deposited

Year wise analysis based on term start to see if there are differences

Content of the RMarkdown
1. Read data
2. Message data
3. Login data
4. App base data
5. Final Data Visualizations


```{r}
install.packages("readxl")
install.packages("psych")
install.packages("ggplot2")
install.packages("dplyr")
install.packages("tidyr")
```

Read Data
```{r}
library(readxl)
appbase <- read_excel("C:/Users/Shreya/Documents/GITHUB/Capstone/ApplicationBase.xlsx")
```

```{r}
library(readxl)
login <- read_excel("C:/Users/Shreya/Documents/GITHUB/Capstone/PersonLogins.xlsx")
```

```{r}
library(readxl)
message <- read_excel("C:/Users/Shreya/Documents/GITHUB/Capstone/PersonMessages.xlsx")
```

Message Data
```{r}
table(message$opened_or_url)
#table(message$identity, message$opened_or_url)
```

```{r}
message$opened_or_url_1 <- ifelse(message$opened_or_url == "NULL", "0", 1)
message$opened_or_url_1 <-as.numeric(message$opened_or_url_1)
```

```{r}
library(dplyr)
library(tidyr)
message$count <- 1
message2 <- message %>% group_by(identity) %>% summarize(sum(opened_or_url_1))
message3 <- message %>% group_by(identity) %>% summarize(sum(count))
```

```{r}
message2 <- setNames(message2, c("identity", "sum_open_or_url_message"))
message3 <- setNames(message3, c("identity", "sum_num_of_message"))
```

```{r}
library(dplyr)
message_main <- dplyr::full_join(message3, message2, by="identity")
```

Login Data
```{r}
library(dplyr)
library(tidyr)
login$count <- 1
login1 <- login %>% group_by(identity) %>% summarize(sum(count))
login1 <- setNames(login1, c("identity", "sum_login_count"))
```

Application Base Data
```{r}
appbase$sex_1 <- ifelse(appbase$sex == "NULL", "Not_Stated", appbase$sex)
appbase$PrevApply_1 <- ifelse(appbase$PrevApply == "0", "No", "Yes")
appbase$Scholarship_app_1 <- ifelse(appbase$Scholarship_app == "0", "No", "Yes")
```

```{r}
range(appbase$age_at_app)
```

```{r}
appbase$age_at_app_new <- ifelse(appbase$age_at_app <=16, NA, appbase$age_at_app)
appbase$age_at_app_new <- as.numeric(appbase$age_at_app_new)
```

```{r}
appbase$credits_req <- appbase$`Credits Req`
appbase$credits_req_new <- 
  ifelse(appbase$credits_req ==32, "32",
         
  ifelse(appbase$credits_req ==33, "33-36",
  ifelse(appbase$credits_req ==34, "33-36",
  ifelse(appbase$credits_req == 36, "33-36",
         
  ifelse(appbase$credits_req == 38, "38-45",
  ifelse(appbase$credits_req == 40, "38-45",
  ifelse(appbase$credits_req == 41, "38-45",
  ifelse(appbase$credits_req == 42, "38-45",
  ifelse(appbase$credits_req == 43, "38-45",
  ifelse(appbase$credits_req == 44, "38-45",
  ifelse(appbase$credits_req == 45, "38-45",
         
  ifelse(appbase$credits_req == 47, "47-60",
  ifelse(appbase$credits_req == 48, "47-60",                    
  ifelse(appbase$credits_req == 52, "47-60",
  ifelse(appbase$credits_req == 57, "47-60",
  ifelse(appbase$credits_req == 60, "47-60",
         
  ifelse(appbase$credits_req == 62, "62-75",
  ifelse(appbase$credits_req == 69, "62-75",
  ifelse(appbase$credits_req == 74, "62-75",
  ifelse(appbase$credits_req == 75, "62-75",             
         
  ifelse(appbase$credits_req == 86, "86-95",
  ifelse(appbase$credits_req == 90, "86-95",
  ifelse(appbase$credits_req == 95, "86-95",
         appbase$credits_req)))))))))))))))))))))))
table(appbase$credits_req_new)
```

Merging Data
```{r}
Data <- dplyr::full_join(appbase, login1, by="identity", all=TRUE)
Data <- dplyr::full_join(Data, message_main, by="identity", all=TRUE)
```

Login Data more manipulation 
spliting date and time
```{r}
login2 <- login
login2$datelogin <- as.Date(login2$issued)
login2$timelogin <- format(login2$issued,"%H:%M:%S")
Data1 <- dplyr::full_join(login2, Data, by="identity", all=TRUE)
```

Difference in login date and term start date
```{r}
Data1$diff_in_days<- difftime(Data1$datelogin, Data1$`Term Start`, units = c("days"))
Data1$diff_in_days <- as.numeric(Data1$diff_in_days)
hist(Data1$diff_in_days)
```








Final Data Visualizations - Clustering of Logins and Messages over Time
#1
```{r}
library(ggplot2)
ggplot(data=Data1, aes(diff_in_days)) + 
  geom_histogram(breaks=seq(-1200,900, by = 10), 
                 aes(fill=..count..)) +
  scale_fill_gradient("Count", low = "darkorange", high = "mediumblue")+
  labs(title="Difference in days from term start date - All Applicants", x="Difference in days from term start date", y="Number of logins on that day")+
  theme(plot.title = element_text(face = "bold", size=15))
```
#2
```{r}
library(ggplot2)
ggplot(data=Data1, aes(diff_in_days)) + 
  geom_histogram(breaks=seq(-365, 10, by = 10), 
                 aes(fill=..count..)) +
  scale_fill_gradient("Count", low = "darkorange", high = "mediumblue")+
  labs(title="Difference in days from term start date - All Applicants", x="Difference in days from term start date", y="Number of logins on that day")+
  theme(plot.title = element_text(face = "bold", size=15))
```
#3
```{r}
Everdeposit_Yes <- Data1[Data1$EverDeposited == "Y",]
Everdeposit_Yes$diff_in_days<- difftime(Everdeposit_Yes$datelogin, Everdeposit_Yes$`Term Start`, units = c("days"))
library(ggplot2)
ggplot(data=Everdeposit_Yes, aes(diff_in_days)) + 
  geom_histogram(breaks=seq(-365, 10, by = 10), 
                 aes(fill=..count..)) +
  scale_fill_gradient("Count", low = "yellow", high = "forestgreen")+
  labs(title="Difference in days from term start date - Applicants who Deposit", x="Difference in days from term start date", y="Number of logins on that day")+
  theme(plot.title = element_text(face = "bold", size=15))
```
#4
```{r}
Everdeposit_No <- Data1[Data1$EverDeposited == "N",]
Everdeposit_No$diff_in_days<- difftime(Everdeposit_No$datelogin, Everdeposit_No$`Term Start`, units = c("days"))
library(ggplot2)
ggplot(data=Everdeposit_No, aes(diff_in_days)) + 
  geom_histogram(breaks=seq(-365, 10, by = 10), 
                 aes(fill=..count..)) +
  scale_fill_gradient("Count", low = "yellow2", high = "red")+
  labs(title="Difference in days from term start date - Applicant who did not Deposit", x="Difference in days from term start date", y="Number of logins on that day")+
  theme(plot.title = element_text(face = "bold", size=15))
```
Data Manipulation
```{r}
Data5 <- Data1
Data5$diff_in_days<- difftime(Data5$datelogin, Data5$`Term Start`, units = c("days"))
Data5$count <- 1
library(dplyr)
library(tidyr)
Data5 <- Data5 %>% group_by(diff_in_days) %>% summarize(sum(count))
Data5 <- setNames(Data5, c("diff_in_days", "Sum_logins_all"))

#summary(Everdeposit_No)
Everdeposit_No$diff_in_days<- difftime(Everdeposit_No$datelogin, Everdeposit_No$`Term Start`, units = c("days"))
Everdeposit_No$count <- 1
library(dplyr)
library(tidyr)
Everdeposit_No_2 <- Everdeposit_No %>% group_by(diff_in_days) %>% summarize(sum(count))
Everdeposit_No_2 <- setNames(Everdeposit_No_2, c("diff_in_days", "Sum_logins_not_deposit"))

#summary(Everdeposit_Yes)
Everdeposit_Yes$diff_in_days<- difftime(Everdeposit_Yes$datelogin, Everdeposit_Yes$`Term Start`, units = c("days"))
Everdeposit_Yes$count <- 1
library(dplyr)
library(tidyr)
Everdeposit_Yes_2 <- Everdeposit_Yes %>% group_by(diff_in_days) %>% summarize(sum(count))
Everdeposit_Yes_2 <- setNames(Everdeposit_Yes_2, c("diff_in_days", "Sum_logins_did_deposit"))

Data_login <- dplyr::full_join(Data5, Everdeposit_No_2, by="diff_in_days", all=TRUE)
Data_login <- dplyr::full_join(Data_login, Everdeposit_Yes_2, by="diff_in_days", all=TRUE)
```
#5
```{r}
ggplot(Data_login, aes(x = diff_in_days)) + 
  geom_line(aes(y = Sum_logins_all, colour = "All")) + 
  geom_line(aes(y = Sum_logins_not_deposit, colour = "Not Deposit"))+
  geom_line(aes(y = Sum_logins_did_deposit, colour = "Did Deposit"))+
labs(x = 'Difference in days from term start date'
       , y = 'Number of logins on that day'
       , title = "Clustering Logins over Time") +
  theme(plot.title = element_text(size = 20)
        ,axis.title = element_text(size = 15, color = '#555555')
        ,axis.title.y = element_text(size = 15, color = '#555555')
        )  + scale_color_manual(values=c("royalblue1", "mediumseagreen", "lightcoral"))
```
#6
```{r}
ggplot(Data_login, aes(x = diff_in_days)) + 
  geom_line(aes(y = Sum_logins_all, colour = "All")) + 
  geom_line(aes(y = Sum_logins_not_deposit, colour = "Not Deposit"))+
  geom_line(aes(y = Sum_logins_did_deposit, colour = "Did Deposit"))+
labs(x = 'Difference in days from term start date'
       , y = 'Number of logins on that day'
       , title = "Clustering Logins over Time") +
  theme(plot.title = element_text(size = 20)
        ,axis.title = element_text(size = 15, color = '#555555')
        ,axis.title.y = element_text(size = 15, color = '#555555')
        ) + coord_cartesian(ylim = c(-20,1500), xlim = c(-400, 10))  + scale_color_manual(values=c("royalblue1", "mediumseagreen", "lightcoral"))
```
Data Manipulation for further graphs
```{r}
Data_login$Sum_logins_not_deposit_1 <- scale(Data_login$Sum_logins_not_deposit)
Data_login$Sum_logins_all_1 <- scale(Data_login$Sum_logins_all)
Data_login$Sum_logins_did_deposit_1 <- scale(Data_login$Sum_logins_did_deposit)
```
#7
```{r}
ggplot(Data_login, aes(x = diff_in_days)) + 
  geom_line(aes(y = Sum_logins_all_1, colour = "All")) + 
  geom_line(aes(y = Sum_logins_not_deposit_1, colour = "Not Deposit"))+
  geom_line(aes(y = Sum_logins_did_deposit_1, colour = "Did Deposit"))+
labs(x = 'Difference in days from term start date'
       , y = 'Number of logins on that day'
       , title = "Clustering Logins over Time") +
  theme(plot.title = element_text(size = 20)
        ,axis.title = element_text(size = 15, color = '#555555')
        ,axis.title.y = element_text(size = 15, color = '#555555')
        ) + coord_cartesian(xlim = c(-400, 10))  + scale_color_manual(values=c("royalblue1", "mediumseagreen", "lightcoral"))
```
#8
```{r}
ggplot(Data_login, aes(x = diff_in_days)) + 
  geom_line(aes(y = Sum_logins_not_deposit_1, colour = "Not Deposit"))+
  geom_line(aes(y = Sum_logins_did_deposit_1, colour = "Did Deposit"))+
labs(x = 'Difference in days from term start date'
       , y = 'Number of logins on that day'
       , title = "Clustering Logins over Time") +
  theme(plot.title = element_text(size = 20)
        ,axis.title = element_text(size = 15, color = '#555555')
        ,axis.title.y = element_text(size = 15, color = '#555555')
        ) + coord_cartesian(xlim = c(-164, 10)) + scale_color_manual(values=c("mediumseagreen", "lightcoral")) + scale_x_continuous(breaks = scales::pretty_breaks(n = 10))
```

Data Manipulation
```{r}
message5 <- message
message5$delivered_date <- as.Date(message5$delivered)
data_message <- dplyr::full_join(message5, message2, by="identity", all=TRUE)
data_message <- dplyr::full_join(data_message, message3, by="identity", all=TRUE)
data6 <- dplyr::full_join(data_message, appbase, by="identity", all=TRUE)
data6$diff_in_days_msg <- difftime(data6$delivered_date, data6$`Term Start`, units = c("days"))
data6$count <- 1
library(dplyr)
library(tidyr)
data7 <- data6 %>% group_by(diff_in_days_msg) %>% summarize(sum(sum_num_of_message))
data7 <- setNames(data7, c("diff_in_days", "number_msg_sent"))
```


```{r}
data9 <- data8[c(1,2,6)]
data9$number_msg_sent <- scale(data9$number_msg_sent)


data10 <- data8[c(1,2,8)]
data10$number_msg_sent <- scale(data10$number_msg_sent)

```
9
```{r}
ggplot(data9, aes(x = diff_in_days)) + 
  geom_line(aes(y = number_msg_sent, colour = "msg_notdeposit")) + 
  geom_line(aes(y = Sum_logins_not_deposit_1, colour = "login_notdeposit"))+
labs(x = 'Difference in days from term start date'
       , y = 'Number of logins on that day'
       , title = "Messages delivered from term start") +
  theme(plot.title = element_text(size = 20)
        ,axis.title = element_text(size = 15, color = '#555555')
        ,axis.title.y = element_text(size = 15, color = '#555555')
        ) + coord_cartesian(xlim = c(-200, 10))
```

10
```{r}
ggplot(data10, aes(x = diff_in_days)) + 
  geom_line(aes(y = number_msg_sent, colour = "msg_diddeposit")) + 
  geom_line(aes(y = Sum_logins_not_deposit_1, colour = "login_diddeposit"))+
labs(x = 'Difference in days from term start date'
       , y = 'Number of logins on that day'
       , title = "Messages delivered from term start") +
  theme(plot.title = element_text(size = 20)
        ,axis.title = element_text(size = 15, color = '#555555')
        ,axis.title.y = element_text(size = 15, color = '#555555')
        ) + coord_cartesian(xlim = c(-200, 10))
```