1.1.Data_wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### DATA WRANGLING"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>ID</th>\n",
       "      <th>ACCOUNT_TYPE</th>\n",
       "      <th>HIGH_CREDIT_OR_SANCTIONED_AMOUNT</th>\n",
       "      <th>DATE_OPENED</th>\n",
       "      <th>CURRENT_BALANCE</th>\n",
       "      <th>ACTUAL_PAYMT_AMT</th>\n",
       "      <th>EMI_AMOUNT</th>\n",
       "      <th>REPAYMENT_TENURE</th>\n",
       "      <th>LOAN_CLASSIFICATION</th>\n",
       "      <th>AMOUNT_OVERDUE</th>\n",
       "      <th>PAYMENT_HISTORY_1</th>\n",
       "      <th>PAYMENT_HISTORY_2</th>\n",
       "      <th>OWNERSHIP_TYPE</th>\n",
       "      <th>COLLATERALVALUE</th>\n",
       "      <th>TU_SCORE</th>\n",
       "      <th>PAYMENT_HISTORY_START_DATE</th>\n",
       "      <th>PAYMENT_HISTORY_END_DATE</th>\n",
       "      <th>DATE_REPORTED_AND_CERTIFIED</th>\n",
       "      <th>DATE_OF_LAST_PAYMENT</th>\n",
       "      <th>Reported_Date</th>\n",
       "      <th>DATE_OF_BIRTH</th>\n",
       "      <th>OCCUPATION_TYPE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>ACTUAL_ROI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002338349</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>818517</td>\n",
       "      <td>13-06-2014</td>\n",
       "      <td>640742</td>\n",
       "      <td>8627.0</td>\n",
       "      <td>8627.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000520520520...</td>\n",
       "      <td>3.00E+19</td>\n",
       "      <td>Joint</td>\n",
       "      <td>2086000.0</td>\n",
       "      <td>681</td>\n",
       "      <td>01-11-2023</td>\n",
       "      <td>01-12-2020</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>21-05-1971</td>\n",
       "      <td>SALARIED</td>\n",
       "      <td>Male</td>\n",
       "      <td>12.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002000537</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>1243755</td>\n",
       "      <td>05-12-2012</td>\n",
       "      <td>949446</td>\n",
       "      <td>500.0</td>\n",
       "      <td>12210.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>0</td>\n",
       "      <td>Joint</td>\n",
       "      <td>1536000.0</td>\n",
       "      <td>784</td>\n",
       "      <td>01-11-2023</td>\n",
       "      <td>01-12-2020</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>12-11-1984</td>\n",
       "      <td>SALARIED</td>\n",
       "      <td>Female</td>\n",
       "      <td>12.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A002421579</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>1826422</td>\n",
       "      <td>16-03-2017</td>\n",
       "      <td>1296849</td>\n",
       "      <td>16036.0</td>\n",
       "      <td>16036.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>0</td>\n",
       "      <td>Joint</td>\n",
       "      <td>3587700.0</td>\n",
       "      <td>748</td>\n",
       "      <td>01-11-2023</td>\n",
       "      <td>01-12-2020</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>11-11-2023</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>01-08-1986</td>\n",
       "      <td>SALARIED</td>\n",
       "      <td>Male</td>\n",
       "      <td>10.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A002152345</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>1847916</td>\n",
       "      <td>24-12-2022</td>\n",
       "      <td>1724256</td>\n",
       "      <td>26996.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Joint</td>\n",
       "      <td>3049600.0</td>\n",
       "      <td>786</td>\n",
       "      <td>01-11-2023</td>\n",
       "      <td>01-12-2022</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>05-11-2023</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>22-07-1969</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Female</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A001952834</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>2318386</td>\n",
       "      <td>21-10-2014</td>\n",
       "      <td>1790937</td>\n",
       "      <td>25131.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>116.0</td>\n",
       "      <td>539</td>\n",
       "      <td>431551.0</td>\n",
       "      <td>5395705705405095065075075075075095095094794784...</td>\n",
       "      <td>4.50E+53</td>\n",
       "      <td>Joint</td>\n",
       "      <td>2785400.0</td>\n",
       "      <td>664</td>\n",
       "      <td>01-11-2023</td>\n",
       "      <td>01-12-2020</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>27-11-2023</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>28-10-1974</td>\n",
       "      <td>SALARIED</td>\n",
       "      <td>Male</td>\n",
       "      <td>11.97</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           ID  ACCOUNT_TYPE  HIGH_CREDIT_OR_SANCTIONED_AMOUNT DATE_OPENED  \\\n",
       "0  A002338349  Housing Loan                            818517  13-06-2014   \n",
       "1  A002000537  Housing Loan                           1243755  05-12-2012   \n",
       "2  A002421579  Housing Loan                           1826422  16-03-2017   \n",
       "3  A002152345  Housing Loan                           1847916  24-12-2022   \n",
       "4  A001952834  Housing Loan                           2318386  21-10-2014   \n",
       "\n",
       "   CURRENT_BALANCE  ACTUAL_PAYMT_AMT  EMI_AMOUNT  REPAYMENT_TENURE  \\\n",
       "0           640742            8627.0      8627.0             142.0   \n",
       "1           949446             500.0     12210.0             156.0   \n",
       "2          1296849           16036.0     16036.0             148.0   \n",
       "3          1724256           26996.0         NaN               NaN   \n",
       "4          1790937           25131.0         NaN             116.0   \n",
       "\n",
       "   LOAN_CLASSIFICATION  AMOUNT_OVERDUE  \\\n",
       "0                    0             NaN   \n",
       "1                    0             NaN   \n",
       "2                    0             NaN   \n",
       "3                    0             NaN   \n",
       "4                  539        431551.0   \n",
       "\n",
       "                                   PAYMENT_HISTORY_1 PAYMENT_HISTORY_2  \\\n",
       "0  0000000000000000000000000000000000000520520520...          3.00E+19   \n",
       "1  0000000000000000000000000000000000000000000000...                 0   \n",
       "2  0000000000000000000000000000000000000000000000...                 0   \n",
       "3                                                  0               NaN   \n",
       "4  5395705705405095065075075075075095095094794784...          4.50E+53   \n",
       "\n",
       "  OWNERSHIP_TYPE  COLLATERALVALUE  TU_SCORE PAYMENT_HISTORY_START_DATE  \\\n",
       "0          Joint        2086000.0       681                 01-11-2023   \n",
       "1          Joint        1536000.0       784                 01-11-2023   \n",
       "2          Joint        3587700.0       748                 01-11-2023   \n",
       "3          Joint        3049600.0       786                 01-11-2023   \n",
       "4          Joint        2785400.0       664                 01-11-2023   \n",
       "\n",
       "  PAYMENT_HISTORY_END_DATE DATE_REPORTED_AND_CERTIFIED DATE_OF_LAST_PAYMENT  \\\n",
       "0               01-12-2020                  30-11-2023           30-11-2023   \n",
       "1               01-12-2020                  30-11-2023           30-11-2023   \n",
       "2               01-12-2020                  30-11-2023           11-11-2023   \n",
       "3               01-12-2022                  30-11-2023           05-11-2023   \n",
       "4               01-12-2020                  30-11-2023           27-11-2023   \n",
       "\n",
       "  Reported_Date DATE_OF_BIRTH OCCUPATION_TYPE  GENDER  ACTUAL_ROI  \n",
       "0    30-11-2023    21-05-1971        SALARIED    Male       12.32  \n",
       "1    30-11-2023    12-11-1984        SALARIED  Female       12.42  \n",
       "2    30-11-2023    01-08-1986        SALARIED    Male       10.87  \n",
       "3    30-11-2023    22-07-1969             NaN  Female       10.00  \n",
       "4    30-11-2023    28-10-1974        SALARIED    Male       11.97  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv(\"train_main_loan.csv\")\n",
    "pd.set_option('display.max_columns', None)\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### *PAYMENT HISTORY 1 (MAKE IT MORE INTERPRATABLE BY THE MODEL)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def preprocess_payment_history(payment_history):\n",
    "    \n",
    "    if payment_history == '0':\n",
    "        return '0' * 54\n",
    "    elif len(payment_history) == 54:\n",
    "        return payment_history\n",
    "    elif len(payment_history) < 54 and 'XXX' in payment_history:\n",
    "        return payment_history + '0' * (54 - len(payment_history))\n",
    "    elif 'E' in payment_history:\n",
    "        # Convert to a standard number and then format it to a string\n",
    "        calculated_value = format(float(payment_history), '.0f')\n",
    "        return '0' * (54 - len(calculated_value)) + calculated_value\n",
    "    elif len(payment_history) > 1 and 'XXX' not in payment_history:\n",
    "        return '0' * (54 - len(payment_history)) + payment_history\n",
    "    else:\n",
    "        return '0'*54\n",
    "\n",
    "# Apply the preprocessing function to the 'PAYMENT_HISTORY_1' column\n",
    "df['PAYMENT_HISTORY_1'] = df['PAYMENT_HISTORY_1'].apply(preprocess_payment_history)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "\n",
    "def wrangle_payment_history(payment_history):\n",
    "    # Break the 54-character string into 3-character segments\n",
    "    segments = [payment_history[i:i+3] for i in range(0, len(payment_history), 3)]\n",
    "\n",
    "    # Feature 1: Number of times paid ('000')\n",
    "    count_paid = segments.count('000')\n",
    "\n",
    "    # Feature 2: Number of times late (non-zero but not 'XXX')\n",
    "    count_late = sum(1 for segment in segments if segment != '000' and 'XXX' not in segment)\n",
    "\n",
    "    # Feature 3: Number of times took more than 90 days\n",
    "    count_more_than_90 = sum(1 for segment in segments if segment.isdigit() and int(segment) > 90)\n",
    "\n",
    "    # Feature 4: Number of times took days between 0-60\n",
    "    count_0_to_60 = sum(1 for segment in segments if segment.isdigit() and 0 < int(segment) <= 60)\n",
    "\n",
    "    # Feature 5: Average days (non-'XXX' values, excluding '000' and 'XXX')\n",
    "    non_xxx_values = [int(segment) for segment in segments if segment.isdigit() and segment != '000']\n",
    "    avg_days = sum(non_xxx_values) / len(non_xxx_values) if non_xxx_values else 0\n",
    "\n",
    "    # Feature 6: Number of missing days\n",
    "    count_missing_days = sum(1 for segment in segments if 'XXX' in segment)\n",
    "\n",
    "    return count_paid, count_late, count_more_than_90, count_0_to_60, avg_days, count_missing_days\n",
    "\n",
    "# Apply the wrangling function to the 'PAYMENT_HISTORY_1' column and store results in separate columns\n",
    "df[['COUNT_PAID', 'COUNT_LATE', 'COUNT_MORE_THAN_90', 'COUNT_0_TO_60', 'AVG_DAYS', 'COUNT_MISSING_DAYS']] = df['PAYMENT_HISTORY_1'].apply(wrangle_payment_history).tolist()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### EXTRACTING NUMERICAL FEATURES FROM THE DATES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def date_opened_to_month(df):\n",
    "  df['DATE_OPENED'] = pd.to_datetime(df['DATE_OPENED'], format='%d-%m-%Y')\n",
    "  \n",
    "  # Define the constant 'Reported_Date'\n",
    "  constant_reported_date = pd.to_datetime('30-11-2023', format='%d-%m-%Y')\n",
    "\n",
    "  # Calculate the number of months\n",
    "  df['Months_Since_Opened'] = (constant_reported_date - df['DATE_OPENED']).dt.days // 30\n",
    "\n",
    "  # Replace 'DATE_OPENED' with the calculated number of months\n",
    "  df['DATE_OPENED'] = df['Months_Since_Opened']\n",
    "\n",
    "  # Drop the 'Months_Since_Opened' column if you don't need it anymore\n",
    "  df.drop(columns=['Months_Since_Opened'], inplace=True)\n",
    "  df.rename(columns={'DATE_OPENED': 'MONTH_OPENED'}, inplace=True)\n",
    "\n",
    "  return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def dob_to_age(df):\n",
    "  df['DATE_OF_BIRTH'] = pd.to_datetime(df['DATE_OF_BIRTH'], format='%d-%m-%Y')\n",
    "  # Define the constant 'Reported_Date'\n",
    "  constant_reported_date = pd.to_datetime('30-11-2023', format='%d-%m-%Y')\n",
    "  # Calculate the number of years\n",
    "  df['Years_Since_Birth'] = (constant_reported_date - df['DATE_OF_BIRTH']).dt.days // 365\n",
    "  # Replace 'DATE_OF_BIRTH' with the calculated number of years\n",
    "  df['DATE_OF_BIRTH'] = df['Years_Since_Birth']\n",
    "  # Drop the 'Years_Since_Birth' column if you don't need it anymore\n",
    "  df.drop(columns=['Years_Since_Birth'], inplace=True)\n",
    "  df.rename(columns={'DATE_OF_BIRTH': 'AGE'}, inplace=True)\n",
    "  return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def date_certified_to_month(df):\n",
    "  df['DATE_REPORTED_AND_CERTIFIED'] = pd.to_datetime(df['DATE_REPORTED_AND_CERTIFIED'], format='%d-%m-%Y')\n",
    "\n",
    "  # Define the constant 'Reported_Date'\n",
    "  constant_reported_date = pd.to_datetime('30-11-2023', format='%d-%m-%Y')\n",
    "\n",
    "  # Calculate the number of months\n",
    "  df['Months_Since_Opened'] = (constant_reported_date - df['DATE_REPORTED_AND_CERTIFIED']).dt.days // 30\n",
    "\n",
    "  # Replace 'DATE_OPENED' with the calculated number of months\n",
    "  df['DATE_REPORTED_AND_CERTIFIED'] = df['Months_Since_Opened']\n",
    "\n",
    "  # Drop the 'Months_Since_Opened' column if you don't need it anymore\n",
    "  df.drop(columns=['Months_Since_Opened'], inplace=True)\n",
    "  df.rename(columns={'DATE_REPORTED_AND_CERTIFIED': 'MONTH_CERTIFIED'}, inplace=True)\n",
    "\n",
    "  return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def last_payment_to_month(df):\n",
    "  df['DATE_OF_LAST_PAYMENT'] = pd.to_datetime(df['DATE_OF_LAST_PAYMENT'], format='%d-%m-%Y')\n",
    "\n",
    "  # Define the constant 'Reported_Date'\n",
    "  constant_reported_date = pd.to_datetime('30-11-2023', format='%d-%m-%Y')\n",
    "\n",
    "  # Calculate the number of months\n",
    "  df['Months_Since_Opened'] = (constant_reported_date - df['DATE_OF_LAST_PAYMENT']).dt.days // 30\n",
    "\n",
    "  # Replace 'DATE_OPENED' with the calculated number of months\n",
    "  df['DATE_OF_LAST_PAYMENT'] = df['Months_Since_Opened']\n",
    "\n",
    "  # Drop the 'Months_Since_Opened' column if you don't need it anymore\n",
    "  df.drop(columns=['Months_Since_Opened'], inplace=True)\n",
    "  df.rename(columns={'DATE_OF_LAST_PAYMENT': 'MONTH_LAST_PAYMENT'}, inplace=True)\n",
    "\n",
    "  return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def history_start_end_dates(df):\n",
    "  df['DATE_OF_LAST_PAYMENT'] = pd.to_datetime(df['DATE_OF_LAST_PAYMENT'], format='%d-%m-%Y')\n",
    "\n",
    "  # Define the constant 'Reported_Date'\n",
    "  constant_reported_date = pd.to_datetime('30-11-2023', format='%d-%m-%Y')\n",
    "\n",
    "  # Calculate the number of months\n",
    "  df['Months_Since_Opened'] = (constant_reported_date - df['DATE_OF_LAST_PAYMENT']).dt.days // 30\n",
    "\n",
    "  # Replace 'DATE_OPENED' with the calculated number of months\n",
    "  df['DATE_OF_LAST_PAYMENT'] = df['Months_Since_Opened']\n",
    "\n",
    "  # Drop the 'Months_Since_Opened' column if you don't need it anymore\n",
    "  df.drop(columns=['Months_Since_Opened'], inplace=True)\n",
    "  df.rename(columns={'DATE_OF_LAST_PAYMENT': 'MONTH_LAST_PAYMENT'}, inplace=True)\n",
    "\n",
    "  return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def history_start_end_dates(df):\n",
    "    # Convert 'PAYMENT_HISTORY_START_DATE' and 'PAYMENT_HISTORY_END_DATE' to datetime\n",
    "    df['PAYMENT_HISTORY_START_DATE'] = pd.to_datetime(df['PAYMENT_HISTORY_START_DATE'], format='%d-%m-%Y')\n",
    "    df['PAYMENT_HISTORY_END_DATE'] = pd.to_datetime(df['PAYMENT_HISTORY_END_DATE'], format='%d-%m-%Y')\n",
    "\n",
    "    # Add your desired operations for 'PAYMENT_HISTORY_START_DATE' here (if needed)\n",
    "\n",
    "    # Example operation: Calculate the number of months between 'PAYMENT_HISTORY_START_DATE' and a constant date\n",
    "    constant_reported_date = pd.to_datetime('30-11-2023', format='%d-%m-%Y')\n",
    "    df['Months_Since_Payment_Start'] = (constant_reported_date - df['PAYMENT_HISTORY_START_DATE']).dt.days // 30\n",
    "\n",
    "    # Replace 'PAYMENT_HISTORY_START_DATE' with the calculated number of months\n",
    "    df['PAYMENT_HISTORY_START_DATE'] = df['Months_Since_Payment_Start']\n",
    "\n",
    "    df.drop(columns=['Months_Since_Payment_Start'], inplace=True)\n",
    "\n",
    "    df.rename(columns={'PAYMENT_HISTORY_START_DATE': 'MONTH_PAYMENT_HISTORY_START'}, inplace=True)\n",
    "\n",
    "    \n",
    "    df['Months_Since_Payment_End'] = (constant_reported_date - df['PAYMENT_HISTORY_END_DATE']).dt.days // 30\n",
    "\n",
    "    df['PAYMENT_HISTORY_END_DATE'] = df['Months_Since_Payment_End']\n",
    "\n",
    "    # Drop the 'Months_Since_Payment_End' column if you don't need it anymore\n",
    "    df.drop(columns=['Months_Since_Payment_End'], inplace=True)\n",
    "\n",
    "    # Rename 'PAYMENT_HISTORY_END_DATE' to 'MONTH_PAYMENT_HISTORY_END'\n",
    "    df.rename(columns={'PAYMENT_HISTORY_END_DATE': 'MONTH_PAYMENT_HISTORY_END'}, inplace=True)\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### *applying these functions*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new=date_certified_to_month(df)\n",
    "df_new=last_payment_to_month(df_new)\n",
    "df_new=dob_to_age(df_new)\n",
    "df_new=date_opened_to_month(df_new)\n",
    "df_new=history_start_end_dates(df_new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>ID</th>\n",
       "      <th>ACCOUNT_TYPE</th>\n",
       "      <th>HIGH_CREDIT_OR_SANCTIONED_AMOUNT</th>\n",
       "      <th>MONTH_OPENED</th>\n",
       "      <th>CURRENT_BALANCE</th>\n",
       "      <th>ACTUAL_PAYMT_AMT</th>\n",
       "      <th>EMI_AMOUNT</th>\n",
       "      <th>REPAYMENT_TENURE</th>\n",
       "      <th>LOAN_CLASSIFICATION</th>\n",
       "      <th>AMOUNT_OVERDUE</th>\n",
       "      <th>PAYMENT_HISTORY_1</th>\n",
       "      <th>PAYMENT_HISTORY_2</th>\n",
       "      <th>OWNERSHIP_TYPE</th>\n",
       "      <th>COLLATERALVALUE</th>\n",
       "      <th>TU_SCORE</th>\n",
       "      <th>MONTH_PAYMENT_HISTORY_START</th>\n",
       "      <th>MONTH_PAYMENT_HISTORY_END</th>\n",
       "      <th>MONTH_CERTIFIED</th>\n",
       "      <th>MONTH_LAST_PAYMENT</th>\n",
       "      <th>Reported_Date</th>\n",
       "      <th>AGE</th>\n",
       "      <th>OCCUPATION_TYPE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>COUNT_PAID</th>\n",
       "      <th>COUNT_LATE</th>\n",
       "      <th>COUNT_MORE_THAN_90</th>\n",
       "      <th>COUNT_0_TO_60</th>\n",
       "      <th>AVG_DAYS</th>\n",
       "      <th>COUNT_MISSING_DAYS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A000614177</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>2277986</td>\n",
       "      <td>91</td>\n",
       "      <td>1824507</td>\n",
       "      <td>22985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>149.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>0</td>\n",
       "      <td>Joint</td>\n",
       "      <td>3780000.0</td>\n",
       "      <td>780</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>35</td>\n",
       "      <td>SEP</td>\n",
       "      <td>Male</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A001744326</td>\n",
       "      <td>Business Loan</td>\n",
       "      <td>1030000</td>\n",
       "      <td>14</td>\n",
       "      <td>819209</td>\n",
       "      <td>30256.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Joint</td>\n",
       "      <td>NaN</td>\n",
       "      <td>774</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>47</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Male</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A000242802</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>960705</td>\n",
       "      <td>3</td>\n",
       "      <td>908604</td>\n",
       "      <td>25229.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Joint</td>\n",
       "      <td>2622010.0</td>\n",
       "      <td>809</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>59</td>\n",
       "      <td>SENP</td>\n",
       "      <td>Female</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B1000244547</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>1437990</td>\n",
       "      <td>69</td>\n",
       "      <td>1367609</td>\n",
       "      <td>13067.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>259.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>0</td>\n",
       "      <td>Joint</td>\n",
       "      <td>2947100.0</td>\n",
       "      <td>759</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>54</td>\n",
       "      <td>SENP</td>\n",
       "      <td>Male</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B3000178046</td>\n",
       "      <td>Business Loan</td>\n",
       "      <td>728474</td>\n",
       "      <td>1</td>\n",
       "      <td>718224</td>\n",
       "      <td>22350.0</td>\n",
       "      <td>21778.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Joint</td>\n",
       "      <td>NaN</td>\n",
       "      <td>731</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30-11-2023</td>\n",
       "      <td>50</td>\n",
       "      <td>SENP</td>\n",
       "      <td>Male</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID   ACCOUNT_TYPE  HIGH_CREDIT_OR_SANCTIONED_AMOUNT  MONTH_OPENED  \\\n",
       "0   A000614177   Housing Loan                           2277986            91   \n",
       "1   A001744326  Business Loan                           1030000            14   \n",
       "2   A000242802   Housing Loan                            960705             3   \n",
       "3  B1000244547   Housing Loan                           1437990            69   \n",
       "4  B3000178046  Business Loan                            728474             1   \n",
       "\n",
       "   CURRENT_BALANCE  ACTUAL_PAYMT_AMT  EMI_AMOUNT  REPAYMENT_TENURE  \\\n",
       "0          1824507           22985.0         NaN             149.0   \n",
       "1           819209           30256.0         NaN               NaN   \n",
       "2           908604           25229.0         NaN               NaN   \n",
       "3          1367609           13067.0         NaN             259.0   \n",
       "4           718224           22350.0     21778.0               NaN   \n",
       "\n",
       "   LOAN_CLASSIFICATION  AMOUNT_OVERDUE  \\\n",
       "0                    0             NaN   \n",
       "1                    0             NaN   \n",
       "2                    0             NaN   \n",
       "3                    0             NaN   \n",
       "4                    0             NaN   \n",
       "\n",
       "                                   PAYMENT_HISTORY_1 PAYMENT_HISTORY_2  \\\n",
       "0  0000000000000000000000000000000000000000000000...                 0   \n",
       "1  0000000000000000000000000000000000000000000000...               NaN   \n",
       "2  0000000000000000000000000000000000000000000000...               NaN   \n",
       "3  0000000000000000000000000000000000000000000000...                 0   \n",
       "4  0000000000000000000000000000000000000000000000...               NaN   \n",
       "\n",
       "  OWNERSHIP_TYPE  COLLATERALVALUE  TU_SCORE  MONTH_PAYMENT_HISTORY_START  \\\n",
       "0          Joint        3780000.0       780                            0   \n",
       "1          Joint              NaN       774                            0   \n",
       "2          Joint        2622010.0       809                            0   \n",
       "3          Joint        2947100.0       759                            0   \n",
       "4          Joint              NaN       731                            0   \n",
       "\n",
       "   MONTH_PAYMENT_HISTORY_END  MONTH_CERTIFIED  MONTH_LAST_PAYMENT  \\\n",
       "0                         36                0                 0.0   \n",
       "1                         15                0                 0.0   \n",
       "2                          4                0                 0.0   \n",
       "3                         36                0                 0.0   \n",
       "4                          2                0                 0.0   \n",
       "\n",
       "  Reported_Date  AGE OCCUPATION_TYPE  GENDER  COUNT_PAID  COUNT_LATE  \\\n",
       "0    30-11-2023   35             SEP    Male        17.0         0.0   \n",
       "1    30-11-2023   47             NaN    Male        18.0         0.0   \n",
       "2    30-11-2023   59            SENP  Female        18.0         0.0   \n",
       "3    30-11-2023   54            SENP    Male        17.0         0.0   \n",
       "4    30-11-2023   50            SENP    Male        18.0         0.0   \n",
       "\n",
       "   COUNT_MORE_THAN_90  COUNT_0_TO_60  AVG_DAYS  COUNT_MISSING_DAYS  \n",
       "0                 0.0            0.0       0.0                 1.0  \n",
       "1                 0.0            0.0       0.0                 0.0  \n",
       "2                 0.0            0.0       0.0                 0.0  \n",
       "3                 0.0            0.0       0.0                 1.0  \n",
       "4                 0.0            0.0       0.0                 0.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### *AT LAST THERE ARE SOME COLUMNS TO DROP THAT ARE NOT SIGNIFICANT*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns_to_drop = ['Reported_Date', 'PAYMENT_HISTORY_2']\n",
    "df_new = df_new.drop(columns=columns_to_drop)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>ACCOUNT_TYPE</th>\n",
       "      <th>HIGH_CREDIT_OR_SANCTIONED_AMOUNT</th>\n",
       "      <th>MONTH_OPENED</th>\n",
       "      <th>CURRENT_BALANCE</th>\n",
       "      <th>ACTUAL_PAYMT_AMT</th>\n",
       "      <th>EMI_AMOUNT</th>\n",
       "      <th>REPAYMENT_TENURE</th>\n",
       "      <th>LOAN_CLASSIFICATION</th>\n",
       "      <th>AMOUNT_OVERDUE</th>\n",
       "      <th>PAYMENT_HISTORY_1</th>\n",
       "      <th>OWNERSHIP_TYPE</th>\n",
       "      <th>COLLATERALVALUE</th>\n",
       "      <th>TU_SCORE</th>\n",
       "      <th>MONTH_PAYMENT_HISTORY_START</th>\n",
       "      <th>MONTH_PAYMENT_HISTORY_END</th>\n",
       "      <th>MONTH_CERTIFIED</th>\n",
       "      <th>MONTH_LAST_PAYMENT</th>\n",
       "      <th>AGE</th>\n",
       "      <th>OCCUPATION_TYPE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>COUNT_PAID</th>\n",
       "      <th>COUNT_LATE</th>\n",
       "      <th>COUNT_MORE_THAN_90</th>\n",
       "      <th>COUNT_0_TO_60</th>\n",
       "      <th>AVG_DAYS</th>\n",
       "      <th>COUNT_MISSING_DAYS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A000614177</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>2277986</td>\n",
       "      <td>91</td>\n",
       "      <td>1824507</td>\n",
       "      <td>22985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>149.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>Joint</td>\n",
       "      <td>3780000.0</td>\n",
       "      <td>780</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>35</td>\n",
       "      <td>SEP</td>\n",
       "      <td>Male</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A001744326</td>\n",
       "      <td>Business Loan</td>\n",
       "      <td>1030000</td>\n",
       "      <td>14</td>\n",
       "      <td>819209</td>\n",
       "      <td>30256.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>Joint</td>\n",
       "      <td>NaN</td>\n",
       "      <td>774</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Male</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A000242802</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>960705</td>\n",
       "      <td>3</td>\n",
       "      <td>908604</td>\n",
       "      <td>25229.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>Joint</td>\n",
       "      <td>2622010.0</td>\n",
       "      <td>809</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>59</td>\n",
       "      <td>SENP</td>\n",
       "      <td>Female</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B1000244547</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>1437990</td>\n",
       "      <td>69</td>\n",
       "      <td>1367609</td>\n",
       "      <td>13067.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>259.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>Joint</td>\n",
       "      <td>2947100.0</td>\n",
       "      <td>759</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>54</td>\n",
       "      <td>SENP</td>\n",
       "      <td>Male</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B3000178046</td>\n",
       "      <td>Business Loan</td>\n",
       "      <td>728474</td>\n",
       "      <td>1</td>\n",
       "      <td>718224</td>\n",
       "      <td>22350.0</td>\n",
       "      <td>21778.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>Joint</td>\n",
       "      <td>NaN</td>\n",
       "      <td>731</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50</td>\n",
       "      <td>SENP</td>\n",
       "      <td>Male</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29978</th>\n",
       "      <td>A002307275</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>949186</td>\n",
       "      <td>66</td>\n",
       "      <td>929479</td>\n",
       "      <td>8890.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>300.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>Joint</td>\n",
       "      <td>1190000.0</td>\n",
       "      <td>749</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39</td>\n",
       "      <td>SALARIED</td>\n",
       "      <td>Male</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29979</th>\n",
       "      <td>A000079323</td>\n",
       "      <td>Business Loan</td>\n",
       "      <td>765335</td>\n",
       "      <td>15</td>\n",
       "      <td>517748</td>\n",
       "      <td>28054.0</td>\n",
       "      <td>28054.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>000000000000000000000000000000000000XXX0000000...</td>\n",
       "      <td>Joint</td>\n",
       "      <td>NaN</td>\n",
       "      <td>743</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>43</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Female</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29980</th>\n",
       "      <td>A002385848</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>920928</td>\n",
       "      <td>105</td>\n",
       "      <td>916673</td>\n",
       "      <td>9974.0</td>\n",
       "      <td>9974.0</td>\n",
       "      <td>374.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>Joint</td>\n",
       "      <td>1859200.0</td>\n",
       "      <td>745</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>46</td>\n",
       "      <td>SENP</td>\n",
       "      <td>Male</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29981</th>\n",
       "      <td>A002435483</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>1108527</td>\n",
       "      <td>0</td>\n",
       "      <td>1108527</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21</td>\n",
       "      <td>2733.0</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>Joint</td>\n",
       "      <td>6226950.0</td>\n",
       "      <td>731</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38</td>\n",
       "      <td>SALARIED</td>\n",
       "      <td>Female</td>\n",
       "      <td>17.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29982</th>\n",
       "      <td>A001881482</td>\n",
       "      <td>Housing Loan</td>\n",
       "      <td>1481179</td>\n",
       "      <td>71</td>\n",
       "      <td>269572</td>\n",
       "      <td>24825.0</td>\n",
       "      <td>24825.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0000000000000000000000000000000000000000000000...</td>\n",
       "      <td>Joint</td>\n",
       "      <td>2065860.0</td>\n",
       "      <td>788</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>48</td>\n",
       "      <td>SALARIED</td>\n",
       "      <td>Male</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>29983 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID   ACCOUNT_TYPE  HIGH_CREDIT_OR_SANCTIONED_AMOUNT  \\\n",
       "0       A000614177   Housing Loan                           2277986   \n",
       "1       A001744326  Business Loan                           1030000   \n",
       "2       A000242802   Housing Loan                            960705   \n",
       "3      B1000244547   Housing Loan                           1437990   \n",
       "4      B3000178046  Business Loan                            728474   \n",
       "...            ...            ...                               ...   \n",
       "29978   A002307275   Housing Loan                            949186   \n",
       "29979   A000079323  Business Loan                            765335   \n",
       "29980   A002385848   Housing Loan                            920928   \n",
       "29981   A002435483   Housing Loan                           1108527   \n",
       "29982   A001881482   Housing Loan                           1481179   \n",
       "\n",
       "       MONTH_OPENED  CURRENT_BALANCE  ACTUAL_PAYMT_AMT  EMI_AMOUNT  \\\n",
       "0                91          1824507           22985.0         NaN   \n",
       "1                14           819209           30256.0         NaN   \n",
       "2                 3           908604           25229.0         NaN   \n",
       "3                69          1367609           13067.0         NaN   \n",
       "4                 1           718224           22350.0     21778.0   \n",
       "...             ...              ...               ...         ...   \n",
       "29978            66           929479            8890.0         NaN   \n",
       "29979            15           517748           28054.0     28054.0   \n",
       "29980           105           916673            9974.0      9974.0   \n",
       "29981             0          1108527               NaN         NaN   \n",
       "29982            71           269572           24825.0     24825.0   \n",
       "\n",
       "       REPAYMENT_TENURE  LOAN_CLASSIFICATION  AMOUNT_OVERDUE  \\\n",
       "0                 149.0                    0             NaN   \n",
       "1                   NaN                    0             NaN   \n",
       "2                   NaN                    0             NaN   \n",
       "3                 259.0                    0             NaN   \n",
       "4                   NaN                    0             NaN   \n",
       "...                 ...                  ...             ...   \n",
       "29978             300.0                    0             NaN   \n",
       "29979              36.0                    0             NaN   \n",
       "29980             374.0                    0             NaN   \n",
       "29981               NaN                   21          2733.0   \n",
       "29982              33.0                    0             NaN   \n",
       "\n",
       "                                       PAYMENT_HISTORY_1 OWNERSHIP_TYPE  \\\n",
       "0      0000000000000000000000000000000000000000000000...          Joint   \n",
       "1      0000000000000000000000000000000000000000000000...          Joint   \n",
       "2      0000000000000000000000000000000000000000000000...          Joint   \n",
       "3      0000000000000000000000000000000000000000000000...          Joint   \n",
       "4      0000000000000000000000000000000000000000000000...          Joint   \n",
       "...                                                  ...            ...   \n",
       "29978  0000000000000000000000000000000000000000000000...          Joint   \n",
       "29979  000000000000000000000000000000000000XXX0000000...          Joint   \n",
       "29980  0000000000000000000000000000000000000000000000...          Joint   \n",
       "29981  0000000000000000000000000000000000000000000000...          Joint   \n",
       "29982  0000000000000000000000000000000000000000000000...          Joint   \n",
       "\n",
       "       COLLATERALVALUE  TU_SCORE  MONTH_PAYMENT_HISTORY_START  \\\n",
       "0            3780000.0       780                            0   \n",
       "1                  NaN       774                            0   \n",
       "2            2622010.0       809                            0   \n",
       "3            2947100.0       759                            0   \n",
       "4                  NaN       731                            0   \n",
       "...                ...       ...                          ...   \n",
       "29978        1190000.0       749                            0   \n",
       "29979              NaN       743                            0   \n",
       "29980        1859200.0       745                            0   \n",
       "29981        6226950.0       731                            0   \n",
       "29982        2065860.0       788                            0   \n",
       "\n",
       "       MONTH_PAYMENT_HISTORY_END  MONTH_CERTIFIED  MONTH_LAST_PAYMENT  AGE  \\\n",
       "0                             36                0                 0.0   35   \n",
       "1                             15                0                 0.0   47   \n",
       "2                              4                0                 0.0   59   \n",
       "3                             36                0                 0.0   54   \n",
       "4                              2                0                 0.0   50   \n",
       "...                          ...              ...                 ...  ...   \n",
       "29978                         36                0                 0.0   39   \n",
       "29979                         16                0                 0.0   43   \n",
       "29980                         36                0                 0.0   46   \n",
       "29981                          0                0                 NaN   38   \n",
       "29982                         36                0                 0.0   48   \n",
       "\n",
       "      OCCUPATION_TYPE  GENDER  COUNT_PAID  COUNT_LATE  COUNT_MORE_THAN_90  \\\n",
       "0                 SEP    Male        17.0         0.0                 0.0   \n",
       "1                 NaN    Male        18.0         0.0                 0.0   \n",
       "2                SENP  Female        18.0         0.0                 0.0   \n",
       "3                SENP    Male        17.0         0.0                 0.0   \n",
       "4                SENP    Male        18.0         0.0                 0.0   \n",
       "...               ...     ...         ...         ...                 ...   \n",
       "29978        SALARIED    Male        17.0         0.0                 0.0   \n",
       "29979             NaN  Female        17.0         0.0                 0.0   \n",
       "29980            SENP    Male        17.0         0.0                 0.0   \n",
       "29981        SALARIED  Female        17.0         1.0                 0.0   \n",
       "29982        SALARIED    Male        17.0         0.0                 0.0   \n",
       "\n",
       "       COUNT_0_TO_60  AVG_DAYS  COUNT_MISSING_DAYS  \n",
       "0                0.0       0.0                 1.0  \n",
       "1                0.0       0.0                 0.0  \n",
       "2                0.0       0.0                 0.0  \n",
       "3                0.0       0.0                 1.0  \n",
       "4                0.0       0.0                 0.0  \n",
       "...              ...       ...                 ...  \n",
       "29978            0.0       0.0                 1.0  \n",
       "29979            0.0       0.0                 1.0  \n",
       "29980            0.0       0.0                 1.0  \n",
       "29981            1.0      21.0                 0.0  \n",
       "29982            0.0       0.0                 1.0  \n",
       "\n",
       "[29983 rows x 27 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new.to_csv('wrangled_data_main_test.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}