Lending club:

For this project, we wish to present and explore the data provided by Lending Club.
Here is the website: Lending Club

Lending Club (LC) is a peer-to-peer online lending platform. It is the world’s largest marketplace connecting borrowers and investors, where consumers and small business owners lower the cost of their credit and enjoy a better experience than traditional bank lending, and investors earn attractive risk-adjusted returns.

How it works:

  1. Customers interested in a loan complete a simple application at LendingClub.com
  2. LC leverage online data and technology to quickly assess risk, determine a credit rating and assign appropriate interest rates.
  3. Qualified applicants receive offers in just minutes and can evaluate loan options with no impact to their credit score
  4. Investors ranging from individuals to institutions select loans in which to invest and can earn monthly returns

The entire process is online, using technology to lower the cost of credit and pass the savings back in the form of lower rates for borrowers and solid returns for investors.

Here is the link to more details about LC.

Goal of the project:

We will present and explore the data provided by LC at this address. This data was made available to us after the creation of an investor account.

The data consists in 4 files updated every quarter on the same day as the quarterly results of the company are released. They contain information on almost all the loans issued by LC. The only loans missing from these files are the few loans where LC was not authorized to release publicly the details of the transactions.

The information available for each loan consists of all the details of the loans at the time of their issuance as well as more information relative to the latest status of loan such as how much principal has been paid so far, how much interest, if the loan was fully paid or defaulted, or if the borrower is late on payments etc.

Downloading the data:

We downloaded the 4 csv files and loaded them in our environement. We then combined them into one table that we called LC and saved it under LC.Rdata:

setwd('/Users/jfdarre/Documents/NYCDS/Project1')
LCA = read.csv("LC2007-2012.csv", stringsAsFactors = FALSE, header = T, skip = 1)
LCB = read.csv("LC2012-2013.csv", stringsAsFactors = FALSE, header = T, skip = 1)
LCC = read.csv("LC2013-2014.csv", stringsAsFactors = FALSE, header = T, skip = 1)
LCD = read.csv("LC2014-2015.csv", stringsAsFactors = FALSE, header = T, skip = 1)
LCE = rbind(LCA, LCB, LCC, LCD)
LC = tbl_df(LCE)
save(LC, file = "LC.RData")
rm(list=ls())

Preparing for the analysis:

The next step is to load all the librabies needed for our exploration of the data:

library(ggplot2)
library(dplyr)
library(reshape)
library(ggthemes)
library(RColorBrewer)
library(maps)
library(lattice)
library(plotly)

Setting up some useful variables and loading loan data:

rm(list=ls())
setwd('/Users/jfdarre/Documents/NYCDS/Project1')
# Date of the latest update of the reports:
report_date = 201506

# Loading the data and storing the original data into LC0:
load("LC.RData")
LC0 = LC
Here is a full description of every fields:
LoanStatNew Description
zip_code The first 3 numbers of the zip code provided by the borrower in the loan application.
addr_state The state provided by the borrower in the loan application
annual_inc The annual income provided by the borrower during registration.
collection_recovery_fee post charge off collection fee
collections_12_mths_ex_med Number of collections in 12 months excluding medical collections
delinq_2yrs The number of 30+ days past-due incidences of delinquency in the borrower’s credit file for the past 2 years
desc Loan description provided by the borrower
dti A ratio calculated using the borrower’s total monthly debt payments on the total debt obligations, excluding mortgage and the requested LC loan, divided by the borrower’s self-reported monthly income.
earliest_cr_line The month the borrower’s earliest reported credit line was opened
emp_length Employment length in years. Possible values are between 0 and 10 where 0 means less than one year and 10 means ten or more years.
emp_title The job title supplied by the Borrower when applying for the loan.
fico_range_high The upper boundary of range the borrower’s FICO belongs to.
fico_range_low The lower boundary of range the borrower’s FICO belongs to.
funded_amnt The total amount committed to that loan at that point in time.
funded_amnt_inv The total amount committed by investors for that loan at that point in time.
grade LC assigned loan grade
home_ownership The home ownership status provided by the borrower during registration. Our values are: RENT, OWN, MORTGAGE, OTHER.
id A unique LC assigned ID for the loan listing.
initial_list_status The initial listing status of the loan. Possible values are – W, F
inq_last_6mths The number of inquiries by creditors during the past 6 months.
installment The monthly payment owed by the borrower if the loan originates.
int_rate Interest Rate on the loan
is_inc_v Indicates if income was verified by LC, not verified, or if the income source was verified
issue_d The month which the loan was funded
last_credit_pull_d The most recent month LC pulled credit for this loan
last_fico_range_high The last upper boundary of range the borrower’s FICO belongs to pulled.
last_fico_range_low The last lower boundary of range the borrower’s FICO belongs to pulled.
last_pymnt_amnt Last total payment amount received
last_pymnt_d Last month payment was received
loan_amnt The listed amount of the loan applied for by the borrower. If at some point in time, the credit department reduces the loan amount, then it will be reflected in this value.
loan_status Current status of the loan
member_id A unique LC assigned Id for the borrower member.
mths_since_last_delinq The number of months since the borrower’s last delinquency.
mths_since_last_major_derog Months since most recent 90-day or worse rating
mths_since_last_record The number of months since the last public record.
next_pymnt_d Next scheduled payment date
open_acc The number of open credit lines in the borrower’s credit file.
out_prncp Remaining outstanding principal for total amount funded
out_prncp_inv Remaining outstanding principal for portion of total amount funded by investors
policy_code Publicly available policy_code=1, new products not publicly available policy_code=2
pub_rec Number of derogatory public records
purpose A category provided by the borrower for the loan request.
pymnt_plan Indicates if a payment plan has been put in place for the loan
recoveries post charge off gross recovery
revol_bal Total credit revolving balance
revol_util Revolving line utilization rate, or the amount of credit the borrower is using relative to all available revolving credit.
sub_grade LC assigned loan subgrade
term The number of payments on the loan. Values are in months and can be either 36 or 60.
title The loan title provided by the borrower
total_acc The total number of credit lines currently in the borrower’s credit file
total_pymnt Payments received to date for total amount funded
total_pymnt_inv Payments received to date for portion of total amount funded by investors
total_rec_int Interest received to date
total_rec_late_fee Late fees received to date
total_rec_prncp Principal received to date
url URL for the LC page with listing data.

Now let’s have a look at some examples to understand the fields:

# illustrative example
t(filter(LC0, id == 36805548))
##                             [,1]                                                                   
## id                          "36805548"                                                             
## member_id                   "39558264"                                                             
## loan_amnt                   "10400"                                                                
## funded_amnt                 "10400"                                                                
## funded_amnt_inv             "10400"                                                                
## term                        " 36 months"                                                           
## int_rate                    "  6.99%"                                                              
## installment                 "321.08"                                                               
## grade                       "A"                                                                    
## sub_grade                   "A3"                                                                   
## emp_title                   "Truck Driver Delivery Personel"                                       
## emp_length                  "8 years"                                                              
## home_ownership              "MORTGAGE"                                                             
## annual_inc                  "58000"                                                                
## verification_status         "not verified"                                                         
## issue_d                     "Dec-2014"                                                             
## loan_status                 "Current"                                                              
## pymnt_plan                  "n"                                                                    
## url                         "https://www.lendingclub.com/browse/loanDetail.action?loan_id=36805548"
## desc                        ""                                                                     
## purpose                     "credit_card"                                                          
## title                       "Credit card refinancing"                                              
## zip_code                    "937xx"                                                                
## addr_state                  "CA"                                                                   
## dti                         "14.92"                                                                
## delinq_2yrs                 "0"                                                                    
## earliest_cr_line            "Sep-1989"                                                             
## fico_range_low              "710"                                                                  
## fico_range_high             "714"                                                                  
## inq_last_6mths              "2"                                                                    
## mths_since_last_delinq      "42"                                                                   
## mths_since_last_record      NA                                                                     
## open_acc                    "17"                                                                   
## pub_rec                     "0"                                                                    
## revol_bal                   "6133"                                                                 
## revol_util                  "31.6%"                                                                
## total_acc                   "36"                                                                   
## initial_list_status         "w"                                                                    
## out_prncp                   "8544.32"                                                              
## out_prncp_inv               "8544.32"                                                              
## total_pymnt                 "2237.46"                                                              
## total_pymnt_inv             "2237.46"                                                              
## total_rec_prncp             "1855.68"                                                              
## total_rec_int               "381.78"                                                               
## total_rec_late_fee          "0"                                                                    
## recoveries                  "0"                                                                    
## collection_recovery_fee     "0"                                                                    
## last_pymnt_d                "Aug-2015"                                                             
## last_pymnt_amnt             "321.08"                                                               
## next_pymnt_d                "Sep-2015"                                                             
## last_credit_pull_d          "Aug-2015"                                                             
## last_fico_range_high        "679"                                                                  
## last_fico_range_low         "675"                                                                  
## collections_12_mths_ex_med  "0"                                                                    
## mths_since_last_major_derog "59"                                                                   
## policy_code                 "1"
# looking for loans that were delinquent at some point in time, and looking at an example
filter(LC, recoveries != 0)
## Source: local data frame [18,006 x 56]
## 
##         id member_id loan_amnt funded_amnt funded_amnt_inv       term
##      (chr)     (int)     (int)       (int)           (dbl)      (chr)
## 1  1077430   1314167      2500        2500            2500  60 months
## 2  1071795   1306957      5600        5600            5600  60 months
## 3  1071570   1306721      5375        5375            5350  60 months
## 4  1064687   1298717      9000        9000            9000  36 months
## 5  1069057   1303503     10000       10000           10000  36 months
## 6  1069559   1304634      6000        6000            6000  36 months
## 7  1069657   1304764      5000        5000            5000  60 months
## 8  1069465   1304521      5000        5000            5000  36 months
## 9  1069248   1304123     15000       15000           15000  36 months
## 10 1069243   1304116     12000       12000           12000  36 months
## ..     ...       ...       ...         ...             ...        ...
## Variables not shown: int_rate (chr), installment (dbl), grade (chr),
##   sub_grade (chr), emp_title (chr), emp_length (chr), home_ownership
##   (chr), annual_inc (dbl), verification_status (chr), issue_d (chr),
##   loan_status (chr), pymnt_plan (chr), url (chr), desc (chr), purpose
##   (chr), title (chr), zip_code (chr), addr_state (chr), dti (dbl),
##   delinq_2yrs (int), earliest_cr_line (chr), fico_range_low (int),
##   fico_range_high (int), inq_last_6mths (int), mths_since_last_delinq
##   (int), mths_since_last_record (int), open_acc (int), pub_rec (int),
##   revol_bal (int), revol_util (chr), total_acc (int), initial_list_status
##   (chr), out_prncp (dbl), out_prncp_inv (dbl), total_pymnt (dbl),
##   total_pymnt_inv (dbl), total_rec_prncp (dbl), total_rec_int (dbl),
##   total_rec_late_fee (dbl), recoveries (dbl), collection_recovery_fee
##   (dbl), last_pymnt_d (chr), last_pymnt_amnt (dbl), next_pymnt_d (chr),
##   last_credit_pull_d (chr), last_fico_range_high (int),
##   last_fico_range_low (int), collections_12_mths_ex_med (int),
##   mths_since_last_major_derog (int), policy_code (int)
# looking for loans that were delinquent at some point in time, and looking at an example
t(filter(LC, id == 1069559))
##                             [,1]                                                                  
## id                          "1069559"                                                             
## member_id                   "1304634"                                                             
## loan_amnt                   "6000"                                                                
## funded_amnt                 "6000"                                                                
## funded_amnt_inv             "6000"                                                                
## term                        " 36 months"                                                          
## int_rate                    " 11.71%"                                                             
## installment                 "198.46"                                                              
## grade                       "B"                                                                   
## sub_grade                   "B3"                                                                  
## emp_title                   "bmg-educational"                                                     
## emp_length                  "1 year"                                                              
## home_ownership              "RENT"                                                                
## annual_inc                  "76000"                                                               
## verification_status         "not verified"                                                        
## issue_d                     "Dec-2011"                                                            
## loan_status                 "Charged Off"                                                         
## pymnt_plan                  "n"                                                                   
## url                         "https://www.lendingclub.com/browse/loanDetail.action?loan_id=1069559"
## desc                        ""                                                                    
## purpose                     "major_purchase"                                                      
## title                       "cash"                                                                
## zip_code                    "900xx"                                                               
## addr_state                  "CA"                                                                  
## dti                         "2.4"                                                                 
## delinq_2yrs                 "0"                                                                   
## earliest_cr_line            "Jun-2001"                                                            
## fico_range_low              "690"                                                                 
## fico_range_high             "694"                                                                 
## inq_last_6mths              "1"                                                                   
## mths_since_last_delinq      NA                                                                    
## mths_since_last_record      NA                                                                    
## open_acc                    "7"                                                                   
## pub_rec                     "0"                                                                   
## revol_bal                   "5963"                                                                
## revol_util                  "29.7%"                                                               
## total_acc                   "7"                                                                   
## initial_list_status         "f"                                                                   
## out_prncp                   "0"                                                                   
## out_prncp_inv               "0"                                                                   
## total_pymnt                 "2050.14"                                                             
## total_pymnt_inv             "2050.14"                                                             
## total_rec_prncp             "1305.58"                                                             
## total_rec_int               "475.25"                                                              
## total_rec_late_fee          "0"                                                                   
## recoveries                  "269.31"                                                              
## collection_recovery_fee     "2.57"                                                                
## last_pymnt_d                "Oct-2012"                                                            
## last_pymnt_amnt             "198.46"                                                              
## next_pymnt_d                ""                                                                    
## last_credit_pull_d          "Mar-2013"                                                            
## last_fico_range_high        "499"                                                                 
## last_fico_range_low         "0"                                                                   
## collections_12_mths_ex_med  "0"                                                                   
## mths_since_last_major_derog NA                                                                    
## policy_code                 "1"

Useful functions:

To facilitate our discovery and manipulation of the data we created some useful functions that will be used repetitively:

# used to create FICO bins to group fico scores
bin_name = function(x) {
  low = 490 + (x - 1) * 30
  high = low + 30
  paste(low, high, sep = "-")
}

# used to generate summaries grouping by ... and showing how much loan amount is in each status
sumPerSatus = function(x, ...){
  x %>% group_by(...) %>%
    summarize(., charged   = round(sum(loan_status_new == "Charged Off") / n() * 100, 2),
                 net_EL    = round(sum((1 - total_rec_prncp / funded_amnt) * 100) / n(), 2),
                 avg_fico  = round(mean(fico_range_high)),
                 avg_grade = sub_grade_vec[mean(LC_score)])
}

# used to generate summaries grouping by ... and showing how much loan amount and how many loans were issued by groups
sumAmnts = function(x, ...) {
  x %>% group_by(., ...) %>%
    summarise(., total_issued = prettyNum(round(sum(loan_amnt/1)),big.mark = ","),
              n = prettyNum(round(n()),big.mark = ","))
}

# used to generate summaries grouping by ... and showing how much loan amount, principal out and recieved principle
# remove the "pretty numbers" to enable the use of the results in formula
sumAmnt = function(x, ...) {
  x %>% group_by(., ...) %>%
    summarise(., total_issued = round(sum(loan_amnt/1e6),1),
              n = round(n()))
}

# used to generate summaries grouping by ... and showing usefull statics about each group
sumStats = function(x, ...) {
  x %>% group_by(., ...) %>%
    summarise(., median = prettyNum(round(median(loan_amnt/1)),big.mark = ","),
              average = prettyNum(round(mean(loan_amnt/1)),big.mark = ","),
              stdev = prettyNum(round(sd(loan_amnt/1)),big.mark = ","))
}

# used to generate summaries grouping by ... and showing usefull statics about each group
# remove the "pretty numbers" to enable the use of the results in formula
sumStat = function(x, ...) {
  x %>% group_by(., ...) %>%
    summarise(., median = round(median(loan_amnt/1)),
              average = round(mean(loan_amnt/1)),
              stdev = round(sd(loan_amnt/1)))
}

# calculating retrun on investment
roi = function(x, ...){
  x %>% group_by(...) %>%
    summarize(., roi       = round(sum((total_pymnt / funded_amnt) * 100) / n(), 2))
}

Modifying, cleaning the data and adding useful columns to the original data:

Now that we have prepared the grounds for our analysis we need to clean up the data and add some fields that will prove useful for our analysis.

First, we will clean the data by removing some corrupted loans. Note that for every entry that we remove, we check the sum of the loan amounts to judge how much of the data we are removing. In the end we will remove around USD 10 mm worth of loans, which corresponds to 0.1% of the total. This is perfectly acceptable.

# removing policy_code == 2, i.e. "not public" and then removing the comlumn
sum(filter(LC0, policy_code != 1)$loan_amnt, na.rm = T)
## [1] 0
LC = filter(LC, policy_code == 1)
LC = select(LC, -policy_code)

# removing 28 records with a lot of missing data:
sum(filter(LC0, is.na(pub_rec))$loan_amnt, na.rm = T)
## [1] 113200
LC = filter(LC, !is.na(pub_rec))

# Filtering out the entries where last_fico_range_high = 0
sum(filter(LC0, last_fico_range_high == 0)$loan_amnt, na.rm = T)
## [1] 1102375
LC = filter(LC, last_fico_range_high != 0)

# Removing the loans without any entry for revol_util
sum(filter(LC0, revol_util == "")$loan_amnt, na.rm = T)
## [1] 4582350
LC = filter(LC, revol_util != "")

# Removing the loans with fico scores < 660 as they are very few of them, and LC changed their
# policy and does not issue loan for scores below 660
sum(filter(LC0, fico_range_high < 660)$loan_amnt, na.rm = T)
## [1] 3531950
LC = filter(LC, fico_range_high >= 660)

# Removing "Does not meet the credit policy.  Status:" from:
# Does not meet the credit policy.  Status:Charged Off
# Does not meet the credit policy.  Status:Current
# Does not meet the credit policy.  Status:Fully Paid
LC = mutate(LC, loan_status_new =
                ifelse(grepl("Does not meet the credit policy.  Status:", loan_status),
                       gsub("Does not meet the credit policy.  Status:","",loan_status),
                       loan_status))

Now we modify and add features to our remaining data set. These features will be key in our analysis. We are converting dates and characters to numeric to enable calculations as well as creating buckets to enable classifications:

# adding issue year, quarter
# adding FICO scores buckets
Months = c("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
LC = mutate(LC, issue_y = strtoi(substr(issue_d, 5, 9)),
                issue_m = match(substr(issue_d, 1, 3),Months),
                issue_ym = issue_y * 100 + issue_m,
                issue_q = ceiling(issue_m / 3),
                issue_yq = paste(issue_y, "-Q", issue_q, sep = ""),
                n = 1)

# FICO buckets for future visualizations
LC = mutate(LC, FICO_buckets_Original = ceiling((fico_range_high - 490) / 30),
                FICO_buckets_Last = ceiling((fico_range_high - 490) / 30),
                FICO_bin_name_Original = sapply(FICO_buckets_Original, bin_name),
                FICO_bin_name_Last = sapply(FICO_buckets_Last, bin_name))

# Add a feature "matured" for Loans that have or would have matured by report_date
LC = mutate(LC, matured = ifelse((issue_ym + ifelse(term == " 36 months", 300, 500)) > report_date, F, T))

# reduce the number of categories of purpose
LC = mutate(LC, purpose_new = ifelse(purpose == "credit_card" | 
                                       purpose == "debt_consolidation", "debt",
                              ifelse(purpose == "car" | 
                                       purpose == "major_purchase" | 
                                       purpose == "vacation" | 
                                       purpose == "wedding" | 
                                       purpose == "medical" | 
                                       purpose == "other", "purchase",
                              ifelse(purpose == "house" | 
                                       purpose == "home_improvement" | 
                                       purpose == "moving" | 
                                       purpose == "renewable_energy", "purchase", purpose))))

# reduce the number of categories of purpose
LC = mutate(LC, home = ifelse(home_ownership == "ANY" | home_ownership == "NONE", "OTHER", home_ownership))

# give LC grade numeric values
sub_grade_vec = unique(LC$sub_grade) %>% .[order(., decreasing = T)]
LC = mutate(LC, LC_score = match(sub_grade, sub_grade_vec))

# adding a feature credit_ym corresponding to how many years old is the credit history of a borrower:
LC = mutate(LC, 
        credit_ym = round(((floor(issue_ym/100)*100 + 
                            ((issue_ym - floor(issue_ym/100)*100)-1)/12*100)
                         - (strtoi(substr(earliest_cr_line, 5, 9)) * 100 + 
                              (match(substr(earliest_cr_line, 1, 3),Months)-1)/12*100))/100,1))

# creating issue_y buckets:
LC = mutate(LC, issue_bucket = ifelse(issue_y <= 2012, "2007-2012", issue_y))

# delinq_2yrs buckets:
LC = mutate(LC, delinq_bucket = ifelse(delinq_2yrs >= 2, "2+", delinq_2yrs))

# inq_last_6mths buckets:
LC = mutate(LC, inq_bucket = ifelse(inq_last_6mths >= 7, "7+", 
                             ifelse(inq_last_6mths >= 5, "5-6", 
                             ifelse(inq_last_6mths >= 3, "3-4", 
                             ifelse(inq_last_6mths >= 1, "1-2", 0)))))

# public record buckets: 
LC = mutate(LC, rec_bucket = #ifelse(pub_rec >= 10, "10+", 
                             #ifelse(pub_rec >= 7, "7-9", 
                             #ifelse(pub_rec >= 4, "4-6", 
                             ifelse(pub_rec >= 1, "1+", 0))#)))

# Annual income quantile buckets:
groupvec = quantile(LC$annual_inc, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, annual_inc_bucket = cut(LC$annual_inc, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

# DTI quantile buckets:
groupvec = quantile(LC$dti, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, dti_bucket = cut(LC$dti, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

# Revolving balance quantile buckets:
LC = mutate(LC, revol = as.numeric(gsub("%","",revol_util)))
groupvec = quantile(LC$revol, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, revol_bucket = cut(LC$revol, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

# Revolving balance quantile buckets:
groupvec = quantile(LC$revol_bal, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, revol_bal_bucket = cut(LC$revol_bal, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

# total accounts buckets:
groupvec = quantile(LC$total_acc, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, total_acc_bucket = cut(LC$total_acc, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

# open accounts buckets:
groupvec = quantile(LC$open_acc, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, open_acc_bucket = cut(LC$open_acc, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

# credit_y quantile buckets:
groupvec = quantile(LC$credit_ym, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, credit_ym_bucket = cut(LC$credit_ym, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

We also create a subset of our modified data. This subset will correspond to all the loans that have or would have matured by the time of the report. The loans with 3 years of maturity may have been issued all the way to Jun-2012 as we recall that our current reports are as of Jun-2015. Similarly, the loans with 5 years of maturity will be selected only if they were issued before Jun-2010.

This subset will be called LCmatured and will enable us to extract true default rates by comparing solely loans whose final status is know with certainty.

Note that we will have to over-write the quantile buckets that we just created on our dataset LC to adjust them to our new subset LCmatured.

# create sub table of only the matured loans:
LCmatured = filter(LC, matured == T)

# we'll re-do the quantile buckets for LCmatured to adjust them:
# Annual income quantile buckets:
groupvec = quantile(LCmatured$annual_inc, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, annual_inc_bucket = cut(LCmatured$annual_inc, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

# DTI quantile buckets:
groupvec = quantile(LCmatured$dti, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, dti_bucket = cut(LCmatured$dti, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

# Revolving balance utilization quantile buckets:
# First we need to mutate a field to convert them to numeric:
groupvec = quantile(LCmatured$revol, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, revol_bucket = cut(LCmatured$revol, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

# Revolving balance quantile buckets:
groupvec = quantile(LCmatured$revol_bal, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, revol_bal_bucket = cut(LCmatured$revol_bal, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

# total accounts buckets:
groupvec = quantile(LCmatured$total_acc, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, total_acc_bucket = cut(LCmatured$total_acc, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

# open accounts buckets:
groupvec = quantile(LCmatured$open_acc, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, open_acc_bucket = cut(LCmatured$open_acc, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

# credit_y quantile buckets:
groupvec = quantile(LCmatured$credit_ym, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, credit_ym_bucket = cut(LCmatured$credit_ym, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))

First look at the data, exploratory research:

Looking at loan amounts types and loan amounts in each category

# Looking at loan amounts types and loan amounts in each category
# Smallest loan:
min(LC$loan_amnt)
## [1] 500
# Largest loan:
max(LC$loan_amnt)
## [1] 35000
# Total amount of loans in USD millon 
prettyNum(round(sum(LC$loan_amnt/1e6), 1),big.mark = ",")
## [1] "9,409.2"
# Total amount of loans in USD millon using the original data:
prettyNum(round(sum(LC0$loan_amnt/1e6, na.rm = T),1), big.mark = ",")
## [1] "9,418.4"

Looking at the distribution of the loans relative to their latest status:

# Looking at total loan amnts per loan_status
# Report showing the amounts and number of loans in each category, here the latest status of the loans
sumAmnts(LC, loan_status_new)
## Source: local data frame [7 x 3]
## 
##      loan_status_new  total_issued       n
##                (chr)         (chr)   (chr)
## 1        Charged Off   481,653,700  33,793
## 2            Current 6,666,935,175 440,981
## 3            Default     2,414,500     160
## 4         Fully Paid 2,016,144,450 154,966
## 5    In Grace Period    83,747,125   5,218
## 6  Late (16-30 days)    31,062,300   2,043
## 7 Late (31-120 days)   127,221,050   8,259
# Report showing some statistics for each category, here the latest status of the loans
sumStats(LC, loan_status_new)
## Source: local data frame [7 x 4]
## 
##      loan_status_new median average stdev
##                (chr)  (chr)   (chr) (chr)
## 1        Charged Off 12,000  14,253 8,362
## 2            Current 14,000  15,118 8,411
## 3            Default 13,750  15,091 9,075
## 4         Fully Paid 11,200  13,010 7,937
## 5    In Grace Period 15,000  16,050 8,543
## 6  Late (16-30 days) 13,750  15,204 8,516
## 7 Late (31-120 days) 14,000  15,404 8,524
# Pie chart of the distribution of Loans across the different status:
sumAmnt(LC, loan_status_new) %>% merge(sumPerSatus(LC, loan_status_new)) %>%
  plot_ly(type = "pie", 
          labels = loan_status_new, 
          values = total_issued, 
          hole = 0.5,
          marker = list(colors = brewer.pal(7, "Pastel2"),
                        line = list(width = 1, color = "rgb(52, 110, 165)")),
          sort = F,
          direction = "counterclockwise",
          rotation = 90,
          textinfo = "label+percent",
          textfont = list(size = 14),
          text = paste("Default rates: ", charged),
          opacity = 1,
          textposition = "outside") %>%
  layout(title = 'LOAN ISSUED GROUPED BY STATUS<br>(Hover for breakdown)',
         height = 500, width = 1400, autosize = T, 
         legend = list(font = list(size = 16), x = 1, y = 1, traceorder = "normal"))
## Success! Created a new plotly here -> https://plot.ly/~jfdarre/1355

Looking at Lending Club’s issuances over the years:

# Summary by issue year
# Report showing the amounts and number of loans in each category, here the year of issuance
sumAmnts(LC, issue_y)
## Source: local data frame [9 x 3]
## 
##   issue_y  total_issued       n
##     (int)         (chr)   (chr)
## 1    2007     3,717,025     409
## 2    2008    18,659,300   2,054
## 3    2009    51,823,950   5,263
## 4    2010   131,893,250  12,513
## 5    2011   261,580,825  21,706
## 6    2012   717,928,125  53,314
## 7    2013 1,980,602,200 134,656
## 8    2014 3,501,506,225 235,472
## 9    2015 2,741,467,400 180,033
# Report showing some statistics for each category, here the year of issuance
sumStats(LC, issue_y)
## Source: local data frame [9 x 4]
## 
##   issue_y median average stdev
##     (int)  (chr)   (chr) (chr)
## 1    2007  6,800   9,088 6,644
## 2    2008  7,500   9,084 5,863
## 3    2009  8,800   9,847 5,991
## 4    2010  9,500  10,540 6,599
## 5    2011 10,000  12,051 8,169
## 6    2012 12,000  13,466 8,086
## 7    2013 13,000  14,709 8,098
## 8    2014 13,000  14,870 8,437
## 9    2015 14,000  15,228 8,480
# Bar chart of LC's quaterly loan issuance
sumAmnt(LC, issue_yq) %>%
  plot_ly(type = "bar", 
          x = issue_yq,
          y = total_issued,
          marker = list(color = total_issued,
                        colorscale = list(c(0, "rgb(201, 218, 248)"), list(1, "rgb(61, 133, 198)")),
                        line = list(width = 1, color = "rgb(255, 255, 255)"))
  ) %>%
  layout(title = "QUATERLY LOAN ISSUANCE", bargap = 0,
         yaxis = list(title = "TOTAL LOAN ISSUED IN MLN USD"),
         xaxis = list(title = "YEAR OF ISSUE", range = c(7,32.5), dtick = 2))
## Success! Created a new plotly here -> https://plot.ly/~jfdarre/1357

Looking at Lending Club’s issuances over the United States:

# Preparing our data for the plot:
sumStates = group_by(LC, addr_state) %>%
  summarise(., total_issued = round(sum(loan_amnt/1e6),1), n = n()) %>%
  merge(sumPerSatus(LC, addr_state)[,c(1,2)]) %>%
  merge(data.frame(addr_state, state_name))
# adding the text for the hovering functionality:
sumStates$hover = with(sumStates, paste(state_name, '<br>', 
                                         "Total amount issued:", prettyNum(total_issued, big.mark = ","), "<br>", 
                                         "Number of issued loans:", prettyNum(n, big.mark = ","), "<br>",
                                         "Default rates:", charged))
# because the range of values is to large, the map looks white aside of california and a few other states.
# To avoid this we limit the the values to a maximum, here 500.
sumStates$map_amnt = with(sumStates, pmin(total_issued, 500))
# options for the markers:
l <- list(color = toRGB("steelblue"), width = 1)
# options for the geo:
g = list(scope = 'usa', projection = list(type = 'albers usa'), showlakes = F, lakecolor = toRGB('white'))
# plot of the map of issuance made by lending club so far by state:
plot_ly(sumStates, 
        z = map_amnt, 
        text = hover, 
        locations = addr_state, 
        type = 'choropleth',
        locationmode = 'USA-states', 
        color = map_amnt, 
        colors = 'Purples',
        colorscale = list(c(0, "rgb(201, 218, 248)"), list(1, "rgb(61, 133, 198)")),
        marker = list(line = l), 
        colorbar = list(title = "Amount Issued <br>in millions USD")) %>%
  layout(title = toupper('Total loan amount issued though LC<br>(Hover for breakdown)'), geo = g)
## Success! Created a new plotly here -> https://plot.ly/~jfdarre/1359

Looking at different statistics and distributions of LC grades:

# Looking at grade statistics:
# Report showing the amounts and number of loans in each category, here LC grades
sumAmnts(LC, grade)
## Source: local data frame [7 x 3]
## 
##   grade  total_issued       n
##   (chr)         (chr)   (chr)
## 1     A 1,464,627,275 106,632
## 2     B 2,482,035,550 184,093
## 3     C 2,523,126,075 176,391
## 4     D 1,603,390,750 105,095
## 5     E   912,883,050  51,295
## 6     F   333,103,450  17,623
## 7     G    90,012,150   4,291
# Same report as above but constrained to LCmatured for comparison:
sumAmnts(LCmatured, grade)
## Source: local data frame [7 x 3]
## 
##   grade total_issued      n
##   (chr)        (chr)  (chr)
## 1     A  134,843,575 14,502
## 2     B  157,486,075 15,183
## 3     C   93,918,425  9,440
## 4     D   62,679,175  5,387
## 5     E   20,798,750  1,473
## 6     F    5,851,850    397
## 7     G    2,277,700    155
# Report showing some statistics for each category, here LC grades
sumStats(LC, grade)
## Source: local data frame [7 x 4]
## 
##   grade median average stdev
##   (chr)  (chr)   (chr) (chr)
## 1     A 12,000  13,735 7,388
## 2     B 12,000  13,483 7,596
## 3     C 12,000  14,304 8,420
## 4     D 14,000  15,257 8,948
## 5     E 16,750  17,797 9,254
## 6     F 18,425  18,902 9,300
## 7     G 20,000  20,977 8,713
# Same report as above but constrained to LCmatured for comparison:
sumStats(LCmatured, grade)
## Source: local data frame [7 x 4]
## 
##   grade median average stdev
##   (chr)  (chr)   (chr) (chr)
## 1     A  8,000   9,298 6,025
## 2     B  9,450  10,373 6,587
## 3     C  9,000   9,949 6,293
## 4     D 10,000  11,635 7,646
## 5     E 12,500  14,120 8,819
## 6     F 14,800  14,740 8,420
## 7     G 14,500  14,695 8,537
# Report showing charge off rates, average LC scores and FICO scores for each category, here LC grades
sumPerSatus(LC, grade)
## Source: local data frame [7 x 5]
## 
##   grade charged net_EL avg_fico avg_grade
##   (chr)   (dbl)  (dbl)    (dbl)     (chr)
## 1     A    1.98  49.93      730        A4
## 2     B    4.11  50.05      702        B4
## 3     C    5.25  58.54      692        C3
## 4     D    7.27  60.85      686        D3
## 5     E    8.61  66.70      685        E3
## 6     F   12.60  65.55      682        F3
## 7     G   13.59  67.04      680        G3
# Same report as above but constrained to LCmatured for comparison:
sumPerSatus(LCmatured, grade)
## Source: local data frame [7 x 5]
## 
##   grade charged net_EL avg_fico avg_grade
##   (chr)   (dbl)  (dbl)    (dbl)     (chr)
## 1     A    6.50   3.60      753        A4
## 2     B   12.03   7.05      713        B4
## 3     C   16.42  10.26      691        C3
## 4     D   20.07  12.77      681        D3
## 5     E   22.47  15.33      678        E3
## 6     F   30.48  20.86      677        F3
## 7     G   32.90  21.76      674        G3
# Pie chart of the distribution of Loans across the different LC grades:
sumAmnt(LC, grade) %>% merge(sumPerSatus(LC, grade)) %>%
  plot_ly(type = "pie", 
          labels = grade, 
          values = total_issued, 
          hole = 0.5,
          marker = list(colors = brewer.pal(7, "Pastel2"),
                        line = list(width = 1, color = "rgb(52, 110, 165)")),
          sort = F,
          opacity = 1,
          direction = "counterclockwise",
          rotation = 120,
          textinfo = "label+percent",
          textfont = list(size = 14),
          text = paste("Default rates: ", charged),
          textposition = "outside") %>%
  layout(title = 'LOAN ISSUED GROUPED BY GRADES<br>(Hover for breakdown)',
         height = 731, width = 1274, autosize = T, 
         legend = list(font = list(size = 16), x = 0.88, y = 1, traceorder = "normal"))
## Success! Created a new plotly here -> https://plot.ly/~jfdarre/1361

Looking at different statistics and distributions across the different FICO buckets:

# Now let's have a look at FICO scores:
# Report showing the amounts and number of loans in each category, here the FICO score buckets
sumAmnts(LC, FICO_bin_name_Original)
## Source: local data frame [7 x 3]
## 
##   FICO_bin_name_Original  total_issued       n
##                    (chr)         (chr)   (chr)
## 1                640-670 1,413,717,625 111,559
## 2                670-700 4,120,303,050 289,106
## 3                700-730 2,506,786,975 157,429
## 4                730-760   900,005,000  55,966
## 5                760-790   325,995,650  21,772
## 6                790-820   121,222,575   8,267
## 7                820-850    21,147,425   1,321
# Report showing some statistics for each category, here the FICO score buckets
sumStats(LC, FICO_bin_name_Original)
## Source: local data frame [7 x 4]
## 
##   FICO_bin_name_Original median average stdev
##                    (chr)  (chr)   (chr) (chr)
## 1                640-670 11,000  12,672 7,492
## 2                670-700 12,000  14,252 8,153
## 3                700-730 15,000  15,923 8,682
## 4                730-760 15,000  16,081 8,868
## 5                760-790 13,500  14,973 8,699
## 6                790-820 12,575  14,663 8,775
## 7                820-850 15,000  16,009 9,305
# Report showing charge off rates, average LC scores and FICO scores for each category, here the FICO score buckets
sumPerSatus(LC, FICO = FICO_bin_name_Original)
## Source: local data frame [7 x 5]
## 
##      FICO charged net_EL avg_fico avg_grade
##     (chr)   (dbl)  (dbl)    (dbl)     (chr)
## 1 640-670    5.86  62.02      666        D1
## 2 670-700    5.75  57.77      686        C4
## 3 700-730    4.69  53.93      714        C1
## 4 730-760    4.17  47.95      744        B3
## 5 760-790    3.19  42.29      775        B1
## 6 790-820    2.42  41.70      803        A5
## 7 820-850    1.51  49.25      831        A4
# Same report as above but constrained to LCmatured for comparison:
sumPerSatus(LCmatured, FICO = FICO_bin_name_Original)
## Source: local data frame [7 x 5]
## 
##      FICO charged net_EL avg_fico avg_grade
##     (chr)   (dbl)  (dbl)    (dbl)     (chr)
## 1 640-670   21.19  13.47      667        D4
## 2 670-700   16.13   9.93      687        C3
## 3 700-730   12.19   7.27      716        B4
## 4 730-760    8.51   5.04      745        A5
## 5 760-790    5.63   3.24      774        A4
## 6 790-820    3.60   2.11      802        A4
## 7 820-850    1.92   0.64      828        A3
# Pie chart of the distribution of Loans across the different FICO score buckets:
sumAmnt(LC, FICO_bin_name_Original) %>% merge(sumPerSatus(LC, FICO_bin_name_Original)) %>%
  plot_ly(type = "pie", 
          labels = FICO_bin_name_Original, 
          values = total_issued, 
          hole = 0.5,
          marker = list(colors = brewer.pal(7, "Pastel2"),
                        line = list(width = 1, color = "rgb(52, 110, 165)")),
          sort = F,
          direction = "counterclockwise",
          rotation = 120,
          textinfo = "label+percent",
          textfont = list(size = 14),
          text = paste("Default rates: ", charged),
          opacity = 1,
          textposition = "outside") %>%
  layout(title = 'LOAN ISSUED GROUPED BY FICO SCORES<br>(Hover for breakdown)',
         height = 731, width = 1274, autosize = T, 
         legend = list(font = list(size = 16), x = 0.88, y = 1, traceorder = "normal"))
## Success! Created a new plotly here -> https://plot.ly/~jfdarre/1363

Second look at the data: Exploration of the relationship between FICO and LC scores:

FICO scores have been implemented to assess the credit worthiness of potential borrowers. A model that Lending Club could have used could have been to simply rely on the foundation of FICO’s scores. The majority of the mortgage industry relies on FICO scores to issue mortgages of 100s of thousands.

Hence our interest in looking into LC’s grade system and more specifically if they have an obvious linear relationship with FICO scores.

At first, it seems obvious that the LC grades and FICO scores are very correlated:

# plot 1 shows the average fico scores of the borrowers against their LC grade:
# as expected, we see that the lower your fico score the worst your LC grade is going to be
gradeVSfico = group_by(LC, grade) %>% summarise(., avg_fico = mean(fico_range_high))
qplot(grade, avg_fico, data = gradeVSfico, geom = "boxplot", color = grade) +
  theme_bw() +
  scale_color_brewer(palette="Pastel2") +
  xlab(toupper("LC Grades ranging from A to G")) +
  ylab("FICO SCORES") +
  ggtitle("AVERAGE FICO SCORE VS. LC GRADES")

But this graph already seems to imply otherwise. Indeed 20 of the 35 possible LC sub grades have average FICO scores within 10pts:

# plot 2 shows the average fico scores of the borrowers against their LC sub_grade:
# grades ranging from D1 to G5 (20 levels) have average fico scores ranging from 678 to 688!
sub_gradeVSfico = group_by(LC, sub_grade) %>% summarise(., avg_fico = mean(fico_range_high))
qplot(x = sub_grade, y = avg_fico, data = sub_gradeVSfico, group = 1, geom = "point", size = I(4), color = I("steelblue")) + 
  theme_bw() + 
  scale_color_brewer(palette="Pastel2") +
  geom_hline(y = 688, color = "red", lty = 2) + 
  geom_hline(y = 678, color = "red", lty = 2) + 
  xlab(toupper("LC SUB Grades ranging from A1 to G5")) +
  ylab("FICO SCORES") +
  ggtitle(toupper("Average FICO score vs. LC SUB Grades"))

Running the full distribution of FICO ratings per LC grade confirm our previous point. Grades E, F, G and arguably D and even C have very close distributions and medians:

# plot 3 shows the distribution of fico scores of the borrowers against their LC's grade:
# although there is a trend, we can see on this plot that some borrowers with very high FICO scores
# got really poor LC Grade
qplot(grade, fico_range_high, data = LC, geom = "boxplot", color = grade) +
  theme_bw() +
  scale_color_brewer(palette="Pastel2") +
  xlab(toupper("LC Grades ranging from A to G")) +
  ylab("FICO SCORES") +
  ggtitle(toupper("Box plot of the FICO score distribution for each LC Grades"))