Pre-Processing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 389
    },
    "colab_type": "code",
    "id": "8W3n2Zj7STUH",
    "outputId": "8bdd3974-6cce-417c-ad1f-d0981dc292a2"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.6/dist-packages/IPython/core/interactiveshell.py:2718: DtypeWarning: Columns (19,55) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No.of columns - 74\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>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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1077501</td>\n",
       "      <td>1296599</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>4975.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>10.65</td>\n",
       "      <td>162.87</td>\n",
       "      <td>B</td>\n",
       "      <td>B2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1077430</td>\n",
       "      <td>1314167</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>15.27</td>\n",
       "      <td>59.83</td>\n",
       "      <td>C</td>\n",
       "      <td>C4</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1077175</td>\n",
       "      <td>1313524</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>15.96</td>\n",
       "      <td>84.33</td>\n",
       "      <td>C</td>\n",
       "      <td>C5</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1076863</td>\n",
       "      <td>1277178</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>13.49</td>\n",
       "      <td>339.31</td>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1075358</td>\n",
       "      <td>1311748</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>12.69</td>\n",
       "      <td>67.79</td>\n",
       "      <td>B</td>\n",
       "      <td>B5</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 74 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id  member_id  loan_amnt  funded_amnt  funded_amnt_inv        term  \\\n",
       "0  1077501    1296599     5000.0       5000.0           4975.0   36 months   \n",
       "1  1077430    1314167     2500.0       2500.0           2500.0   60 months   \n",
       "2  1077175    1313524     2400.0       2400.0           2400.0   36 months   \n",
       "3  1076863    1277178    10000.0      10000.0          10000.0   36 months   \n",
       "4  1075358    1311748     3000.0       3000.0           3000.0   60 months   \n",
       "\n",
       "   int_rate  installment grade sub_grade  ... total_bal_il il_util  \\\n",
       "0     10.65       162.87     B        B2  ...          NaN     NaN   \n",
       "1     15.27        59.83     C        C4  ...          NaN     NaN   \n",
       "2     15.96        84.33     C        C5  ...          NaN     NaN   \n",
       "3     13.49       339.31     C        C1  ...          NaN     NaN   \n",
       "4     12.69        67.79     B        B5  ...          NaN     NaN   \n",
       "\n",
       "  open_rv_12m  open_rv_24m max_bal_bc all_util total_rev_hi_lim inq_fi  \\\n",
       "0         NaN          NaN        NaN      NaN              NaN    NaN   \n",
       "1         NaN          NaN        NaN      NaN              NaN    NaN   \n",
       "2         NaN          NaN        NaN      NaN              NaN    NaN   \n",
       "3         NaN          NaN        NaN      NaN              NaN    NaN   \n",
       "4         NaN          NaN        NaN      NaN              NaN    NaN   \n",
       "\n",
       "  total_cu_tl inq_last_12m  \n",
       "0         NaN          NaN  \n",
       "1         NaN          NaN  \n",
       "2         NaN          NaN  \n",
       "3         NaN          NaN  \n",
       "4         NaN          NaN  \n",
       "\n",
       "[5 rows x 74 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "CS 677 Data Science with Python\n",
    "#########################################\n",
    "Project          - Interest Rate prediction\n",
    "Creation Date    - 15/04/2019\n",
    "Student Name     - Gautam Gowrishankar\n",
    "Note: Loan .csv file should be used as the input file and the code would produce \n",
    "      cleaned.csv after manual pre-processing.\n",
    "\"\"\"\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "\n",
    "# Making a list of missing value types\n",
    "missing_values = [\"n/a\", \"na\", \"--\", \"??\"]\n",
    "\n",
    "#Read csv file into pandas dataframe\n",
    "#df = pd.read_csv(\"loan.csv\", na_values = missing_values, low_memory=False)\n",
    "import io\n",
    "df = pd.read_csv(io.BytesIO(uploaded['loan.csv']))\n",
    "\n",
    "print(\"No.of columns -\",len(df.columns))\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "id": "MWE6KaHxlA50",
    "outputId": "5c453a58-e491-4374-b7f0-20c1f14bcf36"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No.of columns - 31\n"
     ]
    }
   ],
   "source": [
    "#Let the cleansing commence\n",
    "\n",
    "#Drop unused Columns\n",
    "df.drop(['funded_amnt_inv','installment','emp_title','pymnt_plan','url','desc','title',\n",
    "         'zip_code','inq_last_6mths','mths_since_last_record','total_acc',\n",
    "         'initial_list_status','out_prncp','out_prncp_inv','total_pymnt_inv','total_rec_prncp',\n",
    "          'collection_recovery_fee','last_pymnt_d','last_pymnt_amnt','next_pymnt_d',\n",
    "         'last_credit_pull_d','collections_12_mths_ex_med','mths_since_last_major_derog','policy_code',\n",
    "         'acc_now_delinq','tot_coll_amt','open_acc_6m','open_il_6m','open_il_12m','open_il_24m',\n",
    "         'mths_since_rcnt_il','total_bal_il','il_util','open_rv_12m','open_rv_24m','max_bal_bc','all_util',\n",
    "         'total_rev_hi_lim','inq_fi','total_cu_tl','inq_last_12m','revol_util','verification_status_joint'],axis=1,inplace=True)\n",
    "\n",
    "print(\"No.of columns -\",len(df.columns))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "A9GynT8GQBCz"
   },
   "outputs": [],
   "source": [
    "\n",
    "#We consider the employment length of <1 year to be atleast 6 months, 10+ to be 11 years and n/a to be 0\n",
    "df.emp_length.replace(['< 1 year'], ['6 months'], inplace=True)\n",
    "df.emp_length.replace(['10+ years'], ['11 years'], inplace=True)\n",
    "df.emp_length.replace(['n/a'], ['0'], inplace=True)\n",
    "df.term.replace([' 36 months'], [3], inplace=True)\n",
    "df.term.replace([' 60 months'], [5], inplace=True)\n",
    "\n",
    "#df.fillna({'annual_inc_joint':0,'dti_joint':0.0},inplace=True)\n",
    "\n",
    "df['mths_since_last_delinq'].fillna( method ='backfill', inplace = True)\n",
    "df['tot_cur_bal'].fillna(df['tot_cur_bal'].min(), inplace = True)\n",
    "df['annual_inc_joint'].fillna(0, inplace = True)\n",
    "df['dti_joint'].fillna(0, inplace = True)\n",
    "\n",
    "#Check the unique values in emp_length columns\n",
    "#df.emp_length.unique()\n",
    "\n",
    "#Eliminating loans with status ISSUED and GRACE PERIOD as it may not be required for analysis\n",
    "#df_copy= df.copy()\n",
    "df.drop(df.loc[df['loan_status']== 'Issued'].index, inplace=True)\n",
    "df.drop(df.loc[df['loan_status']== 'In Grace Period'].index, inplace=True)\n",
    "df.drop(df.loc[df['loan_status']== 'Does not meet the credit policy. Status:Fully Paid'].index, inplace=True)\n",
    "df.drop(df.loc[df['loan_status']== 'Does not meet the credit policy. Status:Charged Off'].index, inplace=True)\n",
    "df.drop(df.loc[df['loan_status']== 'Late (16-30 days)'].index, inplace=True)\n",
    "df.drop(df.loc[df['loan_status']== 'Late (31-120 days)'].index, inplace=True)\n",
    "df.drop(df.loc[df['loan_status']== 'Current'].index, inplace=True)\n",
    "\n",
    "\n",
    "df.rename(index = str, columns = {\"term\":\"term(years)\"}, inplace = True)\n",
    "                               \n",
    "df.to_csv(os.getcwd()+ \"/cleaned.csv\",index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 136
    },
    "colab_type": "code",
    "id": "8IAn-4oklZ2a",
    "outputId": "27f7c2e1-819b-452d-f770-9fc6be4f2ee0"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Entityset: loandetails\n",
       "  Entities:\n",
       "    loans [Rows: 254190, Columns: 31]\n",
       "    borrowers [Rows: 254190, Columns: 7]\n",
       "    loangrade [Rows: 7, Columns: 1]\n",
       "  Relationships:\n",
       "    No relationships"
      ]
     },
     "execution_count": 6,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Feature Engineering\n",
    "\n",
    "import pandas as pd\n",
    "import featuretools as ft\n",
    "import os\n",
    "\n",
    "# ignore warnings from pandas\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.width', 1000)\n",
    "\n",
    "#Making different dataframes from csv\n",
    "loans = pd.read_csv(os.getcwd()+ \"/cleaned.csv\", low_memory=False)\n",
    "borrowers = loans[['member_id','emp_length','home_ownership','annual_inc','addr_state','mths_since_last_delinq','tot_cur_bal']]\n",
    "loangradedata = ['A','B','C','D','E','F','G']\n",
    "loangrade = pd.DataFrame(loangradedata,columns=['grade'])\n",
    "\n",
    "#create new entityset\n",
    "es = ft.EntitySet(id = 'loandetails')\n",
    "\n",
    "#create an entity from loan dataset\n",
    "es = es.entity_from_dataframe(entity_id = 'loans', dataframe = loans, index = 'id' ,variable_types = {'loan_status': ft.variable_types.Categorical}) #,time_index = 'term'\n",
    "\n",
    "es = es.entity_from_dataframe(entity_id = 'borrowers', dataframe = borrowers, index = 'member_id' ) #,time_index = 'term'\n",
    "\n",
    "es = es.entity_from_dataframe(entity_id = 'loangrade', dataframe = loangrade, index = 'grade' ) #,time_index = 'term'\n",
    "\n",
    "#Look at the entities\n",
    "es"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 156
    },
    "colab_type": "code",
    "id": "lwtSpDEpQBC3",
    "outputId": "4566155c-e8c1-4b0e-e3ed-46f8d5de5087"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-04-27 16:40:44,749 featuretools.entityset - WARNING    Not adding duplicate relationship: <Relationship: loans.member_id -> borrowers.member_id>\n",
      "2019-04-27 16:40:44,752 featuretools.entityset - WARNING    Not adding duplicate relationship: <Relationship: loans.grade -> loangrade.grade>\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 254190 entries, 54734 to 68604659\n",
      "Columns: 296 entries, member_id to loangrade.MODE(loans.loan_status)\n",
      "dtypes: float64(229), int64(38), object(29)\n",
      "memory usage: 576.0+ MB\n"
     ]
    }
   ],
   "source": [
    "#Relationship between borrowers and loans\n",
    "r_borrower_loans = ft.Relationship(es['borrowers']['member_id'],\n",
    "                                    es['loans']['member_id'])\n",
    "r_loans_grades = ft.Relationship(es['loangrade']['grade'],\n",
    "                                es['loans']['grade'])\n",
    "\n",
    "# Add the relationship to the entity set\n",
    "es = es.add_relationship(r_borrower_loans)\n",
    "es = es.add_relationship(r_loans_grades)\n",
    "\n",
    "#es\n",
    "\n",
    "#'percentile','cum_mean','cum_sum','cum_min', 'diff','absolute','year'\n",
    "#max_depth=2\n",
    "\n",
    "# features, feature_names = ft.dfs(entityset = es, target_entity = 'loans', \n",
    "#                                  agg_primitives = ['count','mean','min','max'],\n",
    "#                                   trans_primitives = ['year','divide'],\n",
    "#                                  max_depth = 2)\n",
    "\n",
    "# Perform deep feature synthesis without specifying primitives\n",
    "dpfeatures, dpfeature_names = ft.dfs(entityset=es, target_entity='loans', max_depth = 2)\n",
    "\n",
    "dpfeatures.info()\n",
    "dpfeatures.columns.values.tolist()\n",
    "dpfeatures.to_csv(os.getcwd()+ \"/Features_data.csv\",index = False)\n",
    "#dpfeatures.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1615
    },
    "colab_type": "code",
    "id": "DqAlQpodQBC6",
    "outputId": "2f7d7ee9-b84b-495d-8a41-8d664915f372"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Before elimination\n",
      "No.of columns - 296\n",
      "After elimination\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 254190 entries, 0 to 254189\n",
      "Data columns (total 85 columns):\n",
      "member_id                                       254190 non-null int64\n",
      "loan_amnt                                       254190 non-null float64\n",
      "funded_amnt                                     254190 non-null float64\n",
      "term(years)                                     254190 non-null int64\n",
      "int_rate                                        254190 non-null float64\n",
      "grade                                           254190 non-null object\n",
      "sub_grade                                       254190 non-null object\n",
      "emp_length                                      244222 non-null object\n",
      "home_ownership                                  254190 non-null object\n",
      "annual_inc                                      254190 non-null float64\n",
      "verification_status                             254190 non-null object\n",
      "purpose                                         254190 non-null object\n",
      "addr_state                                      254190 non-null object\n",
      "dti                                             254190 non-null float64\n",
      "delinq_2yrs                                     254190 non-null float64\n",
      "mths_since_last_delinq                          254190 non-null float64\n",
      "open_acc                                        254190 non-null float64\n",
      "pub_rec                                         254190 non-null float64\n",
      "revol_bal                                       254190 non-null float64\n",
      "total_pymnt                                     254190 non-null float64\n",
      "total_rec_int                                   254190 non-null float64\n",
      "total_rec_late_fee                              254190 non-null float64\n",
      "recoveries                                      254190 non-null float64\n",
      "application_type                                254190 non-null object\n",
      "annual_inc_joint                                254190 non-null float64\n",
      "dti_joint                                       254190 non-null float64\n",
      "tot_cur_bal                                     254190 non-null float64\n",
      "loan_status                                     254190 non-null object\n",
      "DAY(issue_d)                                    254190 non-null int64\n",
      "DAY(earliest_cr_line)                           254190 non-null int64\n",
      "YEAR(issue_d)                                   254190 non-null int64\n",
      "YEAR(earliest_cr_line)                          254190 non-null int64\n",
      "MONTH(issue_d)                                  254190 non-null int64\n",
      "MONTH(earliest_cr_line)                         254190 non-null int64\n",
      "WEEKDAY(issue_d)                                254190 non-null int64\n",
      "WEEKDAY(earliest_cr_line)                       254190 non-null int64\n",
      "loangrade.SUM(loans.loan_amnt)                  254190 non-null float64\n",
      "loangrade.SUM(loans.recoveries)                 254190 non-null float64\n",
      "loangrade.STD(loans.loan_amnt)                  254190 non-null float64\n",
      "loangrade.STD(loans.funded_amnt)                254190 non-null float64\n",
      "loangrade.STD(loans.term(years))                254190 non-null float64\n",
      "loangrade.STD(loans.int_rate)                   254190 non-null float64\n",
      "loangrade.STD(loans.annual_inc)                 254190 non-null float64\n",
      "loangrade.STD(loans.dti)                        254190 non-null float64\n",
      "loangrade.STD(loans.delinq_2yrs)                254190 non-null float64\n",
      "loangrade.STD(loans.mths_since_last_delinq)     254190 non-null float64\n",
      "loangrade.STD(loans.open_acc)                   254190 non-null float64\n",
      "loangrade.STD(loans.pub_rec)                    254190 non-null float64\n",
      "loangrade.STD(loans.total_pymnt)                254190 non-null float64\n",
      "loangrade.STD(loans.total_rec_int)              254190 non-null float64\n",
      "loangrade.STD(loans.total_rec_late_fee)         254190 non-null float64\n",
      "loangrade.STD(loans.recoveries)                 254190 non-null float64\n",
      "loangrade.STD(loans.annual_inc_joint)           254190 non-null float64\n",
      "loangrade.STD(loans.dti_joint)                  254190 non-null float64\n",
      "loangrade.MAX(loans.loan_amnt)                  254190 non-null float64\n",
      "loangrade.MAX(loans.term(years))                254190 non-null int64\n",
      "loangrade.MAX(loans.int_rate)                   254190 non-null float64\n",
      "loangrade.MAX(loans.recoveries)                 254190 non-null float64\n",
      "loangrade.MAX(loans.dti_joint)                  254190 non-null float64\n",
      "loangrade.MIN(loans.loan_amnt)                  254190 non-null float64\n",
      "loangrade.MIN(loans.int_rate)                   254190 non-null float64\n",
      "loangrade.MEAN(loans.loan_amnt)                 254190 non-null float64\n",
      "loangrade.MEAN(loans.funded_amnt)               254190 non-null float64\n",
      "loangrade.MEAN(loans.term(years))               254190 non-null float64\n",
      "loangrade.MEAN(loans.int_rate)                  254190 non-null float64\n",
      "loangrade.MEAN(loans.annual_inc)                254190 non-null float64\n",
      "loangrade.MEAN(loans.dti)                       254190 non-null float64\n",
      "loangrade.MEAN(loans.delinq_2yrs)               254190 non-null float64\n",
      "loangrade.MEAN(loans.mths_since_last_delinq)    254190 non-null float64\n",
      "loangrade.MEAN(loans.open_acc)                  254190 non-null float64\n",
      "loangrade.MEAN(loans.pub_rec)                   254190 non-null float64\n",
      "loangrade.MEAN(loans.revol_bal)                 254190 non-null float64\n",
      "loangrade.MEAN(loans.total_pymnt)               254190 non-null float64\n",
      "loangrade.MEAN(loans.total_rec_int)             254190 non-null float64\n",
      "loangrade.MEAN(loans.total_rec_late_fee)        254190 non-null float64\n",
      "loangrade.MEAN(loans.recoveries)                254190 non-null float64\n",
      "loangrade.MEAN(loans.annual_inc_joint)          254190 non-null float64\n",
      "loangrade.MEAN(loans.dti_joint)                 254190 non-null float64\n",
      "loangrade.MEAN(loans.tot_cur_bal)               254190 non-null float64\n",
      "loangrade.COUNT(loans)                          254190 non-null int64\n",
      "loangrade.MODE(loans.sub_grade)                 254190 non-null object\n",
      "loangrade.MODE(loans.home_ownership)            254190 non-null object\n",
      "loangrade.MODE(loans.purpose)                   254190 non-null object\n",
      "loangrade.MODE(loans.addr_state)                254190 non-null object\n",
      "loangrade.MODE(loans.loan_status)               254190 non-null object\n",
      "dtypes: float64(59), int64(12), object(14)\n",
      "memory usage: 164.8+ MB\n",
      "No.of columns - 296\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import featuretools as ft\n",
    "import os\n",
    "\n",
    "# ignore warnings from pandas\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.width', 1000)\n",
    "\n",
    "features = pd.read_csv(os.getcwd()+ \"/Features_data.csv\", low_memory=False)\n",
    "#creating copy of features\n",
    "features_copy = features.copy()\n",
    "\n",
    "print(\"Before elimination\")\n",
    "print(\"No.of columns -\",len(feautures.columns))\n",
    "\n",
    "features.drop(['borrowers.emp_length', \n",
    "                 'borrowers.home_ownership',\n",
    "                 'borrowers.annual_inc',\n",
    "                 'borrowers.addr_state',\n",
    "                 'borrowers.mths_since_last_delinq',\n",
    "                 'borrowers.tot_cur_bal',\n",
    "                 'borrowers.MAX(loans.loan_amnt)',\n",
    "                 'borrowers.MAX(loans.funded_amnt)',\n",
    "                 'borrowers.MAX(loans.int_rate)',\n",
    "                 'borrowers.MAX(loans.annual_inc)',\n",
    "                 'borrowers.MAX(loans.dti)',\n",
    "                 'borrowers.MAX(loans.delinq_2yrs)',\n",
    "                 'borrowers.MAX(loans.mths_since_last_delinq)',\n",
    "                 'borrowers.MAX(loans.open_acc)',\n",
    "                 'borrowers.MAX(loans.pub_rec)',\n",
    "                 'borrowers.MAX(loans.revol_bal)',\n",
    "                 'borrowers.MAX(loans.total_pymnt)',\n",
    "                 'borrowers.MAX(loans.recoveries)',\n",
    "                 'borrowers.MAX(loans.annual_inc_joint)',\n",
    "                 'borrowers.MAX(loans.dti_joint)',\n",
    "                 'borrowers.MAX(loans.tot_cur_bal)',\n",
    "                 'borrowers.MAX(loans.term(years))',\n",
    "                 'borrowers.MAX(loans.total_rec_int)',\n",
    "                 'borrowers.MAX(loans.total_rec_late_fee)',\n",
    "                 'borrowers.MIN(loans.loan_amnt)',\n",
    "                 'borrowers.MIN(loans.funded_amnt)',\n",
    "                 'borrowers.MIN(loans.int_rate)',\n",
    "                 'borrowers.MIN(loans.annual_inc)',\n",
    "                 'borrowers.MIN(loans.dti)',\n",
    "                 'borrowers.MIN(loans.delinq_2yrs)',\n",
    "                 'borrowers.MIN(loans.mths_since_last_delinq)',\n",
    "                 'borrowers.MIN(loans.open_acc)',\n",
    "                 'borrowers.MIN(loans.pub_rec)',\n",
    "                 'borrowers.MIN(loans.revol_bal)',\n",
    "                 'borrowers.MIN(loans.total_pymnt)',\n",
    "                 'borrowers.MIN(loans.recoveries)',\n",
    "                 'borrowers.MIN(loans.annual_inc_joint)',\n",
    "                 'borrowers.MIN(loans.dti_joint)',\n",
    "                 'borrowers.MIN(loans.tot_cur_bal)',\n",
    "                 'borrowers.MEAN(loans.loan_amnt)',\n",
    "                 'borrowers.MIN(loans.term(years))',\n",
    "                 'borrowers.MIN(loans.total_rec_int)',\n",
    "                 'borrowers.MIN(loans.total_rec_late_fee)',\n",
    "                 'borrowers.MEAN(loans.funded_amnt)',\n",
    "                 'borrowers.MEAN(loans.int_rate)',\n",
    "                 'borrowers.MEAN(loans.annual_inc)',\n",
    "                 'borrowers.MEAN(loans.dti)',\n",
    "                 'borrowers.MEAN(loans.delinq_2yrs)',\n",
    "                 'borrowers.MEAN(loans.mths_since_last_delinq)',\n",
    "                 'borrowers.MEAN(loans.open_acc)',\n",
    "                 'borrowers.MEAN(loans.pub_rec)',\n",
    "                 'borrowers.MEAN(loans.revol_bal)',\n",
    "                 'borrowers.MEAN(loans.total_pymnt)',\n",
    "                 'borrowers.MEAN(loans.recoveries)',\n",
    "                 'borrowers.MEAN(loans.annual_inc_joint)',\n",
    "                 'borrowers.MEAN(loans.dti_joint)',\n",
    "                 'borrowers.MEAN(loans.tot_cur_bal)',\n",
    "                 'borrowers.MEAN(loans.term(years))',\n",
    "                 'borrowers.MEAN(loans.total_rec_int)',\n",
    "                 'borrowers.MEAN(loans.total_rec_late_fee)',\n",
    "                 'borrowers.COUNT(loans)',\n",
    "                 'borrowers.NUM_UNIQUE(loans.grade)',\n",
    "                 'borrowers.NUM_UNIQUE(loans.sub_grade)',\n",
    "                 'borrowers.NUM_UNIQUE(loans.emp_length)',\n",
    "                 'borrowers.NUM_UNIQUE(loans.home_ownership)',\n",
    "                 'borrowers.NUM_UNIQUE(loans.verification_status)',\n",
    "                 'borrowers.NUM_UNIQUE(loans.purpose)',\n",
    "                 'borrowers.NUM_UNIQUE(loans.addr_state)',\n",
    "                 'borrowers.NUM_UNIQUE(loans.application_type)',\n",
    "                 'borrowers.NUM_UNIQUE(loans.loan_status)',\n",
    "                 'borrowers.MODE(loans.grade)',\n",
    "                 'borrowers.MODE(loans.sub_grade)',\n",
    "                 'borrowers.MODE(loans.emp_length)',\n",
    "                 'borrowers.MODE(loans.home_ownership)',\n",
    "                 'borrowers.MODE(loans.verification_status)',\n",
    "                 'borrowers.MODE(loans.purpose)',\n",
    "                 'borrowers.MODE(loans.addr_state)',\n",
    "                 'borrowers.MODE(loans.application_type)',\n",
    "                 'borrowers.MODE(loans.loan_status)',\n",
    "                 'borrowers.SKEW(loans.loan_amnt)',\n",
    "                 'borrowers.SKEW(loans.funded_amnt)',\n",
    "                 'borrowers.SKEW(loans.int_rate)',\n",
    "                 'borrowers.SKEW(loans.annual_inc)',\n",
    "                 'borrowers.SKEW(loans.dti)',\n",
    "                 'borrowers.SKEW(loans.delinq_2yrs)',\n",
    "                 'borrowers.SKEW(loans.mths_since_last_delinq)',\n",
    "                 'borrowers.SKEW(loans.open_acc)',\n",
    "                 'borrowers.SKEW(loans.pub_rec)',\n",
    "                 'borrowers.SKEW(loans.revol_bal)',\n",
    "                 'borrowers.SKEW(loans.total_pymnt)',\n",
    "                 'borrowers.SKEW(loans.recoveries)',\n",
    "                 'borrowers.SKEW(loans.annual_inc_joint)',\n",
    "                 'borrowers.SKEW(loans.dti_joint)',\n",
    "                 'borrowers.SKEW(loans.tot_cur_bal)',\n",
    "                 'borrowers.SKEW(loans.term(years))',\n",
    "                 'borrowers.SKEW(loans.total_rec_int)',\n",
    "                 'borrowers.SKEW(loans.total_rec_late_fee)',\n",
    "                 'borrowers.SUM(loans.loan_amnt)',\n",
    "                 'borrowers.SUM(loans.funded_amnt)',\n",
    "                 'borrowers.SUM(loans.int_rate)',\n",
    "                 'borrowers.SUM(loans.annual_inc)',\n",
    "                 'borrowers.SUM(loans.dti)',\n",
    "                 'borrowers.SUM(loans.delinq_2yrs)',\n",
    "                 'borrowers.SUM(loans.mths_since_last_delinq)',\n",
    "                 'borrowers.SUM(loans.open_acc)',\n",
    "                 'borrowers.SUM(loans.pub_rec)',\n",
    "                 'borrowers.SUM(loans.revol_bal)',\n",
    "                 'borrowers.SUM(loans.total_pymnt)',\n",
    "                 'borrowers.SUM(loans.term(years))',\n",
    "                 'borrowers.SUM(loans.recoveries)',\n",
    "                 'borrowers.SUM(loans.annual_inc_joint)',\n",
    "                 'borrowers.SUM(loans.total_rec_int)',\n",
    "                 'borrowers.SUM(loans.dti_joint)',\n",
    "                 'borrowers.SUM(loans.tot_cur_bal)',\n",
    "                 'borrowers.STD(loans.loan_amnt)',\n",
    "                 'borrowers.STD(loans.funded_amnt)',\n",
    "                 'borrowers.STD(loans.int_rate)',\n",
    "                 'borrowers.STD(loans.annual_inc)',\n",
    "                 'borrowers.STD(loans.dti)',\n",
    "                 'borrowers.STD(loans.delinq_2yrs)',\n",
    "                 'borrowers.STD(loans.mths_since_last_delinq)',\n",
    "                 'borrowers.STD(loans.open_acc)',\n",
    "                 'borrowers.STD(loans.pub_rec)',\n",
    "                 'borrowers.STD(loans.revol_bal)',\n",
    "                 'borrowers.STD(loans.total_pymnt)',\n",
    "                 'borrowers.STD(loans.recoveries)',\n",
    "                 'borrowers.STD(loans.annual_inc_joint)',\n",
    "                 'borrowers.STD(loans.dti_joint)',\n",
    "                 'borrowers.STD(loans.tot_cur_bal)',\n",
    "                 'borrowers.STD(loans.term(years))',\n",
    "                 'borrowers.STD(loans.total_rec_int)',\n",
    "                 'borrowers.STD(loans.total_rec_int)',\n",
    "                 'borrowers.STD(loans.total_rec_late_fee)',\n",
    "                 'borrowers.SUM(loans.total_rec_late_fee)',\n",
    "                 'loangrade.SUM(loans.int_rate)',\n",
    "                 'loangrade.SUM(loans.annual_inc_joint)',\n",
    "                 'loangrade.SUM(loans.dti_joint)',\n",
    "                 'loangrade.SUM(loans.tot_cur_bal)',\n",
    "                 'loangrade.SUM(loans.delinq_2yrs)',\n",
    "                 'loangrade.SUM(loans.funded_amnt)',\n",
    "                 'loangrade.SUM(loans.mths_since_last_delinq)',\n",
    "                 'loangrade.SUM(loans.open_acc)',\n",
    "                 'loangrade.SUM(loans.pub_rec)',\n",
    "                 'loangrade.SUM(loans.revol_bal)',\n",
    "                 'loangrade.SUM(loans.term(years))',\n",
    "                 'loangrade.SUM(loans.annual_inc)',\n",
    "                 'loangrade.SUM(loans.dti)',\n",
    "                 'loangrade.SUM(loans.total_rec_int)',\n",
    "                 'loangrade.SUM(loans.total_pymnt)',\n",
    "                 'loangrade.SUM(loans.total_rec_late_fee)',\n",
    "                 'loangrade.STD(loans.revol_bal)',\n",
    "                 'loangrade.STD(loans.tot_cur_bal)',\n",
    "                 'loangrade.MAX(loans.funded_amnt)',\n",
    "                 'loangrade.MAX(loans.mths_since_last_delinq)',\n",
    "                 'loangrade.MAX(loans.open_acc)',\n",
    "                 'loangrade.MAX(loans.pub_rec)',\n",
    "                 'loangrade.MAX(loans.annual_inc_joint)',\n",
    "                 'loangrade.MAX(loans.tot_cur_bal)',\n",
    "                 'loangrade.MAX(loans.dti)',\n",
    "                 'loangrade.MAX(loans.delinq_2yrs)',\n",
    "                 'loangrade.MAX(loans.annual_inc)',\n",
    "                 'loangrade.MAX(loans.revol_bal)',\n",
    "                 'loangrade.MAX(loans.total_pymnt)',\n",
    "                 'loangrade.MAX(loans.total_rec_int)',\n",
    "                 'loangrade.MAX(loans.total_rec_late_fee)',\n",
    "                 'loangrade.SKEW(loans.funded_amnt)',\n",
    "                 'loangrade.SKEW(loans.annual_inc)',\n",
    "                 'loangrade.SKEW(loans.mths_since_last_delinq)',\n",
    "                 'loangrade.SKEW(loans.open_acc)',\n",
    "                 'loangrade.SKEW(loans.pub_rec)',\n",
    "                 'loangrade.SKEW(loans.revol_bal)',\n",
    "                 'loangrade.SKEW(loans.total_pymnt)',\n",
    "                 'loangrade.SKEW(loans.recoveries)',\n",
    "                 'loangrade.SKEW(loans.annual_inc_joint)',\n",
    "                 'loangrade.SKEW(loans.dti_joint)',\n",
    "                 'loangrade.SKEW(loans.tot_cur_bal)',\n",
    "                 'loangrade.SKEW(loans.loan_amnt)',\n",
    "                 'loangrade.SKEW(loans.term(years))',\n",
    "                 'loangrade.SKEW(loans.int_rate)',\n",
    "                 'loangrade.SKEW(loans.dti)',\n",
    "                 'loangrade.SKEW(loans.delinq_2yrs)',\n",
    "                 'loangrade.SKEW(loans.total_rec_int)',\n",
    "                 'loangrade.SKEW(loans.total_rec_late_fee)',\n",
    "                 'loangrade.MIN(loans.funded_amnt)',\n",
    "                 'loangrade.MIN(loans.annual_inc)',\n",
    "                 'loangrade.MIN(loans.dti)',\n",
    "                 'loangrade.MIN(loans.delinq_2yrs)',\n",
    "                 'loangrade.MIN(loans.mths_since_last_delinq)',\n",
    "                 'loangrade.MIN(loans.open_acc)',\n",
    "                 'loangrade.MIN(loans.pub_rec)',\n",
    "                 'loangrade.MIN(loans.revol_bal)',\n",
    "                 'loangrade.MIN(loans.total_pymnt)',\n",
    "                 'loangrade.MIN(loans.recoveries)',\n",
    "                 'loangrade.MIN(loans.annual_inc_joint)',\n",
    "                 'loangrade.MIN(loans.dti_joint)',\n",
    "                 'loangrade.MIN(loans.tot_cur_bal)',\n",
    "                 'loangrade.MIN(loans.term(years))',\n",
    "                 'loangrade.MIN(loans.total_rec_int)',\n",
    "                 'loangrade.MIN(loans.total_rec_late_fee)',\n",
    "                 'loangrade.NUM_UNIQUE(loans.sub_grade)',\n",
    "                 'loangrade.NUM_UNIQUE(loans.emp_length)',\n",
    "                 'loangrade.NUM_UNIQUE(loans.home_ownership)',\n",
    "                 'loangrade.NUM_UNIQUE(loans.verification_status)',\n",
    "                 'loangrade.NUM_UNIQUE(loans.purpose)',\n",
    "                 'loangrade.NUM_UNIQUE(loans.addr_state)',\n",
    "                 'loangrade.NUM_UNIQUE(loans.application_type)',\n",
    "                 'loangrade.NUM_UNIQUE(loans.loan_status)',\n",
    "                 'loangrade.NUM_UNIQUE(loans.member_id)',\n",
    "                 'loangrade.MODE(loans.member_id)',\n",
    "                 'loangrade.MODE(loans.emp_length)',\n",
    "                 'loangrade.MODE(loans.verification_status)',\n",
    "                 'loangrade.MODE(loans.application_type)'],axis =1, inplace=True)\n",
    "\n",
    "#Check the dataframe size after elimination\n",
    "print(\"After elimination\")\n",
    "features.info()\n",
    "features.columns.values.tolist()\n",
    "print(\"No.of columns -\",len(feautures.columns))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "s6pjYZ_0QBC8"
   },
   "outputs": [],
   "source": [
    "#Renaming the columns\n",
    "features.rename(index = str, columns = {\"loangrade.SUM(loans.loan_amnt)\":\"sum_loan_amnt\",\n",
    "                                        \"loangrade.SUM(loans.recoveries)\":\"sum_recoveries\",\n",
    "                                        \"loangrade.STD(loans.loan_amnt)\":\"std_loan_amnt\",\n",
    "                                        \"loangrade.STD(loans.funded_amnt)\":\"std_funded_amnt\",\n",
    "                                        \"loangrade.STD(loans.term(years))\":\"std_term\",\n",
    "                                        \"loangrade.STD(loans.int_rate)\":\"std_int_rate\",\n",
    "                                        \"loangrade.STD(loans.annual_inc)\":\"std_annual_inc\",\n",
    "                                        \"loangrade.STD(loans.dti)\":\"std_dti\",\n",
    "                                        \"loangrade.STD(loans.delinq_2yrs)\":\"std_delinq_2yrs\",\n",
    "                                        \"loangrade.STD(loans.mths_since_last_delinq)\":\"std_mths_since_last_delinq\",\n",
    "                                        \"loangrade.STD(loans.open_acc)\":\"std_open_cc\",\n",
    "                                        \"loangrade.STD(loans.pub_rec)\":\"std_pub_rec\",\n",
    "                                        \"loangrade.STD(loans.total_pymnt)\":\"std_total_paymnt\",\n",
    "                                        \"loangrade.STD(loans.total_rec_int)\":\"std_total_rec_int\",\n",
    "                                        \"loangrade.STD(loans.total_rec_late_fee)\":\"std_rec_late_fee\",\n",
    "                                        \"loangrade.STD(loans.recoveries)\":\"std_recoveries\",\n",
    "                                        \"loangrade.STD(loans.annual_inc_joint)\":\"std_annual_inc_joint\",\n",
    "                                        \"loangrade.STD(loans.dti_joint)\":\"std_dti_joint\",\n",
    "                                        \"loangrade.MAX(loans.loan_amnt)\":\"max_loan_amnt\",\n",
    "                                        \"loangrade.MAX(loans.term(years))\":\"max_term\",\n",
    "                                        \"loangrade.MAX(loans.int_rate)\":\"max_int_rate\",\n",
    "                                        \"loangrade.MAX(loans.recoveries)\":\"max_recoveries\",\n",
    "                                        \"loangrade.MAX(loans.dti_joint)\":\"max_dti_joint\",\n",
    "                                        \"loangrade.MIN(loans.loan_amnt)\":\"min_loan_amt\",\n",
    "                                        \"loangrade.MIN(loans.int_rate)\":\"min_int_rate\",\n",
    "                                        \"loangrade.MEAN(loans.loan_amnt)\":\"mean_loan_amt\",\n",
    "                                        \"loangrade.MEAN(loans.funded_amnt)\":\"mean_funded_amnt\",\n",
    "                                        \"loangrade.MEAN(loans.term(years))\":\"mean_term\",\n",
    "                                        \"loangrade.MEAN(loans.int_rate)\":\"mean_int_rate\",\n",
    "                                        \"loangrade.MEAN(loans.annual_inc)\":\"mean_annual_inc\",\n",
    "                                        \"loangrade.MEAN(loans.dti)\":\"mean_dti\",\n",
    "                                        \"loangrade.MEAN(loans.delinq_2yrs)\":\"mean_delinq_2yrs\",\n",
    "                                        \"loangrade.MEAN(loans.mths_since_last_delinq)\":\"mean_mths_since_last_delinq\",\n",
    "                                        \"loangrade.MEAN(loans.open_acc)\":\"mean_open_cc\",\n",
    "                                        \"loangrade.MEAN(loans.pub_rec)\":\"mean_pub_rec\",\n",
    "                                        \"loangrade.MEAN(loans.total_pymnt)\":\"mean_total_paymnt\",\n",
    "                                        \"loangrade.MEAN(loans.total_rec_int)\":\"mean_rec_int\",\n",
    "                                        \"loangrade.MEAN(loans.recoveries)\":\"mean_recoveries\",\n",
    "                                        \"loangrade.MEAN(loans.annual_inc_joint)\":\"mean_annual_inc_joint\",\n",
    "                                        \"loangrade.MEAN(loans.dti_joint)\":\"mean_dti_joint\",\n",
    "                                        \"loangrade.MEAN(loans.revol_bal)\":\"mean_revol_bal\",\n",
    "                                        \"loangrade.MEAN(loans.tot_cur_bal)\":\"mean_total_cur_bal\",\n",
    "                                        \"loangrade.MEAN(loans.total_rec_late_fee)\":\"mean_total_rec_late_fee\", \n",
    "                                        \"loangrade.COUNT(loans)\":\"loan_count\",\n",
    "                                        \"loangrade.MODE(loans.sub_grade)\":\"mode_loan_subgrade\",\n",
    "                                        \"loangrade.MODE(loans.home_ownership)\":\"mode_home_ownership\",\n",
    "                                        \"loangrade.MODE(loans.purpose)\":\"mode_loan_purpose\",\n",
    "                                        \"loangrade.MODE(loans.addr_state)\":\"mode_addr_state\",\n",
    "                                        \"loangrade.MODE(loans.loan_status)\":\"mode_loan_status\"}, inplace = True)\n",
    "\n",
    "#Writing the data to csv\n",
    "features.to_csv(os.getcwd()+ \"/Features_data_final.csv\",index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "u05hXi2eQBDU"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "accelerator": "TPU",
  "colab": {
   "name": "Untitled.ipynb",
   "provenance": [],
   "version": "0.3.2"
  },
  "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}