{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Introduction"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>id</th>\n",
" <th>member_id</th>\n",
" <th>loan_amnt</th>\n",
" <th>funded_amnt</th>\n",
" <th>funded_amnt_inv</th>\n",
" <th>term</th>\n",
" <th>int_rate</th>\n",
" <th>installment</th>\n",
" <th>grade</th>\n",
" <th>sub_grade</th>\n",
" <th>emp_title</th>\n",
" <th>emp_length</th>\n",
" <th>home_ownership</th>\n",
" <th>annual_inc</th>\n",
" <th>verification_status</th>\n",
" <th>issue_d</th>\n",
" <th>loan_status</th>\n",
" <th>pymnt_plan</th>\n",
" <th>purpose</th>\n",
" <th>title</th>\n",
" <th>zip_code</th>\n",
" <th>addr_state</th>\n",
" <th>dti</th>\n",
" <th>delinq_2yrs</th>\n",
" <th>earliest_cr_line</th>\n",
" <th>inq_last_6mths</th>\n",
" <th>open_acc</th>\n",
" <th>pub_rec</th>\n",
" <th>revol_bal</th>\n",
" <th>revol_util</th>\n",
" <th>total_acc</th>\n",
" <th>initial_list_status</th>\n",
" <th>out_prncp</th>\n",
" <th>out_prncp_inv</th>\n",
" <th>total_pymnt</th>\n",
" <th>total_pymnt_inv</th>\n",
" <th>total_rec_prncp</th>\n",
" <th>total_rec_int</th>\n",
" <th>total_rec_late_fee</th>\n",
" <th>recoveries</th>\n",
" <th>collection_recovery_fee</th>\n",
" <th>last_pymnt_d</th>\n",
" <th>last_pymnt_amnt</th>\n",
" <th>last_credit_pull_d</th>\n",
" <th>collections_12_mths_ex_med</th>\n",
" <th>policy_code</th>\n",
" <th>application_type</th>\n",
" <th>acc_now_delinq</th>\n",
" <th>chargeoff_within_12_mths</th>\n",
" <th>delinq_amnt</th>\n",
" <th>pub_rec_bankruptcies</th>\n",
" <th>tax_liens</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>1077501</td>\n",
" <td>1296599.0</td>\n",
" <td>5000.0</td>\n",
" <td>5000.0</td>\n",
" <td>4975.0</td>\n",
" <td>36 months</td>\n",
" <td>10.65%</td>\n",
" <td>162.87</td>\n",
" <td>B</td>\n",
" <td>B2</td>\n",
" <td>NaN</td>\n",
" <td>10+ years</td>\n",
" <td>RENT</td>\n",
" <td>24000.0</td>\n",
" <td>Verified</td>\n",
" <td>Dec-2011</td>\n",
" <td>Fully Paid</td>\n",
" <td>n</td>\n",
" <td>credit_card</td>\n",
" <td>Computer</td>\n",
" <td>860xx</td>\n",
" <td>AZ</td>\n",
" <td>27.65</td>\n",
" <td>0.0</td>\n",
" <td>Jan-1985</td>\n",
" <td>1.0</td>\n",
" <td>3.0</td>\n",
" <td>0.0</td>\n",
" <td>13648.0</td>\n",
" <td>83.7%</td>\n",
" <td>9.0</td>\n",
" <td>f</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>5863.155187</td>\n",
" <td>5833.84</td>\n",
" <td>5000.00</td>\n",
" <td>863.16</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>Jan-2015</td>\n",
" <td>171.62</td>\n",
" <td>Jun-2016</td>\n",
" <td>0.0</td>\n",
" <td>1.0</td>\n",
" <td>INDIVIDUAL</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1077430</td>\n",
" <td>1314167.0</td>\n",
" <td>2500.0</td>\n",
" <td>2500.0</td>\n",
" <td>2500.0</td>\n",
" <td>60 months</td>\n",
" <td>15.27%</td>\n",
" <td>59.83</td>\n",
" <td>C</td>\n",
" <td>C4</td>\n",
" <td>Ryder</td>\n",
" <td>< 1 year</td>\n",
" <td>RENT</td>\n",
" <td>30000.0</td>\n",
" <td>Source Verified</td>\n",
" <td>Dec-2011</td>\n",
" <td>Charged Off</td>\n",
" <td>n</td>\n",
" <td>car</td>\n",
" <td>bike</td>\n",
" <td>309xx</td>\n",
" <td>GA</td>\n",
" <td>1.00</td>\n",
" <td>0.0</td>\n",
" <td>Apr-1999</td>\n",
" <td>5.0</td>\n",
" <td>3.0</td>\n",
" <td>0.0</td>\n",
" <td>1687.0</td>\n",
" <td>9.4%</td>\n",
" <td>4.0</td>\n",
" <td>f</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>1008.710000</td>\n",
" <td>1008.71</td>\n",
" <td>456.46</td>\n",
" <td>435.17</td>\n",
" <td>0.00</td>\n",
" <td>117.08</td>\n",
" <td>1.11</td>\n",
" <td>Apr-2013</td>\n",
" <td>119.66</td>\n",
" <td>Sep-2013</td>\n",
" <td>0.0</td>\n",
" <td>1.0</td>\n",
" <td>INDIVIDUAL</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>1077175</td>\n",
" <td>1313524.0</td>\n",
" <td>2400.0</td>\n",
" <td>2400.0</td>\n",
" <td>2400.0</td>\n",
" <td>36 months</td>\n",
" <td>15.96%</td>\n",
" <td>84.33</td>\n",
" <td>C</td>\n",
" <td>C5</td>\n",
" <td>NaN</td>\n",
" <td>10+ years</td>\n",
" <td>RENT</td>\n",
" <td>12252.0</td>\n",
" <td>Not Verified</td>\n",
" <td>Dec-2011</td>\n",
" <td>Fully Paid</td>\n",
" <td>n</td>\n",
" <td>small_business</td>\n",
" <td>real estate business</td>\n",
" <td>606xx</td>\n",
" <td>IL</td>\n",
" <td>8.72</td>\n",
" <td>0.0</td>\n",
" <td>Nov-2001</td>\n",
" <td>2.0</td>\n",
" <td>2.0</td>\n",
" <td>0.0</td>\n",
" <td>2956.0</td>\n",
" <td>98.5%</td>\n",
" <td>10.0</td>\n",
" <td>f</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>3005.666844</td>\n",
" <td>3005.67</td>\n",
" <td>2400.00</td>\n",
" <td>605.67</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>Jun-2014</td>\n",
" <td>649.91</td>\n",
" <td>Jun-2016</td>\n",
" <td>0.0</td>\n",
" <td>1.0</td>\n",
" <td>INDIVIDUAL</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>1076863</td>\n",
" <td>1277178.0</td>\n",
" <td>10000.0</td>\n",
" <td>10000.0</td>\n",
" <td>10000.0</td>\n",
" <td>36 months</td>\n",
" <td>13.49%</td>\n",
" <td>339.31</td>\n",
" <td>C</td>\n",
" <td>C1</td>\n",
" <td>AIR RESOURCES BOARD</td>\n",
" <td>10+ years</td>\n",
" <td>RENT</td>\n",
" <td>49200.0</td>\n",
" <td>Source Verified</td>\n",
" <td>Dec-2011</td>\n",
" <td>Fully Paid</td>\n",
" <td>n</td>\n",
" <td>other</td>\n",
" <td>personel</td>\n",
" <td>917xx</td>\n",
" <td>CA</td>\n",
" <td>20.00</td>\n",
" <td>0.0</td>\n",
" <td>Feb-1996</td>\n",
" <td>1.0</td>\n",
" <td>10.0</td>\n",
" <td>0.0</td>\n",
" <td>5598.0</td>\n",
" <td>21%</td>\n",
" <td>37.0</td>\n",
" <td>f</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>12231.890000</td>\n",
" <td>12231.89</td>\n",
" <td>10000.00</td>\n",
" <td>2214.92</td>\n",
" <td>16.97</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>Jan-2015</td>\n",
" <td>357.48</td>\n",
" <td>Apr-2016</td>\n",
" <td>0.0</td>\n",
" <td>1.0</td>\n",
" <td>INDIVIDUAL</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>1075358</td>\n",
" <td>1311748.0</td>\n",
" <td>3000.0</td>\n",
" <td>3000.0</td>\n",
" <td>3000.0</td>\n",
" <td>60 months</td>\n",
" <td>12.69%</td>\n",
" <td>67.79</td>\n",
" <td>B</td>\n",
" <td>B5</td>\n",
" <td>University Medical Group</td>\n",
" <td>1 year</td>\n",
" <td>RENT</td>\n",
" <td>80000.0</td>\n",
" <td>Source Verified</td>\n",
" <td>Dec-2011</td>\n",
" <td>Current</td>\n",
" <td>n</td>\n",
" <td>other</td>\n",
" <td>Personal</td>\n",
" <td>972xx</td>\n",
" <td>OR</td>\n",
" <td>17.94</td>\n",
" <td>0.0</td>\n",
" <td>Jan-1996</td>\n",
" <td>0.0</td>\n",
" <td>15.0</td>\n",
" <td>0.0</td>\n",
" <td>27783.0</td>\n",
" <td>53.9%</td>\n",
" <td>38.0</td>\n",
" <td>f</td>\n",
" <td>461.73</td>\n",
" <td>461.73</td>\n",
" <td>3581.120000</td>\n",
" <td>3581.12</td>\n",
" <td>2538.27</td>\n",
" <td>1042.85</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>Jun-2016</td>\n",
" <td>67.79</td>\n",
" <td>Jun-2016</td>\n",
" <td>0.0</td>\n",
" <td>1.0</td>\n",
" <td>INDIVIDUAL</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" id member_id loan_amnt funded_amnt funded_amnt_inv term \\\n",
"0 1077501 1296599.0 5000.0 5000.0 4975.0 36 months \n",
"1 1077430 1314167.0 2500.0 2500.0 2500.0 60 months \n",
"2 1077175 1313524.0 2400.0 2400.0 2400.0 36 months \n",
"3 1076863 1277178.0 10000.0 10000.0 10000.0 36 months \n",
"4 1075358 1311748.0 3000.0 3000.0 3000.0 60 months \n",
"\n",
" int_rate installment grade sub_grade emp_title emp_length \\\n",
"0 10.65% 162.87 B B2 NaN 10+ years \n",
"1 15.27% 59.83 C C4 Ryder < 1 year \n",
"2 15.96% 84.33 C C5 NaN 10+ years \n",
"3 13.49% 339.31 C C1 AIR RESOURCES BOARD 10+ years \n",
"4 12.69% 67.79 B B5 University Medical Group 1 year \n",
"\n",
" home_ownership annual_inc verification_status issue_d loan_status \\\n",
"0 RENT 24000.0 Verified Dec-2011 Fully Paid \n",
"1 RENT 30000.0 Source Verified Dec-2011 Charged Off \n",
"2 RENT 12252.0 Not Verified Dec-2011 Fully Paid \n",
"3 RENT 49200.0 Source Verified Dec-2011 Fully Paid \n",
"4 RENT 80000.0 Source Verified Dec-2011 Current \n",
"\n",
" pymnt_plan purpose title zip_code addr_state dti \\\n",
"0 n credit_card Computer 860xx AZ 27.65 \n",
"1 n car bike 309xx GA 1.00 \n",
"2 n small_business real estate business 606xx IL 8.72 \n",
"3 n other personel 917xx CA 20.00 \n",
"4 n other Personal 972xx OR 17.94 \n",
"\n",
" delinq_2yrs earliest_cr_line inq_last_6mths open_acc pub_rec revol_bal \\\n",
"0 0.0 Jan-1985 1.0 3.0 0.0 13648.0 \n",
"1 0.0 Apr-1999 5.0 3.0 0.0 1687.0 \n",
"2 0.0 Nov-2001 2.0 2.0 0.0 2956.0 \n",
"3 0.0 Feb-1996 1.0 10.0 0.0 5598.0 \n",
"4 0.0 Jan-1996 0.0 15.0 0.0 27783.0 \n",
"\n",
" revol_util total_acc initial_list_status out_prncp out_prncp_inv \\\n",
"0 83.7% 9.0 f 0.00 0.00 \n",
"1 9.4% 4.0 f 0.00 0.00 \n",
"2 98.5% 10.0 f 0.00 0.00 \n",
"3 21% 37.0 f 0.00 0.00 \n",
"4 53.9% 38.0 f 461.73 461.73 \n",
"\n",
" total_pymnt total_pymnt_inv total_rec_prncp total_rec_int \\\n",
"0 5863.155187 5833.84 5000.00 863.16 \n",
"1 1008.710000 1008.71 456.46 435.17 \n",
"2 3005.666844 3005.67 2400.00 605.67 \n",
"3 12231.890000 12231.89 10000.00 2214.92 \n",
"4 3581.120000 3581.12 2538.27 1042.85 \n",
"\n",
" total_rec_late_fee recoveries collection_recovery_fee last_pymnt_d \\\n",
"0 0.00 0.00 0.00 Jan-2015 \n",
"1 0.00 117.08 1.11 Apr-2013 \n",
"2 0.00 0.00 0.00 Jun-2014 \n",
"3 16.97 0.00 0.00 Jan-2015 \n",
"4 0.00 0.00 0.00 Jun-2016 \n",
"\n",
" last_pymnt_amnt last_credit_pull_d collections_12_mths_ex_med \\\n",
"0 171.62 Jun-2016 0.0 \n",
"1 119.66 Sep-2013 0.0 \n",
"2 649.91 Jun-2016 0.0 \n",
"3 357.48 Apr-2016 0.0 \n",
"4 67.79 Jun-2016 0.0 \n",
"\n",
" policy_code application_type acc_now_delinq chargeoff_within_12_mths \\\n",
"0 1.0 INDIVIDUAL 0.0 0.0 \n",
"1 1.0 INDIVIDUAL 0.0 0.0 \n",
"2 1.0 INDIVIDUAL 0.0 0.0 \n",
"3 1.0 INDIVIDUAL 0.0 0.0 \n",
"4 1.0 INDIVIDUAL 0.0 0.0 \n",
"\n",
" delinq_amnt pub_rec_bankruptcies tax_liens \n",
"0 0.0 0.0 0.0 \n",
"1 0.0 0.0 0.0 \n",
"2 0.0 0.0 0.0 \n",
"3 0.0 0.0 0.0 \n",
"4 0.0 0.0 0.0 "
]
},
"execution_count": 1,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import pandas as pd\n",
"import numpy as np\n",
"pd.options.display.max_columns = 99\n",
"\n",
"first_five = pd.read_csv('loans_2007.csv', nrows=5)\n",
"first_five"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"1.5506629943847656"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"thousand_chunk = pd.read_csv('loans_2007.csv', nrows=1000)\n",
"thousand_chunk.memory_usage(deep=True).sum()/(1024*1024)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Let's try tripling to 3000 rows and calculate the memory footprint for each chunk."
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"4.6512298584\n",
"4.64817810059\n",
"4.64966869354\n",
"4.65059375763\n",
"4.64692020416\n",
"4.64837551117\n",
"4.64736747742\n",
"4.64941596985\n",
"4.64743518829\n",
"4.64727210999\n",
"4.6598777771\n",
"4.6566619873\n",
"4.66349601746\n",
"4.89784526825\n",
"0.880808830261\n"
]
}
],
"source": [
"chunk_iter = pd.read_csv('loans_2007.csv', chunksize=3000)\n",
"for chunk in chunk_iter:\n",
" print(chunk.memory_usage(deep=True).sum()/(1024*1024))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## How many rows in the data set?"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"42538\n"
]
}
],
"source": [
"chunk_iter = pd.read_csv('loans_2007.csv', chunksize=3000)\n",
"total_rows = 0\n",
"for chunk in chunk_iter:\n",
" total_rows += len(chunk)\n",
"print(total_rows)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Exploring the Data in Chunks\n",
"\n",
"## How many columns have a numeric type? How many have a string type?"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 31, 30, 30]\n",
"[21, 21, 21, 21, 21, 21, 21, 21, 21, 21, 21, 21, 21, 22, 22]\n"
]
}
],
"source": [
"# Numeric columns\n",
"loans_chunks = pd.read_csv('loans_2007.csv',chunksize=3000)\n",
"\n",
"numeric = []\n",
"string = []\n",
"for lc in loans_chunks:\n",
" nums = lc.select_dtypes(include=[np.number]).shape[1]\n",
" numeric.append(nums)\n",
" strs = lc.select_dtypes(include=['object']).shape[1]\n",
" string.append(strs)\n",
"\n",
"print(numeric)\n",
"print(string)"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"overall obj cols: ['term', 'int_rate', 'grade', 'sub_grade', 'emp_title', 'emp_length', 'home_ownership', 'verification_status', 'issue_d', 'loan_status', 'pymnt_plan', 'purpose', 'title', 'zip_code', 'addr_state', 'earliest_cr_line', 'revol_util', 'initial_list_status', 'last_pymnt_d', 'last_credit_pull_d', 'application_type'] \n",
"\n",
"chunk obj cols: ['id', 'term', 'int_rate', 'grade', 'sub_grade', 'emp_title', 'emp_length', 'home_ownership', 'verification_status', 'issue_d', 'loan_status', 'pymnt_plan', 'purpose', 'title', 'zip_code', 'addr_state', 'earliest_cr_line', 'revol_util', 'initial_list_status', 'last_pymnt_d', 'last_credit_pull_d', 'application_type'] \n",
"\n",
"overall obj cols: ['term', 'int_rate', 'grade', 'sub_grade', 'emp_title', 'emp_length', 'home_ownership', 'verification_status', 'issue_d', 'loan_status', 'pymnt_plan', 'purpose', 'title', 'zip_code', 'addr_state', 'earliest_cr_line', 'revol_util', 'initial_list_status', 'last_pymnt_d', 'last_credit_pull_d', 'application_type'] \n",
"\n",
"chunk obj cols: ['id', 'term', 'int_rate', 'grade', 'sub_grade', 'emp_title', 'emp_length', 'home_ownership', 'verification_status', 'issue_d', 'loan_status', 'pymnt_plan', 'purpose', 'title', 'zip_code', 'addr_state', 'earliest_cr_line', 'revol_util', 'initial_list_status', 'last_pymnt_d', 'last_credit_pull_d', 'application_type'] \n",
"\n"
]
}
],
"source": [
"# Are string columns consistent across chunks?\n",
"obj_cols = []\n",
"chunk_iter = pd.read_csv('loans_2007.csv', chunksize=3000)\n",
"\n",
"for chunk in chunk_iter:\n",
" chunk_obj_cols = chunk.select_dtypes(include=['object']).columns.tolist()\n",
" if len(obj_cols) > 0:\n",
" is_same = obj_cols == chunk_obj_cols\n",
" if not is_same:\n",
" print(\"overall obj cols:\", obj_cols, \"\\n\")\n",
" print(\"chunk obj cols:\", chunk_obj_cols, \"\\n\") \n",
" else:\n",
" obj_cols = chunk_obj_cols"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": true
},
"source": [
"### Observation 1: By default -- 31 numeric columns and 21 string columns.\n",
"\n",
"### Observation 2: It seems like one column in particular (the `id` column) is being cast to int64 in the last 2 chunks but not in the earlier chunks. Since the `id` column won't be useful for analysis, visualization, or predictive modelling let's ignore this column.\n",
"\n",
"## How many unique values are there in each string column? How many of the string columns contain values that are less than 50% unique?"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"term 2\n",
"loan_status 9\n",
"purpose 14\n",
"grade 7\n",
"sub_grade 35\n",
"initial_list_status 1\n",
"verification_status 3\n",
"application_type 1\n",
"emp_length 12\n",
"home_ownership 5\n",
"pymnt_plan 2\n"
]
}
],
"source": [
"loans_chunks = pd.read_csv('loans_2007.csv',chunksize=3000)\n",
"\n",
"uniques = {}\n",
"for lc in loans_chunks:\n",
" strings_only = lc.select_dtypes(include=['object'])\n",
" cols = strings_only.columns\n",
" for c in cols:\n",
" val_counts = strings_only[c].value_counts()\n",
" if c in uniques:\n",
" uniques[c].append(val_counts)\n",
" else:\n",
" uniques[c] = [val_counts]\n",
"\n",
"uniques_combined = {}\n",
"unique_stats = {\n",
" 'column_name': [],\n",
" 'total_values': [],\n",
" 'unique_values': [],\n",
"}\n",
"for col in uniques:\n",
" u_concat = pd.concat(uniques[col])\n",
" u_group = u_concat.groupby(u_concat.index).sum()\n",
" uniques_combined[col] = u_group\n",
" if u_group.shape[0] < 50:\n",
" print(col, u_group.shape[0])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Which float columns have no missing values and could be candidates for conversion to the integer type?"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"member_id 3\n",
"total_rec_int 3\n",
"total_pymnt_inv 3\n",
"total_pymnt 3\n",
"revol_bal 3\n",
"recoveries 3\n",
"policy_code 3\n",
"out_prncp_inv 3\n",
"out_prncp 3\n",
"total_rec_late_fee 3\n",
"loan_amnt 3\n",
"last_pymnt_amnt 3\n",
"total_rec_prncp 3\n",
"funded_amnt_inv 3\n",
"funded_amnt 3\n",
"dti 3\n",
"collection_recovery_fee 3\n",
"installment 3\n",
"annual_inc 7\n",
"inq_last_6mths 32\n",
"total_acc 32\n",
"delinq_2yrs 32\n",
"pub_rec 32\n",
"delinq_amnt 32\n",
"open_acc 32\n",
"acc_now_delinq 32\n",
"tax_liens 108\n",
"collections_12_mths_ex_med 148\n",
"chargeoff_within_12_mths 148\n",
"pub_rec_bankruptcies 1368\n",
"dtype: int64"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"loans_chunks = pd.read_csv('loans_2007.csv',chunksize=3000)\n",
"\n",
"missing = []\n",
"for lc in loans_chunks:\n",
" floats = lc.select_dtypes(include=['float'])\n",
" missing.append(floats.apply(pd.isnull).sum())\n",
"\n",
"combined_missing = pd.concat(missing)\n",
"combined_missing.groupby(combined_missing.index).sum().sort_values()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Calculate the total memory usage across all of the chunks."
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"66.245146751403809"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"loans_chunks = pd.read_csv('loans_2007.csv',chunksize=3000)\n",
"\n",
"mem_usage = []\n",
"\n",
"for lc in loans_chunks:\n",
" mem_usage.append(lc.memory_usage(deep=True).sum() / 1024 ** 2)\n",
"\n",
"sum(mem_usage)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Optimizing String Columns\n",
"\n",
"### Determine which string columns you can convert to a numeric type if you clean them. Let's focus on columns that would actually be useful for analysis and modelling."
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"['term',\n",
" 'int_rate',\n",
" 'grade',\n",
" 'sub_grade',\n",
" 'emp_title',\n",
" 'emp_length',\n",
" 'home_ownership',\n",
" 'verification_status',\n",
" 'issue_d',\n",
" 'loan_status',\n",
" 'pymnt_plan',\n",
" 'purpose',\n",
" 'title',\n",
" 'zip_code',\n",
" 'addr_state',\n",
" 'earliest_cr_line',\n",
" 'revol_util',\n",
" 'initial_list_status',\n",
" 'last_pymnt_d',\n",
" 'last_credit_pull_d',\n",
" 'application_type']"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"obj_cols"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [],
"source": [
"useful_obj_cols = ['term', 'sub_grade', 'emp_title', 'home_ownership', 'verification_status', 'issue_d', 'purpose', 'earliest_cr_line', 'revol_util', 'last_pymnt_d', 'last_credit_pull_d']"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [],
"source": [
"## Create dictionary (key: column, value: list of Series objects representing each chunk's value counts)\n",
"chunk_iter = pd.read_csv('loans_2007.csv', chunksize=3000)\n",
"str_cols_vc = {}\n",
"for chunk in chunk_iter:\n",
" str_cols = chunk.select_dtypes(include=['object'])\n",
" for col in str_cols.columns:\n",
" current_col_vc = str_cols[col].value_counts()\n",
" if col in str_cols_vc:\n",
" str_cols_vc[col].append(current_col_vc)\n",
" else:\n",
" str_cols_vc[col] = [current_col_vc]"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [],
"source": [
"## Combine the value counts.\n",
"combined_vcs = {}\n",
"\n",
"for col in str_cols_vc:\n",
" combined_vc = pd.concat(str_cols_vc[col])\n",
" final_vc = combined_vc.groupby(combined_vc.index).sum()\n",
" combined_vcs[col] = final_vc"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"term\n",
" 36 months 31534\n",
" 60 months 11001\n",
"Name: term, dtype: int64\n",
"-----------\n",
"sub_grade\n",
"A1 1142\n",
"A2 1520\n",
"A3 1823\n",
"A4 2905\n",
"A5 2793\n",
"B1 1882\n",
"B2 2113\n",
"B3 2997\n",
"B4 2590\n",
"B5 2807\n",
"C1 2264\n",
"C2 2157\n",
"C3 1658\n",
"C4 1370\n",
"C5 1291\n",
"D1 1053\n",
"D2 1485\n",
"D3 1322\n",
"D4 1140\n",
"D5 1016\n",
"E1 884\n",
"E2 791\n",
"E3 668\n",
"E4 552\n",
"E5 499\n",
"F1 392\n",
"F2 308\n",
"F3 236\n",
"F4 211\n",
"F5 154\n",
"G1 141\n",
"G2 107\n",
"G3 79\n",
"G4 99\n",
"G5 86\n",
"Name: sub_grade, dtype: int64\n",
"-----------\n",
"emp_title\n",
" old palm inc 1\n",
" Brocade Communications 1\n",
" CenturyLink 1\n",
" Department of Homeland Security 1\n",
" Down To Earth Distributors, Inc. 1\n",
" Plaid, Inc. 1\n",
" U.S. Dept. Of Homeland Security 1\n",
" pacific crane maintenance company 1\n",
"$260M '06 vintage technology venture capital firm 1\n",
"(Collaborative) Abbott Nutrition Intl 1\n",
"(self) Castleforte Group 1\n",
"0CEAN VIEW INTL REALTY/C21 UNITED PLATINUM 1\n",
"1 Cochran Hyundai 1\n",
"1 and 1 Internet Inc. 1\n",
"1)-Yavapai Regional Medical Center 2)- Dr. cantors office 1\n",
"1-800 Contacts 1\n",
"101 restaurant 1\n",
"1013 communications 1\n",
"1125 memorex dr 1\n",
"1199SEIU Benefit and Pension Funds 1\n",
"1199SEIU UHE 1\n",
"123 Recovery 1\n",
"123 appartment corp. 1\n",
"128 Air Refueling Wing (USAF) 1\n",
"1400 1\n",
"15 1\n",
"162 fighter wing 1\n",
"16th MP BDE, U.S. Army 1\n",
"1800-got-junk? 1\n",
"182nd Airlift Wing Fire Dept. 1\n",
" ..\n",
"wyoming valley hospital 1\n",
"wythe county community hospital 1\n",
"xcel fire protection 1\n",
"xerox cor. 1\n",
"xerox corp 1\n",
"xo communication 1\n",
"xotic deliveries 1\n",
"xpedite technologies inc 1\n",
"xpedx 1\n",
"yankee candle company 1\n",
"ye olde pepper co 1\n",
"yellow cab com. 1\n",
"yellow cab taxi 1\n",
"ymca 3\n",
"yonkers fire department 1\n",
"york hospital dental center 1\n",
"yorkville physical therapy 1\n",
"young and associates 1\n",
"young atitudes aveda 1\n",
"youth villages 1\n",
"yrbnyc.com / Yellow Rat Bastard 1\n",
"yrc 1\n",
"yrcw 1\n",
"zachry industral inc 1\n",
"zakheim and lavrar 1\n",
"zashko inc. 1\n",
"zeno office solutions 1\n",
"zion lutheran school 1\n",
"zoll medical corp 1\n",
"zozaya officiating 1\n",
"Name: emp_title, dtype: int64\n",
"-----------\n",
"home_ownership\n",
"MORTGAGE 18959\n",
"NONE 8\n",
"OTHER 136\n",
"OWN 3251\n",
"RENT 20181\n",
"Name: home_ownership, dtype: int64\n",
"-----------\n",
"verification_status\n",
"Not Verified 18758\n",
"Source Verified 10306\n",
"Verified 13471\n",
"Name: verification_status, dtype: int64\n",
"-----------\n",
"issue_d\n",
"Apr-2008 259\n",
"Apr-2009 333\n",
"Apr-2010 912\n",
"Apr-2011 1563\n",
"Aug-2007 74\n",
"Aug-2008 100\n",
"Aug-2009 446\n",
"Aug-2010 1175\n",
"Aug-2011 1934\n",
"Dec-2007 172\n",
"Dec-2008 253\n",
"Dec-2009 658\n",
"Dec-2010 1335\n",
"Dec-2011 2267\n",
"Feb-2008 306\n",
"Feb-2009 302\n",
"Feb-2010 682\n",
"Feb-2011 1298\n",
"Jan-2008 305\n",
"Jan-2009 269\n",
"Jan-2010 662\n",
"Jan-2011 1380\n",
"Jul-2007 63\n",
"Jul-2008 141\n",
"Jul-2009 411\n",
"Jul-2010 1204\n",
"Jul-2011 1875\n",
"Jun-2007 24\n",
"Jun-2008 124\n",
"Jun-2009 406\n",
"Jun-2010 1105\n",
"Jun-2011 1835\n",
"Mar-2008 402\n",
"Mar-2009 324\n",
"Mar-2010 828\n",
"Mar-2011 1448\n",
"May-2008 115\n",
"May-2009 359\n",
"May-2010 989\n",
"May-2011 1704\n",
"Nov-2007 112\n",
"Nov-2008 209\n",
"Nov-2009 662\n",
"Nov-2010 1224\n",
"Nov-2011 2232\n",
"Oct-2007 105\n",
"Oct-2008 122\n",
"Oct-2009 604\n",
"Oct-2010 1232\n",
"Oct-2011 2118\n",
"Sep-2007 53\n",
"Sep-2008 57\n",
"Sep-2009 507\n",
"Sep-2010 1189\n",
"Sep-2011 2067\n",
"Name: issue_d, dtype: int64\n",
"-----------\n",
"purpose\n",
"car 1615\n",
"credit_card 5477\n",
"debt_consolidation 19776\n",
"educational 422\n",
"home_improvement 3199\n",
"house 426\n",
"major_purchase 2311\n",
"medical 753\n",
"moving 629\n",
"other 4425\n",
"renewable_energy 106\n",
"small_business 1992\n",
"vacation 400\n",
"wedding 1004\n",
"Name: purpose, dtype: int64\n",
"-----------\n",
"earliest_cr_line\n",
"Apr-1964 3\n",
"Apr-1966 1\n",
"Apr-1967 4\n",
"Apr-1968 1\n",
"Apr-1969 1\n",
"Apr-1970 3\n",
"Apr-1971 6\n",
"Apr-1972 2\n",
"Apr-1973 7\n",
"Apr-1974 6\n",
"Apr-1975 11\n",
"Apr-1976 7\n",
"Apr-1977 10\n",
"Apr-1978 14\n",
"Apr-1979 15\n",
"Apr-1980 17\n",
"Apr-1981 15\n",
"Apr-1982 23\n",
"Apr-1983 29\n",
"Apr-1984 27\n",
"Apr-1985 31\n",
"Apr-1986 29\n",
"Apr-1987 48\n",
"Apr-1988 55\n",
"Apr-1989 64\n",
"Apr-1990 79\n",
"Apr-1991 77\n",
"Apr-1992 90\n",
"Apr-1993 100\n",
"Apr-1994 134\n",
" ... \n",
"Sep-1979 20\n",
"Sep-1980 18\n",
"Sep-1981 28\n",
"Sep-1982 16\n",
"Sep-1983 33\n",
"Sep-1984 46\n",
"Sep-1985 41\n",
"Sep-1986 59\n",
"Sep-1987 59\n",
"Sep-1988 63\n",
"Sep-1989 83\n",
"Sep-1990 93\n",
"Sep-1991 70\n",
"Sep-1992 72\n",
"Sep-1993 124\n",
"Sep-1994 119\n",
"Sep-1995 181\n",
"Sep-1996 187\n",
"Sep-1997 196\n",
"Sep-1998 267\n",
"Sep-1999 277\n",
"Sep-2000 325\n",
"Sep-2001 282\n",
"Sep-2002 251\n",
"Sep-2003 220\n",
"Sep-2004 221\n",
"Sep-2005 162\n",
"Sep-2006 150\n",
"Sep-2007 63\n",
"Sep-2008 8\n",
"Name: earliest_cr_line, dtype: int64\n",
"-----------\n",
"revol_util\n",
"0% 1070\n",
"0.01% 1\n",
"0.03% 1\n",
"0.04% 1\n",
"0.05% 1\n",
"0.1% 61\n",
"0.12% 1\n",
"0.16% 1\n",
"0.2% 64\n",
"0.3% 43\n",
"0.4% 45\n",
"0.46% 1\n",
"0.49% 1\n",
"0.5% 49\n",
"0.54% 1\n",
"0.6% 39\n",
"0.7% 47\n",
"0.75% 1\n",
"0.8% 43\n",
"0.83% 1\n",
"0.86% 1\n",
"0.9% 41\n",
"1% 51\n",
"1.1% 39\n",
"1.2% 37\n",
"1.3% 36\n",
"1.4% 32\n",
"1.5% 33\n",
"1.6% 24\n",
"1.7% 30\n",
" ... \n",
"97% 31\n",
"97.1% 39\n",
"97.2% 32\n",
"97.3% 36\n",
"97.4% 33\n",
"97.5% 35\n",
"97.6% 35\n",
"97.7% 45\n",
"97.8% 38\n",
"97.9% 27\n",
"98% 28\n",
"98.1% 25\n",
"98.2% 25\n",
"98.3% 36\n",
"98.4% 31\n",
"98.5% 29\n",
"98.6% 36\n",
"98.7% 27\n",
"98.8% 38\n",
"98.9% 20\n",
"99% 35\n",
"99.1% 33\n",
"99.2% 22\n",
"99.3% 32\n",
"99.4% 24\n",
"99.5% 24\n",
"99.6% 27\n",
"99.7% 32\n",
"99.8% 25\n",
"99.9% 29\n",
"Name: revol_util, dtype: int64\n",
"-----------\n",
"last_pymnt_d\n",
"Apr-2008 23\n",
"Apr-2009 72\n",
"Apr-2010 145\n",
"Apr-2011 519\n",
"Apr-2012 781\n",
"Apr-2013 890\n",
"Apr-2014 682\n",
"Apr-2015 141\n",
"Apr-2016 178\n",
"Aug-2008 31\n",
"Aug-2009 66\n",
"Aug-2010 198\n",
"Aug-2011 479\n",
"Aug-2012 868\n",
"Aug-2013 744\n",
"Aug-2014 836\n",
"Aug-2015 217\n",
"Dec-2007 2\n",
"Dec-2008 31\n",
"Dec-2009 116\n",
"Dec-2010 332\n",
"Dec-2011 585\n",
"Dec-2012 732\n",
"Dec-2013 799\n",
"Dec-2014 949\n",
"Dec-2015 185\n",
"Feb-2008 8\n",
"Feb-2009 65\n",
"Feb-2010 148\n",
"Feb-2011 437\n",
" ... \n",
"May-2011 448\n",
"May-2012 773\n",
"May-2013 943\n",
"May-2014 686\n",
"May-2015 151\n",
"May-2016 203\n",
"Nov-2008 31\n",
"Nov-2009 80\n",
"Nov-2010 258\n",
"Nov-2011 487\n",
"Nov-2012 782\n",
"Nov-2013 701\n",
"Nov-2014 593\n",
"Nov-2015 231\n",
"Oct-2008 55\n",
"Oct-2009 85\n",
"Oct-2010 292\n",
"Oct-2011 482\n",
"Oct-2012 853\n",
"Oct-2013 712\n",
"Oct-2014 813\n",
"Oct-2015 200\n",
"Sep-2008 40\n",
"Sep-2009 55\n",
"Sep-2010 215\n",
"Sep-2011 491\n",
"Sep-2012 802\n",
"Sep-2013 712\n",
"Sep-2014 694\n",
"Sep-2015 211\n",
"Name: last_pymnt_d, dtype: int64\n",
"-----------\n",
"last_credit_pull_d\n",
"Apr-2009 24\n",
"Apr-2010 77\n",
"Apr-2011 177\n",
"Apr-2012 326\n",
"Apr-2013 445\n",
"Apr-2014 507\n",
"Apr-2015 404\n",
"Apr-2016 912\n",
"Aug-2007 17\n",
"Aug-2008 15\n",
"Aug-2009 32\n",
"Aug-2010 68\n",
"Aug-2011 290\n",
"Aug-2012 391\n",
"Aug-2013 375\n",
"Aug-2014 528\n",
"Aug-2015 435\n",
"Dec-2007 3\n",
"Dec-2008 7\n",
"Dec-2009 73\n",
"Dec-2010 156\n",
"Dec-2011 268\n",
"Dec-2012 391\n",
"Dec-2013 431\n",
"Dec-2014 567\n",
"Dec-2015 656\n",
"Feb-2008 3\n",
"Feb-2009 20\n",
"Feb-2010 87\n",
"Feb-2011 199\n",
" ... \n",
"May-2013 470\n",
"May-2014 477\n",
"May-2015 433\n",
"May-2016 766\n",
"Nov-2007 3\n",
"Nov-2009 50\n",
"Nov-2010 126\n",
"Nov-2011 221\n",
"Nov-2012 432\n",
"Nov-2013 491\n",
"Nov-2014 500\n",
"Nov-2015 516\n",
"Oct-2007 3\n",
"Oct-2008 13\n",
"Oct-2009 51\n",
"Oct-2010 120\n",
"Oct-2011 162\n",
"Oct-2012 351\n",
"Oct-2013 429\n",
"Oct-2014 529\n",
"Oct-2015 513\n",
"Sep-2007 5\n",
"Sep-2008 9\n",
"Sep-2009 21\n",
"Sep-2010 119\n",
"Sep-2011 175\n",
"Sep-2012 414\n",
"Sep-2013 408\n",
"Sep-2014 564\n",
"Sep-2015 531\n",
"Name: last_credit_pull_d, dtype: int64\n",
"-----------\n"
]
}
],
"source": [
"for col in useful_obj_cols:\n",
" print(col)\n",
" print(combined_vcs[col])\n",
" print(\"-----------\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Convert to category"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [],
"source": [
"convert_col_dtypes = {\n",
" \"sub_grade\": \"category\", \"home_ownership\": \"category\", \n",
" \"verification_status\": \"category\", \"purpose\": \"category\"\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Convert `term` and `revol_util` to numerical by data cleaning.\n",
"### Convert `issue_d`, `earliest_cr_line`, `last_pymnt_d`, and `last_credit_pull_d` to datetime."
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>term</th>\n",
" <th>sub_grade</th>\n",
" <th>emp_title</th>\n",
" <th>home_ownership</th>\n",
" <th>verification_status</th>\n",
" <th>issue_d</th>\n",
" <th>purpose</th>\n",
" <th>earliest_cr_line</th>\n",
" <th>revol_util</th>\n",
" <th>last_pymnt_d</th>\n",
" <th>last_credit_pull_d</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>42000</th>\n",
" <td>36 months</td>\n",
" <td>C2</td>\n",
" <td>Best Buy</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Jul-2000</td>\n",
" <td>100.7%</td>\n",
" <td>Feb-2011</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42001</th>\n",
" <td>36 months</td>\n",
" <td>G2</td>\n",
" <td>CVS PHARMACY</td>\n",
" <td>OWN</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Mar-1989</td>\n",
" <td>51.9%</td>\n",
" <td>Nov-2008</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42002</th>\n",
" <td>36 months</td>\n",
" <td>E4</td>\n",
" <td>General Motors</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Dec-1998</td>\n",
" <td>80.7%</td>\n",
" <td>Feb-2011</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42003</th>\n",
" <td>36 months</td>\n",
" <td>G4</td>\n",
" <td>usa medical center</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Jul-1995</td>\n",
" <td>57.2%</td>\n",
" <td>Feb-2011</td>\n",
" <td>Jun-2011</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42004</th>\n",
" <td>36 months</td>\n",
" <td>B3</td>\n",
" <td>InvestSource Inc</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Sep-2005</td>\n",
" <td>74%</td>\n",
" <td>Mar-2010</td>\n",
" <td>Aug-2010</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42005</th>\n",
" <td>36 months</td>\n",
" <td>D1</td>\n",
" <td>kaiser</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Nov-2000</td>\n",
" <td>37.6%</td>\n",
" <td>Mar-2010</td>\n",
" <td>Apr-2010</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42006</th>\n",
" <td>36 months</td>\n",
" <td>C1</td>\n",
" <td>Panoramic Software</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Dec-1995</td>\n",
" <td>56%</td>\n",
" <td>Apr-2010</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42007</th>\n",
" <td>36 months</td>\n",
" <td>D5</td>\n",
" <td>AT&T Inc.</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>small_business</td>\n",
" <td>Feb-2000</td>\n",
" <td>49.7%</td>\n",
" <td>Feb-2009</td>\n",
" <td>Oct-2009</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42008</th>\n",
" <td>36 months</td>\n",
" <td>D4</td>\n",
" <td>D&Y</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Oct-1992</td>\n",
" <td>71.5%</td>\n",
" <td>Jan-2010</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42009</th>\n",
" <td>36 months</td>\n",
" <td>D5</td>\n",
" <td>Frederick's of Hollywood</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Oct-1994</td>\n",
" <td>49%</td>\n",
" <td>Jul-2008</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42010</th>\n",
" <td>36 months</td>\n",
" <td>F2</td>\n",
" <td>Yuma Nursing Home Ctr</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Oct-1993</td>\n",
" <td>18.1%</td>\n",
" <td>Feb-2011</td>\n",
" <td>Nov-2014</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42011</th>\n",
" <td>36 months</td>\n",
" <td>E5</td>\n",
" <td>Akiva Incorporated</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>small_business</td>\n",
" <td>Jan-2006</td>\n",
" <td>30.8%</td>\n",
" <td>Jan-2009</td>\n",
" <td>Aug-2009</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42012</th>\n",
" <td>36 months</td>\n",
" <td>G4</td>\n",
" <td>Citrix Online</td>\n",
" <td>OWN</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Sep-1998</td>\n",
" <td>98.1%</td>\n",
" <td>Feb-2011</td>\n",
" <td>Mar-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42013</th>\n",
" <td>36 months</td>\n",
" <td>G3</td>\n",
" <td>Virginia College Online</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>other</td>\n",
" <td>Dec-1990</td>\n",
" <td>78.4%</td>\n",
" <td>Oct-2008</td>\n",
" <td>Sep-2008</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42014</th>\n",
" <td>36 months</td>\n",
" <td>E2</td>\n",
" <td>Zone 7 Water Agency</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Nov-1998</td>\n",
" <td>94.3%</td>\n",
" <td>May-2009</td>\n",
" <td>May-2013</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42015</th>\n",
" <td>36 months</td>\n",
" <td>G5</td>\n",
" <td>Integrity Appraisals</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Feb-1997</td>\n",
" <td>78.7%</td>\n",
" <td>Nov-2008</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42016</th>\n",
" <td>36 months</td>\n",
" <td>G4</td>\n",
" <td>Stordok Document Destruction/Moreno Roofing Co.</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>credit_card</td>\n",
" <td>Jun-1982</td>\n",
" <td>0%</td>\n",
" <td>Feb-2011</td>\n",
" <td>Dec-2014</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42017</th>\n",
" <td>36 months</td>\n",
" <td>E4</td>\n",
" <td>Aladdin Capital Management</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>credit_card</td>\n",
" <td>Nov-1999</td>\n",
" <td>65.2%</td>\n",
" <td>Nov-2009</td>\n",
" <td>Nov-2009</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42018</th>\n",
" <td>36 months</td>\n",
" <td>D4</td>\n",
" <td>Cayuga Medical Center</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Nov-1994</td>\n",
" <td>58.9%</td>\n",
" <td>Feb-2011</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42019</th>\n",
" <td>36 months</td>\n",
" <td>E1</td>\n",
" <td>North Sound PT</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jan-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Feb-2000</td>\n",
" <td>94%</td>\n",
" <td>Oct-2009</td>\n",
" <td>Mar-2010</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42020</th>\n",
" <td>36 months</td>\n",
" <td>C3</td>\n",
" <td>UHS</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jan-2008</td>\n",
" <td>medical</td>\n",
" <td>Oct-1998</td>\n",
" <td>83.1%</td>\n",
" <td>Feb-2011</td>\n",
" <td>Feb-2011</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42021</th>\n",
" <td>36 months</td>\n",
" <td>D4</td>\n",
" <td>Adecco</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Nov-2002</td>\n",
" <td>60.7%</td>\n",
" <td>Aug-2009</td>\n",
" <td>Jan-2011</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42022</th>\n",
" <td>36 months</td>\n",
" <td>E5</td>\n",
" <td>Greatwide logistics Services</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>home_improvement</td>\n",
" <td>Oct-1994</td>\n",
" <td>49.9%</td>\n",
" <td>Sep-2009</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42023</th>\n",
" <td>36 months</td>\n",
" <td>D4</td>\n",
" <td>ASU University</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>credit_card</td>\n",
" <td>Sep-2000</td>\n",
" <td>59.9%</td>\n",
" <td>Feb-2011</td>\n",
" <td>Feb-2011</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42024</th>\n",
" <td>36 months</td>\n",
" <td>B5</td>\n",
" <td>Wells Fargo</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Nov-1995</td>\n",
" <td>32.6%</td>\n",
" <td>Feb-2011</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42025</th>\n",
" <td>36 months</td>\n",
" <td>D5</td>\n",
" <td>lakeside memorial hospital</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Jan-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Nov-1997</td>\n",
" <td>77.4%</td>\n",
" <td>Jan-2011</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42026</th>\n",
" <td>36 months</td>\n",
" <td>B5</td>\n",
" <td>Controller Group</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>small_business</td>\n",
" <td>Mar-1998</td>\n",
" <td>46.5%</td>\n",
" <td>Oct-2008</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42027</th>\n",
" <td>36 months</td>\n",
" <td>F1</td>\n",
" <td>David Johnston Equestrian Training Lic.#29026</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>small_business</td>\n",
" <td>Nov-2000</td>\n",
" <td>18.1%</td>\n",
" <td>Feb-2011</td>\n",
" <td>Feb-2011</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42028</th>\n",
" <td>36 months</td>\n",
" <td>E3</td>\n",
" <td>Lockheed Martin Corp.</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Dec-1983</td>\n",
" <td>45.9%</td>\n",
" <td>Feb-2011</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42029</th>\n",
" <td>36 months</td>\n",
" <td>G4</td>\n",
" <td>Self</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Feb-2008</td>\n",
" <td>small_business</td>\n",
" <td>Apr-1994</td>\n",
" <td>92.3%</td>\n",
" <td>Apr-2008</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42508</th>\n",
" <td>36 months</td>\n",
" <td>D5</td>\n",
" <td>SUNY- ESF</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jul-2007</td>\n",
" <td>credit_card</td>\n",
" <td>Dec-2004</td>\n",
" <td>49.3%</td>\n",
" <td>Jul-2010</td>\n",
" <td>Sep-2012</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42509</th>\n",
" <td>36 months</td>\n",
" <td>D5</td>\n",
" <td>Clinton Shop Rite of Hunterdon County</td>\n",
" <td>OWN</td>\n",
" <td>Not Verified</td>\n",
" <td>Jul-2007</td>\n",
" <td>car</td>\n",
" <td>Dec-2006</td>\n",
" <td>NaN</td>\n",
" <td>Feb-2010</td>\n",
" <td>Jul-2013</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42510</th>\n",
" <td>36 months</td>\n",
" <td>B4</td>\n",
" <td>Scheduall</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Jul-2007</td>\n",
" <td>debt_consolidation</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Feb-2008</td>\n",
" <td>May-2007</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42511</th>\n",
" <td>36 months</td>\n",
" <td>E5</td>\n",
" <td>GA-PCOM</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jul-2007</td>\n",
" <td>educational</td>\n",
" <td>Sep-1999</td>\n",
" <td>85%</td>\n",
" <td>Aug-2010</td>\n",
" <td>Aug-2010</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42512</th>\n",
" <td>36 months</td>\n",
" <td>A1</td>\n",
" <td>Tzigane Inc</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Jul-2007</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Mar-1984</td>\n",
" <td>2.2%</td>\n",
" <td>Jul-2010</td>\n",
" <td>Jun-2010</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42513</th>\n",
" <td>36 months</td>\n",
" <td>C2</td>\n",
" <td>Yale University</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Jan-1996</td>\n",
" <td>66%</td>\n",
" <td>Jun-2010</td>\n",
" <td>Oct-2014</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42514</th>\n",
" <td>36 months</td>\n",
" <td>B4</td>\n",
" <td>Brick Township board of education</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>debt_consolidation</td>\n",
" <td>Jul-2004</td>\n",
" <td>63.5%</td>\n",
" <td>Jun-2010</td>\n",
" <td>Feb-2015</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42515</th>\n",
" <td>36 months</td>\n",
" <td>B2</td>\n",
" <td>Classic Components</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jul-2010</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42516</th>\n",
" <td>36 months</td>\n",
" <td>C4</td>\n",
" <td>Compensation Solutions</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Apr-2010</td>\n",
" <td>Apr-2010</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42517</th>\n",
" <td>36 months</td>\n",
" <td>C3</td>\n",
" <td>Stanford University Libraries, LOCKSS Project</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jul-2010</td>\n",
" <td>Sep-2014</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42518</th>\n",
" <td>36 months</td>\n",
" <td>E3</td>\n",
" <td>Macy's</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jun-2010</td>\n",
" <td>Jun-2010</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42519</th>\n",
" <td>36 months</td>\n",
" <td>A3</td>\n",
" <td>Diamond Management and Technology Consultants</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Aug-2009</td>\n",
" <td>Aug-2009</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42520</th>\n",
" <td>36 months</td>\n",
" <td>A2</td>\n",
" <td>U.S. Bank</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jan-2008</td>\n",
" <td>Jun-2007</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42521</th>\n",
" <td>36 months</td>\n",
" <td>A2</td>\n",
" <td>NC</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jan-2008</td>\n",
" <td>Jun-2007</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42522</th>\n",
" <td>36 months</td>\n",
" <td>A5</td>\n",
" <td>College Pro Painters</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Mar-2010</td>\n",
" <td>Mar-2010</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42523</th>\n",
" <td>36 months</td>\n",
" <td>A1</td>\n",
" <td>Mana Products</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jun-2010</td>\n",
" <td>Apr-2014</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42524</th>\n",
" <td>36 months</td>\n",
" <td>C4</td>\n",
" <td>Apto Solutions</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jul-2010</td>\n",
" <td>Jun-2010</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42525</th>\n",
" <td>36 months</td>\n",
" <td>D3</td>\n",
" <td>Infinitely law group</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>May-2008</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42526</th>\n",
" <td>36 months</td>\n",
" <td>C4</td>\n",
" <td>Town of Plainville</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Aug-2008</td>\n",
" <td>Jun-2007</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42527</th>\n",
" <td>36 months</td>\n",
" <td>B2</td>\n",
" <td>Tanks Tavern</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jul-2010</td>\n",
" <td>Jun-2010</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42528</th>\n",
" <td>36 months</td>\n",
" <td>B3</td>\n",
" <td>NaN</td>\n",
" <td>OWN</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jun-2010</td>\n",
" <td>May-2007</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42529</th>\n",
" <td>36 months</td>\n",
" <td>B5</td>\n",
" <td>NaN</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jul-2010</td>\n",
" <td>Jul-2010</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42530</th>\n",
" <td>36 months</td>\n",
" <td>B4</td>\n",
" <td>Air Force</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>May-2008</td>\n",
" <td>Jun-2016</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42531</th>\n",
" <td>36 months</td>\n",
" <td>C1</td>\n",
" <td>NaN</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Mar-2008</td>\n",
" <td>Feb-2013</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42532</th>\n",
" <td>36 months</td>\n",
" <td>B4</td>\n",
" <td>Halping hands company inc.</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jun-2010</td>\n",
" <td>Sep-2014</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42533</th>\n",
" <td>36 months</td>\n",
" <td>B3</td>\n",
" <td>NaN</td>\n",
" <td>RENT</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jun-2010</td>\n",
" <td>May-2007</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42534</th>\n",
" <td>36 months</td>\n",
" <td>A5</td>\n",
" <td>NaN</td>\n",
" <td>NONE</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jun-2010</td>\n",
" <td>Aug-2007</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42535</th>\n",
" <td>36 months</td>\n",
" <td>A3</td>\n",
" <td>Homemaker</td>\n",
" <td>MORTGAGE</td>\n",
" <td>Not Verified</td>\n",
" <td>Jun-2007</td>\n",
" <td>other</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>Jun-2010</td>\n",
" <td>Feb-2015</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42536</th>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42537</th>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>538 rows × 11 columns</p>\n",
"</div>"
],
"text/plain": [
" term sub_grade emp_title \\\n",
"42000 36 months C2 Best Buy \n",
"42001 36 months G2 CVS PHARMACY \n",
"42002 36 months E4 General Motors \n",
"42003 36 months G4 usa medical center \n",
"42004 36 months B3 InvestSource Inc \n",
"42005 36 months D1 kaiser \n",
"42006 36 months C1 Panoramic Software \n",
"42007 36 months D5 AT&T Inc. \n",
"42008 36 months D4 D&Y \n",
"42009 36 months D5 Frederick's of Hollywood \n",
"42010 36 months F2 Yuma Nursing Home Ctr \n",
"42011 36 months E5 Akiva Incorporated \n",
"42012 36 months G4 Citrix Online \n",
"42013 36 months G3 Virginia College Online \n",
"42014 36 months E2 Zone 7 Water Agency \n",
"42015 36 months G5 Integrity Appraisals \n",
"42016 36 months G4 Stordok Document Destruction/Moreno Roofing Co. \n",
"42017 36 months E4 Aladdin Capital Management \n",
"42018 36 months D4 Cayuga Medical Center \n",
"42019 36 months E1 North Sound PT \n",
"42020 36 months C3 UHS \n",
"42021 36 months D4 Adecco \n",
"42022 36 months E5 Greatwide logistics Services \n",
"42023 36 months D4 ASU University \n",
"42024 36 months B5 Wells Fargo \n",
"42025 36 months D5 lakeside memorial hospital \n",
"42026 36 months B5 Controller Group \n",
"42027 36 months F1 David Johnston Equestrian Training Lic.#29026 \n",
"42028 36 months E3 Lockheed Martin Corp. \n",
"42029 36 months G4 Self \n",
"... ... ... ... \n",
"42508 36 months D5 SUNY- ESF \n",
"42509 36 months D5 Clinton Shop Rite of Hunterdon County \n",
"42510 36 months B4 Scheduall \n",
"42511 36 months E5 GA-PCOM \n",
"42512 36 months A1 Tzigane Inc \n",
"42513 36 months C2 Yale University \n",
"42514 36 months B4 Brick Township board of education \n",
"42515 36 months B2 Classic Components \n",
"42516 36 months C4 Compensation Solutions \n",
"42517 36 months C3 Stanford University Libraries, LOCKSS Project \n",
"42518 36 months E3 Macy's \n",
"42519 36 months A3 Diamond Management and Technology Consultants \n",
"42520 36 months A2 U.S. Bank \n",
"42521 36 months A2 NC \n",
"42522 36 months A5 College Pro Painters \n",
"42523 36 months A1 Mana Products \n",
"42524 36 months C4 Apto Solutions \n",
"42525 36 months D3 Infinitely law group \n",
"42526 36 months C4 Town of Plainville \n",
"42527 36 months B2 Tanks Tavern \n",
"42528 36 months B3 NaN \n",
"42529 36 months B5 NaN \n",
"42530 36 months B4 Air Force \n",
"42531 36 months C1 NaN \n",
"42532 36 months B4 Halping hands company inc. \n",
"42533 36 months B3 NaN \n",
"42534 36 months A5 NaN \n",
"42535 36 months A3 Homemaker \n",
"42536 NaN NaN NaN \n",
"42537 NaN NaN NaN \n",
"\n",
" home_ownership verification_status issue_d purpose \\\n",
"42000 RENT Not Verified Feb-2008 debt_consolidation \n",
"42001 OWN Not Verified Feb-2008 debt_consolidation \n",
"42002 RENT Not Verified Feb-2008 debt_consolidation \n",
"42003 RENT Not Verified Feb-2008 debt_consolidation \n",
"42004 RENT Not Verified Feb-2008 debt_consolidation \n",
"42005 MORTGAGE Not Verified Feb-2008 debt_consolidation \n",
"42006 MORTGAGE Not Verified Feb-2008 debt_consolidation \n",
"42007 RENT Not Verified Feb-2008 small_business \n",
"42008 RENT Not Verified Feb-2008 debt_consolidation \n",
"42009 RENT Not Verified Feb-2008 debt_consolidation \n",
"42010 RENT Not Verified Feb-2008 debt_consolidation \n",
"42011 RENT Not Verified Feb-2008 small_business \n",
"42012 OWN Not Verified Feb-2008 debt_consolidation \n",
"42013 MORTGAGE Not Verified Feb-2008 other \n",
"42014 RENT Not Verified Feb-2008 debt_consolidation \n",
"42015 MORTGAGE Not Verified Feb-2008 debt_consolidation \n",
"42016 MORTGAGE Not Verified Feb-2008 credit_card \n",
"42017 RENT Not Verified Feb-2008 credit_card \n",
"42018 RENT Not Verified Feb-2008 debt_consolidation \n",
"42019 RENT Not Verified Jan-2008 debt_consolidation \n",
"42020 RENT Not Verified Jan-2008 medical \n",
"42021 RENT Not Verified Feb-2008 debt_consolidation \n",
"42022 MORTGAGE Not Verified Feb-2008 home_improvement \n",
"42023 RENT Not Verified Feb-2008 credit_card \n",
"42024 RENT Not Verified Feb-2008 debt_consolidation \n",
"42025 MORTGAGE Not Verified Jan-2008 debt_consolidation \n",
"42026 RENT Not Verified Feb-2008 small_business \n",
"42027 RENT Not Verified Feb-2008 small_business \n",
"42028 MORTGAGE Not Verified Feb-2008 debt_consolidation \n",
"42029 MORTGAGE Not Verified Feb-2008 small_business \n",
"... ... ... ... ... \n",
"42508 RENT Not Verified Jul-2007 credit_card \n",
"42509 OWN Not Verified Jul-2007 car \n",
"42510 MORTGAGE Not Verified Jul-2007 debt_consolidation \n",
"42511 RENT Not Verified Jul-2007 educational \n",
"42512 MORTGAGE Not Verified Jul-2007 debt_consolidation \n",
"42513 RENT Not Verified Jun-2007 debt_consolidation \n",
"42514 MORTGAGE Not Verified Jun-2007 debt_consolidation \n",
"42515 RENT Not Verified Jun-2007 other \n",
"42516 RENT Not Verified Jun-2007 other \n",
"42517 RENT Not Verified Jun-2007 other \n",
"42518 RENT Not Verified Jun-2007 other \n",
"42519 MORTGAGE Not Verified Jun-2007 other \n",
"42520 MORTGAGE Not Verified Jun-2007 other \n",
"42521 MORTGAGE Not Verified Jun-2007 other \n",
"42522 MORTGAGE Not Verified Jun-2007 other \n",
"42523 RENT Not Verified Jun-2007 other \n",
"42524 RENT Not Verified Jun-2007 other \n",
"42525 RENT Not Verified Jun-2007 other \n",
"42526 RENT Not Verified Jun-2007 other \n",
"42527 RENT Not Verified Jun-2007 other \n",
"42528 OWN Not Verified Jun-2007 other \n",
"42529 RENT Not Verified Jun-2007 other \n",
"42530 RENT Not Verified Jun-2007 other \n",
"42531 RENT Not Verified Jun-2007 other \n",
"42532 RENT Not Verified Jun-2007 other \n",
"42533 RENT Not Verified Jun-2007 other \n",
"42534 NONE Not Verified Jun-2007 other \n",
"42535 MORTGAGE Not Verified Jun-2007 other \n",
"42536 NaN NaN NaN NaN \n",
"42537 NaN NaN NaN NaN \n",
"\n",
" earliest_cr_line revol_util last_pymnt_d last_credit_pull_d \n",
"42000 Jul-2000 100.7% Feb-2011 Jun-2016 \n",
"42001 Mar-1989 51.9% Nov-2008 Jun-2016 \n",
"42002 Dec-1998 80.7% Feb-2011 Jun-2016 \n",
"42003 Jul-1995 57.2% Feb-2011 Jun-2011 \n",
"42004 Sep-2005 74% Mar-2010 Aug-2010 \n",
"42005 Nov-2000 37.6% Mar-2010 Apr-2010 \n",
"42006 Dec-1995 56% Apr-2010 Jun-2016 \n",
"42007 Feb-2000 49.7% Feb-2009 Oct-2009 \n",
"42008 Oct-1992 71.5% Jan-2010 Jun-2016 \n",
"42009 Oct-1994 49% Jul-2008 Jun-2016 \n",
"42010 Oct-1993 18.1% Feb-2011 Nov-2014 \n",
"42011 Jan-2006 30.8% Jan-2009 Aug-2009 \n",
"42012 Sep-1998 98.1% Feb-2011 Mar-2016 \n",
"42013 Dec-1990 78.4% Oct-2008 Sep-2008 \n",
"42014 Nov-1998 94.3% May-2009 May-2013 \n",
"42015 Feb-1997 78.7% Nov-2008 Jun-2016 \n",
"42016 Jun-1982 0% Feb-2011 Dec-2014 \n",
"42017 Nov-1999 65.2% Nov-2009 Nov-2009 \n",
"42018 Nov-1994 58.9% Feb-2011 Jun-2016 \n",
"42019 Feb-2000 94% Oct-2009 Mar-2010 \n",
"42020 Oct-1998 83.1% Feb-2011 Feb-2011 \n",
"42021 Nov-2002 60.7% Aug-2009 Jan-2011 \n",
"42022 Oct-1994 49.9% Sep-2009 Jun-2016 \n",
"42023 Sep-2000 59.9% Feb-2011 Feb-2011 \n",
"42024 Nov-1995 32.6% Feb-2011 Jun-2016 \n",
"42025 Nov-1997 77.4% Jan-2011 Jun-2016 \n",
"42026 Mar-1998 46.5% Oct-2008 Jun-2016 \n",
"42027 Nov-2000 18.1% Feb-2011 Feb-2011 \n",
"42028 Dec-1983 45.9% Feb-2011 Jun-2016 \n",
"42029 Apr-1994 92.3% Apr-2008 Jun-2016 \n",
"... ... ... ... ... \n",
"42508 Dec-2004 49.3% Jul-2010 Sep-2012 \n",
"42509 Dec-2006 NaN Feb-2010 Jul-2013 \n",
"42510 NaN NaN Feb-2008 May-2007 \n",
"42511 Sep-1999 85% Aug-2010 Aug-2010 \n",
"42512 Mar-1984 2.2% Jul-2010 Jun-2010 \n",
"42513 Jan-1996 66% Jun-2010 Oct-2014 \n",
"42514 Jul-2004 63.5% Jun-2010 Feb-2015 \n",
"42515 NaN NaN Jul-2010 Jun-2016 \n",
"42516 NaN NaN Apr-2010 Apr-2010 \n",
"42517 NaN NaN Jul-2010 Sep-2014 \n",
"42518 NaN NaN Jun-2010 Jun-2010 \n",
"42519 NaN NaN Aug-2009 Aug-2009 \n",
"42520 NaN NaN Jan-2008 Jun-2007 \n",
"42521 NaN NaN Jan-2008 Jun-2007 \n",
"42522 NaN NaN Mar-2010 Mar-2010 \n",
"42523 NaN NaN Jun-2010 Apr-2014 \n",
"42524 NaN NaN Jul-2010 Jun-2010 \n",
"42525 NaN NaN May-2008 Jun-2016 \n",
"42526 NaN NaN Aug-2008 Jun-2007 \n",
"42527 NaN NaN Jul-2010 Jun-2010 \n",
"42528 NaN NaN Jun-2010 May-2007 \n",
"42529 NaN NaN Jul-2010 Jul-2010 \n",
"42530 NaN NaN May-2008 Jun-2016 \n",
"42531 NaN NaN Mar-2008 Feb-2013 \n",
"42532 NaN NaN Jun-2010 Sep-2014 \n",
"42533 NaN NaN Jun-2010 May-2007 \n",
"42534 NaN NaN Jun-2010 Aug-2007 \n",
"42535 NaN NaN Jun-2010 Feb-2015 \n",
"42536 NaN NaN NaN NaN \n",
"42537 NaN NaN NaN NaN \n",
"\n",
"[538 rows x 11 columns]"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"chunk[useful_obj_cols]"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"id object\n",
"member_id float64\n",
"loan_amnt float64\n",
"funded_amnt float64\n",
"funded_amnt_inv float64\n",
"term float64\n",
"int_rate object\n",
"installment float64\n",
"grade object\n",
"sub_grade category\n",
"emp_title object\n",
"emp_length object\n",
"home_ownership category\n",
"annual_inc float64\n",
"verification_status category\n",
"issue_d datetime64[ns]\n",
"loan_status object\n",
"pymnt_plan object\n",
"purpose category\n",
"title object\n",
"zip_code object\n",
"addr_state object\n",
"dti float64\n",
"delinq_2yrs float64\n",
"earliest_cr_line datetime64[ns]\n",
"inq_last_6mths float64\n",
"open_acc float64\n",
"pub_rec float64\n",
"revol_bal float64\n",
"revol_util float64\n",
"total_acc float64\n",
"initial_list_status object\n",
"out_prncp float64\n",
"out_prncp_inv float64\n",
"total_pymnt float64\n",
"total_pymnt_inv float64\n",
"total_rec_prncp float64\n",
"total_rec_int float64\n",
"total_rec_late_fee float64\n",
"recoveries float64\n",
"collection_recovery_fee float64\n",
"last_pymnt_d datetime64[ns]\n",
"last_pymnt_amnt float64\n",
"last_credit_pull_d datetime64[ns]\n",
"collections_12_mths_ex_med float64\n",
"policy_code float64\n",
"application_type object\n",
"acc_now_delinq float64\n",
"chargeoff_within_12_mths float64\n",
"delinq_amnt float64\n",
"pub_rec_bankruptcies float64\n",
"tax_liens float64\n",
"dtype: object"
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"chunk_iter = pd.read_csv('loans_2007.csv', chunksize=3000, dtype=convert_col_dtypes, parse_dates=[\"issue_d\", \"earliest_cr_line\", \"last_pymnt_d\", \"last_credit_pull_d\"])\n",
"\n",
"for chunk in chunk_iter:\n",
" term_cleaned = chunk['term'].str.lstrip(\" \").str.rstrip(\" months\")\n",
" revol_cleaned = chunk['revol_util'].str.rstrip(\"%\")\n",
" chunk['term'] = pd.to_numeric(term_cleaned)\n",
" chunk['revol_util'] = pd.to_numeric(revol_cleaned)\n",
" \n",
"chunk.dtypes"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"{'acc_now_delinq': 32,\n",
" 'annual_inc': 7,\n",
" 'chargeoff_within_12_mths': 148,\n",
" 'collection_recovery_fee': 3,\n",
" 'collections_12_mths_ex_med': 148,\n",
" 'delinq_2yrs': 32,\n",
" 'delinq_amnt': 32,\n",
" 'dti': 3,\n",
" 'funded_amnt': 3,\n",
" 'funded_amnt_inv': 3,\n",
" 'inq_last_6mths': 32,\n",
" 'installment': 3,\n",
" 'last_pymnt_amnt': 3,\n",
" 'loan_amnt': 3,\n",
" 'member_id': 3,\n",
" 'open_acc': 32,\n",
" 'out_prncp': 3,\n",
" 'out_prncp_inv': 3,\n",
" 'policy_code': 3,\n",
" 'pub_rec': 32,\n",
" 'pub_rec_bankruptcies': 1368,\n",
" 'recoveries': 3,\n",
" 'revol_bal': 3,\n",
" 'revol_util': 93,\n",
" 'tax_liens': 108,\n",
" 'term': 3,\n",
" 'total_acc': 32,\n",
" 'total_pymnt': 3,\n",
" 'total_pymnt_inv': 3,\n",
" 'total_rec_int': 3,\n",
" 'total_rec_late_fee': 3,\n",
" 'total_rec_prncp': 3}"
]
},
"execution_count": 18,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"chunk_iter = pd.read_csv('loans_2007.csv', chunksize=3000, dtype=convert_col_dtypes, parse_dates=[\"issue_d\", \"earliest_cr_line\", \"last_pymnt_d\", \"last_credit_pull_d\"])\n",
"mv_counts = {}\n",
"for chunk in chunk_iter:\n",
" term_cleaned = chunk['term'].str.lstrip(\" \").str.rstrip(\" months\")\n",
" revol_cleaned = chunk['revol_util'].str.rstrip(\"%\")\n",
" chunk['term'] = pd.to_numeric(term_cleaned)\n",
" chunk['revol_util'] = pd.to_numeric(revol_cleaned)\n",
" float_cols = chunk.select_dtypes(include=['float'])\n",
" for col in float_cols.columns:\n",
" missing_values = len(chunk) - chunk[col].count()\n",
" if col in mv_counts:\n",
" mv_counts[col] = mv_counts[col] + missing_values\n",
" else:\n",
" mv_counts[col] = missing_values\n",
"mv_counts"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"{'acc_now_delinq': 32,\n",
" 'annual_inc': 7,\n",
" 'chargeoff_within_12_mths': 148,\n",
" 'collection_recovery_fee': 3,\n",
" 'collections_12_mths_ex_med': 148,\n",
" 'delinq_2yrs': 32,\n",
" 'delinq_amnt': 32,\n",
" 'dti': 3,\n",
" 'funded_amnt': 3,\n",
" 'funded_amnt_inv': 3,\n",
" 'inq_last_6mths': 32,\n",
" 'installment': 3,\n",
" 'last_pymnt_amnt': 3,\n",
" 'loan_amnt': 3,\n",
" 'member_id': 3,\n",
" 'open_acc': 32,\n",
" 'out_prncp': 3,\n",
" 'out_prncp_inv': 3,\n",
" 'policy_code': 3,\n",
" 'pub_rec': 32,\n",
" 'pub_rec_bankruptcies': 1368,\n",
" 'recoveries': 3,\n",
" 'revol_bal': 3,\n",
" 'revol_util': 93,\n",
" 'tax_liens': 108,\n",
" 'term': 3,\n",
" 'total_acc': 32,\n",
" 'total_pymnt': 3,\n",
" 'total_pymnt_inv': 3,\n",
" 'total_rec_int': 3,\n",
" 'total_rec_late_fee': 3,\n",
" 'total_rec_prncp': 3}"
]
},
"execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"chunk_iter = pd.read_csv('loans_2007.csv', chunksize=3000, dtype=convert_col_dtypes, parse_dates=[\"issue_d\", \"earliest_cr_line\", \"last_pymnt_d\", \"last_credit_pull_d\"])\n",
"mv_counts = {}\n",
"for chunk in chunk_iter:\n",
" term_cleaned = chunk['term'].str.lstrip(\" \").str.rstrip(\" months\")\n",
" revol_cleaned = chunk['revol_util'].str.rstrip(\"%\")\n",
" chunk['term'] = pd.to_numeric(term_cleaned)\n",
" chunk['revol_util'] = pd.to_numeric(revol_cleaned)\n",
" chunk = chunk.dropna(how='all')\n",
" float_cols = chunk.select_dtypes(include=['float'])\n",
" for col in float_cols.columns:\n",
" missing_values = len(chunk) - chunk[col].count()\n",
" if col in mv_counts:\n",
" mv_counts[col] = mv_counts[col] + missing_values\n",
" else:\n",
" mv_counts[col] = missing_values\n",
"mv_counts"
]
}
],
"metadata": {
"anaconda-cloud": {},
"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.4.4"
}
},
"nbformat": 4,
"nbformat_minor": 2
}