notebook.community

Edit and run 





In [1]:



    


install.packages(c('data.table', 'ggplot2'), repos='http://cran.us.r-project.org')

library(data.table)
library(ggplot2)



    


















    






package 'data.table' successfully unpacked and MD5 sums checked
package 'ggplot2' successfully unpacked and MD5 sums checked

The downloaded binary packages are in
	C:\Users\ngeorge\AppData\Local\Temp\RtmpugfXS7\downloaded_packages

















In [2]:



    


# shows the current working directory (wd)
getwd()



    


















    






'C:/Users/ngeorge/Documents/GitHub/lending_club_EDA'
















In [3]:



    


# loading takes a while...

# this should be the path to the data.  Adapt for your system
win_filepath <- 'C:/Users/ngeorge/Documents/GitHub/preprocess_lending_club_data/full_data/'
# same path from virtualbox shared folder
linux_filepath <- '/media/sf_C_DRIVE/Users/ngeorge/Documents/GitHub/preprocess_lending_club_data/full_data/'
accepted_def <- read.csv(gzfile(paste(win_filepath, 'accepted_2007_to_2016.csv.gz', sep='')), na.strings='')
acc_dt <- as.data.table(accepted_def)
rejected_def <- read.csv(gzfile(paste(win_filepath, 'rejected_2007_to_2016.csv.gz', sep='')), na.strings='')
rej_dt <- as.data.table(accepted_def)



    











In [4]:



    


# that's a lot of observations
dim(acc_dt)



    


















    







    
	
  1. 1321847
    2. 
	
  2. 111
    3. 



















In [5]:



    


names(acc_dt)



    


















    







    
	
  1. 'id'
    2. 
	
  2. 'member_id'
    3. 
	
  3. 'loan_amnt'
    4. 
	
  4. 'funded_amnt'
    5. 
	
  5. 'funded_amnt_inv'
    6. 
	
  6. 'term'
    7. 
	
  7. 'int_rate'
    8. 
	
  8. 'installment'
    9. 
	
  9. 'grade'
    10. 
	
  10. 'sub_grade'
    11. 
	
  11. 'emp_title'
    12. 
	
  12. 'emp_length'
    13. 
	
  13. 'home_ownership'
    14. 
	
  14. 'annual_inc'
    15. 
	
  15. 'verification_status'
    16. 
	
  16. 'issue_d'
    17. 
	
  17. 'loan_status'
    18. 
	
  18. 'pymnt_plan'
    19. 
	
  19. 'url'
    20. 
	
  20. 'desc'
    21. 
	
  21. 'purpose'
    22. 
	
  22. 'title'
    23. 
	
  23. 'zip_code'
    24. 
	
  24. 'addr_state'
    25. 
	
  25. 'dti'
    26. 
	
  26. 'delinq_2yrs'
    27. 
	
  27. 'earliest_cr_line'
    28. 
	
  28. 'inq_last_6mths'
    29. 
	
  29. 'mths_since_last_delinq'
    30. 
	
  30. 'mths_since_last_record'
    31. 
	
  31. 'open_acc'
    32. 
	
  32. 'pub_rec'
    33. 
	
  33. 'revol_bal'
    34. 
	
  34. 'revol_util'
    35. 
	
  35. 'total_acc'
    36. 
	
  36. 'initial_list_status'
    37. 
	
  37. 'out_prncp'
    38. 
	
  38. 'out_prncp_inv'
    39. 
	
  39. 'total_pymnt'
    40. 
	
  40. 'total_pymnt_inv'
    41. 
	
  41. 'total_rec_prncp'
    42. 
	
  42. 'total_rec_int'
    43. 
	
  43. 'total_rec_late_fee'
    44. 
	
  44. 'recoveries'
    45. 
	
  45. 'collection_recovery_fee'
    46. 
	
  46. 'last_pymnt_d'
    47. 
	
  47. 'last_pymnt_amnt'
    48. 
	
  48. 'next_pymnt_d'
    49. 
	
  49. 'last_credit_pull_d'
    50. 
	
  50. 'collections_12_mths_ex_med'
    51. 
	
  51. 'mths_since_last_major_derog'
    52. 
	
  52. 'policy_code'
    53. 
	
  53. 'application_type'
    54. 
	
  54. 'annual_inc_joint'
    55. 
	
  55. 'dti_joint'
    56. 
	
  56. 'verification_status_joint'
    57. 
	
  57. 'acc_now_delinq'
    58. 
	
  58. 'tot_coll_amt'
    59. 
	
  59. 'tot_cur_bal'
    60. 
	
  60. 'open_acc_6m'
    61. 
	
  61. 'open_il_6m'
    62. 
	
  62. 'open_il_12m'
    63. 
	
  63. 'open_il_24m'
    64. 
	
  64. 'mths_since_rcnt_il'
    65. 
	
  65. 'total_bal_il'
    66. 
	
  66. 'il_util'
    67. 
	
  67. 'open_rv_12m'
    68. 
	
  68. 'open_rv_24m'
    69. 
	
  69. 'max_bal_bc'
    70. 
	
  70. 'all_util'
    71. 
	
  71. 'total_rev_hi_lim'
    72. 
	
  72. 'inq_fi'
    73. 
	
  73. 'total_cu_tl'
    74. 
	
  74. 'inq_last_12m'
    75. 
	
  75. 'acc_open_past_24mths'
    76. 
	
  76. 'avg_cur_bal'
    77. 
	
  77. 'bc_open_to_buy'
    78. 
	
  78. 'bc_util'
    79. 
	
  79. 'chargeoff_within_12_mths'
    80. 
	
  80. 'delinq_amnt'
    81. 
	
  81. 'mo_sin_old_il_acct'
    82. 
	
  82. 'mo_sin_old_rev_tl_op'
    83. 
	
  83. 'mo_sin_rcnt_rev_tl_op'
    84. 
	
  84. 'mo_sin_rcnt_tl'
    85. 
	
  85. 'mort_acc'
    86. 
	
  86. 'mths_since_recent_bc'
    87. 
	
  87. 'mths_since_recent_bc_dlq'
    88. 
	
  88. 'mths_since_recent_inq'
    89. 
	
  89. 'mths_since_recent_revol_delinq'
    90. 
	
  90. 'num_accts_ever_120_pd'
    91. 
	
  91. 'num_actv_bc_tl'
    92. 
	
  92. 'num_actv_rev_tl'
    93. 
	
  93. 'num_bc_sats'
    94. 
	
  94. 'num_bc_tl'
    95. 
	
  95. 'num_il_tl'
    96. 
	
  96. 'num_op_rev_tl'
    97. 
	
  97. 'num_rev_accts'
    98. 
	
  98. 'num_rev_tl_bal_gt_0'
    99. 
	
  99. 'num_sats'
    100. 
	
  100. 'num_tl_120dpd_2m'
    101. 
	
  101. 'num_tl_30dpd'
    102. 
	
  102. 'num_tl_90g_dpd_24m'
    103. 
	
  103. 'num_tl_op_past_12m'
    104. 
	
  104. 'pct_tl_nvr_dlq'
    105. 
	
  105. 'percent_bc_gt_75'
    106. 
	
  106. 'pub_rec_bankruptcies'
    107. 
	
  107. 'tax_liens'
    108. 
	
  108. 'tot_hi_cred_lim'
    109. 
	
  109. 'total_bal_ex_mort'
    110. 
	
  110. 'total_bc_limit'
    111. 
	
  111. 'total_il_high_credit_limit'
    112. 



















In [6]:



    


str(acc_dt)



    


















    






Classes 'data.table' and 'data.frame':	1321847 obs. of  111 variables:
 $ id                            : int  1077501 1077430 1077175 1076863 1075358 1075269 1069639 1072053 1071795 1071570 ...
 $ member_id                     : num  1296599 1314167 1313524 1277178 1311748 ...
 $ loan_amnt                     : num  5000 2500 2400 10000 3000 ...
 $ funded_amnt                   : num  5000 2500 2400 10000 3000 ...
 $ funded_amnt_inv               : num  4975 2500 2400 10000 3000 ...
 $ term                          : Factor w/ 2 levels " 36 months"," 60 months": 1 2 1 1 2 1 2 1 2 2 ...
 $ int_rate                      : Factor w/ 593 levels "  5.32%","  5.42%",..: 110 290 315 211 177 43 315 409 480 177 ...
 $ installment                   : num  162.9 59.8 84.3 339.3 67.8 ...
 $ grade                         : Factor w/ 7 levels "A","B","C","D",..: 2 3 3 3 2 1 3 5 6 2 ...
 $ sub_grade                     : Factor w/ 35 levels "A1","A2","A3",..: 7 14 15 11 10 4 15 21 27 10 ...
 $ emp_title                     : Factor w/ 381900 levels "'Property Manager",..: NA 286944 NA 12233 361022 365337 315798 217775 NA 329499 ...
 $ emp_length                    : Factor w/ 12 levels "< 1 year","1 year",..: 3 1 3 3 2 5 10 11 6 1 ...
 $ home_ownership                : Factor w/ 6 levels "ANY","MORTGAGE",..: 6 6 6 6 6 6 6 6 5 6 ...
 $ annual_inc                    : num  24000 30000 12252 49200 80000 ...
 $ verification_status           : Factor w/ 3 levels "Not Verified",..: 3 2 1 2 2 2 1 2 2 3 ...
 $ issue_d                       : Factor w/ 115 levels "Apr-2008","Apr-2009",..: 24 24 24 24 24 24 24 24 24 24 ...
 $ loan_status                   : Factor w/ 10 levels "Charged Off",..: 6 1 6 6 6 6 6 6 1 1 ...
 $ pymnt_plan                    : Factor w/ 1 level "n": 1 1 1 1 1 1 1 1 1 1 ...
 $ url                           : Factor w/ 1321847 levels "https://lendingclub.com/browse/loanDetail.action?loan_id=1000007",..: 21302 21266 21252 21230 20701 20693 19198 19819 19804 19665 ...
 $ desc                          : Factor w/ 124502 levels "- Pay off Dell Financial: $ 1300.00 - Pay off IRS for 2005: $ 1400.00 - Pay off Mac Comp    : $ 1700.00 - Pay off Bill Me Later"| __truncated__,..: 113416 113421 NA 113272 113248 NA 112361 111645 113246 111648 ...
 $ purpose                       : Factor w/ 14 levels "car","credit_card",..: 2 1 12 10 10 14 3 1 12 10 ...
 $ title                         : Factor w/ 63156 levels "'08 & '09 Roth IRA Investments",..: 10498 4975 52509 50883 50276 42604 36956 7265 24375 6112 ...
 $ zip_code                      : Factor w/ 948 levels "007xx","008xx",..: 820 300 581 867 921 813 271 850 908 739 ...
 $ addr_state                    : Factor w/ 51 levels "AK","AL","AR",..: 4 11 15 5 38 4 28 5 5 44 ...
 $ dti                           : num  27.65 1 8.72 20 17.94 ...
 $ delinq_2yrs                   : num  0 0 0 0 0 0 0 0 0 0 ...
 $ earliest_cr_line              : Factor w/ 718 levels "Apr-1955","Apr-1958",..: 272 42 589 216 283 592 351 294 47 709 ...
 $ inq_last_6mths                : num  1 5 2 1 0 3 1 2 2 0 ...
 $ mths_since_last_delinq        : num  NA NA NA 35 38 NA NA NA NA NA ...
 $ mths_since_last_record        : num  NA NA NA NA NA NA NA NA NA NA ...
 $ open_acc                      : num  3 3 2 10 15 9 7 4 11 2 ...
 $ pub_rec                       : num  0 0 0 0 0 0 0 0 0 0 ...
 $ revol_bal                     : num  13648 1687 2956 5598 27783 ...
 $ revol_util                    : Factor w/ 1386 levels "0%","0.01%","0.03%",..: 1209 1279 1372 456 860 542 1228 1247 601 646 ...
 $ total_acc                     : num  9 4 10 37 38 12 11 4 13 3 ...
 $ initial_list_status           : Factor w/ 2 levels "f","w": 1 1 1 1 1 1 1 1 1 1 ...
 $ out_prncp                     : num  0 0 0 0 0 0 0 0 0 0 ...
 $ out_prncp_inv                 : num  0 0 0 0 0 0 0 0 0 0 ...
 $ total_pymnt                   : num  5863 1015 3006 12232 4067 ...
 $ total_pymnt_inv               : num  5834 1015 3006 12232 4067 ...
 $ total_rec_prncp               : num  5000 456 2400 10000 3000 ...
 $ total_rec_int                 : num  863 435 606 2215 1067 ...
 $ total_rec_late_fee            : num  0 0 0 17 0 ...
 $ recoveries                    : num  0 123 0 0 0 ...
 $ collection_recovery_fee       : num  0 1.11 0 0 0 0 0 0 2.09 2.52 ...
 $ last_pymnt_d                  : Factor w/ 110 levels "Apr-2008","Apr-2009",..: 45 6 63 45 47 45 83 45 5 88 ...
 $ last_pymnt_amnt               : num  171.6 119.7 649.9 357.5 67.3 ...
 $ next_pymnt_d                  : Factor w/ 103 levels "Apr-2008","Apr-2009",..: NA NA NA NA NA NA NA NA NA NA ...
 $ last_credit_pull_d            : Factor w/ 115 levels "Apr-2009","Apr-2010",..: 47 105 47 8 47 46 115 26 105 28 ...
 $ collections_12_mths_ex_med    : num  0 0 0 0 0 0 0 0 0 0 ...
 $ mths_since_last_major_derog   : num  NA NA NA NA NA NA NA NA NA NA ...
 $ policy_code                   : num  1 1 1 1 1 1 1 1 1 1 ...
 $ application_type              : Factor w/ 3 levels "DIRECT_PAY","INDIVIDUAL",..: 2 2 2 2 2 2 2 2 2 2 ...
 $ annual_inc_joint              : num  NA NA NA NA NA NA NA NA NA NA ...
 $ dti_joint                     : num  NA NA NA NA NA NA NA NA NA NA ...
 $ verification_status_joint     : Factor w/ 3 levels "Not Verified",..: NA NA NA NA NA NA NA NA NA NA ...
 $ acc_now_delinq                : num  0 0 0 0 0 0 0 0 0 0 ...
 $ tot_coll_amt                  : num  NA NA NA NA NA NA NA NA NA NA ...
 $ tot_cur_bal                   : num  NA NA NA NA NA NA NA NA NA NA ...
 $ open_acc_6m                   : num  NA NA NA NA NA NA NA NA NA NA ...
 $ open_il_6m                    : num  NA NA NA NA NA NA NA NA NA NA ...
 $ open_il_12m                   : num  NA NA NA NA NA NA NA NA NA NA ...
 $ open_il_24m                   : num  NA NA NA NA NA NA NA NA NA NA ...
 $ mths_since_rcnt_il            : num  NA NA NA NA NA NA NA NA NA NA ...
 $ total_bal_il                  : num  NA NA NA NA NA NA NA NA NA NA ...
 $ il_util                       : num  NA NA NA NA NA NA NA NA NA NA ...
 $ open_rv_12m                   : num  NA NA NA NA NA NA NA NA NA NA ...
 $ open_rv_24m                   : num  NA NA NA NA NA NA NA NA NA NA ...
 $ max_bal_bc                    : num  NA NA NA NA NA NA NA NA NA NA ...
 $ all_util                      : num  NA NA NA NA NA NA NA NA NA NA ...
 $ total_rev_hi_lim              : num  NA NA NA NA NA NA NA NA NA NA ...
 $ inq_fi                        : num  NA NA NA NA NA NA NA NA NA NA ...
 $ total_cu_tl                   : num  NA NA NA NA NA NA NA NA NA NA ...
 $ inq_last_12m                  : num  NA NA NA NA NA NA NA NA NA NA ...
 $ acc_open_past_24mths          : num  NA NA NA NA NA NA NA NA NA NA ...
 $ avg_cur_bal                   : num  NA NA NA NA NA NA NA NA NA NA ...
 $ bc_open_to_buy                : num  NA NA NA NA NA NA NA NA NA NA ...
 $ bc_util                       : num  NA NA NA NA NA NA NA NA NA NA ...
 $ chargeoff_within_12_mths      : num  0 0 0 0 0 0 0 0 0 0 ...
 $ delinq_amnt                   : num  0 0 0 0 0 0 0 0 0 0 ...
 $ mo_sin_old_il_acct            : num  NA NA NA NA NA NA NA NA NA NA ...
 $ mo_sin_old_rev_tl_op          : num  NA NA NA NA NA NA NA NA NA NA ...
 $ mo_sin_rcnt_rev_tl_op         : num  NA NA NA NA NA NA NA NA NA NA ...
 $ mo_sin_rcnt_tl                : num  NA NA NA NA NA NA NA NA NA NA ...
 $ mort_acc                      : num  NA NA NA NA NA NA NA NA NA NA ...
 $ mths_since_recent_bc          : num  NA NA NA NA NA NA NA NA NA NA ...
 $ mths_since_recent_bc_dlq      : num  NA NA NA NA NA NA NA NA NA NA ...
 $ mths_since_recent_inq         : num  NA NA NA NA NA NA NA NA NA NA ...
 $ mths_since_recent_revol_delinq: num  NA NA NA NA NA NA NA NA NA NA ...
 $ num_accts_ever_120_pd         : num  NA NA NA NA NA NA NA NA NA NA ...
 $ num_actv_bc_tl                : num  NA NA NA NA NA NA NA NA NA NA ...
 $ num_actv_rev_tl               : num  NA NA NA NA NA NA NA NA NA NA ...
 $ num_bc_sats                   : num  NA NA NA NA NA NA NA NA NA NA ...
 $ num_bc_tl                     : num  NA NA NA NA NA NA NA NA NA NA ...
 $ num_il_tl                     : num  NA NA NA NA NA NA NA NA NA NA ...
 $ num_op_rev_tl                 : num  NA NA NA NA NA NA NA NA NA NA ...
 $ num_rev_accts                 : num  NA NA NA NA NA NA NA NA NA NA ...
 $ num_rev_tl_bal_gt_0           : num  NA NA NA NA NA NA NA NA NA NA ...
 $ num_sats                      : num  NA NA NA NA NA NA NA NA NA NA ...
  [list output truncated]
 - attr(*, ".internal.selfref")=<externalptr> 

















In [7]:



    


# outliers are screwing it up!
hist(acc_dt[, dti])



    


















    
























In [8]:



    


# from here: http://stackoverflow.com/questions/4787332/how-to-remove-outliers-from-a-dataset
remove_outliers <- function(x, na.rm = TRUE, ...) {
  qnt <- quantile(x, probs=c(.25, .75), na.rm = na.rm, ...)
  H <- 1.5 * IQR(x, na.rm = na.rm)
  y <- x
  y[x < (qnt[1] - H)] <- NA
  y[x > (qnt[2] + H)] <- NA
  y
}



    











In [9]:



    


dti_no_outliers <- remove_outliers(acc_dt[, dti])



    











In [10]:



    


hist(dti_no_outliers)

Content source: nateGeorge/lending_club_EDA

Similar notebooks:

notebook.community | gallery | about