CD_MB_CIBIL_CRIF_CV_Null_audit.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import cx_Oracle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "ip = '172.16.80.74'\n",
    "port = '1521'\n",
    "SID = 'RPTDB'\n",
    "\n",
    "dsn_tns = cx_Oracle.makedsn(ip, port, SID)\n",
    "connection = cx_Oracle.connect('ANLYUSR', 'ANLYUSR$', dsn_tns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [],
   "source": [
    "tab_name = ['TBL_TRN_MB_CIB_ACCOUNT_SEGMENT',\n",
    "'TBL_TRN_MB_CIB_ADDRESSLIST',\n",
    "'TBL_TRN_MB_CIB_EMPLOYMENT_SEG',\n",
    "'TBL_TRN_MB_CIB_RESPONSE_SCORE',\n",
    "'TBL_TRN_MB_CIB_PHONELIST',\n",
    "'TBL_TRN_MB_CIB_ID_SEGMENT',\n",
    "'TBL_TRN_MB_CIB_ENQUIRYLIST',\n",
    "'TBL_TRN_MB_CIB_NAMEHEADER',\n",
    "'TBL_TRN_MB_CIB_ACCOUNTS_DETAIL',\n",
    "'TBL_TRN_MB_HIGH_ACCOUNTSUMMARY',\n",
    "'TBL_TRN_MB_HIGH_INDVRESPONSE',\n",
    "'TBL_TRN_MB_HIGH_INDVRESP_ADDR',\n",
    "'TBL_TRN_MB_HIGH_INDVRESP_ID',\n",
    "'TBL_TRN_MB_HIGH_INDVRESP_PHONE',\n",
    "'TBL_TRN_MB_HIGH_REQUEST',\n",
    "'TBL_TRN_MB_HIGH_SCORE',\n",
    "'TBL_TRN_CV_METADATA',\n",
    "'TBL_TRN_CV_LOCATIONPROFILE',\n",
    "'TBL_TRN_CV_CREDITCARDACCOUNT',\n",
    "'TBL_TRN_CV_LOANACCOUNT',\n",
    "'TBL_TRN_CV_LOANS',\n",
    "'TBL_TRN_CV_CREDITSCORE_FRAUD',\n",
    "'TBL_TRN_CV_BANKACCOUNT',\n",
    "'TBL_TRN_CV_EMAILIDS',\n",
    "'TBL_TRN_CV_INCOME',\n",
    "'TBL_TRN_CV_ALTERNATECONTACT',\n",
    "'TBL_TRN_CV_ACCOUNTS',\n",
    "'TBL_TRN_CV_UTILITYACCOUNT',\n",
    "'TBL_TRN_CV_CREDITCARD',\n",
    "'TBL_TRN_MST_CV_DETAILS']"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "query = \"SELECT COLUMN_NAME FROM all_tab_columns \\\n",
    "where OWNER = 'BIUSR' AND TABLE_NAME = {} \".format(\"'\"+tab_name[0]+\"'\")"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "data = pd.read_sql(query, con=connection)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "unique_col = []"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "for i in data.COLUMN_NAME.values:\n",
    "    unique_col.append(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [],
   "source": [
    "flag1 = True\n",
    "for i in tab_name:\n",
    "    \n",
    "    query = \"SELECT COLUMN_NAME FROM all_tab_columns \\\n",
    "    where OWNER = 'BIUSR' AND TABLE_NAME = {} \".format(\"'\"+i+\"'\")\n",
    "    \n",
    "    columns_DF = pd.read_sql(query, con=connection)\n",
    "\n",
    "    flag2 = True\n",
    "    \n",
    "    if '_CV_' in i: \n",
    "        for j in columns_DF.COLUMN_NAME.values:\n",
    "        \n",
    "            query1 = \"SELECT tb1.status as Identifiers,count(tb1.status) as NULL_count from \\\n",
    "            (SELECT DECODE({},'null','is_null',null,'is_null','not_null')  as status FROM BIUSR.{} WHERE PROPNO like '%CD%') tb1 \\\n",
    "            group by tb1.status\".format(j,i)\n",
    "    \n",
    "            result = pd.read_sql(query1, con=connection)\n",
    "    \n",
    "            result['Column_Name'] = j\n",
    "            result['Table_Name'] = i\n",
    "    \n",
    "            if flag2:\n",
    "                final_result = result\n",
    "                flag2 = False\n",
    "            else:\n",
    "                final_result = pd.concat([final_result,result])\n",
    "    else:\n",
    "        for k in columns_DF.COLUMN_NAME.values:\n",
    "        \n",
    "            query1 = \"SELECT tb1.status as Identifiers,count(tb1.status) as NULL_count from \\\n",
    "            (SELECT DECODE({},'null','is_null',null,'is_null','not_null')  as status FROM BIUSR.{} WHERE PROSPECT_NO like '%CD%') tb1 \\\n",
    "            group by tb1.status\".format(k,i)\n",
    "    \n",
    "            result = pd.read_sql(query1, con=connection)\n",
    "    \n",
    "            result['Column_Name'] = k\n",
    "            result['Table_Name'] = i\n",
    "    \n",
    "            if flag2:\n",
    "                final_result = result\n",
    "                flag2 = False\n",
    "            else:\n",
    "                final_result = pd.concat([final_result,result])\n",
    "        \n",
    "    if flag1:\n",
    "        consol_list = final_result\n",
    "        flag1 = False\n",
    "    else:\n",
    "        consol_list = pd.concat([consol_list,final_result])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "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>IDENTIFIERS</th>\n",
       "      <th>NULL_COUNT</th>\n",
       "      <th>Column_Name</th>\n",
       "      <th>Table_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>PROSPECT_NO</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>SEQ_ACCOUNT_NO</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>REPORTING_MEMBER_SHORTNAME</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>ACCOUNT_NUMBER</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>ACCOUNT_TYPE</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>OWNERSHIP_INDICATOR</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>4572</td>\n",
       "      <td>DATE_OPEN_DISBURSED</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1609089</td>\n",
       "      <td>DATE_OPEN_DISBURSED</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>398986</td>\n",
       "      <td>DATE_LASTPAYMENT</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1214675</td>\n",
       "      <td>DATE_LASTPAYMENT</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>692269</td>\n",
       "      <td>DATE_CLOSED</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>not_null</td>\n",
       "      <td>921392</td>\n",
       "      <td>DATE_CLOSED</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>DATE_REPORTED_AND_CERTIFIED</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>21032</td>\n",
       "      <td>HIGH_CREDIT_SANCTIONED_AMOUNT</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1592629</td>\n",
       "      <td>HIGH_CREDIT_SANCTIONED_AMOUNT</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>CURRENT_BALANCE</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>OVERDUE_AMOUNT</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>PAYMENT_HISTORY1</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>1250348</td>\n",
       "      <td>PAYMENT_HISTORY2</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>not_null</td>\n",
       "      <td>363313</td>\n",
       "      <td>PAYMENT_HISTORY2</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>PAYMENT_HISTORY_STARTDATE</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>PAYMENT_HISTORY_ENDDATE</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>SUIT_FILED_WILFUL_DEFAULT</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>WRITTEN_OFF_SETTLED_STATUS</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>VALUE_OF_COLLATERAL</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>TYPE_OF_COLLATERAL</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>CREDIT_LIMIT</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>CASH_LIMIT</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>REPAYMENT_TENURE</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is_null</td>\n",
       "      <td>1613661</td>\n",
       "      <td>EMI_AMOUNT</td>\n",
       "      <td>TBL_TRN_MB_CIB_ACCOUNT_SEGMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>286642</td>\n",
       "      <td>COUNTOFDISTINCTAPPCATEGORIES</td>\n",
       "      <td>TBL_TRN_CV_UTILITYACCOUNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>286642</td>\n",
       "      <td>COUNTOFSOCIALAPPS</td>\n",
       "      <td>TBL_TRN_CV_UTILITYACCOUNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>286642</td>\n",
       "      <td>COUNTOFFINANCIALAPPS</td>\n",
       "      <td>TBL_TRN_CV_UTILITYACCOUNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>286642</td>\n",
       "      <td>COUNTOFFITNESSAPPS</td>\n",
       "      <td>TBL_TRN_CV_UTILITYACCOUNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>286642</td>\n",
       "      <td>COUNTOFSHOPPINGAPPS</td>\n",
       "      <td>TBL_TRN_CV_UTILITYACCOUNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>286642</td>\n",
       "      <td>COUNTOFWALLETAPPS</td>\n",
       "      <td>TBL_TRN_CV_UTILITYACCOUNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>286642</td>\n",
       "      <td>DEVICENAME</td>\n",
       "      <td>TBL_TRN_CV_UTILITYACCOUNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>286642</td>\n",
       "      <td>DEVICEPRICE</td>\n",
       "      <td>TBL_TRN_CV_UTILITYACCOUNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>286642</td>\n",
       "      <td>CREATED_DATE</td>\n",
       "      <td>TBL_TRN_CV_UTILITYACCOUNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>PROPNO</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>MOBILE</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>SR_NO</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>CARD_NO</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>ISSUERNAME</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>CREDITLIMIT</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>MAXCREDITUTILIZATION</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>TOTALCCTRAXNAMOUNTMONTHYEAR0</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>TOTALCCTRAXNAMOUNTMONTHYEAR1</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>TOTALCCTRAXNAMOUNTMONTHYEAR2</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>TOTALCCPAYMENTMONTHYEAR0</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>TOTALCCPAYMENTMONTHYEAR1</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>TOTALCCPAYMENTMONTHYEAR2</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>COUNTOVERDUELAST3MONTHS</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>COUNTOVERDUEEVER</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>LATESTCCDUEDATE</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>15048</td>\n",
       "      <td>CREATED_DATE</td>\n",
       "      <td>TBL_TRN_CV_CREDITCARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>296979</td>\n",
       "      <td>PROPNO</td>\n",
       "      <td>TBL_TRN_MST_CV_DETAILS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>296979</td>\n",
       "      <td>SR_NO</td>\n",
       "      <td>TBL_TRN_MST_CV_DETAILS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>296979</td>\n",
       "      <td>MOBILE_NO</td>\n",
       "      <td>TBL_TRN_MST_CV_DETAILS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_null</td>\n",
       "      <td>296979</td>\n",
       "      <td>CREATED_DATE</td>\n",
       "      <td>TBL_TRN_MST_CV_DETAILS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>631 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   IDENTIFIERS NULL_COUNT                    Column_Name  \\\n",
       "0     not_null    1613661                    PROSPECT_NO   \n",
       "0     not_null    1613661                 SEQ_ACCOUNT_NO   \n",
       "0     not_null    1613661     REPORTING_MEMBER_SHORTNAME   \n",
       "0      is_null    1613661                 ACCOUNT_NUMBER   \n",
       "0     not_null    1613661                   ACCOUNT_TYPE   \n",
       "0     not_null    1613661            OWNERSHIP_INDICATOR   \n",
       "0      is_null       4572            DATE_OPEN_DISBURSED   \n",
       "1     not_null    1609089            DATE_OPEN_DISBURSED   \n",
       "0      is_null     398986               DATE_LASTPAYMENT   \n",
       "1     not_null    1214675               DATE_LASTPAYMENT   \n",
       "0      is_null     692269                    DATE_CLOSED   \n",
       "1     not_null     921392                    DATE_CLOSED   \n",
       "0     not_null    1613661    DATE_REPORTED_AND_CERTIFIED   \n",
       "0      is_null      21032  HIGH_CREDIT_SANCTIONED_AMOUNT   \n",
       "1     not_null    1592629  HIGH_CREDIT_SANCTIONED_AMOUNT   \n",
       "0     not_null    1613661                CURRENT_BALANCE   \n",
       "0      is_null    1613661                 OVERDUE_AMOUNT   \n",
       "0     not_null    1613661               PAYMENT_HISTORY1   \n",
       "0      is_null    1250348               PAYMENT_HISTORY2   \n",
       "1     not_null     363313               PAYMENT_HISTORY2   \n",
       "0     not_null    1613661      PAYMENT_HISTORY_STARTDATE   \n",
       "0     not_null    1613661        PAYMENT_HISTORY_ENDDATE   \n",
       "0      is_null    1613661      SUIT_FILED_WILFUL_DEFAULT   \n",
       "0      is_null    1613661     WRITTEN_OFF_SETTLED_STATUS   \n",
       "0      is_null    1613661            VALUE_OF_COLLATERAL   \n",
       "0      is_null    1613661             TYPE_OF_COLLATERAL   \n",
       "0      is_null    1613661                   CREDIT_LIMIT   \n",
       "0      is_null    1613661                     CASH_LIMIT   \n",
       "0      is_null    1613661               REPAYMENT_TENURE   \n",
       "0      is_null    1613661                     EMI_AMOUNT   \n",
       "..         ...        ...                            ...   \n",
       "0     not_null     286642   COUNTOFDISTINCTAPPCATEGORIES   \n",
       "0     not_null     286642              COUNTOFSOCIALAPPS   \n",
       "0     not_null     286642           COUNTOFFINANCIALAPPS   \n",
       "0     not_null     286642             COUNTOFFITNESSAPPS   \n",
       "0     not_null     286642            COUNTOFSHOPPINGAPPS   \n",
       "0     not_null     286642              COUNTOFWALLETAPPS   \n",
       "0     not_null     286642                     DEVICENAME   \n",
       "0     not_null     286642                    DEVICEPRICE   \n",
       "0     not_null     286642                   CREATED_DATE   \n",
       "0     not_null      15048                         PROPNO   \n",
       "0     not_null      15048                         MOBILE   \n",
       "0     not_null      15048                          SR_NO   \n",
       "0     not_null      15048                        CARD_NO   \n",
       "0     not_null      15048                     ISSUERNAME   \n",
       "0     not_null      15048                    CREDITLIMIT   \n",
       "0     not_null      15048           MAXCREDITUTILIZATION   \n",
       "0     not_null      15048   TOTALCCTRAXNAMOUNTMONTHYEAR0   \n",
       "0     not_null      15048   TOTALCCTRAXNAMOUNTMONTHYEAR1   \n",
       "0     not_null      15048   TOTALCCTRAXNAMOUNTMONTHYEAR2   \n",
       "0     not_null      15048       TOTALCCPAYMENTMONTHYEAR0   \n",
       "0     not_null      15048       TOTALCCPAYMENTMONTHYEAR1   \n",
       "0     not_null      15048       TOTALCCPAYMENTMONTHYEAR2   \n",
       "0     not_null      15048        COUNTOVERDUELAST3MONTHS   \n",
       "0     not_null      15048               COUNTOVERDUEEVER   \n",
       "0     not_null      15048                LATESTCCDUEDATE   \n",
       "0     not_null      15048                   CREATED_DATE   \n",
       "0     not_null     296979                         PROPNO   \n",
       "0     not_null     296979                          SR_NO   \n",
       "0     not_null     296979                      MOBILE_NO   \n",
       "0     not_null     296979                   CREATED_DATE   \n",
       "\n",
       "                        Table_Name  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "1   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "1   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "1   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "1   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "1   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "0   TBL_TRN_MB_CIB_ACCOUNT_SEGMENT  \n",
       "..                             ...  \n",
       "0        TBL_TRN_CV_UTILITYACCOUNT  \n",
       "0        TBL_TRN_CV_UTILITYACCOUNT  \n",
       "0        TBL_TRN_CV_UTILITYACCOUNT  \n",
       "0        TBL_TRN_CV_UTILITYACCOUNT  \n",
       "0        TBL_TRN_CV_UTILITYACCOUNT  \n",
       "0        TBL_TRN_CV_UTILITYACCOUNT  \n",
       "0        TBL_TRN_CV_UTILITYACCOUNT  \n",
       "0        TBL_TRN_CV_UTILITYACCOUNT  \n",
       "0        TBL_TRN_CV_UTILITYACCOUNT  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0            TBL_TRN_CV_CREDITCARD  \n",
       "0           TBL_TRN_MST_CV_DETAILS  \n",
       "0           TBL_TRN_MST_CV_DETAILS  \n",
       "0           TBL_TRN_MST_CV_DETAILS  \n",
       "0           TBL_TRN_MST_CV_DETAILS  \n",
       "\n",
       "[631 rows x 4 columns]"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "consol_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = consol_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset['NULL_COUNT'] = dataset.NULL_COUNT.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_test = pd.pivot_table(dataset, values = 'NULL_COUNT',\n",
    "               index=['Table_Name','Column_Name'],\n",
    "               columns = 'IDENTIFIERS').reset_index().fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_test = pd.DataFrame(data_test.to_records())[['Table_Name','Column_Name','is_null','not_null']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_test['Total_Records'] = data_test['is_null']+data_test['not_null']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cal_null_perc(row):\n",
    "    try:\n",
    "        return round((row['is_null']/row['Total_Records'])*100,2)\n",
    "    except:\n",
    "        return 0.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_test['null_percentage'] = data_test.apply(cal_null_perc, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "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>Table_Name</th>\n",
       "      <th>Column_Name</th>\n",
       "      <th>is_null</th>\n",
       "      <th>not_null</th>\n",
       "      <th>Total_Records</th>\n",
       "      <th>null_percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>ACCOUNT_CNT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>AMBMONTH0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>AMBMONTH1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>AMBMONTH2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>BANKNAME</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>CREATED_DATE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>CREDITMONTH0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>CREDITMONTH1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>CREDITMONTH2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>DEBITMONTH0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>DEBITMONTH1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>DEBITMONTH2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>LATESTBALANCE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>MOBILE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>PROPNO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>SR_NO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>VOLATILITYBALANCE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>VOLATILITYCREDIT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>TBL_TRN_CV_ACCOUNTS</td>\n",
       "      <td>VOLATILITYDEBIT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>187323.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>TBL_TRN_CV_ALTERNATECONTACT</td>\n",
       "      <td>CREATED_DATE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>TBL_TRN_CV_ALTERNATECONTACT</td>\n",
       "      <td>FREQCONTACT1</td>\n",
       "      <td>129310.0</td>\n",
       "      <td>152086.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>45.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>TBL_TRN_CV_ALTERNATECONTACT</td>\n",
       "      <td>FREQCONTACT2</td>\n",
       "      <td>139038.0</td>\n",
       "      <td>142358.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>49.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>TBL_TRN_CV_ALTERNATECONTACT</td>\n",
       "      <td>FREQCONTACT3</td>\n",
       "      <td>143865.0</td>\n",
       "      <td>137531.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>51.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>TBL_TRN_CV_ALTERNATECONTACT</td>\n",
       "      <td>FREQFAMILYCONTACT</td>\n",
       "      <td>215401.0</td>\n",
       "      <td>65995.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>76.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>TBL_TRN_CV_ALTERNATECONTACT</td>\n",
       "      <td>MOBILE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>TBL_TRN_CV_ALTERNATECONTACT</td>\n",
       "      <td>PROPNO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>TBL_TRN_CV_ALTERNATECONTACT</td>\n",
       "      <td>REGULARLYCALLEDCONTACT1</td>\n",
       "      <td>137921.0</td>\n",
       "      <td>143475.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>49.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>TBL_TRN_CV_ALTERNATECONTACT</td>\n",
       "      <td>REGULARLYCALLEDCONTACT2</td>\n",
       "      <td>157728.0</td>\n",
       "      <td>123668.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>56.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>TBL_TRN_CV_ALTERNATECONTACT</td>\n",
       "      <td>REGULARLYCALLEDCONTACT3</td>\n",
       "      <td>167209.0</td>\n",
       "      <td>114187.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>59.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>TBL_TRN_CV_ALTERNATECONTACT</td>\n",
       "      <td>SR_NO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>281396.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>CREDITREQUESTTYPE</td>\n",
       "      <td>334501.0</td>\n",
       "      <td>294797.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>53.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>EMAIL1</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>451</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>FATHER</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>KENDRA</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>453</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>LOANAMOUNT</td>\n",
       "      <td>334501.0</td>\n",
       "      <td>294797.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>53.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>LOSAPPLICATIONID</td>\n",
       "      <td>334501.0</td>\n",
       "      <td>294797.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>53.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>MEMBERID</td>\n",
       "      <td>334502.0</td>\n",
       "      <td>294796.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>53.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>MFIGROUP</td>\n",
       "      <td>334501.0</td>\n",
       "      <td>294797.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>53.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>457</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>MFIIND</td>\n",
       "      <td>334501.0</td>\n",
       "      <td>294797.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>53.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>MFISCORE</td>\n",
       "      <td>334501.0</td>\n",
       "      <td>294797.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>53.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>459</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>NAME</td>\n",
       "      <td>334501.0</td>\n",
       "      <td>294797.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>53.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>460</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>PROSPECT_NO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>461</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>REQ_DOB</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>462</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>REQ_GENDER</td>\n",
       "      <td>334501.0</td>\n",
       "      <td>294797.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>53.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>SR_NO</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>TRACKING_ID</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>465</th>\n",
       "      <td>TBL_TRN_MB_HIGH_REQUEST</td>\n",
       "      <td>UPDATED_DATE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466</th>\n",
       "      <td>TBL_TRN_MB_HIGH_SCORE</td>\n",
       "      <td>ACK_ID</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>467</th>\n",
       "      <td>TBL_TRN_MB_HIGH_SCORE</td>\n",
       "      <td>CREATED_DATE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>468</th>\n",
       "      <td>TBL_TRN_MB_HIGH_SCORE</td>\n",
       "      <td>HIGH_SCORECOMMENTS</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>469</th>\n",
       "      <td>TBL_TRN_MB_HIGH_SCORE</td>\n",
       "      <td>HIGH_SCORETYPE</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>470</th>\n",
       "      <td>TBL_TRN_MB_HIGH_SCORE</td>\n",
       "      <td>HIGH_SCOREVALUE</td>\n",
       "      <td>227948.0</td>\n",
       "      <td>401350.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>36.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>471</th>\n",
       "      <td>TBL_TRN_MB_HIGH_SCORE</td>\n",
       "      <td>PROSPECT_NO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>TBL_TRN_MB_HIGH_SCORE</td>\n",
       "      <td>SR_NO</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>TBL_TRN_MB_HIGH_SCORE</td>\n",
       "      <td>TRACKING_ID</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>TBL_TRN_MB_HIGH_SCORE</td>\n",
       "      <td>UPDATED_DATE</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>629298.0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>TBL_TRN_MST_CV_DETAILS</td>\n",
       "      <td>CREATED_DATE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>296979.0</td>\n",
       "      <td>296979.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>TBL_TRN_MST_CV_DETAILS</td>\n",
       "      <td>MOBILE_NO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>296979.0</td>\n",
       "      <td>296979.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>477</th>\n",
       "      <td>TBL_TRN_MST_CV_DETAILS</td>\n",
       "      <td>PROPNO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>296979.0</td>\n",
       "      <td>296979.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>478</th>\n",
       "      <td>TBL_TRN_MST_CV_DETAILS</td>\n",
       "      <td>SR_NO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>296979.0</td>\n",
       "      <td>296979.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>479 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Table_Name              Column_Name   is_null  not_null  \\\n",
       "0            TBL_TRN_CV_ACCOUNTS              ACCOUNT_CNT       0.0  187323.0   \n",
       "1            TBL_TRN_CV_ACCOUNTS                AMBMONTH0       0.0  187323.0   \n",
       "2            TBL_TRN_CV_ACCOUNTS                AMBMONTH1       0.0  187323.0   \n",
       "3            TBL_TRN_CV_ACCOUNTS                AMBMONTH2       0.0  187323.0   \n",
       "4            TBL_TRN_CV_ACCOUNTS                 BANKNAME       0.0  187323.0   \n",
       "5            TBL_TRN_CV_ACCOUNTS             CREATED_DATE       0.0  187323.0   \n",
       "6            TBL_TRN_CV_ACCOUNTS             CREDITMONTH0       0.0  187323.0   \n",
       "7            TBL_TRN_CV_ACCOUNTS             CREDITMONTH1       0.0  187323.0   \n",
       "8            TBL_TRN_CV_ACCOUNTS             CREDITMONTH2       0.0  187323.0   \n",
       "9            TBL_TRN_CV_ACCOUNTS              DEBITMONTH0       0.0  187323.0   \n",
       "10           TBL_TRN_CV_ACCOUNTS              DEBITMONTH1       0.0  187323.0   \n",
       "11           TBL_TRN_CV_ACCOUNTS              DEBITMONTH2       0.0  187323.0   \n",
       "12           TBL_TRN_CV_ACCOUNTS            LATESTBALANCE       0.0  187323.0   \n",
       "13           TBL_TRN_CV_ACCOUNTS                   MOBILE       0.0  187323.0   \n",
       "14           TBL_TRN_CV_ACCOUNTS                   PROPNO       0.0  187323.0   \n",
       "15           TBL_TRN_CV_ACCOUNTS                    SR_NO       0.0  187323.0   \n",
       "16           TBL_TRN_CV_ACCOUNTS        VOLATILITYBALANCE       0.0  187323.0   \n",
       "17           TBL_TRN_CV_ACCOUNTS         VOLATILITYCREDIT       0.0  187323.0   \n",
       "18           TBL_TRN_CV_ACCOUNTS          VOLATILITYDEBIT       0.0  187323.0   \n",
       "19   TBL_TRN_CV_ALTERNATECONTACT             CREATED_DATE       0.0  281396.0   \n",
       "20   TBL_TRN_CV_ALTERNATECONTACT             FREQCONTACT1  129310.0  152086.0   \n",
       "21   TBL_TRN_CV_ALTERNATECONTACT             FREQCONTACT2  139038.0  142358.0   \n",
       "22   TBL_TRN_CV_ALTERNATECONTACT             FREQCONTACT3  143865.0  137531.0   \n",
       "23   TBL_TRN_CV_ALTERNATECONTACT        FREQFAMILYCONTACT  215401.0   65995.0   \n",
       "24   TBL_TRN_CV_ALTERNATECONTACT                   MOBILE       0.0  281396.0   \n",
       "25   TBL_TRN_CV_ALTERNATECONTACT                   PROPNO       0.0  281396.0   \n",
       "26   TBL_TRN_CV_ALTERNATECONTACT  REGULARLYCALLEDCONTACT1  137921.0  143475.0   \n",
       "27   TBL_TRN_CV_ALTERNATECONTACT  REGULARLYCALLEDCONTACT2  157728.0  123668.0   \n",
       "28   TBL_TRN_CV_ALTERNATECONTACT  REGULARLYCALLEDCONTACT3  167209.0  114187.0   \n",
       "29   TBL_TRN_CV_ALTERNATECONTACT                    SR_NO       0.0  281396.0   \n",
       "..                           ...                      ...       ...       ...   \n",
       "449      TBL_TRN_MB_HIGH_REQUEST        CREDITREQUESTTYPE  334501.0  294797.0   \n",
       "450      TBL_TRN_MB_HIGH_REQUEST                   EMAIL1  629298.0       0.0   \n",
       "451      TBL_TRN_MB_HIGH_REQUEST                   FATHER  629298.0       0.0   \n",
       "452      TBL_TRN_MB_HIGH_REQUEST                   KENDRA  629298.0       0.0   \n",
       "453      TBL_TRN_MB_HIGH_REQUEST               LOANAMOUNT  334501.0  294797.0   \n",
       "454      TBL_TRN_MB_HIGH_REQUEST         LOSAPPLICATIONID  334501.0  294797.0   \n",
       "455      TBL_TRN_MB_HIGH_REQUEST                 MEMBERID  334502.0  294796.0   \n",
       "456      TBL_TRN_MB_HIGH_REQUEST                 MFIGROUP  334501.0  294797.0   \n",
       "457      TBL_TRN_MB_HIGH_REQUEST                   MFIIND  334501.0  294797.0   \n",
       "458      TBL_TRN_MB_HIGH_REQUEST                 MFISCORE  334501.0  294797.0   \n",
       "459      TBL_TRN_MB_HIGH_REQUEST                     NAME  334501.0  294797.0   \n",
       "460      TBL_TRN_MB_HIGH_REQUEST              PROSPECT_NO       0.0  629298.0   \n",
       "461      TBL_TRN_MB_HIGH_REQUEST                  REQ_DOB  629298.0       0.0   \n",
       "462      TBL_TRN_MB_HIGH_REQUEST               REQ_GENDER  334501.0  294797.0   \n",
       "463      TBL_TRN_MB_HIGH_REQUEST                    SR_NO  629298.0       0.0   \n",
       "464      TBL_TRN_MB_HIGH_REQUEST              TRACKING_ID       0.0  629298.0   \n",
       "465      TBL_TRN_MB_HIGH_REQUEST             UPDATED_DATE       0.0  629298.0   \n",
       "466        TBL_TRN_MB_HIGH_SCORE                   ACK_ID  629298.0       0.0   \n",
       "467        TBL_TRN_MB_HIGH_SCORE             CREATED_DATE       0.0  629298.0   \n",
       "468        TBL_TRN_MB_HIGH_SCORE       HIGH_SCORECOMMENTS  629298.0       0.0   \n",
       "469        TBL_TRN_MB_HIGH_SCORE           HIGH_SCORETYPE  629298.0       0.0   \n",
       "470        TBL_TRN_MB_HIGH_SCORE          HIGH_SCOREVALUE  227948.0  401350.0   \n",
       "471        TBL_TRN_MB_HIGH_SCORE              PROSPECT_NO       0.0  629298.0   \n",
       "472        TBL_TRN_MB_HIGH_SCORE                    SR_NO  629298.0       0.0   \n",
       "473        TBL_TRN_MB_HIGH_SCORE              TRACKING_ID       0.0  629298.0   \n",
       "474        TBL_TRN_MB_HIGH_SCORE             UPDATED_DATE  629298.0       0.0   \n",
       "475       TBL_TRN_MST_CV_DETAILS             CREATED_DATE       0.0  296979.0   \n",
       "476       TBL_TRN_MST_CV_DETAILS                MOBILE_NO       0.0  296979.0   \n",
       "477       TBL_TRN_MST_CV_DETAILS                   PROPNO       0.0  296979.0   \n",
       "478       TBL_TRN_MST_CV_DETAILS                    SR_NO       0.0  296979.0   \n",
       "\n",
       "     Total_Records  null_percentage  \n",
       "0         187323.0             0.00  \n",
       "1         187323.0             0.00  \n",
       "2         187323.0             0.00  \n",
       "3         187323.0             0.00  \n",
       "4         187323.0             0.00  \n",
       "5         187323.0             0.00  \n",
       "6         187323.0             0.00  \n",
       "7         187323.0             0.00  \n",
       "8         187323.0             0.00  \n",
       "9         187323.0             0.00  \n",
       "10        187323.0             0.00  \n",
       "11        187323.0             0.00  \n",
       "12        187323.0             0.00  \n",
       "13        187323.0             0.00  \n",
       "14        187323.0             0.00  \n",
       "15        187323.0             0.00  \n",
       "16        187323.0             0.00  \n",
       "17        187323.0             0.00  \n",
       "18        187323.0             0.00  \n",
       "19        281396.0             0.00  \n",
       "20        281396.0            45.95  \n",
       "21        281396.0            49.41  \n",
       "22        281396.0            51.13  \n",
       "23        281396.0            76.55  \n",
       "24        281396.0             0.00  \n",
       "25        281396.0             0.00  \n",
       "26        281396.0            49.01  \n",
       "27        281396.0            56.05  \n",
       "28        281396.0            59.42  \n",
       "29        281396.0             0.00  \n",
       "..             ...              ...  \n",
       "449       629298.0            53.15  \n",
       "450       629298.0           100.00  \n",
       "451       629298.0           100.00  \n",
       "452       629298.0           100.00  \n",
       "453       629298.0            53.15  \n",
       "454       629298.0            53.15  \n",
       "455       629298.0            53.15  \n",
       "456       629298.0            53.15  \n",
       "457       629298.0            53.15  \n",
       "458       629298.0            53.15  \n",
       "459       629298.0            53.15  \n",
       "460       629298.0             0.00  \n",
       "461       629298.0           100.00  \n",
       "462       629298.0            53.15  \n",
       "463       629298.0           100.00  \n",
       "464       629298.0             0.00  \n",
       "465       629298.0             0.00  \n",
       "466       629298.0           100.00  \n",
       "467       629298.0             0.00  \n",
       "468       629298.0           100.00  \n",
       "469       629298.0           100.00  \n",
       "470       629298.0            36.22  \n",
       "471       629298.0             0.00  \n",
       "472       629298.0           100.00  \n",
       "473       629298.0             0.00  \n",
       "474       629298.0           100.00  \n",
       "475       296979.0             0.00  \n",
       "476       296979.0             0.00  \n",
       "477       296979.0             0.00  \n",
       "478       296979.0             0.00  \n",
       "\n",
       "[479 rows x 6 columns]"
      ]
     },
     "execution_count": 206,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_test.to_csv(\"D:\\CD\\CD_MB_CIBIL_CRIF_CV_AUdit.csv\")"
   ]
  }
 ],
 "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
}