credit_default_risk/1_mortcudf_data_prep.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c1d1a6f2-f76f-4fdb-894f-5f64d0da8f97",
   "metadata": {},
   "outputs": [],
   "source": [
    "############################################################################\n",
    "##\n",
    "## Copyright (C) 2021 NVIDIA Corporation.  All rights reserved.\n",
    "##\n",
    "## NVIDIA Sample Code\n",
    "##\n",
    "## Please refer to the NVIDIA end user license agreement (EULA) associated\n",
    "## with this source code for terms and conditions that govern your use of\n",
    "## this software. Any use, reproduction, disclosure, or distribution of\n",
    "## this software and related documentation outside the terms of the EULA\n",
    "## is strictly prohibited.\n",
    "##\n",
    "############################################################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ec759fb0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-13T20:08:07.408059Z",
     "start_time": "2021-07-13T20:08:06.205278Z"
    }
   },
   "outputs": [],
   "source": [
    "###### https://degravek.github.io/project-pages/project1/2016/11/12/New-Notebook/ #################\n",
    "# with modifications and GPU acceleration by Mark J Bennett mbennett@nvidia.com and Emanuel Scoullos escoullos@nvidia.com\n",
    "\n",
    "###### Best when compared to:\n",
    "###### https://www.bankofengland.co.uk/-/media/boe/files/working-paper/2019/\n",
    "######        machine-learning-explainability-in-finance-an-application-to-default-risk-analysis.pdf\n",
    "\n",
    "from collections import OrderedDict\n",
    "import gc\n",
    "import glob\n",
    "import os\n",
    "import pickle\n",
    "import re\n",
    "import sys\n",
    "import time\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "np.set_printoptions(threshold=sys.maxsize)\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.width', 1000)\n",
    "\n",
    "# make sure the path below points to the data directory where the FannieMae data was downloaded.\n",
    "data_path = '/rapids/data'\n",
    "\n",
    "n_gpus = 1\n",
    "isPlot = False\n",
    "\n",
    "if not n_gpus:\n",
    "    from pandas import read_csv, concat, merge, DataFrame, get_dummies, Series, isnull\n",
    "    from sklearn.preprocessing import OneHotEncoder\n",
    "    from numpy import zeros, double, int8, concatenate\n",
    "else:\n",
    "    from cudf import read_csv, concat, merge, DataFrame, get_dummies, Series\n",
    "    from cuml.preprocessing import OneHotEncoder\n",
    "    from cupy import zeros,double, int8, concatenate"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5ecefb8",
   "metadata": {},
   "source": [
    "# Read and Process Fannie Mae Dataset from 2007-2012"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3e0b86b3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-13T20:08:08.038298Z",
     "start_time": "2021-07-13T20:08:07.906505Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/rapids/data/mortgage_2000-2016.tgz  /rapids/data/rmback.sh\r\n",
      "/rapids/data/names.csv\t\t     /rapids/data/rmfront.sh\r\n",
      "\r\n",
      "/rapids/data/acq:\r\n",
      "Acquisition_2007Q1.txt\tAcquisition_2009Q1.txt\tAcquisition_2011Q1.txt\r\n",
      "Acquisition_2007Q2.txt\tAcquisition_2009Q2.txt\tAcquisition_2011Q2.txt\r\n",
      "Acquisition_2007Q3.txt\tAcquisition_2009Q3.txt\tAcquisition_2011Q3.txt\r\n",
      "Acquisition_2007Q4.txt\tAcquisition_2009Q4.txt\tAcquisition_2011Q4.txt\r\n",
      "Acquisition_2008Q1.txt\tAcquisition_2010Q1.txt\tAcquisition_2012Q1.txt\r\n",
      "Acquisition_2008Q2.txt\tAcquisition_2010Q2.txt\tAcquisition_2012Q2.txt\r\n",
      "Acquisition_2008Q3.txt\tAcquisition_2010Q3.txt\tAcquisition_2012Q3.txt\r\n",
      "Acquisition_2008Q4.txt\tAcquisition_2010Q4.txt\tAcquisition_2012Q4.txt\r\n",
      "\r\n",
      "/rapids/data/perf:\r\n",
      "Performance_2007Q1.txt\t  Performance_2009Q2.txt_1  Performance_2011Q3.txt\r\n",
      "Performance_2007Q2.txt\t  Performance_2009Q3.txt_0  Performance_2011Q4.txt_0\r\n",
      "Performance_2007Q3.txt\t  Performance_2009Q3.txt_1  Performance_2011Q4.txt_1\r\n",
      "Performance_2007Q4.txt\t  Performance_2009Q4.txt    Performance_2012Q1.txt_0\r\n",
      "Performance_2008Q1.txt\t  Performance_2010Q1.txt    Performance_2012Q1.txt_1\r\n",
      "Performance_2008Q2.txt\t  Performance_2010Q2.txt    Performance_2012Q2.txt_0\r\n",
      "Performance_2008Q3.txt\t  Performance_2010Q3.txt    Performance_2012Q2.txt_1\r\n",
      "Performance_2008Q4.txt\t  Performance_2010Q4.txt_0  Performance_2012Q3.txt_0\r\n",
      "Performance_2009Q1.txt_0  Performance_2010Q4.txt_1  Performance_2012Q3.txt_1\r\n",
      "Performance_2009Q1.txt_1  Performance_2011Q1.txt    Performance_2012Q4.txt_0\r\n",
      "Performance_2009Q2.txt_0  Performance_2011Q2.txt    Performance_2012Q4.txt_1\r\n"
     ]
    }
   ],
   "source": [
    "!ls /rapids/data/*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e8bac9a2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-13T20:08:11.548655Z",
     "start_time": "2021-07-13T20:08:11.540638Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['int64', 'category', 'category', 'float64', 'int64', 'int64', 'str', 'str', 'int64', 'float64', 'float64', 'float64', 'float64', 'category', 'category', 'category', 'int64', 'category', 'category', 'int64', 'float64', 'category', 'float64', 'int64', 'category', 'category']\n",
      "col_acq list is len 26\n"
     ]
    }
   ],
   "source": [
    "col_acq = ['LoanID', 'Channel', 'SellerName', 'OrInterestRate', 'OrUnpaidPrinc', 'OrLoanTerm',\n",
    "           'OrDate', 'FirstPayment', 'OrLTV', 'OrCLTV', 'NumBorrow', 'DTIRat', 'CreditScore',\n",
    "           'FTHomeBuyer', 'LoanPurpose', 'PropertyType', 'NumUnits', 'OccStatus', 'PropertyState',\n",
    "           'Zip', 'MortInsPerc', 'ProductType', 'CoCreditScore', 'Extra', 'MortInsType', 'RelMortInd'];\n",
    "dtypesDict = OrderedDict([\n",
    "    (\"LoanID\", \"int64\"),\n",
    "    (\"Channel\", \"category\"),\n",
    "    (\"SellerName\", \"category\"),\n",
    "    (\"OrInterestRate\", \"float64\"),\n",
    "    (\"OrUnpaidPrinc\", \"int64\"),\n",
    "    (\"OrLoanTerm\", \"int64\"),\n",
    "    (\"OrDate\", \"str\"),\n",
    "    (\"FirstPayment\", \"str\"),\n",
    "    (\"OrLTV\", \"int64\"),  # was:float64\n",
    "    (\"OrCLTV\", \"float64\"),\n",
    "    (\"NumBorrow\", \"float64\"),\n",
    "    (\"DTIRat\", \"float64\"),\n",
    "    (\"CreditScore\", \"float64\"),\n",
    "    (\"FTHomeBuyer\", \"category\"),\n",
    "    (\"LoanPurpose\", \"category\"),\n",
    "    (\"PropertyType\", \"category\"),\n",
    "    (\"NumUnits\", \"int64\"),\n",
    "    (\"OccStatus\", \"category\"),\n",
    "    (\"PropertyState\", \"category\"),\n",
    "    (\"Zip\", \"int64\"),\n",
    "    (\"MortInsPerc\", \"float64\"),\n",
    "    (\"ProductType\", \"category\"),\n",
    "    (\"CoCreditScore\", \"float64\"),\n",
    "    (\"Extra\", \"int64\"),\n",
    "    (\"MortInsType\", \"category\"),\n",
    "    (\"RelMortInd\", \"category\"),\n",
    "])\n",
    "\n",
    "dtype = list(dtypesDict.values())\n",
    "print(dtype)\n",
    "print('col_acq list is len', len(col_acq))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1086830c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-13T20:08:14.321645Z",
     "start_time": "2021-07-13T20:08:14.181784Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Acquisition_2007Q1.txt\n",
      "Acquisition_2007Q2.txt\n",
      "Acquisition_2007Q3.txt\n",
      "Acquisition_2007Q4.txt\n",
      "Acquisition_2008Q1.txt\n",
      "Acquisition_2008Q2.txt\n",
      "Acquisition_2008Q3.txt\n",
      "Acquisition_2008Q4.txt\n",
      "Acquisition_2009Q1.txt\n",
      "Acquisition_2009Q2.txt\n",
      "Acquisition_2009Q3.txt\n",
      "Acquisition_2009Q4.txt\n",
      "Acquisition_2010Q1.txt\n",
      "Acquisition_2010Q2.txt\n",
      "Acquisition_2010Q3.txt\n",
      "Acquisition_2010Q4.txt\n",
      "Acquisition_2011Q1.txt\n",
      "Acquisition_2011Q2.txt\n",
      "Acquisition_2011Q3.txt\n",
      "Acquisition_2011Q4.txt\n",
      "Acquisition_2012Q1.txt\n",
      "Acquisition_2012Q2.txt\n",
      "Acquisition_2012Q3.txt\n",
      "Acquisition_2012Q4.txt\n"
     ]
    },
    {
     "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>LoanID</th>\n",
       "      <th>Channel</th>\n",
       "      <th>SellerName</th>\n",
       "      <th>OrInterestRate</th>\n",
       "      <th>OrUnpaidPrinc</th>\n",
       "      <th>OrLoanTerm</th>\n",
       "      <th>OrDate</th>\n",
       "      <th>FirstPayment</th>\n",
       "      <th>OrLTV</th>\n",
       "      <th>OrCLTV</th>\n",
       "      <th>NumBorrow</th>\n",
       "      <th>DTIRat</th>\n",
       "      <th>CreditScore</th>\n",
       "      <th>FTHomeBuyer</th>\n",
       "      <th>LoanPurpose</th>\n",
       "      <th>PropertyType</th>\n",
       "      <th>NumUnits</th>\n",
       "      <th>OccStatus</th>\n",
       "      <th>PropertyState</th>\n",
       "      <th>Zip</th>\n",
       "      <th>MortInsPerc</th>\n",
       "      <th>ProductType</th>\n",
       "      <th>CoCreditScore</th>\n",
       "      <th>Extra</th>\n",
       "      <th>MortInsType</th>\n",
       "      <th>RelMortInd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001461640</td>\n",
       "      <td>R</td>\n",
       "      <td>PNC BANK, N.A.</td>\n",
       "      <td>6.250</td>\n",
       "      <td>137000</td>\n",
       "      <td>360</td>\n",
       "      <td>01/2007</td>\n",
       "      <td>03/2007</td>\n",
       "      <td>56</td>\n",
       "      <td>56.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>741.0</td>\n",
       "      <td>N</td>\n",
       "      <td>C</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>MI</td>\n",
       "      <td>486</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>734.0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100015135004</td>\n",
       "      <td>R</td>\n",
       "      <td>SUNTRUST MORTGAGE INC.</td>\n",
       "      <td>6.000</td>\n",
       "      <td>116000</td>\n",
       "      <td>360</td>\n",
       "      <td>02/2007</td>\n",
       "      <td>04/2007</td>\n",
       "      <td>80</td>\n",
       "      <td>80.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>796.0</td>\n",
       "      <td>N</td>\n",
       "      <td>R</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>S</td>\n",
       "      <td>GA</td>\n",
       "      <td>302</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>762.0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100015306566</td>\n",
       "      <td>C</td>\n",
       "      <td>CITIMORTGAGE, INC.</td>\n",
       "      <td>6.375</td>\n",
       "      <td>58000</td>\n",
       "      <td>180</td>\n",
       "      <td>02/2007</td>\n",
       "      <td>03/2007</td>\n",
       "      <td>78</td>\n",
       "      <td>78.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>710.0</td>\n",
       "      <td>N</td>\n",
       "      <td>R</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>IN</td>\n",
       "      <td>465</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100015319835</td>\n",
       "      <td>C</td>\n",
       "      <td>BANK OF AMERICA, N.A.</td>\n",
       "      <td>6.125</td>\n",
       "      <td>353000</td>\n",
       "      <td>360</td>\n",
       "      <td>12/2006</td>\n",
       "      <td>02/2007</td>\n",
       "      <td>80</td>\n",
       "      <td>80.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>778.0</td>\n",
       "      <td>N</td>\n",
       "      <td>R</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>MA</td>\n",
       "      <td>21</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>656.0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100030521552</td>\n",
       "      <td>C</td>\n",
       "      <td>GMAC MORTGAGE, LLC</td>\n",
       "      <td>5.875</td>\n",
       "      <td>385000</td>\n",
       "      <td>360</td>\n",
       "      <td>12/2006</td>\n",
       "      <td>03/2007</td>\n",
       "      <td>70</td>\n",
       "      <td>70.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>720.0</td>\n",
       "      <td>N</td>\n",
       "      <td>C</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>CA</td>\n",
       "      <td>917</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>700.0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         LoanID Channel              SellerName  OrInterestRate  OrUnpaidPrinc  OrLoanTerm   OrDate FirstPayment  OrLTV  OrCLTV  NumBorrow  DTIRat  CreditScore FTHomeBuyer LoanPurpose PropertyType  NumUnits OccStatus PropertyState  Zip MortInsPerc ProductType CoCreditScore Extra MortInsType  RelMortInd\n",
       "0  100001461640       R          PNC BANK, N.A.           6.250         137000         360  01/2007      03/2007     56    56.0        2.0    37.0        741.0           N           C           SF         1         P            MI  486        <NA>         FRM         734.0  <NA>           N      200701\n",
       "1  100015135004       R  SUNTRUST MORTGAGE INC.           6.000         116000         360  02/2007      04/2007     80    80.0        2.0    11.0        796.0           N           R           SF         1         S            GA  302        <NA>         FRM         762.0  <NA>           N      200701\n",
       "2  100015306566       C      CITIMORTGAGE, INC.           6.375          58000         180  02/2007      03/2007     78    78.0        2.0    30.0        710.0           N           R           SF         1         P            IN  465        <NA>         FRM          <NA>  <NA>           N      200701\n",
       "3  100015319835       C   BANK OF AMERICA, N.A.           6.125         353000         360  12/2006      02/2007     80    80.0        2.0    28.0        778.0           N           R           SF         1         P            MA   21        <NA>         FRM         656.0  <NA>           N      200701\n",
       "4  100030521552       C      GMAC MORTGAGE, LLC           5.875         385000         360  12/2006      03/2007     70    70.0        2.0    50.0        720.0           N           C           SF         1         P            CA  917        <NA>         FRM         700.0  <NA>           N      200701"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# open Acquisition files from each quarter 2007 - 2012\n",
    "\n",
    "pattern = r'Acquisition_(20[0][7,8,9]|201[0,1,2])Q[1-4].txt'\n",
    "\n",
    "fpaths = sorted(filter(re.compile(pattern).match, os.listdir(f'{data_path}/acq')))\n",
    "\n",
    "df_acq = concat([read_csv(f'{data_path}/acq/{fp}', sep='|', names=col_acq, index_col=False) \n",
    "                 for fp in fpaths], axis=0).reset_index(drop=True)\n",
    "print('\\n'.join(fpaths))\n",
    "\n",
    "df_acq.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "594b977e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:52:58.314891Z",
     "start_time": "2021-07-07T23:50:58.673120Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "col_per list is len 31 but using only 2!\n",
      "12.216453552246094 secs\n",
      "(585303356, 2)\n"
     ]
    },
    {
     "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>LoanID</th>\n",
       "      <th>CLDS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           LoanID CLDS\n",
       "130  100006270289    0\n",
       "131  100006270289   -1\n",
       "132  100006270289    0\n",
       "133  100006270289    0\n",
       "134  100006270289    0\n",
       "135  100006270289    0\n",
       "136  100006270289    1\n",
       "137  100006270289    2\n",
       "138  100006270289    3\n",
       "139  100006270289    4"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "col_per = ['LoanID', 'MonthRep', 'Servicer', 'CurrInterestRate', 'CAUPB', 'LoanAge', 'MonthsToMaturity',\n",
    "           'AdMonthsToMaturity', 'MaturityDate', 'MSA', 'CLDS', 'ModFlag', 'ZeroBalCode', 'ZeroBalDate',\n",
    "           'LastInstallDate', 'ForeclosureDate', 'DispositionDate', 'ForeclosureCosts', 'PPRC', 'AssetRecCost', 'MHRC',\n",
    "           'ATFHP', 'NetSaleProceeds', 'CreditEnhProceeds', 'RPMWP', 'OFP', 'NIBUPB', 'PFUPB', 'RMWPF',\n",
    "           'FPWA', 'ServicingIndicator']\n",
    "print('col_per list is len',len(col_per),'but using only 2!')\n",
    "start = time.time()\n",
    "\n",
    "df_per = concat([read_csv(fp, sep='|', names=col_per, usecols=[0, 10],\n",
    "                          dtype={'LoanID': int, 'CLDS': str}, index_col=False) for\n",
    "                 fp in sorted(glob.glob(f'{data_path}/perf/*.txt*'))], axis=0)\n",
    "\n",
    "print(time.time()-start,'secs')\n",
    "tread = time.time()-start\n",
    "print(df_per.shape)\n",
    "df_per.loc[df_per['LoanID'] == 100006270289].tail(10) #,100960628290]].tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3b84f560",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:52:58.595099Z",
     "start_time": "2021-07-07T23:52:58.316115Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(585303356, 2)\n",
      "(585303356, 2)\n"
     ]
    },
    {
     "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>LoanID</th>\n",
       "      <th>CLDS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           LoanID CLDS\n",
       "130  100006270289    0\n",
       "131  100006270289   -1\n",
       "132  100006270289    0\n",
       "133  100006270289    0\n",
       "134  100006270289    0\n",
       "135  100006270289    0\n",
       "136  100006270289    1\n",
       "137  100006270289    2\n",
       "138  100006270289    3\n",
       "139  100006270289    4"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_per.shape)\n",
    "df_per = df_per.dropna(subset=['CLDS'])\n",
    "df_per['CLDS'] = df_per['CLDS'].astype(str)\n",
    "print(df_per.shape)\n",
    "df_per.loc[df_per['LoanID'] == 100006270289].tail(10) #100960628290].tail(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f92d454",
   "metadata": {},
   "source": [
    "  ## Loan delinquency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b81505a3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-14T21:18:07.768698Z",
     "start_time": "2021-07-14T21:18:07.755990Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(724491, 2)\n",
      "(432236, 2)\n"
     ]
    },
    {
     "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>LoanID</th>\n",
       "      <th>CLDS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>100006270289</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           LoanID CLDS\n",
       "139  100006270289    4"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "### SET TO 4 MONTHS (str) below.###\n",
    "NUM_MONTHS = '4'\n",
    "###################################\n",
    "\n",
    "\n",
    "df_delinq4 = df_per.loc[df_per['CLDS'] == NUM_MONTHS]\n",
    "\n",
    "print(df_delinq4.shape)\n",
    "df_delinq4.drop_duplicates(subset='LoanID', keep='last', inplace=True)\n",
    "print(df_delinq4.shape)\n",
    "df_delinq4.loc[df_delinq4['LoanID'] == 100006270289].tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d2e5be4a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:52:59.483167Z",
     "start_time": "2021-07-07T23:52:58.872526Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(11232359, 26) (432236, 2)\n",
      "0.09046173095703125 secs\n",
      "(11232359, 28)\n"
     ]
    },
    {
     "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>index</th>\n",
       "      <th>LoanID</th>\n",
       "      <th>Channel</th>\n",
       "      <th>SellerName</th>\n",
       "      <th>OrInterestRate</th>\n",
       "      <th>OrUnpaidPrinc</th>\n",
       "      <th>OrLoanTerm</th>\n",
       "      <th>OrDate</th>\n",
       "      <th>FirstPayment</th>\n",
       "      <th>OrLTV</th>\n",
       "      <th>OrCLTV</th>\n",
       "      <th>NumBorrow</th>\n",
       "      <th>DTIRat</th>\n",
       "      <th>CreditScore</th>\n",
       "      <th>FTHomeBuyer</th>\n",
       "      <th>LoanPurpose</th>\n",
       "      <th>PropertyType</th>\n",
       "      <th>NumUnits</th>\n",
       "      <th>OccStatus</th>\n",
       "      <th>PropertyState</th>\n",
       "      <th>Zip</th>\n",
       "      <th>MortInsPerc</th>\n",
       "      <th>ProductType</th>\n",
       "      <th>CoCreditScore</th>\n",
       "      <th>Extra</th>\n",
       "      <th>MortInsType</th>\n",
       "      <th>RelMortInd</th>\n",
       "      <th>Default</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1242081</th>\n",
       "      <td>1242081</td>\n",
       "      <td>100006270289</td>\n",
       "      <td>C</td>\n",
       "      <td>WELLS FARGO BANK, N.A.</td>\n",
       "      <td>7.5</td>\n",
       "      <td>226000</td>\n",
       "      <td>360</td>\n",
       "      <td>11/2007</td>\n",
       "      <td>01/2008</td>\n",
       "      <td>95</td>\n",
       "      <td>95.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>704.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>P</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>CA</td>\n",
       "      <td>933</td>\n",
       "      <td>30.0</td>\n",
       "      <td>FRM</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>200801</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           index        LoanID Channel              SellerName  OrInterestRate  OrUnpaidPrinc  OrLoanTerm   OrDate FirstPayment  OrLTV  OrCLTV  NumBorrow  DTIRat  CreditScore FTHomeBuyer LoanPurpose PropertyType  NumUnits OccStatus PropertyState  Zip  MortInsPerc ProductType CoCreditScore  Extra MortInsType  RelMortInd  Default\n",
       "1242081  1242081  100006270289       C  WELLS FARGO BANK, N.A.             7.5         226000         360  11/2007      01/2008     95    95.0        1.0    42.0        704.0           Y           P           SF         1         P            CA  933         30.0         FRM          <NA>    1.0           N      200801        1"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_acq.shape, df_delinq4.shape)\n",
    "\n",
    "start = time.time()\n",
    "df = merge(df_acq, df_delinq4, on='LoanID', how='outer')\n",
    "df['Zip'] = df['Zip'].astype(str)\n",
    "print(time.time()-start, 'secs')\n",
    "\n",
    "del df_acq, df_per, df_delinq4\n",
    "gc.collect()\n",
    "\n",
    "df = df.reset_index().rename(columns={'CLDS': 'Default'})\n",
    "print(df.shape)\n",
    "\n",
    "df.loc[df['Default'] == '4', 'Default'] = 1\n",
    "df.loc[df['Default'].isnull(), 'Default'] = 0  # for null caused by outer join\n",
    "\n",
    "df['Default'] = df['Default'].astype(int)\n",
    "df.loc[df['LoanID'] == 100006270289].tail(10)  # Yes missed payments and so w/100960628290"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ce43bc25",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:52:59.558023Z",
     "start_time": "2021-07-07T23:52:59.485026Z"
    }
   },
   "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>index</th>\n",
       "      <th>LoanID</th>\n",
       "      <th>Channel</th>\n",
       "      <th>SellerName</th>\n",
       "      <th>OrInterestRate</th>\n",
       "      <th>OrUnpaidPrinc</th>\n",
       "      <th>OrLoanTerm</th>\n",
       "      <th>OrDate</th>\n",
       "      <th>FirstPayment</th>\n",
       "      <th>OrLTV</th>\n",
       "      <th>OrCLTV</th>\n",
       "      <th>NumBorrow</th>\n",
       "      <th>DTIRat</th>\n",
       "      <th>CreditScore</th>\n",
       "      <th>FTHomeBuyer</th>\n",
       "      <th>LoanPurpose</th>\n",
       "      <th>PropertyType</th>\n",
       "      <th>NumUnits</th>\n",
       "      <th>OccStatus</th>\n",
       "      <th>PropertyState</th>\n",
       "      <th>Zip</th>\n",
       "      <th>MortInsPerc</th>\n",
       "      <th>ProductType</th>\n",
       "      <th>CoCreditScore</th>\n",
       "      <th>Extra</th>\n",
       "      <th>MortInsType</th>\n",
       "      <th>RelMortInd</th>\n",
       "      <th>Default</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5098609</th>\n",
       "      <td>5098609</td>\n",
       "      <td>100010079393</td>\n",
       "      <td>C</td>\n",
       "      <td>WELLS FARGO BANK, N.A.</td>\n",
       "      <td>4.875</td>\n",
       "      <td>284000</td>\n",
       "      <td>360</td>\n",
       "      <td>01/2010</td>\n",
       "      <td>03/2010</td>\n",
       "      <td>80</td>\n",
       "      <td>80.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>773.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>P</td>\n",
       "      <td>PU</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>TX</td>\n",
       "      <td>787</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>201001</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           index        LoanID Channel              SellerName  OrInterestRate  OrUnpaidPrinc  OrLoanTerm   OrDate FirstPayment  OrLTV  OrCLTV  NumBorrow  DTIRat  CreditScore FTHomeBuyer LoanPurpose PropertyType  NumUnits OccStatus PropertyState  Zip MortInsPerc ProductType CoCreditScore Extra MortInsType  RelMortInd  Default\n",
       "5098609  5098609  100010079393       C  WELLS FARGO BANK, N.A.           4.875         284000         360  01/2010      03/2010     80    80.0        1.0    32.0        773.0           Y           P           PU         1         P            TX  787        <NA>         FRM          <NA>  <NA>           N      201001        0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Default'].value_counts()\n",
    "#Test no default  case:\n",
    "df.loc[df['LoanID'] == 100010079393]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7e0b1b7a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:52:59.652428Z",
     "start_time": "2021-07-07T23:52:59.559054Z"
    }
   },
   "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>index</th>\n",
       "      <th>LoanID</th>\n",
       "      <th>Channel</th>\n",
       "      <th>SellerName</th>\n",
       "      <th>OrInterestRate</th>\n",
       "      <th>OrUnpaidPrinc</th>\n",
       "      <th>OrLoanTerm</th>\n",
       "      <th>OrDate</th>\n",
       "      <th>FirstPayment</th>\n",
       "      <th>OrLTV</th>\n",
       "      <th>OrCLTV</th>\n",
       "      <th>NumBorrow</th>\n",
       "      <th>DTIRat</th>\n",
       "      <th>CreditScore</th>\n",
       "      <th>FTHomeBuyer</th>\n",
       "      <th>LoanPurpose</th>\n",
       "      <th>PropertyType</th>\n",
       "      <th>NumUnits</th>\n",
       "      <th>OccStatus</th>\n",
       "      <th>PropertyState</th>\n",
       "      <th>Zip</th>\n",
       "      <th>MortInsPerc</th>\n",
       "      <th>ProductType</th>\n",
       "      <th>CoCreditScore</th>\n",
       "      <th>Extra</th>\n",
       "      <th>MortInsType</th>\n",
       "      <th>RelMortInd</th>\n",
       "      <th>Default</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>160</td>\n",
       "      <td>189527725762</td>\n",
       "      <td>R</td>\n",
       "      <td>JPMORGAN CHASE BANK, NATIONAL ASSOCIATION</td>\n",
       "      <td>6.125</td>\n",
       "      <td>270000</td>\n",
       "      <td>360</td>\n",
       "      <td>01/2007</td>\n",
       "      <td>03/2007</td>\n",
       "      <td>58</td>\n",
       "      <td>86.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>680.0</td>\n",
       "      <td>N</td>\n",
       "      <td>C</td>\n",
       "      <td>PU</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>SC</td>\n",
       "      <td>294</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>161</td>\n",
       "      <td>189532228559</td>\n",
       "      <td>R</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>6.875</td>\n",
       "      <td>109000</td>\n",
       "      <td>360</td>\n",
       "      <td>03/2007</td>\n",
       "      <td>05/2007</td>\n",
       "      <td>80</td>\n",
       "      <td>80.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>629.0</td>\n",
       "      <td>N</td>\n",
       "      <td>R</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>PA</td>\n",
       "      <td>161</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>668.0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>162</td>\n",
       "      <td>189534717140</td>\n",
       "      <td>C</td>\n",
       "      <td>CITIMORTGAGE, INC.</td>\n",
       "      <td>6.000</td>\n",
       "      <td>190000</td>\n",
       "      <td>360</td>\n",
       "      <td>01/2007</td>\n",
       "      <td>03/2007</td>\n",
       "      <td>76</td>\n",
       "      <td>76.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>710.0</td>\n",
       "      <td>N</td>\n",
       "      <td>R</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>MN</td>\n",
       "      <td>554</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>192</td>\n",
       "      <td>229620456178</td>\n",
       "      <td>C</td>\n",
       "      <td>GMAC MORTGAGE, LLC</td>\n",
       "      <td>5.875</td>\n",
       "      <td>303000</td>\n",
       "      <td>360</td>\n",
       "      <td>01/2007</td>\n",
       "      <td>03/2007</td>\n",
       "      <td>51</td>\n",
       "      <td>71.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>736.0</td>\n",
       "      <td>N</td>\n",
       "      <td>C</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>FL</td>\n",
       "      <td>331</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>193</td>\n",
       "      <td>229650823232</td>\n",
       "      <td>C</td>\n",
       "      <td>BANK OF AMERICA, N.A.</td>\n",
       "      <td>7.000</td>\n",
       "      <td>188000</td>\n",
       "      <td>360</td>\n",
       "      <td>01/2007</td>\n",
       "      <td>03/2007</td>\n",
       "      <td>95</td>\n",
       "      <td>95.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>650.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>P</td>\n",
       "      <td>CO</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>IL</td>\n",
       "      <td>600</td>\n",
       "      <td>30.0</td>\n",
       "      <td>FRM</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>224</td>\n",
       "      <td>118097796777</td>\n",
       "      <td>R</td>\n",
       "      <td>GMAC MORTGAGE, LLC</td>\n",
       "      <td>6.500</td>\n",
       "      <td>250000</td>\n",
       "      <td>360</td>\n",
       "      <td>01/2007</td>\n",
       "      <td>03/2007</td>\n",
       "      <td>80</td>\n",
       "      <td>80.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>768.0</td>\n",
       "      <td>N</td>\n",
       "      <td>C</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>AZ</td>\n",
       "      <td>864</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>752.0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>225</td>\n",
       "      <td>118141013687</td>\n",
       "      <td>C</td>\n",
       "      <td>CITIMORTGAGE, INC.</td>\n",
       "      <td>6.500</td>\n",
       "      <td>250000</td>\n",
       "      <td>360</td>\n",
       "      <td>01/2007</td>\n",
       "      <td>03/2007</td>\n",
       "      <td>80</td>\n",
       "      <td>95.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>676.0</td>\n",
       "      <td>N</td>\n",
       "      <td>P</td>\n",
       "      <td>PU</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>CA</td>\n",
       "      <td>925</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>256</td>\n",
       "      <td>374304413957</td>\n",
       "      <td>B</td>\n",
       "      <td>BANK OF AMERICA, N.A.</td>\n",
       "      <td>6.375</td>\n",
       "      <td>401000</td>\n",
       "      <td>360</td>\n",
       "      <td>12/2006</td>\n",
       "      <td>02/2007</td>\n",
       "      <td>90</td>\n",
       "      <td>90.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>738.0</td>\n",
       "      <td>N</td>\n",
       "      <td>R</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>IL</td>\n",
       "      <td>604</td>\n",
       "      <td>25.0</td>\n",
       "      <td>FRM</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>2.0</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>257</th>\n",
       "      <td>257</td>\n",
       "      <td>374314490698</td>\n",
       "      <td>C</td>\n",
       "      <td>BANK OF AMERICA, N.A.</td>\n",
       "      <td>7.250</td>\n",
       "      <td>71000</td>\n",
       "      <td>360</td>\n",
       "      <td>02/2007</td>\n",
       "      <td>03/2007</td>\n",
       "      <td>84</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>637.0</td>\n",
       "      <td>N</td>\n",
       "      <td>C</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>I</td>\n",
       "      <td>NE</td>\n",
       "      <td>683</td>\n",
       "      <td>12.0</td>\n",
       "      <td>FRM</td>\n",
       "      <td>713.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>258</td>\n",
       "      <td>374320005729</td>\n",
       "      <td>R</td>\n",
       "      <td>BANK OF AMERICA, N.A.</td>\n",
       "      <td>6.375</td>\n",
       "      <td>223000</td>\n",
       "      <td>360</td>\n",
       "      <td>02/2007</td>\n",
       "      <td>04/2007</td>\n",
       "      <td>78</td>\n",
       "      <td>78.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>641.0</td>\n",
       "      <td>N</td>\n",
       "      <td>C</td>\n",
       "      <td>SF</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>TX</td>\n",
       "      <td>786</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>FRM</td>\n",
       "      <td>662.0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>N</td>\n",
       "      <td>200701</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     index        LoanID Channel                                 SellerName  OrInterestRate  OrUnpaidPrinc  OrLoanTerm   OrDate FirstPayment  OrLTV  OrCLTV  NumBorrow  DTIRat  CreditScore FTHomeBuyer LoanPurpose PropertyType  NumUnits OccStatus PropertyState  Zip MortInsPerc ProductType CoCreditScore Extra MortInsType  RelMortInd  Default\n",
       "160    160  189527725762       R  JPMORGAN CHASE BANK, NATIONAL ASSOCIATION           6.125         270000         360  01/2007      03/2007     58    86.0        2.0    34.0        680.0           N           C           PU         1         P            SC  294        <NA>         FRM          <NA>  <NA>           N      200701        1\n",
       "161    161  189532228559       R                                      OTHER           6.875         109000         360  03/2007      05/2007     80    80.0        2.0    50.0        629.0           N           R           SF         1         P            PA  161        <NA>         FRM         668.0  <NA>           N      200701        1\n",
       "162    162  189534717140       C                         CITIMORTGAGE, INC.           6.000         190000         360  01/2007      03/2007     76    76.0        1.0    36.0        710.0           N           R           SF         1         P            MN  554        <NA>         FRM          <NA>  <NA>           N      200701        1\n",
       "192    192  229620456178       C                         GMAC MORTGAGE, LLC           5.875         303000         360  01/2007      03/2007     51    71.0        1.0    51.0        736.0           N           C           SF         1         P            FL  331        <NA>         FRM          <NA>  <NA>           N      200701        1\n",
       "193    193  229650823232       C                      BANK OF AMERICA, N.A.           7.000         188000         360  01/2007      03/2007     95    95.0        1.0    30.0        650.0           Y           P           CO         1         P            IL  600        30.0         FRM          <NA>   1.0           N      200701        1\n",
       "224    224  118097796777       R                         GMAC MORTGAGE, LLC           6.500         250000         360  01/2007      03/2007     80    80.0        2.0    58.0        768.0           N           C           SF         1         P            AZ  864        <NA>         FRM         752.0  <NA>           N      200701        1\n",
       "225    225  118141013687       C                         CITIMORTGAGE, INC.           6.500         250000         360  01/2007      03/2007     80    95.0        2.0    29.0        676.0           N           P           PU         1         P            CA  925        <NA>         FRM          <NA>  <NA>           N      200701        1\n",
       "256    256  374304413957       B                      BANK OF AMERICA, N.A.           6.375         401000         360  12/2006      02/2007     90    90.0        1.0    51.0        738.0           N           R           SF         1         P            IL  604        25.0         FRM          <NA>   2.0           N      200701        1\n",
       "257    257  374314490698       C                      BANK OF AMERICA, N.A.           7.250          71000         360  02/2007      03/2007     84    84.0        2.0    40.0        637.0           N           C           SF         1         I            NE  683        12.0         FRM         713.0   1.0           N      200701        1\n",
       "258    258  374320005729       R                      BANK OF AMERICA, N.A.           6.375         223000         360  02/2007      04/2007     78    78.0        2.0    51.0        641.0           N           C           SF         1         P            TX  786        <NA>         FRM         662.0  <NA>           N      200701        1"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Default'] == 1].head(10)  # focus on rearer defaults"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6bf0676",
   "metadata": {},
   "source": [
    "Before filling in missing values, let’s first take a quick look at the distribution of values in several of the data columns. We can start with our target variable, Default.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "946818d3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:52:59.655476Z",
     "start_time": "2021-07-07T23:52:59.653364Z"
    }
   },
   "outputs": [],
   "source": [
    "nullHistogram = None\n",
    "if n_gpus == 0:\n",
    "    nullHistogram = df.apply(lambda x: x.isnull().sum(), axis=0)\n",
    "nullHistogram"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ff35748a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:52:59.672721Z",
     "start_time": "2021-07-07T23:52:59.656308Z"
    }
   },
   "outputs": [],
   "source": [
    "if n_gpus == 0 and isPlot:\n",
    "    sns.countplot(df['Default'])\n",
    "    vc = df['Default'].value_counts()  # Let's see the imbalance \n",
    "    print('default density = ', vc[1]/vc[0], \"or 1 in\", np.round(vc[0]/vc[1],1))\n",
    "    vc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "b1831203",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:52:59.687877Z",
     "start_time": "2021-07-07T23:52:59.674121Z"
    }
   },
   "outputs": [],
   "source": [
    "if n_gpus == 0 and isPlot:\n",
    "    columns = ['OrInterestRate','OrCLTV','DTIRat','CreditScore','OrUnpaidPrinc']\n",
    "    fig, axes = plt.subplots(nrows=2, ncols=3, figsize=(8,7))\n",
    "    plt.tight_layout(w_pad=3.0, h_pad=3.0)\n",
    "\n",
    "    print(range(1, len(columns)+1), columns)\n",
    "    for i, column in zip(range(1, len(columns)+1), columns):\n",
    "        plt.subplot(3, 3, i)\n",
    "        print(i,column)\n",
    "        print(len(df[\"Default\"]));print(len(df[column]))\n",
    "        sns.boxplot(x=\"Default\", y=column, data=df, linewidth=0.5)\n",
    "        plt.xlabel('Default')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "6de4878e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:52:59.704687Z",
     "start_time": "2021-07-07T23:52:59.689289Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['index', 'LoanID', 'Channel', 'SellerName', 'OrInterestRate', 'OrUnpaidPrinc', 'OrLoanTerm', 'OrDate', 'FirstPayment', 'OrLTV', 'OrCLTV', 'NumBorrow', 'DTIRat', 'CreditScore', 'FTHomeBuyer', 'LoanPurpose', 'PropertyType', 'NumUnits', 'OccStatus', 'PropertyState', 'Zip', 'MortInsPerc', 'ProductType', 'CoCreditScore', 'Extra', 'MortInsType', 'RelMortInd', 'Default'], dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "4e97cf01",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:52:59.731454Z",
     "start_time": "2021-07-07T23:52:59.705969Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(11232359, 22)\n"
     ]
    }
   ],
   "source": [
    "df.drop(['index','OrDate','OrLTV','MortInsPerc','RelMortInd','FirstPayment'], axis=1, inplace=True)\n",
    "\n",
    "print(df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "6e3598dd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:53:00.235487Z",
     "start_time": "2021-07-07T23:52:59.732587Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OrInterestRate\n",
      "OrCLTV\n",
      "NumBorrow\n",
      "DTIRat\n",
      "CreditScore\n",
      "CoCreditScore\n",
      "Extra\n"
     ]
    }
   ],
   "source": [
    "def fillnan(df):\n",
    "    if n_gpus == 0: \n",
    "        columns = df.columns[df.isnull().any().tolist()]\n",
    "    else: \n",
    "        columns = df.columns[df.isnull().any().to_arrow().to_pylist()]\n",
    "    for name in columns:\n",
    "        print(name)\n",
    "        if df[name].dtype == 'object':\n",
    "            df.loc[df[name].isnull(), name] = df[name].mode().to_pandas()[0]\n",
    "        else:\n",
    "            #  a possible better way could be to groupby the particular time period and use those values\n",
    "            #  ex. the interest rate during a particular month, year, state combination.\n",
    "            df.loc[df[name].isnull(), name] = df[name].mean()\n",
    "    return df\n",
    "df = fillnan(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca1eaed9",
   "metadata": {},
   "source": [
    "Now let's factorize the categorical columns and save the cleaned data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "30cfe948",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:53:09.889786Z",
     "start_time": "2021-07-07T23:53:08.808793Z"
    }
   },
   "outputs": [],
   "source": [
    "df['Channel'], Channel = df['Channel'].factorize()\n",
    "df['SellerName'], SellerName = df['SellerName'].factorize()\n",
    "df['FTHomeBuyer'], FTHomeBuyer = df['FTHomeBuyer'].factorize()\n",
    "df['LoanPurpose'], LoanPurpose = df['LoanPurpose'].factorize()\n",
    "df['PropertyType'], PropertyType = df['PropertyType'].factorize()\n",
    "df['OccStatus'], OccStatus = df['OccStatus'].factorize()\n",
    "df['PropertyState'], PropertyState = df['PropertyState'].factorize()\n",
    "df['Zip'], Zip = df['Zip'].factorize()\n",
    "df['ProductType'], ProductType = df['ProductType'].factorize()\n",
    "df['MortInsType'], MortInsType = df['MortInsType'].factorize()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "0d417e4d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:53:25.249833Z",
     "start_time": "2021-07-07T23:53:24.482544Z"
    }
   },
   "outputs": [],
   "source": [
    "# save cleaned data to parquet file\n",
    "\n",
    "os.makedirs('/rapids/notebooks/cleaned_data', exist_ok=True)\n",
    "df.to_parquet('/rapids/notebooks/cleaned_data/fnma_cleaned.pq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "8a36f97c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:53:27.371172Z",
     "start_time": "2021-07-07T23:53:27.361506Z"
    }
   },
   "outputs": [],
   "source": [
    "# save these mappings for transforming back and forth between factorized and real values\n",
    "mappings = {\n",
    "    'Channel': Channel.to_pandas().tolist(),\n",
    "    'SellerName': SellerName.to_pandas().tolist(),\n",
    "    'FTHomeBuyer': FTHomeBuyer.to_pandas().tolist(),\n",
    "    'LoanPurpose': LoanPurpose.to_pandas().tolist(),\n",
    "    'PropertyType': PropertyType.to_pandas().tolist(),\n",
    "    'OccStatus': OccStatus.to_pandas().tolist(),\n",
    "    'PropertyState': PropertyState.to_pandas().tolist(),\n",
    "    'Zip': Zip.to_pandas().tolist(),\n",
    "    'ProductType': ProductType.to_pandas().tolist(),\n",
    "    'MortInsType': MortInsType.to_pandas().tolist(),\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "af36c47f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-07T23:53:34.813607Z",
     "start_time": "2021-07-07T23:53:34.808387Z"
    }
   },
   "outputs": [],
   "source": [
    "with open('/rapids/notebooks/cleaned_data/fnma_mappings.pkl', 'wb') as f:\n",
    "    pickle.dump(mappings, f)\n",
    "\n",
    "# #  test opening pickle file to make sure items serialized appropriately\n",
    "# with open('fnma_mappings.pkl', 'rb') as f:\n",
    "#     asdf = pickle.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25b435e3",
   "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.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}