2. Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.simplefilter('ignore')\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load Dataset\n",
    "First, read the combined data from $2014$ until $2018$ from local disk."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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_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>emp_length</th>\n",
       "      <th>...</th>\n",
       "      <th>pub_rec_bankruptcies</th>\n",
       "      <th>tax_liens</th>\n",
       "      <th>tot_hi_cred_lim</th>\n",
       "      <th>total_bal_ex_mort</th>\n",
       "      <th>total_bc_limit</th>\n",
       "      <th>total_il_high_credit_limit</th>\n",
       "      <th>hardship_flag</th>\n",
       "      <th>disbursement_method</th>\n",
       "      <th>debt_settlement_flag</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10400</td>\n",
       "      <td>10400</td>\n",
       "      <td>10400.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>6.99%</td>\n",
       "      <td>321.08</td>\n",
       "      <td>A</td>\n",
       "      <td>A3</td>\n",
       "      <td>Truck Driver Delivery Personel</td>\n",
       "      <td>8 years</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>179407</td>\n",
       "      <td>15030</td>\n",
       "      <td>13000</td>\n",
       "      <td>11325</td>\n",
       "      <td>N</td>\n",
       "      <td>Cash</td>\n",
       "      <td>N</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15000</td>\n",
       "      <td>15000</td>\n",
       "      <td>15000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>12.39%</td>\n",
       "      <td>336.64</td>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>MANAGEMENT</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>196500</td>\n",
       "      <td>149140</td>\n",
       "      <td>10000</td>\n",
       "      <td>12000</td>\n",
       "      <td>N</td>\n",
       "      <td>Cash</td>\n",
       "      <td>N</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9600</td>\n",
       "      <td>9600</td>\n",
       "      <td>9600.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>13.66%</td>\n",
       "      <td>326.53</td>\n",
       "      <td>C</td>\n",
       "      <td>C3</td>\n",
       "      <td>Admin Specialist</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>52490</td>\n",
       "      <td>38566</td>\n",
       "      <td>21100</td>\n",
       "      <td>24890</td>\n",
       "      <td>N</td>\n",
       "      <td>Cash</td>\n",
       "      <td>N</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7650</td>\n",
       "      <td>7650</td>\n",
       "      <td>7650.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>13.66%</td>\n",
       "      <td>260.20</td>\n",
       "      <td>C</td>\n",
       "      <td>C3</td>\n",
       "      <td>Technical Specialist</td>\n",
       "      <td>&lt; 1 year</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>82331</td>\n",
       "      <td>64426</td>\n",
       "      <td>4900</td>\n",
       "      <td>64031</td>\n",
       "      <td>N</td>\n",
       "      <td>Cash</td>\n",
       "      <td>N</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21425</td>\n",
       "      <td>21425</td>\n",
       "      <td>21425.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>15.59%</td>\n",
       "      <td>516.36</td>\n",
       "      <td>D</td>\n",
       "      <td>D1</td>\n",
       "      <td>Programming Analysis Supervisor</td>\n",
       "      <td>6 years</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>57073</td>\n",
       "      <td>42315</td>\n",
       "      <td>15000</td>\n",
       "      <td>35573</td>\n",
       "      <td>N</td>\n",
       "      <td>Cash</td>\n",
       "      <td>N</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 88 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_amnt  funded_amnt  funded_amnt_inv        term int_rate  installment  \\\n",
       "0      10400        10400          10400.0   36 months    6.99%       321.08   \n",
       "1      15000        15000          15000.0   60 months   12.39%       336.64   \n",
       "2       9600         9600           9600.0   36 months   13.66%       326.53   \n",
       "3       7650         7650           7650.0   36 months   13.66%       260.20   \n",
       "4      21425        21425          21425.0   60 months   15.59%       516.36   \n",
       "\n",
       "  grade sub_grade                        emp_title emp_length  ...   \\\n",
       "0     A        A3   Truck Driver Delivery Personel    8 years  ...    \n",
       "1     C        C1                       MANAGEMENT  10+ years  ...    \n",
       "2     C        C3                 Admin Specialist  10+ years  ...    \n",
       "3     C        C3             Technical Specialist   < 1 year  ...    \n",
       "4     D        D1  Programming Analysis Supervisor    6 years  ...    \n",
       "\n",
       "  pub_rec_bankruptcies  tax_liens tot_hi_cred_lim total_bal_ex_mort  \\\n",
       "0                    0          0          179407             15030   \n",
       "1                    0          0          196500            149140   \n",
       "2                    0          0           52490             38566   \n",
       "3                    0          0           82331             64426   \n",
       "4                    0          0           57073             42315   \n",
       "\n",
       "  total_bc_limit total_il_high_credit_limit hardship_flag disbursement_method  \\\n",
       "0          13000                      11325             N                Cash   \n",
       "1          10000                      12000             N                Cash   \n",
       "2          21100                      24890             N                Cash   \n",
       "3           4900                      64031             N                Cash   \n",
       "4          15000                      35573             N                Cash   \n",
       "\n",
       "  debt_settlement_flag  year  \n",
       "0                    N  2014  \n",
       "1                    N  2014  \n",
       "2                    N  2014  \n",
       "3                    N  2014  \n",
       "4                    N  2014  \n",
       "\n",
       "[5 rows x 88 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv('./data/data_2014_2018.csv')\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1642574 entries, 0 to 1642573\n",
      "Columns: 88 entries, loan_amnt to year\n",
      "dtypes: float64(25), int64(39), object(24)\n",
      "memory usage: 1.1+ GB\n"
     ]
    }
   ],
   "source": [
    "data.info(verbose=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Features collected after loan is issued\n",
    "For this problem, we will assume that our model will run at the moment one begins to apply for the loan. Thus, there should be no information about user's payment behaviors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1642574 entries, 0 to 1642573\n",
      "Columns: 68 entries, loan_amnt to year\n",
      "dtypes: float64(14), int64(37), object(17)\n",
      "memory usage: 852.2+ MB\n"
     ]
    }
   ],
   "source": [
    "ongo_columns = ['funded_amnt', 'funded_amnt_inv', 'issue_d', 'pymnt_plan', 'out_prncp', \n",
    "                'out_prncp_inv', 'total_pymnt', 'total_pymnt_inv', 'total_rec_prncp', \n",
    "                'policy_code', 'total_rec_int', 'total_rec_late_fee', 'recoveries', \n",
    "                'collection_recovery_fee', 'last_pymnt_d', 'last_pymnt_amnt', \n",
    "                'last_credit_pull_d', 'hardship_flag', 'disbursement_method', \n",
    "                'debt_settlement_flag']\n",
    "data = data.drop(labels=ongo_columns, axis=1)\n",
    "\n",
    "data.info(verbose=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Parsing `loan_status`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Current               808178\n",
       "Fully Paid            623342\n",
       "Charged Off           173491\n",
       "Late (31-120 days)     19438\n",
       "In Grace Period        13526\n",
       "Late (16-30 days)       4575\n",
       "Default                   24\n",
       "Name: loan_status, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['loan_status'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are a series of different kinds of loan status. Based on the explanation from [Lending Club](https://help.lendingclub.com/hc/en-us/articles/215488038-What-do-the-different-Note-statuses-mean-), The explanation for each status are listed below:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| Loan Status     | Explanation                                           |\n",
    "| --------------- | ----------------------------------------------------- |\n",
    "| Current         | Loan is up to date on all outstanding payments        |\n",
    "| Fully Paid      | Loan has been fully repaid, either at the expiration of the 3- or 5-year year term or as a result of a prepayment                                                           |\n",
    "| Default         | Loan has not been current for 121 days or more        |\n",
    "| Charged Off     | Loan for which there is no longer a reasonable expectation of further payments. Generally, Charge Off occurs no later than 30 days after the Default status is reached. Upon Charge Off, the remaining principal balance of the Note is deducted from the account balance                          |\n",
    "| In Grace Period | Loan is past due but within the 15-day grace period   |\n",
    "| Late (16-30)    | Loan has not been current for 16 to 30 days           |\n",
    "| Late (31-120)   | Loan has not been current for 31 to 120 days          |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For this project, we don't care about the loan that is in **Current** status. Instead, we are more interested in whether the loan is **Good** or **Bad**. Here, we assume loan in **Good** status if it will be fully paid, and the loan in **Bad** status if it is **Charged Off**, **Default**, or **Late (16-30 days, or 31-120 days)**. For loans that are in **Grace Period**, we will remove them from our data due to uncertainty."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# only keep the data that we are certainty about their final status\n",
    "used_status = ['Charged Off', 'Fully Paid', 'Late (16-30 days)', 'Late (31-120 days)', 'Default']\n",
    "data = data[data['loan_status'].isin(used_status)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 820870 entries, 0 to 1642573\n",
      "Columns: 69 entries, loan_amnt to target\n",
      "dtypes: float64(14), int64(38), object(17)\n",
      "memory usage: 438.4+ MB\n"
     ]
    }
   ],
   "source": [
    "# Encoding the `loan_status`\n",
    "# status 1: 'Charged Off', 'Late (16-30 days)', 'Late (31-120 days)', 'Default'\n",
    "# status 0: 'Fully Paid'\n",
    "data['target'] = 1\n",
    "data.loc[data['loan_status'] == 'Fully Paid', 'target'] = 0\n",
    "\n",
    "data.info(verbose=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Split into training and test dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After above procedures, we have reduced the dataset size from $1,642,574$ to $820,870$, and the features from $88$ to $68$ (including the `target`)."
   ]
  },
  {
   "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>year</th>\n",
       "      <th>counts</th>\n",
       "      <th>ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>215210</td>\n",
       "      <td>0.262173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>302540</td>\n",
       "      <td>0.368560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>204393</td>\n",
       "      <td>0.248996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>92578</td>\n",
       "      <td>0.112780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018</td>\n",
       "      <td>6149</td>\n",
       "      <td>0.007491</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  counts     ratio\n",
       "0  2014  215210  0.262173\n",
       "1  2015  302540  0.368560\n",
       "2  2016  204393  0.248996\n",
       "3  2017   92578  0.112780\n",
       "4  2018    6149  0.007491"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate the number of records for each year\n",
    "year_count = data.groupby('year')['target'].count().reset_index()\n",
    "year_count = year_count.rename(columns={'target': 'counts'})\n",
    "year_count['ratio'] = year_count['counts'] / len(data)\n",
    "year_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAABHgAAAGoCAYAAAA99FLLAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvhp/UCwAAIABJREFUeJzs3X3YZWV9H/rvz0EMalVQfAkvgpE2QeMRM0UbWkzFF0xaMSfaYGMhHk9pcqAxxyQTbHJJg6XHGJO0RmNCKw0mUaSa1GmCNUTQVI2G8SUSIFyMqDDgyCCIJhpw4Hf+2Gtw+/A8M8/A8+y918znc13Ptde619tvX4vR+/rute67ujsAAAAAjNeD5l0AAAAAAA+MgAcAAABg5AQ8AAAAACMn4AEAAAAYOQEPAAAAwMgJeAAAAABGTsADAAAAMHICHgAAAICRE/AAAAAAjNwB8y5gUT3mMY/po446at5lAAAP0Cc+8Ylbu/vQedcxa/oyALBvWG1fRsCzgqOOOipbtmyZdxkAwANUVV+Ydw3zoC8DAPuG1fZlvKIFAAAAMHICHgCAGaiqk6vq2qraWlVnL7P9J6rqyqr6dFV9uKqOndr2muG4a6vqBbOtHAAYAwEPAMA6q6oNSd6S5IVJjk3ysukAZ/CO7v7e7n56kjck+bXh2GOTnJrkKUlOTvKbw/kAAO4l4AEAWH/HJ9na3dd3911JLkpyyvQO3f3VqdWHJelh+ZQkF3X3nd39uSRbh/MBANzLIMsAAOvvsCQ3Tq1vS/LMpTtV1ZlJXp3kwCTPmTr2Y0uOPWx9ygQAxsoTPAAA66+Waev7NHS/pbu/K8nPJ/nFvTk2SarqjKraUlVbduzYcb+LBQDGR8ADALD+tiU5Ymr98CQ372b/i5K8eG+P7e7zu3tjd2889NBDH0C5AMDYCHgAANbfFUmOqaqjq+rATAZN3jy9Q1UdM7X6Q0muG5Y3Jzm1qh5SVUcnOSbJX8ygZgBgRIzBAwCwzrp7Z1WdleT9STYkuaC7r6qqc5Ns6e7NSc6qqucm+WaS25OcPhx7VVVdnOTqJDuTnNndd8/liwAAC0vAAwAwA919SZJLlrS9dmr5Vbs59rwk561fdQDA2HlFCwAAAGDkBDwAAAAAIyfgAQAAABg5AQ8AAADAyAl4AAAAAEZuZgFPVX1HVf1FVf1lVV1VVb80tB9dVR+vquuq6l1VdeDQ/pBhfeuw/aipc71maL+2ql4w1X7y0La1qs6eal/2GgAAAGO0adOmnHbaadm0adO8SwEWxCynSb8zyXO6+2+q6sFJPlxV70vy6iS/3t0XVdVvJXllkrcOn7d395Or6tQkv5zkR6vq2CSnJnlKku9M8qdV9feHa7wlyfOSbEtyRVVt7u6rh2OXuwb7mBvO/d55lzBqR772ynmXAADAKmzfvj033XTTvMsAFsjMnuDpib8ZVh88/HWS5yR599B+YZIXD8unDOsZtp9UVTW0X9Tdd3b355JsTXL88Le1u6/v7ruSXJTklOGYla4BAAAAMHqzfIInVbUhySeSPDmTp20+m+Qr3b1z2GVbksOG5cOS3Jgk3b2zqu5I8uih/WNTp50+5sYl7c8cjlnpGgAAAMs64TdOmHcJKzrwKwfmQXlQbvzKjQtd50f+7UfmXQLsN2Y6yHJ3393dT09yeCZP3HzPcrsNn7XCtrVqv4+qOqOqtlTVlh07diy3CwAAAMDCmcssWt39lSQfTPKsJI+qql1PEh2e5OZheVuSI5Jk2P7IJLdNty85ZqX2W3dzjaV1nd/dG7t746GHHvpAviIAAADAzMxyFq1Dq+pRw/JBSZ6b5Joklyd5ybDb6UneOyxvHtYzbL+su3toP3WYZevoJMck+YskVyQ5Zpgx68BMBmLePByz0jUAAABGpx/auedh96QfuuzLCcB+aJZj8DwhyYXDODwPSnJxd/9RVV2d5KKq+g9JPpXkbcP+b0vyu1W1NZMnd05Nku6+qqouTnJ1kp1Jzuzuu5Okqs5K8v4kG5Jc0N1XDef6+RWuAQAAMDrfPOGb8y4BWDAzC3i6+zNJjlum/fpMxuNZ2v53SV66wrnOS3LeMu2XJLlktdcAAAAA2BfMZQweAAAAANaOgAcAAABg5AQ8AAAAACMn4AEAAAAYOQEPAAAAwMgJeAAAAABGTsADAAAAMHICHgAAAICRE/AAAAAAjJyABwAAAGDkBDwAAAAAIyfgAQAAABg5AQ8AAADAyAl4AAAAAEZOwAMAAAAwcgIeAAAAgJET8AAAAACMnIAHAAAAYOQEPAAAAAAjJ+ABAAAAGDkBDwAAAMDICXgAAAAARk7AAwAAADByB8y7AAAAABi7TZs2Zfv27Xn84x+fN7zhDfMuh/2QgAcAAAAeoO3bt+emm26adxnsx7yiBQAAADByAh4AAACAkRPwAAAAAIycMXgAAAAYhQ+d+Ox5l7CibxywIanKN7ZtW9g6n/1nH5p3CawjT/AAAAAAjJyABwAAAGDkBDwAAAAAIyfgAQAAABg5gywDAADAA/So7m/7hFkT8AAAAMAD9PK775l3CeznvKIFAAAAMHICHgAAAICRE/AAAAAAjJyABwBgBqrq5Kq6tqq2VtXZy2x/dVVdXVWfqaoPVNUTp7bdXVWfHv42z7ZyAGAMDLIMALDOqmpDkrckeV6SbUmuqKrN3X311G6fSrKxu79eVT+Z5A1JfnTY9o3ufvpMiwYARsUTPAAA6+/4JFu7+/ruvivJRUlOmd6huy/v7q8Pqx9LcviMawQARkzAAwCw/g5LcuPU+rahbSWvTPK+qfXvqKotVfWxqnrxSgdV1RnDflt27NjxwCoGAEbFK1oAAOuvlmnrZXesenmSjUmePdV8ZHffXFVPSnJZVV3Z3Z+9zwm7z09yfpJs3Lhx2fMDAPsmT/AAAKy/bUmOmFo/PMnNS3eqqucm+YUkL+ruO3e1d/fNw+f1ST6Y5Lj1LBYAGB8BDwDA+rsiyTFVdXRVHZjk1CTfNhtWVR2X5LczCXdumWo/uKoeMiw/JskJSaYHZwYA8IoWAMB66+6dVXVWkvcn2ZDkgu6+qqrOTbKluzcn+ZUkD0/y36sqSW7o7hcl+Z4kv11V92Ty49zrl8y+BQAg4AEAmIXuviTJJUvaXju1/NwVjvtoku9d3+oAgLHzihYAAADAyAl4AAAAAEZOwAMAAAAwcjMLeKrqiKq6vKquqaqrqupVQ/u/r6qbqurTw98PTh3zmqraWlXXVtULptpPHtq2VtXZU+1HV9XHq+q6qnrXMEtFquohw/rWYftRs/reAAAAAOttlk/w7EzyM939PUmeleTMqjp22Pbr3f304e+SJBm2nZrkKUlOTvKbVbWhqjYkeUuSFyY5NsnLps7zy8O5jklye5JXDu2vTHJ7dz85ya8P+wEAAKu0adOmnHbaadm0adO8SwFgGTMLeLr7i939yWH5a0muSXLYbg45JclF3X1nd38uydYkxw9/W7v7+u6+K8lFSU6pyXyiz0ny7uH4C5O8eOpcFw7L705y0rA/AACwCtu3b89NN92U7du3z7sUAJYxlzF4hlekjkvy8aHprKr6TFVdUFUHD22HJblx6rBtQ9tK7Y9O8pXu3rmk/dvONWy/Y9h/aV1nVNWWqtqyY8eOB/QdAQAAAGblgFlfsKoenuQ9SX66u79aVW9N8rokPXz+apL/K8lyT9h0lg+lejf7Zw/bvtXQfX6S85Nk48aN99kOAADr6YZzv3feJaxo522HJDkgO2/7wsLWeeRrr5x3CQBzM9MneKrqwZmEO7/f3X+QJN39pe6+u7vvSfJfMnkFK5k8gXPE1OGHJ7l5N+23JnlUVR2wpP3bzjVsf2SS29b22wEAAADMxyxn0aokb0tyTXf/2lT7E6Z2++EkfzUsb05y6jAD1tFJjknyF0muSHLMMGPWgZkMxLy5uzvJ5UleMhx/epL3Tp3r9GH5JUkuG/YHAAAAGL1ZvqJ1QpJ/leTKqvr00PbvMpkF6+mZvDL1+ST/Jkm6+6qqujjJ1ZnMwHVmd9+dJFV1VpL3J9mQ5ILuvmo4388nuaiq/kOST2USKGX4/N2q2prJkzunrucXBQCAfc1jvuOeJDuHTwAWzcwCnu7+cJYfC+eS3RxzXpLzlmm/ZLnjuvv6fOsVr+n2v0vy0r2pFwAA+JaffdpX5l0CALsxl1m0AAAAAFg7Ah4AAACAkRPwAAAAAIycgAcAAABg5AQ8AAAAACMn4AEAAAAYOQEPAAAAwMgJeAAAAABGTsADAAAAMHICHgAAAICRE/AAAAAAjJyABwAAAGDkBDwAAAAAIyfgAQAAABg5AQ8AAADAyAl4AAAAAEZOwAMAAAAwcgIeAAAAgJET8AAAAACMnIAHAAAAYOQEPAAAAAAjJ+ABAAAAGDkBDwAAAMDICXgAAAAARk7AAwAAADByAh4AAACAkRPwAAAAAIycgAcAAABg5AQ8AAAAACMn4AEAAAAYOQEPAAAAwMgJeAAAAABGTsADAAAAMHICHgAAAICRE/AAAAAAjNwB8y5g7L7v594+7xJG7RO/ctq8SwAAAIDR8wQPAAAAwMgJeAAAAABGTsADAAAAMHICHgAAAICRE/AAAMxAVZ1cVddW1daqOnuZ7a+uqqur6jNV9YGqeuLUttOr6rrh7/TZVg4AjIGABwBgnVXVhiRvSfLCJMcmeVlVHbtkt08l2djdT0vy7iRvGI49JMk5SZ6Z5Pgk51TVwbOqHQAYBwEPAMD6Oz7J1u6+vrvvSnJRklOmd+juy7v768Pqx5IcPiy/IMml3X1bd9+e5NIkJ8+obgBgJAQ8AADr77AkN06tbxvaVvLKJO/b22Or6oyq2lJVW3bs2PEAygUAxuaAeRcAALAfqGXaetkdq16eZGOSZ+/tsd19fpLzk2Tjxo3L7gMA+4NNmzZl+/btefzjH583vOEN8y5nJgQ8AADrb1uSI6bWD09y89Kdquq5SX4hybO7+86pY39gybEfXJcqAWAfsX379tx0003zLmOmvKIFALD+rkhyTFUdXVUHJjk1yebpHarquCS/neRF3X3L1Kb3J3l+VR08DK78/KENAOBenuABAFhn3b2zqs7KJJjZkOSC7r6qqs5NsqW7Nyf5lSQPT/LfqypJbujuF3X3bVX1ukxCoiQ5t7tvm8PXAAAWmIAHWDcn/MYJ8y5h9D7ybz8y7xKANdLdlyS5ZEnba6eWn7ubYy9IcsH6VQcAjN3MXtGqqiOq6vKquqaqrqqqVw3th1TVpVV13fB58NBeVfWmqtpaVZ+pqmdMnev0Yf/rqur0qfbvq6orh2PeVMPPXytdAwAAAGBfMMsxeHYm+Znu/p4kz0pyZlUdm+TsJB/o7mOSfGBYT5IXJjlm+DsjyVuTSViT5Jwkz0xyfJJzpgKbtw777jru5KF9pWsAAAAAjN7MAp7u/mJ3f3JY/lqSa5IcluSUJBcOu12Y5MXD8ilJ3t4TH0vyqKp6QpIXJLm0u2/r7tuTXJrk5GHbI7r7z7u7k7x9ybmWuwYAAADA6M1lFq2qOirJcUk+nuRx3f3FZBICJXnssNthSW6cOmzb0La79m3LtGc31wAAAAAYvZkHPFX18CTvSfLT3f3V3e26TFvfj/a9qe2MqtpSVVt27NixN4cCAAAAzM1MA56qenAm4c7vd/cfDM1fGl6vyvB5y9C+LckRU4cfnuTmPbQfvkz77q7xbbr7/O7e2N0bDz300Pv3JQEAWBObNm3Kaaedlk2bNs27FABYeLOcRauSvC3JNd39a1ObNifZNRPW6UneO9V+2jCb1rOS3DG8XvX+JM+vqoOHwZWfn+T9w7avVdWzhmudtuRcy10DAIAFtX379tx0003Zvn37vEsBgIV3wAyvdUKSf5Xkyqr69ND275K8PsnFVfXKJDckeemw7ZIkP5hka5KvJ3lFknT3bVX1uiRXDPud2923Dcs/meR3khyU5H3DX3ZzDQAAAIDRm1nA090fzvLj5CTJScvs30nOXOFcFyS5YJn2LUmeukz7l5e7BgDAalXV4zLpmxybyTh/Vyf5ze7+0lwLe4C+7+fePu8SVvT3bv1aNiS54davLWydn/iV0+ZdAsDcvPln/ue8S1jRV27923s/F7XOs371n6/p+eYyixYAwJhU1QmZPFX8L5N8I8nfJfmxJNdV1T+aZ20AAMlsX9ECABirNyZ5Z5Kf6O57kqSqHpTkt5L8apLvn2NtAAACHgCAVXh6kh/fFe4kSXffU1W/luRT8ytr33bPgQ/7tk8AYGUCHgCAPbsjydFJrl3SfnSSr8y+nP3D3x7z/HmXAACjIeABANizi5K8rao2JfloJoMs/+NMZup85zwLAwBIBDwAAKuxKZPZQC/It/pP30zy1iRnz6soAIBdBDwAAHvQ3XcleVVVvSbJd2US9mzt7q/PtzIAgAkBDwDAKg2BzpXzrgMAYCkBDwDAMqpqc5KXd/dXh+UVdfeLZlQWAMCyBDwAAMv7ciaDKSfJbVPLAAALR8ADALCM7n7F1PKPz7EUAIA9etC8CwAAWHRVdUFV/b1l2h9WVRfMoyYAgGkCHgCAPTs9yUHLtB+U5LQZ1wIA7MHDDnxEHvaQR+VhBz5i3qXMjFe0AABWUFWHZDIleiU5uKp2Tm3ekOSHknxpHrUBACs74bv+z3mXMHMCHgCAld2ayeDKneTqZbZ3knNmWhEAwDIEPAAAK/unmTy9c1mSH8lkNq1d7kryhe6+eR6FAQBME/AAAKyguz+UJFV1dJIbu/ueOZcEALAsAQ8AwB509xeSpKq+M8mRSQ5csv3P5lEXAMAuAh4AgD0Ygp13JDkxk3F3avjcZcM86gIA2MU06QAAe/afktyd5NgkX0/yT5K8NMk1SU6eY10AAEk8wQMAsBrPTvJD3f3XVdVJdnT3R6rqziSvS3LpfMsDAPZ3nuABANizgzKZMj2ZzKT12GH56iRPm0tFAABTBDwAAHv210m+e1j+dJKfqKonJjkzyU1zqwoAYOAVLQCAPfvPSR4/LJ+b5H8leVmSO5OcPq+iAAB2WXXAU1UnJvlod+9c0n5Aku83PSgAsK/q7t+fWv5kVR2VyRM9N3T3rSsdBwAwK3vzitblSQ5Zpv2RwzYAgP1Cd3+9uz/Z3bdW1bPmXQ8AwN4EPJWkl2l/dJK/XZtyAAAWT1U9vKoOWtJ2XFX9cZKPzKksAIB77fEVraraPCx2kt8bpgPdZUOSpyb56DrUBgAwV1V1eJJ3JXlWkrur6s1JfiHJbyb5sSSbk/zj+VUIADCxmjF4vjx8VpLbk3xjattdST6c5L+scV0AAIvg9UkenuRVSX5k+PwnSa5M8g+6+3NzrA0A4F57DHi6+xVJUlWfT/LG7vY6FgCwv/inSf5Fd3+kqt6d5OYk7+nu18+5LgCAb7PqWbS6+5fWsxAAgAX0+CSfTZLu3l5V30jy3vmWBABwX3szTfohSc5LclKSx2bJAM3d/Yi1LQ0AYCHcPbV8T5K/m1chAAArWXXAk+RtSY5Lcn4mjycvN6MWAMC+pJJ8qKp2DusHJXlfVd01vVN3P23mlQEATNmbgOekJM/r7o+vVzEAAAtm6Svq75lLFQAAe7A3Ac8tSf5mvQoBAFg0xiAEAMbiQXve5V6/kOTcqnr4ehUDAAAAwN7bmyd4fjHJUUluqaovJPnm9EbvngMAAADMx94EPO9etyoAAAAAuN9WHfB4Bx0AAABgMe3NGDwAAPulqjqxqu7zw1hVHVBVJ86jJgCAaat+gqeqvpakV9re3Y9Yk4oAABbP5UmekMmsotMeOWzbMPOKAACm7M0YPGctWX9wkuOS/EiS89asIgCAxVNZ/oeuRyf52xnXAgBwH3szBs+Fy7VX1SeTnJTkN9aqKACARVBVm4fFTvJ7VXXn1OYNSZ6a5KMzLwwAYIm1GIPn8iT/fA3OAwCwaL48/FWS26fWv5xkW5LfSvLy1Zyoqk6uqmuramtVnb3M9hOr6pNVtbOqXrJk291V9enhb/PSYwEA9uYVrZWcmuTWNTgPAMBC6e5XJElVfT7JG7v7fr2OVVUbkrwlyfMyCYauqKrN3X311G43JPnxJD+7zCm+0d1Pvz/XBgD2D3szyPKV+fZ3zyvJ45IckuQn17guAICF0d2/9ABPcXySrd19fZJU1UVJTklyb8DT3Z8ftt3zAK8FAOyH9uYJnncvWb8nyY4kH+zuv167kgAA5m+ZH7dW1N1P28MuhyW5cWp9W5Jn7kU531FVW5LsTPL67v4fy+1UVWckOSNJjjzyyL04PQAwdnszyPID/eUKAGBMlv649UDUMm2rCo8GR3b3zVX1pCSXVdWV3f3Z+5yw+/wk5yfJxo0b9+b8AMDI7fUYPFX1nCTHZtIpuaq7P7jWRQEAzNsa/7i1LckRU+uHJ7l5L2q5efi8vqo+mOS4JPcJeACA/dfejMFzWJI/TPJ9+VaH5DuHx4V/eFfHAwCA+7giyTFVdXSSmzKZpOJfrubAqjo4yde7+86qekySE5K8Yd0qBQBGaW+mSX9TkruTPLm7j+juI5IcM7S9aU8HV9UFVXVLVf3VVNu/r6qbpqb9/MGpba8ZphG9tqpeMNW+7BSjVXV0VX28qq6rqndV1YFD+0OG9a3D9qP24jsDAKSqrqyqz6z0t6fju3tnkrOSvD/JNUku7u6rqurcqnrRcI1/WFXbkrw0yW9X1VXD4d+TZEtV/WWSyzMZg+fq+14FANif7c0rWs9L8gPd/bldDcNjwj+V5AOrOP53krw5yduXtP96d79xuqGqjs3kl62nJPnOJH9aVX9/2LzSFKO/PJzroqr6rSSvTPLW4fP27n5yVZ067Peje/G9AQCWjsfz4CRPz+Rpmres5gTdfUmSS5a0vXZq+YpMXt1aetxHk3zvXtYLAOxn9noMnmWsairP7v6zvXh65pQkF3X3nUk+V1VbM5leNFlmitGquibJc/KtR50vTPLvMwl4ThmWk0nn7M1VVd1t4EEAYFVWGo+nqn4uyRNnXA4AwH3szStaH0jypqq6d4DAqjoyyX/O6p7gWclZw+PNFwzvmCfLTyV62G7aH53kK8Pjz9Pt33auYfsdw/73UVVnVNWWqtqyY8eOB/CVAID9xB8k+bF5FwEAsDcBz08leWiS66vqC1X1+Uxmb3josO3+eGuS78rkEecvJvnVoX2lqUT3tn1357pvY/f53b2xuzceeuihu6sbACBJTkzy9XkXAQCw6le0uvvGJM+oqucl+e5MgpOru/tP7+/Fu/tLu5ar6r8k+aNhdXdTiS7XfmuSR1XVAcNTOtP77zrXtqo6IMkjk9x2f2sGAPY/VbV5aVOSJ2QyXflaTqcOAHC/7PEJnqp6YVV9vqoemSTdfWl3/0Z3vymTQY4/X1XPvz8Xr6onTK3+cJJdM2xtTnLqMAPW0ZnM1vUXmZpidJgl69Qkm4fxdC5P8pLh+NOTvHfqXKcPyy9JcpnxdwCAvfTlJX+3JPnTJC/s7nPnWRgAQLK6J3jOSvIr3X3H0g3dfUdV/XKSVyX5k92dpKremeQHkjxmmAL0nCQ/UFVPz+SVqc8n+TfDea+qqouTXJ1kZ5Izu/vu4Ty7phjdkOSC7t41hejPJ7moqv5Dkk8ledvQ/rYkvzsM1HxbJqEQAMCqdfcr5l0DAMDurCbgeVqSV+9m+2VJfmFPJ+nuly3T/LZl2nbtf16S85Zpv88Uo0P79fnWTFvT7X+X5KV7qg8AAABgrFYT8Bya3U+F3llhVioAgH1FVb0iycuSHJnkwOlt3f2kuRQFADBYzSxa2zJ5imclT0ty09qUAwCweKrq5zKZ7fMTSY5K8j8yGTvwkCQXzK8yAICJ1QQ8f5zkdVV10NINVfXQJOcO+wAA7Kv+dZIzuvs1Sb6Z5M3d/aJMQp8nzrUyAICs7hWt8zKZfeq6qvqNJH89tH9PJgMwV5L/uD7lAQAshMMzmdEzSb6R5BHD8juH9n89j6IAAHbZY8DT3bdU1fcneWsmQU7t2pTJbFb/T3d/af1KBACYu+1JHpPkhiRfSPKPknw6yZMz6RMBAMzVap7gSXd/IckPVtXBmXRkKsl13X37ehYHALAgLkvyoiSfzGQW0F+vqn+R5BlJLp5nYQAAySoDnl2GQOeKdaoFAGBRnZFh7MLu/q2quj3JCUnek+S351kYAECyukGWAQD2O1V1WVU9alh9eZINu7Z197u6+6e6+83d/c35VAgA8C0CHgCA5Z2Q5KHD8n9L8sg51gIAsFt79YoWAMB+5K+T/MequjyT8Qf/RVV9dbkdu/vtM60MAGAJAQ8AwPJ+Msl/TnJKJjNlvT7Lz5jVSQQ8AMBcCXgAAJbR3R9N8g+TpKruSfKk7r5lvlUBACzPGDwAAHt2dJId8y4CAGAlAh4AgD3o7i8keWpVvbmq3ldVT0iSqnpxVR035/IAAAQ8AAB7UlXPT3JFksOSPCfJQcOm70pyzrzqAgDYRcADALBnr0vy6u7+4SR3TbV/MMnxc6kIAGCKgAcAYM+ekuSSZdpvS3LIjGsBALgPAQ8AwJ7dnsnrWUs9I8m2GdcCAHAfAh4AgD17R5JfqarDk3SSA6rq2UnemOTtc60MACACHgCA1fjFJJ9L8oUkD09ydZLLknw4yXlzrAsAIElywLwLAABYdN39zSQ/VlWvTXJcJj+Sfaq7r5tvZQAAEwIeAIBV6u7PJvnsvOsAAFjKK1oAALtRVQdV1TlV9Zmq+puq+lpV/WVV/WJVHTTv+gAAEk/wAACsqKoOyGSsnWck+V9J/jhJJTk2yWuTvLCqnt3dO+dXJQCAgAcAYHfOSPLkJM/o7qumN1TVU5NcPuzzm3OoDQDgXl7RAgBY2UuSnLc03EmS7v6rJP/fsA8AwFwJeAAAVvaUTF7RWsmfJnnqjGoBAFiRgAcAYGUHJ9mxm+07kjxqRrUAAKxIwAMAsLINSXY3gPI9wz4AAHNlkGUAgJVVkt+rqjtX2P6QWRYDALASAQ8AwMouXMU+b1/3KgAA9kDAAwCwgu5+xbxrAABYDWPwAAAAAIycgAcAAABg5AQ8AAAAACMn4AEAAAAYOQEPAAAAwMgJeAAAAABGTsADAAAAMHICHgAAAIDBfhtGAAATO0lEQVSRE/AAAAAAjJyABwAAAGDkBDwAAAAAIyfgAQAAABg5AQ8AAADAyAl4AABmoKpOrqprq2prVZ29zPYTq+qTVbWzql6yZNvpVXXd8Hf67KoGAMZCwAMAsM6qakOStyR5YZJjk7ysqo5dstsNSX48yTuWHHtIknOSPDPJ8UnOqaqD17tmAGBcBDwAAOvv+CRbu/v67r4ryUVJTpneobs/392fSXLPkmNfkOTS7r6tu29PcmmSk2dRNAAwHgIeAID1d1iSG6fWtw1ta3psVZ1RVVuqasuOHTvuV6EAwDgJeAAA1l8t09ZrfWx3n9/dG7t746GHHrrq4gCA8ZtZwFNVF1TVLVX1V1Nth1TVpcOAgZfuep+8Jt40DEL4map6xtQxyw4yWFXfV1VXDse8qapqd9cAAJihbUmOmFo/PMnNMzgWANhPzPIJnt/Jfd8XPzvJB7r7mCQfGNaTyQCExwx/ZyR5a7LHQQbfOuy767iT93ANAIBZuSLJMVV1dFUdmOTUJJtXeez7kzy/qg4e+j3PH9oAAO41s4Cnu/8syW1Lmk9JcuGwfGGSF0+1v70nPpbkUVX1hKwwyOCw7RHd/efd3UnevuRcy10DAGAmuntnkrMyCWauSXJxd19VVedW1YuSpKr+YVVtS/LSJL9dVVcNx96W5HWZhERXJDl3aAMAuNcBc77+47r7i0nS3V+sqscO7SsNJri79m3LtO/uGvdRVWdk8hRQjjzyyPv7nQAA7qO7L0lyyZK2104tX5HJ61fLHXtBkgvWtUAAYNQWdZDllQYT3Nv2vWJgQgAAAGCM5h3wfGl4vSrD5y1D+0qDCe6u/fBl2nd3DQAAAIB9wrwDns1Jds2EdXqS9061nzbMpvWsJHcMr1ktO8jgsO1rVfWsYfas05aca7lrAAAAAOwTZjYGT1W9M8kPJHnMMIDgOUlen+TiqnplkhsyGVQwmbyf/oNJtib5epJXJJNBBqtq1yCDybcPMviTmczUdVCS9w1/2c01AAAAAPYJMwt4uvtlK2w6aZl9O8mZK5xn2UEGu3tLkqcu0/7l5a4BAAAAsK+Y9ytaAAAAADxAAh4AAACAkZvZK1oAzN+HTnz2vEsYtWf/2YfmXQIAACzLEzwAAAAAIyfgAQAAABg5AQ8AAADAyAl4AAAAAEZOwAMAAAAwcgIeAAAAgJET8AAAAACMnIAHAAAAYOQEPAAAAAAjJ+ABAAAAGDkBDwAAAMDICXgAAAAARk7AAwAAADByAh4AAACAkRPwAAAAAIycgAcAAABg5AQ8AAAAACMn4AEAAAAYOQEPAAAAwMgJeAAAAABGTsADAAAAMHICHgAAAICRE/AAAAAAjJyABwAAAGDkBDwAAAAAIyfgAQAAABg5AQ8AAADAyAl4AAAAAEZOwAMAAAAwcgIeAAAAgJET8AAAAACMnIAHAAAAYOQEPAAAAAAjJ+ABAAAAGDkBDwAAAMDICXgAAAAARk7AAwAAADByAh4AAACAkRPwAAAAAIycgAcAAABg5AQ8AAAAACMn4AEAAAAYOQEPAAAAwMgJeAAAAABGTsADADADVXVyVV1bVVur6uxltj+kqt41bP94VR01tB9VVd+oqk8Pf78169oBgMV3wLwLAADY11XVhiRvSfK8JNuSXFFVm7v76qndXpnk9u5+clWdmuSXk/zosO2z3f30mRYNAIzKQjzBU1Wfr6orh1+ltgxth1TVpVV13fB58NBeVfWm4detz1TVM6bOc/qw/3VVdfpU+/cN5986HFuz/5YAwH7s+CRbu/v67r4ryUVJTlmyzylJLhyW353kJH0WAGC1FiLgGfzT7n56d28c1s9O8oHuPibJB4b1JHlhkmOGvzOSvDWZBEJJzknyzEw6UefsCoWGfc6YOu7k9f86AAD3OizJjVPr24a2Zffp7p1J7kjy6GHb0VX1qar6UFX9k5UuUlVnVNWWqtqyY8eOtaseAFh4ixTwLDX9K9aFSV481f72nvhYkkdV1ROSvCDJpd19W3ffnuTSJCcP2x7R3X/e3Z3k7VPnAgCYheWexOlV7vPFJEd293FJXp3kHVX1iOUu0t3nd/fG7t546KGHPqCCAYBxWZSAp5P8SVV9oqrOGNoe191fTJLh87FD+0q/gO2ufdsy7ffhVy8AYJ1sS3LE1PrhSW5eaZ+qOiDJI5Pc1t13dveXk6S7P5Hks0n+/rpXDACMyqIEPCd09zMyef3qzKo6cTf7rvTr1t6237fRr14AwPq4IskxVXV0VR2Y5NQkm5fssznJrjEEX5Lksu7uqjp0GKQ5VfWkTF43v35GdQMAI7EQAU933zx83pLkDzMZQ+dLw+tVGT5vGXZf6Rew3bUfvkw7AMBMDGPqnJXk/UmuSXJxd19VVedW1YuG3d6W5NFVtTWTV7F2jT94YpLPVNVfZjL48k90922z/QYAwKKb+zTpVfWwJA/q7q8Ny89Pcm6+9SvW64fP9w6HbE5yVlVdlMmAynd09xer6v1J/uPUwMrPT/Ka7r6tqr5WVc9K8vEkpyX5jVl9PwCAJOnuS5JcsqTttVPLf5fkpcsc954k71n3AgGAUZt7wJPkcUn+cJgF9IAk7+ju/1VVVyS5uKpemeSGfKvDc0mSH0yyNcnXk7wiSYYg53WZPAKdJOdO/br1k0l+J8lBSd43/AEAAADsE+Ye8HT39Un+j2Xav5zkpGXaO8mZK5zrgiQXLNO+JclTH3CxAAAAAAtoIcbgAQAAAOD+E/AAAAAAjJyABwAAAGDkBDwAAAAAIyfgAQAAABg5AQ8AAADAyAl4AAAAAEZOwAMAAAAwcgIeAAAAgJET8AAAAACMnIAHAAAAYOQEPAAAAAAjJ+ABAAAAGDkBDwAAAMDICXgAAAAARk7AAwAAADByAh4AAACAkRPwAAAAAIycgAcAAABg5AQ8AAAAACMn4AEAAAAYOQEPAAAAwMgJeAAAAABGTsADAAAAMHICHgAAAICRE/AAAAAAjJyABwAAAGDkBDwAAAAAIyfgAQAAABg5AQ8AAADAyAl4AAAAAEZOwAMAAAAwcgIeAAAAgJET8AAAAACMnIAHAAAAYOQEPAAAAAAjJ+ABAAAAGDkBDwAAAMDICXgAAAAARk7AAwAAADByAh4AAACAkRPwAAAAAIycgAcAAABg5AQ8AAAAACMn4AEAAAAYOQEPAAAAwMgdMO8CAGB/9eaf+Z/zLmHUzvrVfz7vEgAAFoYneAAAAABGTsADAAAAMHICHgAAAICR228Cnqo6uaquraqtVXX2vOsBAPYve+qLVNVDqupdw/aPV9VRU9teM7RfW1UvmGXdAMA47BcBT1VtSPKWJC9McmySl1XVsfOtCgDYX6yyL/LKJLd395OT/HqSXx6OPTbJqUmekuTkJL85nA8A4F77RcCT5PgkW7v7+u6+K8lFSU6Zc00AwP5jNX2RU5JcOCy/O8lJVVVD+0XdfWd3fy7J1uF8AAD32l+mST8syY1T69uSPHPpTlV1RpIzhtW/qaprZ1DbentMklvnXcRK6o2nz7uEWVvo+5Fzat4VzMNC35P6qf3uniz0/Ui5H4vk3/7aqnd94jqWsVqr6Yvcu09376yqO5I8emj/2JJjD1vuIvoys6cvs2D2v77MYt+P6MssHH2ZhbLWfZn9JeBZ7r/ivk9D9/lJzl//cmanqrZ098Z518GE+7F43JPF4n4sFvdjTa2mL7LSPqvqxyT6Mqw/92OxuB+Lxz1ZLPvb/dhfXtHaluSIqfXDk9w8p1oAgP3Pavoi9+5TVQckeWSS21Z5LACwn9tfAp4rkhxTVUdX1YGZDFS4ec41AQD7j9X0RTYn2fW+z0uSXNbdPbSfOsyydXSSY5L8xYzqBgBGYr94RWt4j/2sJO9PsiHJBd191ZzLmpV96jHtfYD7sXjck8XifiwW92ONrNQXqapzk2zp7s1J3pbkd6tqayZP7pw6HHtVVV2c5OokO5Oc2d13z+WLzIf/DheL+7FY3I/F454slv3qftTkhyEAAAAAxmp/eUULAAAAYJ8l4AEAAAAYOQHPyFTVEVV1eVVdU1VXVdWrhvZDqurSqrpu+Dx4aP/uqvrzqrqzqn52mfNtqKpPVdUfzfq77AvW8n5U1eer6sqq+nRVbZnH99kXrPE9eVRVvbuq/no43z+ax3cas7W6H1X1D4Z/G7v+vlpVPz2v7zVWa/zv4/8dzvFXVfXOqvqOeXwnxkdfZrHoyywefZnFoi+zWPRlds8YPCNTVU9I8oTu/mRV/b0kn0jy4iQ/nuS27n59VZ2d5ODu/vmqemySJw773N7db1xyvlcn2ZjkEd39z2b5XfYFa3k/qurzSTZ2962z/h77kjW+Jxcm+d/d/V9rMuvNQ7v7K7P+TmO21v+bNZxzQ5Kbkjyzu78wq++yL1ir+1FVhyX5cJJju/sbNRkA+JLu/p3ZfyvGRl9msejLLB59mcWiL7NY9GV2zxM8I9PdX+zuTw7LX0tyTZLDkpyS5MJhtwsz+Q843X1Ld1+R5JtLz1VVhyf5oST/dQal75PW8n6wNtbqnlTVI5KcmMmsNunuu3SI9t46/Rs5KclndYj23hrfjwOSHFRVByR5aJKb17l89hH6MotFX2bx6MssFn2ZxaIvs3sCnhGrqqOSHJfk40ke191fTCb/0Sd57CpO8Z+SbEpyzzqVuF9Zg/vRSf6kqj5RVWesV537kwd4T56UZEeS/1aTR///a1U9bB3L3eetwb+RXU5N8s61rm9/80DuR3fflOSNSW5I8sUkd3T3n6xnveyb9GUWi77M4tGXWSz6MotFX+a+BDwjVVUPT/KeJD/d3V+9H8f/syS3dPcn1ry4/dADvR+DE7r7GUlemOTMqjpxzQrcD63BPTkgyTOSvLW7j0vyt0nOXsMS9ytr9G8kw+PlL0ry39eqtv3RGvx/yMGZ/FJ2dJLvTPKwqnr52lbJvk5fZrHoyywefZnFoi+zWPRllifgGaGqenAm/zH/fnf/wdD8peF9xF3vJd6yh9OckORFw7vSFyV5TlX93jqVvE9bo/uR7r55+LwlyR8mOX59Kt73rdE92ZZkW3d/fFh/dyadJPbSWv0bGbwwySe7+0trX+n+YY3ux3OTfK67d3T3N5P8QZLvX6+a2ffoyywWfZnFoy+zWPRlFou+zMoEPCNTVZXJe7TXdPevTW3anOT0Yfn0JO/d3Xm6+zXdfXh3H5XJI4KXdffoE8tZW6v7UVUPGwYJy/Do7POT/NXaV7zvW8N/I9uT3FhV/2BoOinJ1Wtc7j5vre7HlJfFI8332xrejxuSPKuqHjqc86RM3oGHPdKXWSz6MotHX2ax6MssFn2Z3TOL1shU1T9O8r+TXJlvvW/+7zJ57/DiJEdm8h/rS7v7tqp6fJItSR4x7P83mYwU/tWpc/5Akp9tM0/stbW6H0kek8kvXcnkcdp3dPd5s/oe+5K1/DdSVU/PZODOA5Ncn+QV3X37LL/P2K3x/XhokhuTPKm775jtN9k3rPH9+KUkP5pkZ5JPJfm/u/vOWX4fxklfZrHoyywefZnFoi+zWPRldk/AAwAAADByXtEC4P9v725CNp3iMIBfF5OyIDI1pCzIQrGaDPlYSJOljZ2m2MiCYoOdj4XNEEX5SIoGs9OkTGJBykozSjazGrHQUExjiJnmWDzP1NvTYJr3eeftft/fr+6eOvfp3Ofs/l3POfcNAABMnIAHAAAAYOIEPAAAAAATJ+ABAAAAmDgBDwAAAMDECXgAAAAAJk7AA0xa2z1tv2l70UL73W1PtL1tveYGAPB/1DLAsgh4gKl7JMkVSZ4+3dD20iRvJ9k9xvhqLR66WIQBAJwjtQywFAIeYNLGGL8leTDJE213zJtfSvJrkmeSpO2Nbfe3Pdb2SNv32m47PUbbW9p+2vaXtkfbfrlirLTd0na0fbjtvrbHkzx33hYJAGxYahlgWQQ8wOSNMT5L8lqSd9vel+T+JLvGGH+3vTrJF0kOJrk5yc4klyX5sG3nQ1yS5J0kdya5Ncm3Sfa3vXzhUc8m2ZfkpiSvr+2qAIDNQi0DLEPHGOs9B4BVa3txZoXP9UmeGmPsnrc/n2T7GOOeFX23Jvl53n7gDGM1yZEkj44x9rbdkuREkpfHGI+v/WoAgM1GLQOslh08wIYwxvgzyQtJ/kry4opb25Pc1fb301eSw/N71yVJ221t32x7qO3RJMcyOwt/zcJjvl7LNQAAm5daBlitLes9AYAlOpnk1Bjj1Iq2C5J8lOTJM/T/af67J7Otzo8l+T6zwurzJIsvHzy+zMkCACxQywDnTMADbHQHktyb5PAY4+S/9LkjyUNjjI+TpO1VSa48T/MDAPgvahngrDiiBWx0ryTZmuSDtjvaXtt2Z9u35mfdk+RQkl1tb5h/cWJvZv98AQCsN7UMcFYEPMCGNsb4McntSS5M8kmS75K8muSPzF42mCQPZLat+WCS95O8keSH8z1XAIBFahngbPmKFgAAAMDE2cEDAAAAMHECHgAAAICJE/AAAAAATJyABwAAAGDiBDwAAAAAEyfgAQAAAJg4AQ8AAADAxAl4AAAAACbuH1QvwxNcngoWAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 1152x432 with 2 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# visualize the time effect\n",
    "fig, ax = plt.subplots(nrows=1, ncols=2, figsize=(16, 6))\n",
    "sns.countplot(x='year', data=data, ax=ax[0])\n",
    "ax[0].set_xlabel('Year', fontsize=14)\n",
    "ax[0].set_ylabel('Count', fontsize=14)\n",
    "sns.barplot(x='year', y='target', data=data, ax=ax[1])\n",
    "ax[1].set_xlabel('Year', fontsize=14)\n",
    "ax[1].set_ylabel('Default Ratio', fontsize=14)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 820870 entries, 0 to 1642573\n",
      "Columns: 68 entries, loan_amnt to target\n",
      "dtypes: float64(14), int64(38), object(16)\n",
      "memory usage: 472.1+ MB\n"
     ]
    }
   ],
   "source": [
    "# drop useless features\n",
    "data = data.drop(labels='loan_status', axis=1)\n",
    "\n",
    "data.info(verbose=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's split the data into training and test set. Based on the above information, we decide to split the data according year information. More specifically, since the data after $2016$ accounts about $12\\%$ of all the data, \n",
    "we will use the data from $2014$ until $2016$ as the training set, data from $2017$ until $2018$ as the test set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# split into train and test set\n",
    "train = data[data['year'] < 2017]\n",
    "test = data[data['year'] >= 2017]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Training set:\t (722143, 68) \t 0.8797\n",
      "Test set:\t (98727, 68) \t 0.1203\n"
     ]
    }
   ],
   "source": [
    "# save to disk\n",
    "train.to_csv('./data/train.csv', index=False)\n",
    "test.to_csv('./data/test.csv', index=False)\n",
    "\n",
    "print('Training set:\\t', train.shape, '\\t', round(len(train) / len(data), 4))\n",
    "print('Test set:\\t', test.shape, '\\t', round(len(test) / len(data), 4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}