Benji_LendingClub.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Parts of this code was used from Harper He Code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import packages\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.ml import feature\n",
    "from pyspark.ml import classification\n",
    "from pyspark.sql import functions as fn\n",
    "from pyspark.sql.functions import isnan, when, count, col\n",
    "from pyspark.ml import Pipeline\n",
    "from pyspark.ml.evaluation import BinaryClassificationEvaluator, \\\n",
    "    MulticlassClassificationEvaluator, \\\n",
    "    RegressionEvaluator\n",
    "from pyspark.ml.tuning import CrossValidator, ParamGridBuilder\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import pandas as pd\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.types import *\n",
    "\n",
    "spark = SparkSession.builder.getOrCreate()\n",
    "sc = spark.sparkContext"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# expand the output display to see more columns of a pandas DataFrame\n",
    "pd.set_option('display.max_columns', None)  \n",
    "pd.set_option('display.expand_frame_repr', False)\n",
    "pd.set_option('max_colwidth', -1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is a function for checking the shape and size\n",
    "def size_shape(df):\n",
    "    print(\"rows:\",df.count())\n",
    "    print(\"columns:\",len(df.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the dataset\n",
    "df = spark.read.csv('loan.csv', header=True, inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>url</th>\n",
       "      <th>desc</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>mths_since_last_delinq</th>\n",
       "      <th>mths_since_last_record</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>next_pymnt_d</th>\n",
       "      <th>last_credit_pull_d</th>\n",
       "      <th>collections_12_mths_ex_med</th>\n",
       "      <th>mths_since_last_major_derog</th>\n",
       "      <th>policy_code</th>\n",
       "      <th>application_type</th>\n",
       "      <th>annual_inc_joint</th>\n",
       "      <th>dti_joint</th>\n",
       "      <th>verification_status_joint</th>\n",
       "      <th>acc_now_delinq</th>\n",
       "      <th>tot_coll_amt</th>\n",
       "      <th>tot_cur_bal</th>\n",
       "      <th>open_acc_6m</th>\n",
       "      <th>open_act_il</th>\n",
       "      <th>open_il_12m</th>\n",
       "      <th>open_il_24m</th>\n",
       "      <th>mths_since_rcnt_il</th>\n",
       "      <th>total_bal_il</th>\n",
       "      <th>il_util</th>\n",
       "      <th>open_rv_12m</th>\n",
       "      <th>open_rv_24m</th>\n",
       "      <th>max_bal_bc</th>\n",
       "      <th>all_util</th>\n",
       "      <th>total_rev_hi_lim</th>\n",
       "      <th>inq_fi</th>\n",
       "      <th>total_cu_tl</th>\n",
       "      <th>inq_last_12m</th>\n",
       "      <th>acc_open_past_24mths</th>\n",
       "      <th>avg_cur_bal</th>\n",
       "      <th>bc_open_to_buy</th>\n",
       "      <th>bc_util</th>\n",
       "      <th>chargeoff_within_12_mths</th>\n",
       "      <th>delinq_amnt</th>\n",
       "      <th>mo_sin_old_il_acct</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>mo_sin_rcnt_rev_tl_op</th>\n",
       "      <th>mo_sin_rcnt_tl</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>mths_since_recent_bc</th>\n",
       "      <th>mths_since_recent_bc_dlq</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "      <th>mths_since_recent_revol_delinq</th>\n",
       "      <th>num_accts_ever_120_pd</th>\n",
       "      <th>num_actv_bc_tl</th>\n",
       "      <th>num_actv_rev_tl</th>\n",
       "      <th>num_bc_sats</th>\n",
       "      <th>num_bc_tl</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>num_op_rev_tl</th>\n",
       "      <th>num_rev_accts</th>\n",
       "      <th>num_rev_tl_bal_gt_0</th>\n",
       "      <th>num_sats</th>\n",
       "      <th>num_tl_120dpd_2m</th>\n",
       "      <th>num_tl_30dpd</th>\n",
       "      <th>num_tl_90g_dpd_24m</th>\n",
       "      <th>num_tl_op_past_12m</th>\n",
       "      <th>pct_tl_nvr_dlq</th>\n",
       "      <th>percent_bc_gt_75</th>\n",
       "      <th>pub_rec_bankruptcies</th>\n",
       "      <th>tax_liens</th>\n",
       "      <th>tot_hi_cred_lim</th>\n",
       "      <th>total_bal_ex_mort</th>\n",
       "      <th>total_bc_limit</th>\n",
       "      <th>total_il_high_credit_limit</th>\n",
       "      <th>revol_bal_joint</th>\n",
       "      <th>sec_app_earliest_cr_line</th>\n",
       "      <th>sec_app_inq_last_6mths</th>\n",
       "      <th>sec_app_mort_acc</th>\n",
       "      <th>sec_app_open_acc</th>\n",
       "      <th>sec_app_revol_util</th>\n",
       "      <th>sec_app_open_act_il</th>\n",
       "      <th>sec_app_num_rev_accts</th>\n",
       "      <th>sec_app_chargeoff_within_12_mths</th>\n",
       "      <th>sec_app_collections_12_mths_ex_med</th>\n",
       "      <th>sec_app_mths_since_last_major_derog</th>\n",
       "      <th>hardship_flag</th>\n",
       "      <th>hardship_type</th>\n",
       "      <th>hardship_reason</th>\n",
       "      <th>hardship_status</th>\n",
       "      <th>deferral_term</th>\n",
       "      <th>hardship_amount</th>\n",
       "      <th>hardship_start_date</th>\n",
       "      <th>hardship_end_date</th>\n",
       "      <th>payment_plan_start_date</th>\n",
       "      <th>hardship_length</th>\n",
       "      <th>hardship_dpd</th>\n",
       "      <th>hardship_loan_status</th>\n",
       "      <th>orig_projected_additional_accrued_interest</th>\n",
       "      <th>hardship_payoff_balance_amount</th>\n",
       "      <th>hardship_last_payment_amount</th>\n",
       "      <th>disbursement_method</th>\n",
       "      <th>debt_settlement_flag</th>\n",
       "      <th>debt_settlement_flag_date</th>\n",
       "      <th>settlement_status</th>\n",
       "      <th>settlement_date</th>\n",
       "      <th>settlement_amount</th>\n",
       "      <th>settlement_percentage</th>\n",
       "      <th>settlement_term</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2500</td>\n",
       "      <td>2500</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>13.56</td>\n",
       "      <td>84.92</td>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>Chef</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>RENT</td>\n",
       "      <td>55000</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>Dec-2018</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>109xx</td>\n",
       "      <td>NY</td>\n",
       "      <td>18.24</td>\n",
       "      <td>0</td>\n",
       "      <td>Apr-2001</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>45</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>4341</td>\n",
       "      <td>10.3</td>\n",
       "      <td>34</td>\n",
       "      <td>w</td>\n",
       "      <td>2386.02</td>\n",
       "      <td>2386.02</td>\n",
       "      <td>167.02</td>\n",
       "      <td>167.02</td>\n",
       "      <td>113.98</td>\n",
       "      <td>53.04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Feb-2019</td>\n",
       "      <td>84.92</td>\n",
       "      <td>Mar-2019</td>\n",
       "      <td>Feb-2019</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>Individual</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16901</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>12560</td>\n",
       "      <td>69</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>2137</td>\n",
       "      <td>28</td>\n",
       "      <td>42000</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>1878</td>\n",
       "      <td>34360.0</td>\n",
       "      <td>5.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>212</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>60124</td>\n",
       "      <td>16901</td>\n",
       "      <td>36500</td>\n",
       "      <td>18124</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Cash</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>18.94</td>\n",
       "      <td>777.23</td>\n",
       "      <td>D</td>\n",
       "      <td>D2</td>\n",
       "      <td>Postmaster</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>90000</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Dec-2018</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>713xx</td>\n",
       "      <td>LA</td>\n",
       "      <td>26.52</td>\n",
       "      <td>0</td>\n",
       "      <td>Jun-1987</td>\n",
       "      <td>0</td>\n",
       "      <td>71</td>\n",
       "      <td>75</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>12315</td>\n",
       "      <td>24.2</td>\n",
       "      <td>44</td>\n",
       "      <td>w</td>\n",
       "      <td>29387.75</td>\n",
       "      <td>29387.75</td>\n",
       "      <td>1507.11</td>\n",
       "      <td>1507.11</td>\n",
       "      <td>612.25</td>\n",
       "      <td>894.86</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Feb-2019</td>\n",
       "      <td>777.23</td>\n",
       "      <td>Mar-2019</td>\n",
       "      <td>Feb-2019</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>Individual</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>1208</td>\n",
       "      <td>321915</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>87153</td>\n",
       "      <td>88</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>998</td>\n",
       "      <td>57</td>\n",
       "      <td>50800</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>24763</td>\n",
       "      <td>13761.0</td>\n",
       "      <td>8.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>378</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "      <td>8</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>95.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>372872</td>\n",
       "      <td>99468</td>\n",
       "      <td>15000</td>\n",
       "      <td>94072</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Cash</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>17.97</td>\n",
       "      <td>180.69</td>\n",
       "      <td>D</td>\n",
       "      <td>D1</td>\n",
       "      <td>Administrative</td>\n",
       "      <td>6 years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>59280</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Dec-2018</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>490xx</td>\n",
       "      <td>MI</td>\n",
       "      <td>10.51</td>\n",
       "      <td>0</td>\n",
       "      <td>Apr-2011</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>4599</td>\n",
       "      <td>19.1</td>\n",
       "      <td>13</td>\n",
       "      <td>w</td>\n",
       "      <td>4787.21</td>\n",
       "      <td>4787.21</td>\n",
       "      <td>353.89</td>\n",
       "      <td>353.89</td>\n",
       "      <td>212.79</td>\n",
       "      <td>141.10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Feb-2019</td>\n",
       "      <td>180.69</td>\n",
       "      <td>Mar-2019</td>\n",
       "      <td>Feb-2019</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>Individual</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>110299</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>7150</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>35</td>\n",
       "      <td>24100</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>18383</td>\n",
       "      <td>13800.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>92</td>\n",
       "      <td>15</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>136927</td>\n",
       "      <td>11749</td>\n",
       "      <td>13800</td>\n",
       "      <td>10000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Cash</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>4000</td>\n",
       "      <td>4000</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>18.94</td>\n",
       "      <td>146.51</td>\n",
       "      <td>D</td>\n",
       "      <td>D2</td>\n",
       "      <td>IT Supervisor</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>92000</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Dec-2018</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>985xx</td>\n",
       "      <td>WA</td>\n",
       "      <td>16.74</td>\n",
       "      <td>0</td>\n",
       "      <td>Feb-2006</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>5468</td>\n",
       "      <td>78.1</td>\n",
       "      <td>13</td>\n",
       "      <td>w</td>\n",
       "      <td>3831.93</td>\n",
       "      <td>3831.93</td>\n",
       "      <td>286.71</td>\n",
       "      <td>286.71</td>\n",
       "      <td>168.07</td>\n",
       "      <td>118.64</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Feb-2019</td>\n",
       "      <td>146.51</td>\n",
       "      <td>Mar-2019</td>\n",
       "      <td>Feb-2019</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>Individual</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>686</td>\n",
       "      <td>305049</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>30683</td>\n",
       "      <td>68</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3761</td>\n",
       "      <td>70</td>\n",
       "      <td>7000</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>30505</td>\n",
       "      <td>1239.0</td>\n",
       "      <td>75.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>154</td>\n",
       "      <td>64</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>385183</td>\n",
       "      <td>36151</td>\n",
       "      <td>5000</td>\n",
       "      <td>44984</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Cash</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>16.14</td>\n",
       "      <td>731.78</td>\n",
       "      <td>C</td>\n",
       "      <td>C4</td>\n",
       "      <td>Mechanic</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>57250</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>Dec-2018</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>212xx</td>\n",
       "      <td>MD</td>\n",
       "      <td>26.35</td>\n",
       "      <td>0</td>\n",
       "      <td>Dec-2000</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>829</td>\n",
       "      <td>3.6</td>\n",
       "      <td>26</td>\n",
       "      <td>w</td>\n",
       "      <td>29339.02</td>\n",
       "      <td>29339.02</td>\n",
       "      <td>1423.21</td>\n",
       "      <td>1423.21</td>\n",
       "      <td>660.98</td>\n",
       "      <td>762.23</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Feb-2019</td>\n",
       "      <td>731.78</td>\n",
       "      <td>Mar-2019</td>\n",
       "      <td>Feb-2019</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>Individual</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>116007</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>28845</td>\n",
       "      <td>89</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>516</td>\n",
       "      <td>54</td>\n",
       "      <td>23100</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>9667</td>\n",
       "      <td>8471.0</td>\n",
       "      <td>8.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>216</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>92.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>157548</td>\n",
       "      <td>29674</td>\n",
       "      <td>9300</td>\n",
       "      <td>32332</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Cash</td>\n",
       "      <td>N</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id member_id  loan_amnt  funded_amnt  funded_amnt_inv        term  int_rate  installment grade sub_grade       emp_title emp_length home_ownership annual_inc verification_status   issue_d loan_status pymnt_plan   url  desc             purpose               title zip_code addr_state    dti delinq_2yrs earliest_cr_line inq_last_6mths mths_since_last_delinq mths_since_last_record open_acc pub_rec revol_bal revol_util total_acc initial_list_status out_prncp out_prncp_inv total_pymnt total_pymnt_inv total_rec_prncp total_rec_int total_rec_late_fee recoveries collection_recovery_fee last_pymnt_d last_pymnt_amnt next_pymnt_d last_credit_pull_d collections_12_mths_ex_med mths_since_last_major_derog policy_code application_type annual_inc_joint dti_joint verification_status_joint acc_now_delinq tot_coll_amt tot_cur_bal open_acc_6m open_act_il open_il_12m open_il_24m mths_since_rcnt_il total_bal_il il_util open_rv_12m open_rv_24m max_bal_bc all_util total_rev_hi_lim inq_fi total_cu_tl inq_last_12m  acc_open_past_24mths avg_cur_bal  bc_open_to_buy bc_util  chargeoff_within_12_mths  delinq_amnt  mo_sin_old_il_acct  mo_sin_old_rev_tl_op  mo_sin_rcnt_rev_tl_op  mo_sin_rcnt_tl  mort_acc  mths_since_recent_bc  mths_since_recent_bc_dlq  mths_since_recent_inq  mths_since_recent_revol_delinq  num_accts_ever_120_pd  num_actv_bc_tl  num_actv_rev_tl  num_bc_sats  num_bc_tl  num_il_tl  num_op_rev_tl  num_rev_accts  num_rev_tl_bal_gt_0  num_sats  num_tl_120dpd_2m  num_tl_30dpd  num_tl_90g_dpd_24m  num_tl_op_past_12m  pct_tl_nvr_dlq  percent_bc_gt_75  pub_rec_bankruptcies  tax_liens  tot_hi_cred_lim  total_bal_ex_mort  total_bc_limit  total_il_high_credit_limit  revol_bal_joint sec_app_earliest_cr_line  sec_app_inq_last_6mths  sec_app_mort_acc  sec_app_open_acc  sec_app_revol_util  sec_app_open_act_il  sec_app_num_rev_accts  sec_app_chargeoff_within_12_mths  sec_app_collections_12_mths_ex_med  sec_app_mths_since_last_major_derog hardship_flag hardship_type hardship_reason hardship_status deferral_term hardship_amount hardship_start_date hardship_end_date payment_plan_start_date hardship_length hardship_dpd hardship_loan_status orig_projected_additional_accrued_interest hardship_payoff_balance_amount hardship_last_payment_amount disbursement_method debt_settlement_flag debt_settlement_flag_date settlement_status settlement_date settlement_amount settlement_percentage settlement_term\n",
       "0  None  None      2500       2500         2500.0            36 months  13.56     84.92        C     C1        Chef            10+ years  RENT           55000      Not Verified        Dec-2018  Current     n          None  None  debt_consolidation  Debt consolidation  109xx    NY         18.24  0           Apr-2001         1              None                   45                     9        1       4341      10.3       34        w                   2386.02   2386.02       167.02      167.02          113.98          53.04         0.0                0.0        0.0                     Feb-2019     84.92           Mar-2019     Feb-2019           0                          None                        1           Individual       None             None      None                      0              0            16901       2           2           1           2           2                  12560        69      2           7           2137       28       42000            1      11          2            9                     1878        34360.0         5.9     0.0                       0            140.0               212                   1                      1               0         1.0                  NaN                        2                     NaN                              0                      2               5                3            3          16         7              18             5                    9         0                 0             0                   3                   100.0           0.0               1                     0          60124            16901              36500           18124                      NaN               None                    NaN                     NaN               NaN               NaN                 NaN                  NaN                    NaN                               NaN                                 NaN                                   N             None          None            None            None          None            None                None              None                    None            None         None                 None                                       None                           None                         Cash                N                    None                      None              None            None              None                  None          \n",
       "1  None  None      30000      30000        30000.0           60 months  18.94     777.23       D     D2        Postmaster      10+ years  MORTGAGE       90000      Source Verified     Dec-2018  Current     n          None  None  debt_consolidation  Debt consolidation  713xx    LA         26.52  0           Jun-1987         0              71                     75                     13       1       12315     24.2       44        w                   29387.75  29387.75      1507.11     1507.11         612.25          894.86        0.0                0.0        0.0                     Feb-2019     777.23          Mar-2019     Feb-2019           0                          None                        1           Individual       None             None      None                      0              1208         321915      4           4           2           3           3                  87153        88      4           5           998        57       50800            2      15          2            10                    24763       13761.0         8.3     0.0                       0            163.0               378                   4                      3               3         4.0                  NaN                        4                     NaN                              0                      2               4                4            9          27         8              14             4                    13        0                 0             0                   6                   95.0            0.0               1                     0          372872           99468              15000           94072                      NaN               None                    NaN                     NaN               NaN               NaN                 NaN                  NaN                    NaN                               NaN                                 NaN                                   N             None          None            None            None          None            None                None              None                    None            None         None                 None                                       None                           None                         Cash                N                    None                      None              None            None              None                  None          \n",
       "2  None  None      5000       5000         5000.0            36 months  17.97     180.69       D     D1        Administrative  6 years    MORTGAGE       59280      Source Verified     Dec-2018  Current     n          None  None  debt_consolidation  Debt consolidation  490xx    MI         10.51  0           Apr-2011         0              None                   None                   8        0       4599      19.1       13        w                   4787.21   4787.21       353.89      353.89          212.79          141.10        0.0                0.0        0.0                     Feb-2019     180.69          Mar-2019     Feb-2019           0                          None                        1           Individual       None             None      None                      0              0            110299      0           1           0           2           14                 7150         72      0           2           0          35       24100            1      5           0            4                     18383       13800.0         0       0.0                       0            87.0                92                    15                     14              2         77.0                 NaN                        14                    NaN                              0                      0               3                3            3          4          6              7              3                    8         0                 0             0                   0                   100.0           0.0               0                     0          136927           11749              13800           10000                      NaN               None                    NaN                     NaN               NaN               NaN                 NaN                  NaN                    NaN                               NaN                                 NaN                                   N             None          None            None            None          None            None                None              None                    None            None         None                 None                                       None                           None                         Cash                N                    None                      None              None            None              None                  None          \n",
       "3  None  None      4000       4000         4000.0            36 months  18.94     146.51       D     D2        IT Supervisor   10+ years  MORTGAGE       92000      Source Verified     Dec-2018  Current     n          None  None  debt_consolidation  Debt consolidation  985xx    WA         16.74  0           Feb-2006         0              None                   None                   10       0       5468      78.1       13        w                   3831.93   3831.93       286.71      286.71          168.07          118.64        0.0                0.0        0.0                     Feb-2019     146.51          Mar-2019     Feb-2019           0                          None                        1           Individual       None             None      None                      0              686          305049      1           5           3           5           5                  30683        68      0           0           3761       70       7000             2      4           3            5                     30505       1239.0          75.2    0.0                       0            62.0                154                   64                     5               3         64.0                 NaN                        5                     NaN                              0                      1               2                1            2          7          2              3              2                    10        0                 0             0                   3                   100.0           100.0             0                     0          385183           36151              5000            44984                      NaN               None                    NaN                     NaN               NaN               NaN                 NaN                  NaN                    NaN                               NaN                                 NaN                                   N             None          None            None            None          None            None                None              None                    None            None         None                 None                                       None                           None                         Cash                N                    None                      None              None            None              None                  None          \n",
       "4  None  None      30000      30000        30000.0           60 months  16.14     731.78       C     C4        Mechanic        10+ years  MORTGAGE       57250      Not Verified        Dec-2018  Current     n          None  None  debt_consolidation  Debt consolidation  212xx    MD         26.35  0           Dec-2000         0              None                   None                   12       0       829       3.6        26        w                   29339.02  29339.02      1423.21     1423.21         660.98          762.23        0.0                0.0        0.0                     Feb-2019     731.78          Mar-2019     Feb-2019           0                          None                        1           Individual       None             None      None                      0              0            116007      3           5           3           5           4                  28845        89      2           4           516        54       23100            1      0           0            9                     9667        8471.0          8.9     0.0                       0            53.0                216                   2                      2               2         2.0                  NaN                        13                    NaN                              0                      2               2                3            8          9          6              15             2                    12        0                 0             0                   5                   92.3            0.0               0                     0          157548           29674              9300            32332                      NaN               None                    NaN                     NaN               NaN               NaN                 NaN                  NaN                    NaN                               NaN                                 NaN                                   N             None          None            None            None          None            None                None              None                    None            None         None                 None                                       None                           None                         Cash                N                    None                      None              None            None              None                  None          "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# take a look at the first 10 rows\n",
    "df_pd = df.limit(10).toPandas()\n",
    "display(df_pd.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-------+\n",
      "|         loan_status|  count|\n",
      "+--------------------+-------+\n",
      "|          Fully Paid|1041952|\n",
      "|             Default|     31|\n",
      "|     In Grace Period|   8952|\n",
      "|Does not meet the...|   1988|\n",
      "|         Charged Off| 261654|\n",
      "|            Oct-2015|      1|\n",
      "|  Late (31-120 days)|  21897|\n",
      "|             Current| 919695|\n",
      "|Does not meet the...|    761|\n",
      "|   Late (16-30 days)|   3737|\n",
      "+--------------------+-------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# check how many records for each loan status\n",
    "df_bystatus=df.groupby(df.loan_status).count()\n",
    "df_bystatus.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# show all the column names\n",
    "# df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows: 2260668\n",
      "columns: 145\n"
     ]
    }
   ],
   "source": [
    "# check the shape of df\n",
    "# print(\"rows:\",df.count())\n",
    "# print(\"columns:\",len(df.columns))\n",
    "\n",
    "size_shape(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+------+\n",
      "|loan_status| count|\n",
      "+-----------+------+\n",
      "|Charged Off|261654|\n",
      "|       Late| 25634|\n",
      "|    Current|919695|\n",
      "+-----------+------+\n",
      "\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# check the number of Nan or Null for each column\n",
    "#As per our objective, Filtering only the columns required\n",
    "current_df = df.filter(col('loan_status').isin(['Late (31-120 days)','Charged Off','Late (16-30 days)','Current']))\n",
    "#Renaming the Late values into a single late\n",
    "current_df = current_df.withColumn(\"loan_status\", \\\n",
    "              when(current_df[\"loan_status\"].isin(['Late (31-120 days)','Late (16-30 days)']),'Late').otherwise(current_df[\"loan_status\"]))\n",
    "#Our dataset with only the desired columns values (Late,charged off,current)\n",
    "print(current_df.groupby(current_df.loan_status).count().show())\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>url</th>\n",
       "      <th>desc</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>mths_since_last_delinq</th>\n",
       "      <th>mths_since_last_record</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>next_pymnt_d</th>\n",
       "      <th>last_credit_pull_d</th>\n",
       "      <th>collections_12_mths_ex_med</th>\n",
       "      <th>mths_since_last_major_derog</th>\n",
       "      <th>policy_code</th>\n",
       "      <th>application_type</th>\n",
       "      <th>annual_inc_joint</th>\n",
       "      <th>dti_joint</th>\n",
       "      <th>verification_status_joint</th>\n",
       "      <th>acc_now_delinq</th>\n",
       "      <th>tot_coll_amt</th>\n",
       "      <th>tot_cur_bal</th>\n",
       "      <th>open_acc_6m</th>\n",
       "      <th>open_act_il</th>\n",
       "      <th>open_il_12m</th>\n",
       "      <th>open_il_24m</th>\n",
       "      <th>mths_since_rcnt_il</th>\n",
       "      <th>total_bal_il</th>\n",
       "      <th>il_util</th>\n",
       "      <th>open_rv_12m</th>\n",
       "      <th>open_rv_24m</th>\n",
       "      <th>max_bal_bc</th>\n",
       "      <th>all_util</th>\n",
       "      <th>total_rev_hi_lim</th>\n",
       "      <th>inq_fi</th>\n",
       "      <th>total_cu_tl</th>\n",
       "      <th>inq_last_12m</th>\n",
       "      <th>acc_open_past_24mths</th>\n",
       "      <th>avg_cur_bal</th>\n",
       "      <th>bc_open_to_buy</th>\n",
       "      <th>bc_util</th>\n",
       "      <th>chargeoff_within_12_mths</th>\n",
       "      <th>delinq_amnt</th>\n",
       "      <th>mo_sin_old_il_acct</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>mo_sin_rcnt_rev_tl_op</th>\n",
       "      <th>mo_sin_rcnt_tl</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>mths_since_recent_bc</th>\n",
       "      <th>mths_since_recent_bc_dlq</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "      <th>mths_since_recent_revol_delinq</th>\n",
       "      <th>num_accts_ever_120_pd</th>\n",
       "      <th>num_actv_bc_tl</th>\n",
       "      <th>num_actv_rev_tl</th>\n",
       "      <th>num_bc_sats</th>\n",
       "      <th>num_bc_tl</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>num_op_rev_tl</th>\n",
       "      <th>num_rev_accts</th>\n",
       "      <th>num_rev_tl_bal_gt_0</th>\n",
       "      <th>num_sats</th>\n",
       "      <th>num_tl_120dpd_2m</th>\n",
       "      <th>num_tl_30dpd</th>\n",
       "      <th>num_tl_90g_dpd_24m</th>\n",
       "      <th>num_tl_op_past_12m</th>\n",
       "      <th>pct_tl_nvr_dlq</th>\n",
       "      <th>percent_bc_gt_75</th>\n",
       "      <th>pub_rec_bankruptcies</th>\n",
       "      <th>tax_liens</th>\n",
       "      <th>tot_hi_cred_lim</th>\n",
       "      <th>total_bal_ex_mort</th>\n",
       "      <th>total_bc_limit</th>\n",
       "      <th>total_il_high_credit_limit</th>\n",
       "      <th>revol_bal_joint</th>\n",
       "      <th>sec_app_earliest_cr_line</th>\n",
       "      <th>sec_app_inq_last_6mths</th>\n",
       "      <th>sec_app_mort_acc</th>\n",
       "      <th>sec_app_open_acc</th>\n",
       "      <th>sec_app_revol_util</th>\n",
       "      <th>sec_app_open_act_il</th>\n",
       "      <th>sec_app_num_rev_accts</th>\n",
       "      <th>sec_app_chargeoff_within_12_mths</th>\n",
       "      <th>sec_app_collections_12_mths_ex_med</th>\n",
       "      <th>sec_app_mths_since_last_major_derog</th>\n",
       "      <th>hardship_flag</th>\n",
       "      <th>hardship_type</th>\n",
       "      <th>hardship_reason</th>\n",
       "      <th>hardship_status</th>\n",
       "      <th>deferral_term</th>\n",
       "      <th>hardship_amount</th>\n",
       "      <th>hardship_start_date</th>\n",
       "      <th>hardship_end_date</th>\n",
       "      <th>payment_plan_start_date</th>\n",
       "      <th>hardship_length</th>\n",
       "      <th>hardship_dpd</th>\n",
       "      <th>hardship_loan_status</th>\n",
       "      <th>orig_projected_additional_accrued_interest</th>\n",
       "      <th>hardship_payoff_balance_amount</th>\n",
       "      <th>hardship_last_payment_amount</th>\n",
       "      <th>disbursement_method</th>\n",
       "      <th>debt_settlement_flag</th>\n",
       "      <th>debt_settlement_flag_date</th>\n",
       "      <th>settlement_status</th>\n",
       "      <th>settlement_date</th>\n",
       "      <th>settlement_amount</th>\n",
       "      <th>settlement_percentage</th>\n",
       "      <th>settlement_term</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1206983</td>\n",
       "      <td>1206983</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>105247</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1206983</td>\n",
       "      <td>1187738</td>\n",
       "      <td>0</td>\n",
       "      <td>12520</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1456</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>622762</td>\n",
       "      <td>1021293</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>1120</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2414</td>\n",
       "      <td>1</td>\n",
       "      <td>261622</td>\n",
       "      <td>31</td>\n",
       "      <td>15</td>\n",
       "      <td>895784</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>1104847</td>\n",
       "      <td>1104856</td>\n",
       "      <td>1109686</td>\n",
       "      <td>18</td>\n",
       "      <td>10302</td>\n",
       "      <td>10299</td>\n",
       "      <td>206300</td>\n",
       "      <td>206297</td>\n",
       "      <td>206304</td>\n",
       "      <td>206307</td>\n",
       "      <td>240368</td>\n",
       "      <td>206306</td>\n",
       "      <td>355256</td>\n",
       "      <td>206310</td>\n",
       "      <td>206308</td>\n",
       "      <td>206309</td>\n",
       "      <td>206487</td>\n",
       "      <td>10316</td>\n",
       "      <td>206310</td>\n",
       "      <td>206310</td>\n",
       "      <td>206313</td>\n",
       "      <td>6884</td>\n",
       "      <td>10370</td>\n",
       "      <td>21150</td>\n",
       "      <td>21696</td>\n",
       "      <td>39</td>\n",
       "      <td>28</td>\n",
       "      <td>50004</td>\n",
       "      <td>10310</td>\n",
       "      <td>10315</td>\n",
       "      <td>10314</td>\n",
       "      <td>6885</td>\n",
       "      <td>20333</td>\n",
       "      <td>933557</td>\n",
       "      <td>147619</td>\n",
       "      <td>815085</td>\n",
       "      <td>10318</td>\n",
       "      <td>10317</td>\n",
       "      <td>10319</td>\n",
       "      <td>8263</td>\n",
       "      <td>10317</td>\n",
       "      <td>10317</td>\n",
       "      <td>10317</td>\n",
       "      <td>10318</td>\n",
       "      <td>10317</td>\n",
       "      <td>8263</td>\n",
       "      <td>58317</td>\n",
       "      <td>10318</td>\n",
       "      <td>10316</td>\n",
       "      <td>10316</td>\n",
       "      <td>10341</td>\n",
       "      <td>21262</td>\n",
       "      <td>147</td>\n",
       "      <td>32</td>\n",
       "      <td>10309</td>\n",
       "      <td>6877</td>\n",
       "      <td>6882</td>\n",
       "      <td>10314</td>\n",
       "      <td>1112427</td>\n",
       "      <td>1112425</td>\n",
       "      <td>1112422</td>\n",
       "      <td>1112422</td>\n",
       "      <td>1112425</td>\n",
       "      <td>1114082</td>\n",
       "      <td>1112428</td>\n",
       "      <td>1112429</td>\n",
       "      <td>1112428</td>\n",
       "      <td>1112428</td>\n",
       "      <td>1175616</td>\n",
       "      <td>33</td>\n",
       "      <td>1198269</td>\n",
       "      <td>1198263</td>\n",
       "      <td>1198269</td>\n",
       "      <td>1198269</td>\n",
       "      <td>1198271</td>\n",
       "      <td>1198270</td>\n",
       "      <td>1198267</td>\n",
       "      <td>1198269</td>\n",
       "      <td>1198268</td>\n",
       "      <td>1198272</td>\n",
       "      <td>1198271</td>\n",
       "      <td>1200360</td>\n",
       "      <td>1198272</td>\n",
       "      <td>1198271</td>\n",
       "      <td>34</td>\n",
       "      <td>30</td>\n",
       "      <td>1173940</td>\n",
       "      <td>1173941</td>\n",
       "      <td>1173945</td>\n",
       "      <td>1173947</td>\n",
       "      <td>1173950</td>\n",
       "      <td>1173948</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id  member_id  loan_amnt  funded_amnt  funded_amnt_inv  term  int_rate  installment  grade  sub_grade  emp_title  emp_length  home_ownership  annual_inc  verification_status  issue_d  loan_status  pymnt_plan      url     desc  purpose  title  zip_code  addr_state   dti  delinq_2yrs  earliest_cr_line  inq_last_6mths  mths_since_last_delinq  mths_since_last_record  open_acc  pub_rec  revol_bal  revol_util  total_acc  initial_list_status  out_prncp  out_prncp_inv  total_pymnt  total_pymnt_inv  total_rec_prncp  total_rec_int  total_rec_late_fee  recoveries  collection_recovery_fee  last_pymnt_d  last_pymnt_amnt  next_pymnt_d  last_credit_pull_d  collections_12_mths_ex_med  mths_since_last_major_derog  policy_code  application_type  annual_inc_joint  dti_joint  verification_status_joint  acc_now_delinq  tot_coll_amt  tot_cur_bal  open_acc_6m  open_act_il  open_il_12m  open_il_24m  mths_since_rcnt_il  total_bal_il  il_util  open_rv_12m  open_rv_24m  max_bal_bc  all_util  total_rev_hi_lim  inq_fi  total_cu_tl  inq_last_12m  acc_open_past_24mths  avg_cur_bal  bc_open_to_buy  bc_util  chargeoff_within_12_mths  delinq_amnt  mo_sin_old_il_acct  mo_sin_old_rev_tl_op  mo_sin_rcnt_rev_tl_op  mo_sin_rcnt_tl  mort_acc  mths_since_recent_bc  mths_since_recent_bc_dlq  mths_since_recent_inq  mths_since_recent_revol_delinq  num_accts_ever_120_pd  num_actv_bc_tl  num_actv_rev_tl  num_bc_sats  num_bc_tl  num_il_tl  num_op_rev_tl  num_rev_accts  num_rev_tl_bal_gt_0  num_sats  num_tl_120dpd_2m  num_tl_30dpd  num_tl_90g_dpd_24m  num_tl_op_past_12m  pct_tl_nvr_dlq  percent_bc_gt_75  pub_rec_bankruptcies  tax_liens  tot_hi_cred_lim  total_bal_ex_mort  total_bc_limit  total_il_high_credit_limit  revol_bal_joint  sec_app_earliest_cr_line  sec_app_inq_last_6mths  sec_app_mort_acc  sec_app_open_acc  sec_app_revol_util  sec_app_open_act_il  sec_app_num_rev_accts  sec_app_chargeoff_within_12_mths  sec_app_collections_12_mths_ex_med  sec_app_mths_since_last_major_derog  hardship_flag  hardship_type  hardship_reason  hardship_status  deferral_term  hardship_amount  hardship_start_date  hardship_end_date  payment_plan_start_date  hardship_length  hardship_dpd  hardship_loan_status  orig_projected_additional_accrued_interest  hardship_payoff_balance_amount  hardship_last_payment_amount  disbursement_method  debt_settlement_flag  debt_settlement_flag_date  settlement_status  settlement_date  settlement_amount  settlement_percentage  settlement_term\n",
       "0  1206983  1206983    0          0            0                0     0         0            0      0          105247     0           0               0           0                    0        0            0           1206983  1187738  0        12520  1         1           1456  1            1                 1               622762                  1021293                 5         8        6          1120        2          3                    5          8              5            2                3                1              2                   2           2                        2414          1                261622        31                  15                          895784                       8            12                1104847           1104856    1109686                    18              10302         10299        206300       206297       206304       206307       240368              206306        355256   206310       206308       206309      206487    10316             206310  206310       206313        6884                  10370        21150           21696    39                        28           50004               10310                 10315                  10314           6885      20333                 933557                    147619                 815085                          10318                  10317           10319            8263         10317      10317      10317          10318          10317                8263      58317             10318         10316               10316               10341           21262             147                   32         10309            6877               6882            10314                       1112427          1112425                   1112422                 1112422           1112425           1114082             1112428              1112429                1112428                           1112428                             1175616                              33             1198269        1198263          1198269          1198269        1198271          1198270              1198267            1198269                  1198268          1198272       1198271               1200360                                     1198272                         1198271                       34                   30                    1173940                    1173941            1173945          1173947            1173950                1173948        "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Contains the nas present in all the columns in the dataset. We are planning to remove those coluomns with more than 50%Nas\n",
    "checkna_pd = current_df.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in current_df.columns]).toPandas()\n",
    "checkna_pd\n",
    "\n",
    "# current_df.select([c for c in checkna_pd.columns])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#https://stackoverflow.com/questions/51322445/how-to-drop-all-columns-with-null-values-in-a-pyspark-dataframe - include in our references\n",
    "\n",
    "#Creating a dataframe which has the count of Nas of each column\n",
    "checkna = current_df.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in current_df.columns])\n",
    "#Selecting the columns with NA values not greater than 50%\n",
    "No_of_rows = current_df.count()\n",
    "checkna_greater_than_50 = checkna.select([when(fn.col(c)<(No_of_rows*0.50),c).alias(c) for c in checkna.columns])\n",
    "\n",
    "\n",
    "def drop_null_columns(df):\n",
    "    \"\"\"\n",
    "    This function drops all columns which contain null values.\n",
    "    :param df: A PySpark DataFrame\n",
    "    \"\"\"\n",
    "    null_counts = df.select([fn.count(fn.when(fn.col(c).isNull()|isnan(fn.col(c)), c)).alias(c) for c in df.columns]).collect()[0].asDict()\n",
    "    to_drop = [k for k, v in null_counts.items() if v > 0]\n",
    "    df = df.drop(*to_drop)\n",
    "    return df\n",
    "\n",
    "# Using the function to find the list of columns with less than 50%Na values\n",
    "final_cols = drop_null_columns(checkna_greater_than_50)\n",
    "current_df1 = current_df.select([fn.col(c) for c in final_cols.columns])\n",
    "\n",
    "#selecting our dataframe with the final cols\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Benji Explore Columns 1-25"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_amnt</th>\n",
       "      <th>funded_amnt</th>\n",
       "      <th>funded_amnt_inv</th>\n",
       "      <th>term</th>\n",
       "      <th>int_rate</th>\n",
       "      <th>installment</th>\n",
       "      <th>grade</th>\n",
       "      <th>sub_grade</th>\n",
       "      <th>emp_title</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2500</td>\n",
       "      <td>2500</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>13.56</td>\n",
       "      <td>84.92</td>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>Chef</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>RENT</td>\n",
       "      <td>55000</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>Dec-2018</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>109xx</td>\n",
       "      <td>NY</td>\n",
       "      <td>18.24</td>\n",
       "      <td>0</td>\n",
       "      <td>Apr-2001</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>18.94</td>\n",
       "      <td>777.23</td>\n",
       "      <td>D</td>\n",
       "      <td>D2</td>\n",
       "      <td>Postmaster</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>90000</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Dec-2018</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>713xx</td>\n",
       "      <td>LA</td>\n",
       "      <td>26.52</td>\n",
       "      <td>0</td>\n",
       "      <td>Jun-1987</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>17.97</td>\n",
       "      <td>180.69</td>\n",
       "      <td>D</td>\n",
       "      <td>D1</td>\n",
       "      <td>Administrative</td>\n",
       "      <td>6 years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>59280</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Dec-2018</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>490xx</td>\n",
       "      <td>MI</td>\n",
       "      <td>10.51</td>\n",
       "      <td>0</td>\n",
       "      <td>Apr-2011</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4000</td>\n",
       "      <td>4000</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>18.94</td>\n",
       "      <td>146.51</td>\n",
       "      <td>D</td>\n",
       "      <td>D2</td>\n",
       "      <td>IT Supervisor</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>92000</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Dec-2018</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>985xx</td>\n",
       "      <td>WA</td>\n",
       "      <td>16.74</td>\n",
       "      <td>0</td>\n",
       "      <td>Feb-2006</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>16.14</td>\n",
       "      <td>731.78</td>\n",
       "      <td>C</td>\n",
       "      <td>C4</td>\n",
       "      <td>Mechanic</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>57250</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>Dec-2018</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt consolidation</td>\n",
       "      <td>212xx</td>\n",
       "      <td>MD</td>\n",
       "      <td>26.35</td>\n",
       "      <td>0</td>\n",
       "      <td>Dec-2000</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_amnt  funded_amnt  funded_amnt_inv        term  int_rate  installment grade sub_grade       emp_title emp_length home_ownership annual_inc verification_status   issue_d loan_status pymnt_plan             purpose               title zip_code addr_state    dti delinq_2yrs earliest_cr_line inq_last_6mths open_acc\n",
       "0  2500       2500         2500.0            36 months  13.56     84.92        C     C1        Chef            10+ years  RENT           55000      Not Verified        Dec-2018  Current     n          debt_consolidation  Debt consolidation  109xx    NY         18.24  0           Apr-2001         1              9      \n",
       "1  30000      30000        30000.0           60 months  18.94     777.23       D     D2        Postmaster      10+ years  MORTGAGE       90000      Source Verified     Dec-2018  Current     n          debt_consolidation  Debt consolidation  713xx    LA         26.52  0           Jun-1987         0              13     \n",
       "2  5000       5000         5000.0            36 months  17.97     180.69       D     D1        Administrative  6 years    MORTGAGE       59280      Source Verified     Dec-2018  Current     n          debt_consolidation  Debt consolidation  490xx    MI         10.51  0           Apr-2011         0              8      \n",
       "3  4000       4000         4000.0            36 months  18.94     146.51       D     D2        IT Supervisor   10+ years  MORTGAGE       92000      Source Verified     Dec-2018  Current     n          debt_consolidation  Debt consolidation  985xx    WA         16.74  0           Feb-2006         0              10     \n",
       "4  30000      30000        30000.0           60 months  16.14     731.78       C     C4        Mechanic        10+ years  MORTGAGE       57250      Not Verified        Dec-2018  Current     n          debt_consolidation  Debt consolidation  212xx    MD         26.35  0           Dec-2000         0              12     "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "part1=current_df1.select(current_df1.columns[0:25])\n",
    "#part1.columns\n",
    "part1.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows: 1206983\n",
      "columns: 21\n",
      "None\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>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>loan_status</th>\n",
       "      <th>pymnt_plan</th>\n",
       "      <th>purpose</th>\n",
       "      <th>addr_state</th>\n",
       "      <th>dti</th>\n",
       "      <th>delinq_2yrs</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>open_acc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2500</td>\n",
       "      <td>2500</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>13.56</td>\n",
       "      <td>84.92</td>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>Chef</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>RENT</td>\n",
       "      <td>55000</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>NY</td>\n",
       "      <td>18.24</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>18.94</td>\n",
       "      <td>777.23</td>\n",
       "      <td>D</td>\n",
       "      <td>D2</td>\n",
       "      <td>Postmaster</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>90000</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>LA</td>\n",
       "      <td>26.52</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>17.97</td>\n",
       "      <td>180.69</td>\n",
       "      <td>D</td>\n",
       "      <td>D1</td>\n",
       "      <td>Administrative</td>\n",
       "      <td>6 years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>59280</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>MI</td>\n",
       "      <td>10.51</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4000</td>\n",
       "      <td>4000</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>18.94</td>\n",
       "      <td>146.51</td>\n",
       "      <td>D</td>\n",
       "      <td>D2</td>\n",
       "      <td>IT Supervisor</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>92000</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>WA</td>\n",
       "      <td>16.74</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>16.14</td>\n",
       "      <td>731.78</td>\n",
       "      <td>C</td>\n",
       "      <td>C4</td>\n",
       "      <td>Mechanic</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>57250</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>MD</td>\n",
       "      <td>26.35</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_amnt  funded_amnt  funded_amnt_inv        term  int_rate  installment grade sub_grade       emp_title emp_length home_ownership annual_inc verification_status loan_status pymnt_plan             purpose addr_state    dti delinq_2yrs inq_last_6mths open_acc\n",
       "0  2500       2500         2500.0            36 months  13.56     84.92        C     C1        Chef            10+ years  RENT           55000      Not Verified        Current     n          debt_consolidation  NY         18.24  0           1              9      \n",
       "1  30000      30000        30000.0           60 months  18.94     777.23       D     D2        Postmaster      10+ years  MORTGAGE       90000      Source Verified     Current     n          debt_consolidation  LA         26.52  0           0              13     \n",
       "2  5000       5000         5000.0            36 months  17.97     180.69       D     D1        Administrative  6 years    MORTGAGE       59280      Source Verified     Current     n          debt_consolidation  MI         10.51  0           0              8      \n",
       "3  4000       4000         4000.0            36 months  18.94     146.51       D     D2        IT Supervisor   10+ years  MORTGAGE       92000      Source Verified     Current     n          debt_consolidation  WA         16.74  0           0              10     \n",
       "4  30000      30000        30000.0           60 months  16.14     731.78       C     C4        Mechanic        10+ years  MORTGAGE       57250      Not Verified        Current     n          debt_consolidation  MD         26.35  0           0              12     "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dropping columns that have date or will not use in analysis\n",
    "# drop the cols\n",
    "drop_var=['issue_d','zip_code','earliest_cr_line']\n",
    "part1=part1.drop(*drop_var)\n",
    "print(size_shape(part1))\n",
    "part1.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- loan_amnt: integer (nullable = true)\n",
      " |-- funded_amnt: integer (nullable = true)\n",
      " |-- funded_amnt_inv: double (nullable = true)\n",
      " |-- term: string (nullable = true)\n",
      " |-- int_rate: double (nullable = true)\n",
      " |-- installment: double (nullable = true)\n",
      " |-- grade: string (nullable = true)\n",
      " |-- sub_grade: string (nullable = true)\n",
      " |-- emp_title: string (nullable = true)\n",
      " |-- emp_length: string (nullable = true)\n",
      " |-- home_ownership: string (nullable = true)\n",
      " |-- annual_inc: string (nullable = true)\n",
      " |-- verification_status: string (nullable = true)\n",
      " |-- loan_status: string (nullable = true)\n",
      " |-- pymnt_plan: string (nullable = true)\n",
      " |-- purpose: string (nullable = true)\n",
      " |-- addr_state: string (nullable = true)\n",
      " |-- dti: string (nullable = true)\n",
      " |-- delinq_2yrs: string (nullable = true)\n",
      " |-- inq_last_6mths: string (nullable = true)\n",
      " |-- open_acc: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "part1.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>summary</td>\n",
       "      <td>count</td>\n",
       "      <td>mean</td>\n",
       "      <td>stddev</td>\n",
       "      <td>min</td>\n",
       "      <td>max</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>loan_amnt</td>\n",
       "      <td>1206983</td>\n",
       "      <td>15830.35548139452</td>\n",
       "      <td>9555.658605549792</td>\n",
       "      <td>900</td>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>funded_amnt</td>\n",
       "      <td>1206983</td>\n",
       "      <td>15828.553219059424</td>\n",
       "      <td>9554.977988522249</td>\n",
       "      <td>900</td>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>funded_amnt_inv</td>\n",
       "      <td>1206983</td>\n",
       "      <td>15819.40287733609</td>\n",
       "      <td>9554.938771141058</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>term</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>36 months</td>\n",
       "      <td>60 months</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>int_rate</td>\n",
       "      <td>1206983</td>\n",
       "      <td>13.464910922524924</td>\n",
       "      <td>5.05230831155585</td>\n",
       "      <td>5.31</td>\n",
       "      <td>30.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>installment</td>\n",
       "      <td>1206983</td>\n",
       "      <td>458.06588146641303</td>\n",
       "      <td>272.13968537730744</td>\n",
       "      <td>7.61</td>\n",
       "      <td>1719.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>grade</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>sub_grade</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>A1</td>\n",
       "      <td>G5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_title</td>\n",
       "      <td>1101736</td>\n",
       "      <td>2835.36587628866</td>\n",
       "      <td>10391.035313133278</td>\n",
       "      <td>\\tCFO</td>\n",
       "      <td>👨‍🍳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_length</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1 year</td>\n",
       "      <td>n/a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>home_ownership</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ANY</td>\n",
       "      <td>RENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>annual_inc</td>\n",
       "      <td>1206983</td>\n",
       "      <td>78303.23068339817</td>\n",
       "      <td>139189.24715047498</td>\n",
       "      <td>0</td>\n",
       "      <td>99999.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>verification_status</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>Verified</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>loan_status</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Charged Off</td>\n",
       "      <td>Late</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>pymnt_plan</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>purpose</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Hilal Khalil Homaidan\"</td>\n",
       "      <td>wedding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>addr_state</td>\n",
       "      <td>1206982</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Friday the 13th Part VII: The New Blood (1988). The movies director and production coordinator are moving this straight to DVD &amp; Blu-Ray as to open the market right up for the viewers. The movie will be produced by late 2009 and seeing income as soon as October. As for myself I am a very responsible person and will stake my name on this project</td>\n",
       "      <td>shopping. This past year though she has been showing \"\"her age\"\".....   even though she has faithfully been taken care of by a friend that owns a garage...... there is a time to let go</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>dti</td>\n",
       "      <td>1205527</td>\n",
       "      <td>19.72749241389833</td>\n",
       "      <td>16.520479076307634</td>\n",
       "      <td>FUNCTIONAL</td>\n",
       "      <td>small_business</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>delinq_2yrs</td>\n",
       "      <td>1206982</td>\n",
       "      <td>0.30476758810623783</td>\n",
       "      <td>0.8725759910741755</td>\n",
       "      <td>GOAL-ORIENTED</td>\n",
       "      <td>small_business</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>inq_last_6mths</td>\n",
       "      <td>1206982</td>\n",
       "      <td>0.52426437849173</td>\n",
       "      <td>0.825048392970046</td>\n",
       "      <td>and decided to see what kind of rate I could get to consolidate my debt.  &lt;br/&gt;I haven't had enough time to research the pros and cons of a home equity line of credit</td>\n",
       "      <td>medical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>open_acc</td>\n",
       "      <td>1206978</td>\n",
       "      <td>11.699844236760125</td>\n",
       "      <td>5.8215632276588964</td>\n",
       "      <td>and have never had a problem since.  As you know</td>\n",
       "      <td>VA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           0                    1                   2                                                                                                                                                                                                                                                                                                                                                            3                                                                                                                                                                                         4\n",
       "summary              count    mean                 stddev              min                                                                                                                                                                                                                                                                                                                                                          max                                                                                                                                                                                     \n",
       "loan_amnt            1206983  15830.35548139452    9555.658605549792   900                                                                                                                                                                                                                                                                                                                                                          40000                                                                                                                                                                                   \n",
       "funded_amnt          1206983  15828.553219059424   9554.977988522249   900                                                                                                                                                                                                                                                                                                                                                          40000                                                                                                                                                                                   \n",
       "funded_amnt_inv      1206983  15819.40287733609    9554.938771141058   0.0                                                                                                                                                                                                                                                                                                                                                          40000.0                                                                                                                                                                                 \n",
       "term                 1206983  None                 None                 36 months                                                                                                                                                                                                                                                                                                                                                    60 months                                                                                                                                                                              \n",
       "int_rate             1206983  13.464910922524924   5.05230831155585    5.31                                                                                                                                                                                                                                                                                                                                                         30.99                                                                                                                                                                                   \n",
       "installment          1206983  458.06588146641303   272.13968537730744  7.61                                                                                                                                                                                                                                                                                                                                                         1719.83                                                                                                                                                                                 \n",
       "grade                1206983  None                 None                A                                                                                                                                                                                                                                                                                                                                                            G                                                                                                                                                                                       \n",
       "sub_grade            1206983  None                 None                A1                                                                                                                                                                                                                                                                                                                                                           G5                                                                                                                                                                                      \n",
       "emp_title            1101736  2835.36587628866     10391.035313133278  \\tCFO                                                                                                                                                                                                                                                                                                                                                        👨‍🍳                                                                                                                                                                                     \n",
       "emp_length           1206983  None                 None                1 year                                                                                                                                                                                                                                                                                                                                                       n/a                                                                                                                                                                                     \n",
       "home_ownership       1206983  None                 None                ANY                                                                                                                                                                                                                                                                                                                                                          RENT                                                                                                                                                                                    \n",
       "annual_inc           1206983  78303.23068339817    139189.24715047498  0                                                                                                                                                                                                                                                                                                                                                            99999.84                                                                                                                                                                                \n",
       "verification_status  1206983  None                 None                Not Verified                                                                                                                                                                                                                                                                                                                                                 Verified                                                                                                                                                                                \n",
       "loan_status          1206983  None                 None                Charged Off                                                                                                                                                                                                                                                                                                                                                  Late                                                                                                                                                                                    \n",
       "pymnt_plan           1206983  None                 None                n                                                                                                                                                                                                                                                                                                                                                            y                                                                                                                                                                                       \n",
       "purpose              1206983  None                 None                  Hilal Khalil Homaidan\"                                                                                                                                                                                                                                                                                                                                     wedding                                                                                                                                                                                 \n",
       "addr_state           1206982  None                 None                 Friday the 13th Part VII: The New Blood (1988). The movies director and production coordinator are moving this straight to DVD & Blu-Ray as to open the market right up for the viewers. The movie will be produced by late 2009 and seeing income as soon as October. As for myself I am a very responsible person and will stake my name on this project  shopping. This past year though she has been showing \"\"her age\"\".....   even though she has faithfully been taken care of by a friend that owns a garage...... there is a time to let go\n",
       "dti                  1205527  19.72749241389833    16.520479076307634   FUNCTIONAL                                                                                                                                                                                                                                                                                                                                                  small_business                                                                                                                                                                          \n",
       "delinq_2yrs          1206982  0.30476758810623783  0.8725759910741755   GOAL-ORIENTED                                                                                                                                                                                                                                                                                                                                               small_business                                                                                                                                                                          \n",
       "inq_last_6mths       1206982  0.52426437849173     0.825048392970046    and decided to see what kind of rate I could get to consolidate my debt.  <br/>I haven't had enough time to research the pros and cons of a home equity line of credit                                                                                                                                                                                      medical                                                                                                                                                                                 \n",
       "open_acc             1206978  11.699844236760125   5.8215632276588964   and have never had a problem since.  As you know                                                                                                                                                                                                                                                                                                            VA                                                                                                                                                                                      "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the summary of each col\n",
    "part1.describe().toPandas().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_amnt</th>\n",
       "      <th>funded_amnt</th>\n",
       "      <th>funded_amnt_inv</th>\n",
       "      <th>term</th>\n",
       "      <th>int_rate</th>\n",
       "      <th>installment</th>\n",
       "      <th>grade</th>\n",
       "      <th>sub_grade</th>\n",
       "      <th>emp_title</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>loan_status</th>\n",
       "      <th>pymnt_plan</th>\n",
       "      <th>purpose</th>\n",
       "      <th>addr_state</th>\n",
       "      <th>dti</th>\n",
       "      <th>delinq_2yrs</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>open_acc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>105247</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1456</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_amnt  funded_amnt  funded_amnt_inv  term  int_rate  installment  grade  sub_grade  emp_title  emp_length  home_ownership  annual_inc  verification_status  loan_status  pymnt_plan  purpose  addr_state   dti  delinq_2yrs  inq_last_6mths  open_acc\n",
       "0  0          0            0                0     0         0            0      0          105247     0           0               0           0                    0            0           0        1           1456  1            1               5       "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the missing values\n",
    "checkna_part1 = part1.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in part1.columns])\n",
    "checkna_part1.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Columns with missing values | the number of missing values | explanation of the type of data\n",
    "emp_title      | 105,247 | The job title supplied by the Borrower when applying for the loan.*\n",
    "\n",
    "title          | 12,520 | The loan title provided by the borrower\n",
    "\n",
    "addr_state     | 1 | The state provided by the borrower in the loan application\n",
    "\n",
    "dti            | 1,456 | A ratio calculated using the borrower’s total monthly debt payments on the total debt obligations, excluding mortgage and the requested LC loan, divided by the borrower’s self-reported monthly income.\n",
    "\n",
    "delinq_2yrs    | 1 | The number of 30+ days past-due incidences of delinquency in the borrower's credit file for the past 2 years\n",
    "\n",
    "inq_last_6mths | 1 | The number of inquiries in past 6 months (excluding auto and mortgage inquiries)\n",
    "\n",
    "open_acc       | 5 | The number of open credit lines in the borrower's credit file.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lets look at the columns thave null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-----+\n",
      "|           emp_title|count|\n",
      "+--------------------+-----+\n",
      "|   Exhibits director|    2|\n",
      "|                 CSR|  645|\n",
      "| Pharmacist Director|    1|\n",
      "|Lead Sales Consul...|    2|\n",
      "|     Clinic Director|   29|\n",
      "|           Physician|  991|\n",
      "|Journeyman Electr...|  112|\n",
      "|Mortgage Loan off...|    6|\n",
      "|Digital Marketing...|   46|\n",
      "|  Corporate Director|   16|\n",
      "|Physical therapis...|   39|\n",
      "|    GES Coordinator |    2|\n",
      "|      Dispatch Clerk|    5|\n",
      "|           machinist|  413|\n",
      "|   Brand Coordinator|    5|\n",
      "|    Dedicated Driver|    5|\n",
      "|         Bank Teller|   53|\n",
      "|  English Instructor|   18|\n",
      "| Route sales manager|    2|\n",
      "|        library tech|    4|\n",
      "+--------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n",
      "None\n",
      "+--------------------+-----+\n",
      "|          addr_state|count|\n",
      "+--------------------+-----+\n",
      "|                  SC|14948|\n",
      "|                  AZ|28068|\n",
      "|                  LA|14102|\n",
      "|                  MN|20722|\n",
      "|Advocate business...|    1|\n",
      "|                  NJ|45687|\n",
      "| and anyone this ...|    1|\n",
      "|Loan To Eliminate...|    1|\n",
      "|             guiding|    1|\n",
      "|                  DC| 2392|\n",
      "|                  OR|13005|\n",
      "|shopping. This pa...|    1|\n",
      "|The Beginning of ...|    1|\n",
      "|                  VA|33129|\n",
      "|                null|    1|\n",
      "|                  RI| 5289|\n",
      "| and help expand ...|    1|\n",
      "|A Better Type Of ...|    1|\n",
      "|                  KY|11961|\n",
      "|                  WY| 2361|\n",
      "+--------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n",
      "None\n",
      "+------+-----+\n",
      "|   dti|count|\n",
      "+------+-----+\n",
      "| 13.87|  500|\n",
      "|  7.16|  262|\n",
      "| 17.42|  498|\n",
      "| 19.06|  483|\n",
      "| 23.97|  433|\n",
      "|  10.7|  396|\n",
      "| 31.47|  207|\n",
      "| 28.37|  301|\n",
      "|   8.5|  345|\n",
      "| 34.63|  141|\n",
      "| 36.65|   80|\n",
      "| 28.79|  188|\n",
      "| 26.63|  306|\n",
      "| 34.71|  155|\n",
      "| 37.21|   76|\n",
      "| 30.86|  204|\n",
      "| 20.64|  534|\n",
      "| 48.04|   11|\n",
      "| 43.67|    8|\n",
      "|131.57|    1|\n",
      "+------+-----+\n",
      "only showing top 20 rows\n",
      "\n",
      "None\n",
      "+--------------------+------+\n",
      "|         delinq_2yrs| count|\n",
      "+--------------------+------+\n",
      "| and we have a gr...|     1|\n",
      "|                   7|  1113|\n",
      "|                  AZ|     1|\n",
      "|                  15|    51|\n",
      "|               12.24|     1|\n",
      "|                  11|   204|\n",
      "|                  42|     1|\n",
      "|               049xx|     1|\n",
      "|                   3| 15713|\n",
      "|                  30|     1|\n",
      "| some regulatory ...|     1|\n",
      "|                   8|   647|\n",
      "|Business Booster ...|     1|\n",
      "|                  22|     2|\n",
      "|                  28|     1|\n",
      "|                  16|    32|\n",
      "|                  35|     1|\n",
      "|                   0|984734|\n",
      "|MEDICAL CO-PAYS A...|     1|\n",
      "|                null|     1|\n",
      "+--------------------+------+\n",
      "only showing top 20 rows\n",
      "\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "print(part1.groupby(part1.emp_title).count().show())\n",
    "print(part1.groupby(part1.addr_state).count().show())\n",
    "print(part1.groupby(part1.dti).count().show())\n",
    "print(part1.groupby(part1.delinq_2yrs).count().show())\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+------+\n",
      "|      inq_last_6mths| count|\n",
      "+--------------------+------+\n",
      "|                   7|    12|\n",
      "|            Aug-1995|     1|\n",
      "| let’s take this ...|     1|\n",
      "|               850xx|     1|\n",
      "| and decided to s...|     1|\n",
      "|                  NJ|     1|\n",
      "|                   3| 31322|\n",
      "| clean credit his...|     1|\n",
      "|                   8|     4|\n",
      "|                   0|765822|\n",
      "|                null|     1|\n",
      "| my FICO score is...|     1|\n",
      "| my intention is ...|     1|\n",
      "|                   5|  2314|\n",
      "|                  NV|     1|\n",
      "|               18.24|     1|\n",
      "|000 in taxable re...|     1|\n",
      "|                   6|   243|\n",
      "| and not with mor...|     1|\n",
      "| but I do not pra...|     1|\n",
      "+--------------------+------+\n",
      "only showing top 20 rows\n",
      "\n",
      "None\n",
      "+--------------------+-----+\n",
      "|            open_acc|count|\n",
      "+--------------------+-----+\n",
      "| and have never h...|    1|\n",
      "|Debt Smash & Capi...|    1|\n",
      "|                   7|90921|\n",
      "|                  51|   47|\n",
      "|                  15|53365|\n",
      "|                  54|   20|\n",
      "|            Oct-1999|    1|\n",
      "|                  11|91247|\n",
      "|                 101|    1|\n",
      "|                  29| 3370|\n",
      "|                  69|    3|\n",
      "|                  42|  251|\n",
      "|                  87|    1|\n",
      "|                  64|    3|\n",
      "|                   3|19363|\n",
      "|                  30| 2757|\n",
      "|                  34| 1110|\n",
      "|                  59|   12|\n",
      "|            Oct-1997|    1|\n",
      "|                   8|97952|\n",
      "+--------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "print(part1.groupby(part1.inq_last_6mths).count().show())\n",
    "print(part1.groupby(part1.open_acc).count().show())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-----+\n",
      "|             purpose|count|\n",
      "+--------------------+-----+\n",
      "| MD\"\"... approx. ...|    1|\n",
      "| i wasn't even bo...|    1|\n",
      "| it is a historic...|    1|\n",
      "| expensive electr...|    1|\n",
      "| you may feel con...|    1|\n",
      "| so that our comp...|    1|\n",
      "|             wedding|  284|\n",
      "|,debt_consolidati...|    1|\n",
      "| at the time of t...|    1|\n",
      "|         educational|   55|\n",
      "| progressive mult...|    1|\n",
      "|          550 amount|    1|\n",
      "| meaning the metr...|    1|\n",
      "| it was too late ...|    1|\n",
      "|  and another one...|    1|\n",
      "|               proms|    1|\n",
      "| affordable famil...|    1|\n",
      "|               other|79362|\n",
      "|      small_business|13855|\n",
      "| which is their l...|    1|\n",
      "+--------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n",
      "None\n",
      "+----------+-------+\n",
      "|pymnt_plan|  count|\n",
      "+----------+-------+\n",
      "|         n|1206304|\n",
      "|         y|    679|\n",
      "+----------+-------+\n",
      "\n",
      "None\n",
      "+-------------------+------+\n",
      "|verification_status| count|\n",
      "+-------------------+------+\n",
      "|           Verified|316069|\n",
      "|    Source Verified|484749|\n",
      "|       Not Verified|406165|\n",
      "+-------------------+------+\n",
      "\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# CHeck the columns to see if they are categories\n",
    "print(part1.groupby(part1.purpose).count().show())\n",
    "print(part1.groupby(part1.pymnt_plan).count().show())\n",
    "print(part1.groupby(part1.verification_status).count().show())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+\n",
      "|      term| count|\n",
      "+----------+------+\n",
      "| 36 months|772572|\n",
      "| 60 months|434411|\n",
      "+----------+------+\n",
      "\n",
      "None\n",
      "+-----+------+\n",
      "|grade| count|\n",
      "+-----+------+\n",
      "|    F| 24101|\n",
      "|    E| 78099|\n",
      "|    B|332113|\n",
      "|    D|185921|\n",
      "|    C|359555|\n",
      "|    A|219782|\n",
      "|    G|  7412|\n",
      "+-----+------+\n",
      "\n",
      "None\n",
      "+---------+-----+\n",
      "|sub_grade|count|\n",
      "+---------+-----+\n",
      "|       D5|28162|\n",
      "|       F2| 5351|\n",
      "|       B4|70645|\n",
      "|       A2|35334|\n",
      "|       E4|13337|\n",
      "|       B2|62685|\n",
      "|       C3|72139|\n",
      "|       D1|45204|\n",
      "|       C4|72228|\n",
      "|       F1| 7649|\n",
      "|       D3|37770|\n",
      "|       F5| 3132|\n",
      "|       G2| 1576|\n",
      "|       B1|63317|\n",
      "|       B3|62023|\n",
      "|       E5|14229|\n",
      "|       C5|67563|\n",
      "|       G3| 1276|\n",
      "|       A4|48675|\n",
      "|       F4| 3566|\n",
      "+---------+-----+\n",
      "only showing top 20 rows\n",
      "\n",
      "None\n",
      "+--------------+------+\n",
      "|home_ownership| count|\n",
      "+--------------+------+\n",
      "|           OWN|140935|\n",
      "|          RENT|493054|\n",
      "|      MORTGAGE|572185|\n",
      "|           ANY|   774|\n",
      "|         OTHER|    27|\n",
      "|          NONE|     8|\n",
      "+--------------+------+\n",
      "\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# Check the columns to see if they are categories\n",
    "print(part1.groupby(part1.term).count().show())\n",
    "print(part1.groupby(part1.grade).count().show())\n",
    "print(part1.groupby(part1.sub_grade).count().show())\n",
    "print(part1.groupby(part1.home_ownership).count().show())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- term: string (nullable = true)\n",
      " |-- grade: string (nullable = true)\n",
      " |-- sub_grade: string (nullable = true)\n",
      " |-- home_ownership: string (nullable = true)\n",
      " |-- verification_status: string (nullable = true)\n",
      " |-- loan_status: string (nullable = true)\n",
      " |-- pymnt_plan: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Make the columns into categories\n",
    "\n",
    "cat_col=['term','grade','sub_grade','home_ownership','verification_status','loan_status','pymnt_plan']\n",
    "part1_cat_col=part1.select(cat_col)\n",
    "part1_cat_col.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now we had columns that had bad data but casting the correct type should solve issue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows: 1206983\n",
      "columns: 17\n",
      "None\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>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>home_ownership</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>loan_status</th>\n",
       "      <th>pymnt_plan</th>\n",
       "      <th>dti</th>\n",
       "      <th>delinq_2yrs</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>open_acc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2500</td>\n",
       "      <td>2500</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>13.56</td>\n",
       "      <td>84.92</td>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>RENT</td>\n",
       "      <td>55000</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>18.24</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>18.94</td>\n",
       "      <td>777.23</td>\n",
       "      <td>D</td>\n",
       "      <td>D2</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>90000</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>26.52</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>17.97</td>\n",
       "      <td>180.69</td>\n",
       "      <td>D</td>\n",
       "      <td>D1</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>59280</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>10.51</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4000</td>\n",
       "      <td>4000</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>18.94</td>\n",
       "      <td>146.51</td>\n",
       "      <td>D</td>\n",
       "      <td>D2</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>92000</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>16.74</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>16.14</td>\n",
       "      <td>731.78</td>\n",
       "      <td>C</td>\n",
       "      <td>C4</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>57250</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>26.35</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_amnt  funded_amnt  funded_amnt_inv        term  int_rate  installment grade sub_grade home_ownership annual_inc verification_status loan_status pymnt_plan    dti delinq_2yrs inq_last_6mths open_acc\n",
       "0  2500       2500         2500.0            36 months  13.56     84.92        C     C1        RENT           55000      Not Verified        Current     n          18.24  0           1              9      \n",
       "1  30000      30000        30000.0           60 months  18.94     777.23       D     D2        MORTGAGE       90000      Source Verified     Current     n          26.52  0           0              13     \n",
       "2  5000       5000         5000.0            36 months  17.97     180.69       D     D1        MORTGAGE       59280      Source Verified     Current     n          10.51  0           0              8      \n",
       "3  4000       4000         4000.0            36 months  18.94     146.51       D     D2        MORTGAGE       92000      Source Verified     Current     n          16.74  0           0              10     \n",
       "4  30000      30000        30000.0           60 months  16.14     731.78       C     C4        MORTGAGE       57250      Not Verified        Current     n          26.35  0           0              12     "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# After looking at the values, we have some more columns to drop\n",
    "drop_var_2=['emp_title',\"title\",'purpose','emp_length','addr_state']\n",
    "part1=part1.drop(*drop_var_2)\n",
    "print(size_shape(part1))\n",
    "part1.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at changing the numerical cols to the right type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "part1_num_col=part1.drop(*cat_col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- loan_amnt: integer (nullable = true)\n",
      " |-- funded_amnt: integer (nullable = true)\n",
      " |-- funded_amnt_inv: double (nullable = true)\n",
      " |-- int_rate: double (nullable = true)\n",
      " |-- installment: double (nullable = true)\n",
      " |-- annual_inc: string (nullable = true)\n",
      " |-- dti: string (nullable = true)\n",
      " |-- delinq_2yrs: string (nullable = true)\n",
      " |-- inq_last_6mths: string (nullable = true)\n",
      " |-- open_acc: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "part1_num_col.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.types import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "part1 = part1.withColumn('loan_amnt', part1['loan_amnt'].cast(FloatType()))\n",
    "part1 = part1.withColumn('funded_amnt', part1['funded_amnt'].cast(FloatType()))\n",
    "part1 = part1.withColumn('funded_amnt_inv', part1['funded_amnt_inv'].cast(FloatType()))\n",
    "part1 = part1.withColumn('int_rate',part1['int_rate'].cast(FloatType()))\n",
    "part1 = part1.withColumn('installment', part1['installment'].cast(FloatType()))\n",
    "part1 = part1.withColumn('annual_inc', part1['annual_inc'].cast(FloatType()))\n",
    "part1 = part1.withColumn('dti', part1['dti'].cast(FloatType()))\n",
    "part1 = part1.withColumn('delinq_2yrs', part1['delinq_2yrs'].cast(IntegerType()))\n",
    "part1 = part1.withColumn('inq_last_6mths', part1['inq_last_6mths'].cast(IntegerType()))\n",
    "part1 = part1.withColumn(\"open_acc\",part1[\"open_acc\"].cast(IntegerType()))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- loan_amnt: float (nullable = true)\n",
      " |-- funded_amnt: float (nullable = true)\n",
      " |-- funded_amnt_inv: float (nullable = true)\n",
      " |-- term: string (nullable = true)\n",
      " |-- int_rate: float (nullable = true)\n",
      " |-- installment: float (nullable = true)\n",
      " |-- grade: string (nullable = true)\n",
      " |-- sub_grade: string (nullable = true)\n",
      " |-- home_ownership: string (nullable = true)\n",
      " |-- annual_inc: float (nullable = true)\n",
      " |-- verification_status: string (nullable = true)\n",
      " |-- loan_status: string (nullable = true)\n",
      " |-- pymnt_plan: string (nullable = true)\n",
      " |-- dti: float (nullable = true)\n",
      " |-- delinq_2yrs: integer (nullable = true)\n",
      " |-- inq_last_6mths: integer (nullable = true)\n",
      " |-- open_acc: integer (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "part1.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_amnt</th>\n",
       "      <th>funded_amnt</th>\n",
       "      <th>funded_amnt_inv</th>\n",
       "      <th>term</th>\n",
       "      <th>int_rate</th>\n",
       "      <th>installment</th>\n",
       "      <th>grade</th>\n",
       "      <th>sub_grade</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>loan_status</th>\n",
       "      <th>pymnt_plan</th>\n",
       "      <th>dti</th>\n",
       "      <th>delinq_2yrs</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>open_acc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1489</td>\n",
       "      <td>31</td>\n",
       "      <td>24</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_amnt  funded_amnt  funded_amnt_inv  term  int_rate  installment  grade  sub_grade  home_ownership  annual_inc  verification_status  loan_status  pymnt_plan   dti  delinq_2yrs  inq_last_6mths  open_acc\n",
       "0  0          0            0                0     0         0            0      0          0               0           0                    0            0           1489  31           24              23      "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "part1.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in part1.columns]).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Columns with missing values | the number of missing values | explanation of the type of data\n",
    "\n",
    "dti            | 1,489 | A ratio calculated using the borrower’s total monthly debt payments on the total debt obligations, excluding mortgage and the requested LC loan, divided by the borrower’s self-reported monthly income.\n",
    "\n",
    "delinq_2yrs    | 31 | The number of 30+ days past-due incidences of delinquency in the borrower's credit file for the past 2 years\n",
    "\n",
    "inq_last_6mths | 24 | The number of inquiries in past 6 months (excluding auto and mortgage inquiries)\n",
    "\n",
    "open_acc       | 23 | The number of open credit lines in the borrower's credit file.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lets fill in the values that are missing with the mean value\n",
    "\n",
    "dti_mean=part1.select(fn.avg('dti')).collect()\n",
    "part1 = part1.na.fill({'dti':dti_mean[0][0]})\n",
    "\n",
    "delinq_2yrs_mean=part1.select(fn.avg('delinq_2yrs')).collect()\n",
    "part1 = part1.na.fill({'delinq_2yrs':delinq_2yrs_mean[0][0]})\n",
    "\n",
    "inq_last_6mths_mean=part1.select(fn.avg('inq_last_6mths')).collect()\n",
    "part1 = part1.na.fill({'inq_last_6mths':inq_last_6mths_mean[0][0]})\n",
    "\n",
    "open_acc_mean=part1.select(fn.avg('open_acc')).collect()\n",
    "part1 = part1.na.fill({'open_acc':open_acc_mean[0][0]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_amnt</th>\n",
       "      <th>funded_amnt</th>\n",
       "      <th>funded_amnt_inv</th>\n",
       "      <th>term</th>\n",
       "      <th>int_rate</th>\n",
       "      <th>installment</th>\n",
       "      <th>grade</th>\n",
       "      <th>sub_grade</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>loan_status</th>\n",
       "      <th>pymnt_plan</th>\n",
       "      <th>dti</th>\n",
       "      <th>delinq_2yrs</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>open_acc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_amnt  funded_amnt  funded_amnt_inv  term  int_rate  installment  grade  sub_grade  home_ownership  annual_inc  verification_status  loan_status  pymnt_plan  dti  delinq_2yrs  inq_last_6mths  open_acc\n",
       "0  0          0            0                0     0         0            0      0          0               0           0                    0            0           0    0            0               0       "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Recheck for missing values\n",
    "part1.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in part1.columns]).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rows: 1206983\n",
      "columns: 17\n"
     ]
    }
   ],
   "source": [
    "size_shape(part1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>summary</td>\n",
       "      <td>count</td>\n",
       "      <td>mean</td>\n",
       "      <td>stddev</td>\n",
       "      <td>min</td>\n",
       "      <td>max</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>loan_amnt</td>\n",
       "      <td>1206983</td>\n",
       "      <td>15830.35548139452</td>\n",
       "      <td>9555.658605549792</td>\n",
       "      <td>900.0</td>\n",
       "      <td>40000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>funded_amnt</td>\n",
       "      <td>1206983</td>\n",
       "      <td>15828.553219059424</td>\n",
       "      <td>9554.977988522249</td>\n",
       "      <td>900.0</td>\n",
       "      <td>40000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>funded_amnt_inv</td>\n",
       "      <td>1206983</td>\n",
       "      <td>15819.402877311093</td>\n",
       "      <td>9554.93877112251</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>term</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>36 months</td>\n",
       "      <td>60 months</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>int_rate</td>\n",
       "      <td>1206983</td>\n",
       "      <td>13.464910887388545</td>\n",
       "      <td>5.052308304807695</td>\n",
       "      <td>5.31</td>\n",
       "      <td>30.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>installment</td>\n",
       "      <td>1206983</td>\n",
       "      <td>458.065881507693</td>\n",
       "      <td>272.13968553916976</td>\n",
       "      <td>7.61</td>\n",
       "      <td>1719.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>grade</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>sub_grade</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>A1</td>\n",
       "      <td>G5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>home_ownership</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ANY</td>\n",
       "      <td>RENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>annual_inc</td>\n",
       "      <td>1206983</td>\n",
       "      <td>78303.23068381898</td>\n",
       "      <td>139189.24715062467</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.1E8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>verification_status</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>Verified</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>loan_status</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Charged Off</td>\n",
       "      <td>Late</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>pymnt_plan</td>\n",
       "      <td>1206983</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>dti</td>\n",
       "      <td>1206983</td>\n",
       "      <td>19.72749241566551</td>\n",
       "      <td>16.510285643285677</td>\n",
       "      <td>0.0</td>\n",
       "      <td>999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>delinq_2yrs</td>\n",
       "      <td>1206983</td>\n",
       "      <td>0.30475822774637257</td>\n",
       "      <td>0.8725369152244395</td>\n",
       "      <td>0</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>inq_last_6mths</td>\n",
       "      <td>1206983</td>\n",
       "      <td>0.5242534484744192</td>\n",
       "      <td>0.8250319563368279</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>open_acc</td>\n",
       "      <td>1206983</td>\n",
       "      <td>11.699830900683772</td>\n",
       "      <td>5.8215085617376126</td>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           0                    1                   2             3           4\n",
       "summary              count    mean                 stddev              min           max       \n",
       "loan_amnt            1206983  15830.35548139452    9555.658605549792   900.0         40000.0   \n",
       "funded_amnt          1206983  15828.553219059424   9554.977988522249   900.0         40000.0   \n",
       "funded_amnt_inv      1206983  15819.402877311093   9554.93877112251    0.0           40000.0   \n",
       "term                 1206983  None                 None                 36 months     60 months\n",
       "int_rate             1206983  13.464910887388545   5.052308304807695   5.31          30.99     \n",
       "installment          1206983  458.065881507693     272.13968553916976  7.61          1719.83   \n",
       "grade                1206983  None                 None                A             G         \n",
       "sub_grade            1206983  None                 None                A1            G5        \n",
       "home_ownership       1206983  None                 None                ANY           RENT      \n",
       "annual_inc           1206983  78303.23068381898    139189.24715062467  0.0           1.1E8     \n",
       "verification_status  1206983  None                 None                Not Verified  Verified  \n",
       "loan_status          1206983  None                 None                Charged Off   Late      \n",
       "pymnt_plan           1206983  None                 None                n             y         \n",
       "dti                  1206983  19.72749241566551    16.510285643285677  0.0           999.0     \n",
       "delinq_2yrs          1206983  0.30475822774637257  0.8725369152244395  0             58        \n",
       "inq_last_6mths       1206983  0.5242534484744192   0.8250319563368279  0             20        \n",
       "open_acc             1206983  11.699830900683772   5.8215085617376126  0             101       "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "part1.describe().toPandas().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert string ro index label using the StringIndexer\n",
    "cat_col_list=['term','grade','sub_grade','home_ownership','verification_status','loan_status','pymnt_plan']\n",
    "stringIndexer = feature.StringIndexer(inputCols=[cols for cols in cat_col_list],\n",
    "                                      outputCols=[\"encoded_\" + cols for cols in cat_col_list])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# now lets use numeric columns\n",
    "numeric_cols = [cols for cols in part1.columns if cols not in cat_col_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['loan_amnt',\n",
       " 'funded_amnt',\n",
       " 'funded_amnt_inv',\n",
       " 'int_rate',\n",
       " 'installment',\n",
       " 'annual_inc',\n",
       " 'dti',\n",
       " 'delinq_2yrs',\n",
       " 'inq_last_6mths',\n",
       " 'open_acc']"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "numeric_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define the VectorAssembler object\n",
    "va = feature.VectorAssembler(inputCols=[cols for cols in numeric_cols], outputCol=\"features\")\n",
    "# define the StandardScaler object\n",
    "sScaler = feature.StandardScaler(withMean=True, withStd=True, inputCol=\"features\", outputCol=\"scaled_features\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define the pipeline which contains 3 stages: stringindexer, VectorAssembler and StandardScaler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "part1_pipe=Pipeline(stages=[stringIndexer, va, sScaler])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fit the pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "fitted_part1 = part1_pipe.fit(part1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Apply the pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "transformed_part1 = fitted_part1.transform(part1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_amnt</th>\n",
       "      <th>funded_amnt</th>\n",
       "      <th>funded_amnt_inv</th>\n",
       "      <th>term</th>\n",
       "      <th>int_rate</th>\n",
       "      <th>installment</th>\n",
       "      <th>grade</th>\n",
       "      <th>sub_grade</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>loan_status</th>\n",
       "      <th>pymnt_plan</th>\n",
       "      <th>dti</th>\n",
       "      <th>delinq_2yrs</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>open_acc</th>\n",
       "      <th>encoded_pymnt_plan</th>\n",
       "      <th>encoded_grade</th>\n",
       "      <th>encoded_verification_status</th>\n",
       "      <th>encoded_home_ownership</th>\n",
       "      <th>encoded_loan_status</th>\n",
       "      <th>encoded_term</th>\n",
       "      <th>encoded_sub_grade</th>\n",
       "      <th>features</th>\n",
       "      <th>scaled_features</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>13.560000</td>\n",
       "      <td>84.919998</td>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>RENT</td>\n",
       "      <td>55000.0</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>18.24</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[2500.0, 2500.0, 2500.0, 13.5600004196167, 84.91999816894531, 55000.0, 18.239999771118164, 0.0, 1.0, 9.0]</td>\n",
       "      <td>[-1.39502215720143, -1.3949329067079026, -1.393980976368545, 0.01882100744677316, -1.3711557085085266, -0.1674211992726832, -0.09009490669546945, -0.3492783198381707, 0.5766401505681225, -0.4637682607612515]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>18.940001</td>\n",
       "      <td>777.229980</td>\n",
       "      <td>D</td>\n",
       "      <td>D2</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>90000.0</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>26.52</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>[30000.0, 30000.0, 30000.0, 18.940000534057617, 777.22998046875, 90000.0, 26.520000457763672, 0.0, 0.0, 13.0]</td>\n",
       "      <td>[1.4828537836602895, 1.4831480300597057, 1.484111773227308, 1.083680827921584, 1.1727951339721765, 0.08403500669504162, 0.4114106920288488, -0.3492783198381707, -0.6354341119126155, 0.22333886234604042]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>17.969999</td>\n",
       "      <td>180.690002</td>\n",
       "      <td>D</td>\n",
       "      <td>D1</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>59280.0</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>10.51</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>[5000.0, 5000.0, 5000.0, 17.969999313354492, 180.69000244140625, 59280.0, 10.510000228881836, 0.0, 0.0, 8.0]</td>\n",
       "      <td>[-1.1333970716685464, -1.1332891851835747, -1.13233618095074, 0.8916891357716636, -1.01924083037262, -0.13667169751434427, -0.558287868903844, -0.3492783198381707, -0.6354341119126155, -0.6355450415380745]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>18.940001</td>\n",
       "      <td>146.509995</td>\n",
       "      <td>D</td>\n",
       "      <td>D2</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>92000.0</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>16.74</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>[4000.0, 4000.0, 4000.0, 18.940000534057617, 146.50999450683594, 92000.0, 16.739999771118164, 0.0, 0.0, 10.0]</td>\n",
       "      <td>[-1.2380471058817, -1.2379466737933058, -1.2369940991178618, 1.083680827921584, -1.1448381237877718, 0.09840393275034018, -0.18094736269825284, -0.3492783198381707, -0.6354341119126155, -0.29199147998442854]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>16.139999</td>\n",
       "      <td>731.780029</td>\n",
       "      <td>C</td>\n",
       "      <td>C4</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>57250.0</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>Current</td>\n",
       "      <td>n</td>\n",
       "      <td>26.35</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>[30000.0, 30000.0, 30000.0, 16.139999389648438, 731.780029296875, 57250.0, 26.350000381469727, 0.0, 0.0, 12.0]</td>\n",
       "      <td>[1.4828537836602895, 1.4831480300597057, 1.484111773227308, 0.52947847614809, 1.0057854930158108, -0.15125615746047233, 0.4011140757275385, -0.3492783198381707, -0.6354341119126155, 0.05156208156921744]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_amnt  funded_amnt  funded_amnt_inv        term   int_rate  installment grade sub_grade home_ownership  annual_inc verification_status loan_status pymnt_plan    dti  delinq_2yrs  inq_last_6mths  open_acc  encoded_pymnt_plan  encoded_grade  encoded_verification_status  encoded_home_ownership  encoded_loan_status  encoded_term  encoded_sub_grade                                                                                                        features                                                                                                                                                                                                  scaled_features\n",
       "0  2500.0     2500.0       2500.0            36 months  13.560000  84.919998    C     C1        RENT           55000.0     Not Verified        Current     n          18.24  0            1               9         0.0                 0.0            1.0                          1.0                     0.0                  0.0           0.0                [2500.0, 2500.0, 2500.0, 13.5600004196167, 84.91999816894531, 55000.0, 18.239999771118164, 0.0, 1.0, 9.0]       [-1.39502215720143, -1.3949329067079026, -1.393980976368545, 0.01882100744677316, -1.3711557085085266, -0.1674211992726832, -0.09009490669546945, -0.3492783198381707, 0.5766401505681225, -0.4637682607612515]\n",
       "1  30000.0    30000.0      30000.0           60 months  18.940001  777.229980   D     D2        MORTGAGE       90000.0     Source Verified     Current     n          26.52  0            0               13        0.0                 3.0            0.0                          0.0                     0.0                  1.0           14.0               [30000.0, 30000.0, 30000.0, 18.940000534057617, 777.22998046875, 90000.0, 26.520000457763672, 0.0, 0.0, 13.0]   [1.4828537836602895, 1.4831480300597057, 1.484111773227308, 1.083680827921584, 1.1727951339721765, 0.08403500669504162, 0.4114106920288488, -0.3492783198381707, -0.6354341119126155, 0.22333886234604042]     \n",
       "2  5000.0     5000.0       5000.0            36 months  17.969999  180.690002   D     D1        MORTGAGE       59280.0     Source Verified     Current     n          10.51  0            0               8         0.0                 3.0            0.0                          0.0                     0.0                  0.0           13.0               [5000.0, 5000.0, 5000.0, 17.969999313354492, 180.69000244140625, 59280.0, 10.510000228881836, 0.0, 0.0, 8.0]    [-1.1333970716685464, -1.1332891851835747, -1.13233618095074, 0.8916891357716636, -1.01924083037262, -0.13667169751434427, -0.558287868903844, -0.3492783198381707, -0.6354341119126155, -0.6355450415380745]  \n",
       "3  4000.0     4000.0       4000.0            36 months  18.940001  146.509995   D     D2        MORTGAGE       92000.0     Source Verified     Current     n          16.74  0            0               10        0.0                 3.0            0.0                          0.0                     0.0                  0.0           14.0               [4000.0, 4000.0, 4000.0, 18.940000534057617, 146.50999450683594, 92000.0, 16.739999771118164, 0.0, 0.0, 10.0]   [-1.2380471058817, -1.2379466737933058, -1.2369940991178618, 1.083680827921584, -1.1448381237877718, 0.09840393275034018, -0.18094736269825284, -0.3492783198381707, -0.6354341119126155, -0.29199147998442854]\n",
       "4  30000.0    30000.0      30000.0           60 months  16.139999  731.780029   C     C4        MORTGAGE       57250.0     Not Verified        Current     n          26.35  0            0               12        0.0                 0.0            1.0                          0.0                     0.0                  1.0           2.0                [30000.0, 30000.0, 30000.0, 16.139999389648438, 731.780029296875, 57250.0, 26.350000381469727, 0.0, 0.0, 12.0]  [1.4828537836602895, 1.4831480300597057, 1.484111773227308, 0.52947847614809, 1.0057854930158108, -0.15125615746047233, 0.4011140757275385, -0.3492783198381707, -0.6354341119126155, 0.05156208156921744]     "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transformed_part1.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}