Step_7) Expected Loss.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import data.\n",
    "loan_data_preprocessed_backup = pd.read_csv('data/loan_data_2007_2014_preprocessed.csv')\n",
    "loan_data_preprocessed = loan_data_preprocessed_backup.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(466285, 209)\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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</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>...</th>\n",
       "      <th>addr_state:UT</th>\n",
       "      <th>addr_state:VA</th>\n",
       "      <th>addr_state:VT</th>\n",
       "      <th>addr_state:WA</th>\n",
       "      <th>addr_state:WI</th>\n",
       "      <th>addr_state:WV</th>\n",
       "      <th>addr_state:WY</th>\n",
       "      <th>initial_list_status:f</th>\n",
       "      <th>initial_list_status:w</th>\n",
       "      <th>good_bad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1077501</td>\n",
       "      <td>1296599</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000</td>\n",
       "      <td>4975.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>10.65</td>\n",
       "      <td>162.87</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1077430</td>\n",
       "      <td>1314167</td>\n",
       "      <td>2500</td>\n",
       "      <td>2500</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>15.27</td>\n",
       "      <td>59.83</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1077175</td>\n",
       "      <td>1313524</td>\n",
       "      <td>2400</td>\n",
       "      <td>2400</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>15.96</td>\n",
       "      <td>84.33</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1076863</td>\n",
       "      <td>1277178</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>13.49</td>\n",
       "      <td>339.31</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1075358</td>\n",
       "      <td>1311748</td>\n",
       "      <td>3000</td>\n",
       "      <td>3000</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>12.69</td>\n",
       "      <td>67.79</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 209 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Unnamed: 0.1       id  member_id  loan_amnt  funded_amnt  \\\n",
       "0           0             0  1077501    1296599       5000         5000   \n",
       "1           1             1  1077430    1314167       2500         2500   \n",
       "2           2             2  1077175    1313524       2400         2400   \n",
       "3           3             3  1076863    1277178      10000        10000   \n",
       "4           4             4  1075358    1311748       3000         3000   \n",
       "\n",
       "   funded_amnt_inv        term  int_rate  installment  ... addr_state:UT  \\\n",
       "0           4975.0   36 months     10.65       162.87  ...             0   \n",
       "1           2500.0   60 months     15.27        59.83  ...             0   \n",
       "2           2400.0   36 months     15.96        84.33  ...             0   \n",
       "3          10000.0   36 months     13.49       339.31  ...             0   \n",
       "4           3000.0   60 months     12.69        67.79  ...             0   \n",
       "\n",
       "  addr_state:VA addr_state:VT addr_state:WA addr_state:WI  addr_state:WV  \\\n",
       "0             0             0             0             0              0   \n",
       "1             0             0             0             0              0   \n",
       "2             0             0             0             0              0   \n",
       "3             0             0             0             0              0   \n",
       "4             0             0             0             0              0   \n",
       "\n",
       "  addr_state:WY initial_list_status:f initial_list_status:w good_bad  \n",
       "0             0                     1                     0        1  \n",
       "1             0                     1                     0        0  \n",
       "2             0                     1                     0        1  \n",
       "3             0                     1                     0        1  \n",
       "4             0                     1                     0        1  \n",
       "\n",
       "[5 rows x 209 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(loan_data_preprocessed.shape)\n",
    "loan_data_preprocessed.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unnamed: 0                                                              0\n",
       "Unnamed: 0.1                                                            0\n",
       "id                                                                      0\n",
       "member_id                                                               0\n",
       "loan_amnt                                                               0\n",
       "funded_amnt                                                             0\n",
       "funded_amnt_inv                                                         0\n",
       "term                                                                    0\n",
       "int_rate                                                                0\n",
       "installment                                                             0\n",
       "grade                                                                   0\n",
       "sub_grade                                                               0\n",
       "emp_title                                                           27588\n",
       "emp_length                                                          21008\n",
       "home_ownership                                                          0\n",
       "annual_inc                                                              0\n",
       "verification_status                                                     0\n",
       "issue_d                                                                 0\n",
       "loan_status                                                             0\n",
       "pymnt_plan                                                              0\n",
       "url                                                                     0\n",
       "desc                                                               340302\n",
       "purpose                                                                 0\n",
       "title                                                                  20\n",
       "zip_code                                                                0\n",
       "addr_state                                                              0\n",
       "dti                                                                     0\n",
       "delinq_2yrs                                                             0\n",
       "earliest_cr_line                                                       29\n",
       "inq_last_6mths                                                          0\n",
       "mths_since_last_delinq                                             250351\n",
       "mths_since_last_record                                             403647\n",
       "open_acc                                                                0\n",
       "pub_rec                                                                 0\n",
       "revol_bal                                                               0\n",
       "revol_util                                                            340\n",
       "total_acc                                                               0\n",
       "initial_list_status                                                     0\n",
       "out_prncp                                                               0\n",
       "out_prncp_inv                                                           0\n",
       "total_pymnt                                                             0\n",
       "total_pymnt_inv                                                         0\n",
       "total_rec_prncp                                                         0\n",
       "total_rec_int                                                           0\n",
       "total_rec_late_fee                                                      0\n",
       "recoveries                                                              0\n",
       "collection_recovery_fee                                                 0\n",
       "last_pymnt_d                                                          376\n",
       "last_pymnt_amnt                                                         0\n",
       "next_pymnt_d                                                       227214\n",
       "last_credit_pull_d                                                     42\n",
       "collections_12_mths_ex_med                                            145\n",
       "mths_since_last_major_derog                                        367311\n",
       "policy_code                                                             0\n",
       "application_type                                                        0\n",
       "annual_inc_joint                                                   466285\n",
       "dti_joint                                                          466285\n",
       "verification_status_joint                                          466285\n",
       "acc_now_delinq                                                          0\n",
       "tot_coll_amt                                                        70276\n",
       "tot_cur_bal                                                         70276\n",
       "open_acc_6m                                                        466285\n",
       "open_il_6m                                                         466285\n",
       "open_il_12m                                                        466285\n",
       "open_il_24m                                                        466285\n",
       "mths_since_rcnt_il                                                 466285\n",
       "total_bal_il                                                       466285\n",
       "il_util                                                            466285\n",
       "open_rv_12m                                                        466285\n",
       "open_rv_24m                                                        466285\n",
       "max_bal_bc                                                         466285\n",
       "all_util                                                           466285\n",
       "total_rev_hi_lim                                                        0\n",
       "inq_fi                                                             466285\n",
       "total_cu_tl                                                        466285\n",
       "inq_last_12m                                                       466285\n",
       "emp_length_int                                                          0\n",
       "earliest_cr_line_date                                                  29\n",
       "mths_since_earliest_cr_line                                             0\n",
       "term_int                                                                0\n",
       "issue_d_date                                                            0\n",
       "mths_since_issue_d                                                      0\n",
       "grade:A                                                                 0\n",
       "grade:B                                                                 0\n",
       "grade:C                                                                 0\n",
       "grade:D                                                                 0\n",
       "grade:E                                                                 0\n",
       "grade:F                                                                 0\n",
       "grade:G                                                                 0\n",
       "sub_grade:A1                                                            0\n",
       "sub_grade:A2                                                            0\n",
       "sub_grade:A3                                                            0\n",
       "sub_grade:A4                                                            0\n",
       "sub_grade:A5                                                            0\n",
       "sub_grade:B1                                                            0\n",
       "sub_grade:B2                                                            0\n",
       "sub_grade:B3                                                            0\n",
       "sub_grade:B4                                                            0\n",
       "sub_grade:B5                                                            0\n",
       "sub_grade:C1                                                            0\n",
       "sub_grade:C2                                                            0\n",
       "sub_grade:C3                                                            0\n",
       "sub_grade:C4                                                            0\n",
       "sub_grade:C5                                                            0\n",
       "sub_grade:D1                                                            0\n",
       "sub_grade:D2                                                            0\n",
       "sub_grade:D3                                                            0\n",
       "sub_grade:D4                                                            0\n",
       "sub_grade:D5                                                            0\n",
       "sub_grade:E1                                                            0\n",
       "sub_grade:E2                                                            0\n",
       "sub_grade:E3                                                            0\n",
       "sub_grade:E4                                                            0\n",
       "sub_grade:E5                                                            0\n",
       "sub_grade:F1                                                            0\n",
       "sub_grade:F2                                                            0\n",
       "sub_grade:F3                                                            0\n",
       "sub_grade:F4                                                            0\n",
       "sub_grade:F5                                                            0\n",
       "sub_grade:G1                                                            0\n",
       "sub_grade:G2                                                            0\n",
       "sub_grade:G3                                                            0\n",
       "sub_grade:G4                                                            0\n",
       "sub_grade:G5                                                            0\n",
       "home_ownership:ANY                                                      0\n",
       "home_ownership:MORTGAGE                                                 0\n",
       "home_ownership:NONE                                                     0\n",
       "home_ownership:OTHER                                                    0\n",
       "home_ownership:OWN                                                      0\n",
       "home_ownership:RENT                                                     0\n",
       "verification_status:Not Verified                                        0\n",
       "verification_status:Source Verified                                     0\n",
       "verification_status:Verified                                            0\n",
       "loan_status:Charged Off                                                 0\n",
       "loan_status:Current                                                     0\n",
       "loan_status:Default                                                     0\n",
       "loan_status:Does not meet the credit policy. Status:Charged Off         0\n",
       "loan_status:Does not meet the credit policy. Status:Fully Paid          0\n",
       "loan_status:Fully Paid                                                  0\n",
       "loan_status:In Grace Period                                             0\n",
       "loan_status:Late (16-30 days)                                           0\n",
       "loan_status:Late (31-120 days)                                          0\n",
       "purpose:car                                                             0\n",
       "purpose:credit_card                                                     0\n",
       "purpose:debt_consolidation                                              0\n",
       "purpose:educational                                                     0\n",
       "purpose:home_improvement                                                0\n",
       "purpose:house                                                           0\n",
       "purpose:major_purchase                                                  0\n",
       "purpose:medical                                                         0\n",
       "purpose:moving                                                          0\n",
       "purpose:other                                                           0\n",
       "purpose:renewable_energy                                                0\n",
       "purpose:small_business                                                  0\n",
       "purpose:vacation                                                        0\n",
       "purpose:wedding                                                         0\n",
       "addr_state:AK                                                           0\n",
       "addr_state:AL                                                           0\n",
       "addr_state:AR                                                           0\n",
       "addr_state:AZ                                                           0\n",
       "addr_state:CA                                                           0\n",
       "addr_state:CO                                                           0\n",
       "addr_state:CT                                                           0\n",
       "addr_state:DC                                                           0\n",
       "addr_state:DE                                                           0\n",
       "addr_state:FL                                                           0\n",
       "addr_state:GA                                                           0\n",
       "addr_state:HI                                                           0\n",
       "addr_state:IA                                                           0\n",
       "addr_state:ID                                                           0\n",
       "addr_state:IL                                                           0\n",
       "addr_state:IN                                                           0\n",
       "addr_state:KS                                                           0\n",
       "addr_state:KY                                                           0\n",
       "addr_state:LA                                                           0\n",
       "addr_state:MA                                                           0\n",
       "addr_state:MD                                                           0\n",
       "addr_state:ME                                                           0\n",
       "addr_state:MI                                                           0\n",
       "addr_state:MN                                                           0\n",
       "addr_state:MO                                                           0\n",
       "addr_state:MS                                                           0\n",
       "addr_state:MT                                                           0\n",
       "addr_state:NC                                                           0\n",
       "addr_state:NE                                                           0\n",
       "addr_state:NH                                                           0\n",
       "addr_state:NJ                                                           0\n",
       "addr_state:NM                                                           0\n",
       "addr_state:NV                                                           0\n",
       "addr_state:NY                                                           0\n",
       "addr_state:OH                                                           0\n",
       "addr_state:OK                                                           0\n",
       "addr_state:OR                                                           0\n",
       "addr_state:PA                                                           0\n",
       "addr_state:RI                                                           0\n",
       "addr_state:SC                                                           0\n",
       "addr_state:SD                                                           0\n",
       "addr_state:TN                                                           0\n",
       "addr_state:TX                                                           0\n",
       "addr_state:UT                                                           0\n",
       "addr_state:VA                                                           0\n",
       "addr_state:VT                                                           0\n",
       "addr_state:WA                                                           0\n",
       "addr_state:WI                                                           0\n",
       "addr_state:WV                                                           0\n",
       "addr_state:WY                                                           0\n",
       "initial_list_status:f                                                   0\n",
       "initial_list_status:w                                                   0\n",
       "good_bad                                                                0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.options.display.max_rows=None\n",
    "loan_data_preprocessed.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed['mths_since_last_delinq'].fillna(0, inplace = True)\n",
    "# We fill the missing values with zeroes.\n",
    "\n",
    "loan_data_preprocessed['mths_since_last_record'].fillna(0, inplace = True)\n",
    "# We fill the missing values with zeroes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1) PD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn import metrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# P values for sklearn logistic regression.\n",
    "\n",
    "# Class to display p-values for logistic regression in sklearn.\n",
    "\n",
    "from sklearn import linear_model\n",
    "import scipy.stats as stat\n",
    "\n",
    "class LogisticRegression_with_p_values:\n",
    "    \n",
    "    def __init__(self,*args,**kwargs):#,**kwargs):\n",
    "        self.model = linear_model.LogisticRegression(max_iter=1000,*args,**kwargs)#,**args)\n",
    "\n",
    "    def fit(self,X,y):\n",
    "        self.model.fit(X,y)\n",
    "        \n",
    "        from sklearn.feature_selection import chi2\n",
    "        scores, pvalues = chi2(inputs_train, loan_data_targets_train)\n",
    "        self.coef_ = self.model.coef_\n",
    "        self.intercept_ = self.model.intercept_\n",
    "        #self.z_scores = z_scores\n",
    "        self.p_values = pvalues\n",
    "        \n",
    "        \"\"\"#### Get p-values for the fitted model ####\n",
    "        denom = (2.0 * (1.0 + np.cosh(self.model.decision_function(X))))\n",
    "        denom = np.tile(denom,(X.shape[1],1)).T\n",
    "        F_ij = np.dot((X / denom).T,X) ## Fisher Information Matrix\n",
    "        Cramer_Rao = np.linalg.inv(F_ij) ## Inverse Information Matrix\n",
    "        sigma_estimates = np.sqrt(np.diagonal(Cramer_Rao))\n",
    "        z_scores = self.model.coef_[0] / sigma_estimates # z-score for eaach model coefficient\n",
    "        p_values = [stat.norm.sf(abs(x)) * 2 for x in z_scores] ### two tailed test for p-values\n",
    "        \n",
    "        self.coef_ = self.model.coef_\n",
    "        self.intercept_ = self.model.intercept_\n",
    "        #self.z_scores = z_scores\n",
    "        self.p_values = p_values\n",
    "        #self.sigma_estimates = sigma_estimates\n",
    "        #self.F_ij = F_ij\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We import the PD model, stored in the 'pd_model.sav' file.\n",
    "reg_pd = pickle.load(open('models/pd_model.sav', 'rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_inputs_train = pd.read_csv('data/loan_data_inputs_train.csv', index_col = 0)\n",
    "loan_data_targets_train = pd.read_csv('data/loan_data_targets_train.csv', index_col = 0, header = 0)\n",
    "loan_data_inputs_test = pd.read_csv('data/loan_data_inputs_test.csv', index_col = 0)\n",
    "loan_data_targets_test = pd.read_csv('data/loan_data_targets_test.csv', index_col = 0, header = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_inputs_pd = pd.concat([loan_data_inputs_train, loan_data_inputs_test], axis = 0)\n",
    "# We concatenate the two dataframes along the rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(466285, 323)\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>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>...</th>\n",
       "      <th>dti:21.7-22.4</th>\n",
       "      <th>dti:22.4-35</th>\n",
       "      <th>dti:&gt;35</th>\n",
       "      <th>mths_since_last_record:Missing</th>\n",
       "      <th>mths_since_last_record:0-2</th>\n",
       "      <th>mths_since_last_record:3-20</th>\n",
       "      <th>mths_since_last_record:21-31</th>\n",
       "      <th>mths_since_last_record:32-80</th>\n",
       "      <th>mths_since_last_record:81-86</th>\n",
       "      <th>mths_since_last_record:&gt;86</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>427211</th>\n",
       "      <td>12796369</td>\n",
       "      <td>14818505</td>\n",
       "      <td>24000</td>\n",
       "      <td>24000</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>8.90</td>\n",
       "      <td>762.08</td>\n",
       "      <td>A</td>\n",
       "      <td>A5</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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",
       "    <tr>\n",
       "      <th>206088</th>\n",
       "      <td>1439740</td>\n",
       "      <td>1691948</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>14.33</td>\n",
       "      <td>343.39</td>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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",
       "    <tr>\n",
       "      <th>136020</th>\n",
       "      <td>5214749</td>\n",
       "      <td>6556909</td>\n",
       "      <td>20425</td>\n",
       "      <td>20425</td>\n",
       "      <td>20425.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>8.90</td>\n",
       "      <td>648.56</td>\n",
       "      <td>A</td>\n",
       "      <td>A5</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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",
       "    <tr>\n",
       "      <th>412305</th>\n",
       "      <td>13827698</td>\n",
       "      <td>15890016</td>\n",
       "      <td>17200</td>\n",
       "      <td>17200</td>\n",
       "      <td>17200.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>16.59</td>\n",
       "      <td>609.73</td>\n",
       "      <td>D</td>\n",
       "      <td>D1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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",
       "    <tr>\n",
       "      <th>36159</th>\n",
       "      <td>422455</td>\n",
       "      <td>496525</td>\n",
       "      <td>8400</td>\n",
       "      <td>8400</td>\n",
       "      <td>7450.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>12.84</td>\n",
       "      <td>282.40</td>\n",
       "      <td>C</td>\n",
       "      <td>C2</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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",
       "<p>5 rows × 323 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              id  member_id  loan_amnt  funded_amnt  funded_amnt_inv  \\\n",
       "427211  12796369   14818505      24000        24000          24000.0   \n",
       "206088   1439740    1691948      10000        10000          10000.0   \n",
       "136020   5214749    6556909      20425        20425          20425.0   \n",
       "412305  13827698   15890016      17200        17200          17200.0   \n",
       "36159     422455     496525       8400         8400           7450.0   \n",
       "\n",
       "              term  int_rate  installment grade sub_grade  ... dti:21.7-22.4  \\\n",
       "427211   36 months      8.90       762.08     A        A5  ...             1   \n",
       "206088   36 months     14.33       343.39     C        C1  ...             0   \n",
       "136020   36 months      8.90       648.56     A        A5  ...             0   \n",
       "412305   36 months     16.59       609.73     D        D1  ...             0   \n",
       "36159    36 months     12.84       282.40     C        C2  ...             0   \n",
       "\n",
       "       dti:22.4-35 dti:>35  mths_since_last_record:Missing  \\\n",
       "427211           0       0                               1   \n",
       "206088           0       0                               1   \n",
       "136020           0       0                               1   \n",
       "412305           0       0                               1   \n",
       "36159            1       0                               1   \n",
       "\n",
       "       mths_since_last_record:0-2 mths_since_last_record:3-20  \\\n",
       "427211                          0                           0   \n",
       "206088                          0                           0   \n",
       "136020                          0                           0   \n",
       "412305                          0                           0   \n",
       "36159                           0                           0   \n",
       "\n",
       "       mths_since_last_record:21-31 mths_since_last_record:32-80  \\\n",
       "427211                            0                            0   \n",
       "206088                            0                            0   \n",
       "136020                            0                            0   \n",
       "412305                            0                            0   \n",
       "36159                             0                            0   \n",
       "\n",
       "       mths_since_last_record:81-86 mths_since_last_record:>86  \n",
       "427211                            0                          0  \n",
       "206088                            0                          0  \n",
       "136020                            0                          0  \n",
       "412305                            0                          0  \n",
       "36159                             0                          0  \n",
       "\n",
       "[5 rows x 323 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(loan_data_inputs_pd.shape)\n",
    "loan_data_inputs_pd.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "features_all_pd = ['grade:A',\n",
    "'grade:B',\n",
    "'grade:C',\n",
    "'grade:D',\n",
    "'grade:E',\n",
    "'grade:F',\n",
    "'grade:G',\n",
    "'home_ownership:RENT_OTHER_NONE_ANY',\n",
    "'home_ownership:OWN',\n",
    "'home_ownership:MORTGAGE',\n",
    "'addr_state:ND_NE_IA_NV_FL_HI_AL',\n",
    "'addr_state:NM_VA',\n",
    "'addr_state:NY',\n",
    "'addr_state:OK_TN_MO_LA_MD_NC',\n",
    "'addr_state:CA',\n",
    "'addr_state:UT_KY_AZ_NJ',\n",
    "'addr_state:AR_MI_PA_OH_MN',\n",
    "'addr_state:RI_MA_DE_SD_IN',\n",
    "'addr_state:GA_WA_OR',\n",
    "'addr_state:WI_MT',\n",
    "'addr_state:TX',\n",
    "'addr_state:IL_CT',\n",
    "'addr_state:KS_SC_CO_VT_AK_MS',\n",
    "'addr_state:WV_NH_WY_DC_ME_ID',\n",
    "'verification_status:Not Verified',\n",
    "'verification_status:Source Verified',\n",
    "'verification_status:Verified',\n",
    "'purpose:educ__sm_b__wedd__ren_en__mov__house',\n",
    "'purpose:credit_card',\n",
    "'purpose:debt_consolidation',\n",
    "'purpose:oth__med__vacation',\n",
    "'purpose:major_purch__car__home_impr',\n",
    "'initial_list_status:f',\n",
    "'initial_list_status:w',\n",
    "'term:36',\n",
    "'term:60',\n",
    "'emp_length:0',\n",
    "'emp_length:1',\n",
    "'emp_length:2-4',\n",
    "'emp_length:5-6',\n",
    "'emp_length:7-9',\n",
    "'emp_length:10',\n",
    "'months_since_issue_d:<38',\n",
    "'months_since_issue_d:38-39',\n",
    "'months_since_issue_d:40-41',\n",
    "'months_since_issue_d:42-48',\n",
    "'months_since_issue_d:49-52',\n",
    "'months_since_issue_d:53-64',\n",
    "'months_since_issue_d:65-84',\n",
    "'months_since_issue_d:>84',\n",
    "'int_rate:<9.548',\n",
    "'int_rate:9.548-12.025',\n",
    "'int_rate:12.025-15.74',\n",
    "'int_rate:15.74-20.281',\n",
    "'int_rate:>20.281',\n",
    "'months_since_earliest_cr_line:<140',\n",
    "'months_since_earliest_cr_line:141-164',\n",
    "'months_since_earliest_cr_line:165-247',\n",
    "'months_since_earliest_cr_line:248-270',\n",
    "'months_since_earliest_cr_line:271-352',\n",
    "'months_since_earliest_cr_line:>352',\n",
    "'inq_last_6mths:0',\n",
    "'inq_last_6mths:1-2',\n",
    "'inq_last_6mths:3-6',\n",
    "'inq_last_6mths:>6',\n",
    "'acc_now_delinq:0',\n",
    "'acc_now_delinq:>=1',\n",
    "'annual_inc:<20K',\n",
    "'annual_inc:20K-30K',\n",
    "'annual_inc:30K-40K',\n",
    "'annual_inc:40K-50K',\n",
    "'annual_inc:50K-60K',\n",
    "'annual_inc:60K-70K',\n",
    "'annual_inc:70K-80K',\n",
    "'annual_inc:80K-90K',\n",
    "'annual_inc:90K-100K',\n",
    "'annual_inc:100K-120K',\n",
    "'annual_inc:120K-140K',\n",
    "'annual_inc:>140K',\n",
    "'dti:<=1.4',\n",
    "'dti:1.4-3.5',\n",
    "'dti:3.5-7.7',\n",
    "'dti:7.7-10.5',\n",
    "'dti:10.5-16.1',\n",
    "'dti:16.1-20.3',\n",
    "'dti:20.3-21.7',\n",
    "'dti:21.7-22.4',\n",
    "'dti:22.4-35',\n",
    "'dti:>35',\n",
    "'mths_since_last_delinq:Missing',\n",
    "'mths_since_last_delinq:0-3',\n",
    "'mths_since_last_delinq:4-30',\n",
    "'mths_since_last_delinq:31-56',\n",
    "'mths_since_last_delinq:>=57',\n",
    "'mths_since_last_record:Missing',\n",
    "'mths_since_last_record:0-2',\n",
    "'mths_since_last_record:3-20',\n",
    "'mths_since_last_record:21-31',\n",
    "'mths_since_last_record:32-80',\n",
    "'mths_since_last_record:81-86',\n",
    "'mths_since_last_record:>86']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "ref_categories_pd = ['grade:G',\n",
    "'home_ownership:RENT_OTHER_NONE_ANY',\n",
    "'addr_state:ND_NE_IA_NV_FL_HI_AL',\n",
    "'verification_status:Verified',\n",
    "'purpose:educ__sm_b__wedd__ren_en__mov__house',\n",
    "'initial_list_status:f',\n",
    "'term:60',\n",
    "'emp_length:0',\n",
    "'months_since_issue_d:>84',\n",
    "'int_rate:>20.281',\n",
    "'months_since_earliest_cr_line:<140',\n",
    "'inq_last_6mths:>6',\n",
    "'acc_now_delinq:0',\n",
    "'annual_inc:<20K',\n",
    "'dti:>35',\n",
    "'mths_since_last_delinq:0-3',\n",
    "'mths_since_last_record:0-2']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_inputs_pd_temp = loan_data_inputs_pd[features_all_pd]\n",
    "# Here we keep only the variables we need for the model.\n",
    "\n",
    "loan_data_inputs_pd_temp = loan_data_inputs_pd_temp.drop(ref_categories_pd, axis = 1)\n",
    "# Here we remove the dummy variable reference categories."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(466285, 84)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_inputs_pd_temp.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.04282214, 0.08518189, 0.03577883, ..., 0.03186755, 0.05200223,\n",
       "       0.04884268])"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reg_pd.model.predict_proba(loan_data_inputs_pd_temp)[: ][: , 0]\n",
    "# We apply the PD model to caclulate estimated default probabilities."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_inputs_pd['PD'] = reg_pd.model.predict_proba(loan_data_inputs_pd_temp)[: ][: , 0]\n",
    "# We apply the PD model to caclulate estimated default probabilities."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "#loan_data_preprocessed_new = pd.concat([loan_data_preprocessed, loan_data_inputs_pd], axis = 1)\n",
    "# We concatenate the dataframes where we calculated LGD and EAD and the dataframe where we calculated PD along the columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2) Loss Given Default"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn import linear_model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Since the p-values are obtained through certain statistics, we need the 'stat' module from scipy.stats\n",
    "import scipy.stats as stat\n",
    "\n",
    "# Since we are using an object oriented language such as Python, we can simply define our own \n",
    "# LinearRegression class (the same one from sklearn)\n",
    "# By typing the code below we will ovewrite a part of the class with one that includes p-values\n",
    "# Here's the full source code of the ORIGINAL class: https://github.com/scikit-learn/scikit-learn/blob/7b136e9/sklearn/linear_model/base.py#L362\n",
    "\n",
    "\n",
    "class LinearRegression(linear_model.LinearRegression):\n",
    "    \"\"\"\n",
    "    LinearRegression class after sklearn's, but calculate t-statistics\n",
    "    and p-values for model coefficients (betas).\n",
    "    Additional attributes available after .fit()\n",
    "    are `t` and `p` which are of the shape (y.shape[1], X.shape[1])\n",
    "    which is (n_features, n_coefs)\n",
    "    This class sets the intercept to 0 by default, since usually we include it\n",
    "    in X.\n",
    "    \"\"\"\n",
    "    \n",
    "    # nothing changes in __init__\n",
    "    def __init__(self, fit_intercept=True, normalize=False, copy_X=True,\n",
    "                 n_jobs=1):\n",
    "        self.fit_intercept = fit_intercept\n",
    "        self.normalize = normalize\n",
    "        self.copy_X = copy_X\n",
    "        self.n_jobs = n_jobs\n",
    "\n",
    "    \n",
    "    def fit(self, X, y, n_jobs=1):\n",
    "        self = super(LinearRegression, self).fit(X, y, n_jobs)\n",
    "        \n",
    "        # Calculate SSE (sum of squared errors)\n",
    "        # and SE (standard error)\n",
    "        sse = np.sum((self.predict(X) - y) ** 2, axis=0) / float(X.shape[0] - X.shape[1])\n",
    "        se = np.array([np.sqrt(np.diagonal(sse * np.linalg.inv(np.dot(X.T, X))))])\n",
    "\n",
    "        # compute the t-statistic for each feature\n",
    "        self.t = self.coef_ / se\n",
    "        # find the p-value for each feature\n",
    "        self.p = np.squeeze(2 * (1 - stat.t.cdf(np.abs(self.t), y.shape[0] - X.shape[1])))\n",
    "        return self"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "#load LGD stage 1 & stage 2 model\n",
    "reg_lgd_st_1 = pickle.load(open('models/lgd_model_stage_1.sav', 'rb'))\n",
    "reg_lgd_st_2 = pickle.load(open('models/lgd_model_stage_2.sav', 'rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "features_all = ['grade:A',\n",
    "'grade:B',\n",
    "'grade:C',\n",
    "'grade:D',\n",
    "'grade:E',\n",
    "'grade:F',\n",
    "'grade:G',\n",
    "'home_ownership:MORTGAGE',\n",
    "'home_ownership:NONE',\n",
    "'home_ownership:OTHER',\n",
    "'home_ownership:OWN',\n",
    "'home_ownership:RENT',\n",
    "'verification_status:Not Verified',\n",
    "'verification_status:Source Verified',\n",
    "'verification_status:Verified',\n",
    "'purpose:car',\n",
    "'purpose:credit_card',\n",
    "'purpose:debt_consolidation',\n",
    "'purpose:educational',\n",
    "'purpose:home_improvement',\n",
    "'purpose:house',\n",
    "'purpose:major_purchase',\n",
    "'purpose:medical',\n",
    "'purpose:moving',\n",
    "'purpose:other',\n",
    "'purpose:renewable_energy',\n",
    "'purpose:small_business',\n",
    "'purpose:vacation',\n",
    "'purpose:wedding',\n",
    "'initial_list_status:f',\n",
    "'initial_list_status:w',\n",
    "'term_int',\n",
    "'emp_length_int',\n",
    "'mths_since_issue_d',\n",
    "'mths_since_earliest_cr_line',\n",
    "'funded_amnt',\n",
    "'int_rate',\n",
    "'installment',\n",
    "'annual_inc',\n",
    "'dti',\n",
    "'delinq_2yrs',\n",
    "'inq_last_6mths',\n",
    "'mths_since_last_delinq',\n",
    "'mths_since_last_record',\n",
    "'open_acc',\n",
    "'pub_rec',\n",
    "'total_acc',\n",
    "'acc_now_delinq',\n",
    "'total_rev_hi_lim']\n",
    "# List of all independent variables for the models.\n",
    "\n",
    "features_reference_cat = ['grade:G',\n",
    "'home_ownership:RENT',\n",
    "'verification_status:Verified',\n",
    "'purpose:credit_card',\n",
    "'initial_list_status:f']\n",
    "# List of the dummy variable reference categories. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed_lgd_ead = loan_data_preprocessed[features_all]\n",
    "# Here we keep only the variables we need for the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed_lgd_ead = loan_data_preprocessed_lgd_ead.drop(features_reference_cat, axis = 1)\n",
    "# Here we remove the dummy variable reference categories."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed['recovery_rate_st_1'] = reg_lgd_st_1.model.predict(loan_data_preprocessed_lgd_ead)\n",
    "# We apply the stage 1 LGD model and calculate predicted values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed['recovery_rate_st_2'] = reg_lgd_st_2.predict(loan_data_preprocessed_lgd_ead)\n",
    "# We apply the stage 2 LGD model and calculate predicted values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed['recovery_rate'] = loan_data_preprocessed['recovery_rate_st_1'] * loan_data_preprocessed['recovery_rate_st_2']\n",
    "# We combine the predicted values from the stage 1 predicted model and the stage 2 predicted model\n",
    "# to calculate the final estimated recovery rate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed['recovery_rate'] = np.where(loan_data_preprocessed['recovery_rate'] < 0, 0, loan_data_preprocessed['recovery_rate'])\n",
    "loan_data_preprocessed['recovery_rate'] = np.where(loan_data_preprocessed['recovery_rate'] > 1, 1, loan_data_preprocessed['recovery_rate'])\n",
    "# We set estimated recovery rates that are greater than 1 to 1 and  estimated recovery rates that are less than 0 to 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed['LGD'] = 1 - loan_data_preprocessed['recovery_rate']\n",
    "# We calculate estimated LGD. Estimated LGD equals 1 - estimated recovery rate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3) Exposure at default (EAD)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "#load EAD model\n",
    "reg_ead = pickle.load(open('models/reg_ead.sav', 'rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed['CCF'] = reg_ead.predict(loan_data_preprocessed_lgd_ead)\n",
    "# We apply the EAD model to calculate estimated credit conversion factor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed['CCF'] = np.where(loan_data_preprocessed['CCF'] < 0, 0, loan_data_preprocessed['CCF'])\n",
    "loan_data_preprocessed['CCF'] = np.where(loan_data_preprocessed['CCF'] > 1, 1, loan_data_preprocessed['CCF'])\n",
    "# We set estimated CCF that are greater than 1 to 1 and  estimated CCF that are less than 0 to 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed['EAD'] = loan_data_preprocessed['CCF'] * loan_data_preprocessed_lgd_ead['funded_amnt']\n",
    "# We calculate estimated EAD. Estimated EAD equals estimated CCF multiplied by funded amount."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Expected Loss"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed_new = pd.concat([loan_data_preprocessed, loan_data_inputs_pd], axis = 1)\n",
    "# We concatenate the dataframes where we calculated LGD and EAD and the dataframe where we calculated PD along the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "loan_data_preprocessed_new['EL'] = loan_data_preprocessed_new['PD']*loan_data_preprocessed_new['LGD']*loan_data_preprocessed_new['EAD']\n",
    "# We calculate Expected Loss. EL = PD * LGD * EAD."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>funded_amnt</th>\n",
       "      <th>funded_amnt</th>\n",
       "      <th>PD</th>\n",
       "      <th>LGD</th>\n",
       "      <th>EAD</th>\n",
       "      <th>EL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0.129025</td>\n",
       "      <td>0.913729</td>\n",
       "      <td>2949.608449</td>\n",
       "      <td>347.741612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2500</td>\n",
       "      <td>2500</td>\n",
       "      <td>0.253241</td>\n",
       "      <td>0.915482</td>\n",
       "      <td>1944.433378</td>\n",
       "      <td>450.792607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2400</td>\n",
       "      <td>2400</td>\n",
       "      <td>0.198979</td>\n",
       "      <td>0.919484</td>\n",
       "      <td>1579.934302</td>\n",
       "      <td>289.061529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000</td>\n",
       "      <td>10000</td>\n",
       "      <td>0.162627</td>\n",
       "      <td>0.904924</td>\n",
       "      <td>6606.559612</td>\n",
       "      <td>972.256733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3000</td>\n",
       "      <td>3000</td>\n",
       "      <td>0.101499</td>\n",
       "      <td>0.911453</td>\n",
       "      <td>2124.631667</td>\n",
       "      <td>196.553781</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   funded_amnt  funded_amnt        PD       LGD          EAD          EL\n",
       "0         5000         5000  0.129025  0.913729  2949.608449  347.741612\n",
       "1         2500         2500  0.253241  0.915482  1944.433378  450.792607\n",
       "2         2400         2400  0.198979  0.919484  1579.934302  289.061529\n",
       "3        10000        10000  0.162627  0.904924  6606.559612  972.256733\n",
       "4         3000         3000  0.101499  0.911453  2124.631667  196.553781"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_preprocessed_new[['funded_amnt','PD','LGD','EAD','EL']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    466285.000000\n",
       "mean          0.109365\n",
       "std           0.068291\n",
       "min           0.007533\n",
       "25%           0.057254\n",
       "50%           0.095353\n",
       "75%           0.146449\n",
       "max           0.685083\n",
       "Name: PD, dtype: float64"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_preprocessed_new['PD'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    466285.000000\n",
       "mean          0.919597\n",
       "std           0.057557\n",
       "min           0.564539\n",
       "25%           0.872698\n",
       "50%           0.898636\n",
       "75%           1.000000\n",
       "max           1.000000\n",
       "Name: LGD, dtype: float64"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_preprocessed_new['LGD'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    466285.000000\n",
       "mean      10814.846760\n",
       "std        6935.184562\n",
       "min         190.347372\n",
       "25%        5495.101413\n",
       "50%        9208.479591\n",
       "75%       14692.844549\n",
       "max       35000.000000\n",
       "Name: EAD, dtype: float64"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_preprocessed_new['EAD'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    466285.000000\n",
       "mean       1085.100656\n",
       "std        1081.839495\n",
       "min           8.991861\n",
       "25%         364.615314\n",
       "50%         715.598354\n",
       "75%        1414.182017\n",
       "max       12037.202304\n",
       "Name: EL, dtype: float64"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_data_preprocessed_new['EL'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Expected Loss at portfolio level = 505966159.4671676\n"
     ]
    }
   ],
   "source": [
    "print(\"Expected Loss at portfolio level =\",loan_data_preprocessed_new['EL'].sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Expected Loss as a proportion of total funded amount for all loans = 0.07592469646111018\n"
     ]
    }
   ],
   "source": [
    "print(\"Total Expected Loss as a proportion of total funded amount for all loans =\",loan_data_preprocessed_new['EL'].sum() / loan_data_preprocessed_new['funded_amnt'].sum().values[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# END"
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}