library(tidyverse)
library(lubridate)
library(corrplot)
library(zoo)
library(plyr)
library(magrittr)
library(ROSE)
library(caTools)
library(ROCR)
library(caret)
library(randomForest)
library(readxl)
library(ggplot2)
install.packages('Amelia')
install.packages('Rcpp')
install.packages('xgboost')
install.packages('ipred')
install.packagee('DMwR')
install.packages("smotefamily")
library(Rcpp)
library(Amelia)
library(corrplot)
library(caTools)
library(carData)
library(car)
library(ineq)
library(class)
library(e1071)
library(DMwR)

setwd("C:/Users/satishp2/Desktop/Current/Loan Default")

#Load Dataset
loan_data_default <- read_excel("loan default_data.xlsx")

#Data Report preparation
dim(loan_data_default)
head(loan_data_default)
str(loan_data_default)

# checking for missing values by Visualizing
colSums(is.na(loan_data_default))

#changing the date format in few columns 

loan_data_default$earliest_cr_line <- as.Date(as.yearmon(loan_data_default$earliest_cr_line, "%b-%y"))
loan_data_default$issue_d <- as.Date(as.yearmon(loan_data_default$issue_d, "%b-%y"))
loan_data_default$last_pymnt_d <- as.Date(as.yearmon(loan_data_default$last_pymnt_d, "%b-%y"))
loan_data_default$last_credit_pull_d <- as.Date(as.yearmon(loan_data_default$last_credit_pull_d, "%b-%y"))

# Replacing missing values
loan_data_default[is.na(loan_data_default)] = 0
colSums(is.na(loan_data_default))

#There are few Date columns where there are NA values, however we are good to go for EDA
#Exlporatory Data Analysis
# Univariate Analysis
Loanamountdist1 <- ggplot(data=loan_data_default, aes(x=loan_data_default$loan_amnt)) + 
  geom_histogram(aes(y=..density..),
                 col='black', 
                 fill='dodgerblue1', 
                 alpha=0.3) +
  geom_density(adjust=3)
print(Loanamountdist1 + theme(plot.title=element_text(face="bold")) + ggtitle('Distribution of the loan amounts'))

annualincomedist1 <-ggplot(data=loan_data_default, aes(x=loan_data_default$annual_inc)) + 
  geom_histogram(aes(y=..density..),
                 col='black', 
                 fill='dodgerblue1', 
                 alpha=0.3) +
  geom_density(adjust=3)
print(annualincomedist1 + theme(plot.title=element_text(face="bold")) + ggtitle('Distribution of the Annual Income'))

interest_rate1 <-ggplot(data=loan_data_default, aes(x=loan_data_default$int_rate)) + 
  geom_histogram(aes(y=..density..),
                 col='black', 
                 fill='dodgerblue1', 
                 alpha=0.3) +
  geom_density(adjust=3)
print(interest_rate1 + theme(plot.title=element_text(face="bold")) + ggtitle('Distribution of the Interest Rate'))

#rest of the analysis I have done in Tableau

#Bi-Vairate Analysis
#High level Corrplot with all variables
numcol <- sapply(loan_data_default,is.numeric)
pearsoncor <- cor(loan_data_default[numcol], use="complete.obs")
corrplot(pearsoncor, "number")

#	funded_amt and funded_amv_inv, don't seem to vary in numbers for significant data set. We can drop one column.
#	term: There are two terms of the Loan, 36 months and 60 months
#	grade: There are 7 grades of Loan, categorized from A to G
#	home_ownership, has 6 categories, ANY, MORTGAGE, NONE OTHER, OWN, RENT. 
#   o	The number of rows for ANY is 1 out of 226786 rows. We can remove it.
#   o	The number of rows for NONE is 36 out of 226786 rows. We can remove it.
#   o	The number of rows for OTHER is 114 out of 226786 rows. We can keep it.
#   o	Rest all we can keep it.
#	verification_status has 3 categories, Verified, Not Verified & Source Verified
#	pymnt_plan, only 6 out of 226786 customers have a payment plan. We can remove their data.
#	desc column has a lot of text and the key information is categorized in purpose column. So we can drop the desc column 
#	total_pymnt and total_pymnt_inv don't seem to vary in numbers for significant data set. We may choose to drop one column.
#	recoveries and collection_recovery_fee have only "0" value. Can be dropped
#	As the data is from the past, next_pymnt_d is not relevant data. Can be dropped
#	Application_type has 2 categories, INDIVIDUAL and JOINT
#	The number of rows for JOINT is 6 out of 226786 rows. We can remove it.
#	out_prncp & out_prncp_inv don't seem to vary in numbers for significant data set. We can drop one column.
# added a column Default with Default=1, Fully Paid =0

# Now let us look at the new dataset

loan_default_imputed= read_excel("loan default_Imputed.xlsx")
dim(loan_default_imputed)
head(loan_default_imputed)

str(loan_default_imputed)
loan_default_imputed$Default <- as.factor(loan_default_imputed$Default)
loan_default_imputed$earliest_cr_line <- as.Date(as.yearmon(loan_default_imputed$earliest_cr_line, "%b-%y"))
loan_default_imputed$issue_d <- as.Date(as.yearmon(loan_default_imputed$issue_d, "%b-%y"))
loan_default_imputed$last_pymnt_d <- as.Date(as.yearmon(loan_default_imputed$last_pymnt_d, "%b-%y"))
loan_default_imputed$last_credit_pull_d <- as.Date(as.yearmon(loan_default_imputed$last_credit_pull_d, "%b-%y"))

str(loan_default_imputed)

#Recoding home ownership and employer length variables

loan_data_clean <- loan_default_imputed %>%
  mutate(emplengthgiven = ifelse(emp_length == "n/a", 1, 0),
         emp_length = ifelse(emp_length == "< 1 year" | emp_length == "n/a", 0, emp_length),
         emp_length = as.numeric(gsub("\\D", "", emp_length)),
         home_ownership = ifelse(home_ownership == "NONE", "OTHER", home_ownership))
str(loan_data_clean)

# Creation of new variables

loan_data_clean1 <- loan_data_clean %>% mutate(credit_age= issue_d - earliest_cr_line)
view(loan_data_clean1)

#rename columns to more code friendly

names(loan_data_clean1)[7]<-"monthly_instl_amount"
names(loan_data_clean1)[13]<-"issue_month"

sum(is.na(loan_data_clean1))
unique (unlist (lapply (loan_data_clean1, function (x) which (is.na (x)))))

loan_data_new <- loan_data_clean1[!(is.na(loan_data_clean1$loan_amnt)) |
                     +                      !(is.na(loan_data_clean1$funded_amnt)) |
                     +                      !(is.na(loan_data_clean1$term)) |
                     +                      !(is.na(loan_data_clean1$int_rate))|
                     +                      !(is.na(loan_data_clean1$monthly_instl_amount)) |
                     +                      !(is.na(loan_data_clean1$grade)) |
                     +                      !(is.na(loan_data_clean1$emp_length))|
                     +                      !(is.na(loan_data_clean1$home_ownership))|
                     +                      !(is.na(loan_data_clean1$annual_inc)) |
                     +                      !(is.na(loan_data_clean1$verification_status)) |
                     +                      !(is.na(loan_data_clean1$issue_month))|
                     +                      !(is.na(loan_data_clean1$pymnt_plan)) |
                     +                      !(is.na(loan_data_clean1$purpose)) |
                     +                      !(is.na(loan_data_clean1$addr_state)) |
                     +                      !(is.na(loan_data_clean1$dti)) |
                     +                      !(is.na(loan_data_clean1$delinq_2yrs))|
                     +                      !(is.na(loan_data_clean1$earliest_cr_line)) |
                     +                      !(is.na(loan_data_clean1$inq_last_6mths)) |
                     +                      !(is.na(loan_data_clean1$mths_since_last_delinq))|
                     +                      !(is.na(loan_data_clean1$open_acc)) |
                     +                      !(is.na(loan_data_clean1$revol_bal)) |
                     +                      !(is.na(loan_data_clean1$revol_util_1))|
                     +                      !(is.na(loan_data_clean1$total_acc)) |
                     +                      !(is.na(loan_data_clean1$out_prncp)) |
                     +                      !(is.na(loan_data_clean1$total_pymnt)) |
                     +                      !(is.na(loan_data_clean1$total_rec_prncp)) |
                     +                      !(is.na(loan_data_clean1$total_rec_int)) |
                     +                      !(is.na(loan_data_clean1$total_rec_late_fee)) |
                     +                      !(is.na(loan_data_clean1$last_pymnt_d)) |
                     +                      !(is.na(loan_data_clean1$last_credit_pull_d)) |
                     +                      !(is.na(loan_data_clean1$application_type)) |
                     +                      !(is.na(loan_data_clean1$Default))
                     +                      !(is.na(loan_data_clean1$emplengthgiven))
                     +                      !(is.na(loan_data_clean1$credit_age))
                     +                      !(is.na(loan_data_clean1$loan_status)),]

nrow(loan_data_new)

loan_data_new$term <- as.factor(loan_data_new$term)
loan_data_new$grade <- as.factor(loan_data_new$grade)
loan_data_new$emp_length <- as.factor(loan_data_new$emp_length)
loan_data_new$home_ownership <- as.factor(loan_data_new$home_ownership)
loan_data_new$verification_status <- as.factor(loan_data_new$verification_status)
loan_data_new$pymnt_plan <- as.factor(loan_data_new$pymnt_plan)
loan_data_new$purpose <- as.factor(loan_data_new$purpose)
loan_data_new$addr_state <- as.factor(loan_data_new$addr_state)
loan_data_new$application_type <- as.factor(loan_data_new$application_type)
loan_data_new$loan_status <- as.factor(loan_data_new$loan_status)
loan_data_new$credit_age <- as.numeric(loan_data_new$credit_age)
str(loan_data_new)

#As we have removed the missing values, transformed and recoded our variables, we are agrregating it into a new dataset, including the computed columns.

Final_loan_data <- loan_data_new %>% 
  select(Default, loan_amnt,emp_length, emplengthgiven, verification_status, 
         home_ownership, annual_inc, purpose, inq_last_6mths, open_acc, revol_util_1, dti, total_acc, delinq_2yrs, loan_status,credit_age)

str(Final_loan_data)
