ed-final-notebook-part-1-creating-df.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
   },
   "outputs": [],
   "source": [
    "import numpy as np # linear algebra\n",
    "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "%matplotlib inline\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "from sklearn import model_selection\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.preprocessing import RobustScaler\n",
    "import lightgbm as lgb\n",
    "import xgboost as xgb\n",
    "from sklearn.decomposition import PCA,KernelPCA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
    "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a"
   },
   "outputs": [],
   "source": [
    "train_forecloosure = pd.read_csv(\"../input/train_foreclosure.csv\")\n",
    "test_foreclosure = pd.read_csv(\"../input/test_foreclosure.csv\")\n",
    "RF_Final_Data = pd.read_excel(\"../input/RF_Final_Data.xlsx\")\n",
    "LMS = pd.read_excel(\"../input/LMS_31JAN2019.xlsx\")\n",
    "customers = pd.read_excel(\"../input/Customers_31JAN2019.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "764194f6589ba884963632a79b3fc5be0dc9ca37"
   },
   "source": [
    "## Lets Find Missing Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "_uuid": "3320377c736dc79361958e070789b3a5969b7b52"
   },
   "outputs": [],
   "source": [
    "# Function to calculate missing values by column# Funct \n",
    "def missing_values_table(df):\n",
    "        # Total missing values\n",
    "        mis_val = df.isnull().sum()\n",
    "        \n",
    "        # Percentage of missing values\n",
    "        mis_val_percent = 100 * df.isnull().sum() / len(df)\n",
    "        \n",
    "        # Make a table with the results\n",
    "        mis_val_table = pd.concat([mis_val, mis_val_percent], axis=1)\n",
    "        \n",
    "        # Rename the columns\n",
    "        mis_val_table_ren_columns = mis_val_table.rename(\n",
    "        columns = {0 : 'Missing Values', 1 : '% of Total Values'})\n",
    "        \n",
    "        # Sort the table by percentage of missing descending\n",
    "        mis_val_table_ren_columns = mis_val_table_ren_columns[\n",
    "            mis_val_table_ren_columns.iloc[:,1] != 0].sort_values(\n",
    "        '% of Total Values', ascending=False).round(1)\n",
    "        \n",
    "        # Print some summary information\n",
    "        print (\"Your selected dataframe has \" + str(df.shape[1]) + \" columns.\\n\"      \n",
    "            \"There are \" + str(mis_val_table_ren_columns.shape[0]) +\n",
    "              \" columns that have missing values.\")\n",
    "        \n",
    "        # Return the dataframe with missing information\n",
    "        return mis_val_table_ren_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "_uuid": "5806578118e1a0c78655419e4f3d576b77d2fdcd"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Your selected dataframe has 2 columns.\n",
      "There are 0 columns that have missing values.\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>Missing Values</th>\n",
       "      <th>% of Total Values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Missing Values, % of Total Values]\n",
       "Index: []"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Missing values statistics of Customers\n",
    "missing_values = missing_values_table(train_forecloosure)\n",
    "missing_values.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "_uuid": "468fde29e5495abc8bc9fb3bb8f14a65042f0ac7"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Your selected dataframe has 2 columns.\n",
      "There are 0 columns that have missing values.\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>Missing Values</th>\n",
       "      <th>% of Total Values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Missing Values, % of Total Values]\n",
       "Index: []"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Missing values statistics of Customers\n",
    "missing_values = missing_values_table(train_forecloosure)\n",
    "missing_values.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "_uuid": "ff26a5e24cbbd35e352ee566d488d0085e087296"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Your selected dataframe has 9 columns.\n",
      "There are 2 columns that have missing values.\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>Missing Values</th>\n",
       "      <th>% of Total Values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Preprocessed_Subject</th>\n",
       "      <td>183</td>\n",
       "      <td>2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Preprocessed_EmailBody</th>\n",
       "      <td>155</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Missing Values  % of Total Values\n",
       "Preprocessed_Subject               183                2.4\n",
       "Preprocessed_EmailBody             155                2.1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Missing values statistics of Customers\n",
    "missing_values = missing_values_table(RF_Final_Data)\n",
    "missing_values.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "_uuid": "113f1f11627618f645a7bb568720be12d970eb6a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Your selected dataframe has 38 columns.\n",
      "There are 10 columns that have missing values.\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>Missing Values</th>\n",
       "      <th>% of Total Values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NPA_IN_LAST_MONTH</th>\n",
       "      <td>610891</td>\n",
       "      <td>97.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NPA_IN_CURRENT_MONTH</th>\n",
       "      <td>610814</td>\n",
       "      <td>97.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LAST_RECEIPT_AMOUNT</th>\n",
       "      <td>12045</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LAST_RECEIPT_DATE</th>\n",
       "      <td>3787</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUSTOMERID</th>\n",
       "      <td>516</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCHEMEID</th>\n",
       "      <td>516</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DPD</th>\n",
       "      <td>31</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CITY</th>\n",
       "      <td>8</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CURRENT_TENOR</th>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BALANCE_TENURE</th>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Missing Values  % of Total Values\n",
       "NPA_IN_LAST_MONTH             610891               97.9\n",
       "NPA_IN_CURRENT_MONTH          610814               97.8\n",
       "LAST_RECEIPT_AMOUNT            12045                1.9\n",
       "LAST_RECEIPT_DATE               3787                0.6\n",
       "CUSTOMERID                       516                0.1\n",
       "SCHEMEID                         516                0.1\n",
       "DPD                               31                0.0\n",
       "CITY                               8                0.0\n",
       "CURRENT_TENOR                      5                0.0\n",
       "BALANCE_TENURE                     5                0.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Missing values statistics of Customers\n",
    "missing_values = missing_values_table(LMS)\n",
    "missing_values.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "_uuid": "f7b482b97c3f130493fa9413db889f458b46a9f9"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Your selected dataframe has 15 columns.\n",
      "There are 10 columns that have missing values.\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>Missing Values</th>\n",
       "      <th>% of Total Values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>PROFESSION</th>\n",
       "      <td>10000</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OCCUPATION</th>\n",
       "      <td>10000</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>POSITION</th>\n",
       "      <td>9339</td>\n",
       "      <td>93.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRE_JOBYEARS</th>\n",
       "      <td>7625</td>\n",
       "      <td>76.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QUALIFICATION</th>\n",
       "      <td>603</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGE</th>\n",
       "      <td>396</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MARITAL_STATUS</th>\n",
       "      <td>396</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEX</th>\n",
       "      <td>394</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NO_OF_DEPENDENT</th>\n",
       "      <td>79</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BRANCH_PINCODE</th>\n",
       "      <td>73</td>\n",
       "      <td>0.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Missing Values  % of Total Values\n",
       "PROFESSION                10000              100.0\n",
       "OCCUPATION                10000              100.0\n",
       "POSITION                   9339               93.4\n",
       "PRE_JOBYEARS               7625               76.2\n",
       "QUALIFICATION               603                6.0\n",
       "AGE                         396                4.0\n",
       "MARITAL_STATUS              396                4.0\n",
       "SEX                         394                3.9\n",
       "NO_OF_DEPENDENT              79                0.8\n",
       "BRANCH_PINCODE               73                0.7"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Missing values statistics of Customers\n",
    "missing_values = missing_values_table(customers)\n",
    "missing_values.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "a93875d04c4568aa2442e5b47ae1890f18fdf234"
   },
   "source": [
    "## Lets adjust the LMS DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "_uuid": "8bf1403495e971b95a00f042d5dd1868b12b5305"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['AGREEMENTID', 'CUSTOMERID', 'LOAN_AMT', 'NET_DISBURSED_AMT',\n",
       "       'INTEREST_START_DATE', 'CURRENT_ROI', 'ORIGNAL_ROI', 'CURRENT_TENOR',\n",
       "       'ORIGNAL_TENOR', 'DUEDAY', 'AUTHORIZATIONDATE', 'CITY',\n",
       "       'PRE_EMI_DUEAMT', 'PRE_EMI_RECEIVED_AMT', 'PRE_EMI_OS_AMOUNT',\n",
       "       'EMI_DUEAMT', 'EMI_RECEIVED_AMT', 'EMI_OS_AMOUNT', 'EXCESS_AVAILABLE',\n",
       "       'EXCESS_ADJUSTED_AMT', 'BALANCE_EXCESS', 'NET_RECEIVABLE',\n",
       "       'OUTSTANDING_PRINCIPAL', 'PAID_PRINCIPAL', 'PAID_INTEREST',\n",
       "       'MONTHOPENING', 'LAST_RECEIPT_DATE', 'LAST_RECEIPT_AMOUNT', 'NET_LTV',\n",
       "       'COMPLETED_TENURE', 'BALANCE_TENURE', 'DPD', 'FOIR', 'PRODUCT',\n",
       "       'SCHEMEID', 'NPA_IN_LAST_MONTH', 'NPA_IN_CURRENT_MONTH', 'MOB'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "LMS.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "_uuid": "0c0863c9124733ca9bf3466a62cc354616cb0313"
   },
   "outputs": [],
   "source": [
    "LMS=LMS.groupby('AGREEMENTID').agg({      # find the sum of the durations for each group\n",
    "                                     'CUSTOMERID': 'last',\n",
    "                                  'LOAN_AMT':'last',\n",
    "                                  'NET_DISBURSED_AMT':'last',\n",
    "                                  'INTEREST_START_DATE':'last',\n",
    "                                  'CURRENT_ROI':'last',  #Could me mean\n",
    "                                  'ORIGNAL_ROI':'last',\n",
    "                                  'CURRENT_TENOR':'last',\n",
    "                                  'ORIGNAL_TENOR':'last',\n",
    "                                  'DUEDAY':'last',\n",
    "                                  'AUTHORIZATIONDATE':'last',\n",
    "                                  'CITY':'last',\n",
    "                                  'PRE_EMI_DUEAMT':sum,\n",
    "                                  'PRE_EMI_RECEIVED_AMT':sum,\n",
    "                                  'PRE_EMI_OS_AMOUNT':sum,\n",
    "                                  'EMI_DUEAMT':sum,\n",
    "                                  'EMI_RECEIVED_AMT':sum,\n",
    "                                  'EMI_OS_AMOUNT':sum,\n",
    "                                  'EXCESS_AVAILABLE':sum,\n",
    "                                  'EXCESS_ADJUSTED_AMT':sum,\n",
    "                                  'BALANCE_EXCESS':sum,\n",
    "                                  'NET_RECEIVABLE':sum,\n",
    "                                  'OUTSTANDING_PRINCIPAL':'last',\n",
    "                                  'PAID_PRINCIPAL':sum,\n",
    "                                  'PAID_INTEREST':sum,\n",
    "                                  'MONTHOPENING':'last',\n",
    "                                  'LAST_RECEIPT_DATE':'last',\n",
    "                                  'LAST_RECEIPT_AMOUNT':sum,\n",
    "                                  'NET_LTV':'last',\n",
    "                                  'COMPLETED_TENURE':'last',\n",
    "                                  'BALANCE_TENURE':'last',\n",
    "                                  'DPD':'last',\n",
    "                                  'FOIR':'last',\n",
    "                                  'PRODUCT':'last',\n",
    "                                  'SCHEMEID':'last',\n",
    "                                  'NPA_IN_LAST_MONTH':'last',\n",
    "                                  'NPA_IN_CURRENT_MONTH':'last',\n",
    "                                  'MOB':'last'  \n",
    "                                })    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "_uuid": "946f880464d21e74b8dbdd74ad3472c80fb103ff"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Your selected dataframe has 37 columns.\n",
      "There are 6 columns that have missing values.\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>Missing Values</th>\n",
       "      <th>% of Total Values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NPA_IN_LAST_MONTH</th>\n",
       "      <td>20338</td>\n",
       "      <td>61.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NPA_IN_CURRENT_MONTH</th>\n",
       "      <td>20303</td>\n",
       "      <td>60.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUSTOMERID</th>\n",
       "      <td>459</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCHEMEID</th>\n",
       "      <td>459</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LAST_RECEIPT_DATE</th>\n",
       "      <td>128</td>\n",
       "      <td>0.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DPD</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Missing Values  % of Total Values\n",
       "NPA_IN_LAST_MONTH              20338               61.0\n",
       "NPA_IN_CURRENT_MONTH           20303               60.9\n",
       "CUSTOMERID                       459                1.4\n",
       "SCHEMEID                         459                1.4\n",
       "LAST_RECEIPT_DATE                128                0.4\n",
       "DPD                                1                0.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_values = missing_values_table(LMS)\n",
    "missing_values.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "d358607d115910d6b2aef47be596fa887569cbb5"
   },
   "source": [
    "## Lets Merge the Customer and LMS dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "_uuid": "6a64195b0d26dc6ddcfc63b14829b8a714351c01"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10000, 15)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "_uuid": "6835acddfd22b67d7b828659abead07e4caca281"
   },
   "outputs": [],
   "source": [
    "LMS=LMS.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "_uuid": "482bba7ad9274b835973fb15b7cae2bf9e2bf4b4"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7511, 9)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RF_Final_Data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "_uuid": "509470e99343344cc743304eaa565365b9160584"
   },
   "outputs": [],
   "source": [
    "Combine_df = pd.merge(LMS, customers, on='CUSTOMERID',how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "_uuid": "54b514ba9b14747fea82654ae597ee11cd48fd00"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(33354, 52)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Combine_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "_uuid": "f1a665703a02dda6b5cd9703c69fd50ea3116e6c"
   },
   "outputs": [],
   "source": [
    "Combine_df = pd.merge(Combine_df, train_forecloosure, on='AGREEMENTID',how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "_uuid": "971aa98e927f84395c6dba81df5c1ff088def9b5"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Your selected dataframe has 53 columns.\n",
      "There are 21 columns that have missing values.\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>Missing Values</th>\n",
       "      <th>% of Total Values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>PROFESSION</th>\n",
       "      <td>33354</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OCCUPATION</th>\n",
       "      <td>33354</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>POSITION</th>\n",
       "      <td>32693</td>\n",
       "      <td>98.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRE_JOBYEARS</th>\n",
       "      <td>30979</td>\n",
       "      <td>92.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QUALIFICATION</th>\n",
       "      <td>23957</td>\n",
       "      <td>71.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGE</th>\n",
       "      <td>23750</td>\n",
       "      <td>71.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MARITAL_STATUS</th>\n",
       "      <td>23750</td>\n",
       "      <td>71.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEX</th>\n",
       "      <td>23748</td>\n",
       "      <td>71.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NO_OF_DEPENDENT</th>\n",
       "      <td>23433</td>\n",
       "      <td>70.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BRANCH_PINCODE</th>\n",
       "      <td>23427</td>\n",
       "      <td>70.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GROSS_INCOME</th>\n",
       "      <td>23354</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NETTAKEHOMEINCOME</th>\n",
       "      <td>23354</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUST_CONSTTYPE_ID</th>\n",
       "      <td>23354</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUST_CATEGORYID</th>\n",
       "      <td>23354</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NPA_IN_LAST_MONTH</th>\n",
       "      <td>20338</td>\n",
       "      <td>61.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NPA_IN_CURRENT_MONTH</th>\n",
       "      <td>20303</td>\n",
       "      <td>60.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FORECLOSURE</th>\n",
       "      <td>13342</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCHEMEID</th>\n",
       "      <td>459</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUSTOMERID</th>\n",
       "      <td>459</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LAST_RECEIPT_DATE</th>\n",
       "      <td>128</td>\n",
       "      <td>0.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Missing Values  % of Total Values\n",
       "PROFESSION                     33354              100.0\n",
       "OCCUPATION                     33354              100.0\n",
       "POSITION                       32693               98.0\n",
       "PRE_JOBYEARS                   30979               92.9\n",
       "QUALIFICATION                  23957               71.8\n",
       "AGE                            23750               71.2\n",
       "MARITAL_STATUS                 23750               71.2\n",
       "SEX                            23748               71.2\n",
       "NO_OF_DEPENDENT                23433               70.3\n",
       "BRANCH_PINCODE                 23427               70.2\n",
       "GROSS_INCOME                   23354               70.0\n",
       "NETTAKEHOMEINCOME              23354               70.0\n",
       "CUST_CONSTTYPE_ID              23354               70.0\n",
       "CUST_CATEGORYID                23354               70.0\n",
       "NPA_IN_LAST_MONTH              20338               61.0\n",
       "NPA_IN_CURRENT_MONTH           20303               60.9\n",
       "FORECLOSURE                    13342               40.0\n",
       "SCHEMEID                         459                1.4\n",
       "CUSTOMERID                       459                1.4\n",
       "LAST_RECEIPT_DATE                128                0.4"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Missing values statistics of Customers\n",
    "missing_values = missing_values_table(Combine_df)\n",
    "missing_values.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "002fae6ab85939465dd670e9bc80a39d0dbef99b"
   },
   "source": [
    "## Lets Merge Combined DB with RF Final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "_uuid": "b1c468715992bd73020f6180460bb89ac3ad36fe"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['REQUEST', 'General', 'QUERY', 'Unassigned', 'COMPLAINT',\n",
       "       'Request', 'ROI', 'Foreclosure'], dtype=object)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RF_Final_Data['Type'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "_uuid": "6f95cac4faba8eb7d689a10da908e388addb27c1"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['PROVISIONAL IT CERTIFICATE', 'SOA', 'Others', 'CHANGEOFROI',\n",
       "       'TDSREIMBRELATED', 'FORECLOSURE', 'REPAYMENT SCHEDULE',\n",
       "       'FINAL IT CERTIFICATE', 'LISTOFDOCS', 'Unassigned',\n",
       "       'PREPAYEMICHNG', 'Final Income Tax Certificate',\n",
       "       'FINAL IT CERTIFICATE NONHL', 'COPYOFORIGDOCS', 'PREPAYTENORCHNG',\n",
       "       'CHANGEREPAYMODE', 'CHANGEADDRESS', 'EMIPOSTCLOSURE',\n",
       "       'UPDATECONTACTDET', 'Statement of Account',\n",
       "       'PROVISIONAL IT CERTIFICATE NONHL', 'WRONGDEBITEMI',\n",
       "       'INSURPOLNOTRECD', 'STATUSOFLOANAPP', 'REFOFINSUR', 'NONRECOFINSU',\n",
       "       'NONRECTLISTDOCS', 'ORIGDOCNOTRECD', 'Repayment Schedule',\n",
       "       'WRONGCONTACTDET', 'EMINOTBANKED', 'DOCSUBMITFORLOAN', 'COMPLAINT',\n",
       "       'EMI NOT BANKED', 'NO LIEN LETTER REQUIRED',\n",
       "       'ORG PROPERTY PAPERS REQUIRED POST LOAN CLOSURE',\n",
       "       'PAN NO REQUIRED', 'EMI TO BE DEBITED', 'TOP UP LOAN REQUIRED',\n",
       "       'EXCESS EMI REFUND', 'CHANGE IN THE EMI DUE DATE',\n",
       "       'NEFT DETAILS REQ', 'ROC CHARGE REMOVAL', 'NOC REISSUE',\n",
       "       'DISBURSEMENT BREAK UP REQUIRED', 'CIBILTOBEUPDATED',\n",
       "       'ORG SHARE CERTIFICATE REQUIRED FOR TRANSFER', 'WELKITNOTRECD',\n",
       "       'ROI Approval', 'Foreclosure Approval'], dtype=object)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RF_Final_Data['SubType'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "_uuid": "6a980df963582d7f71e4ca8d7c4e77b609b0a868"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7511, 9)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RF_Final_Data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "_uuid": "6492387a7c03cb996f438d143eb1fa4d4a3b64a2"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2709"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RF_Final_Data['Masked_CustomerID'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "_uuid": "97b49a4a3a39b8f42c2c4f6c577e1495b3c4282c"
   },
   "outputs": [],
   "source": [
    "Email_Data = RF_Final_Data[['Type','SubType','Masked_CustomerID']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "_uuid": "3185ff1fde13c87314f897b26efa3663b528eb39"
   },
   "outputs": [],
   "source": [
    "Email_Data = Email_Data.rename(columns={'Masked_CustomerID': 'CUSTOMERID'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "_uuid": "7d03ee26942e7ce4cb6aa0586fdb62d109fbfe3a"
   },
   "outputs": [],
   "source": [
    "Email_Data = pd.get_dummies(Email_Data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "_uuid": "b4f00b1f78387ded12f4069a8cb417d9f0a0521e"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CUSTOMERID', 'Type_COMPLAINT', 'Type_Foreclosure', 'Type_General',\n",
       "       'Type_QUERY', 'Type_REQUEST', 'Type_ROI', 'Type_Request',\n",
       "       'Type_Unassigned', 'SubType_CHANGE IN THE EMI DUE DATE',\n",
       "       'SubType_CHANGEADDRESS', 'SubType_CHANGEOFROI',\n",
       "       'SubType_CHANGEREPAYMODE', 'SubType_CIBILTOBEUPDATED',\n",
       "       'SubType_COMPLAINT', 'SubType_COPYOFORIGDOCS',\n",
       "       'SubType_DISBURSEMENT BREAK UP REQUIRED', 'SubType_DOCSUBMITFORLOAN',\n",
       "       'SubType_EMI NOT BANKED', 'SubType_EMI TO BE DEBITED',\n",
       "       'SubType_EMINOTBANKED', 'SubType_EMIPOSTCLOSURE',\n",
       "       'SubType_EXCESS EMI REFUND', 'SubType_FINAL IT CERTIFICATE',\n",
       "       'SubType_FINAL IT CERTIFICATE NONHL', 'SubType_FORECLOSURE',\n",
       "       'SubType_Final Income Tax Certificate', 'SubType_Foreclosure Approval',\n",
       "       'SubType_INSURPOLNOTRECD', 'SubType_LISTOFDOCS',\n",
       "       'SubType_NEFT DETAILS REQ', 'SubType_NO LIEN LETTER REQUIRED',\n",
       "       'SubType_NOC REISSUE', 'SubType_NONRECOFINSU',\n",
       "       'SubType_NONRECTLISTDOCS',\n",
       "       'SubType_ORG PROPERTY PAPERS REQUIRED POST LOAN CLOSURE',\n",
       "       'SubType_ORG SHARE CERTIFICATE REQUIRED FOR TRANSFER',\n",
       "       'SubType_ORIGDOCNOTRECD', 'SubType_Others', 'SubType_PAN NO REQUIRED',\n",
       "       'SubType_PREPAYEMICHNG', 'SubType_PREPAYTENORCHNG',\n",
       "       'SubType_PROVISIONAL IT CERTIFICATE',\n",
       "       'SubType_PROVISIONAL IT CERTIFICATE NONHL', 'SubType_REFOFINSUR',\n",
       "       'SubType_REPAYMENT SCHEDULE', 'SubType_ROC CHARGE REMOVAL',\n",
       "       'SubType_ROI Approval', 'SubType_Repayment Schedule', 'SubType_SOA',\n",
       "       'SubType_STATUSOFLOANAPP', 'SubType_Statement of Account',\n",
       "       'SubType_TDSREIMBRELATED', 'SubType_TOP UP LOAN REQUIRED',\n",
       "       'SubType_UPDATECONTACTDET', 'SubType_Unassigned',\n",
       "       'SubType_WELKITNOTRECD', 'SubType_WRONGCONTACTDET',\n",
       "       'SubType_WRONGDEBITEMI'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Email_Data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "_uuid": "a1ad59f8f350e2e136bb9a9f0d1e2457ba536e69"
   },
   "outputs": [],
   "source": [
    "Email_Data_groupby=Email_Data.groupby('CUSTOMERID').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "_uuid": "6b6d8b40ecbf6a74818c4f91eecd9a96207d64d5"
   },
   "outputs": [],
   "source": [
    "Email_Data_groupby = Email_Data_groupby.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "_uuid": "4a2b7667f86e5b3b9b739f5a8c1a8371a921fddc"
   },
   "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>CUSTOMERID</th>\n",
       "      <th>Type_COMPLAINT</th>\n",
       "      <th>Type_Foreclosure</th>\n",
       "      <th>Type_General</th>\n",
       "      <th>Type_QUERY</th>\n",
       "      <th>Type_REQUEST</th>\n",
       "      <th>Type_ROI</th>\n",
       "      <th>Type_Request</th>\n",
       "      <th>Type_Unassigned</th>\n",
       "      <th>SubType_CHANGE IN THE EMI DUE DATE</th>\n",
       "      <th>SubType_CHANGEADDRESS</th>\n",
       "      <th>SubType_CHANGEOFROI</th>\n",
       "      <th>SubType_CHANGEREPAYMODE</th>\n",
       "      <th>SubType_CIBILTOBEUPDATED</th>\n",
       "      <th>SubType_COMPLAINT</th>\n",
       "      <th>SubType_COPYOFORIGDOCS</th>\n",
       "      <th>SubType_DISBURSEMENT BREAK UP REQUIRED</th>\n",
       "      <th>SubType_DOCSUBMITFORLOAN</th>\n",
       "      <th>SubType_EMI NOT BANKED</th>\n",
       "      <th>SubType_EMI TO BE DEBITED</th>\n",
       "      <th>SubType_EMINOTBANKED</th>\n",
       "      <th>SubType_EMIPOSTCLOSURE</th>\n",
       "      <th>SubType_EXCESS EMI REFUND</th>\n",
       "      <th>SubType_FINAL IT CERTIFICATE</th>\n",
       "      <th>SubType_FINAL IT CERTIFICATE NONHL</th>\n",
       "      <th>SubType_FORECLOSURE</th>\n",
       "      <th>SubType_Final Income Tax Certificate</th>\n",
       "      <th>SubType_Foreclosure Approval</th>\n",
       "      <th>SubType_INSURPOLNOTRECD</th>\n",
       "      <th>SubType_LISTOFDOCS</th>\n",
       "      <th>SubType_NEFT DETAILS REQ</th>\n",
       "      <th>SubType_NO LIEN LETTER REQUIRED</th>\n",
       "      <th>SubType_NOC REISSUE</th>\n",
       "      <th>SubType_NONRECOFINSU</th>\n",
       "      <th>SubType_NONRECTLISTDOCS</th>\n",
       "      <th>SubType_ORG PROPERTY PAPERS REQUIRED POST LOAN CLOSURE</th>\n",
       "      <th>SubType_ORG SHARE CERTIFICATE REQUIRED FOR TRANSFER</th>\n",
       "      <th>SubType_ORIGDOCNOTRECD</th>\n",
       "      <th>SubType_Others</th>\n",
       "      <th>SubType_PAN NO REQUIRED</th>\n",
       "      <th>SubType_PREPAYEMICHNG</th>\n",
       "      <th>SubType_PREPAYTENORCHNG</th>\n",
       "      <th>SubType_PROVISIONAL IT CERTIFICATE</th>\n",
       "      <th>SubType_PROVISIONAL IT CERTIFICATE NONHL</th>\n",
       "      <th>SubType_REFOFINSUR</th>\n",
       "      <th>SubType_REPAYMENT SCHEDULE</th>\n",
       "      <th>SubType_ROC CHARGE REMOVAL</th>\n",
       "      <th>SubType_ROI Approval</th>\n",
       "      <th>SubType_Repayment Schedule</th>\n",
       "      <th>SubType_SOA</th>\n",
       "      <th>SubType_STATUSOFLOANAPP</th>\n",
       "      <th>SubType_Statement of Account</th>\n",
       "      <th>SubType_TDSREIMBRELATED</th>\n",
       "      <th>SubType_TOP UP LOAN REQUIRED</th>\n",
       "      <th>SubType_UPDATECONTACTDET</th>\n",
       "      <th>SubType_Unassigned</th>\n",
       "      <th>SubType_WELKITNOTRECD</th>\n",
       "      <th>SubType_WRONGCONTACTDET</th>\n",
       "      <th>SubType_WRONGDEBITEMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12001002</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "      <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",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12001003</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <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",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12001005</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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>3</th>\n",
       "      <td>12001009</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12001010</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</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",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CUSTOMERID          ...            SubType_WRONGDEBITEMI\n",
       "0    12001002          ...                                0\n",
       "1    12001003          ...                                0\n",
       "2    12001005          ...                                0\n",
       "3    12001009          ...                                0\n",
       "4    12001010          ...                                0\n",
       "\n",
       "[5 rows x 59 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Email_Data_groupby.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "_uuid": "7a03a348ac73098228df5f31823a42672e4131e6"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2709"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Email_Data_groupby['CUSTOMERID'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "_uuid": "655e3aa0aed3c3149a541e310d816eee4c528052"
   },
   "outputs": [],
   "source": [
    "Combine_df_with_RF_Data = pd.merge(Combine_df, Email_Data_groupby, on='CUSTOMERID',how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "_uuid": "a06bffd42d6736ffe49e71b2272ca0cfb871de72"
   },
   "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>AGREEMENTID</th>\n",
       "      <th>CUSTOMERID</th>\n",
       "      <th>LOAN_AMT</th>\n",
       "      <th>NET_DISBURSED_AMT</th>\n",
       "      <th>INTEREST_START_DATE</th>\n",
       "      <th>CURRENT_ROI</th>\n",
       "      <th>ORIGNAL_ROI</th>\n",
       "      <th>CURRENT_TENOR</th>\n",
       "      <th>ORIGNAL_TENOR</th>\n",
       "      <th>DUEDAY</th>\n",
       "      <th>AUTHORIZATIONDATE</th>\n",
       "      <th>CITY</th>\n",
       "      <th>PRE_EMI_DUEAMT</th>\n",
       "      <th>PRE_EMI_RECEIVED_AMT</th>\n",
       "      <th>PRE_EMI_OS_AMOUNT</th>\n",
       "      <th>EMI_DUEAMT</th>\n",
       "      <th>EMI_RECEIVED_AMT</th>\n",
       "      <th>EMI_OS_AMOUNT</th>\n",
       "      <th>EXCESS_AVAILABLE</th>\n",
       "      <th>EXCESS_ADJUSTED_AMT</th>\n",
       "      <th>BALANCE_EXCESS</th>\n",
       "      <th>NET_RECEIVABLE</th>\n",
       "      <th>OUTSTANDING_PRINCIPAL</th>\n",
       "      <th>PAID_PRINCIPAL</th>\n",
       "      <th>PAID_INTEREST</th>\n",
       "      <th>MONTHOPENING</th>\n",
       "      <th>LAST_RECEIPT_DATE</th>\n",
       "      <th>LAST_RECEIPT_AMOUNT</th>\n",
       "      <th>NET_LTV</th>\n",
       "      <th>COMPLETED_TENURE</th>\n",
       "      <th>BALANCE_TENURE</th>\n",
       "      <th>DPD</th>\n",
       "      <th>FOIR</th>\n",
       "      <th>PRODUCT</th>\n",
       "      <th>SCHEMEID</th>\n",
       "      <th>NPA_IN_LAST_MONTH</th>\n",
       "      <th>NPA_IN_CURRENT_MONTH</th>\n",
       "      <th>MOB</th>\n",
       "      <th>CUST_CONSTTYPE_ID</th>\n",
       "      <th>CUST_CATEGORYID</th>\n",
       "      <th>...</th>\n",
       "      <th>SubType_EMI TO BE DEBITED</th>\n",
       "      <th>SubType_EMINOTBANKED</th>\n",
       "      <th>SubType_EMIPOSTCLOSURE</th>\n",
       "      <th>SubType_EXCESS EMI REFUND</th>\n",
       "      <th>SubType_FINAL IT CERTIFICATE</th>\n",
       "      <th>SubType_FINAL IT CERTIFICATE NONHL</th>\n",
       "      <th>SubType_FORECLOSURE</th>\n",
       "      <th>SubType_Final Income Tax Certificate</th>\n",
       "      <th>SubType_Foreclosure Approval</th>\n",
       "      <th>SubType_INSURPOLNOTRECD</th>\n",
       "      <th>SubType_LISTOFDOCS</th>\n",
       "      <th>SubType_NEFT DETAILS REQ</th>\n",
       "      <th>SubType_NO LIEN LETTER REQUIRED</th>\n",
       "      <th>SubType_NOC REISSUE</th>\n",
       "      <th>SubType_NONRECOFINSU</th>\n",
       "      <th>SubType_NONRECTLISTDOCS</th>\n",
       "      <th>SubType_ORG PROPERTY PAPERS REQUIRED POST LOAN CLOSURE</th>\n",
       "      <th>SubType_ORG SHARE CERTIFICATE REQUIRED FOR TRANSFER</th>\n",
       "      <th>SubType_ORIGDOCNOTRECD</th>\n",
       "      <th>SubType_Others</th>\n",
       "      <th>SubType_PAN NO REQUIRED</th>\n",
       "      <th>SubType_PREPAYEMICHNG</th>\n",
       "      <th>SubType_PREPAYTENORCHNG</th>\n",
       "      <th>SubType_PROVISIONAL IT CERTIFICATE</th>\n",
       "      <th>SubType_PROVISIONAL IT CERTIFICATE NONHL</th>\n",
       "      <th>SubType_REFOFINSUR</th>\n",
       "      <th>SubType_REPAYMENT SCHEDULE</th>\n",
       "      <th>SubType_ROC CHARGE REMOVAL</th>\n",
       "      <th>SubType_ROI Approval</th>\n",
       "      <th>SubType_Repayment Schedule</th>\n",
       "      <th>SubType_SOA</th>\n",
       "      <th>SubType_STATUSOFLOANAPP</th>\n",
       "      <th>SubType_Statement of Account</th>\n",
       "      <th>SubType_TDSREIMBRELATED</th>\n",
       "      <th>SubType_TOP UP LOAN REQUIRED</th>\n",
       "      <th>SubType_UPDATECONTACTDET</th>\n",
       "      <th>SubType_Unassigned</th>\n",
       "      <th>SubType_WELKITNOTRECD</th>\n",
       "      <th>SubType_WRONGCONTACTDET</th>\n",
       "      <th>SubType_WRONGDEBITEMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11220001</td>\n",
       "      <td>12001000.0</td>\n",
       "      <td>1.171011e+07</td>\n",
       "      <td>1.171011e+07</td>\n",
       "      <td>2010-08-30</td>\n",
       "      <td>13.421934</td>\n",
       "      <td>10.612692</td>\n",
       "      <td>45.0</td>\n",
       "      <td>120</td>\n",
       "      <td>1</td>\n",
       "      <td>2010-08-29</td>\n",
       "      <td>MUMBAI</td>\n",
       "      <td>55959.915122</td>\n",
       "      <td>55959.915122</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.629871e+07</td>\n",
       "      <td>5.520051e+07</td>\n",
       "      <td>1.098200e+06</td>\n",
       "      <td>7.569304e+05</td>\n",
       "      <td>7.569304e+05</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>1.098200e+06</td>\n",
       "      <td>1.164472e+05</td>\n",
       "      <td>6.987372e+07</td>\n",
       "      <td>1.871981e+07</td>\n",
       "      <td>8.693575e+06</td>\n",
       "      <td>2014-05-05</td>\n",
       "      <td>1501354.00</td>\n",
       "      <td>40.06</td>\n",
       "      <td>45</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.6000</td>\n",
       "      <td>HL</td>\n",
       "      <td>10901100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <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>11220002</td>\n",
       "      <td>12001001.0</td>\n",
       "      <td>1.929025e+07</td>\n",
       "      <td>1.929025e+07</td>\n",
       "      <td>2010-09-15</td>\n",
       "      <td>13.734072</td>\n",
       "      <td>10.612692</td>\n",
       "      <td>137.0</td>\n",
       "      <td>180</td>\n",
       "      <td>1</td>\n",
       "      <td>2010-09-15</td>\n",
       "      <td>MUMBAI</td>\n",
       "      <td>100504.575864</td>\n",
       "      <td>100504.575864</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.061903e+07</td>\n",
       "      <td>1.033958e+07</td>\n",
       "      <td>2.794481e+05</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>2.794481e+05</td>\n",
       "      <td>1.234760e+07</td>\n",
       "      <td>6.942656e+06</td>\n",
       "      <td>4.945154e+06</td>\n",
       "      <td>1.713861e+07</td>\n",
       "      <td>2013-11-01</td>\n",
       "      <td>126530.00</td>\n",
       "      <td>84.31</td>\n",
       "      <td>38</td>\n",
       "      <td>99.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.6000</td>\n",
       "      <td>HL</td>\n",
       "      <td>10901100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <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>11220003</td>\n",
       "      <td>12001002.0</td>\n",
       "      <td>4.053499e+06</td>\n",
       "      <td>4.053499e+06</td>\n",
       "      <td>2010-09-06</td>\n",
       "      <td>17.167590</td>\n",
       "      <td>12.485520</td>\n",
       "      <td>360.0</td>\n",
       "      <td>180</td>\n",
       "      <td>5</td>\n",
       "      <td>2010-09-06</td>\n",
       "      <td>MUMBAI</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.055559e+08</td>\n",
       "      <td>2.055559e+08</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>3.139265e+06</td>\n",
       "      <td>3.139033e+06</td>\n",
       "      <td>231.89796</td>\n",
       "      <td>-2.318980e+02</td>\n",
       "      <td>3.917012e+06</td>\n",
       "      <td>7.461579e+06</td>\n",
       "      <td>1.322681e+08</td>\n",
       "      <td>3.917012e+06</td>\n",
       "      <td>2015-08-05</td>\n",
       "      <td>1424381.21</td>\n",
       "      <td>79.34</td>\n",
       "      <td>59</td>\n",
       "      <td>301.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.9100</td>\n",
       "      <td>HL</td>\n",
       "      <td>10901101.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>59</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11220004</td>\n",
       "      <td>12001003.0</td>\n",
       "      <td>1.325703e+07</td>\n",
       "      <td>1.325703e+07</td>\n",
       "      <td>2010-09-29</td>\n",
       "      <td>15.606900</td>\n",
       "      <td>10.924830</td>\n",
       "      <td>198.0</td>\n",
       "      <td>180</td>\n",
       "      <td>5</td>\n",
       "      <td>2010-09-29</td>\n",
       "      <td>MUMBAI</td>\n",
       "      <td>28441.732656</td>\n",
       "      <td>28441.732656</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.211965e+06</td>\n",
       "      <td>7.211965e+06</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.030278e+07</td>\n",
       "      <td>2.954253e+06</td>\n",
       "      <td>4.219464e+06</td>\n",
       "      <td>1.255472e+07</td>\n",
       "      <td>2013-11-05</td>\n",
       "      <td>88256.00</td>\n",
       "      <td>86.86</td>\n",
       "      <td>37</td>\n",
       "      <td>161.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.9686</td>\n",
       "      <td>HL</td>\n",
       "      <td>10901101.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11220005</td>\n",
       "      <td>12001004.0</td>\n",
       "      <td>1.321140e+07</td>\n",
       "      <td>1.321140e+07</td>\n",
       "      <td>2010-09-06</td>\n",
       "      <td>16.543314</td>\n",
       "      <td>11.861244</td>\n",
       "      <td>142.0</td>\n",
       "      <td>180</td>\n",
       "      <td>5</td>\n",
       "      <td>2010-09-06</td>\n",
       "      <td>MUMBAI</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.671444e+07</td>\n",
       "      <td>7.671444e+07</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.014741e+06</td>\n",
       "      <td>1.014741e+06</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>8.110481e+06</td>\n",
       "      <td>4.422800e+07</td>\n",
       "      <td>3.764543e+07</td>\n",
       "      <td>1.141333e+07</td>\n",
       "      <td>2014-07-05</td>\n",
       "      <td>827028.00</td>\n",
       "      <td>40.93</td>\n",
       "      <td>46</td>\n",
       "      <td>96.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.7300</td>\n",
       "      <td>HL</td>\n",
       "      <td>10901101.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <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",
       "</div>"
      ],
      "text/plain": [
       "   AGREEMENTID          ...            SubType_WRONGDEBITEMI\n",
       "0     11220001          ...                              NaN\n",
       "1     11220002          ...                              NaN\n",
       "2     11220003          ...                              0.0\n",
       "3     11220004          ...                              0.0\n",
       "4     11220005          ...                              NaN\n",
       "\n",
       "[5 rows x 111 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Combine_df_with_RF_Data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "_uuid": "be3f983ca8ee863eae26801ea661a0b2c89fe8d1"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(33354, 111)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Combine_df_with_RF_Data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "6bde5f295924b6a8c33d5e8c153b84499cbf4458"
   },
   "source": [
    "## We now have our DF , Lets try and delete missing columns and create train and test data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "_uuid": "2e55865085fc9a10b3c7df8b76b7c2b92c3042bc"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Your selected dataframe has 111 columns.\n",
      "There are 79 columns that have missing values.\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>Missing Values</th>\n",
       "      <th>% of Total Values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>PROFESSION</th>\n",
       "      <td>33354</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OCCUPATION</th>\n",
       "      <td>33354</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>POSITION</th>\n",
       "      <td>32693</td>\n",
       "      <td>98.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRE_JOBYEARS</th>\n",
       "      <td>30979</td>\n",
       "      <td>92.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_EMI TO BE DEBITED</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_NONRECOFINSU</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_Others</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_ORIGDOCNOTRECD</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_ORG SHARE CERTIFICATE REQUIRED FOR TRANSFER</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_ORG PROPERTY PAPERS REQUIRED POST LOAN CLOSURE</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_NONRECTLISTDOCS</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_NOC REISSUE</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_PREPAYEMICHNG</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_NO LIEN LETTER REQUIRED</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_NEFT DETAILS REQ</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_LISTOFDOCS</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_INSURPOLNOTRECD</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_Foreclosure Approval</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_Final Income Tax Certificate</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_FORECLOSURE</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_FINAL IT CERTIFICATE NONHL</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_PAN NO REQUIRED</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_PROVISIONAL IT CERTIFICATE NONHL</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_PREPAYTENORCHNG</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_PROVISIONAL IT CERTIFICATE</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_WRONGCONTACTDET</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_WELKITNOTRECD</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_Unassigned</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_UPDATECONTACTDET</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_TOP UP LOAN REQUIRED</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Type_ROI</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Type_Request</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Type_Unassigned</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_CHANGE IN THE EMI DUE DATE</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Type_General</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_CHANGEOFROI</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_CIBILTOBEUPDATED</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_COMPLAINT</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_COPYOFORIGDOCS</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_DISBURSEMENT BREAK UP REQUIRED</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_DOCSUBMITFORLOAN</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_EMI NOT BANKED</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SubType_CHANGEREPAYMODE</th>\n",
       "      <td>30645</td>\n",
       "      <td>91.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QUALIFICATION</th>\n",
       "      <td>23957</td>\n",
       "      <td>71.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGE</th>\n",
       "      <td>23750</td>\n",
       "      <td>71.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MARITAL_STATUS</th>\n",
       "      <td>23750</td>\n",
       "      <td>71.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEX</th>\n",
       "      <td>23748</td>\n",
       "      <td>71.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NO_OF_DEPENDENT</th>\n",
       "      <td>23433</td>\n",
       "      <td>70.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BRANCH_PINCODE</th>\n",
       "      <td>23427</td>\n",
       "      <td>70.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUST_CONSTTYPE_ID</th>\n",
       "      <td>23354</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUST_CATEGORYID</th>\n",
       "      <td>23354</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GROSS_INCOME</th>\n",
       "      <td>23354</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NETTAKEHOMEINCOME</th>\n",
       "      <td>23354</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NPA_IN_LAST_MONTH</th>\n",
       "      <td>20338</td>\n",
       "      <td>61.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NPA_IN_CURRENT_MONTH</th>\n",
       "      <td>20303</td>\n",
       "      <td>60.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FORECLOSURE</th>\n",
       "      <td>13342</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCHEMEID</th>\n",
       "      <td>459</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUSTOMERID</th>\n",
       "      <td>459</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LAST_RECEIPT_DATE</th>\n",
       "      <td>128</td>\n",
       "      <td>0.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DPD</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>79 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    Missing Values  % of Total Values\n",
       "PROFESSION                                                   33354              100.0\n",
       "OCCUPATION                                                   33354              100.0\n",
       "POSITION                                                     32693               98.0\n",
       "PRE_JOBYEARS                                                 30979               92.9\n",
       "SubType_EMI TO BE DEBITED                                    30645               91.9\n",
       "SubType_NONRECOFINSU                                         30645               91.9\n",
       "SubType_Others                                               30645               91.9\n",
       "SubType_ORIGDOCNOTRECD                                       30645               91.9\n",
       "SubType_ORG SHARE CERTIFICATE REQUIRED FOR TRAN...           30645               91.9\n",
       "SubType_ORG PROPERTY PAPERS REQUIRED POST LOAN ...           30645               91.9\n",
       "SubType_NONRECTLISTDOCS                                      30645               91.9\n",
       "SubType_NOC REISSUE                                          30645               91.9\n",
       "SubType_PREPAYEMICHNG                                        30645               91.9\n",
       "SubType_NO LIEN LETTER REQUIRED                              30645               91.9\n",
       "SubType_NEFT DETAILS REQ                                     30645               91.9\n",
       "SubType_LISTOFDOCS                                           30645               91.9\n",
       "SubType_INSURPOLNOTRECD                                      30645               91.9\n",
       "SubType_Foreclosure Approval                                 30645               91.9\n",
       "SubType_Final Income Tax Certificate                         30645               91.9\n",
       "SubType_FORECLOSURE                                          30645               91.9\n",
       "SubType_FINAL IT CERTIFICATE NONHL                           30645               91.9\n",
       "SubType_PAN NO REQUIRED                                      30645               91.9\n",
       "SubType_PROVISIONAL IT CERTIFICATE NONHL                     30645               91.9\n",
       "SubType_PREPAYTENORCHNG                                      30645               91.9\n",
       "SubType_PROVISIONAL IT CERTIFICATE                           30645               91.9\n",
       "SubType_WRONGCONTACTDET                                      30645               91.9\n",
       "SubType_WELKITNOTRECD                                        30645               91.9\n",
       "SubType_Unassigned                                           30645               91.9\n",
       "SubType_UPDATECONTACTDET                                     30645               91.9\n",
       "SubType_TOP UP LOAN REQUIRED                                 30645               91.9\n",
       "...                                                            ...                ...\n",
       "Type_ROI                                                     30645               91.9\n",
       "Type_Request                                                 30645               91.9\n",
       "Type_Unassigned                                              30645               91.9\n",
       "SubType_CHANGE IN THE EMI DUE DATE                           30645               91.9\n",
       "Type_General                                                 30645               91.9\n",
       "SubType_CHANGEOFROI                                          30645               91.9\n",
       "SubType_CIBILTOBEUPDATED                                     30645               91.9\n",
       "SubType_COMPLAINT                                            30645               91.9\n",
       "SubType_COPYOFORIGDOCS                                       30645               91.9\n",
       "SubType_DISBURSEMENT BREAK UP REQUIRED                       30645               91.9\n",
       "SubType_DOCSUBMITFORLOAN                                     30645               91.9\n",
       "SubType_EMI NOT BANKED                                       30645               91.9\n",
       "SubType_CHANGEREPAYMODE                                      30645               91.9\n",
       "QUALIFICATION                                                23957               71.8\n",
       "AGE                                                          23750               71.2\n",
       "MARITAL_STATUS                                               23750               71.2\n",
       "SEX                                                          23748               71.2\n",
       "NO_OF_DEPENDENT                                              23433               70.3\n",
       "BRANCH_PINCODE                                               23427               70.2\n",
       "CUST_CONSTTYPE_ID                                            23354               70.0\n",
       "CUST_CATEGORYID                                              23354               70.0\n",
       "GROSS_INCOME                                                 23354               70.0\n",
       "NETTAKEHOMEINCOME                                            23354               70.0\n",
       "NPA_IN_LAST_MONTH                                            20338               61.0\n",
       "NPA_IN_CURRENT_MONTH                                         20303               60.9\n",
       "FORECLOSURE                                                  13342               40.0\n",
       "SCHEMEID                                                       459                1.4\n",
       "CUSTOMERID                                                     459                1.4\n",
       "LAST_RECEIPT_DATE                                              128                0.4\n",
       "DPD                                                              1                0.0\n",
       "\n",
       "[79 rows x 2 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_values = missing_values_table(Combine_df_with_RF_Data)\n",
    "missing_values.head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "_uuid": "f3e0bb6c1aca762881ff1bd83799154181b7d081"
   },
   "outputs": [],
   "source": [
    "Combine_df_with_RF_Data = Combine_df_with_RF_Data.drop(['PROFESSION','OCCUPATION','PRE_JOBYEARS','POSITION'],axis=1)\n",
    "Combine_df= Combine_df.drop(['PROFESSION','OCCUPATION','PRE_JOBYEARS','POSITION'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "_uuid": "b9fc4f616335dba67f7c4924860e5845b1b93310"
   },
   "outputs": [],
   "source": [
    "train_df = Combine_df[Combine_df['FORECLOSURE'].notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "_uuid": "5bdc4540380f20d98790f6507578e91b92bf1c97"
   },
   "outputs": [],
   "source": [
    "test_df = Combine_df[Combine_df['FORECLOSURE'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "_uuid": "2b21ec999bc564b8b6322938198b50c7c7172fe9"
   },
   "outputs": [],
   "source": [
    "train_df_with_RF = Combine_df_with_RF_Data[Combine_df_with_RF_Data['FORECLOSURE'].notnull()]\n",
    "test_df_with_RF = Combine_df_with_RF_Data[Combine_df_with_RF_Data['FORECLOSURE'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "_uuid": "a3cd90f2cf061b962a19b5138838d1d4d5642e10"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(20012, 49)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "_uuid": "a6e13859d4c95ff0dff4dddd75276cee8707a18b"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(13342, 49)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "_uuid": "fdf97767b479f72dc6f9caecd540c1311e58f233"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(20012, 107)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df_with_RF.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "_uuid": "e4af1f0c61fd48162ba342d4c353998c4995ec5c"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(13342, 107)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df_with_RF.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "_uuid": "6e363b4a1481d7b86c6d51a7ca7bfb926a495ccd"
   },
   "outputs": [],
   "source": [
    "train_df.to_csv('train_df.csv',index=False)\n",
    "test_df.to_csv('test_df.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "_uuid": "fd4b09658cc6ffd24b1346c5747c81a2c3df3ccd"
   },
   "outputs": [],
   "source": [
    "train_df_with_RF.to_csv('train_df_with_RF.csv',index=False)\n",
    "test_df_with_RF.to_csv('test_df_with_RF.csv',index=False)"
   ]
  }
 ],
 "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}