{ "cells": [ { "cell_type": "markdown", "id": "3cf958e5", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Import libraries & files" ] }, { "cell_type": "code", "execution_count": 117, "id": "cb60ab0b", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "import pandas as pd\n", "import warnings, ast, os, fnmatch, duckdb, swifter\n", "from datetime import datetime\n", "from dateutil.relativedelta import relativedelta\n", "import numpy as np\n", "from pandas.tseries.offsets import MonthEnd\n", "from joblib import Parallel, delayed\n", "\n", "warnings.filterwarnings('ignore')\n", "pd.set_option('display.max_columns', None)\n", "pd.set_option('display.max_rows', None)" ] }, { "cell_type": "code", "execution_count": null, "id": "dada74b0", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "output_folder = './script/merchant_risk/output'\n", "for file in os.listdir(output_folder):\n", " if fnmatch.fnmatch(file, 'merchant_risk_after_adj*'):\n", " file_path = os.path.join(output_folder, file)" ] }, { "cell_type": "code", "execution_count": 119, "id": "8d70db03", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "009e5414cc12491c8ed19d2a31ccfa8b", "version_major": 2, "version_minor": 0 }, "text/plain": [ "Pandas Apply: 0%| | 0/65785 [00:00 start_date)][cols]\n", "\n", "# Convert and sort 'emi_date_wise_amount_list'\n", "df_master['emi_date_wise_amount_list'] = df_master['emi_date_wise_amount_list'].swifter.apply(\n", " lambda x: dict(sorted(ast.literal_eval(x).items(), key=lambda item: datetime.strptime(item[0], '%d-%m-%Y')))\n", ")" ] }, { "cell_type": "markdown", "id": "d29f7588", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### POS & DPD Calculation" ] }, { "cell_type": "code", "execution_count": 120, "id": "742affeb", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "base_df = df_master[~df_master['application_status'].isin(['closed','cancelled','settled'])].reset_index(drop=True)" ] }, { "cell_type": "code", "execution_count": 121, "id": "6e3c5a76", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "def vintage_calculation(row):\n", " application_id = row['application_id']\n", " loan_principal = row['loan_principal']\n", " irr_rate = row['irr_rate'] / 100\n", " total_amount_paid = row['loan_principal'] if (\n", " row['lender_name'] in ['Singularity Creditworld Private Limited', 'Vivriti Capital Limited', 'CAPRI GLOBAL CAPITAL LIMITED']\n", " or \n", " row['merchant_name'] in [\"Pristyn Care (GHV)\", \"PRISTYN CARE - REIMBURSEMENT INSURANCE\", \"Klub Works Private Limited\",\n", " \"Hexa Health (Insurance Reimbursement)\", \"EasyAspataal (Insurance Reimbursement)\",\n", " \"Ayu Health (Insurance Reimbursement)\", \"PRISTYN CARE - BNPL\", \"EasyAspataal - BNPL\",\n", " \"CLAIMBUDDY (Insurance Reimbursement)\"]\n", " ) else row['amount_paid_for_emi']\n", " emi_date_amount_list = row['emi_date_wise_amount_list'] # List of varying EMI dates & amount\n", " first_emi_date = row['first_emi_date']\n", " first_unpaid_emi_date = row['first_unpaid_emi_date'] if row['lender_name'] not in ['Singularity Creditworld Private Limited',\n", " 'Vivriti Capital Limited','CAPRI GLOBAL CAPITAL LIMITED'] or row['merchant_name'] not in [\"Pristyn Care (GHV)\",\n", " \"PRISTYN CARE - REIMBURSEMENT INSURANCE\",\"Klub Works Private Limited\",\"Hexa Health (Insurance Reimbursement)\",\n", " \"EasyAspataal (Insurance Reimbursement)\",\"Ayu Health (Insurance Reimbursement)\",\"PRISTYN CARE - BNPL\",\"EasyAspataal - BNPL\",\n", " \"CLAIMBUDDY (Insurance Reimbursement)\"] else pd.NaT\n", " adjusted_amount = row['amt_to_be_adjusted']\n", " remaining_emi = len(list(emi_date_amount_list.keys()))\n", " amortization_table = []\n", " \n", " # Initialize values\n", " beginning_balance = loan_principal\n", " \n", " for period in range(1, 24 + 1): # kept 24 to create 24 mob can extend to remaining_emi\n", " if period <= len(emi_date_amount_list):\n", " emi_date, emi_amount = list(emi_date_amount_list.items())[period - 1]\n", " emi_amount = int(emi_amount)\n", " emi_date = datetime.strptime(emi_date, '%d-%m-%Y').date()\n", " else:\n", " emi_date = emi_date + relativedelta(months=1)\n", " emi_amount = int(emi_amount)\n", "\n", " # ---- Interest Calculation ----\n", " interest_component = emi_amount if (beginning_balance * irr_rate) > emi_amount else (beginning_balance * irr_rate)\n", " interest_paid = round(min(total_amount_paid, interest_component),0) \n", " total_amount_paid = total_amount_paid - interest_paid # Subract interest component from total_amount\n", " \n", " # ---- Principal Calculation ----\n", " principal_component = max(emi_amount - interest_component, 0)\n", " principal_available = total_amount_paid + adjusted_amount # consider amt_to_be_adjusted here\n", " principal_paid = round(min(principal_available, principal_component), 0)\n", " \n", " # Deduct principal from total_amount_paid first, then from adjusted_amount if needed\n", " if principal_paid <= total_amount_paid:\n", " total_amount_paid -= principal_paid\n", " else:\n", " adjusted_draw = principal_paid - total_amount_paid\n", " total_amount_paid = 0\n", " adjusted_amount -= adjusted_draw\n", "\n", " # Calculate the ending balance\n", " ending_balance = beginning_balance - principal_paid\n", " beginning_balance = ending_balance\n", "\n", " amortization_table.append({\n", " 'application_id': application_id,\n", " 'installment number': period,\n", " 'emi_date': pd.to_datetime(emi_date),\n", " 'beginning_balance': round(beginning_balance + principal_component),\n", " 'emi_amount_expected': emi_amount,\n", " 'emi_amount_received' : interest_paid + principal_paid,\n", " 'pos': beginning_balance,\n", " 'first_emi_date': first_emi_date,\n", " 'first_unpaid_emi_date': pd.to_datetime(first_unpaid_emi_date)\n", " })\n", " \n", " # Stop if balance is fully paid\n", " if beginning_balance <= 0:\n", " break\n", " \n", " # Convert amortization table into a DataFrame\n", " df_vintage = pd.DataFrame(amortization_table)\n", " \n", " # Return results as DataFrame\n", " return df_vintage" ] }, { "cell_type": "code", "execution_count": 122, "id": "46543ee8", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# # Run vintage_calculation in parallel on each row\n", "# result_list = Parallel(n_jobs=-1)( # n_jobs=-1 uses all available CPU cores\n", "# delayed(vintage_calculation)(row)\n", "# for _, row in base_df.iterrows()\n", "# )\n", "\n", "# # Combine all resulting DataFrames into one\n", "# rps_df = pd.concat(result_list, ignore_index=True)" ] }, { "cell_type": "code", "execution_count": 123, "id": "9eda71ec", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "d28c2c773fc14c5fa05d2e5046d0df70", "version_major": 2, "version_minor": 0 }, "text/plain": [ "Pandas Apply: 0%| | 0/18436 [00:00\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
emi_month_year
712025-01-31
722025-02-28
732025-03-31
742025-04-30
752025-05-31
\n", "" ], "text/plain": [ " emi_month_year\n", "71 2025-01-31\n", "72 2025-02-28\n", "73 2025-03-31\n", "74 2025-04-30\n", "75 2025-05-31" ] }, "execution_count": 127, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Create EMI month-end dates DataFrame up to analysis_date\n", "\n", "emi_dates_df = pd.DataFrame({\n", " 'emi_month_year': pd.date_range(\n", " app_dpd_pos_df['emi_month_year'].min(),\n", " min(app_dpd_pos_df['emi_month_year'].max(), analysis_date.date()),\n", " freq='M'\n", " ).date\n", "})\n", "\n", "emi_dates_df.tail()" ] }, { "cell_type": "markdown", "id": "b9f6eaff", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Creating month wise Static pool" ] }, { "cell_type": "code", "execution_count": 128, "id": "0f7edad0", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Vintage Curves function\n", "\n", "# get max column count\n", "column_count = sum(i.startswith('dpd_') for i in app_dpd_pos_df.columns)\n", "\n", "\n", "def vintage_curves(column_count, app_dpd_pos_df,emi_dates_df,net_disb_df,dpd_key):\n", " app_dpd_pos_df = app_dpd_pos_df\n", " emi_dates_df = emi_dates_df\n", " net_disb_df = net_disb_df\n", "\n", " # generate SQL case statements for all n columns\n", " case_statements_1 = [f\"sum(case when a2.dpd_{i} > {dpd_key} then a2.pos_{i} else null end) emi_{i}\" for i in range(1, column_count + 1)]\n", "\n", "\n", " # combine all the CASE statements into the final SELECT query\n", " select_clause_1 = \",\\n\".join([statement.strip() for statement in case_statements_1])\n", "\n", "\n", " query_1 = f\"\"\"\n", " select a1.emi_month_year,{select_clause_1}\n", " from emi_dates_df a1\n", " left join app_dpd_pos_df a2 ON a2.emi_month_year = a1.emi_month_year\n", " group by a1.emi_month_year\n", " order by a1.emi_month_year\n", " \"\"\"\n", "\n", " vintage_df = duckdb.query(query_1).df().fillna(0).sort_values(by=\"emi_month_year\").reset_index(drop=True)\n", "\n", " vintage_df['emi_month_year'] = pd.to_datetime(vintage_df['emi_month_year'])\n", " net_disb_df['emi_month_year'] = pd.to_datetime(net_disb_df['emi_month_year'])\n", " # Join overall net disbursement with static pool\n", " vintage_df = pd.merge(vintage_df,net_disb_df,how='left',on='emi_month_year').fillna(0)\n", "\n", "\n", "\n", " # Create pos/net_disbursement percentage vintage curves\n", " # generate SQL case statements for all n columns\n", " case_statements_2 = [f\"round(emi_{i}/net_disbursement_amount * 100.00,2) emi_{i}\" for i in range(1, column_count + 1)]\n", "\n", "\n", " # combine all the CASE statements into the final SELECT query\n", " select_clause_2 = \",\\n\".join([statement.strip() for statement in case_statements_2])\n", "\n", "\n", " # Final SQL query with dynamic CASE statements\n", " query_2 = f\"\"\"\n", " select emi_month_year,{select_clause_2}\n", " from vintage_df\n", " \"\"\"\n", "\n", " vintage_df_percent = duckdb.query(query_2).df().fillna(0).sort_values(by=\"emi_month_year\").reset_index(drop=True)\n", " vintage_df_percent['emi_month_year'] = pd.to_datetime(vintage_df_percent['emi_month_year']).dt.date\n", " # totals = vintage_df_percent.mean(numeric_only=True).round(2)\n", " # totals_row = pd.Series(['Average'] + totals.tolist(), index=vintage_df_percent.columns)\n", " # vintage_df_percent = vintage_df_percent.append(totals_row, ignore_index=True)\n", " # vintage_df_percent = pd.concat([vintage_df_percent, totals_row.to_frame().T],ignore_index=True)\n", "\n", "\n", " # add % symbol to percent df\n", " emi_columns = vintage_df_percent.columns.difference(['emi_month_year'])\n", " #vintage_df_percent[emi_columns] = vintage_df_percent[emi_columns].astype(str).applymap(lambda x: f\"{x}%\")\n", "\n", " return vintage_df,vintage_df_percent" ] }, { "cell_type": "markdown", "id": "6e7bafe3", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Overall" ] }, { "cell_type": "code", "execution_count": 129, "id": "7bec4ccd", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "core_net_disb = df_master[(df_master['first_emi_date'] <= analysis_date)][['first_emi_date','net_disbursement_amount']].copy()\n", "df_master['first_emi_date'] = pd.to_datetime(df_master['first_emi_date'], errors='coerce')\n", "core_net_disb['emi_month_year'] = (df_master['first_emi_date'] + MonthEnd(0)).dt.date\n", "core_net_disb = core_net_disb.groupby('emi_month_year').agg({'net_disbursement_amount':'sum'}).reset_index()" ] }, { "cell_type": "code", "execution_count": null, "id": "5ce57d45", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "fintech_df = pd.read_excel('./script/merchant_risk/input/fintech_sheet.xlsx',sheet_name=1)\n", "fintech_df['disbursement_date'] = pd.to_datetime(fintech_df['disbursement_date']).dt.date\n", "fintech_df['Total'] = fintech_df.select_dtypes(include='number').sum(axis=1)\n", "fintech_df_scf = fintech_df[['disbursement_date','Solar Channel Financing']].copy().rename(columns={'Solar Channel Financing':'Total'})\n", "fintech_df = fintech_df[['disbursement_date','Total']]" ] }, { "cell_type": "code", "execution_count": 131, "id": "fd844132", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "overall_net_disb = pd.merge(core_net_disb,fintech_df,left_on='emi_month_year',right_on='disbursement_date',how='left').fillna(0)\n", "overall_net_disb['net_disbursement_amount'] = overall_net_disb['net_disbursement_amount'] + overall_net_disb['Total']\n", "overall_net_disb.drop(columns=['disbursement_date','Total'],axis=1,inplace=True)" ] }, { "cell_type": "code", "execution_count": 132, "id": "727a0921", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "vintage_df_90_overall, vintage_df_90_percent_overall = vintage_curves(column_count, app_dpd_pos_df, emi_dates_df, overall_net_disb,90)\n", "vintage_df_60_overall, vintage_df_60_percent_overall = vintage_curves(column_count, app_dpd_pos_df, emi_dates_df, overall_net_disb,60)\n", "vintage_df_30_overall, vintage_df_30_percent_overall = vintage_curves(column_count, app_dpd_pos_df, emi_dates_df, overall_net_disb,30)" ] }, { "cell_type": "markdown", "id": "b5169f42", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Core" ] }, { "cell_type": "code", "execution_count": 133, "id": "aef705d3", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "app_dpd_pos_df_core = app_dpd_pos_df[~app_dpd_pos_df['merchant_category'].isin(['Revenue Based Financing','Personal Loan'])]" ] }, { "cell_type": "code", "execution_count": 134, "id": "de30de17", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "vintage_df_90_core, vintage_df_90_percent_core = vintage_curves(column_count, app_dpd_pos_df_core, emi_dates_df, core_net_disb,90)\n", "vintage_df_60_core, vintage_df_60_percent_core = vintage_curves(column_count, app_dpd_pos_df_core, emi_dates_df, core_net_disb,60)\n", "vintage_df_30_core, vintage_df_30_percent_core = vintage_curves(column_count, app_dpd_pos_df_core, emi_dates_df, core_net_disb,30)" ] }, { "cell_type": "markdown", "id": "048074c5", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Solar + SCF" ] }, { "cell_type": "code", "execution_count": 135, "id": "f90bdb32", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "solar_core_net_disb = df_master[(df_master['first_emi_date'] <= analysis_date) & (df_master['merchant_category'] == 'Solar and Rooftop Solutions')][['first_emi_date','net_disbursement_amount']].copy()\n", "solar_core_net_disb['emi_month_year'] = (solar_core_net_disb['first_emi_date'] + MonthEnd(0)).dt.date\n", "solar_core_net_disb = solar_core_net_disb.groupby('emi_month_year').agg({'net_disbursement_amount':'sum'}).reset_index()" ] }, { "cell_type": "code", "execution_count": 136, "id": "b9cb5edb", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "solar_core_net_disb = pd.merge(solar_core_net_disb,fintech_df_scf,left_on='emi_month_year',right_on='disbursement_date',how='left').fillna(0)\n", "solar_core_net_disb['net_disbursement_amount'] = solar_core_net_disb['net_disbursement_amount'] + solar_core_net_disb['Total']\n", "solar_core_net_disb.drop(columns=['disbursement_date','Total'],axis=1,inplace=True)" ] }, { "cell_type": "code", "execution_count": 137, "id": "035b59ef", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "app_dpd_pos_df_solar = app_dpd_pos_df[app_dpd_pos_df['merchant_category'].isin(['Solar and Rooftop Solutions'])]" ] }, { "cell_type": "code", "execution_count": 138, "id": "26225b8a", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "vintage_df_90_solar_scf, vintage_df_90_percent_solar_scf = vintage_curves(column_count, app_dpd_pos_df_solar, emi_dates_df, solar_core_net_disb,90)\n", "vintage_df_60_solar_scf, vintage_df_60_percent_solar_scf = vintage_curves(column_count, app_dpd_pos_df_solar, emi_dates_df, solar_core_net_disb,60)\n", "vintage_df_30_solar_scf, vintage_df_30_percent_solar_scf = vintage_curves(column_count, app_dpd_pos_df_solar, emi_dates_df, solar_core_net_disb,30)" ] }, { "cell_type": "markdown", "id": "42f05321", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Residential" ] }, { "cell_type": "code", "execution_count": 139, "id": "81d36d01", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "residential_core_net_disb = df_master[(df_master['first_emi_date'] <= analysis_date) & \n", " (df_master['merchant_category'] == 'Solar and Rooftop Solutions') & \n", " (df_master['revised_type_of_customer'] == 'Residential')][['first_emi_date','net_disbursement_amount']].copy()\n", "residential_core_net_disb['emi_month_year'] = (residential_core_net_disb['first_emi_date'] + MonthEnd(0)).dt.date\n", "residential_core_net_disb = residential_core_net_disb.groupby('emi_month_year').agg({'net_disbursement_amount':'sum'}).reset_index()" ] }, { "cell_type": "code", "execution_count": 140, "id": "c4185bfc", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "app_dpd_pos_df_residential = app_dpd_pos_df[(app_dpd_pos_df['merchant_category'] == 'Solar and Rooftop Solutions') & (app_dpd_pos_df['revised_type_of_customer'] == 'Residential')]" ] }, { "cell_type": "code", "execution_count": 141, "id": "bd2b555d", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "vintage_df_90_residential, vintage_df_90_percent_residential = vintage_curves(column_count, app_dpd_pos_df_residential, emi_dates_df, residential_core_net_disb,90)\n", "vintage_df_60_residential, vintage_df_60_percent_residential = vintage_curves(column_count, app_dpd_pos_df_residential, emi_dates_df, residential_core_net_disb,60)\n", "vintage_df_30_residential, vintage_df_30_percent_residential = vintage_curves(column_count, app_dpd_pos_df_residential, emi_dates_df, residential_core_net_disb,30)" ] }, { "cell_type": "markdown", "id": "9e1cbf3c", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Rural" ] }, { "cell_type": "code", "execution_count": 142, "id": "0dd0efe1", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "rural_core_net_disb = df_master[(df_master['first_emi_date'] <= analysis_date) & \n", " (df_master['merchant_category'] == 'Solar and Rooftop Solutions') & \n", " (df_master['revised_type_of_customer'] == 'Rural C&I MSME(Attachakki, Oil Expeller etc.)')][['first_emi_date','net_disbursement_amount']].copy()\n", "rural_core_net_disb['emi_month_year'] = (rural_core_net_disb['first_emi_date'] + MonthEnd(0)).dt.date\n", "rural_core_net_disb = rural_core_net_disb.groupby('emi_month_year').agg({'net_disbursement_amount':'sum'}).reset_index()" ] }, { "cell_type": "code", "execution_count": 143, "id": "f99eabe3", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "app_dpd_pos_df_rural = app_dpd_pos_df[(app_dpd_pos_df['merchant_category'] == 'Solar and Rooftop Solutions') & (app_dpd_pos_df['revised_type_of_customer'] == 'Rural C&I MSME(Attachakki, Oil Expeller etc.)')]" ] }, { "cell_type": "code", "execution_count": 144, "id": "2afb9a65", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "vintage_df_90_rural, vintage_df_90_percent_rural = vintage_curves(column_count, app_dpd_pos_df_rural, emi_dates_df, rural_core_net_disb,90)\n", "vintage_df_60_rural, vintage_df_60_percent_rural = vintage_curves(column_count, app_dpd_pos_df_rural, emi_dates_df, rural_core_net_disb,60)\n", "vintage_df_30_rural, vintage_df_30_percent_rural = vintage_curves(column_count, app_dpd_pos_df_rural, emi_dates_df, rural_core_net_disb,30)" ] }, { "cell_type": "markdown", "id": "455bed55", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### MSME + SCF" ] }, { "cell_type": "code", "execution_count": 145, "id": "714d9e95", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "msme_core_net_disb = df_master[(df_master['first_emi_date'] <= analysis_date) & \n", " (df_master['merchant_category'] == 'Solar and Rooftop Solutions') & \n", " (df_master['revised_type_of_customer'] == 'Urban C&I MSME(Factories, Hospitals, Hotels, Offices, etc.)')][['first_emi_date','net_disbursement_amount']].copy()\n", "msme_core_net_disb['emi_month_year'] = (msme_core_net_disb['first_emi_date'] + MonthEnd(0)).dt.date\n", "msme_core_net_disb = msme_core_net_disb.groupby('emi_month_year').agg({'net_disbursement_amount':'sum'}).reset_index()" ] }, { "cell_type": "code", "execution_count": 146, "id": "ce271f67", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "msme_core_net_disb = pd.merge(msme_core_net_disb,fintech_df_scf,left_on='emi_month_year',right_on='disbursement_date',how='left').fillna(0)\n", "msme_core_net_disb['net_disbursement_amount'] = msme_core_net_disb['net_disbursement_amount'] + msme_core_net_disb['Total']\n", "msme_core_net_disb.drop(columns=['disbursement_date','Total'],axis=1,inplace=True)" ] }, { "cell_type": "code", "execution_count": 147, "id": "c4f99f78", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "app_dpd_pos_df_msme = app_dpd_pos_df[(app_dpd_pos_df['merchant_category'] == 'Solar and Rooftop Solutions') & (app_dpd_pos_df['revised_type_of_customer'] == 'Urban C&I MSME(Factories, Hospitals, Hotels, Offices, etc.)')]" ] }, { "cell_type": "code", "execution_count": 148, "id": "07beedd2", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "vintage_df_90_msme, vintage_df_90_percent_msme = vintage_curves(column_count, app_dpd_pos_df_msme, emi_dates_df, msme_core_net_disb,90)\n", "vintage_df_60_msme, vintage_df_60_percent_msme = vintage_curves(column_count, app_dpd_pos_df_msme, emi_dates_df, msme_core_net_disb,60)\n", "vintage_df_30_msme, vintage_df_30_percent_msme = vintage_curves(column_count, app_dpd_pos_df_msme, emi_dates_df, msme_core_net_disb,30)" ] }, { "cell_type": "code", "execution_count": 149, "id": "99713d69", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "def nullify_future_emis(df, analysis_date):\n", " df = df.copy() # Avoid modifying original DataFrame\n", "\n", " # Convert 'emi_month_year' to datetime\n", " df['emi_month_year'] = pd.to_datetime(df['emi_month_year'], errors='coerce')\n", " analysis_date = pd.to_datetime(analysis_date)\n", "\n", " # Compute month differences as integers (vectorized)\n", " mask_valid_dates = df['emi_month_year'].notna()\n", " month_diff = np.full(len(df), np.nan) # default as NaN\n", " month_diff[mask_valid_dates] = (\n", " analysis_date.to_period('M').ordinal - df.loc[mask_valid_dates, 'emi_month_year'].dt.to_period('M').view('int64')\n", ")\n", " df['month_diff'] = month_diff\n", "\n", " # Get EMI columns (excluding 'emi_month_year' if it's mistakenly included)\n", " emi_cols = [col for col in df.columns if col.startswith('emi_') and col != 'emi_month_year']\n", "\n", " # Create a 2D array for fast masking\n", " emi_numbers = np.array([int(col.split('_')[1]) for col in emi_cols])\n", " month_diff_array = df['month_diff'].to_numpy(dtype=float)[:, None] # Shape (rows, 1)\n", " max_emi_allowed = month_diff_array + 1 # Broadcasted shape (rows, emi_cols)\n", "\n", " # Build a mask: where emi_number > max allowed → set to NaN\n", " mask = emi_numbers > max_emi_allowed\n", "\n", " # Apply mask efficiently\n", " df.loc[:, emi_cols] = np.where(mask, np.nan, df[emi_cols])\n", "\n", " # Drop helper column\n", " df.drop(columns='month_diff', inplace=True)\n", " # df = df[:-1]\n", " average_row = df.mean(numeric_only=True).round(2)\n", " df.loc['Average'] = average_row\n", " df[emi_cols] = df[emi_cols].applymap(lambda x: f\"{x}%\" if pd.notna(x) else x)\n", "\n", " return df" ] }, { "cell_type": "code", "execution_count": 150, "id": "5297e3e2", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# vintage_df_90_percent_overall = nullify_future_emis(vintage_df_90_percent_overall,analysis_date)" ] }, { "cell_type": "code", "execution_count": 151, "id": "922f5c36", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# List of variable names as strings\n", "df_names = [\n", " 'vintage_df_90_percent_overall',\n", " 'vintage_df_60_percent_overall',\n", " 'vintage_df_30_percent_overall',\n", " 'vintage_df_90_percent_core',\n", " 'vintage_df_60_percent_core',\n", " 'vintage_df_30_percent_core',\n", " 'vintage_df_90_percent_solar_scf',\n", " 'vintage_df_60_percent_solar_scf',\n", " 'vintage_df_30_percent_solar_scf',\n", " 'vintage_df_90_percent_residential',\n", " 'vintage_df_60_percent_residential',\n", " 'vintage_df_30_percent_residential',\n", " 'vintage_df_90_percent_rural',\n", " 'vintage_df_60_percent_rural',\n", " 'vintage_df_30_percent_rural',\n", " 'vintage_df_90_percent_msme',\n", " 'vintage_df_60_percent_msme',\n", " 'vintage_df_30_percent_msme'\n", "]\n", "\n", "# Apply the function to each DataFrame\n", "for name in df_names:\n", " globals()[name] = nullify_future_emis(globals()[name], analysis_date)" ] }, { "cell_type": "markdown", "id": "fec00be1", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Export files to Excel" ] }, { "cell_type": "code", "execution_count": null, "id": "5962294e", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "analysis_month = datetime.strftime(analysis_date, '%b-%Y')\n", "file_name = f'vintage_curves_{analysis_month}.xlsx'\n", "\n", "# Create a Pandas Excel writer\n", "with pd.ExcelWriter(f'./script/merchant_risk/output/{file_name}', engine='xlsxwriter') as writer:\n", " \n", " workbook = writer.book\n", " bold_format = workbook.add_format({'bold': True})\n", " \n", " # Write DataFrame to 'Case wise pos dpd' sheet\n", " app_dpd_pos_df.to_excel(writer, sheet_name='Case wise pos dpd', index=False)\n", "\n", " # add new sheet for 90+ vintage curves\n", " worksheet_vintage = workbook.add_worksheet('static_pool_90')\n", "\n", " worksheet_vintage.write(1, column_count + 5, 'Performance Months (PAR >90+ %) - Overall Portfolio', bold_format) # write title\n", " vintage_df_90_overall.to_excel(writer, sheet_name='static_pool_90', startrow=2, index=False)\n", " vintage_df_90_percent_overall.to_excel(writer, sheet_name='static_pool_90', startrow=2, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_90_overall) + 10, column_count + 5, 'Performance Months (PAR >90+ %) - Core Portfolio', bold_format)\n", " vintage_df_90_core.to_excel(writer, sheet_name='static_pool_90', startrow = 1 + len(vintage_df_90_overall) + 10 + 1, index=False)\n", " vintage_df_90_percent_overall.to_excel(writer, sheet_name='static_pool_90', startrow = 1 + len(vintage_df_90_overall) + 10 + 1, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_90_overall) + len(vintage_df_90_core) + 20, column_count + 5, 'Performance Months (PAR >90+ %) - Solar + SCF Portfolio', bold_format)\n", " vintage_df_90_solar_scf.to_excel(writer, sheet_name='static_pool_90', startrow = 1 + len(vintage_df_90_overall) + len(vintage_df_90_core) + 20 + 1, index=False)\n", " vintage_df_90_percent_solar_scf.to_excel(writer, sheet_name='static_pool_90', startrow = 1 + len(vintage_df_90_overall) + len(vintage_df_90_core) + 20 + 1, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_90_overall) + len(vintage_df_90_core) + len(vintage_df_90_solar_scf) + 30, column_count + 5, 'Performance Months (PAR >90+ %) - Solar Residential Portfolio', bold_format)\n", " vintage_df_90_residential.to_excel(writer, sheet_name='static_pool_90', startrow = 1 + len(vintage_df_90_overall) + len(vintage_df_90_core) + len(vintage_df_90_solar_scf) + 30 + 1, index=False)\n", " vintage_df_90_percent_residential.to_excel(writer, sheet_name='static_pool_90', startrow = 1 + len(vintage_df_90_overall) + len(vintage_df_90_core) + len(vintage_df_90_solar_scf) + 30 + 1, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_90_overall) + len(vintage_df_90_core) + len(vintage_df_90_solar_scf) + len(vintage_df_90_residential) + 40, column_count + 5, 'Performance Months (PAR >90+ %) - Solar Rural Portfolio', bold_format)\n", " vintage_df_90_rural.to_excel(writer, sheet_name='static_pool_90', startrow = 1 + len(vintage_df_90_overall) + len(vintage_df_90_core) + len(vintage_df_90_solar_scf) + len(vintage_df_90_residential) + 40 + 1, index=False)\n", " vintage_df_90_percent_rural.to_excel(writer, sheet_name='static_pool_90', startrow = 1 + len(vintage_df_90_overall) + len(vintage_df_90_core) + len(vintage_df_90_solar_scf) + len(vintage_df_90_residential) + 40 + 1, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_90_overall) + len(vintage_df_90_core) + len(vintage_df_90_solar_scf) + len(vintage_df_90_residential) + len(vintage_df_90_rural) + 50, column_count + 5, 'Performance Months (PAR >90+ %) - Solar MSME + SCF Portfolio', bold_format)\n", " vintage_df_90_msme.to_excel(writer, sheet_name='static_pool_90', startrow = 1 + len(vintage_df_90_overall) + len(vintage_df_90_core) + len(vintage_df_90_solar_scf) + len(vintage_df_90_residential) + len(vintage_df_90_rural) + 50 + 1, index=False)\n", " vintage_df_90_percent_msme.to_excel(writer, sheet_name='static_pool_90', startrow = 1 + len(vintage_df_90_overall) + len(vintage_df_90_core) + len(vintage_df_90_solar_scf) + len(vintage_df_90_residential) + len(vintage_df_90_rural) + 50 + 1, startcol=column_count + 5, index=False)\n", "\n", "\n", "\n", " # add new sheet for 60+ vintage curves\n", " worksheet_vintage = workbook.add_worksheet('static_pool_60')\n", "\n", " worksheet_vintage.write(1, column_count + 5, 'Performance Months (PAR >60+ %) - Overall Portfolio', bold_format) # write title\n", " vintage_df_60_overall.to_excel(writer, sheet_name='static_pool_60', startrow=2, index=False)\n", " vintage_df_60_percent_overall.to_excel(writer, sheet_name='static_pool_60', startrow=2, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_60_overall) + 10, column_count + 5, 'Performance Months (PAR >60+ %) - Core Portfolio', bold_format)\n", " vintage_df_60_core.to_excel(writer, sheet_name='static_pool_60', startrow = 1 + len(vintage_df_60_overall) + 10 + 1, index=False)\n", " vintage_df_60_percent_overall.to_excel(writer, sheet_name='static_pool_60', startrow = 1 + len(vintage_df_60_overall) + 10 + 1, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_60_overall) + len(vintage_df_60_core) + 20, column_count + 5, 'Performance Months (PAR >60+ %) - Solar + SCF Portfolio', bold_format)\n", " vintage_df_60_solar_scf.to_excel(writer, sheet_name='static_pool_60', startrow = 1 + len(vintage_df_60_overall) + len(vintage_df_60_core) + 20 + 1, index=False)\n", " vintage_df_60_percent_solar_scf.to_excel(writer, sheet_name='static_pool_60', startrow = 1 + len(vintage_df_60_overall) + len(vintage_df_60_core) + 20 + 1, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_60_overall) + len(vintage_df_60_core) + len(vintage_df_60_solar_scf) + 30, column_count + 5, 'Performance Months (PAR >60+ %) - Solar Residential Portfolio', bold_format)\n", " vintage_df_60_residential.to_excel(writer, sheet_name='static_pool_60', startrow = 1 + len(vintage_df_60_overall) + len(vintage_df_60_core) + len(vintage_df_60_solar_scf) + 30 + 1, index=False)\n", " vintage_df_60_percent_residential.to_excel(writer, sheet_name='static_pool_60', startrow = 1 + len(vintage_df_60_overall) + len(vintage_df_60_core) + len(vintage_df_60_solar_scf) + 30 + 1, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_60_overall) + len(vintage_df_60_core) + len(vintage_df_60_solar_scf) + len(vintage_df_60_residential) + 40, column_count + 5, 'Performance Months (PAR >60+ %) - Solar Rural Portfolio', bold_format)\n", " vintage_df_60_rural.to_excel(writer, sheet_name='static_pool_60', startrow = 1 + len(vintage_df_60_overall) + len(vintage_df_60_core) + len(vintage_df_60_solar_scf) + len(vintage_df_60_residential) + 40 + 1, index=False)\n", " vintage_df_60_percent_rural.to_excel(writer, sheet_name='static_pool_60', startrow = 1 + len(vintage_df_60_overall) + len(vintage_df_60_core) + len(vintage_df_60_solar_scf) + len(vintage_df_60_residential) + 40 + 1, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_60_overall) + len(vintage_df_60_core) + len(vintage_df_60_solar_scf) + len(vintage_df_60_residential) + len(vintage_df_60_rural) + 50, column_count + 5, 'Performance Months (PAR >60+ %) - Solar MSME + SCF Portfolio', bold_format)\n", " vintage_df_60_msme.to_excel(writer, sheet_name='static_pool_60', startrow = 1 + len(vintage_df_60_overall) + len(vintage_df_60_core) + len(vintage_df_60_solar_scf) + len(vintage_df_60_residential) + len(vintage_df_60_rural) + 50 + 1, index=False)\n", " vintage_df_60_percent_msme.to_excel(writer, sheet_name='static_pool_60', startrow = 1 + len(vintage_df_60_overall) + len(vintage_df_60_core) + len(vintage_df_60_solar_scf) + len(vintage_df_60_residential) + len(vintage_df_60_rural) + 50 + 1, startcol=column_count + 5, index=False)\n", "\n", "\n", "\n", " # add new sheet for 30+ vintage curves\n", " worksheet_vintage = workbook.add_worksheet('static_pool_30')\n", "\n", " worksheet_vintage.write(1, column_count + 5, 'Performance Months (PAR >30+ %) - Overall Portfolio', bold_format) # write title\n", " vintage_df_30_overall.to_excel(writer, sheet_name='static_pool_30', startrow=2, index=False)\n", " vintage_df_30_percent_overall.to_excel(writer, sheet_name='static_pool_30', startrow=2, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_30_overall) + 10, column_count + 5, 'Performance Months (PAR >30+ %) - Core Portfolio', bold_format)\n", " vintage_df_30_core.to_excel(writer, sheet_name='static_pool_30', startrow = 1 + len(vintage_df_30_overall) + 10 + 1, index=False)\n", " vintage_df_30_percent_overall.to_excel(writer, sheet_name='static_pool_30', startrow = 1 + len(vintage_df_30_overall) + 10 + 1, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_30_overall) + len(vintage_df_30_core) + 20, column_count + 5, 'Performance Months (PAR >30+ %) - Solar + SCF Portfolio', bold_format)\n", " vintage_df_30_solar_scf.to_excel(writer, sheet_name='static_pool_30', startrow = 1 + len(vintage_df_30_overall) + len(vintage_df_30_core) + 20 + 1, index=False)\n", " vintage_df_30_percent_solar_scf.to_excel(writer, sheet_name='static_pool_30', startrow = 1 + len(vintage_df_30_overall) + len(vintage_df_30_core) + 20 + 1, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_30_overall) + len(vintage_df_30_core) + len(vintage_df_30_solar_scf) + 30, column_count + 5, 'Performance Months (PAR >30+ %) - Solar Residential Portfolio', bold_format)\n", " vintage_df_30_residential.to_excel(writer, sheet_name='static_pool_30', startrow = 1 + len(vintage_df_30_overall) + len(vintage_df_30_core) + len(vintage_df_30_solar_scf) + 30 + 1, index=False)\n", " vintage_df_30_percent_residential.to_excel(writer, sheet_name='static_pool_30', startrow = 1 + len(vintage_df_30_overall) + len(vintage_df_30_core) + len(vintage_df_30_solar_scf) + 30 + 1, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_30_overall) + len(vintage_df_30_core) + len(vintage_df_30_solar_scf) + len(vintage_df_30_residential) + 40, column_count + 5, 'Performance Months (PAR >30+ %) - Solar Rural Portfolio', bold_format)\n", " vintage_df_30_rural.to_excel(writer, sheet_name='static_pool_30', startrow = 1 + len(vintage_df_30_overall) + len(vintage_df_30_core) + len(vintage_df_30_solar_scf) + len(vintage_df_30_residential) + 40 + 1, index=False)\n", " vintage_df_30_percent_rural.to_excel(writer, sheet_name='static_pool_30', startrow = 1 + len(vintage_df_30_overall) + len(vintage_df_30_core) + len(vintage_df_30_solar_scf) + len(vintage_df_30_residential) + 40 + 1, startcol=column_count + 5, index=False)\n", "\n", " worksheet_vintage.write(1 + len(vintage_df_30_overall) + len(vintage_df_30_core) + len(vintage_df_30_solar_scf) + len(vintage_df_30_residential) + len(vintage_df_30_rural) + 50, column_count + 5, 'Performance Months (PAR >30+ %) - Solar MSME + SCF Portfolio', bold_format)\n", " vintage_df_30_msme.to_excel(writer, sheet_name='static_pool_30', startrow = 1 + len(vintage_df_30_overall) + len(vintage_df_30_core) + len(vintage_df_30_solar_scf) + len(vintage_df_30_residential) + len(vintage_df_30_rural) + 50 + 1, index=False)\n", " vintage_df_30_percent_msme.to_excel(writer, sheet_name='static_pool_30', startrow = 1 + len(vintage_df_30_overall) + len(vintage_df_30_core) + len(vintage_df_30_solar_scf) + len(vintage_df_30_residential) + len(vintage_df_30_rural) + 50 + 1, startcol=column_count + 5, index=False)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.3" } }, "nbformat": 4, "nbformat_minor": 5 }