{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import statsmodels.api as sm\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from itertools import product\n",
    "from sklearn.metrics import f1_score, recall_score, roc_auc_score, accuracy_score\n",
    "from imblearn.over_sampling import SMOTE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.chdir(\"path_to_data\") # please change the path to your data file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the data files\n",
    "df_public = pd.read_csv('lc_loan.csv')\n",
    "df_test = pd.read_csv('lc_loan_test.csv')\n",
    "\n",
    "# uncomment to subsample the training set if your computer does not have enough memory\n",
    "# df_public = df_public.sample(n=100000, random_state=42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_r2(y, pred_y, benchmark = 0, is_train = True):\n",
    "\n",
    "    # Determine the benchmark\n",
    "    if is_train:\n",
    "        benchmark = y.mean()\n",
    "\n",
    "    # Calculate the total sum of squares (TSS)\n",
    "    tss = np.sum((y - benchmark) ** 2)\n",
    "\n",
    "    # Calculate the residual sum of squares (RSS)\n",
    "    rss = np.sum((y - pred_y) ** 2)\n",
    "\n",
    "    # Calculate R-squared\n",
    "    r_squared = 1 - (rss / tss)\n",
    "\n",
    "    return r_squared\n",
    "\n",
    "def feature_split(data):\n",
    "    \n",
    "    # split features and predictors\n",
    "    X = data.drop(columns=['id', 'issue_d', 'loan_status', 'year', 'early_default', 'return'])\n",
    "    loan_return = data['return']\n",
    "    loan_default = data['loan_status']\n",
    "\n",
    "    return X, loan_return, loan_default"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature Preprocessing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing Value Processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>Missing Values</th>\n",
       "      <th>Percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>mths_since_last_delinq</th>\n",
       "      <td>464293</td>\n",
       "      <td>49.754919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emp_length</th>\n",
       "      <td>58768</td>\n",
       "      <td>6.297741</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Missing Values  Percentage\n",
       "mths_since_last_delinq          464293   49.754919\n",
       "emp_length                       58768    6.297741"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# missing value\n",
    "missing_values = df_public.isnull().sum()\n",
    "missing_percentage = (missing_values / len(df_public)) * 100\n",
    "missing_data = pd.DataFrame({'Missing Values': missing_values, 'Percentage': missing_percentage})\n",
    "missing_tmp = missing_data[missing_data['Missing Values'] > 0]\n",
    "\n",
    "# lookup table\n",
    "missing_tmp.sort_values(by='Missing Values', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Missing Values</th>\n",
       "      <th>Percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>mths_since_last_delinq</th>\n",
       "      <td>54347</td>\n",
       "      <td>5.823974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>emp_length</th>\n",
       "      <td>8374</td>\n",
       "      <td>0.897381</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Missing Values  Percentage\n",
       "mths_since_last_delinq           54347    5.823974\n",
       "emp_length                        8374    0.897381"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# missing value\n",
    "missing_values = df_test.isnull().sum()\n",
    "missing_percentage = (missing_values / len(df_public)) * 100\n",
    "missing_data = pd.DataFrame({'Missing Values': missing_values, 'Percentage': missing_percentage})\n",
    "missing_tmp = missing_data[missing_data['Missing Values'] > 0]\n",
    "\n",
    "# lookup table\n",
    "missing_tmp.sort_values(by='Missing Values', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mths_since_last_delinq\n",
    "df_public['dummy_delinq'] = df_public['mths_since_last_delinq'].isnull().astype(int)\n",
    "max_delinq = df_public['mths_since_last_delinq'].max()\n",
    "df_public['mths_since_last_delinq'] = df_public['mths_since_last_delinq'].fillna(max_delinq)\n",
    "df_public['inter_delinq'] = df_public['dummy_delinq'] * df_public['mths_since_last_delinq']\n",
    "\n",
    "# emp_length\n",
    "df_public['emp_length'] = df_public['emp_length'].replace({'10+ years': '10', '< 1 year': '0'}).str.extract('(\\d+)').astype(float)\n",
    "average_emp_length = df_public['emp_length'].mean()\n",
    "df_public['emp_length'] = df_public['emp_length'].fillna(average_emp_length)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Categorical Features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['grade', 'sub_grade', 'home_ownership', 'verification_status',\n",
      "       'issue_d', 'loan_status', 'purpose', 'zip_code', 'addr_state',\n",
      "       'earliest_cr_line'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# print the categorical features\n",
    "non_numeric_columns = df_public.select_dtypes(exclude=['int64', 'float64']).columns\n",
    "print(non_numeric_columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# grade and subgrade\n",
    "df_public.drop(columns=['sub_grade'], inplace=True)\n",
    "grade_dummies = pd.get_dummies(df_public['grade'], prefix='grade', drop_first=True).astype(int)\n",
    "df_public = pd.concat([df_public, grade_dummies], axis=1)\n",
    "df_public.drop(columns=['grade'], inplace=True)\n",
    "\n",
    "# homeownership\n",
    "home_ownership_dummies = pd.get_dummies(df_public['home_ownership'], prefix='home', drop_first=False).astype(int)\n",
    "home_ownership_dummies.drop(columns=[\"home_ANY\", \"home_OTHER\", \"home_NONE\"], inplace=True)\n",
    "df_public = pd.concat([df_public, home_ownership_dummies], axis=1)\n",
    "df_public.drop(columns=['home_ownership'], inplace=True)\n",
    "\n",
    "# verification_status\n",
    "df_public['verification_status'] = df_public['verification_status'].map({'Not Verified': 0, 'Verified': 1, 'Source Verified': 1})\n",
    "\n",
    "# purpose\n",
    "purpose_dummies = pd.get_dummies(df_public['purpose'], prefix='purpose', drop_first=True).astype(int)\n",
    "df_public = pd.concat([df_public, purpose_dummies], axis=1)\n",
    "df_public.drop(columns=['purpose'], inplace=True)\n",
    "\n",
    "# earliest_cr_line\n",
    "df_public['earliest_cr_line'] = pd.to_datetime(df_public['earliest_cr_line'], format='%b-%Y')\n",
    "df_public['issue_d'] = pd.to_datetime(df_public['issue_d'], format='%b-%Y')\n",
    "df_public['length_credit'] = (df_public['issue_d'] - df_public['earliest_cr_line']).dt.days / 365.25\n",
    "df_public.drop(columns=['earliest_cr_line'], inplace=True)\n",
    "\n",
    "# fico_range_low and fico_range_high\n",
    "df_public.drop(columns=['fico_range_high'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# interaction term: Annual income and verification\n",
    "df_public['inter_income'] = df_public['verification_status'] * df_public['annual_inc']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# feature engineering: create a new feature state_income\n",
    "state_income = df_public[df_public['verification_status'] == 1].groupby(['addr_state', 'year'])['annual_inc'].mean().reset_index()\n",
    "state_income.rename(columns={'annual_inc': 'state_income'}, inplace=True)\n",
    "df_public = df_public.merge(state_income, on=['addr_state', 'year'], how='left')\n",
    "df_public['state_income'] = df_public['state_income'].fillna(df_public['state_income'].mean())\n",
    "\n",
    "# drop the location related features\n",
    "df_public.drop(columns=['addr_state','zip_code'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop 'loan_amnt' since 'loan_amnt' and 'funded_amnt' are highly correlated\n",
    "df_public.drop(columns=['loan_amnt'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# standardize features\n",
    "scale_fea_set = ['funded_amnt', 'installment', 'emp_length', 'annual_inc', \n",
    "                'dti', 'delinq_2yrs', 'fico_range_low', 'inq_last_6mths', \n",
    "                'mths_since_last_delinq', 'open_acc', 'pub_rec', 'revol_bal', \n",
    "                'revol_util', 'total_acc', 'collections_12_mths_ex_med',\n",
    "                'acc_now_delinq', 'chargeoff_within_12_mths', 'delinq_amnt',\n",
    "                'pub_rec_bankruptcies', 'tax_liens', 'inter_delinq',\n",
    "                'length_credit', 'inter_income', 'state_income']\n",
    "\n",
    "# Standardize the features in scale_fea_set for data\n",
    "scaled_df = pd.DataFrame(columns=df_public.columns)\n",
    "\n",
    "# Use a list to collect scaled dataframes for concatenation\n",
    "scaled_dataframes = []\n",
    "\n",
    "for year, data in df_public.groupby('year'):\n",
    "    scaler = StandardScaler()\n",
    "    # Standardize the features\n",
    "    data[scale_fea_set] = scaler.fit_transform(data[scale_fea_set])\n",
    "    # Append the scaled data to the list\n",
    "    scaled_dataframes.append(data)\n",
    "\n",
    "# Concatenate all scaled dataframes at once\n",
    "scaled_df = pd.concat(scaled_dataframes, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Split Training and Validation Set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split scaled_df into train and validation sets in a 7:3 ratio\n",
    "train_df, val_df = train_test_split(scaled_df, test_size=0.3, random_state=42)\n",
    "\n",
    "# Reset the index\n",
    "train_df.reset_index(drop=True, inplace=True)\n",
    "val_df.reset_index(drop=True, inplace=True)\n",
    "\n",
    "# Split X and y\n",
    "X_train, return_train, default_train = feature_split(train_df)\n",
    "X_val, return_val, default_val = feature_split(val_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Training Linear Regression Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "In-sample R-squared: 0.012451788737303127\n",
      "Out-of-sample R-squared: 0.07689086096289743\n"
     ]
    }
   ],
   "source": [
    "# Add a constant to the model (intercept)\n",
    "X_train_const = sm.add_constant(X_train)\n",
    "X_val_const = sm.add_constant(X_val)\n",
    "\n",
    "# Fit the OLS model\n",
    "model = sm.OLS(return_train, X_train_const).fit()\n",
    "\n",
    "# Predictions\n",
    "train_pred = model.predict(X_train_const)  # in-sample prediction\n",
    "val_pred = model.predict(X_val_const)  # out-of-sample prediction\n",
    "\n",
    "# Evaluation\n",
    "in_sample_eval = get_r2(return_train, train_pred, is_train=True)  # in-sample evaluation\n",
    "out_of_sample_eval = get_r2(return_val, val_pred, benchmark=0, is_train=False)\n",
    "\n",
    "print(f\"In-sample R-squared: {in_sample_eval}\")\n",
    "print(f\"Out-of-sample R-squared: {out_of_sample_eval}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prediction on Test Set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import data\n",
    "df_test = pd.read_csv('lc_loan_test.csv')\n",
    "submission = pd.read_csv('submission_test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mths_since_last_delinq\n",
    "df_test['dummy_delinq'] = df_test['mths_since_last_delinq'].isnull().astype(int)\n",
    "max_delinq = df_test['mths_since_last_delinq'].max()\n",
    "df_test['mths_since_last_delinq'] = df_test['mths_since_last_delinq'].fillna(max_delinq)\n",
    "df_test['inter_delinq'] = df_test['dummy_delinq'] * df_test['mths_since_last_delinq']\n",
    "\n",
    "# emp_length\n",
    "df_test['emp_length'] = df_test['emp_length'].replace({'10+ years': '10', '< 1 year': '0'}).str.extract('(\\d+)').astype(float)\n",
    "average_emp_length = df_test['emp_length'].mean()\n",
    "df_test['emp_length'] = df_test['emp_length'].fillna(average_emp_length)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# grade and subgrade\n",
    "df_test.drop(columns=['sub_grade'], inplace=True)\n",
    "grade_dummies = pd.get_dummies(df_test['grade'], prefix='grade', drop_first=False).astype(int)\n",
    "df_test = pd.concat([df_test, grade_dummies], axis=1)\n",
    "df_test.drop(columns=['grade'], inplace=True)\n",
    "\n",
    "# homeownership\n",
    "home_ownership_dummies = pd.get_dummies(df_test['home_ownership'], prefix='home', drop_first=False).astype(int)\n",
    "df_test = pd.concat([df_test, home_ownership_dummies], axis=1)\n",
    "df_test.drop(columns=['home_ownership'], inplace=True)\n",
    "\n",
    "# verification_status\n",
    "df_test['verification_status'] = df_test['verification_status'].map({'Not Verified': 0, 'Verified': 1, 'Source Verified': 1})\n",
    "\n",
    "# purpose\n",
    "purpose_dummies = pd.get_dummies(df_test['purpose'], prefix='purpose', drop_first=False).astype(int)\n",
    "df_test = pd.concat([df_test, purpose_dummies], axis=1)\n",
    "df_test.drop(columns=['purpose'], inplace=True)\n",
    "\n",
    "# earliest_cr_line\n",
    "df_test['earliest_cr_line'] = pd.to_datetime(df_test['earliest_cr_line'], format='%b-%Y')\n",
    "df_test['issue_d'] = pd.to_datetime(df_test['issue_d'], format='%b-%Y')\n",
    "df_test['length_credit'] = (df_test['issue_d'] - df_test['earliest_cr_line']).dt.days / 365.25\n",
    "df_test.drop(columns=['earliest_cr_line'], inplace=True)\n",
    "\n",
    "# fico_range_low and fico_range_high\n",
    "df_test.drop(columns=['fico_range_high'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# interaction term: Annual income and verification\n",
    "df_test['inter_income'] = df_test['verification_status'] * df_test['annual_inc']\n",
    "\n",
    "# feature engineering: create a new feature state_income\n",
    "state_income = df_test[df_test['verification_status'] == 1].groupby(['addr_state', 'year'])['annual_inc'].mean().reset_index()\n",
    "state_income.rename(columns={'annual_inc': 'state_income'}, inplace=True)\n",
    "df_test = df_test.merge(state_income, on=['addr_state', 'year'], how='left')\n",
    "df_test['state_income'] = df_test['state_income'].fillna(df_test['state_income'].mean())\n",
    "\n",
    "# drop the location related features\n",
    "df_test.drop(columns=['addr_state','zip_code'], inplace=True)\n",
    "\n",
    "# drop 'loan_amnt' since 'loan_amnt' and 'funded_amnt' are highly correlated\n",
    "df_test.drop(columns=['loan_amnt'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns in df_public but not in df_test:\n",
      "['early_default', 'loan_status', 'purpose_educational', 'purpose_wedding', 'return']\n",
      "Columns in df_test but not in df_public:\n",
      "['grade_A', 'home_ANY', 'purpose_car']\n"
     ]
    }
   ],
   "source": [
    "missing_in_test = df_public.columns.difference(df_test.columns)\n",
    "missing_in_public = df_test.columns.difference(df_public.columns)\n",
    "\n",
    "print(\"Columns in df_public but not in df_test:\")\n",
    "print(missing_in_test.tolist())\n",
    "\n",
    "print(\"Columns in df_test but not in df_public:\")\n",
    "print(missing_in_public.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# align the features in df_public and df_test\n",
    "for col in missing_in_test:\n",
    "    df_test[col] = 0\n",
    "\n",
    "df_test.drop(columns=missing_in_public.tolist(), inplace=True)\n",
    "\n",
    "df_test = df_test[df_public.columns] # rearrange the columns in df_test according to df_public"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# standardize features\n",
    "scale_fea_set = ['funded_amnt', 'installment', 'emp_length', 'annual_inc', \n",
    "                'dti', 'delinq_2yrs', 'fico_range_low', 'inq_last_6mths', \n",
    "                'mths_since_last_delinq', 'open_acc', 'pub_rec', 'revol_bal', \n",
    "                'revol_util', 'total_acc', 'collections_12_mths_ex_med',\n",
    "                'acc_now_delinq', 'chargeoff_within_12_mths', 'delinq_amnt',\n",
    "                'pub_rec_bankruptcies', 'tax_liens', 'inter_delinq',\n",
    "                'length_credit', 'inter_income', 'state_income']\n",
    "\n",
    "scaled_df_test = df_test.copy()\n",
    "scaler = StandardScaler()\n",
    "scaled_df_test[scale_fea_set] = scaler.fit_transform(scaled_df_test[scale_fea_set]) # Standardize the features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split X and y\n",
    "X_test, _, __ = feature_split(scaled_df_test)\n",
    "\n",
    "# Add constant\n",
    "X_test_const = sm.add_constant(X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "y_pred = model.predict(X_test_const)  # out-of-sample prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update the submission file\n",
    "submission['return'] = y_pred\n",
    "\n",
    "submission.to_csv('submission_attempt_1.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "teaching",
   "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.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
