logit_regression_on_mandate.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.max_rows', 1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\5029874\\AppData\\Local\\Continuum\\anaconda3\\envs\\trainingenv\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3020: DtypeWarning: Columns (16,38,69) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "data = pd.read_csv(\"D:\\CD\\CD_Variables_Final_v3.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filt = data1[['PROPNO']].groupby(['PROPNO']).size().reset_index(name='counts')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filt.loc[filt['counts'] == 2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PROPNO</th>\n",
       "      <th>AGMTNO</th>\n",
       "      <th>DEALER_CODE</th>\n",
       "      <th>PORTFOLIO</th>\n",
       "      <th>PREPAIDPOSTPAIDFLAG</th>\n",
       "      <th>AVGMOBILEBILLAMOUNTLAST3MON</th>\n",
       "      <th>AVGMOBRECHARGEAMOUNTLAST3MON</th>\n",
       "      <th>INSTALLTIME</th>\n",
       "      <th>LASTPINGTIME</th>\n",
       "      <th>REQUESTTIME</th>\n",
       "      <th>DEALER_TYPE</th>\n",
       "      <th>PRODUCT_CODE</th>\n",
       "      <th>MODEL_CODE</th>\n",
       "      <th>SCHEME_TYPE</th>\n",
       "      <th>LTV</th>\n",
       "      <th>STATE</th>\n",
       "      <th>IMEI_NUMBER</th>\n",
       "      <th>MAKE_CODE</th>\n",
       "      <th>MODEL_DESC</th>\n",
       "      <th>MODEL_DESC_V2_MAKE</th>\n",
       "      <th>DEALER_CITY</th>\n",
       "      <th>REGISTRATION_TYPE</th>\n",
       "      <th>SCHEME_CODE</th>\n",
       "      <th>CIBIL_HIT</th>\n",
       "      <th>SURROGATE</th>\n",
       "      <th>ACTUAL_LOAN_AMOUNT</th>\n",
       "      <th>CITY</th>\n",
       "      <th>LATESTBALANCE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>QUALIFICATION</th>\n",
       "      <th>AGE</th>\n",
       "      <th>CIBIL_SCORE</th>\n",
       "      <th>EXISTING_CUSTOMER</th>\n",
       "      <th>SPL_INSURANCE_TYPE</th>\n",
       "      <th>ADDRESS_TYPE</th>\n",
       "      <th>RESIDENT_TYPE</th>\n",
       "      <th>ADDRESS1</th>\n",
       "      <th>ADDRESS2</th>\n",
       "      <th>ADDRESS3</th>\n",
       "      <th>VOTERID</th>\n",
       "      <th>PANNO</th>\n",
       "      <th>DRIVING_LICENSE</th>\n",
       "      <th>PASSPORT</th>\n",
       "      <th>AADHAR</th>\n",
       "      <th>RATION</th>\n",
       "      <th>ACTIVITY_CODE</th>\n",
       "      <th>TAT_HOUR</th>\n",
       "      <th>TAT_DAYS</th>\n",
       "      <th>TAT_DAYS_EXCLUDE_SUNDAY</th>\n",
       "      <th>PAYMENT_TYPE</th>\n",
       "      <th>IFSC_CODE</th>\n",
       "      <th>ELIGIBILITYGRID</th>\n",
       "      <th>ELIGIBILITYGRID_REMARK</th>\n",
       "      <th>IS_MANDATE_REGISTERED</th>\n",
       "      <th>DELAER_NAME</th>\n",
       "      <th>DELAER_CATEGORY</th>\n",
       "      <th>IS_MOBILE</th>\n",
       "      <th>Mobile_active_hours</th>\n",
       "      <th>Mobile_Bill</th>\n",
       "      <th>Mobile_Bill_bin</th>\n",
       "      <th>IS_CIBIL_HIT</th>\n",
       "      <th>SURROGATE_CAT</th>\n",
       "      <th>TICKET_SIZE</th>\n",
       "      <th>CROSS_SELL_INSURANCE</th>\n",
       "      <th>DEALER_CITY_V2</th>\n",
       "      <th>BANK_NAME</th>\n",
       "      <th>MICR_CODE</th>\n",
       "      <th>BANK_BRANCH</th>\n",
       "      <th>BANK_ADDRESS</th>\n",
       "      <th>BANK_CONTACT</th>\n",
       "      <th>BANK_CITY</th>\n",
       "      <th>BANK_DISTRICT</th>\n",
       "      <th>BANK_STATE</th>\n",
       "      <th>DEALER_CUST_CITY</th>\n",
       "      <th>CUST_BANK_CITY_DIFF</th>\n",
       "      <th>AGE_BIN</th>\n",
       "      <th>CIBIL_SCORE_NEW</th>\n",
       "      <th>CIBIL_SCORE_NEW_BIN</th>\n",
       "      <th>ADDRESS1_LEN</th>\n",
       "      <th>ADDRESS2_LEN</th>\n",
       "      <th>ADDRESS3_LEN</th>\n",
       "      <th>AGMTNO_v1</th>\n",
       "      <th>applAssetCtg</th>\n",
       "      <th>applAssetMake</th>\n",
       "      <th>D_LOGIN_TIME</th>\n",
       "      <th>LOGIN_dateTime</th>\n",
       "      <th>croApprovedDate</th>\n",
       "      <th>loanDisbDate</th>\n",
       "      <th>residenceCity</th>\n",
       "      <th>Cust_bankName</th>\n",
       "      <th>bankIFSCCode</th>\n",
       "      <th>Bank_Name</th>\n",
       "      <th>Bank_Type</th>\n",
       "      <th>Actual_TAT_Days</th>\n",
       "      <th>Disp_Month_bin</th>\n",
       "      <th>IS_WEEKEND</th>\n",
       "      <th>Processing Unit</th>\n",
       "      <th>CONTACT1</th>\n",
       "      <th>CONTACT2</th>\n",
       "      <th>MOBILE1</th>\n",
       "      <th>MOBILE2</th>\n",
       "      <th>MOBILE1_len</th>\n",
       "      <th>IFSC_LEN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3078CD0009160</td>\n",
       "      <td>WB3078CD0009160</td>\n",
       "      <td>CD00910</td>\n",
       "      <td>CD</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2018-04-23T12:10:07.466Z</td>\n",
       "      <td>2018-04-23T12:14:29.339Z</td>\n",
       "      <td>2018-04-23T20:12:08.774Z</td>\n",
       "      <td>DEALER</td>\n",
       "      <td>TV</td>\n",
       "      <td>CD0003748</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>68.18</td>\n",
       "      <td>WB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SAMSUNG</td>\n",
       "      <td>SAMSUNG TV UA43K5002AKXXL</td>\n",
       "      <td>SAMSUNG TV</td>\n",
       "      <td>DT1234</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CD_M_0085</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32611</td>\n",
       "      <td>Jalpaiguri</td>\n",
       "      <td>190758.16</td>\n",
       "      <td>MALE</td>\n",
       "      <td>SSC</td>\n",
       "      <td>32</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RS</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>DUMCHIPARA TEA GARDEN</td>\n",
       "      <td>JATHA LINE.   .</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>DISBURSAL_TAT</td>\n",
       "      <td>22.52</td>\n",
       "      <td>0.94</td>\n",
       "      <td>0.94</td>\n",
       "      <td>ADM</td>\n",
       "      <td>SBIN0002025</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No eligibility criteria matched</td>\n",
       "      <td>1</td>\n",
       "      <td>GOYAL RADIOS</td>\n",
       "      <td>DD</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>Jalpaiguri</td>\n",
       "      <td>STATE BANK OF INDIA</td>\n",
       "      <td>735002505</td>\n",
       "      <td>BIRPARA</td>\n",
       "      <td>PO BIRPARA,JALPAIGURI,WB</td>\n",
       "      <td>0</td>\n",
       "      <td>BIRPARA</td>\n",
       "      <td>JALPAIGURI</td>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>WB3078CD0009160</td>\n",
       "      <td>TELEVISION</td>\n",
       "      <td>SAMSUNG</td>\n",
       "      <td>23/04/2018</td>\n",
       "      <td>2018-04-23T12:10:20.784Z</td>\n",
       "      <td>2018-04-23T12:11:07.154Z</td>\n",
       "      <td>24/04/2018</td>\n",
       "      <td>GARGANDA B.O</td>\n",
       "      <td>STATE BANK OF INDIA</td>\n",
       "      <td>SBIN0002025</td>\n",
       "      <td>STATE BANK OF INDIA</td>\n",
       "      <td>SBI AND ITS ASSOCIATE BANKS</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Sumosoft</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8802205999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3078CD0009162</td>\n",
       "      <td>WB3078CD0009162</td>\n",
       "      <td>CD00458</td>\n",
       "      <td>CD</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2018-04-23T10:08:59.507Z</td>\n",
       "      <td>2018-04-23T15:37:31.502Z</td>\n",
       "      <td>2018-04-23T19:15:05.057Z</td>\n",
       "      <td>DEALER</td>\n",
       "      <td>TV</td>\n",
       "      <td>CD0007287</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>66.66</td>\n",
       "      <td>WB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SONY</td>\n",
       "      <td>SONY TV KLV-32R302E IN5</td>\n",
       "      <td>SONY TV</td>\n",
       "      <td>DT1223</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CD_M_0153</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16332</td>\n",
       "      <td>Murshidabad</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MALE</td>\n",
       "      <td>GRADUATE</td>\n",
       "      <td>23</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RS</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>JHAUBANA NAWDA</td>\n",
       "      <td>JHAUBANA NAWDA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>DISBURSAL_TAT</td>\n",
       "      <td>144.43</td>\n",
       "      <td>6.02</td>\n",
       "      <td>4.02</td>\n",
       "      <td>ADM</td>\n",
       "      <td>SBIN0007104</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No eligibility criteria matched</td>\n",
       "      <td>1</td>\n",
       "      <td>SUR SANGAM</td>\n",
       "      <td>DD</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>Murshidabad</td>\n",
       "      <td>STATE BANK OF INDIA</td>\n",
       "      <td>741002616</td>\n",
       "      <td>TRIMOHINI</td>\n",
       "      <td>DIST MURSHIDABAD,WB 742133</td>\n",
       "      <td>0</td>\n",
       "      <td>TRIMOHINI</td>\n",
       "      <td>MURSHIDABAD</td>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>WB3078CD0009162</td>\n",
       "      <td>TELEVISION</td>\n",
       "      <td>SONY</td>\n",
       "      <td>23/04/2018</td>\n",
       "      <td>2018-04-23T10:09:03.353Z</td>\n",
       "      <td>2018-04-23T10:09:49.616Z</td>\n",
       "      <td>26/04/2018</td>\n",
       "      <td>BEHARIA B.O</td>\n",
       "      <td>STATE BANK OF INDIA</td>\n",
       "      <td>SBIN0007104</td>\n",
       "      <td>STATE BANK OF INDIA</td>\n",
       "      <td>SBI AND ITS ASSOCIATE BANKS</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Sumosoft</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7407578710</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3078CD0009161</td>\n",
       "      <td>WB3078CD0009161</td>\n",
       "      <td>CD00442</td>\n",
       "      <td>CD</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEALER</td>\n",
       "      <td>FRIDGE</td>\n",
       "      <td>CD0011224</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>66.65</td>\n",
       "      <td>WB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SAMSUNG</td>\n",
       "      <td>SAMSUNG FRIDGE RR20N2Y2ZD3/NL</td>\n",
       "      <td>SAMSUNG FRIDGE</td>\n",
       "      <td>DT1222</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CD_M_0034</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11064</td>\n",
       "      <td>Bardhaman</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MALE</td>\n",
       "      <td>12TH</td>\n",
       "      <td>43</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RS</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>MATHRUN MONGALKOTE</td>\n",
       "      <td>MATHRUN BARDDHAMAN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>DISBURSAL_TAT</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>ACH</td>\n",
       "      <td>UBIN0540510</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No eligibility criteria matched</td>\n",
       "      <td>1</td>\n",
       "      <td>ROY ELECTRONICS  AND SERVICE CENTRE</td>\n",
       "      <td>DD</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>Bardhaman</td>\n",
       "      <td>UNION BANK OF INDIA</td>\n",
       "      <td>713026604</td>\n",
       "      <td>KAICHAR</td>\n",
       "      <td>ATPOST KAICHAR  DIST. BURDWAN  WEST BENGAL  PI...</td>\n",
       "      <td>262236</td>\n",
       "      <td>KAICHAR</td>\n",
       "      <td>BARDHAMAN</td>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>WB3078CD0009161</td>\n",
       "      <td>REFRIGERATOR</td>\n",
       "      <td>SAMSUNG</td>\n",
       "      <td>23/04/2018</td>\n",
       "      <td>2018-04-23T09:55:59.184Z</td>\n",
       "      <td>2018-04-23T09:56:30.820Z</td>\n",
       "      <td>23/04/2018</td>\n",
       "      <td>AURIA B.O</td>\n",
       "      <td>UNION BANK OF INDIA</td>\n",
       "      <td>UBIN0540510</td>\n",
       "      <td>UNION BANK OF INDIA</td>\n",
       "      <td>NATIONALISED BANKS</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Sumosoft</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8537018497</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3009CD0000002</td>\n",
       "      <td>KA3009CD0000002</td>\n",
       "      <td>CD01183</td>\n",
       "      <td>CD</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2018-03-17T10:49:16.175Z</td>\n",
       "      <td>2018-03-17T14:48:12.875Z</td>\n",
       "      <td>2018-03-17T19:14:46.949Z</td>\n",
       "      <td>DEALER</td>\n",
       "      <td>TV</td>\n",
       "      <td>CD0002970</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>66.67</td>\n",
       "      <td>KA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PANASONIC</td>\n",
       "      <td>PANASONIC TV TH-32E200DX</td>\n",
       "      <td>PANASONIC TV</td>\n",
       "      <td>DT1128</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CD_M_0038</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10800</td>\n",
       "      <td>Bangalore</td>\n",
       "      <td>11575.00</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>UNDER GRADUATE</td>\n",
       "      <td>26</td>\n",
       "      <td>726.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RS</td>\n",
       "      <td>RENT</td>\n",
       "      <td>#242 2ND MAIN ROAD VIJAYANANDANAGAR</td>\n",
       "      <td>BANGALORE NORTHBANGALORE  NANDINILAYOUT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>DISBURSAL_TAT</td>\n",
       "      <td>3.21</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.13</td>\n",
       "      <td>ACH</td>\n",
       "      <td>ICIC0002317</td>\n",
       "      <td>7.0</td>\n",
       "      <td>CD/TW -720+ norms met</td>\n",
       "      <td>1</td>\n",
       "      <td>E SHOPPE</td>\n",
       "      <td>DS</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>Bangalore</td>\n",
       "      <td>ICICI BANK LIMITED</td>\n",
       "      <td>560229072</td>\n",
       "      <td>CHANDRA LAYOUT</td>\n",
       "      <td>ICICI BANK LTD., NO.1210,1ST STAGE,2ND PHASE,C...</td>\n",
       "      <td>0</td>\n",
       "      <td>BANGALORE</td>\n",
       "      <td>BANGALORE URBAN</td>\n",
       "      <td>KARNATAKA</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>726.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>35</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>KA3009CD0000002</td>\n",
       "      <td>TELEVISION</td>\n",
       "      <td>PANASONIC</td>\n",
       "      <td>01/03/2018</td>\n",
       "      <td>2018-03-01T16:06:26.854Z</td>\n",
       "      <td>2018-03-01T16:06:52.420Z</td>\n",
       "      <td>03/03/2018</td>\n",
       "      <td>NANDINILAYOUT S.O</td>\n",
       "      <td>ICICI BANK LIMITED</td>\n",
       "      <td>ICIC0002317</td>\n",
       "      <td>ICICI BANK LIMITED</td>\n",
       "      <td>PRIVATE SECTOR BANKS - INDIAN BANKS</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Sumosoft</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9632811202</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3009CD0000011</td>\n",
       "      <td>KA3009CD0000011</td>\n",
       "      <td>CD01216</td>\n",
       "      <td>CD</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEALER</td>\n",
       "      <td>TV</td>\n",
       "      <td>CD0006206</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>66.66</td>\n",
       "      <td>KA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SAMSUNG</td>\n",
       "      <td>SAMSUNG TV UA32M4200DRLXL</td>\n",
       "      <td>SAMSUNG TV</td>\n",
       "      <td>DT1128</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CD_M_0034</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17332</td>\n",
       "      <td>Bangalore</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MALE</td>\n",
       "      <td>SSC</td>\n",
       "      <td>23</td>\n",
       "      <td>710.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RS</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>U84, 2 nd main road</td>\n",
       "      <td>swathantrapalya srirampura</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>DISBURSAL_TAT</td>\n",
       "      <td>3.00</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.00</td>\n",
       "      <td>ACH</td>\n",
       "      <td>SBIN0007080</td>\n",
       "      <td>4.0</td>\n",
       "      <td>CD/TW- 650-719 norms met</td>\n",
       "      <td>1</td>\n",
       "      <td>VENUS WORLD</td>\n",
       "      <td>DS</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>Bangalore</td>\n",
       "      <td>STATE BANK OF INDIA</td>\n",
       "      <td>560002031</td>\n",
       "      <td>MALLESWARAM</td>\n",
       "      <td>143,MARGOSA ROAD,9TH CROSS,BANGALORE,PIN 560003</td>\n",
       "      <td>0</td>\n",
       "      <td>BANGALORE</td>\n",
       "      <td>BANGALORE URBAN</td>\n",
       "      <td>KARNATAKA</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>710.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>19</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>KA3009CD0000011</td>\n",
       "      <td>TELEVISION</td>\n",
       "      <td>LG</td>\n",
       "      <td>04/03/2018</td>\n",
       "      <td>2018-03-04T09:28:56.998Z</td>\n",
       "      <td>2018-03-04T09:29:17.762Z</td>\n",
       "      <td>08/03/2018</td>\n",
       "      <td>GAYATHRINAGAR S.O</td>\n",
       "      <td>STATE BANK OF INDIA</td>\n",
       "      <td>SBIN0007080</td>\n",
       "      <td>STATE BANK OF INDIA</td>\n",
       "      <td>SBI AND ITS ASSOCIATE BANKS</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Sumosoft</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9535129402</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          PROPNO           AGMTNO DEALER_CODE PORTFOLIO  PREPAIDPOSTPAIDFLAG  \\\n",
       "0  3078CD0009160  WB3078CD0009160     CD00910        CD                    0   \n",
       "1  3078CD0009162  WB3078CD0009162     CD00458        CD                    0   \n",
       "2  3078CD0009161  WB3078CD0009161     CD00442        CD                    0   \n",
       "3  3009CD0000002  KA3009CD0000002     CD01183        CD                    0   \n",
       "4  3009CD0000011  KA3009CD0000011     CD01216        CD                    0   \n",
       "\n",
       "   AVGMOBILEBILLAMOUNTLAST3MON  AVGMOBRECHARGEAMOUNTLAST3MON  \\\n",
       "0                          0.0                           0.0   \n",
       "1                          0.0                           0.0   \n",
       "2                          0.0                           0.0   \n",
       "3                          0.0                           0.0   \n",
       "4                          0.0                           0.0   \n",
       "\n",
       "                INSTALLTIME              LASTPINGTIME  \\\n",
       "0  2018-04-23T12:10:07.466Z  2018-04-23T12:14:29.339Z   \n",
       "1  2018-04-23T10:08:59.507Z  2018-04-23T15:37:31.502Z   \n",
       "2                       NaN                       NaN   \n",
       "3  2018-03-17T10:49:16.175Z  2018-03-17T14:48:12.875Z   \n",
       "4                       NaN                       NaN   \n",
       "\n",
       "                REQUESTTIME DEALER_TYPE PRODUCT_CODE MODEL_CODE SCHEME_TYPE  \\\n",
       "0  2018-04-23T20:12:08.774Z      DEALER           TV  CD0003748       CDOEM   \n",
       "1  2018-04-23T19:15:05.057Z      DEALER           TV  CD0007287       CDOEM   \n",
       "2                       NaN      DEALER       FRIDGE  CD0011224       CDOEM   \n",
       "3  2018-03-17T19:14:46.949Z      DEALER           TV  CD0002970       CDOEM   \n",
       "4                       NaN      DEALER           TV  CD0006206       CDOEM   \n",
       "\n",
       "     LTV STATE IMEI_NUMBER  MAKE_CODE                     MODEL_DESC  \\\n",
       "0  68.18    WB         NaN    SAMSUNG      SAMSUNG TV UA43K5002AKXXL   \n",
       "1  66.66    WB         NaN       SONY        SONY TV KLV-32R302E IN5   \n",
       "2  66.65    WB         NaN    SAMSUNG  SAMSUNG FRIDGE RR20N2Y2ZD3/NL   \n",
       "3  66.67    KA         NaN  PANASONIC       PANASONIC TV TH-32E200DX   \n",
       "4  66.66    KA         NaN    SAMSUNG      SAMSUNG TV UA32M4200DRLXL   \n",
       "\n",
       "  MODEL_DESC_V2_MAKE DEALER_CITY  REGISTRATION_TYPE SCHEME_CODE CIBIL_HIT  \\\n",
       "0        SAMSUNG TV       DT1234                NaN   CD_M_0085         Y   \n",
       "1           SONY TV       DT1223                NaN   CD_M_0153         Y   \n",
       "2    SAMSUNG FRIDGE       DT1222                NaN   CD_M_0034         Y   \n",
       "3      PANASONIC TV       DT1128                NaN   CD_M_0038         Y   \n",
       "4        SAMSUNG TV       DT1128                NaN   CD_M_0034         Y   \n",
       "\n",
       "  SURROGATE  ACTUAL_LOAN_AMOUNT         CITY  LATESTBALANCE  GENDER  \\\n",
       "0       NaN               32611   Jalpaiguri      190758.16    MALE   \n",
       "1       NaN               16332  Murshidabad            NaN    MALE   \n",
       "2       NaN               11064    Bardhaman            NaN    MALE   \n",
       "3       NaN               10800    Bangalore       11575.00  FEMALE   \n",
       "4       NaN               17332    Bangalore            NaN    MALE   \n",
       "\n",
       "    QUALIFICATION  AGE  CIBIL_SCORE  EXISTING_CUSTOMER SPL_INSURANCE_TYPE  \\\n",
       "0             SSC   32         -1.0                NaN                NaN   \n",
       "1        GRADUATE   23         -1.0                NaN                NaN   \n",
       "2            12TH   43         -1.0                NaN                NaN   \n",
       "3  UNDER GRADUATE   26        726.0                NaN                NaN   \n",
       "4             SSC   23        710.0                NaN                NaN   \n",
       "\n",
       "  ADDRESS_TYPE RESIDENT_TYPE                             ADDRESS1  \\\n",
       "0           RS         OWNED                DUMCHIPARA TEA GARDEN   \n",
       "1           RS         OWNED                       JHAUBANA NAWDA   \n",
       "2           RS         OWNED                   MATHRUN MONGALKOTE   \n",
       "3           RS          RENT  #242 2ND MAIN ROAD VIJAYANANDANAGAR   \n",
       "4           RS         OWNED                  U84, 2 nd main road   \n",
       "\n",
       "                                  ADDRESS2 ADDRESS3 VOTERID PANNO  \\\n",
       "0                          JATHA LINE.   .      NaN       N     Y   \n",
       "1                           JHAUBANA NAWDA      NaN       N     N   \n",
       "2                       MATHRUN BARDDHAMAN      NaN       N     N   \n",
       "3  BANGALORE NORTHBANGALORE  NANDINILAYOUT      NaN       N     N   \n",
       "4               swathantrapalya srirampura      NaN       N     N   \n",
       "\n",
       "  DRIVING_LICENSE PASSPORT AADHAR RATION  ACTIVITY_CODE  TAT_HOUR  TAT_DAYS  \\\n",
       "0               N        N      Y      N  DISBURSAL_TAT     22.52      0.94   \n",
       "1               N        N      Y      N  DISBURSAL_TAT    144.43      6.02   \n",
       "2               N        N      Y      N  DISBURSAL_TAT      0.00      0.00   \n",
       "3               N        N      Y      N  DISBURSAL_TAT      3.21      0.13   \n",
       "4               N        N      Y      N  DISBURSAL_TAT      3.00      0.13   \n",
       "\n",
       "   TAT_DAYS_EXCLUDE_SUNDAY PAYMENT_TYPE    IFSC_CODE  ELIGIBILITYGRID  \\\n",
       "0                     0.94          ADM  SBIN0002025              NaN   \n",
       "1                     4.02          ADM  SBIN0007104              NaN   \n",
       "2                     0.00          ACH  UBIN0540510              NaN   \n",
       "3                     0.13          ACH  ICIC0002317              7.0   \n",
       "4                     0.00          ACH  SBIN0007080              4.0   \n",
       "\n",
       "            ELIGIBILITYGRID_REMARK  IS_MANDATE_REGISTERED  \\\n",
       "0  No eligibility criteria matched                      1   \n",
       "1  No eligibility criteria matched                      1   \n",
       "2  No eligibility criteria matched                      1   \n",
       "3            CD/TW -720+ norms met                      1   \n",
       "4         CD/TW- 650-719 norms met                      1   \n",
       "\n",
       "                           DELAER_NAME DELAER_CATEGORY  IS_MOBILE  \\\n",
       "0                         GOYAL RADIOS              DD          0   \n",
       "1                           SUR SANGAM              DD          0   \n",
       "2  ROY ELECTRONICS  AND SERVICE CENTRE              DD          0   \n",
       "3                             E SHOPPE              DS          0   \n",
       "4                          VENUS WORLD              DS          0   \n",
       "\n",
       "   Mobile_active_hours  Mobile_Bill  Mobile_Bill_bin  IS_CIBIL_HIT  \\\n",
       "0                  0.0          0.0                0             1   \n",
       "1                  5.0          0.0                0             1   \n",
       "2                  NaN          0.0                0             1   \n",
       "3                  4.0          0.0                0             1   \n",
       "4                  NaN          0.0                0             1   \n",
       "\n",
       "   SURROGATE_CAT  TICKET_SIZE  CROSS_SELL_INSURANCE DEALER_CITY_V2  \\\n",
       "0              0           10                     0     Jalpaiguri   \n",
       "1              0            7                     0    Murshidabad   \n",
       "2              0            4                     0      Bardhaman   \n",
       "3              0            3                     0      Bangalore   \n",
       "4              0            8                     0      Bangalore   \n",
       "\n",
       "             BANK_NAME  MICR_CODE     BANK_BRANCH  \\\n",
       "0  STATE BANK OF INDIA  735002505         BIRPARA   \n",
       "1  STATE BANK OF INDIA  741002616       TRIMOHINI   \n",
       "2  UNION BANK OF INDIA  713026604         KAICHAR   \n",
       "3   ICICI BANK LIMITED  560229072  CHANDRA LAYOUT   \n",
       "4  STATE BANK OF INDIA  560002031     MALLESWARAM   \n",
       "\n",
       "                                        BANK_ADDRESS BANK_CONTACT  BANK_CITY  \\\n",
       "0                           PO BIRPARA,JALPAIGURI,WB            0    BIRPARA   \n",
       "1                         DIST MURSHIDABAD,WB 742133            0  TRIMOHINI   \n",
       "2  ATPOST KAICHAR  DIST. BURDWAN  WEST BENGAL  PI...       262236    KAICHAR   \n",
       "3  ICICI BANK LTD., NO.1210,1ST STAGE,2ND PHASE,C...            0  BANGALORE   \n",
       "4    143,MARGOSA ROAD,9TH CROSS,BANGALORE,PIN 560003            0  BANGALORE   \n",
       "\n",
       "     BANK_DISTRICT   BANK_STATE  DEALER_CUST_CITY  CUST_BANK_CITY_DIFF  \\\n",
       "0       JALPAIGURI  WEST BENGAL                 1                    0   \n",
       "1      MURSHIDABAD  WEST BENGAL                 1                    0   \n",
       "2        BARDHAMAN  WEST BENGAL                 1                    0   \n",
       "3  BANGALORE URBAN    KARNATAKA                 1                    1   \n",
       "4  BANGALORE URBAN    KARNATAKA                 1                    1   \n",
       "\n",
       "   AGE_BIN  CIBIL_SCORE_NEW  CIBIL_SCORE_NEW_BIN  ADDRESS1_LEN  ADDRESS2_LEN  \\\n",
       "0      5.0              0.0                  1.0            21            15   \n",
       "1      1.0              0.0                  1.0            14            14   \n",
       "2      8.0              0.0                  1.0            18            18   \n",
       "3      2.0            726.0                  7.0            35            39   \n",
       "4      1.0            710.0                  7.0            19            26   \n",
       "\n",
       "   ADDRESS3_LEN        AGMTNO_v1  applAssetCtg applAssetMake D_LOGIN_TIME  \\\n",
       "0             0  WB3078CD0009160    TELEVISION       SAMSUNG   23/04/2018   \n",
       "1             0  WB3078CD0009162    TELEVISION          SONY   23/04/2018   \n",
       "2             0  WB3078CD0009161  REFRIGERATOR       SAMSUNG   23/04/2018   \n",
       "3             0  KA3009CD0000002    TELEVISION     PANASONIC   01/03/2018   \n",
       "4             0  KA3009CD0000011    TELEVISION            LG   04/03/2018   \n",
       "\n",
       "             LOGIN_dateTime           croApprovedDate loanDisbDate  \\\n",
       "0  2018-04-23T12:10:20.784Z  2018-04-23T12:11:07.154Z   24/04/2018   \n",
       "1  2018-04-23T10:09:03.353Z  2018-04-23T10:09:49.616Z   26/04/2018   \n",
       "2  2018-04-23T09:55:59.184Z  2018-04-23T09:56:30.820Z   23/04/2018   \n",
       "3  2018-03-01T16:06:26.854Z  2018-03-01T16:06:52.420Z   03/03/2018   \n",
       "4  2018-03-04T09:28:56.998Z  2018-03-04T09:29:17.762Z   08/03/2018   \n",
       "\n",
       "       residenceCity        Cust_bankName bankIFSCCode            Bank_Name  \\\n",
       "0       GARGANDA B.O  STATE BANK OF INDIA  SBIN0002025  STATE BANK OF INDIA   \n",
       "1        BEHARIA B.O  STATE BANK OF INDIA  SBIN0007104  STATE BANK OF INDIA   \n",
       "2          AURIA B.O  UNION BANK OF INDIA  UBIN0540510  UNION BANK OF INDIA   \n",
       "3  NANDINILAYOUT S.O   ICICI BANK LIMITED  ICIC0002317   ICICI BANK LIMITED   \n",
       "4  GAYATHRINAGAR S.O  STATE BANK OF INDIA  SBIN0007080  STATE BANK OF INDIA   \n",
       "\n",
       "                             Bank_Type  Actual_TAT_Days  Disp_Month_bin  \\\n",
       "0          SBI AND ITS ASSOCIATE BANKS                1               1   \n",
       "1          SBI AND ITS ASSOCIATE BANKS                3               1   \n",
       "2                   NATIONALISED BANKS                0               1   \n",
       "3  PRIVATE SECTOR BANKS - INDIAN BANKS                2               1   \n",
       "4          SBI AND ITS ASSOCIATE BANKS                4               1   \n",
       "\n",
       "   IS_WEEKEND Processing Unit  CONTACT1  CONTACT2     MOBILE1  MOBILE2  \\\n",
       "0           0        Sumosoft       NaN       NaN  8802205999      NaN   \n",
       "1           0        Sumosoft       NaN       NaN  7407578710      NaN   \n",
       "2           0        Sumosoft       NaN       NaN  8537018497      NaN   \n",
       "3           0        Sumosoft       NaN       NaN  9632811202      NaN   \n",
       "4           1        Sumosoft       NaN       NaN  9535129402      NaN   \n",
       "\n",
       "   MOBILE1_len  IFSC_LEN  \n",
       "0           10        11  \n",
       "1           10        11  \n",
       "2           10        11  \n",
       "3           10        11  \n",
       "4           10        11  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()              #9159873699/915987369\t,\t9970734753,915814280"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "data1= data[['AGMTNO','PROPNO','PREPAIDPOSTPAIDFLAG','LTV','ACTUAL_LOAN_AMOUNT','LATESTBALANCE','ELIGIBILITYGRID','IS_MOBILE',\n",
    "'Mobile_Bill_bin','IS_CIBIL_HIT','PRODUCT_CODE','CITY','SCHEME_TYPE','RESIDENT_TYPE','SURROGATE_CAT','AGE','GENDER','QUALIFICATION','TICKET_SIZE','CROSS_SELL_INSURANCE',\n",
    "'DEALER_CUST_CITY','AGE_BIN','CIBIL_SCORE_NEW_BIN','Actual_TAT_Days','Disp_Month_bin',\n",
    "'IS_WEEKEND','VOTERID','PANNO','DRIVING_LICENSE','PASSPORT','AADHAR','RATION','STATE','IS_MANDATE_REGISTERED']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "data1 = data1.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "existing = pd.read_csv(\"D:\\CD\\EXCISTING_CUST.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "existing.columns = ['AGMTNO', 'IS_EXIST']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "data1 = pd.merge(data1, existing, on='AGMTNO', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "data1 = data1[['AGMTNO','PROPNO','PREPAIDPOSTPAIDFLAG','LTV','ACTUAL_LOAN_AMOUNT','LATESTBALANCE','ELIGIBILITYGRID','IS_MOBILE',\n",
    "'Mobile_Bill_bin','IS_CIBIL_HIT','PRODUCT_CODE','CITY','SCHEME_TYPE','RESIDENT_TYPE','SURROGATE_CAT','AGE','GENDER','QUALIFICATION','TICKET_SIZE','CROSS_SELL_INSURANCE',\n",
    "'DEALER_CUST_CITY','AGE_BIN','CIBIL_SCORE_NEW_BIN','Actual_TAT_Days','Disp_Month_bin',\n",
    "'IS_WEEKEND','VOTERID','PANNO','DRIVING_LICENSE','PASSPORT','AADHAR','RATION','STATE','IS_EXIST','IS_MANDATE_REGISTERED']]"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "data2 = pd.merge(data1, filt, on='PROPNO', how='inner')"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "data2.head()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "data2.groupby('PROPNO')['Bank_Type'].max()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "data2.loc[(~data2['Bank_Type'].isnull()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "data1.Bank_Type.isnull().sum()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "data1 = data1.drop_duplicates()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "data1.to_csv(\"D:/CD/CD_Variable_all_25_06.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_group = pd.read_csv(\"D:/CD/City_group.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_group = c_group[['City', 'group']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "c_group.columns = ['CITY', 'City_Group']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CITY', 'City_Group'], dtype='object')"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_group.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 325 entries, 0 to 324\n",
      "Data columns (total 2 columns):\n",
      "CITY          324 non-null object\n",
      "City_Group    324 non-null float64\n",
      "dtypes: float64(1), object(1)\n",
      "memory usage: 5.2+ KB\n"
     ]
    }
   ],
   "source": [
    "c_group.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "prod_group = pd.read_csv(\"D:/CD/Product_Group.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "prod_group = prod_group[['PRODUCT_CODE','PROD_GROUP']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRODUCT_CODE</th>\n",
       "      <th>PROD_GROUP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MOBILE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TV</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FRIDGE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WAM</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AC</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PRODUCT_CODE  PROD_GROUP\n",
       "0       MOBILE           1\n",
       "1           TV           1\n",
       "2       FRIDGE           1\n",
       "3          WAM           1\n",
       "4           AC           1"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prod_group.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_new = pd.merge(data1, c_group, on=['CITY'], how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_new2 = pd.merge(data_new, prod_group, on=['PRODUCT_CODE'], how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "def change_tgt(row):\n",
    "    if row['IS_MANDATE_REGISTERED'] == 0:\n",
    "        return 2\n",
    "    else:\n",
    "        return 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_new2['IS_MANDATE_REGISTERED_v1'] = data_new2.apply(change_tgt, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_new2['IS_MANDATE_REGISTERED'] = data_new2.apply(lambda x: 1 if x['IS_MANDATE_REGISTERED_v1'] == 2 else 0, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": [
    "del data_new2['IS_MANDATE_REGISTERED_v1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_new2['PROD_GROUP'] = data_new2['PROD_GROUP'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AGMTNO</th>\n",
       "      <th>PROPNO</th>\n",
       "      <th>PREPAIDPOSTPAIDFLAG</th>\n",
       "      <th>LTV</th>\n",
       "      <th>ACTUAL_LOAN_AMOUNT</th>\n",
       "      <th>LATESTBALANCE</th>\n",
       "      <th>ELIGIBILITYGRID</th>\n",
       "      <th>IS_MOBILE</th>\n",
       "      <th>Mobile_Bill_bin</th>\n",
       "      <th>IS_CIBIL_HIT</th>\n",
       "      <th>PRODUCT_CODE</th>\n",
       "      <th>CITY</th>\n",
       "      <th>SCHEME_TYPE</th>\n",
       "      <th>RESIDENT_TYPE</th>\n",
       "      <th>SURROGATE_CAT</th>\n",
       "      <th>AGE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>QUALIFICATION</th>\n",
       "      <th>TICKET_SIZE</th>\n",
       "      <th>CROSS_SELL_INSURANCE</th>\n",
       "      <th>DEALER_CUST_CITY</th>\n",
       "      <th>AGE_BIN</th>\n",
       "      <th>CIBIL_SCORE_NEW_BIN</th>\n",
       "      <th>Actual_TAT_Days</th>\n",
       "      <th>Disp_Month_bin</th>\n",
       "      <th>IS_WEEKEND</th>\n",
       "      <th>VOTERID</th>\n",
       "      <th>PANNO</th>\n",
       "      <th>DRIVING_LICENSE</th>\n",
       "      <th>PASSPORT</th>\n",
       "      <th>AADHAR</th>\n",
       "      <th>RATION</th>\n",
       "      <th>STATE</th>\n",
       "      <th>IS_EXIST</th>\n",
       "      <th>IS_MANDATE_REGISTERED</th>\n",
       "      <th>City_Group</th>\n",
       "      <th>PROD_GROUP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WB3078CD0009160</td>\n",
       "      <td>3078CD0009160</td>\n",
       "      <td>0</td>\n",
       "      <td>68.18</td>\n",
       "      <td>32611</td>\n",
       "      <td>190758.16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>TV</td>\n",
       "      <td>Jalpaiguri</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>MALE</td>\n",
       "      <td>SSC</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WB3078CD0006318</td>\n",
       "      <td>3078CD0006318</td>\n",
       "      <td>0</td>\n",
       "      <td>77.77</td>\n",
       "      <td>17032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>TV</td>\n",
       "      <td>Jalpaiguri</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>35</td>\n",
       "      <td>MALE</td>\n",
       "      <td>GRADUATE</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WB3078CD0007026</td>\n",
       "      <td>3078CD0007026</td>\n",
       "      <td>0</td>\n",
       "      <td>74.66</td>\n",
       "      <td>18664</td>\n",
       "      <td>29890.58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>TV</td>\n",
       "      <td>Jalpaiguri</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>34</td>\n",
       "      <td>MALE</td>\n",
       "      <td>UNDER GRADUATE</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WB3078CD0040195</td>\n",
       "      <td>3078CD0040195</td>\n",
       "      <td>0</td>\n",
       "      <td>77.78</td>\n",
       "      <td>37332</td>\n",
       "      <td>188925.04</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>TV</td>\n",
       "      <td>Jalpaiguri</td>\n",
       "      <td>CDSPL</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>MALE</td>\n",
       "      <td>GRADUATE</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>WB3078CD0006403</td>\n",
       "      <td>3078CD0006403</td>\n",
       "      <td>0</td>\n",
       "      <td>77.77</td>\n",
       "      <td>31108</td>\n",
       "      <td>3270.44</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>TV</td>\n",
       "      <td>Jalpaiguri</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>MALE</td>\n",
       "      <td>GRADUATE</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            AGMTNO         PROPNO  PREPAIDPOSTPAIDFLAG    LTV  \\\n",
       "0  WB3078CD0009160  3078CD0009160                    0  68.18   \n",
       "1  WB3078CD0006318  3078CD0006318                    0  77.77   \n",
       "2  WB3078CD0007026  3078CD0007026                    0  74.66   \n",
       "3  WB3078CD0040195  3078CD0040195                    0  77.78   \n",
       "4  WB3078CD0006403  3078CD0006403                    0  77.77   \n",
       "\n",
       "   ACTUAL_LOAN_AMOUNT  LATESTBALANCE  ELIGIBILITYGRID  IS_MOBILE  \\\n",
       "0               32611      190758.16              NaN          0   \n",
       "1               17032            NaN              5.0          0   \n",
       "2               18664       29890.58              NaN          0   \n",
       "3               37332      188925.04             16.0          0   \n",
       "4               31108        3270.44             22.0          0   \n",
       "\n",
       "   Mobile_Bill_bin  IS_CIBIL_HIT PRODUCT_CODE        CITY SCHEME_TYPE  \\\n",
       "0                0             1           TV  Jalpaiguri       CDOEM   \n",
       "1                0             1           TV  Jalpaiguri       CDOEM   \n",
       "2                0             1           TV  Jalpaiguri       CDOEM   \n",
       "3                0             1           TV  Jalpaiguri       CDSPL   \n",
       "4                0             1           TV  Jalpaiguri       CDOEM   \n",
       "\n",
       "  RESIDENT_TYPE  SURROGATE_CAT  AGE GENDER   QUALIFICATION  TICKET_SIZE  \\\n",
       "0         OWNED              0   32   MALE             SSC           10   \n",
       "1         OWNED              0   35   MALE        GRADUATE            8   \n",
       "2         OWNED              0   34   MALE  UNDER GRADUATE            8   \n",
       "3         OWNED              0   53   MALE        GRADUATE           10   \n",
       "4         OWNED              0   50   MALE        GRADUATE           10   \n",
       "\n",
       "   CROSS_SELL_INSURANCE  DEALER_CUST_CITY  AGE_BIN  CIBIL_SCORE_NEW_BIN  \\\n",
       "0                     0                 1      5.0                  1.0   \n",
       "1                     0                 1      6.0                  6.0   \n",
       "2                     0                 1      6.0                  7.0   \n",
       "3                     1                 0      9.0                  1.0   \n",
       "4                     0                 1      9.0                  8.0   \n",
       "\n",
       "   Actual_TAT_Days  Disp_Month_bin  IS_WEEKEND VOTERID PANNO DRIVING_LICENSE  \\\n",
       "0                1               1           0       N     Y               N   \n",
       "1                5               1           0       N     N               N   \n",
       "2                2               1           0       N     N               N   \n",
       "3                8               1           0       N     N               N   \n",
       "4                1               1           1       N     N               N   \n",
       "\n",
       "  PASSPORT AADHAR RATION STATE  IS_EXIST  IS_MANDATE_REGISTERED  City_Group  \\\n",
       "0        N      Y      N    WB       0.0                      0         1.0   \n",
       "1        N      Y      N    WB       0.0                      0         1.0   \n",
       "2        N      Y      N    WB       0.0                      0         1.0   \n",
       "3        N      Y      N    WB       0.0                      0         1.0   \n",
       "4        N      Y      N    WB       0.0                      0         1.0   \n",
       "\n",
       "  PROD_GROUP  \n",
       "0          1  \n",
       "1          1  \n",
       "2          1  \n",
       "3          1  \n",
       "4          1  "
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_new2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['AGMTNO', 'PROPNO', 'PREPAIDPOSTPAIDFLAG', 'LTV', 'ACTUAL_LOAN_AMOUNT',\n",
       "       'LATESTBALANCE', 'ELIGIBILITYGRID', 'IS_MOBILE', 'Mobile_Bill_bin',\n",
       "       'IS_CIBIL_HIT', 'PRODUCT_CODE', 'CITY', 'SCHEME_TYPE', 'RESIDENT_TYPE',\n",
       "       'SURROGATE_CAT', 'AGE', 'GENDER', 'QUALIFICATION', 'TICKET_SIZE',\n",
       "       'CROSS_SELL_INSURANCE', 'DEALER_CUST_CITY', 'AGE_BIN',\n",
       "       'CIBIL_SCORE_NEW_BIN', 'Actual_TAT_Days', 'Disp_Month_bin',\n",
       "       'IS_WEEKEND', 'VOTERID', 'PANNO', 'DRIVING_LICENSE', 'PASSPORT',\n",
       "       'AADHAR', 'RATION', 'STATE', 'IS_EXIST', 'IS_MANDATE_REGISTERED',\n",
       "       'City_Group', 'PROD_GROUP'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_new2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_new2[['City_Group','PROD_GROUP','PREPAIDPOSTPAIDFLAG', 'IS_MOBILE', 'IS_CIBIL_HIT',\n",
    "       'SURROGATE_CAT', 'CROSS_SELL_INSURANCE',\n",
    "       'DEALER_CUST_CITY',\n",
    "       'Disp_Month_bin', 'IS_WEEKEND', 'IS_EXIST']] = data_new2[['City_Group','PROD_GROUP','PREPAIDPOSTPAIDFLAG', 'IS_MOBILE', 'IS_CIBIL_HIT',\n",
    "       'SURROGATE_CAT', 'CROSS_SELL_INSURANCE',\n",
    "       'DEALER_CUST_CITY',\n",
    "       'Disp_Month_bin', 'IS_WEEKEND', 'IS_EXIST']].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_new2.to_csv(\"D:/CD/CD_Variable_all_28_06_new.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PROPNO</th>\n",
       "      <th>PREPAIDPOSTPAIDFLAG</th>\n",
       "      <th>LTV</th>\n",
       "      <th>ACTUAL_LOAN_AMOUNT</th>\n",
       "      <th>LATESTBALANCE</th>\n",
       "      <th>ELIGIBILITYGRID</th>\n",
       "      <th>IS_MOBILE</th>\n",
       "      <th>Mobile_Bill_bin</th>\n",
       "      <th>IS_CIBIL_HIT</th>\n",
       "      <th>SCHEME_TYPE</th>\n",
       "      <th>RESIDENT_TYPE</th>\n",
       "      <th>SURROGATE_CAT</th>\n",
       "      <th>AGE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>QUALIFICATION</th>\n",
       "      <th>TICKET_SIZE</th>\n",
       "      <th>CROSS_SELL_INSURANCE</th>\n",
       "      <th>DEALER_CUST_CITY</th>\n",
       "      <th>AGE_BIN</th>\n",
       "      <th>CIBIL_SCORE_NEW_BIN</th>\n",
       "      <th>Actual_TAT_Days</th>\n",
       "      <th>Disp_Month_bin</th>\n",
       "      <th>IS_WEEKEND</th>\n",
       "      <th>VOTERID</th>\n",
       "      <th>PANNO</th>\n",
       "      <th>DRIVING_LICENSE</th>\n",
       "      <th>PASSPORT</th>\n",
       "      <th>AADHAR</th>\n",
       "      <th>RATION</th>\n",
       "      <th>STATE</th>\n",
       "      <th>IS_EXIST</th>\n",
       "      <th>IS_MANDATE_REGISTERED</th>\n",
       "      <th>City_Group</th>\n",
       "      <th>PROD_GROUP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3078CD0009160</td>\n",
       "      <td>0</td>\n",
       "      <td>68.18</td>\n",
       "      <td>32611</td>\n",
       "      <td>190758.16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>MALE</td>\n",
       "      <td>SSC</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3078CD0006318</td>\n",
       "      <td>0</td>\n",
       "      <td>77.77</td>\n",
       "      <td>17032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>35</td>\n",
       "      <td>MALE</td>\n",
       "      <td>GRADUATE</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3078CD0007026</td>\n",
       "      <td>0</td>\n",
       "      <td>74.66</td>\n",
       "      <td>18664</td>\n",
       "      <td>29890.58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>34</td>\n",
       "      <td>MALE</td>\n",
       "      <td>UNDER GRADUATE</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3078CD0040195</td>\n",
       "      <td>0</td>\n",
       "      <td>77.78</td>\n",
       "      <td>37332</td>\n",
       "      <td>188925.04</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>CDSPL</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>MALE</td>\n",
       "      <td>GRADUATE</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3078CD0006403</td>\n",
       "      <td>0</td>\n",
       "      <td>77.77</td>\n",
       "      <td>31108</td>\n",
       "      <td>3270.44</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>MALE</td>\n",
       "      <td>GRADUATE</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          PROPNO PREPAIDPOSTPAIDFLAG    LTV  ACTUAL_LOAN_AMOUNT  \\\n",
       "0  3078CD0009160                   0  68.18               32611   \n",
       "1  3078CD0006318                   0  77.77               17032   \n",
       "2  3078CD0007026                   0  74.66               18664   \n",
       "3  3078CD0040195                   0  77.78               37332   \n",
       "4  3078CD0006403                   0  77.77               31108   \n",
       "\n",
       "   LATESTBALANCE  ELIGIBILITYGRID IS_MOBILE  Mobile_Bill_bin IS_CIBIL_HIT  \\\n",
       "0      190758.16              NaN         0                0            1   \n",
       "1            NaN              5.0         0                0            1   \n",
       "2       29890.58              NaN         0                0            1   \n",
       "3      188925.04             16.0         0                0            1   \n",
       "4        3270.44             22.0         0                0            1   \n",
       "\n",
       "  SCHEME_TYPE RESIDENT_TYPE SURROGATE_CAT  AGE GENDER   QUALIFICATION  \\\n",
       "0       CDOEM         OWNED             0   32   MALE             SSC   \n",
       "1       CDOEM         OWNED             0   35   MALE        GRADUATE   \n",
       "2       CDOEM         OWNED             0   34   MALE  UNDER GRADUATE   \n",
       "3       CDSPL         OWNED             0   53   MALE        GRADUATE   \n",
       "4       CDOEM         OWNED             0   50   MALE        GRADUATE   \n",
       "\n",
       "   TICKET_SIZE CROSS_SELL_INSURANCE DEALER_CUST_CITY  AGE_BIN  \\\n",
       "0           10                    0                1      5.0   \n",
       "1            8                    0                1      6.0   \n",
       "2            8                    0                1      6.0   \n",
       "3           10                    1                0      9.0   \n",
       "4           10                    0                1      9.0   \n",
       "\n",
       "   CIBIL_SCORE_NEW_BIN  Actual_TAT_Days Disp_Month_bin IS_WEEKEND VOTERID  \\\n",
       "0                  1.0                1              1          0       N   \n",
       "1                  6.0                5              1          0       N   \n",
       "2                  7.0                2              1          0       N   \n",
       "3                  1.0                8              1          0       N   \n",
       "4                  8.0                1              1          1       N   \n",
       "\n",
       "  PANNO DRIVING_LICENSE PASSPORT AADHAR RATION STATE IS_EXIST  \\\n",
       "0     Y               N        N      Y      N    WB      0.0   \n",
       "1     N               N        N      Y      N    WB      0.0   \n",
       "2     N               N        N      Y      N    WB      0.0   \n",
       "3     N               N        N      Y      N    WB      0.0   \n",
       "4     N               N        N      Y      N    WB      0.0   \n",
       "\n",
       "   IS_MANDATE_REGISTERED City_Group PROD_GROUP  \n",
       "0                      0        1.0          1  \n",
       "1                      0        1.0          1  \n",
       "2                      0        1.0          1  \n",
       "3                      0        1.0          1  \n",
       "4                      0        1.0          1  "
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_new2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "491594"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data_new2.PROPNO.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-146"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "491594 - 491740# 491594"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_new2.drop(['AGMTNO', 'PRODUCT_CODE', 'CITY'],axis=1,inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_cat = data_new2.select_dtypes(include='object')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PROPNO</th>\n",
       "      <th>PREPAIDPOSTPAIDFLAG</th>\n",
       "      <th>IS_MOBILE</th>\n",
       "      <th>IS_CIBIL_HIT</th>\n",
       "      <th>SCHEME_TYPE</th>\n",
       "      <th>RESIDENT_TYPE</th>\n",
       "      <th>SURROGATE_CAT</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>QUALIFICATION</th>\n",
       "      <th>CROSS_SELL_INSURANCE</th>\n",
       "      <th>DEALER_CUST_CITY</th>\n",
       "      <th>Disp_Month_bin</th>\n",
       "      <th>IS_WEEKEND</th>\n",
       "      <th>VOTERID</th>\n",
       "      <th>PANNO</th>\n",
       "      <th>DRIVING_LICENSE</th>\n",
       "      <th>PASSPORT</th>\n",
       "      <th>AADHAR</th>\n",
       "      <th>RATION</th>\n",
       "      <th>STATE</th>\n",
       "      <th>IS_EXIST</th>\n",
       "      <th>City_Group</th>\n",
       "      <th>PROD_GROUP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3078CD0009160</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>MALE</td>\n",
       "      <td>SSC</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3078CD0006318</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>MALE</td>\n",
       "      <td>GRADUATE</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3078CD0007026</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>MALE</td>\n",
       "      <td>UNDER GRADUATE</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3078CD0040195</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>CDSPL</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>MALE</td>\n",
       "      <td>GRADUATE</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3078CD0006403</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>CDOEM</td>\n",
       "      <td>OWNED</td>\n",
       "      <td>0</td>\n",
       "      <td>MALE</td>\n",
       "      <td>GRADUATE</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>WB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          PROPNO PREPAIDPOSTPAIDFLAG IS_MOBILE IS_CIBIL_HIT SCHEME_TYPE  \\\n",
       "0  3078CD0009160                   0         0            1       CDOEM   \n",
       "1  3078CD0006318                   0         0            1       CDOEM   \n",
       "2  3078CD0007026                   0         0            1       CDOEM   \n",
       "3  3078CD0040195                   0         0            1       CDSPL   \n",
       "4  3078CD0006403                   0         0            1       CDOEM   \n",
       "\n",
       "  RESIDENT_TYPE SURROGATE_CAT GENDER   QUALIFICATION CROSS_SELL_INSURANCE  \\\n",
       "0         OWNED             0   MALE             SSC                    0   \n",
       "1         OWNED             0   MALE        GRADUATE                    0   \n",
       "2         OWNED             0   MALE  UNDER GRADUATE                    0   \n",
       "3         OWNED             0   MALE        GRADUATE                    1   \n",
       "4         OWNED             0   MALE        GRADUATE                    0   \n",
       "\n",
       "  DEALER_CUST_CITY Disp_Month_bin IS_WEEKEND VOTERID PANNO DRIVING_LICENSE  \\\n",
       "0                1              1          0       N     Y               N   \n",
       "1                1              1          0       N     N               N   \n",
       "2                1              1          0       N     N               N   \n",
       "3                0              1          0       N     N               N   \n",
       "4                1              1          1       N     N               N   \n",
       "\n",
       "  PASSPORT AADHAR RATION STATE IS_EXIST City_Group PROD_GROUP  \n",
       "0        N      Y      N    WB      0.0        1.0          1  \n",
       "1        N      Y      N    WB      0.0        1.0          1  \n",
       "2        N      Y      N    WB      0.0        1.0          1  \n",
       "3        N      Y      N    WB      0.0        1.0          1  \n",
       "4        N      Y      N    WB      0.0        1.0          1  "
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_cat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "del data_cat['PROPNO']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_cat.City_Group.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_catv1 = pd.get_dummies(data_cat, prefix=None, prefix_sep='_', dummy_na=False, columns=None, sparse=True, drop_first=True, \n",
    "                            dtype=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PREPAIDPOSTPAIDFLAG_1</th>\n",
       "      <th>PREPAIDPOSTPAIDFLAG_2</th>\n",
       "      <th>IS_MOBILE_1</th>\n",
       "      <th>IS_CIBIL_HIT_1</th>\n",
       "      <th>SCHEME_TYPE_CDOEM</th>\n",
       "      <th>SCHEME_TYPE_CDSPL</th>\n",
       "      <th>RESIDENT_TYPE_RENT</th>\n",
       "      <th>SURROGATE_CAT_1</th>\n",
       "      <th>SURROGATE_CAT_10</th>\n",
       "      <th>SURROGATE_CAT_2</th>\n",
       "      <th>SURROGATE_CAT_3</th>\n",
       "      <th>SURROGATE_CAT_4</th>\n",
       "      <th>SURROGATE_CAT_5</th>\n",
       "      <th>SURROGATE_CAT_6</th>\n",
       "      <th>SURROGATE_CAT_7</th>\n",
       "      <th>SURROGATE_CAT_8</th>\n",
       "      <th>SURROGATE_CAT_9</th>\n",
       "      <th>GENDER_MALE</th>\n",
       "      <th>QUALIFICATION_GRADUATE</th>\n",
       "      <th>QUALIFICATION_OTHERS</th>\n",
       "      <th>QUALIFICATION_POST-GRADUATE</th>\n",
       "      <th>QUALIFICATION_PROFESSIONAL</th>\n",
       "      <th>QUALIFICATION_SSC</th>\n",
       "      <th>QUALIFICATION_UNDER GRADUATE</th>\n",
       "      <th>CROSS_SELL_INSURANCE_1</th>\n",
       "      <th>DEALER_CUST_CITY_1</th>\n",
       "      <th>Disp_Month_bin_2</th>\n",
       "      <th>IS_WEEKEND_1</th>\n",
       "      <th>VOTERID_Y</th>\n",
       "      <th>PANNO_Y</th>\n",
       "      <th>DRIVING_LICENSE_Y</th>\n",
       "      <th>AADHAR_Y</th>\n",
       "      <th>STATE_KA</th>\n",
       "      <th>STATE_MH</th>\n",
       "      <th>STATE_TN</th>\n",
       "      <th>STATE_WB</th>\n",
       "      <th>IS_EXIST_1.0</th>\n",
       "      <th>IS_EXIST_2.0</th>\n",
       "      <th>IS_EXIST_3.0</th>\n",
       "      <th>IS_EXIST_nan</th>\n",
       "      <th>City_Group_2.0</th>\n",
       "      <th>City_Group_3.0</th>\n",
       "      <th>PROD_GROUP_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\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>1</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\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>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>1</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>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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <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>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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <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>1</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>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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>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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PREPAIDPOSTPAIDFLAG_1  PREPAIDPOSTPAIDFLAG_2  IS_MOBILE_1  IS_CIBIL_HIT_1  \\\n",
       "0                      0                      0            0               1   \n",
       "1                      0                      0            0               1   \n",
       "2                      0                      0            0               1   \n",
       "3                      0                      0            0               1   \n",
       "4                      0                      0            0               1   \n",
       "\n",
       "   SCHEME_TYPE_CDOEM  SCHEME_TYPE_CDSPL  RESIDENT_TYPE_RENT  SURROGATE_CAT_1  \\\n",
       "0                  1                  0                   0                0   \n",
       "1                  1                  0                   0                0   \n",
       "2                  1                  0                   0                0   \n",
       "3                  0                  1                   0                0   \n",
       "4                  1                  0                   0                0   \n",
       "\n",
       "   SURROGATE_CAT_10  SURROGATE_CAT_2  SURROGATE_CAT_3  SURROGATE_CAT_4  \\\n",
       "0                 0                0                0                0   \n",
       "1                 0                0                0                0   \n",
       "2                 0                0                0                0   \n",
       "3                 0                0                0                0   \n",
       "4                 0                0                0                0   \n",
       "\n",
       "   SURROGATE_CAT_5  SURROGATE_CAT_6  SURROGATE_CAT_7  SURROGATE_CAT_8  \\\n",
       "0                0                0                0                0   \n",
       "1                0                0                0                0   \n",
       "2                0                0                0                0   \n",
       "3                0                0                0                0   \n",
       "4                0                0                0                0   \n",
       "\n",
       "   SURROGATE_CAT_9  GENDER_MALE  QUALIFICATION_GRADUATE  QUALIFICATION_OTHERS  \\\n",
       "0                0            1                       0                     0   \n",
       "1                0            1                       1                     0   \n",
       "2                0            1                       0                     0   \n",
       "3                0            1                       1                     0   \n",
       "4                0            1                       1                     0   \n",
       "\n",
       "   QUALIFICATION_POST-GRADUATE  QUALIFICATION_PROFESSIONAL  QUALIFICATION_SSC  \\\n",
       "0                            0                           0                  1   \n",
       "1                            0                           0                  0   \n",
       "2                            0                           0                  0   \n",
       "3                            0                           0                  0   \n",
       "4                            0                           0                  0   \n",
       "\n",
       "   QUALIFICATION_UNDER GRADUATE  CROSS_SELL_INSURANCE_1  DEALER_CUST_CITY_1  \\\n",
       "0                             0                       0                   1   \n",
       "1                             0                       0                   1   \n",
       "2                             1                       0                   1   \n",
       "3                             0                       1                   0   \n",
       "4                             0                       0                   1   \n",
       "\n",
       "   Disp_Month_bin_2  IS_WEEKEND_1  VOTERID_Y  PANNO_Y  DRIVING_LICENSE_Y  \\\n",
       "0                 0             0          0        1                  0   \n",
       "1                 0             0          0        0                  0   \n",
       "2                 0             0          0        0                  0   \n",
       "3                 0             0          0        0                  0   \n",
       "4                 0             1          0        0                  0   \n",
       "\n",
       "   AADHAR_Y  STATE_KA  STATE_MH  STATE_TN  STATE_WB  IS_EXIST_1.0  \\\n",
       "0         1         0         0         0         1             0   \n",
       "1         1         0         0         0         1             0   \n",
       "2         1         0         0         0         1             0   \n",
       "3         1         0         0         0         1             0   \n",
       "4         1         0         0         0         1             0   \n",
       "\n",
       "   IS_EXIST_2.0  IS_EXIST_3.0  IS_EXIST_nan  City_Group_2.0  City_Group_3.0  \\\n",
       "0             0             0             0               0               0   \n",
       "1             0             0             0               0               0   \n",
       "2             0             0             0               0               0   \n",
       "3             0             0             0               0               0   \n",
       "4             0             0             0               0               0   \n",
       "\n",
       "   PROD_GROUP_2  \n",
       "0             0  \n",
       "1             0  \n",
       "2             0  \n",
       "3             0  \n",
       "4             0  "
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_catv1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_catv1['PROPNO'] = data_new2['PROPNO']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_catv1 = data_catv1[['PROPNO','PREPAIDPOSTPAIDFLAG_1', 'PREPAIDPOSTPAIDFLAG_2', 'IS_MOBILE_1',\n",
    "       'IS_CIBIL_HIT_1', 'SCHEME_TYPE_CDOEM', 'SCHEME_TYPE_CDSPL',\n",
    "       'RESIDENT_TYPE_RENT', 'SURROGATE_CAT_1', 'SURROGATE_CAT_10',\n",
    "       'SURROGATE_CAT_2', 'SURROGATE_CAT_3', 'SURROGATE_CAT_4',\n",
    "       'SURROGATE_CAT_5', 'SURROGATE_CAT_6', 'SURROGATE_CAT_7',\n",
    "       'SURROGATE_CAT_8', 'SURROGATE_CAT_9', 'GENDER_MALE',\n",
    "       'QUALIFICATION_GRADUATE', 'QUALIFICATION_OTHERS',\n",
    "       'QUALIFICATION_POST-GRADUATE', 'QUALIFICATION_PROFESSIONAL',\n",
    "       'QUALIFICATION_SSC', 'QUALIFICATION_UNDER GRADUATE',\n",
    "       'CROSS_SELL_INSURANCE_1', 'DEALER_CUST_CITY_1', 'Disp_Month_bin_2',\n",
    "       'IS_WEEKEND_1', 'VOTERID_Y', 'PANNO_Y', 'DRIVING_LICENSE_Y', 'AADHAR_Y',\n",
    "       'STATE_KA', 'STATE_MH', 'STATE_TN', 'STATE_WB', 'IS_EXIST_1.0',\n",
    "       'IS_EXIST_2.0', 'IS_EXIST_3.0', 'IS_EXIST_nan', 'City_Group_2.0',\n",
    "       'City_Group_3.0', 'PROD_GROUP_2']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " No of Columns :  44\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>PROPNO</th>\n",
       "      <th>PREPAIDPOSTPAIDFLAG_1</th>\n",
       "      <th>PREPAIDPOSTPAIDFLAG_2</th>\n",
       "      <th>IS_MOBILE_1</th>\n",
       "      <th>IS_CIBIL_HIT_1</th>\n",
       "      <th>SCHEME_TYPE_CDOEM</th>\n",
       "      <th>SCHEME_TYPE_CDSPL</th>\n",
       "      <th>RESIDENT_TYPE_RENT</th>\n",
       "      <th>SURROGATE_CAT_1</th>\n",
       "      <th>SURROGATE_CAT_10</th>\n",
       "      <th>SURROGATE_CAT_2</th>\n",
       "      <th>SURROGATE_CAT_3</th>\n",
       "      <th>SURROGATE_CAT_4</th>\n",
       "      <th>SURROGATE_CAT_5</th>\n",
       "      <th>SURROGATE_CAT_6</th>\n",
       "      <th>SURROGATE_CAT_7</th>\n",
       "      <th>SURROGATE_CAT_8</th>\n",
       "      <th>SURROGATE_CAT_9</th>\n",
       "      <th>GENDER_MALE</th>\n",
       "      <th>QUALIFICATION_GRADUATE</th>\n",
       "      <th>QUALIFICATION_OTHERS</th>\n",
       "      <th>QUALIFICATION_POST-GRADUATE</th>\n",
       "      <th>QUALIFICATION_PROFESSIONAL</th>\n",
       "      <th>QUALIFICATION_SSC</th>\n",
       "      <th>QUALIFICATION_UNDER GRADUATE</th>\n",
       "      <th>CROSS_SELL_INSURANCE_1</th>\n",
       "      <th>DEALER_CUST_CITY_1</th>\n",
       "      <th>Disp_Month_bin_2</th>\n",
       "      <th>IS_WEEKEND_1</th>\n",
       "      <th>VOTERID_Y</th>\n",
       "      <th>PANNO_Y</th>\n",
       "      <th>DRIVING_LICENSE_Y</th>\n",
       "      <th>AADHAR_Y</th>\n",
       "      <th>STATE_KA</th>\n",
       "      <th>STATE_MH</th>\n",
       "      <th>STATE_TN</th>\n",
       "      <th>STATE_WB</th>\n",
       "      <th>IS_EXIST_1.0</th>\n",
       "      <th>IS_EXIST_2.0</th>\n",
       "      <th>IS_EXIST_3.0</th>\n",
       "      <th>IS_EXIST_nan</th>\n",
       "      <th>City_Group_2.0</th>\n",
       "      <th>City_Group_3.0</th>\n",
       "      <th>PROD_GROUP_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3078CD0009160</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>1</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3078CD0006318</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>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>1</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>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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3078CD0007026</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <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>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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3078CD0040195</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <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>1</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>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3078CD0006403</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>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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          PROPNO  PREPAIDPOSTPAIDFLAG_1  PREPAIDPOSTPAIDFLAG_2  IS_MOBILE_1  \\\n",
       "0  3078CD0009160                      0                      0            0   \n",
       "1  3078CD0006318                      0                      0            0   \n",
       "2  3078CD0007026                      0                      0            0   \n",
       "3  3078CD0040195                      0                      0            0   \n",
       "4  3078CD0006403                      0                      0            0   \n",
       "\n",
       "   IS_CIBIL_HIT_1  SCHEME_TYPE_CDOEM  SCHEME_TYPE_CDSPL  RESIDENT_TYPE_RENT  \\\n",
       "0               1                  1                  0                   0   \n",
       "1               1                  1                  0                   0   \n",
       "2               1                  1                  0                   0   \n",
       "3               1                  0                  1                   0   \n",
       "4               1                  1                  0                   0   \n",
       "\n",
       "   SURROGATE_CAT_1  SURROGATE_CAT_10  SURROGATE_CAT_2  SURROGATE_CAT_3  \\\n",
       "0                0                 0                0                0   \n",
       "1                0                 0                0                0   \n",
       "2                0                 0                0                0   \n",
       "3                0                 0                0                0   \n",
       "4                0                 0                0                0   \n",
       "\n",
       "   SURROGATE_CAT_4  SURROGATE_CAT_5  SURROGATE_CAT_6  SURROGATE_CAT_7  \\\n",
       "0                0                0                0                0   \n",
       "1                0                0                0                0   \n",
       "2                0                0                0                0   \n",
       "3                0                0                0                0   \n",
       "4                0                0                0                0   \n",
       "\n",
       "   SURROGATE_CAT_8  SURROGATE_CAT_9  GENDER_MALE  QUALIFICATION_GRADUATE  \\\n",
       "0                0                0            1                       0   \n",
       "1                0                0            1                       1   \n",
       "2                0                0            1                       0   \n",
       "3                0                0            1                       1   \n",
       "4                0                0            1                       1   \n",
       "\n",
       "   QUALIFICATION_OTHERS  QUALIFICATION_POST-GRADUATE  \\\n",
       "0                     0                            0   \n",
       "1                     0                            0   \n",
       "2                     0                            0   \n",
       "3                     0                            0   \n",
       "4                     0                            0   \n",
       "\n",
       "   QUALIFICATION_PROFESSIONAL  QUALIFICATION_SSC  \\\n",
       "0                           0                  1   \n",
       "1                           0                  0   \n",
       "2                           0                  0   \n",
       "3                           0                  0   \n",
       "4                           0                  0   \n",
       "\n",
       "   QUALIFICATION_UNDER GRADUATE  CROSS_SELL_INSURANCE_1  DEALER_CUST_CITY_1  \\\n",
       "0                             0                       0                   1   \n",
       "1                             0                       0                   1   \n",
       "2                             1                       0                   1   \n",
       "3                             0                       1                   0   \n",
       "4                             0                       0                   1   \n",
       "\n",
       "   Disp_Month_bin_2  IS_WEEKEND_1  VOTERID_Y  PANNO_Y  DRIVING_LICENSE_Y  \\\n",
       "0                 0             0          0        1                  0   \n",
       "1                 0             0          0        0                  0   \n",
       "2                 0             0          0        0                  0   \n",
       "3                 0             0          0        0                  0   \n",
       "4                 0             1          0        0                  0   \n",
       "\n",
       "   AADHAR_Y  STATE_KA  STATE_MH  STATE_TN  STATE_WB  IS_EXIST_1.0  \\\n",
       "0         1         0         0         0         1             0   \n",
       "1         1         0         0         0         1             0   \n",
       "2         1         0         0         0         1             0   \n",
       "3         1         0         0         0         1             0   \n",
       "4         1         0         0         0         1             0   \n",
       "\n",
       "   IS_EXIST_2.0  IS_EXIST_3.0  IS_EXIST_nan  City_Group_2.0  City_Group_3.0  \\\n",
       "0             0             0             0               0               0   \n",
       "1             0             0             0               0               0   \n",
       "2             0             0             0               0               0   \n",
       "3             0             0             0               0               0   \n",
       "4             0             0             0               0               0   \n",
       "\n",
       "   PROD_GROUP_2  \n",
       "0             0  \n",
       "1             0  \n",
       "2             0  \n",
       "3             0  \n",
       "4             0  "
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\" No of Columns : \", len(data_catv1.columns))\n",
    "data_catv1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_num = data_new2.select_dtypes(exclude='object')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_num = data_num.fillna(value=-9999)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_num = data_num[['PROPNO','LTV', 'ACTUAL_LOAN_AMOUNT', 'LATESTBALANCE', 'ELIGIBILITYGRID',\n",
    "       'Mobile_Bill_bin', 'AGE', 'TICKET_SIZE', 'AGE_BIN',\n",
    "       'CIBIL_SCORE_NEW_BIN', 'Actual_TAT_Days', 'IS_MANDATE_REGISTERED']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " No of Columns :  12\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>PROPNO</th>\n",
       "      <th>LTV</th>\n",
       "      <th>ACTUAL_LOAN_AMOUNT</th>\n",
       "      <th>LATESTBALANCE</th>\n",
       "      <th>ELIGIBILITYGRID</th>\n",
       "      <th>Mobile_Bill_bin</th>\n",
       "      <th>AGE</th>\n",
       "      <th>TICKET_SIZE</th>\n",
       "      <th>AGE_BIN</th>\n",
       "      <th>CIBIL_SCORE_NEW_BIN</th>\n",
       "      <th>Actual_TAT_Days</th>\n",
       "      <th>IS_MANDATE_REGISTERED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3078CD0009160</td>\n",
       "      <td>68.18</td>\n",
       "      <td>32611</td>\n",
       "      <td>190758.16</td>\n",
       "      <td>-9999.0</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>10</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3078CD0009162</td>\n",
       "      <td>77.77</td>\n",
       "      <td>17032</td>\n",
       "      <td>-9999.00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>35</td>\n",
       "      <td>8</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3078CD0009161</td>\n",
       "      <td>74.66</td>\n",
       "      <td>18664</td>\n",
       "      <td>29890.58</td>\n",
       "      <td>-9999.0</td>\n",
       "      <td>0</td>\n",
       "      <td>34</td>\n",
       "      <td>8</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3009CD0000002</td>\n",
       "      <td>77.78</td>\n",
       "      <td>37332</td>\n",
       "      <td>188925.04</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>10</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3009CD0000011</td>\n",
       "      <td>77.77</td>\n",
       "      <td>31108</td>\n",
       "      <td>3270.44</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>10</td>\n",
       "      <td>9.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          PROPNO    LTV  ACTUAL_LOAN_AMOUNT  LATESTBALANCE  ELIGIBILITYGRID  \\\n",
       "0  3078CD0009160  68.18               32611      190758.16          -9999.0   \n",
       "1  3078CD0009162  77.77               17032       -9999.00              5.0   \n",
       "2  3078CD0009161  74.66               18664       29890.58          -9999.0   \n",
       "3  3009CD0000002  77.78               37332      188925.04             16.0   \n",
       "4  3009CD0000011  77.77               31108        3270.44             22.0   \n",
       "\n",
       "   Mobile_Bill_bin  AGE  TICKET_SIZE  AGE_BIN  CIBIL_SCORE_NEW_BIN  \\\n",
       "0                0   32           10      5.0                  1.0   \n",
       "1                0   35            8      6.0                  6.0   \n",
       "2                0   34            8      6.0                  7.0   \n",
       "3                0   53           10      9.0                  1.0   \n",
       "4                0   50           10      9.0                  8.0   \n",
       "\n",
       "   Actual_TAT_Days  IS_MANDATE_REGISTERED  \n",
       "0                1                      0  \n",
       "1                5                      0  \n",
       "2                2                      0  \n",
       "3                8                      0  \n",
       "4                1                      0  "
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\" No of Columns : \", len(data_num.columns))\n",
    "data_num.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_num['PROPNO'] = data1['PROPNO']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "491740"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data_catv1.PROPNO)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "491740"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data_num.PROPNO)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_catv1.to_csv(\"D:/CD/data_cat.csv\")\n",
    "data_num.to_csv(\"D:/CD/data_num.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [],
   "source": [
    "datacat = pd.read_csv(\"D:/CD/data_cat.csv\")\n",
    "datanum = pd.read_csv(\"D:/CD/data_num.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [],
   "source": [
    "datacat = datacat.iloc[:,1:]\n",
    "datanum = datanum.iloc[:,1:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = pd.merge(datacat, datanum, on=['PROPNO'], how='inner')"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "final_df = pd.read_csv(\"D:/CD/CD_variables_28_06_onehot.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PROPNO</th>\n",
       "      <th>PREPAIDPOSTPAIDFLAG_1</th>\n",
       "      <th>PREPAIDPOSTPAIDFLAG_2</th>\n",
       "      <th>IS_MOBILE_1</th>\n",
       "      <th>IS_CIBIL_HIT_1</th>\n",
       "      <th>SCHEME_TYPE_CDOEM</th>\n",
       "      <th>SCHEME_TYPE_CDSPL</th>\n",
       "      <th>RESIDENT_TYPE_RENT</th>\n",
       "      <th>SURROGATE_CAT_1</th>\n",
       "      <th>SURROGATE_CAT_10</th>\n",
       "      <th>SURROGATE_CAT_2</th>\n",
       "      <th>SURROGATE_CAT_3</th>\n",
       "      <th>SURROGATE_CAT_4</th>\n",
       "      <th>SURROGATE_CAT_5</th>\n",
       "      <th>SURROGATE_CAT_6</th>\n",
       "      <th>SURROGATE_CAT_7</th>\n",
       "      <th>SURROGATE_CAT_8</th>\n",
       "      <th>SURROGATE_CAT_9</th>\n",
       "      <th>GENDER_MALE</th>\n",
       "      <th>QUALIFICATION_GRADUATE</th>\n",
       "      <th>QUALIFICATION_OTHERS</th>\n",
       "      <th>QUALIFICATION_POST-GRADUATE</th>\n",
       "      <th>QUALIFICATION_PROFESSIONAL</th>\n",
       "      <th>QUALIFICATION_SSC</th>\n",
       "      <th>QUALIFICATION_UNDER GRADUATE</th>\n",
       "      <th>CROSS_SELL_INSURANCE_1</th>\n",
       "      <th>DEALER_CUST_CITY_1</th>\n",
       "      <th>Disp_Month_bin_2</th>\n",
       "      <th>IS_WEEKEND_1</th>\n",
       "      <th>VOTERID_Y</th>\n",
       "      <th>PANNO_Y</th>\n",
       "      <th>DRIVING_LICENSE_Y</th>\n",
       "      <th>AADHAR_Y</th>\n",
       "      <th>STATE_KA</th>\n",
       "      <th>STATE_MH</th>\n",
       "      <th>STATE_TN</th>\n",
       "      <th>STATE_WB</th>\n",
       "      <th>IS_EXIST_1.0</th>\n",
       "      <th>IS_EXIST_2.0</th>\n",
       "      <th>IS_EXIST_3.0</th>\n",
       "      <th>IS_EXIST_nan</th>\n",
       "      <th>City_Group_2.0</th>\n",
       "      <th>City_Group_3.0</th>\n",
       "      <th>PROD_GROUP_2</th>\n",
       "      <th>LTV</th>\n",
       "      <th>ACTUAL_LOAN_AMOUNT</th>\n",
       "      <th>LATESTBALANCE</th>\n",
       "      <th>ELIGIBILITYGRID</th>\n",
       "      <th>Mobile_Bill_bin</th>\n",
       "      <th>AGE</th>\n",
       "      <th>TICKET_SIZE</th>\n",
       "      <th>AGE_BIN</th>\n",
       "      <th>CIBIL_SCORE_NEW_BIN</th>\n",
       "      <th>Actual_TAT_Days</th>\n",
       "      <th>IS_MANDATE_REGISTERED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3078CD0009160</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>1</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <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>68.18</td>\n",
       "      <td>32611</td>\n",
       "      <td>190758.16</td>\n",
       "      <td>-9999.0</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>10</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3078CD0006318</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>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>1</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>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>66.65</td>\n",
       "      <td>11990</td>\n",
       "      <td>-9999.00</td>\n",
       "      <td>-9999.0</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3078CD0007026</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <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>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>66.66</td>\n",
       "      <td>15332</td>\n",
       "      <td>-9999.00</td>\n",
       "      <td>-9999.0</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>7</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3078CD0040195</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <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>1</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>1</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>66.65</td>\n",
       "      <td>9690</td>\n",
       "      <td>3088.30</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3078CD0006403</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>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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>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>66.67</td>\n",
       "      <td>9332</td>\n",
       "      <td>-9999.00</td>\n",
       "      <td>-9999.0</td>\n",
       "      <td>0</td>\n",
       "      <td>46</td>\n",
       "      <td>2</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          PROPNO  PREPAIDPOSTPAIDFLAG_1  PREPAIDPOSTPAIDFLAG_2  IS_MOBILE_1  \\\n",
       "0  3078CD0009160                      0                      0            0   \n",
       "1  3078CD0006318                      0                      0            0   \n",
       "2  3078CD0007026                      0                      0            0   \n",
       "3  3078CD0040195                      0                      0            0   \n",
       "4  3078CD0006403                      0                      0            0   \n",
       "\n",
       "   IS_CIBIL_HIT_1  SCHEME_TYPE_CDOEM  SCHEME_TYPE_CDSPL  RESIDENT_TYPE_RENT  \\\n",
       "0               1                  1                  0                   0   \n",
       "1               1                  1                  0                   0   \n",
       "2               1                  1                  0                   0   \n",
       "3               1                  0                  1                   0   \n",
       "4               1                  1                  0                   0   \n",
       "\n",
       "   SURROGATE_CAT_1  SURROGATE_CAT_10  SURROGATE_CAT_2  SURROGATE_CAT_3  \\\n",
       "0                0                 0                0                0   \n",
       "1                0                 0                0                0   \n",
       "2                0                 0                0                0   \n",
       "3                0                 0                0                0   \n",
       "4                0                 0                0                0   \n",
       "\n",
       "   SURROGATE_CAT_4  SURROGATE_CAT_5  SURROGATE_CAT_6  SURROGATE_CAT_7  \\\n",
       "0                0                0                0                0   \n",
       "1                0                0                0                0   \n",
       "2                0                0                0                0   \n",
       "3                0                0                0                0   \n",
       "4                0                0                0                0   \n",
       "\n",
       "   SURROGATE_CAT_8  SURROGATE_CAT_9  GENDER_MALE  QUALIFICATION_GRADUATE  \\\n",
       "0                0                0            1                       0   \n",
       "1                0                0            1                       1   \n",
       "2                0                0            1                       0   \n",
       "3                0                0            1                       1   \n",
       "4                0                0            1                       1   \n",
       "\n",
       "   QUALIFICATION_OTHERS  QUALIFICATION_POST-GRADUATE  \\\n",
       "0                     0                            0   \n",
       "1                     0                            0   \n",
       "2                     0                            0   \n",
       "3                     0                            0   \n",
       "4                     0                            0   \n",
       "\n",
       "   QUALIFICATION_PROFESSIONAL  QUALIFICATION_SSC  \\\n",
       "0                           0                  1   \n",
       "1                           0                  0   \n",
       "2                           0                  0   \n",
       "3                           0                  0   \n",
       "4                           0                  0   \n",
       "\n",
       "   QUALIFICATION_UNDER GRADUATE  CROSS_SELL_INSURANCE_1  DEALER_CUST_CITY_1  \\\n",
       "0                             0                       0                   1   \n",
       "1                             0                       0                   1   \n",
       "2                             1                       0                   1   \n",
       "3                             0                       1                   0   \n",
       "4                             0                       0                   1   \n",
       "\n",
       "   Disp_Month_bin_2  IS_WEEKEND_1  VOTERID_Y  PANNO_Y  DRIVING_LICENSE_Y  \\\n",
       "0                 0             0          0        1                  0   \n",
       "1                 0             0          0        0                  0   \n",
       "2                 0             0          0        0                  0   \n",
       "3                 0             0          0        0                  0   \n",
       "4                 0             1          0        0                  0   \n",
       "\n",
       "   AADHAR_Y  STATE_KA  STATE_MH  STATE_TN  STATE_WB  IS_EXIST_1.0  \\\n",
       "0         1         0         0         0         1             0   \n",
       "1         1         0         0         0         1             0   \n",
       "2         1         0         0         0         1             0   \n",
       "3         1         0         0         0         1             0   \n",
       "4         1         0         0         0         1             0   \n",
       "\n",
       "   IS_EXIST_2.0  IS_EXIST_3.0  IS_EXIST_nan  City_Group_2.0  City_Group_3.0  \\\n",
       "0             0             0             0               0               0   \n",
       "1             0             0             0               0               0   \n",
       "2             0             0             0               0               0   \n",
       "3             0             0             0               0               0   \n",
       "4             0             0             0               0               0   \n",
       "\n",
       "   PROD_GROUP_2    LTV  ACTUAL_LOAN_AMOUNT  LATESTBALANCE  ELIGIBILITYGRID  \\\n",
       "0             0  68.18               32611      190758.16          -9999.0   \n",
       "1             0  66.65               11990       -9999.00          -9999.0   \n",
       "2             0  66.66               15332       -9999.00          -9999.0   \n",
       "3             0  66.65                9690        3088.30              5.0   \n",
       "4             0  66.67                9332       -9999.00          -9999.0   \n",
       "\n",
       "   Mobile_Bill_bin  AGE  TICKET_SIZE  AGE_BIN  CIBIL_SCORE_NEW_BIN  \\\n",
       "0                0   32           10      5.0                  1.0   \n",
       "1                0   29            4      4.0                  1.0   \n",
       "2                0   29            7      4.0                  1.0   \n",
       "3                0   27            2      3.0                  1.0   \n",
       "4                0   46            2      8.0                  1.0   \n",
       "\n",
       "   Actual_TAT_Days  IS_MANDATE_REGISTERED  \n",
       "0                1                      0  \n",
       "1                0                      0  \n",
       "2                1                      0  \n",
       "3                0                      0  \n",
       "4                0                      0  "
      ]
     },
     "execution_count": 192,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0, 1], dtype=int64)"
      ]
     },
     "execution_count": 193,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.IS_MANDATE_REGISTERED.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_csv(\"D:/CD/CD_variables_28_06_onehot.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df1 = final_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(final_df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "y = final_df[['IS_MANDATE_REGISTERED']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = final_df.drop('IS_MANDATE_REGISTERED', 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "X = final_df.iloc[:,1:38]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x_train, x_test, y_train, y_test = train_test_split(digits.data, digits.target, test_size=0.25, random_state=0)\n",
    "# all parameters not specified are set to their defaults\n",
    "logisticRegr = LogisticRegression()\n",
    "logisticRegr.fit(x_train, y_train)\n",
    "# Returns a NumPy Array\n",
    "# Predict for One Observation (image)\n",
    "logisticRegr.predict(x_test[0].reshape(1,-1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'PRODUCT_CODE' 'ELIGIBILITYGRID' 'CITY' 'SCHEME_TYPE' 'Bank_Type'"
   ]
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}