0.Using_EMI_formula.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### In this notebook I have tried to calculate the ROI(Rate of intrest) using the EMI formula \n",
    "### Estimations and guess made for missing values are following:\n",
    "#### 1. *ACTUAL_PAYMT_AMT=EMI_AMOUNT*\n",
    "#### 2. *Assigned random values to repayment tenure between a range using domain specicifc knowledge*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df=pd.read_csv('train_main_loan.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna(subset=['ACTUAL_PAYMT_AMT'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['EMI_AMOUNT'].fillna(df['ACTUAL_PAYMT_AMT'], inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['PRINCIPAL'] = df['CURRENT_BALANCE'] + df['ACTUAL_PAYMT_AMT']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "# Assuming your DataFrame is named 'df'\n",
    "\n",
    "# Define functions to generate random values within the specified ranges\n",
    "def generate_random_personal_loan():\n",
    "    return np.random.randint(45,46)\n",
    "\n",
    "def generate_random_business_loan():\n",
    "    return np.random.randint(48,50)\n",
    "\n",
    "def generate_random_housing_loan():\n",
    "    return np.random.randint(175,176)\n",
    "\n",
    "def generate_random_property_loan():\n",
    "    return np.random.randint(125, 126)\n",
    "\n",
    "# Create a mapping dictionary for each 'ACCOUNT_TYPE' to the respective generator function\n",
    "repayment_tenure_mapping = {\n",
    "    'Personal Loan': generate_random_personal_loan,\n",
    "    'Business Loan': generate_random_business_loan,\n",
    "    'Housing Loan': generate_random_housing_loan,\n",
    "    'Property Loan': generate_random_property_loan\n",
    "}\n",
    "\n",
    "# Fill missing values in 'REPAYMENT_TENURE' based on 'ACCOUNT_TYPE'\n",
    "for account_type, generator_function in repayment_tenure_mapping.items():\n",
    "    mask = (df['ACCOUNT_TYPE'] == account_type) & (df['REPAYMENT_TENURE'].isna())\n",
    "    df.loc[mask, 'REPAYMENT_TENURE'] = df.loc[mask].apply(lambda x: generator_function(), axis=1)\n",
    "\n",
    "# Now, 'df' contains filled values in 'REPAYMENT_TENURE' based on 'ACCOUNT_TYPE'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df = df[['ID','PRINCIPAL', 'EMI_AMOUNT', 'REPAYMENT_TENURE', 'ACTUAL_ROI']]\n",
    "new_df.to_csv('domain_knowledge.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_csv('domain_knowledge.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Unnamed: 0</th>\n",
       "      <th>ID</th>\n",
       "      <th>PRINCIPAL</th>\n",
       "      <th>EMI_AMOUNT</th>\n",
       "      <th>REPAYMENT_TENURE</th>\n",
       "      <th>ACTUAL_ROI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>A001511563</td>\n",
       "      <td>175780.0</td>\n",
       "      <td>6106.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>7.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>A000762578</td>\n",
       "      <td>242866.0</td>\n",
       "      <td>11445.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>7.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>A000283400</td>\n",
       "      <td>138373.0</td>\n",
       "      <td>6600.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>7.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>A002246620</td>\n",
       "      <td>187630.0</td>\n",
       "      <td>1419.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>7.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>A002386531</td>\n",
       "      <td>241052.0</td>\n",
       "      <td>14212.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>7.75</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>65736</th>\n",
       "      <td>69953</td>\n",
       "      <td>A000716290</td>\n",
       "      <td>883709.0</td>\n",
       "      <td>16000.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>20.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65737</th>\n",
       "      <td>69954</td>\n",
       "      <td>A000113866</td>\n",
       "      <td>126446.0</td>\n",
       "      <td>11617.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>32.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65738</th>\n",
       "      <td>69955</td>\n",
       "      <td>A000690527</td>\n",
       "      <td>214220.0</td>\n",
       "      <td>24741.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>32.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65739</th>\n",
       "      <td>69956</td>\n",
       "      <td>A000143419</td>\n",
       "      <td>164027.0</td>\n",
       "      <td>4171.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>20.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65740</th>\n",
       "      <td>69957</td>\n",
       "      <td>A001397760</td>\n",
       "      <td>68588.0</td>\n",
       "      <td>5419.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>20.67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65741 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0          ID  PRINCIPAL  EMI_AMOUNT  REPAYMENT_TENURE  \\\n",
       "0               0  A001511563   175780.0      6106.0              48.0   \n",
       "1               1  A000762578   242866.0     11445.0              39.0   \n",
       "2               2  A000283400   138373.0      6600.0              38.0   \n",
       "3               3  A002246620   187630.0      1419.0              41.0   \n",
       "4               4  A002386531   241052.0     14212.0              34.0   \n",
       "...           ...         ...        ...         ...               ...   \n",
       "65736       69953  A000716290   883709.0     16000.0             152.0   \n",
       "65737       69954  A000113866   126446.0     11617.0              49.0   \n",
       "65738       69955  A000690527   214220.0     24741.0              48.0   \n",
       "65739       69956  A000143419   164027.0      4171.0              79.0   \n",
       "65740       69957  A001397760    68588.0      5419.0              36.0   \n",
       "\n",
       "       ACTUAL_ROI  \n",
       "0            7.50  \n",
       "1            7.50  \n",
       "2            7.50  \n",
       "3            7.50  \n",
       "4            7.75  \n",
       "...           ...  \n",
       "65736       20.92  \n",
       "65737       32.00  \n",
       "65738       32.00  \n",
       "65739       20.67  \n",
       "65740       20.67  \n",
       "\n",
       "[65741 rows x 6 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.metrics import r2_score\n",
    "\n",
    "# Assuming your DataFrame is named 'df'\n",
    "df = df[~df['CALCULATED_ROI'].astype(str).str.contains('#NUM!')]\n",
    "\n",
    "# Convert 'ACTUAL_ROI' and 'CALCULATED_ROI' columns to numeric, handling errors with 'coerce'\n",
    "df['CALCULATED_ROI'] = pd.to_numeric(df['CALCULATED_ROI'], errors='coerce')\n",
    "\n",
    "# Drop rows with NaN values in the 'CALCULATED_ROI' column\n",
    "df.dropna(subset=['CALCULATED_ROI'], inplace=True)\n",
    "\n",
    "# Filter rows based on conditions\n",
    "df = df[(df['ACTUAL_ROI'] >= 0) & (df['CALCULATED_ROI'] >= 0) & (df['CALCULATED_ROI'] <= 0.50)]\n",
    "\n",
    "# Drop rows with the string '#NUM!' in 'ACTUAL_ROI' and 'CALCULATED_ROI'\n",
    "\n",
    "# Calculate R2 between 'ACTUAL_ROI' and 'CALCULATED_ROI'\n",
    "actual_roi = df['ACTUAL_ROI']/100\n",
    "calculated_roi = df['CALCULATED_ROI']\n",
    "r2_value = r2_score(actual_roi, calculated_roi)\n",
    "\n",
    "# Now, 'df' contains the filtered rows, and you have the R2 value\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>PRINCIPAL</th>\n",
       "      <th>EMI_AMOUNT</th>\n",
       "      <th>REPAYMENT_TENURE</th>\n",
       "      <th>ACTUAL_ROI</th>\n",
       "      <th>CALCULATED_ROI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002338349</td>\n",
       "      <td>649369</td>\n",
       "      <td>8627</td>\n",
       "      <td>142</td>\n",
       "      <td>12.32</td>\n",
       "      <td>0.121122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002000537</td>\n",
       "      <td>949946</td>\n",
       "      <td>12210</td>\n",
       "      <td>156</td>\n",
       "      <td>12.42</td>\n",
       "      <td>0.122687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A002421579</td>\n",
       "      <td>1312885</td>\n",
       "      <td>16036</td>\n",
       "      <td>148</td>\n",
       "      <td>10.87</td>\n",
       "      <td>0.107336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A002152345</td>\n",
       "      <td>1751252</td>\n",
       "      <td>26996</td>\n",
       "      <td>181</td>\n",
       "      <td>10.00</td>\n",
       "      <td>0.170616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A001952834</td>\n",
       "      <td>1816068</td>\n",
       "      <td>25131</td>\n",
       "      <td>116</td>\n",
       "      <td>11.97</td>\n",
       "      <td>0.106440</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>65736</th>\n",
       "      <td>A000195582</td>\n",
       "      <td>2412199</td>\n",
       "      <td>30762</td>\n",
       "      <td>162</td>\n",
       "      <td>12.97</td>\n",
       "      <td>0.124150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65737</th>\n",
       "      <td>A001735798</td>\n",
       "      <td>820337</td>\n",
       "      <td>29851</td>\n",
       "      <td>48</td>\n",
       "      <td>18.00</td>\n",
       "      <td>0.306563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65738</th>\n",
       "      <td>A002435973</td>\n",
       "      <td>291494</td>\n",
       "      <td>10388</td>\n",
       "      <td>48</td>\n",
       "      <td>21.00</td>\n",
       "      <td>0.293641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65739</th>\n",
       "      <td>A000027755</td>\n",
       "      <td>1271974</td>\n",
       "      <td>16739</td>\n",
       "      <td>143</td>\n",
       "      <td>12.07</td>\n",
       "      <td>0.119741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65740</th>\n",
       "      <td>A000500401</td>\n",
       "      <td>360095</td>\n",
       "      <td>2878</td>\n",
       "      <td>180</td>\n",
       "      <td>12.85</td>\n",
       "      <td>0.051615</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>47381 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               ID  PRINCIPAL  EMI_AMOUNT  REPAYMENT_TENURE  ACTUAL_ROI  \\\n",
       "0      A002338349     649369        8627               142       12.32   \n",
       "1      A002000537     949946       12210               156       12.42   \n",
       "2      A002421579    1312885       16036               148       10.87   \n",
       "3      A002152345    1751252       26996               181       10.00   \n",
       "4      A001952834    1816068       25131               116       11.97   \n",
       "...           ...        ...         ...               ...         ...   \n",
       "65736  A000195582    2412199       30762               162       12.97   \n",
       "65737  A001735798     820337       29851                48       18.00   \n",
       "65738  A002435973     291494       10388                48       21.00   \n",
       "65739  A000027755    1271974       16739               143       12.07   \n",
       "65740  A000500401     360095        2878               180       12.85   \n",
       "\n",
       "       CALCULATED_ROI  \n",
       "0            0.121122  \n",
       "1            0.122687  \n",
       "2            0.107336  \n",
       "3            0.170616  \n",
       "4            0.106440  \n",
       "...               ...  \n",
       "65736        0.124150  \n",
       "65737        0.306563  \n",
       "65738        0.293641  \n",
       "65739        0.119741  \n",
       "65740        0.051615  \n",
       "\n",
       "[47381 rows x 6 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-3.2406001018032553\n"
     ]
    }
   ],
   "source": [
    "print(r2_value)"
   ]
  },
  {
   "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
}