etl_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9c3e88db",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b5409b01",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8b56f4c1",
   "metadata": {
    "scrolled": false
   },
   "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>id</th>\n",
       "      <th>loan_amnt</th>\n",
       "      <th>funded_amnt</th>\n",
       "      <th>funded_amnt_inv</th>\n",
       "      <th>term</th>\n",
       "      <th>int_rate</th>\n",
       "      <th>installment</th>\n",
       "      <th>grade</th>\n",
       "      <th>sub_grade</th>\n",
       "      <th>emp_title</th>\n",
       "      <th>...</th>\n",
       "      <th>hardship_start_date</th>\n",
       "      <th>hardship_end_date</th>\n",
       "      <th>payment_plan_start_date</th>\n",
       "      <th>hardship_length</th>\n",
       "      <th>hardship_dpd</th>\n",
       "      <th>hardship_loan_status</th>\n",
       "      <th>orig_projected_additional_accrued_interest</th>\n",
       "      <th>hardship_payoff_balance_amount</th>\n",
       "      <th>hardship_last_payment_amount</th>\n",
       "      <th>debt_settlement_flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1077501</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>4975.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>10.65%</td>\n",
       "      <td>162.87</td>\n",
       "      <td>B</td>\n",
       "      <td>B2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1077430</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>15.27%</td>\n",
       "      <td>59.83</td>\n",
       "      <td>C</td>\n",
       "      <td>C4</td>\n",
       "      <td>Ryder</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1077175</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>15.96%</td>\n",
       "      <td>84.33</td>\n",
       "      <td>C</td>\n",
       "      <td>C5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1076863</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>13.49%</td>\n",
       "      <td>339.31</td>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>AIR RESOURCES BOARD</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1075358</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>12.69%</td>\n",
       "      <td>67.79</td>\n",
       "      <td>B</td>\n",
       "      <td>B5</td>\n",
       "      <td>University Medical Group</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2925488</th>\n",
       "      <td>102556443</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>23.99%</td>\n",
       "      <td>690.30</td>\n",
       "      <td>E</td>\n",
       "      <td>E2</td>\n",
       "      <td>Senior Escrow Officer</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2925489</th>\n",
       "      <td>102653304</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>7.99%</td>\n",
       "      <td>313.32</td>\n",
       "      <td>A</td>\n",
       "      <td>A5</td>\n",
       "      <td>Rn</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2925490</th>\n",
       "      <td>102628603</td>\n",
       "      <td>10050.0</td>\n",
       "      <td>10050.0</td>\n",
       "      <td>10050.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>16.99%</td>\n",
       "      <td>358.26</td>\n",
       "      <td>D</td>\n",
       "      <td>D1</td>\n",
       "      <td>Sales Associate</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2925491</th>\n",
       "      <td>102196576</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>11.44%</td>\n",
       "      <td>197.69</td>\n",
       "      <td>B</td>\n",
       "      <td>B4</td>\n",
       "      <td>Contact input</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2925492</th>\n",
       "      <td>99799684</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>25.49%</td>\n",
       "      <td>889.18</td>\n",
       "      <td>E</td>\n",
       "      <td>E4</td>\n",
       "      <td>Assistant Manager</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2925493 rows × 141 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                id  loan_amnt  funded_amnt  funded_amnt_inv        term  \\\n",
       "0          1077501     5000.0       5000.0           4975.0   36 months   \n",
       "1          1077430     2500.0       2500.0           2500.0   60 months   \n",
       "2          1077175     2400.0       2400.0           2400.0   36 months   \n",
       "3          1076863    10000.0      10000.0          10000.0   36 months   \n",
       "4          1075358     3000.0       3000.0           3000.0   60 months   \n",
       "...            ...        ...          ...              ...         ...   \n",
       "2925488  102556443    24000.0      24000.0          24000.0   60 months   \n",
       "2925489  102653304    10000.0      10000.0          10000.0   36 months   \n",
       "2925490  102628603    10050.0      10050.0          10050.0   36 months   \n",
       "2925491  102196576     6000.0       6000.0           6000.0   36 months   \n",
       "2925492   99799684    30000.0      30000.0          30000.0   60 months   \n",
       "\n",
       "        int_rate  installment grade sub_grade                 emp_title  ...  \\\n",
       "0         10.65%       162.87     B        B2                       NaN  ...   \n",
       "1         15.27%        59.83     C        C4                     Ryder  ...   \n",
       "2         15.96%        84.33     C        C5                       NaN  ...   \n",
       "3         13.49%       339.31     C        C1       AIR RESOURCES BOARD  ...   \n",
       "4         12.69%        67.79     B        B5  University Medical Group  ...   \n",
       "...          ...          ...   ...       ...                       ...  ...   \n",
       "2925488   23.99%       690.30     E        E2     Senior Escrow Officer  ...   \n",
       "2925489    7.99%       313.32     A        A5                        Rn  ...   \n",
       "2925490   16.99%       358.26     D        D1           Sales Associate  ...   \n",
       "2925491   11.44%       197.69     B        B4             Contact input  ...   \n",
       "2925492   25.49%       889.18     E        E4         Assistant Manager  ...   \n",
       "\n",
       "        hardship_start_date hardship_end_date  payment_plan_start_date  \\\n",
       "0                       NaN               NaN                      NaN   \n",
       "1                       NaN               NaN                      NaN   \n",
       "2                       NaN               NaN                      NaN   \n",
       "3                       NaN               NaN                      NaN   \n",
       "4                       NaN               NaN                      NaN   \n",
       "...                     ...               ...                      ...   \n",
       "2925488                 NaN               NaN                      NaN   \n",
       "2925489                 NaN               NaN                      NaN   \n",
       "2925490                 NaN               NaN                      NaN   \n",
       "2925491                 NaN               NaN                      NaN   \n",
       "2925492                 NaN               NaN                      NaN   \n",
       "\n",
       "        hardship_length hardship_dpd hardship_loan_status  \\\n",
       "0                   NaN          NaN                  NaN   \n",
       "1                   NaN          NaN                  NaN   \n",
       "2                   NaN          NaN                  NaN   \n",
       "3                   NaN          NaN                  NaN   \n",
       "4                   NaN          NaN                  NaN   \n",
       "...                 ...          ...                  ...   \n",
       "2925488             NaN          NaN                  NaN   \n",
       "2925489             NaN          NaN                  NaN   \n",
       "2925490             NaN          NaN                  NaN   \n",
       "2925491             NaN          NaN                  NaN   \n",
       "2925492             NaN          NaN                  NaN   \n",
       "\n",
       "        orig_projected_additional_accrued_interest  \\\n",
       "0                                              NaN   \n",
       "1                                              NaN   \n",
       "2                                              NaN   \n",
       "3                                              NaN   \n",
       "4                                              NaN   \n",
       "...                                            ...   \n",
       "2925488                                        NaN   \n",
       "2925489                                        NaN   \n",
       "2925490                                        NaN   \n",
       "2925491                                        NaN   \n",
       "2925492                                        NaN   \n",
       "\n",
       "        hardship_payoff_balance_amount hardship_last_payment_amount  \\\n",
       "0                                  NaN                          NaN   \n",
       "1                                  NaN                          NaN   \n",
       "2                                  NaN                          NaN   \n",
       "3                                  NaN                          NaN   \n",
       "4                                  NaN                          NaN   \n",
       "...                                ...                          ...   \n",
       "2925488                            NaN                          NaN   \n",
       "2925489                            NaN                          NaN   \n",
       "2925490                            NaN                          NaN   \n",
       "2925491                            NaN                          NaN   \n",
       "2925492                            NaN                          NaN   \n",
       "\n",
       "        debt_settlement_flag  \n",
       "0                          N  \n",
       "1                          N  \n",
       "2                          N  \n",
       "3                          N  \n",
       "4                          N  \n",
       "...                      ...  \n",
       "2925488                    N  \n",
       "2925489                    N  \n",
       "2925490                    N  \n",
       "2925491                    N  \n",
       "2925492                    N  \n",
       "\n",
       "[2925493 rows x 141 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan = pd.read_csv('Loan_status_2007-2020Q3.csv').iloc[:, 1:]\n",
    "# loan = pd.read_csv('loan_10%.csv').iloc[:, 1:]\n",
    "loan"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "590a337e",
   "metadata": {},
   "source": [
    "some domain knowledge\n",
    "\n",
    "account types:\n",
    "- revolving account (rv/rev) and open revolving account\n",
    "    https://finance.yahoo.com/news/credit-101-revolving-account-160156744.html?guccounter=1&guce_referrer=aHR0cHM6Ly93d3cuYmluZy5jb20v&guce_referrer_sig=AQAAAC5tynIU9PSiuHiOsa8TUXN46hin0knyqXtRWEYZxFfiZt0min4vpyutMQhXwjLeGrNSBBmR5HNdJmnlQ6PzxMURqb_J4KINLWrbtxUU2yVXqQUi8mmTzfh1IQkXxeEm9RkSduAW2Wz2-nzYTqNMo2cRW0w8egEIcv1Hf171RAr7\n",
    "- installment account (il)\n",
    "- bankcard account (bc)\n",
    "- mortgage account\n",
    "\n",
    "ratio types:\n",
    "- util rate\n",
    "- dti\n",
    "- high credit / credit limit"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bc67717",
   "metadata": {},
   "source": [
    "rename for standardizing the column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "35aefe66",
   "metadata": {},
   "outputs": [],
   "source": [
    "find = [\n",
    "    'mths_since_rcnt_il', 'mo_sin_rcnt_rev_tl_op', 'mo_sin_rcnt_tl', # rcnt and mo_sin_rcnt\n",
    "    'mths_since_recent_bc_dlq', 'pct_tl_nvr_dlq', # dlq\n",
    "    'mo_sin_old_il_acct', 'mo_sin_old_rev_tl_op', # mo_sin_old\n",
    "]\n",
    "replace = [\n",
    "    'mths_since_recent_il', 'mths_since_recent_rev_tl_op', 'mths_since_recent_tl', # recent and mths_since\n",
    "    'mths_since_recent_bc_delinq', 'percent_tl_nvr_deliq', # delinq\n",
    "    'mths_since_old_il_acct', 'mths_since_old_rev_tl_op' # mths_since_old\n",
    "]\n",
    "\n",
    "loan.rename(columns={\n",
    "    find[i]: replace[i] for i in range(len(find))\n",
    "}, inplace=True)\n",
    "\n",
    "del find, replace"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e4817c7",
   "metadata": {},
   "source": [
    "change these back to datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5ed943bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "datetime_columns = ['issue_d', 'earliest_cr_line', 'last_pymnt_d', 'next_pymnt_d',\n",
    "                    'last_credit_pull_d', 'sec_app_earliest_cr_line', 'hardship_start_date',\n",
    "                    'hardship_end_date', 'payment_plan_start_date']\n",
    "\n",
    "for col in datetime_columns:\n",
    "    loan[col] = pd.to_datetime(loan[col], format='%b-%Y')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1306fd4",
   "metadata": {},
   "source": [
    "remove loan data issued before 1-1-2013"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1bcfc2ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "loan['issue_d_year'] = loan['issue_d'].dt.year\n",
    "loan = loan[loan['issue_d_year'] > 2012]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2644c767",
   "metadata": {},
   "source": [
    "change int_rate, revol_util from dd% to dd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e49b2b93",
   "metadata": {},
   "outputs": [],
   "source": [
    "loan['int_rate'] = loan['int_rate'].apply(\n",
    "    lambda x: float(x.strip().split('%')[0]) if pd.notnull(x) else np.nan\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "51e186fe",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "loan['revol_util'] = loan['revol_util'].apply(\n",
    "    lambda x: float(x.strip().split('%')[0]) if pd.notnull(x) else np.nan\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ea8b6de",
   "metadata": {},
   "source": [
    "create a column for deciding if it is matured loan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5d5e6e42",
   "metadata": {},
   "outputs": [],
   "source": [
    "loan['is_matured_loan'] = loan['next_pymnt_d'].isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "163389ea",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "loan = loan[loan['is_matured_loan']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8640494",
   "metadata": {},
   "source": [
    "create functions to handle na"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "82bffc46",
   "metadata": {},
   "outputs": [],
   "source": [
    "# handling columns with missing value (not used)\n",
    "def check_dtype(df, col):\n",
    "    if df[col].dtype == 'object':\n",
    "        return 'object'\n",
    "    elif np.modf(df[col])[0].nunique() == 1:\n",
    "        return 'int'\n",
    "    else:\n",
    "        return 'float'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5fe09ee1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# turn some into happens or not\n",
    "# e.g. for months since last delinquent, people have NA in their record as they never delinq.\n",
    "def convert_to_bin(df, col):\n",
    "    df[col + '_binary'] = (~df[col].isna()) * 1\n",
    "\n",
    "# fill na with most freq. values or empty / 0\n",
    "def fill_na(df, col, method):\n",
    "    if method == 'mode':\n",
    "        to_fill = df[col].value_counts().index[0]\n",
    "    elif method == 'mean':\n",
    "        to_fill = df[col].mean()\n",
    "    elif method == 'zero':\n",
    "        to_fill = 0\n",
    "    elif method == 'string':\n",
    "        to_fill = ''\n",
    "    else:\n",
    "        raise ValueError(\"method must be 'mode', 'mean', 'zero' or 'string'.\")\n",
    "    df[col].fillna(to_fill, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "0a991f61",
   "metadata": {},
   "outputs": [],
   "source": [
    "# handle columns starting with 'mths_since'\n",
    "# motivation: e.g. NAN in number of months since the last delinquent = the applicant has a clean record\n",
    "# use binary variable to represent instead of number of months\n",
    "mths_since_to_drop = []\n",
    "\n",
    "for col in loan.columns:\n",
    "    if 'mths_since_' in col and col not in ['mths_since_recent_rev_tl_op', \n",
    "                                            'mths_since_recent_tl']:\n",
    "        convert_to_bin(loan, col)\n",
    "        mths_since_to_drop.append(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "107774df",
   "metadata": {},
   "outputs": [],
   "source": [
    "# these are only able after 2015\n",
    "\n",
    "# ratio or balance\n",
    "for col in ['il_util', 'all_util', 'total_bal_il', 'max_bal_bc']:\n",
    "    fill_na(loan, col, 'mean')\n",
    "    \n",
    "# times\n",
    "for col in ['open_acc_6m', 'open_act_il', 'open_il_12m', 'open_il_24m',\n",
    "            'open_rv_12m', 'open_rv_24m', 'inq_fi', 'total_cu_tl', 'inq_last_12m']:\n",
    "    fill_na(loan, col, 'mode')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "3f8820c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# fill by 0 in general\n",
    "for col in ['deferral_term']:\n",
    "    fill_na(loan, col, 'zero')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "4ad1ac08",
   "metadata": {},
   "outputs": [],
   "source": [
    "# fill by max entry in general\n",
    "for col in ['emp_length', 'num_tl_120dpd_2m', 'hardship_flag',\n",
    "            'num_rev_accts', 'inq_last_6mths', 'zip_code']:\n",
    "    fill_na(loan, col, 'mode')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "2b39af64",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# fill by mean in general\n",
    "for col in ['percent_bc_gt_75', 'bc_util', 'revol_util', 'dti',\n",
    "            'percent_tl_nvr_deliq', 'avg_cur_bal', 'bc_open_to_buy']:\n",
    "    fill_na(loan, col, 'mean')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "69fcfc4e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# fill by empty string in general\n",
    "for col in ['emp_title', 'title', 'purpose']:\n",
    "    fill_na(loan, col, 'string')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eafe6e8d",
   "metadata": {},
   "source": [
    "create columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "991a0284",
   "metadata": {},
   "outputs": [],
   "source": [
    "# loan to income ratio\n",
    "loan['loan_to_income'] = loan.loan_amnt / (loan.annual_inc + 1)\n",
    "\n",
    "# delinq to income ratio\n",
    "loan['deliq_to_income'] = loan.delinq_amnt / (loan.annual_inc + 1)\n",
    "\n",
    "# revol_bal to income ratio\n",
    "loan['revol_to_income'] = loan.revol_bal / (loan.annual_inc + 1)\n",
    "\n",
    "# tot_cur_bal to income ratio\n",
    "loan['tot_cur_bal_to_income'] = loan.tot_cur_bal / (loan.annual_inc + 1)\n",
    "\n",
    "# max_bal_bc to income ratio\n",
    "loan['max_bal_bc_to_income'] = loan.max_bal_bc / (loan.annual_inc + 1)\n",
    "\n",
    "# avg_cur_bal to income ratio\n",
    "loan['avg_cur_bal_to_income'] = loan.avg_cur_bal / (loan.annual_inc + 1)\n",
    "\n",
    "# total_bal_ex_mort to income ratio\n",
    "loan['total_bal_ex_mort_to_income'] = loan.total_bal_ex_mort / (loan.annual_inc + 1)\n",
    "\n",
    "# ratio of the number of open credit lines to the number of total credit lines in the borrower's credit file.\n",
    "loan['open_cl_ratio'] = loan['open_acc'] / loan['total_acc']\n",
    "\n",
    "# fico change\n",
    "loan['FICO_change'] = loan.apply(\n",
    "    lambda r: r['fico_range_high'] / r['last_fico_range_high'] if r['last_fico_range_high'] > 0 else 1, axis=1\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca7aa260",
   "metadata": {},
   "source": [
    "calculate installment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "74d641e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "def month_diff(a, b):\n",
    "    return 12 * (a.dt.year - b.dt.year) + (a.dt.month - b.dt.month)\n",
    "\n",
    "num_mths_to_pay = month_diff(\n",
    "    pd.to_datetime(loan.last_pymnt_d), \n",
    "    pd.to_datetime(loan.issue_d)\n",
    ")\n",
    "\n",
    "missing_num_mths_to_pay = loan[loan.last_pymnt_d.isna()].apply(\n",
    "    lambda x: x['total_pymnt']//x['installment'] if x['total_pymnt'] > 0 else 0, axis=1\n",
    ")\n",
    "\n",
    "num_mths_to_pay[num_mths_to_pay.isna()] = missing_num_mths_to_pay"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "e4d54f87",
   "metadata": {},
   "outputs": [],
   "source": [
    "p = loan['funded_amnt']\n",
    "r = loan['int_rate'] / 100\n",
    "n = loan['term'].apply(lambda x: int(x.strip()[:2]))\n",
    "\n",
    "loan['calculated_installment'] = p * (r/12) * (1+r/12)**n / ((1+r/12)**n - 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "9a2a2e4b",
   "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>installment</th>\n",
       "      <th>calculated_installment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42536</th>\n",
       "      <td>392.81</td>\n",
       "      <td>392.807780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42537</th>\n",
       "      <td>157.13</td>\n",
       "      <td>157.123112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42538</th>\n",
       "      <td>885.46</td>\n",
       "      <td>885.454205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42539</th>\n",
       "      <td>373.94</td>\n",
       "      <td>373.936320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42540</th>\n",
       "      <td>470.71</td>\n",
       "      <td>470.704473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2925488</th>\n",
       "      <td>690.30</td>\n",
       "      <td>690.291882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2925489</th>\n",
       "      <td>313.32</td>\n",
       "      <td>313.317525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2925490</th>\n",
       "      <td>358.26</td>\n",
       "      <td>358.259896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2925491</th>\n",
       "      <td>197.69</td>\n",
       "      <td>197.684869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2925492</th>\n",
       "      <td>889.18</td>\n",
       "      <td>889.178589</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1767178 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         installment  calculated_installment\n",
       "42536         392.81              392.807780\n",
       "42537         157.13              157.123112\n",
       "42538         885.46              885.454205\n",
       "42539         373.94              373.936320\n",
       "42540         470.71              470.704473\n",
       "...              ...                     ...\n",
       "2925488       690.30              690.291882\n",
       "2925489       313.32              313.317525\n",
       "2925490       358.26              358.259896\n",
       "2925491       197.69              197.684869\n",
       "2925492       889.18              889.178589\n",
       "\n",
       "[1767178 rows x 2 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan[['installment', 'calculated_installment']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acb9dbe9",
   "metadata": {},
   "source": [
    "calculate ROI\n",
    "\n",
    "The cash flow of a loan consists of five possible parts: Principal, interest, late fees, sales proceeds, and recovery amount.8 From the lender’s perspective, the typical set of cash flows for a loan may look like the following: {−P0, P1, . . . , PT }, consisting of an initial cash outflow P0 and monthly cash inflows {Pt, t ∈ {1, . . . , T}} until the loan is fully paid or charged off. We can approximate the monthly payments of a loan by the average monthly payment, i.e., the total payments received divided by the number of months between origination and the last payment. The cash flows for a portfolio of loans can be generated by summing up the cash flows of constituent loans\n",
    "\n",
    "The monthly payments information is not available in our dataset. We approximate the monthly payment based on loan origination date, last payment date, total payments, and collection recovery fee.\n",
    "\n",
    "- P0 = funded amount (the inital cashflow)\n",
    "- P1...PT are calculated from issue_d, last_pymnt_d, total_pymnt, collection_recovery_fee (charge off), recoveries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "1bbc26c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assume\n",
    "depreciation_rate = 0.02"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "b3887ba5",
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp = pd.DataFrame({\n",
    "    'funded_amnt': loan['funded_amnt'] * -1,\n",
    "    'avg_pymnt': loan['total_pymnt'] / num_mths_to_pay,\n",
    "    'num_mths_to_pay': num_mths_to_pay.astype(int)\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "58508a6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp.loc[tmp['num_mths_to_pay']==0, 'avg_pymnt'] = loan.loc[tmp['num_mths_to_pay']==0, 'last_pymnt_amnt']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "1d09b047",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "CDP = tmp['num_mths_to_pay'].apply(\n",
    "    lambda x: np.sum([(1+depreciation_rate/12)**(-t) for t in range(1, int(x)+1)])\n",
    ") * tmp['avg_pymnt']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "80a0bc55",
   "metadata": {},
   "outputs": [],
   "source": [
    "# for those immediately paid right after issued\n",
    "CDP[(tmp['avg_pymnt']>0)&(tmp['num_mths_to_pay']==0)] = tmp['avg_pymnt'][(tmp['avg_pymnt']>0)&(tmp['num_mths_to_pay']==0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "947b034f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# approximately 6 months after last payment\n",
    "net_recoveries = loan['recoveries'] - loan['collection_recovery_fee']\n",
    "depreciated_net_recoveries = net_recoveries[net_recoveries>0] * \\\n",
    "                                tmp['num_mths_to_pay'][net_recoveries>0]\\\n",
    "                                    .apply(lambda x: 1/(1+depreciation_rate/12)**(x+6))\n",
    "\n",
    "CDP[net_recoveries>0] += depreciated_net_recoveries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "4f2c989f",
   "metadata": {},
   "outputs": [],
   "source": [
    "ROI = ((CDP + tmp['funded_amnt']) / - tmp['funded_amnt'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "c340f9b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "loan['ROI'] = ROI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "978cae8f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Axes: ylabel='Frequency'>"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAlYAAAGeCAYAAABW9k9bAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjcuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8pXeV/AAAACXBIWXMAAA9hAAAPYQGoP6dpAABDlElEQVR4nO3df3RU9Z3/8dcYkiFkk2kgJJMRBLaFCAQ9EFoItAYUEigB0X6LGIyJpVGrgCzk28L2tILfCig/7K6uSlkKiti4FrD1RNPwm1ISfgSiRBBYCyRIQhDDBKgkET7fP1zueklACBeSCc/HOfeczOe+587nM5dxXn7uj3EZY4wAAABwzW5p6g4AAAC0FAQrAAAAhxCsAAAAHEKwAgAAcAjBCgAAwCEEKwAAAIcQrAAAABxCsAIAAHAIwQoAAMAhrZq6Azeb8+fP6+jRowoPD5fL5Wrq7gAAgCtgjNGpU6fk8/l0yy2XmZcyTWjWrFmmb9++5p/+6Z9M+/btzb333ms+/vhjW8358+fN008/bWJjY03r1q1NUlKSKSkpsdWcPXvWTJgwwbRr1860adPGjBw50pSVldlqPv/8c/PQQw+ZiIgIExERYR566CFTVVVlqzl8+LBJTU01bdq0Me3atTMTJ040NTU1tpoPP/zQ3HXXXaZ169bG5/OZmTNnmvPnz1/xmMvKyowkFhYWFhYWlgBcLs4XF2vSGauNGzfqySef1He/+119+eWX+uUvf6nk5GTt2bNHYWFhkqTnn39eCxYs0NKlS9WtWzf95je/0dChQ7Vv3z6Fh4dLkiZPnqx3331XOTk5ateunaZOnarU1FQVFRUpKChIkpSWlqYjR44oLy9PkvToo48qPT1d7777riTp3LlzGjFihNq3b6/NmzfrxIkTysjIkDFGL774oiSpurpaQ4cO1eDBg7V9+3bt379fmZmZCgsL09SpU69ozBf6XFZWpoiICOfeTAAAcN1UV1erY8eO1vf4JV3xVMsNUFlZaSSZjRs3GmO+mq3yer1mzpw5Vs3Zs2eNx+Mxr776qjHGmJMnT5rg4GCTk5Nj1Xz66afmlltuMXl5ecYYY/bs2WMkmcLCQqumoKDASLJmyN577z1zyy23mE8//dSq+cMf/mDcbrfx+/3GGGNefvll4/F4zNmzZ62a2bNnG5/Pd8WzVn6/30iytgkAAJq/K/3+blYnr/v9fklS27ZtJUkHDx5URUWFkpOTrRq3262kpCRt2bJFklRUVKS6ujpbjc/nU3x8vFVTUFAgj8ejfv36WTX9+/eXx+Ox1cTHx8vn81k1KSkpqqmpUVFRkVWTlJQkt9ttqzl69KgOHTrU4JhqampUXV1tWwAAQMvUbIKVMUZTpkzR97//fcXHx0uSKioqJEkxMTG22piYGGtdRUWFQkJCFBkZedma6Ojoeq8ZHR1tq7n4dSIjIxUSEnLZmguPL9RcbPbs2fJ4PNbSsWPHb3gnAABAoGo2wWrChAn68MMP9Yc//KHeuouvnjPGfOMVdRfXNFTvRI0x5pLPlaTp06fL7/dbS1lZ2WX7DQAAAlezCFYTJ07Un//8Z61fv14dOnSw2r1er6T6s0GVlZXWTJHX61Vtba2qqqouW3Ps2LF6r3v8+HFbzcWvU1VVpbq6usvWVFZWSqo/q3aB2+1WRESEbQEAAC1TkwYrY4wmTJiglStXat26derSpYttfZcuXeT1erV69Wqrrba2Vhs3btSAAQMkSQkJCQoODrbVlJeXq6SkxKpJTEyU3+/Xtm3brJqtW7fK7/fbakpKSlReXm7V5Ofny+12KyEhwarZtGmTamtrbTU+n0+dO3d26F0BAAAB63qfRX85P/vZz4zH4zEbNmww5eXl1vKPf/zDqpkzZ47xeDxm5cqVZvfu3ebBBx80sbGxprq62qp5/PHHTYcOHcyaNWvMzp07zd13323uvPNO8+WXX1o1w4YNM3fccYcpKCgwBQUFplevXiY1NdVa/+WXX5r4+Hhzzz33mJ07d5o1a9aYDh06mAkTJlg1J0+eNDExMebBBx80u3fvNitXrjQRERFm3rx5VzxmrgoEACDwXOn3d5MGK13i5ltLliyxai7cINTr9Rq3223uuusus3v3btt2vvjiCzNhwgTTtm1bExoaalJTU01paamt5sSJE2bcuHEmPDzchIeHm3HjxjV4g9ARI0aY0NBQ07ZtWzNhwgTbrRWM+eoGoT/4wQ+M2+02Xq/XzJgx46puEEqwAgAg8Fzp97fLmP85+xo3RHV1tTwej/x+P+dbAQAQIK70+7tZnLwOAADQEhCsAAAAHEKwAgAAcAjBCgAAwCEEKwAAAIcQrAAAABzSqqk7AAC4tM7Tcuu1HZozogl6AuBKMGMFAADgEIIVAACAQwhWAAAADiFYAQAAOIRgBQAA4BCCFQAAgEMIVgAAAA4hWAEAADiEYAUAAOAQghUAAIBDCFYAAAAOIVgBAAA4hGAFAADgEIIVAACAQwhWAAAADiFYAQAAOIRgBQAA4BCCFQAAgEMIVgAAAA4hWAEAADiEYAUAAOAQghUAAIBDCFYAAAAOIVgBAAA4hGAFAADgEIIVAACAQwhWAAAADiFYAQAAOKRJg9WmTZs0cuRI+Xw+uVwuvfPOO7b1LperwWXu3LlWzaBBg+qtHzt2rG07VVVVSk9Pl8fjkcfjUXp6uk6ePGmrKS0t1ciRIxUWFqaoqChNmjRJtbW1tprdu3crKSlJoaGhuvXWW/XMM8/IGOPoewIAAAJXq6Z88TNnzujOO+/UI488oh/96Ef11peXl9sev//++xo/fny92qysLD3zzDPW49DQUNv6tLQ0HTlyRHl5eZKkRx99VOnp6Xr33XclSefOndOIESPUvn17bd68WSdOnFBGRoaMMXrxxRclSdXV1Ro6dKgGDx6s7du3a//+/crMzFRYWJimTp167W8GAAAIeE0arIYPH67hw4dfcr3X67U9/tOf/qTBgwfrn//5n23tbdq0qVd7wd69e5WXl6fCwkL169dPkrRo0SIlJiZq3759iouLU35+vvbs2aOysjL5fD5J0vz585WZmalnn31WERERWr58uc6ePaulS5fK7XYrPj5e+/fv14IFCzRlyhS5XK4GX7+mpkY1NTXW4+rq6m9+YwAAQEAKmHOsjh07ptzcXI0fP77euuXLlysqKko9e/ZUdna2Tp06Za0rKCiQx+OxQpUk9e/fXx6PR1u2bLFq4uPjrVAlSSkpKaqpqVFRUZFVk5SUJLfbbas5evSoDh06dMl+z5492zoE6fF41LFjx0a/BwAAoHkLmGD12muvKTw8XPfff7+tfdy4cfrDH/6gDRs26Fe/+pVWrFhhq6moqFB0dHS97UVHR6uiosKqiYmJsa2PjIxUSEjIZWsuPL5Q05Dp06fL7/dbS1lZ2VWMGgAABJImPRR4NX7/+99r3Lhxat26ta09KyvL+js+Pl5du3ZV3759tXPnTvXp00eSGjxMZ4yxtTem5sKJ65c6DChJbrfbNssFAABaroCYsfrrX/+qffv26ac//ek31vbp00fBwcE6cOCApK/O0zp27Fi9uuPHj1szTl6vt96sU1VVlerq6i5bU1lZKUn1ZrIAAMDNKSCC1eLFi5WQkKA777zzG2s/+ugj1dXVKTY2VpKUmJgov9+vbdu2WTVbt26V3+/XgAEDrJqSkhLbVYj5+flyu91KSEiwajZt2mS7BUN+fr58Pp86d+7sxDABAECAa9Jgdfr0aRUXF6u4uFiSdPDgQRUXF6u0tNSqqa6u1ttvv93gbNUnn3yiZ555Rjt27NChQ4f03nvv6cc//rF69+6tgQMHSpK6d++uYcOGKSsrS4WFhSosLFRWVpZSU1MVFxcnSUpOTlaPHj2Unp6uXbt2ae3atcrOzlZWVpYiIiIkfXXLBrfbrczMTJWUlGjVqlWaNWvWZa8IBAAAN5cmDVY7duxQ79691bt3b0nSlClT1Lt3b/3617+2anJycmSM0YMPPljv+SEhIVq7dq1SUlIUFxenSZMmKTk5WWvWrFFQUJBVt3z5cvXq1UvJyclKTk7WHXfcoWXLllnrg4KClJubq9atW2vgwIEaM2aMRo8erXnz5lk1Ho9Hq1ev1pEjR9S3b1898cQTmjJliqZMmXI93hoAABCAXIZbh99Q1dXV8ng88vv91mwYAFxK52m59doOzRnRBD0Bbm5X+v0dEOdYAQAABAKCFQAAgEMIVgAAAA4hWAEAADiEYAUAAOAQghUAAIBDAua3AgGgJWvotgoAAg8zVgAAAA4hWAEAADiEYAUAAOAQghUAAIBDCFYAAAAOIVgBAAA4hGAFAADgEIIVAACAQwhWAAAADiFYAQAAOIRgBQAA4BCCFQAAgEMIVgAAAA4hWAEAADiEYAUAAOAQghUAAIBDCFYAAAAOIVgBAAA4hGAFAADgEIIVAACAQwhWAAAADiFYAQAAOIRgBQAA4BCCFQAAgEMIVgAAAA4hWAEAADiEYAUAAOCQJg1WmzZt0siRI+Xz+eRyufTOO+/Y1mdmZsrlctmW/v3722pqamo0ceJERUVFKSwsTKNGjdKRI0dsNVVVVUpPT5fH45HH41F6erpOnjxpqyktLdXIkSMVFhamqKgoTZo0SbW1tbaa3bt3KykpSaGhobr11lv1zDPPyBjj2PsBAAACW5MGqzNnzujOO+/USy+9dMmaYcOGqby83Free+892/rJkydr1apVysnJ0ebNm3X69Gmlpqbq3LlzVk1aWpqKi4uVl5envLw8FRcXKz093Vp/7tw5jRgxQmfOnNHmzZuVk5OjFStWaOrUqVZNdXW1hg4dKp/Pp+3bt+vFF1/UvHnztGDBAgffEQAAEMhaNeWLDx8+XMOHD79sjdvtltfrbXCd3+/X4sWLtWzZMg0ZMkSS9MYbb6hjx45as2aNUlJStHfvXuXl5amwsFD9+vWTJC1atEiJiYnat2+f4uLilJ+frz179qisrEw+n0+SNH/+fGVmZurZZ59VRESEli9frrNnz2rp0qVyu92Kj4/X/v37tWDBAk2ZMkUul8vBdwYAAASiZn+O1YYNGxQdHa1u3bopKytLlZWV1rqioiLV1dUpOTnZavP5fIqPj9eWLVskSQUFBfJ4PFaokqT+/fvL4/HYauLj461QJUkpKSmqqalRUVGRVZOUlCS3222rOXr0qA4dOnTJ/tfU1Ki6utq2AACAlqlZB6vhw4dr+fLlWrdunebPn6/t27fr7rvvVk1NjSSpoqJCISEhioyMtD0vJiZGFRUVVk10dHS9bUdHR9tqYmJibOsjIyMVEhJy2ZoLjy/UNGT27NnWuV0ej0cdO3a8mrcAAAAEkCY9FPhNHnjgAevv+Ph49e3bV506dVJubq7uv//+Sz7PGGM7NNfQYTonai6cuH65w4DTp0/XlClTrMfV1dWEKwAAWqhmPWN1sdjYWHXq1EkHDhyQJHm9XtXW1qqqqspWV1lZac0meb1eHTt2rN62jh8/bqu5eNapqqpKdXV1l625cFjy4pmsr3O73YqIiLAtAACgZQqoYHXixAmVlZUpNjZWkpSQkKDg4GCtXr3aqikvL1dJSYkGDBggSUpMTJTf79e2bdusmq1bt8rv99tqSkpKVF5ebtXk5+fL7XYrISHBqtm0aZPtFgz5+fny+Xzq3LnzdRszAAAIHE0arE6fPq3i4mIVFxdLkg4ePKji4mKVlpbq9OnTys7OVkFBgQ4dOqQNGzZo5MiRioqK0n333SdJ8ng8Gj9+vKZOnaq1a9dq165deuihh9SrVy/rKsHu3btr2LBhysrKUmFhoQoLC5WVlaXU1FTFxcVJkpKTk9WjRw+lp6dr165dWrt2rbKzs5WVlWXNMKWlpcntdiszM1MlJSVatWqVZs2axRWBAADA0qTnWO3YsUODBw+2Hl84FykjI0OvvPKKdu/erddff10nT55UbGysBg8erLfeekvh4eHWc1544QW1atVKY8aM0RdffKF77rlHS5cuVVBQkFWzfPlyTZo0ybp6cNSoUbZ7ZwUFBSk3N1dPPPGEBg4cqNDQUKWlpWnevHlWjcfj0erVq/Xkk0+qb9++ioyM1JQpU2znTwEAgJuby3Dr8BuqurpaHo9Hfr+f860AWDpPy73i2kNzRlzHngBoyJV+fwfUOVYAAADNGcEKAADAIQQrAAAAhxCsAAAAHEKwAgAAcAjBCgAAwCEEKwAAAIcQrAAAABxCsAIAAHAIwQoAAMAhBCsAAACHEKwAAAAcQrACAABwCMEKAADAIQQrAAAAhxCsAAAAHEKwAgAAcAjBCgAAwCEEKwAAAIcQrAAAABxCsAIAAHAIwQoAAMAhBCsAAACHtGrqDgAArk7nabkNth+aM+IG9wTAxZixAgAAcAjBCgAAwCEEKwAAAIcQrAAAABxCsAIAAHAIwQoAAMAhBCsAAACHcB8rAGghuL8V0PSYsQIAAHAIwQoAAMAhBCsAAACHNGmw2rRpk0aOHCmfzyeXy6V33nnHWldXV6df/OIX6tWrl8LCwuTz+fTwww/r6NGjtm0MGjRILpfLtowdO9ZWU1VVpfT0dHk8Hnk8HqWnp+vkyZO2mtLSUo0cOVJhYWGKiorSpEmTVFtba6vZvXu3kpKSFBoaqltvvVXPPPOMjDGOvicAACBwNWmwOnPmjO6880699NJL9db94x//0M6dO/WrX/1KO3fu1MqVK7V//36NGjWqXm1WVpbKy8utZeHChbb1aWlpKi4uVl5envLy8lRcXKz09HRr/blz5zRixAidOXNGmzdvVk5OjlasWKGpU6daNdXV1Ro6dKh8Pp+2b9+uF198UfPmzdOCBQscfEcAAEAga9KrAocPH67hw4c3uM7j8Wj16tW2thdffFHf+973VFpaqttuu81qb9Omjbxeb4Pb2bt3r/Ly8lRYWKh+/fpJkhYtWqTExETt27dPcXFxys/P1549e1RWViafzydJmj9/vjIzM/Xss88qIiJCy5cv19mzZ7V06VK53W7Fx8dr//79WrBggaZMmSKXy+XEWwIAAAJYQJ1j5ff75XK59K1vfcvWvnz5ckVFRalnz57Kzs7WqVOnrHUFBQXyeDxWqJKk/v37y+PxaMuWLVZNfHy8FaokKSUlRTU1NSoqKrJqkpKS5Ha7bTVHjx7VoUOHLtnnmpoaVVdX2xYAANAyBcx9rM6ePatp06YpLS1NERERVvu4cePUpUsXeb1elZSUaPr06frggw+s2a6KigpFR0fX2150dLQqKiqsmpiYGNv6yMhIhYSE2Go6d+5sq7nwnIqKCnXp0qXBfs+ePVszZ85s3KABAEBACYhgVVdXp7Fjx+r8+fN6+eWXbeuysrKsv+Pj49W1a1f17dtXO3fuVJ8+fSSpwcN0xhhbe2NqLpy4frnDgNOnT9eUKVOsx9XV1erYseMl6wEAQOBq9ocC6+rqNGbMGB08eFCrV6+2zVY1pE+fPgoODtaBAwckSV6vV8eOHatXd/z4cWvGyev1WjNTF1RVVamuru6yNZWVlZJUb7br69xutyIiImwLAABomZp1sLoQqg4cOKA1a9aoXbt23/icjz76SHV1dYqNjZUkJSYmyu/3a9u2bVbN1q1b5ff7NWDAAKumpKRE5eXlVk1+fr7cbrcSEhKsmk2bNtluwZCfny+fz1fvECEAALg5NWmwOn36tIqLi1VcXCxJOnjwoIqLi1VaWqovv/xS/+f//B/t2LFDy5cv17lz51RRUaGKigor3HzyySd65plntGPHDh06dEjvvfeefvzjH6t3794aOHCgJKl79+4aNmyYsrKyVFhYqMLCQmVlZSk1NVVxcXGSpOTkZPXo0UPp6enatWuX1q5dq+zsbGVlZVkzTGlpaXK73crMzFRJSYlWrVqlWbNmcUUgAACwuEwT3uFyw4YNGjx4cL32jIwMzZgx45InhK9fv16DBg1SWVmZHnroIZWUlOj06dPq2LGjRowYoaefflpt27a16j///HNNmjRJf/7znyVJo0aN0ksvvWS7urC0tFRPPPGE1q1bp9DQUKWlpWnevHm2qwB3796tJ598Utu2bVNkZKQef/xx/frXv76qYFVdXS2PxyO/389hQQCWS/2AshP4EWbg2l3p93eTBqubEcEKQEMIVkDzdqXf3836HCsAAIBAQrACAABwCMEKAADAIQQrAAAAhzQqWB08eNDpfgAAAAS8RgWr73znOxo8eLDeeOMNnT171uk+AQAABKRGBasPPvhAvXv31tSpU+X1evXYY4/Z7mwOAABwM2pUsIqPj9eCBQv06aefasmSJaqoqND3v/999ezZUwsWLNDx48ed7icAAECzd00nr7dq1Ur33Xef/uu//kvPPfecPvnkE2VnZ6tDhw56+OGHbb+9BwAA0NJdU7DasWOHnnjiCcXGxmrBggXKzs7WJ598onXr1unTTz/Vvffe61Q/AQAAmr1WjXnSggULtGTJEu3bt08//OEP9frrr+uHP/yhbrnlq5zWpUsXLVy4ULfffrujnQUAAGjOGhWsXnnlFf3kJz/RI488Iq/X22DNbbfdpsWLF19T5wAAAAJJo4LVgQMHvrEmJCREGRkZjdk8AABAQGrUOVZLlizR22+/Xa/97bff1muvvXbNnQIAAAhEjQpWc+bMUVRUVL326OhozZo165o7BQAAEIgaFawOHz6sLl261Gvv1KmTSktLr7lTAAAAgahRwSo6OloffvhhvfYPPvhA7dq1u+ZOAQAABKJGnbw+duxYTZo0SeHh4brrrrskSRs3btRTTz2lsWPHOtpBAMC16Twtt17boTkjmqAnQMvXqGD1m9/8RocPH9Y999yjVq2+2sT58+f18MMPc44VAAC4aTUqWIWEhOitt97S//t//08ffPCBQkND1atXL3Xq1Mnp/gEAAASMRgWrC7p166Zu3bo51RcAAICA1qhgde7cOS1dulRr165VZWWlzp8/b1u/bt06RzoHAAAQSBoVrJ566iktXbpUI0aMUHx8vFwul9P9AgAACDiNClY5OTn6r//6L/3whz90uj8AAAABq1H3sQoJCdF3vvMdp/sCAAAQ0BoVrKZOnap/+7d/kzHG6f4AAAAErEYdCty8ebPWr1+v999/Xz179lRwcLBt/cqVKx3pHAC0NA3drBNAy9GoYPWtb31L9913n9N9AQAACGiNClZLlixxuh8AAAABr1HnWEnSl19+qTVr1mjhwoU6deqUJOno0aM6ffq0Y50DAAAIJI2asTp8+LCGDRum0tJS1dTUaOjQoQoPD9fzzz+vs2fP6tVXX3W6nwAAAM1eo2asnnrqKfXt21dVVVUKDQ212u+77z6tXbvWsc4BAAAEkkZfFfi3v/1NISEhtvZOnTrp008/daRjAAAAgaZRM1bnz5/XuXPn6rUfOXJE4eHh19wpAACAQNSoYDV06FD99re/tR67XC6dPn1aTz/9ND9zAwAAblqNClYvvPCCNm7cqB49eujs2bNKS0tT586d9emnn+q555674u1s2rRJI0eOlM/nk8vl0jvvvGNbb4zRjBkz5PP5FBoaqkGDBumjjz6y1dTU1GjixImKiopSWFiYRo0apSNHjthqqqqqlJ6eLo/HI4/Ho/T0dJ08edJWU1paqpEjRyosLExRUVGaNGmSamtrbTW7d+9WUlKSQkNDdeutt+qZZ57h7vMAAMDSqGDl8/lUXFys7OxsPfbYY+rdu7fmzJmjXbt2KTo6+oq3c+bMGd1555166aWXGlz//PPPa8GCBXrppZe0fft2eb1eDR061Lq9gyRNnjxZq1atUk5OjjZv3qzTp08rNTXVdqgyLS1NxcXFysvLU15enoqLi5Wenm6tP3funEaMGKEzZ85o8+bNysnJ0YoVKzR16lSrprq6WkOHDpXP59P27dv14osvat68eVqwYMHVvHUAAKAFc5lmMuXicrm0atUqjR49WtJXs1U+n0+TJ0/WL37xC0lfzU7FxMToueee02OPPSa/36/27dtr2bJleuCBByR9dS+tjh076r333lNKSor27t2rHj16qLCwUP369ZMkFRYWKjExUR9//LHi4uL0/vvvKzU1VWVlZfL5fJKknJwcZWZmqrKyUhEREXrllVc0ffp0HTt2TG63W5I0Z84cvfjiizpy5IhcLleD46qpqVFNTY31uLq6Wh07dpTf71dERMR1eS8BNF/N5SdtDs0Z0dRdAAJKdXW1PB7PN35/N+qqwNdff/2y6x9++OHGbNbm4MGDqqioUHJystXmdruVlJSkLVu26LHHHlNRUZHq6upsNT6fT/Hx8dqyZYtSUlJUUFAgj8djhSpJ6t+/vzwej7Zs2aK4uDgVFBQoPj7eClWSlJKSopqaGhUVFWnw4MEqKChQUlKSFaou1EyfPl2HDh1Sly5dGhzH7NmzNXPmzGt+PwAAQPPXqGD11FNP2R7X1dXpH//4h0JCQtSmTRtHglVFRYUkKSYmxtYeExOjw4cPWzUhISGKjIysV3Ph+RUVFQ0enoyOjrbVXPw6kZGRCgkJsdV07ty53utcWHepYDV9+nRNmTLFenxhxgoAALQ8jQpWVVVV9doOHDign/3sZ/q///f/XnOnvu7iQ2zGmEsedrtUTUP1TtRcOIp6uf643W7bLBcAAGi5Gv1bgRfr2rWr5syZU282q7G8Xq+k/525uqCystKaKfJ6vaqtra0X9C6uOXbsWL3tHz9+3FZz8etUVVWprq7usjWVlZWS6s+qAQCAm5NjwUqSgoKCdPToUUe21aVLF3m9Xq1evdpqq62t1caNGzVgwABJUkJCgoKDg2015eXlKikpsWoSExPl9/u1bds2q2br1q3y+/22mpKSEpWXl1s1+fn5crvdSkhIsGo2bdpkuwVDfn6+fD5fvUOEAADg5tSoQ4F//vOfbY+NMSovL9dLL72kgQMHXvF2Tp8+rf/+7/+2Hh88eFDFxcVq27atbrvtNk2ePFmzZs1S165d1bVrV82aNUtt2rRRWlqaJMnj8Wj8+PGaOnWq2rVrp7Zt2yo7O1u9evXSkCFDJEndu3fXsGHDlJWVpYULF0qSHn30UaWmpiouLk6SlJycrB49eig9PV1z587V559/ruzsbGVlZVln/qelpWnmzJnKzMzUv/7rv+rAgQOaNWuWfv3rX3/joUkAAHBzaFSwunBLhAtcLpfat2+vu+++W/Pnz7/i7ezYsUODBw+2Hl84yTsjI0NLly7Vz3/+c33xxRd64oknVFVVpX79+ik/P9/2szkvvPCCWrVqpTFjxuiLL77QPffco6VLlyooKMiqWb58uSZNmmRdPThq1CjbvbOCgoKUm5urJ554QgMHDlRoaKjS0tI0b948q8bj8Wj16tV68skn1bdvX0VGRmrKlCm2E9MBAMDNrdncx+pmcaX3wQDQMnEfKyAwXen3t6PnWAEAANzMGnUo8GoOf/GTLwAA4GbRqGC1a9cu7dy5U19++aV1Avj+/fsVFBSkPn36WHWc1A0AAG4mjQpWI0eOVHh4uF577TXrrudVVVV65JFH9IMf/MD248UAAAA3i0adYzV//nzNnj3b9lMykZGR+s1vfnNVVwUCAAC0JI0KVtXV1Q3ezbyyslKnTp265k4BAAAEokYFq/vuu0+PPPKI/vjHP+rIkSM6cuSI/vjHP2r8+PG6//77ne4jAABAQGjUOVavvvqqsrOz9dBDD6muru6rDbVqpfHjx2vu3LmOdhAAACBQNCpYtWnTRi+//LLmzp2rTz75RMYYfec731FYWJjT/QMAAAgY13SD0PLycpWXl6tbt24KCwsTN3EHAAA3s0YFqxMnTuiee+5Rt27d9MMf/lDl5eWSpJ/+9KfcagEAANy0GhWs/uVf/kXBwcEqLS1VmzZtrPYHHnhAeXl5jnUOAAAgkDTqHKv8/Hz95S9/UYcOHWztXbt21eHDhx3pGAAAQKBp1IzVmTNnbDNVF3z22Wdyu93X3CkAAIBA1Khgddddd+n111+3HrtcLp0/f15z587V4MGDHescAABAIGnUocC5c+dq0KBB2rFjh2pra/Xzn/9cH330kT7//HP97W9/c7qPAAAAAaFRM1Y9evTQhx9+qO9973saOnSozpw5o/vvv1+7du3St7/9baf7CAAAEBCuesaqrq5OycnJWrhwoWbOnHk9+gQAABCQrnrGKjg4WCUlJXK5XNejPwAAAAGrUYcCH374YS1evNjpvgAAAAS0Rp28Xltbq//8z//U6tWr1bdv33q/EbhgwQJHOgcAABBIripY/f3vf1fnzp1VUlKiPn36SJL2799vq+EQIQAAuFldVbDq2rWrysvLtX79eklf/YTNv//7vysmJua6dA4AACCQXNU5VsYY2+P3339fZ86ccbRDAAAAgapRJ69fcHHQAgAAuJldVbByuVz1zqHinCoAAICvXNU5VsYYZWZmWj+0fPbsWT3++OP1rgpcuXKlcz0EAAAIEFcVrDIyMmyPH3roIUc7AwAAEMiuKlgtWbLkevUDAAAg4F3TyesAAAD4X4268zoAILB1npbbYPuhOSNucE+AloUZKwAAAIcQrAAAABxCsAIAAHAIwQoAAMAhzT5Yde7c2brj+9eXJ598UpKUmZlZb13//v1t26ipqdHEiRMVFRWlsLAwjRo1SkeOHLHVVFVVKT09XR6PRx6PR+np6Tp58qStprS0VCNHjlRYWJiioqI0adIk1dbWXtfxAwCAwNHsg9X27dtVXl5uLatXr5Yk/fjHP7Zqhg0bZqt57733bNuYPHmyVq1apZycHG3evFmnT59Wamqqzp07Z9WkpaWpuLhYeXl5ysvLU3FxsdLT0631586d04gRI3TmzBlt3rxZOTk5WrFihaZOnXqd3wEAABAomv3tFtq3b297PGfOHH37299WUlKS1eZ2u+X1eht8vt/v1+LFi7Vs2TINGTJEkvTGG2+oY8eOWrNmjVJSUrR3717l5eWpsLBQ/fr1kyQtWrRIiYmJ2rdvn+Li4pSfn689e/aorKxMPp9PkjR//nxlZmbq2WefVURERIOvX1NTo5qaGutxdXV1498MAADQrDX7Gauvq62t1RtvvKGf/OQnth9/3rBhg6Kjo9WtWzdlZWWpsrLSWldUVKS6ujolJydbbT6fT/Hx8dqyZYskqaCgQB6PxwpVktS/f395PB5bTXx8vBWqJCklJUU1NTUqKiq6ZJ9nz55tHV70eDzq2LHjtb8RAACgWQqoYPXOO+/o5MmTyszMtNqGDx+u5cuXa926dZo/f762b9+uu+++25olqqioUEhIiCIjI23biomJUUVFhVUTHR1d7/Wio6NtNTExMbb1kZGRCgkJsWoaMn36dPn9fmspKytr1NgBAEDz1+wPBX7d4sWLNXz4cNus0QMPPGD9HR8fr759+6pTp07Kzc3V/ffff8ltGWNss15f//taai7mdrvldrsvPSgAANBiBMyM1eHDh7VmzRr99Kc/vWxdbGysOnXqpAMHDkiSvF6vamtrVVVVZaurrKy0ZqC8Xq+OHTtWb1vHjx+31Vw8M1VVVaW6urp6M1kAAODmFDDBasmSJYqOjtaIEZf/HasTJ06orKxMsbGxkqSEhAQFBwdbVxNKUnl5uUpKSjRgwABJUmJiovx+v7Zt22bVbN26VX6/31ZTUlKi8vJyqyY/P19ut1sJCQmOjRMAAASugAhW58+f15IlS5SRkaFWrf736OXp06eVnZ2tgoICHTp0SBs2bNDIkSMVFRWl++67T5Lk8Xg0fvx4TZ06VWvXrtWuXbv00EMPqVevXtZVgt27d9ewYcOUlZWlwsJCFRYWKisrS6mpqYqLi5MkJScnq0ePHkpPT9euXbu0du1aZWdnKysr65JXBAIAgJtLQASrNWvWqLS0VD/5yU9s7UFBQdq9e7fuvfdedevWTRkZGerWrZsKCgoUHh5u1b3wwgsaPXq0xowZo4EDB6pNmzZ69913FRQUZNUsX75cvXr1UnJyspKTk3XHHXdo2bJlttfKzc1V69atNXDgQI0ZM0ajR4/WvHnzrv8bAAAAAoLLGGOauhM3k+rqank8Hvn9fma6gJtQ52m5Td2Fyzo05/KnWwA3qyv9/g6IGSsAAIBAQLACAABwCMEKAADAIQQrAAAAhxCsAAAAHBJQP2kDAIGiuV/9B+D6YMYKAADAIQQrAAAAhxCsAAAAHEKwAgAAcAjBCgAAwCEEKwAAAIcQrAAAABxCsAIAAHAIwQoAAMAhBCsAAACHEKwAAAAcQrACAABwCMEKAADAIQQrAAAAhxCsAAAAHEKwAgAAcAjBCgAAwCEEKwAAAIcQrAAAABxCsAIAAHAIwQoAAMAhBCsAAACHEKwAAAAcQrACAABwCMEKAADAIQQrAAAAhxCsAAAAHEKwAgAAcEizDlYzZsyQy+WyLV6v11pvjNGMGTPk8/kUGhqqQYMG6aOPPrJto6amRhMnTlRUVJTCwsI0atQoHTlyxFZTVVWl9PR0eTweeTwepaen6+TJk7aa0tJSjRw5UmFhYYqKitKkSZNUW1t73cYOAAACT7MOVpLUs2dPlZeXW8vu3butdc8//7wWLFigl156Sdu3b5fX69XQoUN16tQpq2by5MlatWqVcnJytHnzZp0+fVqpqak6d+6cVZOWlqbi4mLl5eUpLy9PxcXFSk9Pt9afO3dOI0aM0JkzZ7R582bl5ORoxYoVmjp16o15EwAAQEBo1dQd+CatWrWyzVJdYIzRb3/7W/3yl7/U/fffL0l67bXXFBMTozfffFOPPfaY/H6/Fi9erGXLlmnIkCGSpDfeeEMdO3bUmjVrlJKSor179yovL0+FhYXq16+fJGnRokVKTEzUvn37FBcXp/z8fO3Zs0dlZWXy+XySpPnz5yszM1PPPvusIiIibtC7AQAAmrNmP2N14MAB+Xw+denSRWPHjtXf//53SdLBgwdVUVGh5ORkq9btdispKUlbtmyRJBUVFamurs5W4/P5FB8fb9UUFBTI4/FYoUqS+vfvL4/HY6uJj4+3QpUkpaSkqKamRkVFRZftf01Njaqrq20LAABomZp1sOrXr59ef/11/eUvf9GiRYtUUVGhAQMG6MSJE6qoqJAkxcTE2J4TExNjrauoqFBISIgiIyMvWxMdHV3vtaOjo201F79OZGSkQkJCrJpLmT17tnXulsfjUceOHa/iHQAAAIGkWQer4cOH60c/+pF69eqlIUOGKDc3V9JXh/wucLlctucYY+q1XezimobqG1PTkOnTp8vv91tLWVnZZesBAEDgatbB6mJhYWHq1auXDhw4YJ13dfGMUWVlpTW75PV6VVtbq6qqqsvWHDt2rN5rHT9+3FZz8etUVVWprq6u3kzWxdxutyIiImwLAABomQIqWNXU1Gjv3r2KjY1Vly5d5PV6tXr1amt9bW2tNm7cqAEDBkiSEhISFBwcbKspLy9XSUmJVZOYmCi/369t27ZZNVu3bpXf77fVlJSUqLy83KrJz8+X2+1WQkLCdR0zAAAIHM36qsDs7GyNHDlSt912myorK/Wb3/xG1dXVysjIkMvl0uTJkzVr1ix17dpVXbt21axZs9SmTRulpaVJkjwej8aPH6+pU6eqXbt2atu2rbKzs61Di5LUvXt3DRs2TFlZWVq4cKEk6dFHH1Vqaqri4uIkScnJyerRo4fS09M1d+5cff7558rOzlZWVhYzUAAAwNKsg9WRI0f04IMP6rPPPlP79u3Vv39/FRYWqlOnTpKkn//85/riiy/0xBNPqKqqSv369VN+fr7Cw8Otbbzwwgtq1aqVxowZoy+++EL33HOPli5dqqCgIKtm+fLlmjRpknX14KhRo/TSSy9Z64OCgpSbm6snnnhCAwcOVGhoqNLS0jRv3rwb9E4AAIBA4DLGmKbuxM2kurpaHo9Hfr+f2S6gBes8Lbepu9Aoh+aMaOouAM3SlX5/N+sZKwDAjXWpQEjgAq5MQJ28DgAA0JwRrAAAABxCsAIAAHAIwQoAAMAhBCsAAACHEKwAAAAcQrACAABwCMEKAADAIQQrAAAAhxCsAAAAHEKwAgAAcAjBCgAAwCEEKwAAAIcQrAAAABxCsAIAAHAIwQoAAMAhBCsAAACHEKwAAAAcQrACAABwCMEKAADAIQQrAAAAhxCsAAAAHEKwAgAAcAjBCgAAwCEEKwAAAIcQrAAAABxCsAIAAHAIwQoAAMAhBCsAAACHtGrqDgAAmr/O03LrtR2aM6IJegI0b8xYAQAAOIRgBQAA4BCCFQAAgEMIVgAAAA5p1sFq9uzZ+u53v6vw8HBFR0dr9OjR2rdvn60mMzNTLpfLtvTv399WU1NTo4kTJyoqKkphYWEaNWqUjhw5YqupqqpSenq6PB6PPB6P0tPTdfLkSVtNaWmpRo4cqbCwMEVFRWnSpEmqra29LmMHAACBp1kHq40bN+rJJ59UYWGhVq9erS+//FLJyck6c+aMrW7YsGEqLy+3lvfee8+2fvLkyVq1apVycnK0efNmnT59WqmpqTp37pxVk5aWpuLiYuXl5SkvL0/FxcVKT0+31p87d04jRozQmTNntHnzZuXk5GjFihWaOnXq9X0TAABAwGjWt1vIy8uzPV6yZImio6NVVFSku+66y2p3u93yer0NbsPv92vx4sVatmyZhgwZIkl644031LFjR61Zs0YpKSnau3ev8vLyVFhYqH79+kmSFi1apMTERO3bt09xcXHKz8/Xnj17VFZWJp/PJ0maP3++MjMz9eyzzyoiIuJ6vAUAACCANOsZq4v5/X5JUtu2bW3tGzZsUHR0tLp166asrCxVVlZa64qKilRXV6fk5GSrzefzKT4+Xlu2bJEkFRQUyOPxWKFKkvr37y+Px2OriY+Pt0KVJKWkpKimpkZFRUWX7HNNTY2qq6ttCwAAaJkCJlgZYzRlyhR9//vfV3x8vNU+fPhwLV++XOvWrdP8+fO1fft23X333aqpqZEkVVRUKCQkRJGRkbbtxcTEqKKiwqqJjo6u95rR0dG2mpiYGNv6yMhIhYSEWDUNmT17tnXelsfjUceOHRv3BgAAgGavWR8K/LoJEyboww8/1ObNm23tDzzwgPV3fHy8+vbtq06dOik3N1f333//JbdnjJHL5bIef/3va6m52PTp0zVlyhTrcXV1NeEKAIAWKiBmrCZOnKg///nPWr9+vTp06HDZ2tjYWHXq1EkHDhyQJHm9XtXW1qqqqspWV1lZac1Aeb1eHTt2rN62jh8/bqu5eGaqqqpKdXV19Wayvs7tdisiIsK2AACAlqlZBytjjCZMmKCVK1dq3bp16tKlyzc+58SJEyorK1NsbKwkKSEhQcHBwVq9erVVU15erpKSEg0YMECSlJiYKL/fr23btlk1W7duld/vt9WUlJSovLzcqsnPz5fb7VZCQoIj4wUAAIGtWR8KfPLJJ/Xmm2/qT3/6k8LDw60ZI4/Ho9DQUJ0+fVozZszQj370I8XGxurQoUP613/9V0VFRem+++6zasePH6+pU6eqXbt2atu2rbKzs9WrVy/rKsHu3btr2LBhysrK0sKFCyVJjz76qFJTUxUXFydJSk5OVo8ePZSenq65c+fq888/V3Z2trKyspiFAgAAkpr5jNUrr7wiv9+vQYMGKTY21lreeustSVJQUJB2796te++9V926dVNGRoa6deumgoIChYeHW9t54YUXNHr0aI0ZM0YDBw5UmzZt9O677yooKMiqWb58uXr16qXk5GQlJyfrjjvu0LJly6z1QUFBys3NVevWrTVw4ECNGTNGo0eP1rx5827cGwIAAJo1lzHGNHUnbibV1dXyeDzy+/3MdAEtWOdpuU3dhevu0JwRTd0F4Ia50u/vZj1jBQAAEEgIVgAAAA5p1ievAwCar0sd7uQQIW5mzFgBAAA4hGAFAADgEIIVAACAQwhWAAAADiFYAQAAOIRgBQAA4BCCFQAAgEMIVgAAAA4hWAEAADiEYAUAAOAQghUAAIBDCFYAAAAOIVgBAAA4pFVTdwAA0LJ0npbbYPuhOSNucE+AG48ZKwAAAIcQrAAAABxCsAIAAHAIwQoAAMAhBCsAAACHEKwAAAAcQrACAABwCMEKAADAIdwgFGhKMzwNtPmvvPZy9QCAG45gBTjpUuHnem7jauoJYQBwXRGsgAucCEXNnSPBj3AGAJdCsAJwda56Ro0gBuDmQbBCy3YzzEI1dxyqxP/gx5lxMyBYoWUgQLUM13M/EtoA3AAEKwA3hxscvg+1vrr6zmffvD4dAXBDEazQPDEDhZvModZpV1xLCAOaL5cxxjR1J24m1dXV8ng88vv9ioiIaOruNA+EKMARLSlwcd4Vmpsr/f4mWN1gLS5YEYqAZo/ABVy7K/3+5lBgI7z88suaO3euysvL1bNnT/32t7/VD37wg6bu1tW52rt4E6CAgNXQYcaWFLaA5oQZq6v01ltvKT09XS+//LIGDhyohQsX6j//8z+1Z88e3Xbbbd/4/Os6Y0X4AXCNAjVwMZOF641DgddJv3791KdPH73yyitWW/fu3TV69GjNnj27Xn1NTY1qamqsx36/X7fddpvKysqcD1azOzi7PQC4jPizi5u6C5dVMjOlqbuAFqS6ulodO3bUyZMn5fFceiKDQ4FXoba2VkVFRZo2bZqtPTk5WVu2bGnwObNnz9bMmTPrtXfs2PG69BEAbpwxTd2By/L8tql7gJbo1KlTBCunfPbZZzp37pxiYmJs7TExMaqoqGjwOdOnT9eUKVOsx+fPn9fnn3+udu3ayeVyOda3C0n6usyENROMsWVgjC3DzTBG6eYYJ2O8MsYYnTp1Sj6f77J1BKtGuDgQGWMuGZLcbrfcbret7Vvf+tb16poiIiJa7AfjAsbYMjDGluFmGKN0c4yTMX6zy81UXXBLo7d+E4qKilJQUFC92anKysp6s1gAAODmQ7C6CiEhIUpISNDq1att7atXr9aAAQOaqFcAAKC54FDgVZoyZYrS09PVt29fJSYm6ne/+51KS0v1+OOPN2m/3G63nn766XqHHVsSxtgyMMaW4WYYo3RzjJMxOovbLTTCyy+/rOeff17l5eWKj4/XCy+8oLvuuqupuwUAAJoYwQoAAMAhnGMFAADgEIIVAACAQwhWAAAADiFYAQAAOIRgFSCeffZZDRgwQG3atLniO7cbYzRjxgz5fD6FhoZq0KBB+uijj2w1NTU1mjhxoqKiohQWFqZRo0bpyJEj12EE36yqqkrp6enyeDzyeDxKT0/XyZMnL/scl8vV4DJ37lyrZtCgQfXWjx079jqPpmGNGWNmZma9/vfv399WE8j7sa6uTr/4xS/Uq1cvhYWFyefz6eGHH9bRo0dtdU29H19++WV16dJFrVu3VkJCgv76179etn7jxo1KSEhQ69at9c///M969dVX69WsWLFCPXr0kNvtVo8ePbRq1arr1f0rcjVjXLlypYYOHar27dsrIiJCiYmJ+stf/mKrWbp0aYOfz7Nnz17voVzS1Yxxw4YNDfb/448/ttUF8n5s6L8vLpdLPXv2tGqa237ctGmTRo4cKZ/PJ5fLpXfeeecbn3NDP48GAeHXv/61WbBggZkyZYrxeDxX9Jw5c+aY8PBws2LFCrN7927zwAMPmNjYWFNdXW3VPP744+bWW281q1evNjt37jSDBw82d955p/nyyy+v00gubdiwYSY+Pt5s2bLFbNmyxcTHx5vU1NTLPqe8vNy2/P73vzcul8t88sknVk1SUpLJysqy1Z08efJ6D6dBjRljRkaGGTZsmK3/J06csNUE8n48efKkGTJkiHnrrbfMxx9/bAoKCky/fv1MQkKCra4p92NOTo4JDg42ixYtMnv27DFPPfWUCQsLM4cPH26w/u9//7tp06aNeeqpp8yePXvMokWLTHBwsPnjH/9o1WzZssUEBQWZWbNmmb1795pZs2aZVq1amcLCwhsypotd7Rifeuop89xzz5lt27aZ/fv3m+nTp5vg4GCzc+dOq2bJkiUmIiKi3ue0qVztGNevX28kmX379tn6//XPVaDvx5MnT9rGVlZWZtq2bWuefvppq6a57cf33nvP/PKXvzQrVqwwksyqVasuW3+jP48EqwCzZMmSKwpW58+fN16v18yZM8dqO3v2rPF4PObVV181xnz1gQoODjY5OTlWzaeffmpuueUWk5eX53jfL2fPnj1Gku0fcUFBgZFkPv744yvezr333mvuvvtuW1tSUpJ56qmnnOpqozV2jBkZGebee++95PqWuB+3bdtmJNm+DJpyP37ve98zjz/+uK3t9ttvN9OmTWuw/uc//7m5/fbbbW2PPfaY6d+/v/V4zJgxZtiwYbaalJQUM3bsWId6fXWudowN6dGjh5k5c6b1+Er/e3WjXO0YLwSrqqqqS26zpe3HVatWGZfLZQ4dOmS1Nbf9+HVXEqxu9OeRQ4Et1MGDB1VRUaHk5GSrze12KykpSVu2bJEkFRUVqa6uzlbj8/kUHx9v1dwoBQUF8ng86tevn9XWv39/eTyeK+7LsWPHlJubq/Hjx9dbt3z5ckVFRalnz57Kzs7WqVOnHOv7lbqWMW7YsEHR0dHq1q2bsrKyVFlZaa1raftRkvx+v1wuV73D3k2xH2tra1VUVGR7fyUpOTn5kmMqKCioV5+SkqIdO3aorq7usjU3ep9JjRvjxc6fP69Tp06pbdu2tvbTp0+rU6dO6tChg1JTU7Vr1y7H+n01rmWMvXv3VmxsrO655x6tX7/etq6l7cfFixdryJAh6tSpk629uezHxrjRn0d+0qaFuvBD0Rf/OHRMTIwOHz5s1YSEhCgyMrJezcU/NH29VVRUKDo6ul57dHT0FffltddeU3h4uO6//35b+7hx49SlSxd5vV6VlJRo+vTp+uCDD+r95uP11tgxDh8+XD/+8Y/VqVMnHTx4UL/61a909913q6ioSG63u8Xtx7Nnz2ratGlKS0uz/Qp9U+3Hzz77TOfOnWvws3SpMVVUVDRY/+WXX+qzzz5TbGzsJWtu9D6TGjfGi82fP19nzpzRmDFjrLbbb79dS5cuVa9evVRdXa1/+7d/08CBA/XBBx+oa9eujo7hmzRmjLGxsfrd736nhIQE1dTUaNmyZbrnnnu0YcMG69c2WtJ+LC8v1/vvv68333zT1t6c9mNj3OjPI8GqCc2YMUMzZ868bM327dvVt2/fRr+Gy+WyPTbG1Gu72JXUXKkrHaNUv69X25ff//73GjdunFq3bm1rz8rKsv6Oj49X165d1bdvX+3cuVN9+vS5om1fzvUe4wMPPGD9HR8fr759+6pTp07Kzc2tFyKvZrtX40btx7q6Oo0dO1bnz5/Xyy+/bFt3vffjN7naz1JD9Re3N+bzeT01tj9/+MMfNGPGDP3pT3+yBev+/fvbLrQYOHCg+vTpoxdffFH//u//7lzHr8LVjDEuLk5xcXHW48TERJWVlWnevHm2nzFrKftx6dKl+ta3vqXRo0fb2pvjfrxaN/LzSLBqQhMmTPjGq5o6d+7cqG17vV5JXyX12NhYq72ystJK5V6vV7W1taqqqrLNdlRWVmrAgAGNet2LXekYP/zwQx07dqzeuuPHj9f7v4iG/PWvf9W+ffv01ltvfWNtnz59FBwcrAMHDjjyhXyjxnhBbGysOnXqpAMHDkhqOfuxrq5OY8aM0cGDB7Vu3TrbbFVDnN6PlxIVFaWgoKB6/+f69c/Sxbxeb4P1rVq1Urt27S5bczX/FpzSmDFe8NZbb2n8+PF6++23NWTIkMvW3nLLLfrud79r/du9ka5ljF/Xv39/vfHGG9bjlrIfjTH6/e9/r/T0dIWEhFy2tin3Y2Pc8M/jVZ+VhSZ1tSevP/fcc1ZbTU1Ngyevv/XWW1bN0aNHm/Sk561bt1pthYWFV3zSc0ZGRr2ryC5l9+7dRpLZuHFjo/vbGNc6xgs+++wz43a7zWuvvWaMaRn7sba21owePdr07NnTVFZWXtFr3cj9+L3vfc/87Gc/s7V17979sievd+/e3db2+OOP1ztZdvjw4baaYcOGNelJz1czRmOMefPNN03r1q2/8eThC86fP2/69u1rHnnkkWvpaqM1ZowX+9GPfmQGDx5sPW4J+9GY/z1Rf/fu3d/4Gk29H79OV3jy+o38PBKsAsThw4fNrl27zMyZM80//dM/mV27dpldu3aZU6dOWTVxcXFm5cqV1uM5c+YYj8djVq5caXbv3m0efPDBBm+30KFDB7NmzRqzc+dOc/fddzfpZfp33HGHKSgoMAUFBaZXr171LtO/eIzGGOP3+02bNm3MK6+8Um+b//3f/21mzpxptm/fbg4ePGhyc3PN7bffbnr37h0QYzx16pSZOnWq2bJlizl48KBZv369SUxMNLfeemuL2Y91dXVm1KhRpkOHDqa4uNh2OXdNTY0xpun344VL2BcvXmz27NljJk+ebMLCwqwrp6ZNm2bS09Ot+guXd//Lv/yL2bNnj1m8eHG9y7v/9re/maCgIDNnzhyzd+9eM2fOnGZxmf6VjvHNN980rVq1Mv/xH/9xyVtgzJgxw+Tl5ZlPPvnE7Nq1yzzyyCOmVatWtuB9I13tGF944QWzatUqs3//flNSUmKmTZtmJJkVK1ZYNYG+Hy946KGHTL9+/RrcZnPbj6dOnbK+AyWZBQsWmF27dllXETf155FgFSAyMjKMpHrL+vXrrRpJZsmSJdbj8+fPm6efftp4vV7jdrvNXXfdVe//Rr744gszYcIE07ZtWxMaGmpSU1NNaWnpDRqV3YkTJ8y4ceNMeHi4CQ8PN+PGjat3mfPFYzTGmIULF5rQ0NAG72lUWlpq7rrrLtO2bVsTEhJivv3tb5tJkybVuw/UjXK1Y/zHP/5hkpOTTfv27U1wcLC57bbbTEZGRr19FMj78eDBgw3+2/76v+/msB//4z/+w3Tq1MmEhISYPn362GbKMjIyTFJSkq1+w4YNpnfv3iYkJMR07ty5weD/9ttvm7i4OBMcHGxuv/122xd2U7iaMSYlJTW4zzIyMqyayZMnm9tuu82EhISY9u3bm+TkZLNly5YbOKL6rmaMzz33nPn2t79tWrdubSIjI833v/99k5ubW2+bgbwfjflq1js0NNT87ne/a3B7zW0/Xphdu9S/vab+PLqM+Z8zuAAAAHBNuI8VAACAQwhWAAAADiFYAQAAOIRgBQAA4BCCFQAAgEMIVgAAAA4hWAEAADiEYAUAAOAQghUAAIBDCFYAAAAOIVgBAAA45P8Dj57I02u0kwkAAAAASUVORK5CYII=",
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "loan[\n",
    "    (loan.loan_status.str.contains('Fully'))&(loan.term.str.contains('36'))\n",
    "].ROI.plot.hist(bins=[-1+i*0.025 for i in range(80)])\n",
    "                      \n",
    "loan[\n",
    "    (loan.loan_status.str.contains('Charged'))&(loan.term.str.contains('36'))\n",
    "].ROI.plot.hist(bins=[-1+i*0.025 for i in range(80)])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ded3c63e",
   "metadata": {},
   "source": [
    "calculate IRR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "a358b842",
   "metadata": {},
   "outputs": [],
   "source": [
    "# For fully paid but they paid right after the loans were issued\n",
    "tmp.loc[(loan.issue_d == loan.last_pymnt_d)&(loan.loan_status.str.contains('Fully')), 'num_mths_to_pay'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "65bf883a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cf = tmp.apply(lambda r: [r['funded_amnt']] + [r['avg_pymnt']]*int(r['num_mths_to_pay']), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "8bc4e944",
   "metadata": {},
   "outputs": [],
   "source": [
    "def irr(cash_flow):\n",
    "    \"\"\"\n",
    "    Solving this to get IRR:\n",
    "    p0 + p1 * (1 + IRR)^(-1) + p2 * (1 + IRR)^(-2) + ... + pn * (1 + IRR)^(-n) = 0 _ (*)\n",
    "\n",
    "    Let g = 1 + IRR and multiply (*) by g^n and you will get:\n",
    "    p0 * g^n + p1 * g^(n-1) + p2 * g^(n-2) + ... + pn = 0\n",
    "\n",
    "    Then solve the polynomial and get the positive roots\n",
    "    IRR = g - 1\n",
    "\n",
    "    :param: cash_flow: a stream of cash flow to pay and receive with a NPV = 0\n",
    "    \"\"\"\n",
    "    cash_flow = np.array(cash_flow)\n",
    "    same_sign = np.all(cash_flow > 0) if cash_flow[0] > 0 else np.all(cash_flow < 0)\n",
    "    if same_sign:\n",
    "        if len(cash_flow) == 1 and cash_flow[0] < 0:\n",
    "            return -1\n",
    "        \n",
    "        raise ValueError(\"cash_flow must contain positive and negative values\")\n",
    "\n",
    "    g = np.roots(cash_flow)\n",
    "    IRR = np.real(g[np.isreal(g)]) - 1\n",
    "\n",
    "    # realistic IRR\n",
    "    IRR = IRR[(IRR <= 1)&(IRR >= -1)]\n",
    "\n",
    "    # if no real solution\n",
    "    if len(IRR) == 0:\n",
    "        return np.nan\n",
    "\n",
    "    # if only one real solution\n",
    "    if len(IRR) == 1:\n",
    "        return IRR[0]\n",
    "\n",
    "    # check sign of all IRR solutions\n",
    "    same_sign = np.all(IRR > 0) if IRR[0] > 0 else np.all(IRR < 0)\n",
    "    \n",
    "    # if not the same, first filter potential IRR by comparing the total positive and negative cash flows\n",
    "    if not same_sign:\n",
    "        pos = sum(cash_flow[cash_flow>0])\n",
    "        neg = sum(cash_flow[cash_flow<0])\n",
    "        if pos >= neg:\n",
    "            IRR = IRR[IRR >= 0]\n",
    "        else:\n",
    "            IRR = IRR[IRR < 0]\n",
    "    \n",
    "    # pick the smallest one in magnitude and return\n",
    "    abs_IRR = np.abs(IRR)\n",
    "    return IRR[np.argmin(abs_IRR)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "ea3f70af",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "irr_res = cf.apply(irr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "29996191",
   "metadata": {},
   "outputs": [],
   "source": [
    "loan['IRR'] = irr_res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "e1526c3a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-0.012521647695488063 0.012313792411589988 -0.14495831525395558\n"
     ]
    }
   ],
   "source": [
    "print(loan.loc[(loan.term.str.contains('36')), 'IRR'].mean(),\n",
    "      loan.loc[(loan.term.str.contains('36'))&(loan.loan_status.str.contains('Fully')), 'IRR'].mean(),\n",
    "      loan.loc[(loan.term.str.contains('36'))&(loan.loan_status.str.contains('Charged')), 'IRR'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "f17176ea",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-0.017049150992344653 0.013682444402946659 -0.14227073445799196\n"
     ]
    }
   ],
   "source": [
    "print(loan['IRR'].mean(),\n",
    "      loan.loc[(loan.loan_status.str.contains('Fully')), 'IRR'].mean(),\n",
    "      loan.loc[(loan.loan_status.str.contains('Charged')), 'IRR'].mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71f770ed",
   "metadata": {},
   "source": [
    "drop irrelevant columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "e59b4d71",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "to_drop = [\n",
    "    col for col in loan.columns if \n",
    "        ('hardship' in col and 'hardship_flag' != col) or \n",
    "        ('mths_since_' in col and '_binary' not in col) or \n",
    "        ('sec_app' in col) or \n",
    "        ('joint' in col)\n",
    "        \n",
    "] + [\n",
    "    'orig_projected_additional_accrued_interest', \n",
    "    'payment_plan_start_date', 'pymnt_plan',\n",
    "    'issue_d_year', 'policy_code',\n",
    "    'next_pymnt_d', 'earliest_cr_line',\n",
    "    'url', 'last_credit_pull_d', 'last_pymnt_d'\n",
    "    \n",
    "] + mths_since_to_drop\n",
    "\n",
    "loan.drop(to_drop, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "1972214b",
   "metadata": {},
   "outputs": [],
   "source": [
    "class MissingValueError(Exception):\n",
    "    pass\n",
    "\n",
    "if loan.isna().sum().sum() > 0:\n",
    "    raise MissingValueError('Found missing value')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "9159d661",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1767178, 118)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5a8ed71",
   "metadata": {},
   "source": [
    "binning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "3b2df972",
   "metadata": {},
   "outputs": [],
   "source": [
    "def binning_int_rate(int_rate):\n",
    "    if int_rate < 10:\n",
    "        return 'less than 10%'\n",
    "    for i in range(2, 6):\n",
    "        if i*5 <= int_rate and (i+1)*5 > int_rate:\n",
    "            return f'within {i*5}% and {i*5+5}%'\n",
    "    return 'greater than or equal to 30%'\n",
    "\n",
    "loan['int_rate_bin'] = loan['int_rate'].apply(binning_int_rate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "621c4464",
   "metadata": {},
   "outputs": [],
   "source": [
    "def binning_income(income):\n",
    "    if income < 20000:\n",
    "        return 'less than 20k'\n",
    "    for i in range(2, 16):\n",
    "        if i*10000 <= income and (i+1)*10000 > income:\n",
    "            return f'within {i*10}k and {(i+1)*10}k'\n",
    "    return f'greater than or equal to {(i+1)*10}k'\n",
    "\n",
    "loan['annual_inc_bin'] = loan['annual_inc'].apply(binning_income)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c0bab0c",
   "metadata": {},
   "source": [
    "load to database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "6ebb7c3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import mysql.connector\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "34afef14",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to MySQL Server\n"
     ]
    }
   ],
   "source": [
    "with open('db_config.json', 'r') as json_file:\n",
    "    db_config = json.load(json_file)\n",
    "\n",
    "try:\n",
    "    # Attempt to connect to the MySQL server\n",
    "    connection = mysql.connector.connect(**db_config)\n",
    "\n",
    "    # Check if the connection is successful\n",
    "    if connection.is_connected():\n",
    "        print(\"Connected to MySQL Server\")\n",
    "        \n",
    "#     sql = \"DROP TABLE matured_loan\"\n",
    "#     cursor = connection.cursor()\n",
    "#     cursor.execute(sql)\n",
    "\n",
    "    # Close the connection\n",
    "    connection.close()\n",
    "\n",
    "except mysql.connector.Error as err:\n",
    "    print(f\"Error: {err}\")\n",
    "    print(\"Failed to connect to MySQL Server\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "0b56054d",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(\n",
    "    f\"mysql+mysqlconnector://{db_config['user']}:{db_config['password']}@{db_config['host']}/{db_config['database']}\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "12fe195b",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Transaction committed successfully.\n"
     ]
    }
   ],
   "source": [
    "with engine.begin() as connection:\n",
    "    try:\n",
    "        table_name = 'matured_loan'\n",
    "\n",
    "        # Start an explicit control over transaction\n",
    "        tran = connection.begin()\n",
    "        \n",
    "        # Insert the DataFrame data into the MySQL table using explicit transaction in append mode\n",
    "        loan.to_sql(name=table_name, \n",
    "                    con=connection, \n",
    "                    if_exists='replace', \n",
    "                    chunksize=1000,\n",
    "                    index=False)\n",
    "        \n",
    "        # Commit the transaction explicitly if above insert statements work fine\n",
    "        tran.commit()\n",
    "        print(\"Transaction committed successfully.\")\n",
    "    except Exception as e:\n",
    "        # Roll back the transaction if any error occurs\n",
    "        tran.rollback()\n",
    "        print(\"Transaction rolled back due to an error:\", e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "099352c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fdae6c20",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}