LC_Cleaning_LightEDA(UPDATED).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook is the next step in data cleaning and light feature engineering. Unlike the python script that conducted dated cleaning for \"obvious\" things like fixing data types, (E.g. replacing 19.2% with 0.192) and added measures like calculating lost principle on defaulted loans and monthly debt payments per customer. We also removed a small number of rows due to having missing data, or because they were for loans that still current, so we wouldn't know if the loan would be paid off vs. eventually going into default. \n",
    "\n",
    "**High Level Findings (based on the dataset post cleaning)**\n",
    "* Default rate: 19.18% \n",
    "* Average loan Amount: 15,307.37\n",
    "* Average interest rate: 12.55% "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt  \n",
    "import seaborn as sn\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set parameters for how many viewable columns there are \n",
    "\n",
    "pd.options.display.max_rows = 999\n",
    "pd.options.display.max_columns = 999\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(396607, 60)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lending = pd.read_csv('data/processed_2015_loan_data_april22.csv')\n",
    "lending.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>funded_amnt</th>\n",
       "      <th>term</th>\n",
       "      <th>int_rate</th>\n",
       "      <th>installment</th>\n",
       "      <th>grade</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>issue_d</th>\n",
       "      <th>loan_status</th>\n",
       "      <th>purpose</th>\n",
       "      <th>addr_state</th>\n",
       "      <th>dti</th>\n",
       "      <th>delinq_2yrs</th>\n",
       "      <th>earliest_cr_line</th>\n",
       "      <th>fico_range_low</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>mths_since_last_delinq</th>\n",
       "      <th>open_acc</th>\n",
       "      <th>pub_rec</th>\n",
       "      <th>pub_rec_bankruptcies</th>\n",
       "      <th>revol_bal</th>\n",
       "      <th>revol_util</th>\n",
       "      <th>total_acc</th>\n",
       "      <th>total_pymnt</th>\n",
       "      <th>total_pymnt_inv</th>\n",
       "      <th>total_rec_prncp</th>\n",
       "      <th>total_rec_int</th>\n",
       "      <th>total_rec_late_fee</th>\n",
       "      <th>collections_12_mths_ex_med</th>\n",
       "      <th>application_type</th>\n",
       "      <th>acc_now_delinq</th>\n",
       "      <th>chargeoff_within_12_mths</th>\n",
       "      <th>acc_open_past_24mths</th>\n",
       "      <th>avg_cur_bal</th>\n",
       "      <th>delinq_amnt</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>mo_sin_rcnt_rev_tl_op</th>\n",
       "      <th>mo_sin_rcnt_tl</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "      <th>mths_since_recent_revol_delinq</th>\n",
       "      <th>num_accts_ever_120_pd</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>num_tl_120dpd_2m</th>\n",
       "      <th>pct_tl_nvr_dlq</th>\n",
       "      <th>tot_coll_amt</th>\n",
       "      <th>tot_cur_bal</th>\n",
       "      <th>total_bal_ex_mort</th>\n",
       "      <th>emp_length_months</th>\n",
       "      <th>dti_dec</th>\n",
       "      <th>length_of_credit_history</th>\n",
       "      <th>monthly_income</th>\n",
       "      <th>monthly_debt_payments</th>\n",
       "      <th>updated_monthly_debt_payments</th>\n",
       "      <th>lost_principle</th>\n",
       "      <th>total_payments</th>\n",
       "      <th>post_loan_dti</th>\n",
       "      <th>net_gain</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>0.1485</td>\n",
       "      <td>691.84</td>\n",
       "      <td>C</td>\n",
       "      <td>6</td>\n",
       "      <td>RENT</td>\n",
       "      <td>110000.0</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>IL</td>\n",
       "      <td>12.45</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2007-06-01</td>\n",
       "      <td>690.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21374.0</td>\n",
       "      <td>0.845</td>\n",
       "      <td>12.0</td>\n",
       "      <td>24889.01336</td>\n",
       "      <td>24889.01</td>\n",
       "      <td>20000.00</td>\n",
       "      <td>4889.01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Individual</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5356.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>37491.0</td>\n",
       "      <td>37491.0</td>\n",
       "      <td>72</td>\n",
       "      <td>0.1245</td>\n",
       "      <td>102</td>\n",
       "      <td>9166.666667</td>\n",
       "      <td>1141.250000</td>\n",
       "      <td>1833.090000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>35.975100</td>\n",
       "      <td>0.199973</td>\n",
       "      <td>4889.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>0.1577</td>\n",
       "      <td>700.88</td>\n",
       "      <td>D</td>\n",
       "      <td>5</td>\n",
       "      <td>RENT</td>\n",
       "      <td>70000.0</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>house</td>\n",
       "      <td>FL</td>\n",
       "      <td>22.21</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2004-07-01</td>\n",
       "      <td>680.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19077.0</td>\n",
       "      <td>0.366</td>\n",
       "      <td>63.0</td>\n",
       "      <td>21780.58678</td>\n",
       "      <td>21780.59</td>\n",
       "      <td>20000.00</td>\n",
       "      <td>1780.59</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Individual</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2759.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>137.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>92.1</td>\n",
       "      <td>264.0</td>\n",
       "      <td>63456.0</td>\n",
       "      <td>63456.0</td>\n",
       "      <td>60</td>\n",
       "      <td>0.2221</td>\n",
       "      <td>137</td>\n",
       "      <td>5833.333333</td>\n",
       "      <td>1295.583333</td>\n",
       "      <td>1996.463333</td>\n",
       "      <td>0.00</td>\n",
       "      <td>31.076057</td>\n",
       "      <td>0.342251</td>\n",
       "      <td>1780.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>0.1797</td>\n",
       "      <td>253.78</td>\n",
       "      <td>D</td>\n",
       "      <td>2</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>55000.0</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>Charged Off</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>CO</td>\n",
       "      <td>35.70</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2001-04-01</td>\n",
       "      <td>685.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38623.0</td>\n",
       "      <td>0.780</td>\n",
       "      <td>28.0</td>\n",
       "      <td>5558.20000</td>\n",
       "      <td>5558.20</td>\n",
       "      <td>2687.15</td>\n",
       "      <td>2871.05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Individual</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>20578.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>288087.0</td>\n",
       "      <td>71518.0</td>\n",
       "      <td>24</td>\n",
       "      <td>0.3570</td>\n",
       "      <td>176</td>\n",
       "      <td>4583.333333</td>\n",
       "      <td>1636.250000</td>\n",
       "      <td>1890.030000</td>\n",
       "      <td>7312.85</td>\n",
       "      <td>21.901647</td>\n",
       "      <td>0.412370</td>\n",
       "      <td>-4441.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>0.0849</td>\n",
       "      <td>631.26</td>\n",
       "      <td>B</td>\n",
       "      <td>10</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>85000.0</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>major_purchase</td>\n",
       "      <td>SC</td>\n",
       "      <td>17.61</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1999-02-01</td>\n",
       "      <td>705.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>826.0</td>\n",
       "      <td>0.057</td>\n",
       "      <td>15.0</td>\n",
       "      <td>21538.50898</td>\n",
       "      <td>21538.51</td>\n",
       "      <td>20000.00</td>\n",
       "      <td>1538.51</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Individual</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>17700.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>141601.0</td>\n",
       "      <td>27937.0</td>\n",
       "      <td>120</td>\n",
       "      <td>0.1761</td>\n",
       "      <td>201</td>\n",
       "      <td>7083.333333</td>\n",
       "      <td>1247.375000</td>\n",
       "      <td>1878.635000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>34.119870</td>\n",
       "      <td>0.265219</td>\n",
       "      <td>1538.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>0.0649</td>\n",
       "      <td>306.45</td>\n",
       "      <td>A</td>\n",
       "      <td>6</td>\n",
       "      <td>RENT</td>\n",
       "      <td>85000.0</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>PA</td>\n",
       "      <td>13.07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2002-04-01</td>\n",
       "      <td>685.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10464.0</td>\n",
       "      <td>0.345</td>\n",
       "      <td>23.0</td>\n",
       "      <td>10998.97157</td>\n",
       "      <td>10998.97</td>\n",
       "      <td>10000.00</td>\n",
       "      <td>998.97</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Individual</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1997.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>95.7</td>\n",
       "      <td>8341.0</td>\n",
       "      <td>27957.0</td>\n",
       "      <td>27957.0</td>\n",
       "      <td>72</td>\n",
       "      <td>0.1307</td>\n",
       "      <td>164</td>\n",
       "      <td>7083.333333</td>\n",
       "      <td>925.791667</td>\n",
       "      <td>1232.241667</td>\n",
       "      <td>0.00</td>\n",
       "      <td>35.891570</td>\n",
       "      <td>0.173964</td>\n",
       "      <td>998.97</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   funded_amnt        term  int_rate  installment grade  emp_length  \\\n",
       "0      20000.0   36 months    0.1485       691.84     C           6   \n",
       "1      20000.0   36 months    0.1577       700.88     D           5   \n",
       "2      10000.0   60 months    0.1797       253.78     D           2   \n",
       "3      20000.0   36 months    0.0849       631.26     B          10   \n",
       "4      10000.0   36 months    0.0649       306.45     A           6   \n",
       "\n",
       "  home_ownership  annual_inc verification_status     issue_d  loan_status  \\\n",
       "0           RENT    110000.0        Not Verified  2015-12-01   Fully Paid   \n",
       "1           RENT     70000.0        Not Verified  2015-12-01   Fully Paid   \n",
       "2       MORTGAGE     55000.0        Not Verified  2015-12-01  Charged Off   \n",
       "3       MORTGAGE     85000.0        Not Verified  2015-12-01   Fully Paid   \n",
       "4           RENT     85000.0        Not Verified  2015-12-01   Fully Paid   \n",
       "\n",
       "          purpose addr_state    dti  delinq_2yrs earliest_cr_line  \\\n",
       "0     credit_card         IL  12.45          0.0       2007-06-01   \n",
       "1           house         FL  22.21          0.0       2004-07-01   \n",
       "2     credit_card         CO  35.70          0.0       2001-04-01   \n",
       "3  major_purchase         SC  17.61          1.0       1999-02-01   \n",
       "4     credit_card         PA  13.07          0.0       2002-04-01   \n",
       "\n",
       "   fico_range_low  inq_last_6mths  mths_since_last_delinq  open_acc  pub_rec  \\\n",
       "0           690.0             0.0                     NaN       8.0      0.0   \n",
       "1           680.0             0.0                    38.0      24.0      0.0   \n",
       "2           685.0             0.0                     NaN      14.0      0.0   \n",
       "3           705.0             0.0                     3.0       8.0      0.0   \n",
       "4           685.0             1.0                     NaN      14.0      1.0   \n",
       "\n",
       "   pub_rec_bankruptcies  revol_bal  revol_util  total_acc  total_pymnt  \\\n",
       "0                   0.0    21374.0       0.845       12.0  24889.01336   \n",
       "1                   0.0    19077.0       0.366       63.0  21780.58678   \n",
       "2                   0.0    38623.0       0.780       28.0   5558.20000   \n",
       "3                   0.0      826.0       0.057       15.0  21538.50898   \n",
       "4                   1.0    10464.0       0.345       23.0  10998.97157   \n",
       "\n",
       "   total_pymnt_inv  total_rec_prncp  total_rec_int  total_rec_late_fee  \\\n",
       "0         24889.01         20000.00        4889.01                 0.0   \n",
       "1         21780.59         20000.00        1780.59                 0.0   \n",
       "2          5558.20          2687.15        2871.05                 0.0   \n",
       "3         21538.51         20000.00        1538.51                 0.0   \n",
       "4         10998.97         10000.00         998.97                 0.0   \n",
       "\n",
       "   collections_12_mths_ex_med application_type  acc_now_delinq  \\\n",
       "0                         0.0       Individual             0.0   \n",
       "1                         0.0       Individual             0.0   \n",
       "2                         0.0       Individual             0.0   \n",
       "3                         0.0       Individual             0.0   \n",
       "4                         0.0       Individual             0.0   \n",
       "\n",
       "   chargeoff_within_12_mths  acc_open_past_24mths  avg_cur_bal  delinq_amnt  \\\n",
       "0                       0.0                   3.0       5356.0          0.0   \n",
       "1                       0.0                   9.0       2759.0          0.0   \n",
       "2                       0.0                   4.0      20578.0          0.0   \n",
       "3                       0.0                   4.0      17700.0          0.0   \n",
       "4                       0.0                   7.0       1997.0          0.0   \n",
       "\n",
       "   mo_sin_old_rev_tl_op  mo_sin_rcnt_rev_tl_op  mo_sin_rcnt_tl  mort_acc  \\\n",
       "0                 102.0                   16.0            10.0       0.0   \n",
       "1                 137.0                    2.0             2.0       1.0   \n",
       "2                 176.0                    7.0             6.0       6.0   \n",
       "3                  55.0                   32.0            13.0       3.0   \n",
       "4                 129.0                    1.0             1.0       1.0   \n",
       "\n",
       "   mths_since_recent_inq  mths_since_recent_revol_delinq  \\\n",
       "0                    9.0                             NaN   \n",
       "1                    9.0                             NaN   \n",
       "2                    6.0                             NaN   \n",
       "3                    8.0                             NaN   \n",
       "4                    1.0                             NaN   \n",
       "\n",
       "   num_accts_ever_120_pd  num_il_tl  num_tl_120dpd_2m  pct_tl_nvr_dlq  \\\n",
       "0                    0.0        1.0               0.0           100.0   \n",
       "1                    5.0       39.0               0.0            92.1   \n",
       "2                    0.0        6.0               0.0           100.0   \n",
       "3                    1.0        9.0               0.0            93.3   \n",
       "4                    0.0        3.0               0.0            95.7   \n",
       "\n",
       "   tot_coll_amt  tot_cur_bal  total_bal_ex_mort  emp_length_months  dti_dec  \\\n",
       "0           0.0      37491.0            37491.0                 72   0.1245   \n",
       "1         264.0      63456.0            63456.0                 60   0.2221   \n",
       "2           0.0     288087.0            71518.0                 24   0.3570   \n",
       "3           0.0     141601.0            27937.0                120   0.1761   \n",
       "4        8341.0      27957.0            27957.0                 72   0.1307   \n",
       "\n",
       "   length_of_credit_history  monthly_income  monthly_debt_payments  \\\n",
       "0                       102     9166.666667            1141.250000   \n",
       "1                       137     5833.333333            1295.583333   \n",
       "2                       176     4583.333333            1636.250000   \n",
       "3                       201     7083.333333            1247.375000   \n",
       "4                       164     7083.333333             925.791667   \n",
       "\n",
       "   updated_monthly_debt_payments  lost_principle  total_payments  \\\n",
       "0                    1833.090000            0.00       35.975100   \n",
       "1                    1996.463333            0.00       31.076057   \n",
       "2                    1890.030000         7312.85       21.901647   \n",
       "3                    1878.635000            0.00       34.119870   \n",
       "4                    1232.241667            0.00       35.891570   \n",
       "\n",
       "   post_loan_dti  net_gain  \n",
       "0       0.199973   4889.01  \n",
       "1       0.342251   1780.59  \n",
       "2       0.412370  -4441.80  \n",
       "3       0.265219   1538.51  \n",
       "4       0.173964    998.97  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lending.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get high level summary stats before we proceed further with cleaning and EDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>funded_amnt</th>\n",
       "      <th>int_rate</th>\n",
       "      <th>installment</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>dti</th>\n",
       "      <th>delinq_2yrs</th>\n",
       "      <th>fico_range_low</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>mths_since_last_delinq</th>\n",
       "      <th>open_acc</th>\n",
       "      <th>pub_rec</th>\n",
       "      <th>pub_rec_bankruptcies</th>\n",
       "      <th>revol_bal</th>\n",
       "      <th>revol_util</th>\n",
       "      <th>total_acc</th>\n",
       "      <th>total_pymnt</th>\n",
       "      <th>total_pymnt_inv</th>\n",
       "      <th>total_rec_prncp</th>\n",
       "      <th>total_rec_int</th>\n",
       "      <th>total_rec_late_fee</th>\n",
       "      <th>collections_12_mths_ex_med</th>\n",
       "      <th>acc_now_delinq</th>\n",
       "      <th>chargeoff_within_12_mths</th>\n",
       "      <th>acc_open_past_24mths</th>\n",
       "      <th>avg_cur_bal</th>\n",
       "      <th>delinq_amnt</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>mo_sin_rcnt_rev_tl_op</th>\n",
       "      <th>mo_sin_rcnt_tl</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "      <th>mths_since_recent_revol_delinq</th>\n",
       "      <th>num_accts_ever_120_pd</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>num_tl_120dpd_2m</th>\n",
       "      <th>pct_tl_nvr_dlq</th>\n",
       "      <th>tot_coll_amt</th>\n",
       "      <th>tot_cur_bal</th>\n",
       "      <th>total_bal_ex_mort</th>\n",
       "      <th>emp_length_months</th>\n",
       "      <th>dti_dec</th>\n",
       "      <th>length_of_credit_history</th>\n",
       "      <th>monthly_income</th>\n",
       "      <th>monthly_debt_payments</th>\n",
       "      <th>updated_monthly_debt_payments</th>\n",
       "      <th>lost_principle</th>\n",
       "      <th>total_payments</th>\n",
       "      <th>post_loan_dti</th>\n",
       "      <th>net_gain</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>3.966070e+05</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>205025.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>3.966070e+05</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>3.966070e+05</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>143116.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>3.966070e+05</td>\n",
       "      <td>3.966070e+05</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "      <td>396607.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>15432.514113</td>\n",
       "      <td>0.125966</td>\n",
       "      <td>446.517832</td>\n",
       "      <td>6.110959</td>\n",
       "      <td>7.850727e+04</td>\n",
       "      <td>19.065596</td>\n",
       "      <td>0.351681</td>\n",
       "      <td>693.606063</td>\n",
       "      <td>0.572229</td>\n",
       "      <td>33.838386</td>\n",
       "      <td>12.030105</td>\n",
       "      <td>0.227359</td>\n",
       "      <td>0.128530</td>\n",
       "      <td>1.786801e+04</td>\n",
       "      <td>0.540277</td>\n",
       "      <td>25.575736</td>\n",
       "      <td>16792.291190</td>\n",
       "      <td>16785.669719</td>\n",
       "      <td>13384.143746</td>\n",
       "      <td>3114.960832</td>\n",
       "      <td>2.121773e+00</td>\n",
       "      <td>0.020322</td>\n",
       "      <td>0.006127</td>\n",
       "      <td>0.010348</td>\n",
       "      <td>4.680979</td>\n",
       "      <td>13299.150461</td>\n",
       "      <td>13.717350</td>\n",
       "      <td>182.348012</td>\n",
       "      <td>13.466399</td>\n",
       "      <td>7.971692</td>\n",
       "      <td>1.676836</td>\n",
       "      <td>6.035272</td>\n",
       "      <td>35.618561</td>\n",
       "      <td>0.524640</td>\n",
       "      <td>8.800019</td>\n",
       "      <td>0.000736</td>\n",
       "      <td>93.923565</td>\n",
       "      <td>252.900748</td>\n",
       "      <td>1.427852e+05</td>\n",
       "      <td>5.343058e+04</td>\n",
       "      <td>73.331504</td>\n",
       "      <td>0.190656</td>\n",
       "      <td>196.642427</td>\n",
       "      <td>6542.272520</td>\n",
       "      <td>1150.506642</td>\n",
       "      <td>1597.024474</td>\n",
       "      <td>2048.370368</td>\n",
       "      <td>37.340225</td>\n",
       "      <td>0.269028</td>\n",
       "      <td>1068.712238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>8586.000742</td>\n",
       "      <td>0.043243</td>\n",
       "      <td>245.390252</td>\n",
       "      <td>3.577283</td>\n",
       "      <td>7.525791e+04</td>\n",
       "      <td>8.652028</td>\n",
       "      <td>0.933297</td>\n",
       "      <td>29.957265</td>\n",
       "      <td>0.867657</td>\n",
       "      <td>21.959684</td>\n",
       "      <td>5.650937</td>\n",
       "      <td>0.644387</td>\n",
       "      <td>0.380431</td>\n",
       "      <td>2.438064e+04</td>\n",
       "      <td>0.238760</td>\n",
       "      <td>12.108512</td>\n",
       "      <td>10869.118986</td>\n",
       "      <td>10864.638855</td>\n",
       "      <td>8859.976340</td>\n",
       "      <td>3161.258740</td>\n",
       "      <td>1.488623e+01</td>\n",
       "      <td>0.157995</td>\n",
       "      <td>0.086785</td>\n",
       "      <td>0.116421</td>\n",
       "      <td>3.200389</td>\n",
       "      <td>15779.469157</td>\n",
       "      <td>784.327467</td>\n",
       "      <td>90.257651</td>\n",
       "      <td>17.077018</td>\n",
       "      <td>9.018917</td>\n",
       "      <td>1.986882</td>\n",
       "      <td>5.958791</td>\n",
       "      <td>22.591692</td>\n",
       "      <td>1.332438</td>\n",
       "      <td>7.524807</td>\n",
       "      <td>0.029698</td>\n",
       "      <td>8.878326</td>\n",
       "      <td>2232.729788</td>\n",
       "      <td>1.565731e+05</td>\n",
       "      <td>4.959436e+04</td>\n",
       "      <td>42.927394</td>\n",
       "      <td>0.086520</td>\n",
       "      <td>86.219747</td>\n",
       "      <td>6271.492559</td>\n",
       "      <td>747.086392</td>\n",
       "      <td>876.180515</td>\n",
       "      <td>5244.105415</td>\n",
       "      <td>12.436093</td>\n",
       "      <td>0.103904</td>\n",
       "      <td>5829.755112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1000.000000</td>\n",
       "      <td>0.053200</td>\n",
       "      <td>14.010000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.800000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>660.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-5.100000e-09</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>316.666667</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>31.420000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000623</td>\n",
       "      <td>-35000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>9000.000000</td>\n",
       "      <td>0.091700</td>\n",
       "      <td>267.790000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>4.800000e+04</td>\n",
       "      <td>12.550000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>670.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.573000e+03</td>\n",
       "      <td>0.363000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>8309.014966</td>\n",
       "      <td>8307.370000</td>\n",
       "      <td>6023.450000</td>\n",
       "      <td>1006.730000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>3294.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>119.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>90.900000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.225300e+04</td>\n",
       "      <td>2.347900e+04</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>0.125500</td>\n",
       "      <td>137.000000</td>\n",
       "      <td>4000.000000</td>\n",
       "      <td>649.875000</td>\n",
       "      <td>988.763333</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>33.511177</td>\n",
       "      <td>0.191339</td>\n",
       "      <td>490.955000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>14025.000000</td>\n",
       "      <td>0.122900</td>\n",
       "      <td>390.830000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>6.590000e+04</td>\n",
       "      <td>18.510000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>685.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.214300e+04</td>\n",
       "      <td>0.544000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>14359.543590</td>\n",
       "      <td>14352.470000</td>\n",
       "      <td>12000.000000</td>\n",
       "      <td>2005.130000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>7454.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>167.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>97.400000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.266800e+04</td>\n",
       "      <td>4.064900e+04</td>\n",
       "      <td>72.000000</td>\n",
       "      <td>0.185100</td>\n",
       "      <td>180.000000</td>\n",
       "      <td>5491.666667</td>\n",
       "      <td>1001.201300</td>\n",
       "      <td>1426.733333</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>35.901470</td>\n",
       "      <td>0.263362</td>\n",
       "      <td>1482.530000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>20225.000000</td>\n",
       "      <td>0.155900</td>\n",
       "      <td>586.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>9.400000e+04</td>\n",
       "      <td>25.210000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>710.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.165000e+04</td>\n",
       "      <td>0.723000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>23129.849290</td>\n",
       "      <td>23121.495000</td>\n",
       "      <td>19750.000000</td>\n",
       "      <td>4128.455000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>18240.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>232.000000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>53.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.111530e+05</td>\n",
       "      <td>6.706200e+04</td>\n",
       "      <td>120.000000</td>\n",
       "      <td>0.252100</td>\n",
       "      <td>244.000000</td>\n",
       "      <td>7833.333333</td>\n",
       "      <td>1478.166667</td>\n",
       "      <td>2016.910000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>41.217202</td>\n",
       "      <td>0.342351</td>\n",
       "      <td>3378.990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>35000.000000</td>\n",
       "      <td>0.289900</td>\n",
       "      <td>1445.460000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>9.500000e+06</td>\n",
       "      <td>380.530000</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>845.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>176.000000</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>86.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>2.904836e+06</td>\n",
       "      <td>1.930000</td>\n",
       "      <td>169.000000</td>\n",
       "      <td>65659.902030</td>\n",
       "      <td>65659.900000</td>\n",
       "      <td>35000.000000</td>\n",
       "      <td>30659.900000</td>\n",
       "      <td>1.115370e+03</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>555925.000000</td>\n",
       "      <td>159177.000000</td>\n",
       "      <td>562.000000</td>\n",
       "      <td>324.000000</td>\n",
       "      <td>263.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>176.000000</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>132.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>496651.000000</td>\n",
       "      <td>4.447397e+06</td>\n",
       "      <td>2.921551e+06</td>\n",
       "      <td>120.000000</td>\n",
       "      <td>3.805300</td>\n",
       "      <td>562.000000</td>\n",
       "      <td>791666.666667</td>\n",
       "      <td>42802.083333</td>\n",
       "      <td>43771.943333</td>\n",
       "      <td>35000.000000</td>\n",
       "      <td>545.233822</td>\n",
       "      <td>4.938340</td>\n",
       "      <td>30659.900000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         funded_amnt       int_rate    installment     emp_length  \\\n",
       "count  396607.000000  396607.000000  396607.000000  396607.000000   \n",
       "mean    15432.514113       0.125966     446.517832       6.110959   \n",
       "std      8586.000742       0.043243     245.390252       3.577283   \n",
       "min      1000.000000       0.053200      14.010000       1.000000   \n",
       "25%      9000.000000       0.091700     267.790000       2.000000   \n",
       "50%     14025.000000       0.122900     390.830000       6.000000   \n",
       "75%     20225.000000       0.155900     586.000000      10.000000   \n",
       "max     35000.000000       0.289900    1445.460000      10.000000   \n",
       "\n",
       "         annual_inc            dti    delinq_2yrs  fico_range_low  \\\n",
       "count  3.966070e+05  396607.000000  396607.000000   396607.000000   \n",
       "mean   7.850727e+04      19.065596       0.351681      693.606063   \n",
       "std    7.525791e+04       8.652028       0.933297       29.957265   \n",
       "min    3.800000e+03       0.000000       0.000000      660.000000   \n",
       "25%    4.800000e+04      12.550000       0.000000      670.000000   \n",
       "50%    6.590000e+04      18.510000       0.000000      685.000000   \n",
       "75%    9.400000e+04      25.210000       0.000000      710.000000   \n",
       "max    9.500000e+06     380.530000      39.000000      845.000000   \n",
       "\n",
       "       inq_last_6mths  mths_since_last_delinq       open_acc        pub_rec  \\\n",
       "count   396607.000000           205025.000000  396607.000000  396607.000000   \n",
       "mean         0.572229               33.838386      12.030105       0.227359   \n",
       "std          0.867657               21.959684       5.650937       0.644387   \n",
       "min          0.000000                0.000000       1.000000       0.000000   \n",
       "25%          0.000000               15.000000       8.000000       0.000000   \n",
       "50%          0.000000               30.000000      11.000000       0.000000   \n",
       "75%          1.000000               50.000000      15.000000       0.000000   \n",
       "max          6.000000              176.000000      90.000000      86.000000   \n",
       "\n",
       "       pub_rec_bankruptcies     revol_bal     revol_util      total_acc  \\\n",
       "count         396607.000000  3.966070e+05  396607.000000  396607.000000   \n",
       "mean               0.128530  1.786801e+04       0.540277      25.575736   \n",
       "std                0.380431  2.438064e+04       0.238760      12.108512   \n",
       "min                0.000000  0.000000e+00       0.000000       4.000000   \n",
       "25%                0.000000  6.573000e+03       0.363000      17.000000   \n",
       "50%                0.000000  1.214300e+04       0.544000      24.000000   \n",
       "75%                0.000000  2.165000e+04       0.723000      32.000000   \n",
       "max               11.000000  2.904836e+06       1.930000     169.000000   \n",
       "\n",
       "         total_pymnt  total_pymnt_inv  total_rec_prncp  total_rec_int  \\\n",
       "count  396607.000000    396607.000000    396607.000000  396607.000000   \n",
       "mean    16792.291190     16785.669719     13384.143746    3114.960832   \n",
       "std     10869.118986     10864.638855      8859.976340    3161.258740   \n",
       "min         0.000000         0.000000         0.000000       0.000000   \n",
       "25%      8309.014966      8307.370000      6023.450000    1006.730000   \n",
       "50%     14359.543590     14352.470000     12000.000000    2005.130000   \n",
       "75%     23129.849290     23121.495000     19750.000000    4128.455000   \n",
       "max     65659.902030     65659.900000     35000.000000   30659.900000   \n",
       "\n",
       "       total_rec_late_fee  collections_12_mths_ex_med  acc_now_delinq  \\\n",
       "count        3.966070e+05               396607.000000   396607.000000   \n",
       "mean         2.121773e+00                    0.020322        0.006127   \n",
       "std          1.488623e+01                    0.157995        0.086785   \n",
       "min         -5.100000e-09                    0.000000        0.000000   \n",
       "25%          0.000000e+00                    0.000000        0.000000   \n",
       "50%          0.000000e+00                    0.000000        0.000000   \n",
       "75%          0.000000e+00                    0.000000        0.000000   \n",
       "max          1.115370e+03                   14.000000       14.000000   \n",
       "\n",
       "       chargeoff_within_12_mths  acc_open_past_24mths    avg_cur_bal  \\\n",
       "count             396607.000000         396607.000000  396607.000000   \n",
       "mean                   0.010348              4.680979   13299.150461   \n",
       "std                    0.116421              3.200389   15779.469157   \n",
       "min                    0.000000              0.000000       0.000000   \n",
       "25%                    0.000000              2.000000    3294.000000   \n",
       "50%                    0.000000              4.000000    7454.000000   \n",
       "75%                    0.000000              6.000000   18240.000000   \n",
       "max                   10.000000             64.000000  555925.000000   \n",
       "\n",
       "         delinq_amnt  mo_sin_old_rev_tl_op  mo_sin_rcnt_rev_tl_op  \\\n",
       "count  396607.000000         396607.000000          396607.000000   \n",
       "mean       13.717350            182.348012              13.466399   \n",
       "std       784.327467             90.257651              17.077018   \n",
       "min         0.000000              3.000000               0.000000   \n",
       "25%         0.000000            119.000000               4.000000   \n",
       "50%         0.000000            167.000000               8.000000   \n",
       "75%         0.000000            232.000000              16.000000   \n",
       "max    159177.000000            562.000000             324.000000   \n",
       "\n",
       "       mo_sin_rcnt_tl       mort_acc  mths_since_recent_inq  \\\n",
       "count   396607.000000  396607.000000          396607.000000   \n",
       "mean         7.971692       1.676836               6.035272   \n",
       "std          9.018917       1.986882               5.958791   \n",
       "min          0.000000       0.000000               0.000000   \n",
       "25%          3.000000       0.000000               1.000000   \n",
       "50%          5.000000       1.000000               4.000000   \n",
       "75%         10.000000       3.000000               9.000000   \n",
       "max        263.000000      52.000000              25.000000   \n",
       "\n",
       "       mths_since_recent_revol_delinq  num_accts_ever_120_pd      num_il_tl  \\\n",
       "count                   143116.000000          396607.000000  396607.000000   \n",
       "mean                        35.618561               0.524640       8.800019   \n",
       "std                         22.591692               1.332438       7.524807   \n",
       "min                          0.000000               0.000000       0.000000   \n",
       "25%                         16.000000               0.000000       4.000000   \n",
       "50%                         32.000000               0.000000       7.000000   \n",
       "75%                         53.000000               0.000000      12.000000   \n",
       "max                        176.000000              39.000000     132.000000   \n",
       "\n",
       "       num_tl_120dpd_2m  pct_tl_nvr_dlq   tot_coll_amt   tot_cur_bal  \\\n",
       "count     396607.000000   396607.000000  396607.000000  3.966070e+05   \n",
       "mean           0.000736       93.923565     252.900748  1.427852e+05   \n",
       "std            0.029698        8.878326    2232.729788  1.565731e+05   \n",
       "min            0.000000        0.000000       0.000000  0.000000e+00   \n",
       "25%            0.000000       90.900000       0.000000  3.225300e+04   \n",
       "50%            0.000000       97.400000       0.000000  8.266800e+04   \n",
       "75%            0.000000      100.000000       0.000000  2.111530e+05   \n",
       "max            6.000000      100.000000  496651.000000  4.447397e+06   \n",
       "\n",
       "       total_bal_ex_mort  emp_length_months        dti_dec  \\\n",
       "count       3.966070e+05      396607.000000  396607.000000   \n",
       "mean        5.343058e+04          73.331504       0.190656   \n",
       "std         4.959436e+04          42.927394       0.086520   \n",
       "min         0.000000e+00          12.000000       0.000000   \n",
       "25%         2.347900e+04          24.000000       0.125500   \n",
       "50%         4.064900e+04          72.000000       0.185100   \n",
       "75%         6.706200e+04         120.000000       0.252100   \n",
       "max         2.921551e+06         120.000000       3.805300   \n",
       "\n",
       "       length_of_credit_history  monthly_income  monthly_debt_payments  \\\n",
       "count             396607.000000   396607.000000          396607.000000   \n",
       "mean                 196.642427     6542.272520            1150.506642   \n",
       "std                   86.219747     6271.492559             747.086392   \n",
       "min                   36.000000      316.666667               0.000000   \n",
       "25%                  137.000000     4000.000000             649.875000   \n",
       "50%                  180.000000     5491.666667            1001.201300   \n",
       "75%                  244.000000     7833.333333            1478.166667   \n",
       "max                  562.000000   791666.666667           42802.083333   \n",
       "\n",
       "       updated_monthly_debt_payments  lost_principle  total_payments  \\\n",
       "count                  396607.000000   396607.000000   396607.000000   \n",
       "mean                     1597.024474     2048.370368       37.340225   \n",
       "std                       876.180515     5244.105415       12.436093   \n",
       "min                        31.420000        0.000000        0.000000   \n",
       "25%                       988.763333        0.000000       33.511177   \n",
       "50%                      1426.733333        0.000000       35.901470   \n",
       "75%                      2016.910000        0.000000       41.217202   \n",
       "max                     43771.943333    35000.000000      545.233822   \n",
       "\n",
       "       post_loan_dti       net_gain  \n",
       "count  396607.000000  396607.000000  \n",
       "mean        0.269028    1068.712238  \n",
       "std         0.103904    5829.755112  \n",
       "min         0.000623  -35000.000000  \n",
       "25%         0.191339     490.955000  \n",
       "50%         0.263362    1482.530000  \n",
       "75%         0.342351    3378.990000  \n",
       "max         4.938340   30659.900000  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lending.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Analyze the loan purpose column, as there may be opportunities to condense the categories. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_purpose</th>\n",
       "      <th>count</th>\n",
       "      <th>per_of_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>236525</td>\n",
       "      <td>0.596371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>credit_card</td>\n",
       "      <td>95842</td>\n",
       "      <td>0.241655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>home_improvement</td>\n",
       "      <td>23423</td>\n",
       "      <td>0.059058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>other</td>\n",
       "      <td>17760</td>\n",
       "      <td>0.044780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>major_purchase</td>\n",
       "      <td>6995</td>\n",
       "      <td>0.017637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>medical</td>\n",
       "      <td>3598</td>\n",
       "      <td>0.009072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>car</td>\n",
       "      <td>3275</td>\n",
       "      <td>0.008258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>small_business</td>\n",
       "      <td>3252</td>\n",
       "      <td>0.008200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>moving</td>\n",
       "      <td>2268</td>\n",
       "      <td>0.005719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>vacation</td>\n",
       "      <td>2072</td>\n",
       "      <td>0.005224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>house</td>\n",
       "      <td>1380</td>\n",
       "      <td>0.003480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>renewable_energy</td>\n",
       "      <td>212</td>\n",
       "      <td>0.000535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>wedding</td>\n",
       "      <td>4</td>\n",
       "      <td>0.000010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>educational</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          loan_purpose   count  per_of_total\n",
       "0   debt_consolidation  236525      0.596371\n",
       "1          credit_card   95842      0.241655\n",
       "2     home_improvement   23423      0.059058\n",
       "3                other   17760      0.044780\n",
       "4       major_purchase    6995      0.017637\n",
       "5              medical    3598      0.009072\n",
       "6                  car    3275      0.008258\n",
       "7       small_business    3252      0.008200\n",
       "8               moving    2268      0.005719\n",
       "9             vacation    2072      0.005224\n",
       "10               house    1380      0.003480\n",
       "11    renewable_energy     212      0.000535\n",
       "12             wedding       4      0.000010\n",
       "13         educational       1      0.000003"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count the values in each column \n",
    "\n",
    "purpose_count = pd.DataFrame(lending['purpose'].value_counts())\n",
    "\n",
    "# resetting the index because the values in the purpose column will get set as the index\n",
    "\n",
    "purpose_df = purpose_count.reset_index()\n",
    "\n",
    "# fix the true index vs. categories columns \n",
    "\n",
    "purpose_df.rename(columns={'purpose':'count'}, inplace = True)\n",
    "purpose_df.rename(columns={'index':'loan_purpose'}, inplace = True)\n",
    "\n",
    "# next add % of total \n",
    "\n",
    "loan_count = len(lending)\n",
    "\n",
    "purpose_df['per_of_total'] = purpose_df['count'] / loan_count\n",
    "\n",
    "purpose_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Caveat: all of these purposes are self_reported, so they may not have much value. People could be reporting the category that they think makes it most likely that they'll get the loan, rather than their true purpose. \n",
    "* ~83.8% of the customers were in the debt consolidation or credit card category, which are just two different ways of saying the same thing, so we will consolidate those cateogies.  \n",
    "* ~90% of customers were taking out loans to consolidate debt or for home improvements. \n",
    "* The other categories are too small to be relevant from a modeling perspective, will combine the categories as follows, condensing 14 categories into three:\n",
    "    * debt consolidation & credit card into 'debt_consolidation'\n",
    "    * consumer_credit made up of home_improvement, major_purchase, car, house, vacation, moving \n",
    "    * rest will go into \"other\" \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# the code to condense categories will hot encode them at the same time \n",
    "\n",
    "lending.loc[:, 'debt_consolidation'] = lending.loc[:,'purpose'].apply(lambda x:\\\n",
    "            1 if x == 'debt_consolidation' else (1 if x == 'credit_card' else 0))\n",
    "\n",
    "lending.loc[:, 'consumer_credit'] = lending.loc[:,'purpose'].apply(lambda x: 1 if\\\n",
    "            x == 'home_improvement' else (1 if x == 'major_purchase' else 1 if x\\\n",
    "                                          == 'car' else 1 if x == 'wedding' else 1 if x == 'moving' else 1 if x\\\n",
    "                                          == 'house' else 1 if x == 'vacation' else  0))\n",
    "\n",
    "lending.loc[:, 'other'] = lending.loc[:,'purpose'].apply(lambda x: 1 if x\\\n",
    "            == 'other' else (1 if x == 'medical' else 1 if x == 'small_business' else\\\n",
    "                             1 if x == 'renewable_energy' else 1 if x == 'educational' else 0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>funded_amnt</th>\n",
       "      <th>term</th>\n",
       "      <th>int_rate</th>\n",
       "      <th>installment</th>\n",
       "      <th>grade</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>issue_d</th>\n",
       "      <th>loan_status</th>\n",
       "      <th>purpose</th>\n",
       "      <th>addr_state</th>\n",
       "      <th>dti</th>\n",
       "      <th>delinq_2yrs</th>\n",
       "      <th>earliest_cr_line</th>\n",
       "      <th>fico_range_low</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>mths_since_last_delinq</th>\n",
       "      <th>open_acc</th>\n",
       "      <th>pub_rec</th>\n",
       "      <th>pub_rec_bankruptcies</th>\n",
       "      <th>revol_bal</th>\n",
       "      <th>revol_util</th>\n",
       "      <th>total_acc</th>\n",
       "      <th>total_pymnt</th>\n",
       "      <th>total_pymnt_inv</th>\n",
       "      <th>total_rec_prncp</th>\n",
       "      <th>total_rec_int</th>\n",
       "      <th>total_rec_late_fee</th>\n",
       "      <th>collections_12_mths_ex_med</th>\n",
       "      <th>application_type</th>\n",
       "      <th>acc_now_delinq</th>\n",
       "      <th>chargeoff_within_12_mths</th>\n",
       "      <th>acc_open_past_24mths</th>\n",
       "      <th>avg_cur_bal</th>\n",
       "      <th>delinq_amnt</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>mo_sin_rcnt_rev_tl_op</th>\n",
       "      <th>mo_sin_rcnt_tl</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "      <th>mths_since_recent_revol_delinq</th>\n",
       "      <th>num_accts_ever_120_pd</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>num_tl_120dpd_2m</th>\n",
       "      <th>pct_tl_nvr_dlq</th>\n",
       "      <th>tot_coll_amt</th>\n",
       "      <th>tot_cur_bal</th>\n",
       "      <th>total_bal_ex_mort</th>\n",
       "      <th>emp_length_months</th>\n",
       "      <th>dti_dec</th>\n",
       "      <th>length_of_credit_history</th>\n",
       "      <th>monthly_income</th>\n",
       "      <th>monthly_debt_payments</th>\n",
       "      <th>updated_monthly_debt_payments</th>\n",
       "      <th>lost_principle</th>\n",
       "      <th>total_payments</th>\n",
       "      <th>post_loan_dti</th>\n",
       "      <th>net_gain</th>\n",
       "      <th>debt_consolidation</th>\n",
       "      <th>consumer_credit</th>\n",
       "      <th>other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>0.1485</td>\n",
       "      <td>691.84</td>\n",
       "      <td>C</td>\n",
       "      <td>6</td>\n",
       "      <td>RENT</td>\n",
       "      <td>110000.0</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>IL</td>\n",
       "      <td>12.45</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2007-06-01</td>\n",
       "      <td>690.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21374.0</td>\n",
       "      <td>0.845</td>\n",
       "      <td>12.0</td>\n",
       "      <td>24889.01336</td>\n",
       "      <td>24889.01</td>\n",
       "      <td>20000.00</td>\n",
       "      <td>4889.01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Individual</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5356.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>37491.0</td>\n",
       "      <td>37491.0</td>\n",
       "      <td>72</td>\n",
       "      <td>0.1245</td>\n",
       "      <td>102</td>\n",
       "      <td>9166.666667</td>\n",
       "      <td>1141.250000</td>\n",
       "      <td>1833.090000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>35.975100</td>\n",
       "      <td>0.199973</td>\n",
       "      <td>4889.01</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>0.1577</td>\n",
       "      <td>700.88</td>\n",
       "      <td>D</td>\n",
       "      <td>5</td>\n",
       "      <td>RENT</td>\n",
       "      <td>70000.0</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>house</td>\n",
       "      <td>FL</td>\n",
       "      <td>22.21</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2004-07-01</td>\n",
       "      <td>680.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19077.0</td>\n",
       "      <td>0.366</td>\n",
       "      <td>63.0</td>\n",
       "      <td>21780.58678</td>\n",
       "      <td>21780.59</td>\n",
       "      <td>20000.00</td>\n",
       "      <td>1780.59</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Individual</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2759.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>137.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>92.1</td>\n",
       "      <td>264.0</td>\n",
       "      <td>63456.0</td>\n",
       "      <td>63456.0</td>\n",
       "      <td>60</td>\n",
       "      <td>0.2221</td>\n",
       "      <td>137</td>\n",
       "      <td>5833.333333</td>\n",
       "      <td>1295.583333</td>\n",
       "      <td>1996.463333</td>\n",
       "      <td>0.00</td>\n",
       "      <td>31.076057</td>\n",
       "      <td>0.342251</td>\n",
       "      <td>1780.59</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>0.1797</td>\n",
       "      <td>253.78</td>\n",
       "      <td>D</td>\n",
       "      <td>2</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>55000.0</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>Charged Off</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>CO</td>\n",
       "      <td>35.70</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2001-04-01</td>\n",
       "      <td>685.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38623.0</td>\n",
       "      <td>0.780</td>\n",
       "      <td>28.0</td>\n",
       "      <td>5558.20000</td>\n",
       "      <td>5558.20</td>\n",
       "      <td>2687.15</td>\n",
       "      <td>2871.05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Individual</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>20578.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>288087.0</td>\n",
       "      <td>71518.0</td>\n",
       "      <td>24</td>\n",
       "      <td>0.3570</td>\n",
       "      <td>176</td>\n",
       "      <td>4583.333333</td>\n",
       "      <td>1636.250000</td>\n",
       "      <td>1890.030000</td>\n",
       "      <td>7312.85</td>\n",
       "      <td>21.901647</td>\n",
       "      <td>0.412370</td>\n",
       "      <td>-4441.80</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>0.0849</td>\n",
       "      <td>631.26</td>\n",
       "      <td>B</td>\n",
       "      <td>10</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>85000.0</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>major_purchase</td>\n",
       "      <td>SC</td>\n",
       "      <td>17.61</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1999-02-01</td>\n",
       "      <td>705.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>826.0</td>\n",
       "      <td>0.057</td>\n",
       "      <td>15.0</td>\n",
       "      <td>21538.50898</td>\n",
       "      <td>21538.51</td>\n",
       "      <td>20000.00</td>\n",
       "      <td>1538.51</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Individual</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>17700.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>141601.0</td>\n",
       "      <td>27937.0</td>\n",
       "      <td>120</td>\n",
       "      <td>0.1761</td>\n",
       "      <td>201</td>\n",
       "      <td>7083.333333</td>\n",
       "      <td>1247.375000</td>\n",
       "      <td>1878.635000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>34.119870</td>\n",
       "      <td>0.265219</td>\n",
       "      <td>1538.51</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>0.0649</td>\n",
       "      <td>306.45</td>\n",
       "      <td>A</td>\n",
       "      <td>6</td>\n",
       "      <td>RENT</td>\n",
       "      <td>85000.0</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>PA</td>\n",
       "      <td>13.07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2002-04-01</td>\n",
       "      <td>685.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10464.0</td>\n",
       "      <td>0.345</td>\n",
       "      <td>23.0</td>\n",
       "      <td>10998.97157</td>\n",
       "      <td>10998.97</td>\n",
       "      <td>10000.00</td>\n",
       "      <td>998.97</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Individual</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1997.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>95.7</td>\n",
       "      <td>8341.0</td>\n",
       "      <td>27957.0</td>\n",
       "      <td>27957.0</td>\n",
       "      <td>72</td>\n",
       "      <td>0.1307</td>\n",
       "      <td>164</td>\n",
       "      <td>7083.333333</td>\n",
       "      <td>925.791667</td>\n",
       "      <td>1232.241667</td>\n",
       "      <td>0.00</td>\n",
       "      <td>35.891570</td>\n",
       "      <td>0.173964</td>\n",
       "      <td>998.97</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   funded_amnt        term  int_rate  installment grade  emp_length  \\\n",
       "0      20000.0   36 months    0.1485       691.84     C           6   \n",
       "1      20000.0   36 months    0.1577       700.88     D           5   \n",
       "2      10000.0   60 months    0.1797       253.78     D           2   \n",
       "3      20000.0   36 months    0.0849       631.26     B          10   \n",
       "4      10000.0   36 months    0.0649       306.45     A           6   \n",
       "\n",
       "  home_ownership  annual_inc verification_status     issue_d  loan_status  \\\n",
       "0           RENT    110000.0        Not Verified  2015-12-01   Fully Paid   \n",
       "1           RENT     70000.0        Not Verified  2015-12-01   Fully Paid   \n",
       "2       MORTGAGE     55000.0        Not Verified  2015-12-01  Charged Off   \n",
       "3       MORTGAGE     85000.0        Not Verified  2015-12-01   Fully Paid   \n",
       "4           RENT     85000.0        Not Verified  2015-12-01   Fully Paid   \n",
       "\n",
       "          purpose addr_state    dti  delinq_2yrs earliest_cr_line  \\\n",
       "0     credit_card         IL  12.45          0.0       2007-06-01   \n",
       "1           house         FL  22.21          0.0       2004-07-01   \n",
       "2     credit_card         CO  35.70          0.0       2001-04-01   \n",
       "3  major_purchase         SC  17.61          1.0       1999-02-01   \n",
       "4     credit_card         PA  13.07          0.0       2002-04-01   \n",
       "\n",
       "   fico_range_low  inq_last_6mths  mths_since_last_delinq  open_acc  pub_rec  \\\n",
       "0           690.0             0.0                     NaN       8.0      0.0   \n",
       "1           680.0             0.0                    38.0      24.0      0.0   \n",
       "2           685.0             0.0                     NaN      14.0      0.0   \n",
       "3           705.0             0.0                     3.0       8.0      0.0   \n",
       "4           685.0             1.0                     NaN      14.0      1.0   \n",
       "\n",
       "   pub_rec_bankruptcies  revol_bal  revol_util  total_acc  total_pymnt  \\\n",
       "0                   0.0    21374.0       0.845       12.0  24889.01336   \n",
       "1                   0.0    19077.0       0.366       63.0  21780.58678   \n",
       "2                   0.0    38623.0       0.780       28.0   5558.20000   \n",
       "3                   0.0      826.0       0.057       15.0  21538.50898   \n",
       "4                   1.0    10464.0       0.345       23.0  10998.97157   \n",
       "\n",
       "   total_pymnt_inv  total_rec_prncp  total_rec_int  total_rec_late_fee  \\\n",
       "0         24889.01         20000.00        4889.01                 0.0   \n",
       "1         21780.59         20000.00        1780.59                 0.0   \n",
       "2          5558.20          2687.15        2871.05                 0.0   \n",
       "3         21538.51         20000.00        1538.51                 0.0   \n",
       "4         10998.97         10000.00         998.97                 0.0   \n",
       "\n",
       "   collections_12_mths_ex_med application_type  acc_now_delinq  \\\n",
       "0                         0.0       Individual             0.0   \n",
       "1                         0.0       Individual             0.0   \n",
       "2                         0.0       Individual             0.0   \n",
       "3                         0.0       Individual             0.0   \n",
       "4                         0.0       Individual             0.0   \n",
       "\n",
       "   chargeoff_within_12_mths  acc_open_past_24mths  avg_cur_bal  delinq_amnt  \\\n",
       "0                       0.0                   3.0       5356.0          0.0   \n",
       "1                       0.0                   9.0       2759.0          0.0   \n",
       "2                       0.0                   4.0      20578.0          0.0   \n",
       "3                       0.0                   4.0      17700.0          0.0   \n",
       "4                       0.0                   7.0       1997.0          0.0   \n",
       "\n",
       "   mo_sin_old_rev_tl_op  mo_sin_rcnt_rev_tl_op  mo_sin_rcnt_tl  mort_acc  \\\n",
       "0                 102.0                   16.0            10.0       0.0   \n",
       "1                 137.0                    2.0             2.0       1.0   \n",
       "2                 176.0                    7.0             6.0       6.0   \n",
       "3                  55.0                   32.0            13.0       3.0   \n",
       "4                 129.0                    1.0             1.0       1.0   \n",
       "\n",
       "   mths_since_recent_inq  mths_since_recent_revol_delinq  \\\n",
       "0                    9.0                             NaN   \n",
       "1                    9.0                             NaN   \n",
       "2                    6.0                             NaN   \n",
       "3                    8.0                             NaN   \n",
       "4                    1.0                             NaN   \n",
       "\n",
       "   num_accts_ever_120_pd  num_il_tl  num_tl_120dpd_2m  pct_tl_nvr_dlq  \\\n",
       "0                    0.0        1.0               0.0           100.0   \n",
       "1                    5.0       39.0               0.0            92.1   \n",
       "2                    0.0        6.0               0.0           100.0   \n",
       "3                    1.0        9.0               0.0            93.3   \n",
       "4                    0.0        3.0               0.0            95.7   \n",
       "\n",
       "   tot_coll_amt  tot_cur_bal  total_bal_ex_mort  emp_length_months  dti_dec  \\\n",
       "0           0.0      37491.0            37491.0                 72   0.1245   \n",
       "1         264.0      63456.0            63456.0                 60   0.2221   \n",
       "2           0.0     288087.0            71518.0                 24   0.3570   \n",
       "3           0.0     141601.0            27937.0                120   0.1761   \n",
       "4        8341.0      27957.0            27957.0                 72   0.1307   \n",
       "\n",
       "   length_of_credit_history  monthly_income  monthly_debt_payments  \\\n",
       "0                       102     9166.666667            1141.250000   \n",
       "1                       137     5833.333333            1295.583333   \n",
       "2                       176     4583.333333            1636.250000   \n",
       "3                       201     7083.333333            1247.375000   \n",
       "4                       164     7083.333333             925.791667   \n",
       "\n",
       "   updated_monthly_debt_payments  lost_principle  total_payments  \\\n",
       "0                    1833.090000            0.00       35.975100   \n",
       "1                    1996.463333            0.00       31.076057   \n",
       "2                    1890.030000         7312.85       21.901647   \n",
       "3                    1878.635000            0.00       34.119870   \n",
       "4                    1232.241667            0.00       35.891570   \n",
       "\n",
       "   post_loan_dti  net_gain  debt_consolidation  consumer_credit  other  \n",
       "0       0.199973   4889.01                   1                0      0  \n",
       "1       0.342251   1780.59                   0                1      0  \n",
       "2       0.412370  -4441.80                   1                0      0  \n",
       "3       0.265219   1538.51                   0                1      0  \n",
       "4       0.173964    998.97                   1                0      0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lending.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_status</th>\n",
       "      <th>count</th>\n",
       "      <th>per_of_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>312420</td>\n",
       "      <td>0.787732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Charged Off</td>\n",
       "      <td>74119</td>\n",
       "      <td>0.186883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Current</td>\n",
       "      <td>9572</td>\n",
       "      <td>0.024135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>In Grace Period</td>\n",
       "      <td>253</td>\n",
       "      <td>0.000638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Late (31-120 days)</td>\n",
       "      <td>156</td>\n",
       "      <td>0.000393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Late (16-30 days)</td>\n",
       "      <td>76</td>\n",
       "      <td>0.000192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Default</td>\n",
       "      <td>11</td>\n",
       "      <td>0.000028</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          loan_status   count  per_of_total\n",
       "0          Fully Paid  312420      0.787732\n",
       "1         Charged Off   74119      0.186883\n",
       "2             Current    9572      0.024135\n",
       "3     In Grace Period     253      0.000638\n",
       "4  Late (31-120 days)     156      0.000393\n",
       "5   Late (16-30 days)      76      0.000192\n",
       "6             Default      11      0.000028"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# fix loan status column \n",
    "\n",
    "status_count = pd.DataFrame(lending['loan_status'].value_counts())\n",
    "\n",
    "# reset and fix index and column labels \n",
    "status_df = status_count.reset_index()\n",
    "\n",
    "status_df.rename(columns={'loan_status':'count'}, inplace = True)\n",
    "status_df.rename(columns={'index':'loan_status'}, inplace = True)\n",
    "\n",
    "# next add % of total \n",
    "\n",
    "total_loans = len(lending)\n",
    "\n",
    "status_df['per_of_total'] = status_df['count'] / total_loans \n",
    "\n",
    "status_df\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* ~97.5% fall into either fully paid or charged off. \n",
    "* Given that we're just trying to identify customers that have either paid off their loans or the loans have been deemed uncollectible (i.e. \"charged off\") the other statuses aren't relevant\n",
    "* There is potential value in applying the model on the loans that are either current, in the grace period or have missed payments and then observing what happens to assess how accurate the model is at predicting current customers who will default/would need significant interventions to prevent defaults, but that's beyond the scope of this exercise. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(386539, 63)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# isolate just statuses of fully paid or charged off. \n",
    "\n",
    "statuses = ['Fully Paid', 'Charged Off']\n",
    "lending.loan_status.isin(statuses)\n",
    "lending = lending[lending.loan_status.isin(statuses)]\n",
    "\n",
    "lending.shape\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_status</th>\n",
       "      <th>count</th>\n",
       "      <th>per_of_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>312420</td>\n",
       "      <td>0.80825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Charged Off</td>\n",
       "      <td>74119</td>\n",
       "      <td>0.19175</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_status   count  per_of_total\n",
       "0   Fully Paid  312420       0.80825\n",
       "1  Charged Off   74119       0.19175"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's assess the charge off rate with just loans that been deeemed uncollectible\n",
    "# i.e. \"charged off\" vs fully paid \n",
    "\n",
    "status_count = pd.DataFrame(lending['loan_status'].value_counts())\n",
    "\n",
    "# reset and fix index and column labels \n",
    "status_df = status_count.reset_index()\n",
    "\n",
    "status_df.rename(columns={'loan_status':'count'}, inplace = True)\n",
    "status_df.rename(columns={'index':'loan_status'}, inplace = True)\n",
    "\n",
    "# next add % of total \n",
    "\n",
    "total_loans = len(lending)\n",
    "\n",
    "status_df['per_of_total'] = status_df['count'] / total_loans \n",
    "\n",
    "status_df\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A rather high 19.18% of the loans were defaulted on, which makes this a risky proposition for investors.\n",
    "\n",
    "If you were to invest 10k in a bucket of loans that roughly approximated the performance of all the loans in this dataset at the average interest rate, a lot of your gains would be offset by the losses on the bad loans. \n",
    "\n",
    "A more detailed analysis on potential gains and losses will follow once the data is fully cleaned and more high level EDA completed. \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(199926, 63)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# need to assess how to handle the NaNs in months since last delinquency\n",
    "# there are nearly 200k records with NaNs, suggesting this is not a data collection issue\n",
    "# but is more likely customers that simply don't have a delinquency on their credit report \n",
    "\n",
    "\n",
    "test = lending.dropna(subset=['mths_since_last_delinq'])\n",
    "\n",
    "test.shape\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_status</th>\n",
       "      <th>count</th>\n",
       "      <th>per_of_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>160253</td>\n",
       "      <td>0.801562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Charged Off</td>\n",
       "      <td>39673</td>\n",
       "      <td>0.198438</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_status   count  per_of_total\n",
       "0   Fully Paid  160253      0.801562\n",
       "1  Charged Off   39673      0.198438"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's look at the default rates in the group without NaNs \n",
    "\n",
    "\n",
    "status_count_test = pd.DataFrame(test['loan_status'].value_counts())\n",
    "\n",
    "# reset and fix index and column labels \n",
    "status_df_test = status_count_test.reset_index()\n",
    "\n",
    "status_df_test.rename(columns={'loan_status':'count'}, inplace = True)\n",
    "status_df_test.rename(columns={'index':'loan_status'}, inplace = True)\n",
    "\n",
    "# next add % of total \n",
    "\n",
    "total_loans_test = len(test)\n",
    "\n",
    "status_df_test['per_of_total'] = status_df_test['count'] / total_loans_test \n",
    "\n",
    "status_df_test\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Given that nearly 1/2 of our dataset has NaN values for months since last delinquency, we have to study this column further as dropping all of these rows would probably hurt our analysis, as would filling in the values with the average. We're going to study this more to make a higher quality inference around what these values should be. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(457, 63)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's check and see if there are any zero values \n",
    "\n",
    "zero_delinq = lending[(lending['mths_since_last_delinq'] == 0)]\n",
    "zero_delinq.shape\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_status</th>\n",
       "      <th>count</th>\n",
       "      <th>per_of_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fully Paid</td>\n",
       "      <td>152167</td>\n",
       "      <td>0.815415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Charged Off</td>\n",
       "      <td>34446</td>\n",
       "      <td>0.184585</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_status   count  per_of_total\n",
       "0   Fully Paid  152167      0.815415\n",
       "1  Charged Off   34446      0.184585"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's keep the rows with NaN values for months since last Delinquency \n",
    "# and see how they perform as far as paying off thier loans \n",
    "\n",
    "# let's look at the default rates in the group without NaNs \n",
    "\n",
    "count_null = lending[lending['mths_since_last_delinq'].isnull()]\n",
    "\n",
    "\n",
    "status_count_null = pd.DataFrame(count_null['loan_status'].value_counts())\n",
    "\n",
    "# reset and fix index and column labels \n",
    "status_df_null = status_count_null.reset_index()\n",
    "\n",
    "\n",
    "status_df_null.rename(columns={'loan_status':'count'}, inplace = True)\n",
    "status_df_null.rename(columns={'index':'loan_status'}, inplace = True)\n",
    "\n",
    "# next add % of total \n",
    "\n",
    "total_loans_null = len(count_null)\n",
    "\n",
    "status_df_null['per_of_total'] = status_df_null['count'] / total_loans_null\n",
    "\n",
    "status_df_null\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "703.0411064609647"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# since the charge off rates are rather close, let's see if there is a difference in average credit score\n",
    "\n",
    "\n",
    "count_null['fico_range_low'].mean()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "684.7605864169743"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['fico_range_low'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "693.5860417706881"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lending['fico_range_low'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The FICO scores are:\n",
    "\n",
    "* 703 for the customers with NaN for months since last delinquency \n",
    "* 684 for the customers without a NaN, nearly all of whom have a value for time since last delinquency\n",
    "* 693 for the customer group overall. \n",
    "\n",
    "The NaN group having a higher FICO does support the idea that since there wasn't a delinquency on their credit report, LC's systems simply didn't enter in a value and thus a NaN was generated. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.1231286405555883"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's look at interest rates for both groups as well \n",
    "\n",
    "count_null['int_rate'].mean()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.1278172303752388"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['int_rate'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2690.0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# see if the group with NaN have lower instances of having ever had a significantly past due accounts \n",
    "\n",
    "count_null['num_accts_ever_120_pd'].sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "201053.0"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['num_accts_ever_120_pd'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Findings Summary**\n",
    "\n",
    "* Nearly 1/2 of the total dataset has \"NaN\" for months since last delinquency \n",
    "* Default rates for both the NaN and non NaN cohorts are very close at 19.8% and 18.46% respectively\n",
    "* The credit scores for the group with NaN are nearly an entire tier higher, as they're nearly 20 points higher than the group without NaNs. \n",
    "* interest rates for the group without NaNs is about 1/2 a point higher.\n",
    "* The group without NaNs have had 201,053 instances (for 199k loans) of having an account seriously past due (120 days) vs. only 2,690 for the NaN group of roughly ~184k loans. \n",
    "\n",
    "Given the evidence, it's reasonable to presume that the group with NaNs have significantly better credit and payment history and that the NaNs represent not having had a delinquency. The solution for this will be as follows:\n",
    "\n",
    "* Remove the zeros as such a small number (457) that it won't hurt our analysis \n",
    "* fill in all the missing values with zeros \n",
    "* subtract 84 from all the other values and store that result in a column called \"countdown_zero_delinq\", as that way a value of 1 month since last delinquency isn't seen by the model as only slightly better than 1 and a lot worse than 75, when in fact 75 is a lot better than one and 0 is best of all  \n",
    "\n",
    "Note: using 84 as delinquencies only stay on one's credit report for 84 months"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# filter out the zero values \n",
    "\n",
    "lending = lending[(lending['mths_since_last_delinq'] != 0)]\n",
    "\n",
    "\n",
    "# fill in the NaNs with zeroes \n",
    "\n",
    "lending.loc[:,'mths_since_last_delinq'].fillna(0, inplace=True)\n",
    "\n",
    "\n",
    "# create the column with the countdown to zero delinquencies \n",
    "\n",
    "lending.loc[:, 'countdown_zero_delinq'] =\\\n",
    "lending.loc[:,'mths_since_last_delinq'].apply(lambda x: 0 if x == 0 else x - 84)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2, 64)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# see how many zeroes are in the months since recent revolving delinquency column \n",
    "\n",
    "zero_revol_delinq = lending[(lending['mths_since_recent_revol_delinq'] == 0)]\n",
    "zero_revol_delinq.shape\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# repeat the process above for the column 'mths_since_recent_revol_delinq' as the same idea applies, if\n",
    "# not more since there are even fewer zeroes (2 vs. 457)\n",
    "\n",
    "# filter out the zero values \n",
    "\n",
    "lending = lending[(lending['mths_since_recent_revol_delinq'] != 0)]\n",
    "\n",
    "\n",
    "# fill in the NaNs with zeroes \n",
    "\n",
    "lending.loc[:,'mths_since_recent_revol_delinq'].fillna(0, inplace=True)\n",
    "\n",
    "\n",
    "# create the column with the countdown to zero delinquencies \n",
    "\n",
    "lending.loc[:, 'countdown_zero_revol_delinq'] =\\\n",
    "lending.loc[:,'mths_since_recent_revol_delinq'].apply(lambda x: 0 if x == 0 else x - 84)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(386080, 65)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lending.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " \n",
      "Count total NaN at each column in a DataFrame : \n",
      "\n",
      " funded_amnt                       0\n",
      "term                              0\n",
      "int_rate                          0\n",
      "installment                       0\n",
      "grade                             0\n",
      "emp_length                        0\n",
      "home_ownership                    0\n",
      "annual_inc                        0\n",
      "verification_status               0\n",
      "issue_d                           0\n",
      "loan_status                       0\n",
      "purpose                           0\n",
      "addr_state                        0\n",
      "dti                               0\n",
      "delinq_2yrs                       0\n",
      "earliest_cr_line                  0\n",
      "fico_range_low                    0\n",
      "inq_last_6mths                    0\n",
      "mths_since_last_delinq            0\n",
      "open_acc                          0\n",
      "pub_rec                           0\n",
      "pub_rec_bankruptcies              0\n",
      "revol_bal                         0\n",
      "revol_util                        0\n",
      "total_acc                         0\n",
      "total_pymnt                       0\n",
      "total_pymnt_inv                   0\n",
      "total_rec_prncp                   0\n",
      "total_rec_int                     0\n",
      "total_rec_late_fee                0\n",
      "collections_12_mths_ex_med        0\n",
      "application_type                  0\n",
      "acc_now_delinq                    0\n",
      "chargeoff_within_12_mths          0\n",
      "acc_open_past_24mths              0\n",
      "avg_cur_bal                       0\n",
      "delinq_amnt                       0\n",
      "mo_sin_old_rev_tl_op              0\n",
      "mo_sin_rcnt_rev_tl_op             0\n",
      "mo_sin_rcnt_tl                    0\n",
      "mort_acc                          0\n",
      "mths_since_recent_inq             0\n",
      "mths_since_recent_revol_delinq    0\n",
      "num_accts_ever_120_pd             0\n",
      "num_il_tl                         0\n",
      "num_tl_120dpd_2m                  0\n",
      "pct_tl_nvr_dlq                    0\n",
      "tot_coll_amt                      0\n",
      "tot_cur_bal                       0\n",
      "total_bal_ex_mort                 0\n",
      "emp_length_months                 0\n",
      "dti_dec                           0\n",
      "length_of_credit_history          0\n",
      "monthly_income                    0\n",
      "monthly_debt_payments             0\n",
      "updated_monthly_debt_payments     0\n",
      "lost_principle                    0\n",
      "total_payments                    0\n",
      "post_loan_dti                     0\n",
      "net_gain                          0\n",
      "debt_consolidation                0\n",
      "consumer_credit                   0\n",
      "other                             0\n",
      "countdown_zero_delinq             0\n",
      "countdown_zero_revol_delinq       0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# evaluate the rest of the columns for potential NaNs \n",
    "\n",
    "\n",
    "print(\" \\nCount total NaN at each column in a DataFrame : \\n\\n\", \n",
    "      lending.isnull().sum()) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the original pass the following columns all had the exact same number of NaNs 367,120:\n",
    "* all_util\n",
    "* inq_last_12m\n",
    "* max_bal_bc\n",
    "* open_acc_6m\n",
    "* open_il_12m\n",
    "* open_act_il \n",
    "\n",
    "Given the consistency of the number of NaNs it stands to reason that this data field wasn't being tracked at the time 90% of this data was generated. Since it's not present for 367k out of 384k records there is no value in adding zero, so I updated the pre-processing file not to select these columns.  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(386080, 65)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# see the shape of the final data frame \n",
    "\n",
    "lending.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>funded_amnt</th>\n",
       "      <th>int_rate</th>\n",
       "      <th>installment</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>dti</th>\n",
       "      <th>delinq_2yrs</th>\n",
       "      <th>fico_range_low</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>mths_since_last_delinq</th>\n",
       "      <th>open_acc</th>\n",
       "      <th>pub_rec</th>\n",
       "      <th>pub_rec_bankruptcies</th>\n",
       "      <th>revol_bal</th>\n",
       "      <th>revol_util</th>\n",
       "      <th>total_acc</th>\n",
       "      <th>total_pymnt</th>\n",
       "      <th>total_pymnt_inv</th>\n",
       "      <th>total_rec_prncp</th>\n",
       "      <th>total_rec_int</th>\n",
       "      <th>total_rec_late_fee</th>\n",
       "      <th>collections_12_mths_ex_med</th>\n",
       "      <th>acc_now_delinq</th>\n",
       "      <th>chargeoff_within_12_mths</th>\n",
       "      <th>acc_open_past_24mths</th>\n",
       "      <th>avg_cur_bal</th>\n",
       "      <th>delinq_amnt</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>mo_sin_rcnt_rev_tl_op</th>\n",
       "      <th>mo_sin_rcnt_tl</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "      <th>mths_since_recent_revol_delinq</th>\n",
       "      <th>num_accts_ever_120_pd</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>num_tl_120dpd_2m</th>\n",
       "      <th>pct_tl_nvr_dlq</th>\n",
       "      <th>tot_coll_amt</th>\n",
       "      <th>tot_cur_bal</th>\n",
       "      <th>total_bal_ex_mort</th>\n",
       "      <th>emp_length_months</th>\n",
       "      <th>dti_dec</th>\n",
       "      <th>length_of_credit_history</th>\n",
       "      <th>monthly_income</th>\n",
       "      <th>monthly_debt_payments</th>\n",
       "      <th>updated_monthly_debt_payments</th>\n",
       "      <th>lost_principle</th>\n",
       "      <th>total_payments</th>\n",
       "      <th>post_loan_dti</th>\n",
       "      <th>net_gain</th>\n",
       "      <th>debt_consolidation</th>\n",
       "      <th>consumer_credit</th>\n",
       "      <th>other</th>\n",
       "      <th>countdown_zero_delinq</th>\n",
       "      <th>countdown_zero_revol_delinq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>3.860800e+05</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>3.860800e+05</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>3.860800e+05</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>3.860800e+05</td>\n",
       "      <td>3.860800e+05</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "      <td>386080.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>15307.366478</td>\n",
       "      <td>0.125536</td>\n",
       "      <td>445.786265</td>\n",
       "      <td>6.102082</td>\n",
       "      <td>7.835030e+04</td>\n",
       "      <td>19.008054</td>\n",
       "      <td>0.349205</td>\n",
       "      <td>693.605432</td>\n",
       "      <td>0.575303</td>\n",
       "      <td>17.552518</td>\n",
       "      <td>12.014010</td>\n",
       "      <td>0.228973</td>\n",
       "      <td>0.129945</td>\n",
       "      <td>1.771811e+04</td>\n",
       "      <td>0.538706</td>\n",
       "      <td>25.576326</td>\n",
       "      <td>16521.146714</td>\n",
       "      <td>16514.811016</td>\n",
       "      <td>13238.893420</td>\n",
       "      <td>2981.649551</td>\n",
       "      <td>2.031257e+00</td>\n",
       "      <td>0.020327</td>\n",
       "      <td>0.004784</td>\n",
       "      <td>0.010311</td>\n",
       "      <td>4.699456</td>\n",
       "      <td>13262.583475</td>\n",
       "      <td>12.343926</td>\n",
       "      <td>181.790305</td>\n",
       "      <td>13.376520</td>\n",
       "      <td>7.936687</td>\n",
       "      <td>1.674327</td>\n",
       "      <td>6.025640</td>\n",
       "      <td>12.900000</td>\n",
       "      <td>0.527025</td>\n",
       "      <td>8.799938</td>\n",
       "      <td>0.000658</td>\n",
       "      <td>93.924865</td>\n",
       "      <td>254.913088</td>\n",
       "      <td>1.422298e+05</td>\n",
       "      <td>5.317361e+04</td>\n",
       "      <td>73.224990</td>\n",
       "      <td>0.190081</td>\n",
       "      <td>196.101049</td>\n",
       "      <td>6529.192061</td>\n",
       "      <td>1144.250805</td>\n",
       "      <td>1590.037069</td>\n",
       "      <td>2068.473058</td>\n",
       "      <td>36.819191</td>\n",
       "      <td>0.268446</td>\n",
       "      <td>915.207750</td>\n",
       "      <td>0.837212</td>\n",
       "      <td>0.099715</td>\n",
       "      <td>0.063072</td>\n",
       "      <td>-25.845814</td>\n",
       "      <td>-17.388127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>8581.671296</td>\n",
       "      <td>0.043296</td>\n",
       "      <td>246.847515</td>\n",
       "      <td>3.577298</td>\n",
       "      <td>7.534039e+04</td>\n",
       "      <td>8.640392</td>\n",
       "      <td>0.931179</td>\n",
       "      <td>30.015812</td>\n",
       "      <td>0.870252</td>\n",
       "      <td>23.168981</td>\n",
       "      <td>5.643891</td>\n",
       "      <td>0.645457</td>\n",
       "      <td>0.382094</td>\n",
       "      <td>2.413611e+04</td>\n",
       "      <td>0.238768</td>\n",
       "      <td>12.120637</td>\n",
       "      <td>10750.914315</td>\n",
       "      <td>10746.779102</td>\n",
       "      <td>8859.566281</td>\n",
       "      <td>3020.294310</td>\n",
       "      <td>1.406705e+01</td>\n",
       "      <td>0.157691</td>\n",
       "      <td>0.076783</td>\n",
       "      <td>0.116233</td>\n",
       "      <td>3.206543</td>\n",
       "      <td>15784.976601</td>\n",
       "      <td>734.823896</td>\n",
       "      <td>90.137581</td>\n",
       "      <td>16.942333</td>\n",
       "      <td>8.976146</td>\n",
       "      <td>1.989637</td>\n",
       "      <td>5.950307</td>\n",
       "      <td>21.877108</td>\n",
       "      <td>1.335718</td>\n",
       "      <td>7.530607</td>\n",
       "      <td>0.027961</td>\n",
       "      <td>8.878989</td>\n",
       "      <td>2244.523049</td>\n",
       "      <td>1.564473e+05</td>\n",
       "      <td>4.930936e+04</td>\n",
       "      <td>42.927577</td>\n",
       "      <td>0.086404</td>\n",
       "      <td>86.136177</td>\n",
       "      <td>6278.365865</td>\n",
       "      <td>743.807738</td>\n",
       "      <td>874.139569</td>\n",
       "      <td>5307.394348</td>\n",
       "      <td>12.161359</td>\n",
       "      <td>0.103852</td>\n",
       "      <td>5794.860137</td>\n",
       "      <td>0.369172</td>\n",
       "      <td>0.299620</td>\n",
       "      <td>0.243093</td>\n",
       "      <td>29.555443</td>\n",
       "      <td>26.826744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1000.000000</td>\n",
       "      <td>0.053200</td>\n",
       "      <td>14.010000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.800000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>660.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-5.100000e-09</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>316.666667</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>31.420000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000623</td>\n",
       "      <td>-35000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-83.000000</td>\n",
       "      <td>-83.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>8800.000000</td>\n",
       "      <td>0.091700</td>\n",
       "      <td>266.207500</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>4.800000e+04</td>\n",
       "      <td>12.500000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>670.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.511000e+03</td>\n",
       "      <td>0.361000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>8144.207502</td>\n",
       "      <td>8141.602500</td>\n",
       "      <td>6000.000000</td>\n",
       "      <td>989.107500</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>3274.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>119.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>90.900000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.199475e+04</td>\n",
       "      <td>2.333775e+04</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>0.125000</td>\n",
       "      <td>136.000000</td>\n",
       "      <td>4000.000000</td>\n",
       "      <td>645.900000</td>\n",
       "      <td>983.338333</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>33.393406</td>\n",
       "      <td>0.190758</td>\n",
       "      <td>462.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-55.000000</td>\n",
       "      <td>-37.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>14000.000000</td>\n",
       "      <td>0.122900</td>\n",
       "      <td>388.130000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>6.500000e+04</td>\n",
       "      <td>18.440000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>685.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.202500e+04</td>\n",
       "      <td>0.542000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>14017.494895</td>\n",
       "      <td>14012.135000</td>\n",
       "      <td>11550.000000</td>\n",
       "      <td>1943.070000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>7392.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>167.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>97.400000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.178050e+04</td>\n",
       "      <td>4.041900e+04</td>\n",
       "      <td>72.000000</td>\n",
       "      <td>0.184400</td>\n",
       "      <td>180.000000</td>\n",
       "      <td>5416.666667</td>\n",
       "      <td>995.450000</td>\n",
       "      <td>1419.653333</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>35.863795</td>\n",
       "      <td>0.262701</td>\n",
       "      <td>1423.620000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-8.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>20000.000000</td>\n",
       "      <td>0.155900</td>\n",
       "      <td>586.240000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>9.350000e+04</td>\n",
       "      <td>25.140000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>710.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.144300e+04</td>\n",
       "      <td>0.722000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>22769.788688</td>\n",
       "      <td>22760.797500</td>\n",
       "      <td>19200.000000</td>\n",
       "      <td>3910.562500</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>18185.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>232.000000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.102302e+05</td>\n",
       "      <td>6.675300e+04</td>\n",
       "      <td>120.000000</td>\n",
       "      <td>0.251400</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>7791.666667</td>\n",
       "      <td>1470.500000</td>\n",
       "      <td>2008.730000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>36.196862</td>\n",
       "      <td>0.341724</td>\n",
       "      <td>3180.822500</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>35000.000000</td>\n",
       "      <td>0.289900</td>\n",
       "      <td>1445.460000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>9.500000e+06</td>\n",
       "      <td>380.530000</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>845.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>176.000000</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>86.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>2.904836e+06</td>\n",
       "      <td>1.930000</td>\n",
       "      <td>169.000000</td>\n",
       "      <td>65659.902030</td>\n",
       "      <td>65659.900000</td>\n",
       "      <td>35000.000000</td>\n",
       "      <td>30659.900000</td>\n",
       "      <td>1.098360e+03</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>555925.000000</td>\n",
       "      <td>94521.000000</td>\n",
       "      <td>562.000000</td>\n",
       "      <td>324.000000</td>\n",
       "      <td>263.000000</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>176.000000</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>132.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>496651.000000</td>\n",
       "      <td>4.447397e+06</td>\n",
       "      <td>2.921551e+06</td>\n",
       "      <td>120.000000</td>\n",
       "      <td>3.805300</td>\n",
       "      <td>562.000000</td>\n",
       "      <td>791666.666667</td>\n",
       "      <td>42802.083333</td>\n",
       "      <td>43771.943333</td>\n",
       "      <td>35000.000000</td>\n",
       "      <td>545.233822</td>\n",
       "      <td>4.938340</td>\n",
       "      <td>30659.900000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>92.000000</td>\n",
       "      <td>92.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         funded_amnt       int_rate    installment     emp_length  \\\n",
       "count  386080.000000  386080.000000  386080.000000  386080.000000   \n",
       "mean    15307.366478       0.125536     445.786265       6.102082   \n",
       "std      8581.671296       0.043296     246.847515       3.577298   \n",
       "min      1000.000000       0.053200      14.010000       1.000000   \n",
       "25%      8800.000000       0.091700     266.207500       2.000000   \n",
       "50%     14000.000000       0.122900     388.130000       6.000000   \n",
       "75%     20000.000000       0.155900     586.240000      10.000000   \n",
       "max     35000.000000       0.289900    1445.460000      10.000000   \n",
       "\n",
       "         annual_inc            dti    delinq_2yrs  fico_range_low  \\\n",
       "count  3.860800e+05  386080.000000  386080.000000   386080.000000   \n",
       "mean   7.835030e+04      19.008054       0.349205      693.605432   \n",
       "std    7.534039e+04       8.640392       0.931179       30.015812   \n",
       "min    3.800000e+03       0.000000       0.000000      660.000000   \n",
       "25%    4.800000e+04      12.500000       0.000000      670.000000   \n",
       "50%    6.500000e+04      18.440000       0.000000      685.000000   \n",
       "75%    9.350000e+04      25.140000       0.000000      710.000000   \n",
       "max    9.500000e+06     380.530000      39.000000      845.000000   \n",
       "\n",
       "       inq_last_6mths  mths_since_last_delinq       open_acc        pub_rec  \\\n",
       "count   386080.000000           386080.000000  386080.000000  386080.000000   \n",
       "mean         0.575303               17.552518      12.014010       0.228973   \n",
       "std          0.870252               23.168981       5.643891       0.645457   \n",
       "min          0.000000                0.000000       1.000000       0.000000   \n",
       "25%          0.000000                0.000000       8.000000       0.000000   \n",
       "50%          0.000000                4.000000      11.000000       0.000000   \n",
       "75%          1.000000               32.000000      15.000000       0.000000   \n",
       "max          6.000000              176.000000      90.000000      86.000000   \n",
       "\n",
       "       pub_rec_bankruptcies     revol_bal     revol_util      total_acc  \\\n",
       "count         386080.000000  3.860800e+05  386080.000000  386080.000000   \n",
       "mean               0.129945  1.771811e+04       0.538706      25.576326   \n",
       "std                0.382094  2.413611e+04       0.238768      12.120637   \n",
       "min                0.000000  0.000000e+00       0.000000       4.000000   \n",
       "25%                0.000000  6.511000e+03       0.361000      17.000000   \n",
       "50%                0.000000  1.202500e+04       0.542000      24.000000   \n",
       "75%                0.000000  2.144300e+04       0.722000      32.000000   \n",
       "max               11.000000  2.904836e+06       1.930000     169.000000   \n",
       "\n",
       "         total_pymnt  total_pymnt_inv  total_rec_prncp  total_rec_int  \\\n",
       "count  386080.000000    386080.000000    386080.000000  386080.000000   \n",
       "mean    16521.146714     16514.811016     13238.893420    2981.649551   \n",
       "std     10750.914315     10746.779102      8859.566281    3020.294310   \n",
       "min         0.000000         0.000000         0.000000       0.000000   \n",
       "25%      8144.207502      8141.602500      6000.000000     989.107500   \n",
       "50%     14017.494895     14012.135000     11550.000000    1943.070000   \n",
       "75%     22769.788688     22760.797500     19200.000000    3910.562500   \n",
       "max     65659.902030     65659.900000     35000.000000   30659.900000   \n",
       "\n",
       "       total_rec_late_fee  collections_12_mths_ex_med  acc_now_delinq  \\\n",
       "count        3.860800e+05               386080.000000   386080.000000   \n",
       "mean         2.031257e+00                    0.020327        0.004784   \n",
       "std          1.406705e+01                    0.157691        0.076783   \n",
       "min         -5.100000e-09                    0.000000        0.000000   \n",
       "25%          0.000000e+00                    0.000000        0.000000   \n",
       "50%          0.000000e+00                    0.000000        0.000000   \n",
       "75%          0.000000e+00                    0.000000        0.000000   \n",
       "max          1.098360e+03                   14.000000       14.000000   \n",
       "\n",
       "       chargeoff_within_12_mths  acc_open_past_24mths    avg_cur_bal  \\\n",
       "count             386080.000000         386080.000000  386080.000000   \n",
       "mean                   0.010311              4.699456   13262.583475   \n",
       "std                    0.116233              3.206543   15784.976601   \n",
       "min                    0.000000              0.000000       0.000000   \n",
       "25%                    0.000000              2.000000    3274.000000   \n",
       "50%                    0.000000              4.000000    7392.000000   \n",
       "75%                    0.000000              6.000000   18185.000000   \n",
       "max                   10.000000             64.000000  555925.000000   \n",
       "\n",
       "         delinq_amnt  mo_sin_old_rev_tl_op  mo_sin_rcnt_rev_tl_op  \\\n",
       "count  386080.000000         386080.000000          386080.000000   \n",
       "mean       12.343926            181.790305              13.376520   \n",
       "std       734.823896             90.137581              16.942333   \n",
       "min         0.000000              3.000000               0.000000   \n",
       "25%         0.000000            119.000000               4.000000   \n",
       "50%         0.000000            167.000000               8.000000   \n",
       "75%         0.000000            232.000000              16.000000   \n",
       "max     94521.000000            562.000000             324.000000   \n",
       "\n",
       "       mo_sin_rcnt_tl       mort_acc  mths_since_recent_inq  \\\n",
       "count   386080.000000  386080.000000          386080.000000   \n",
       "mean         7.936687       1.674327               6.025640   \n",
       "std          8.976146       1.989637               5.950307   \n",
       "min          0.000000       0.000000               0.000000   \n",
       "25%          3.000000       0.000000               1.000000   \n",
       "50%          5.000000       1.000000               4.000000   \n",
       "75%         10.000000       3.000000               9.000000   \n",
       "max        263.000000      52.000000              25.000000   \n",
       "\n",
       "       mths_since_recent_revol_delinq  num_accts_ever_120_pd      num_il_tl  \\\n",
       "count                   386080.000000          386080.000000  386080.000000   \n",
       "mean                        12.900000               0.527025       8.799938   \n",
       "std                         21.877108               1.335718       7.530607   \n",
       "min                          0.000000               0.000000       0.000000   \n",
       "25%                          0.000000               0.000000       4.000000   \n",
       "50%                          0.000000               0.000000       7.000000   \n",
       "75%                         20.000000               0.000000      12.000000   \n",
       "max                        176.000000              39.000000     132.000000   \n",
       "\n",
       "       num_tl_120dpd_2m  pct_tl_nvr_dlq   tot_coll_amt   tot_cur_bal  \\\n",
       "count     386080.000000   386080.000000  386080.000000  3.860800e+05   \n",
       "mean           0.000658       93.924865     254.913088  1.422298e+05   \n",
       "std            0.027961        8.878989    2244.523049  1.564473e+05   \n",
       "min            0.000000        0.000000       0.000000  0.000000e+00   \n",
       "25%            0.000000       90.900000       0.000000  3.199475e+04   \n",
       "50%            0.000000       97.400000       0.000000  8.178050e+04   \n",
       "75%            0.000000      100.000000       0.000000  2.102302e+05   \n",
       "max            6.000000      100.000000  496651.000000  4.447397e+06   \n",
       "\n",
       "       total_bal_ex_mort  emp_length_months        dti_dec  \\\n",
       "count       3.860800e+05      386080.000000  386080.000000   \n",
       "mean        5.317361e+04          73.224990       0.190081   \n",
       "std         4.930936e+04          42.927577       0.086404   \n",
       "min         0.000000e+00          12.000000       0.000000   \n",
       "25%         2.333775e+04          24.000000       0.125000   \n",
       "50%         4.041900e+04          72.000000       0.184400   \n",
       "75%         6.675300e+04         120.000000       0.251400   \n",
       "max         2.921551e+06         120.000000       3.805300   \n",
       "\n",
       "       length_of_credit_history  monthly_income  monthly_debt_payments  \\\n",
       "count             386080.000000   386080.000000          386080.000000   \n",
       "mean                 196.101049     6529.192061            1144.250805   \n",
       "std                   86.136177     6278.365865             743.807738   \n",
       "min                   36.000000      316.666667               0.000000   \n",
       "25%                  136.000000     4000.000000             645.900000   \n",
       "50%                  180.000000     5416.666667             995.450000   \n",
       "75%                  243.000000     7791.666667            1470.500000   \n",
       "max                  562.000000   791666.666667           42802.083333   \n",
       "\n",
       "       updated_monthly_debt_payments  lost_principle  total_payments  \\\n",
       "count                  386080.000000   386080.000000   386080.000000   \n",
       "mean                     1590.037069     2068.473058       36.819191   \n",
       "std                       874.139569     5307.394348       12.161359   \n",
       "min                        31.420000        0.000000        0.000000   \n",
       "25%                       983.338333        0.000000       33.393406   \n",
       "50%                      1419.653333        0.000000       35.863795   \n",
       "75%                      2008.730000        0.000000       36.196862   \n",
       "max                     43771.943333    35000.000000      545.233822   \n",
       "\n",
       "       post_loan_dti       net_gain  debt_consolidation  consumer_credit  \\\n",
       "count  386080.000000  386080.000000       386080.000000    386080.000000   \n",
       "mean        0.268446     915.207750            0.837212         0.099715   \n",
       "std         0.103852    5794.860137            0.369172         0.299620   \n",
       "min         0.000623  -35000.000000            0.000000         0.000000   \n",
       "25%         0.190758     462.000000            1.000000         0.000000   \n",
       "50%         0.262701    1423.620000            1.000000         0.000000   \n",
       "75%         0.341724    3180.822500            1.000000         0.000000   \n",
       "max         4.938340   30659.900000            1.000000         1.000000   \n",
       "\n",
       "               other  countdown_zero_delinq  countdown_zero_revol_delinq  \n",
       "count  386080.000000          386080.000000                386080.000000  \n",
       "mean        0.063072             -25.845814                   -17.388127  \n",
       "std         0.243093              29.555443                    26.826744  \n",
       "min         0.000000             -83.000000                   -83.000000  \n",
       "25%         0.000000             -55.000000                   -37.000000  \n",
       "50%         0.000000              -8.000000                     0.000000  \n",
       "75%         0.000000               0.000000                     0.000000  \n",
       "max         1.000000              92.000000                    92.000000  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# do another round of describe to see what the updated high level numbers are\n",
    "\n",
    "lending.describe()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write the final data frame to csv file for use in the machine learning and additional EDA notebooks\n",
    "\n",
    "lending.to_csv('data/LC_2015_clean(4)_updated_April2022.csv', date_format='%Y-%m-%d', index=False)\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "GeneralDS",
   "language": "python",
   "name": "generalds"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}