{ "cells": [ { "cell_type": "code", "execution_count": 22, "id": "39392689-8f88-405c-a962-bd1056e33041", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "import pandas as pd\n", "import warnings, duckdb, os, fnmatch, swifter\n", "import numpy_financial as npf\n", "import numpy as np\n", "from datetime import datetime\n", "\n", "warnings.filterwarnings('ignore')\n", "pd.set_option('display.max_columns', None)\n", "pd.set_option('display.max_rows', None)\n", "pd.options.display.float_format = '{:,.2f}'.format" ] }, { "cell_type": "markdown", "id": "1c26139f", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Import base file and add new required columns" ] }, { "cell_type": "code", "execution_count": null, "id": "6b281ffa", "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": 24, "id": "b4ab2bde-168d-43f4-a57f-028f50665be2", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "df = pd.read_csv(file_path,parse_dates=['analysis_date','disbursement_date'], dayfirst=True)\n", "analysis_date = datetime.strftime(df['analysis_date'][0],'%b-%Y')\n", "portfolio_cuts_df = df[(df['merchant_name'] != 'Fundly') & (df['lender_name'] != 'K. M. Global Credit Private Limited')][['application_id','merchant_name','application_status','lender_name','current_address_state','bureau_score','employee_status','monthly_income','merchant_category','emi_amount','remaining_emi','net_disbursement_amount','hold_amount','principal_outstanding','current_month_revised_dpd','write_off_flag']]\n", "portfolio_cuts_df = portfolio_cuts_df[portfolio_cuts_df['application_status'] != 'settled']\n", "\n", "# singularity pos 80 percent\n", "portfolio_cuts_df.loc[portfolio_cuts_df['lender_name'] == 'Singularity Creditworld Private Limited', 'principal_outstanding'] = portfolio_cuts_df['principal_outstanding'] * 0.8\n", "\n", "portfolio_cuts_df['bureau_score'] = portfolio_cuts_df['bureau_score'].astype('float64')\n", "#portfolio_cuts_df['monthly_income'] = portfolio_cuts_df['monthly_income'].str.replace(',','').astype('float64').fillna(0)\n", "portfolio_cuts_df['lender_type'] = portfolio_cuts_df['lender_name'].apply(lambda x: 'On-book' if x == 'K. M. Global Credit Private Limited' else 'Off-book')\n", "# portfolio_cuts_df.head()" ] }, { "cell_type": "code", "execution_count": 25, "id": "a757b2f0-263e-4914-bc17-c341ea95a46d", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "8dd36a7e94e24b979cd166c9b5573834", "version_major": 2, "version_minor": 0 }, "text/plain": [ "Pandas Apply: 0%| | 0/18667 [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", " \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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
LenderNumber of Cases
0Aditya Birla Finance Limited603
1Mufin DA June 24137
2JM Financial Products Ltd193
3Lendbox P2P650
4HLF-PTC256
5Lendbox LSP108
6Singularity Creditworld Private Limited740
7Pride Financial Services Private Limited0
8Capri Global-DA158
9K. M. GLOBAL P2P FINANCE PRIVATE LIMITED392
10CAPRI GLOBAL CAPITAL LIMITED26
11Avanse Financial Services Ltd.738
12Vivriti Capital Limited3
\n", "" ], "text/plain": [ " Lender Number of Cases\n", "0 Aditya Birla Finance Limited 603\n", "1 Mufin DA June 24 137\n", "2 JM Financial Products Ltd 193\n", "3 Lendbox P2P 650\n", "4 HLF-PTC 256\n", "5 Lendbox LSP 108\n", "6 Singularity Creditworld Private Limited 740\n", "7 Pride Financial Services Private Limited 0\n", "8 Capri Global-DA 158\n", "9 K. M. GLOBAL P2P FINANCE PRIVATE LIMITED 392\n", "10 CAPRI GLOBAL CAPITAL LIMITED 26\n", "11 Avanse Financial Services Ltd. 738\n", "12 Vivriti Capital Limited 3" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "qry = \"\"\" \n", "select \n", " lender_name \"Lender\",\n", " count(distinct case when (application_status not in ('closed', 'cancelled', 'settled')) and current_month_revised_dpd < 365 and principal_outstanding > 0 then application_id end) \"Number of Cases\"\n", "from portfolio_cuts_df\n", "where lender_name != 'K. M. Global Credit Private Limited'\n", "group by 1;\n", "\n", "\"\"\"\n", "duckdb.query(qry).df().fillna(0).reset_index(drop=True)" ] }, { "cell_type": "markdown", "id": "6811578d", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Portfolio Cuts: Geography" ] }, { "cell_type": "code", "execution_count": null, "id": "5dbe5a93", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "fintech_df = pd.read_excel('./script/merchant_risk/input/fintech_sheet.xlsx',sheet_name=0) # read outstanding amount sheet from fintech file\n", "fintech_df = fintech_df[fintech_df['merchant_category']=='Fundly offbook'].reset_index(drop=True)\n", "# fintech_df" ] }, { "cell_type": "code", "execution_count": 28, "id": "e5aec56a", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# State Buckets\n", "other_states_list = [ 'Himachal Pradesh', 'Chhattisgarh', 'Meghalaya', 'Andaman & Nicobar Islands', 'Jammu & Kashmir', 'Tripura', \n", " 'telangana ', 'Nagaland', 'Manipur', 'Sikkim', 'Chandigarh',\n", " 'Arunachal Pradesh', 'Goa', 'Pondicherry/Puducherry', 'Mizoram',\n", " 'Daman & Diu', 'Lakshadweep', 'Dadra & Nagar Haveli', 'Odisha',\n", " 'Jammu and Kashmir', 'Uttarakhand', 'Daman and Diu']\n", "\n", "portfolio_cuts_df['state_buckets'] = portfolio_cuts_df['current_address_state'].apply(lambda x: 'Others' if (x in other_states_list or pd.isna(x)) else x)" ] }, { "cell_type": "code", "execution_count": 29, "id": "2b6e969f", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "qry = \"\"\" \n", "select \n", " state_buckets \"State\",\n", " count(distinct case when (application_status not in ('closed', 'cancelled', 'settled')) and current_month_revised_dpd < 365 and principal_outstanding > 0 then application_id end) \"Number of Loans\",\n", " sum(case when current_month_revised_dpd < 365 then principal_outstanding end) \"Total Principal Outstanding (In Crs)\",\n", " sum(case when current_month_revised_dpd = 0 then principal_outstanding end) \"Principal without overdues (In Crs)\",\n", " count(distinct case when current_month_revised_dpd < 365 and current_month_revised_dpd > 0 and principal_outstanding > 0 then application_id end) \"No of cases in arrears\",\n", " count(distinct case when current_month_revised_dpd > 364 then application_id end) \"No of loans written off\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 0 and current_month_revised_dpd <= 30) then principal_outstanding end) \"1-30\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 30 and current_month_revised_dpd <= 60) then principal_outstanding end) \"31-60\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 60 and current_month_revised_dpd <= 90) then principal_outstanding end) \"61-90\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 90 and current_month_revised_dpd <= 120) then principal_outstanding end) \"91-120\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 120 and current_month_revised_dpd <= 150) then principal_outstanding end) \"121-150\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 150 and current_month_revised_dpd <= 180) then principal_outstanding end) \"151-180\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 180 and current_month_revised_dpd <= 364) then principal_outstanding end) \"181-364\",\n", " 0 \"Greater than 364\"\n", "from portfolio_cuts_df\n", "group by 1;\n", "\n", "\"\"\"\n", "states_portfolio_cuts = duckdb.query(qry).df().fillna(0).sort_values(by=\"Number of Loans\",ascending=False).reset_index(drop=True)\n", "states_portfolio_cuts['Total PAR (In Crs)'] = states_portfolio_cuts[[\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\"]].sum(axis=1)\n", "states_portfolio_cuts = states_portfolio_cuts[[\"State\",\"Number of Loans\",\"Total Principal Outstanding (In Crs)\",\"Principal without overdues (In Crs)\",\"No of cases in arrears\",\n", " \"No of loans written off\",\"Total PAR (In Crs)\",\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\",\"Greater than 364\"]]\n", "\n", "# add fintechs count and amount in 'Other category'\n", "states_portfolio_cuts.loc[states_portfolio_cuts['State'] == 'Others', 'Number of Loans'] += fintech_df.outstanding_count.loc[0:].sum()\n", "states_portfolio_cuts.loc[states_portfolio_cuts['State'] == 'Others', 'Total Principal Outstanding (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "states_portfolio_cuts.loc[states_portfolio_cuts['State'] == 'Others', 'Principal without overdues (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "\n", "totals = states_portfolio_cuts.sum(numeric_only=True)\n", "totals_row = pd.Series(['Total'] + totals.tolist(), index=states_portfolio_cuts.columns)\n", "states_portfolio_cuts = pd.concat([states_portfolio_cuts, totals_row.to_frame().T], ignore_index=True)\n", "\n", "# states_portfolio_cuts = states_portfolio_cuts.apply(lambda x: round(x / 1e7) if not x.name.startswith('N') and x.dtype in ['float64', 'int64'] else x)\n", "states_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]] = states_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]].apply(lambda x: round(x / 1e7, 2))\n", "#states_portfolio_cuts" ] }, { "cell_type": "markdown", "id": "68e01595", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Portfolio Cuts : Tenure" ] }, { "cell_type": "code", "execution_count": 30, "id": "0248117d-2780-442d-b612-d80632bd7f3c", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Tenure buckets\n", "tenure_buckets = [-1, 6, 12, 24, 36, 60]\n", "tenure_labels = ['1) 0 to 6 months', '2) 7 to 12 months', '3) 13 to 24 months', '4) 25 to 36 months', '5) 37 to 60 months']\n", "portfolio_cuts_df['tenure_buckets'] = pd.cut(portfolio_cuts_df['remaining_emi'], bins=tenure_buckets, labels=tenure_labels, right=True)" ] }, { "cell_type": "code", "execution_count": 31, "id": "420e6b7a", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "qry = \"\"\" \n", "select \n", " tenure_buckets \"Tenure\",\n", " count(distinct case when (application_status not in ('closed', 'cancelled', 'settled')) and current_month_revised_dpd < 365 and principal_outstanding > 0 then application_id end) \"Number of Loans\",\n", " sum(case when current_month_revised_dpd < 365 then principal_outstanding end) \"Total Principal Outstanding (In Crs)\",\n", " sum(case when current_month_revised_dpd = 0 then principal_outstanding end) \"Principal without overdues (In Crs)\",\n", " count(distinct case when current_month_revised_dpd < 365 and current_month_revised_dpd > 0 and principal_outstanding > 0 then application_id end) \"No of cases in arrears\",\n", " count(distinct case when current_month_revised_dpd > 364 then application_id end) \"No of loans written off\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 0 and current_month_revised_dpd <= 30) then principal_outstanding end) \"1-30\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 30 and current_month_revised_dpd <= 60) then principal_outstanding end) \"31-60\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 60 and current_month_revised_dpd <= 90) then principal_outstanding end) \"61-90\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 90 and current_month_revised_dpd <= 120) then principal_outstanding end) \"91-120\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 120 and current_month_revised_dpd <= 150) then principal_outstanding end) \"121-150\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 150 and current_month_revised_dpd <= 180) then principal_outstanding end) \"151-180\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 180 and current_month_revised_dpd <= 364) then principal_outstanding end) \"181-364\",\n", " 0 \"Greater than 364\"\n", "from portfolio_cuts_df\n", "group by 1;\n", "\n", "\"\"\"\n", "tenure_portfolio_cuts = duckdb.query(qry).df().apply(lambda col: col.fillna(0) if col.dtype in ['float64', 'int64'] else col).sort_values(by=\"Tenure\").reset_index(drop=True)\n", "tenure_portfolio_cuts['Total PAR (In Crs)'] = tenure_portfolio_cuts[[\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\"]].sum(axis=1)\n", "tenure_portfolio_cuts = tenure_portfolio_cuts[[\"Tenure\",\"Number of Loans\",\"Total Principal Outstanding (In Crs)\",\"Principal without overdues (In Crs)\",\"No of cases in arrears\",\n", " \"No of loans written off\",\"Total PAR (In Crs)\",\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\",\"Greater than 364\"]]\n", "\n", "# add fintechs count and amount in '0 to 6 months category'\n", "tenure_portfolio_cuts.loc[tenure_portfolio_cuts['Tenure'] == '1) 0 to 6 months', 'Number of Loans'] += fintech_df.outstanding_count.loc[0:].sum()\n", "tenure_portfolio_cuts.loc[tenure_portfolio_cuts['Tenure'] == '1) 0 to 6 months', 'Total Principal Outstanding (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "tenure_portfolio_cuts.loc[tenure_portfolio_cuts['Tenure'] == '1) 0 to 6 months', 'Principal without overdues (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "\n", "totals = tenure_portfolio_cuts.sum(numeric_only=True)\n", "totals_row = pd.Series(['Total'] + totals.tolist(), index=tenure_portfolio_cuts.columns)\n", "tenure_portfolio_cuts = pd.concat([tenure_portfolio_cuts, totals_row.to_frame().T], ignore_index=True)\n", "\n", "tenure_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]] = tenure_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]].apply(lambda x: round(x / 1e7, 2))\n", "#tenure_portfolio_cuts" ] }, { "cell_type": "markdown", "id": "17ed77d2", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Portfolio Cuts: Ticket Size" ] }, { "cell_type": "code", "execution_count": 32, "id": "bdce575f", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "ticket_size_buckets = [0, 50000, 100000, 200000, 300000, 400000, 500000, 1000000, 2500000, 5000000, float('inf')]\n", "ticket_size_labels = ['1) ₹ 0 to 50k', '2) ₹ 50k to 100k', '3) ₹ 100k to 200k', '4) ₹ 200k to 300k', '5) ₹ 300k to 400k', \n", " '6) ₹ 400k to 500k', '7) ₹ 500k to 1000k', '8) ₹ 1000k to 2500k', '9) ₹ 2500k to 5000k', '10) Greater than ₹ 5000k']\n", "\n", "\n", "portfolio_cuts_df['ticket_size_buckets'] = pd.cut(portfolio_cuts_df['net_disbursement_amount'], bins=ticket_size_buckets, labels=ticket_size_labels, right=True)\n", "portfolio_cuts_df['ticket_size_buckets'] = portfolio_cuts_df['ticket_size_buckets'].fillna('1) ₹ 0 to 50k')" ] }, { "cell_type": "code", "execution_count": 33, "id": "644423e8", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "qry = \"\"\" \n", "select \n", " ticket_size_buckets \"Loan Ticket size\",\n", " count(distinct case when (application_status not in ('closed', 'cancelled', 'settled')) and current_month_revised_dpd < 365 and principal_outstanding > 0 then application_id end) \"Number of Loans\",\n", " sum(case when current_month_revised_dpd < 365 then principal_outstanding end) \"Total Principal Outstanding (In Crs)\",\n", " sum(case when current_month_revised_dpd = 0 then principal_outstanding end) \"Principal without overdues (In Crs)\",\n", " count(distinct case when current_month_revised_dpd < 365 and current_month_revised_dpd > 0 and principal_outstanding > 0 then application_id end) \"No of cases in arrears\",\n", " count(distinct case when current_month_revised_dpd > 364 then application_id end) \"No of loans written off\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 0 and current_month_revised_dpd <= 30) then principal_outstanding end) \"1-30\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 30 and current_month_revised_dpd <= 60) then principal_outstanding end) \"31-60\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 60 and current_month_revised_dpd <= 90) then principal_outstanding end) \"61-90\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 90 and current_month_revised_dpd <= 120) then principal_outstanding end) \"91-120\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 120 and current_month_revised_dpd <= 150) then principal_outstanding end) \"121-150\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 150 and current_month_revised_dpd <= 180) then principal_outstanding end) \"151-180\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 180 and current_month_revised_dpd <= 364) then principal_outstanding end) \"181-364\",\n", " 0 \"Greater than 364\"\n", "from portfolio_cuts_df\n", "group by 1;\n", "\n", "\"\"\"\n", "ticket_size_portfolio_cuts = duckdb.query(qry).df().apply(lambda col: col.fillna(0) if col.dtype in ['float64', 'int64'] else col).sort_values(by=\"Loan Ticket size\").reset_index(drop=True)\n", "ticket_size_portfolio_cuts['Total PAR (In Crs)'] = ticket_size_portfolio_cuts[[\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\"]].sum(axis=1)\n", "ticket_size_portfolio_cuts = ticket_size_portfolio_cuts[[\"Loan Ticket size\",\"Number of Loans\",\"Total Principal Outstanding (In Crs)\",\"Principal without overdues (In Crs)\",\"No of cases in arrears\",\n", " \"No of loans written off\",\"Total PAR (In Crs)\",\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\",\"Greater than 364\"]]\n", "\n", "# add fintechs count and amount in ' ₹ 0 to 50k category'\n", "ticket_size_portfolio_cuts.loc[ticket_size_portfolio_cuts['Loan Ticket size'] == '1) ₹ 0 to 50k', 'Number of Loans'] += fintech_df.outstanding_count.loc[0:].sum()\n", "ticket_size_portfolio_cuts.loc[ticket_size_portfolio_cuts['Loan Ticket size'] == '1) ₹ 0 to 50k', 'Total Principal Outstanding (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "ticket_size_portfolio_cuts.loc[ticket_size_portfolio_cuts['Loan Ticket size'] == '1) ₹ 0 to 50k', 'Principal without overdues (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "\n", "totals = ticket_size_portfolio_cuts.sum(numeric_only=True)\n", "totals_row = pd.Series(['Total'] + totals.tolist(), index=ticket_size_portfolio_cuts.columns)\n", "ticket_size_portfolio_cuts = pd.concat([ticket_size_portfolio_cuts, totals_row.to_frame().T], ignore_index=True)\n", "\n", "ticket_size_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]] = ticket_size_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]].apply(lambda x: round(x / 1e7, 2))\n", "#ticket_size_portfolio_cuts" ] }, { "cell_type": "markdown", "id": "9d39fd54", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Portfolio Cuts: Category" ] }, { "cell_type": "code", "execution_count": 34, "id": "c0cf7736-46b9-4126-acfb-091534d76a75", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Main category bcukets\n", "group_category_dict = {\n", " 'Solar/GE' : ['Solar and Rooftop Solutions', 'Electronic Battery', 'Electronic Vehicles'],\n", " 'Healthcare' : ['Cosmetics', 'Dental Care', 'Equipment Financing', 'IVF and Fertility Treatments', 'Insurance Premium Financing', 'Multispeciality Treatments', 'Stemcell banking and treatment'],\n", " 'Home Decor' : ['Home Downpayment', 'Modular Kitchen, wardrobes and Interior', 'Painting Services', 'Water Purifier'],\n", " 'Education' : ['Education', 'Entrance prep', 'K12 Coaching', 'K12 Edtech', 'K12 Education', 'LSP - Green Energy & Education', 'Upskilling Edtech'],\n", " 'Revenue Based Financing' : ['Revenue Based Financing'],\n", " 'PL/Others' : ['Electronic Products', 'Loose Furniture', 'Personal Loan', 'Jewelry']\n", "}\n", "\n", "def group_category(category):\n", " for group_category, category_list in group_category_dict.items():\n", " if category in category_list:\n", " return group_category\n", " return category\n", "\n", "\n", "portfolio_cuts_df['group_category'] = portfolio_cuts_df['merchant_category'].apply(group_category)" ] }, { "cell_type": "code", "execution_count": 35, "id": "b7e2d3ff", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "qry = \"\"\" \n", "select \n", " group_category \"Category\",\n", " count(distinct case when (application_status not in ('closed', 'cancelled', 'settled')) and current_month_revised_dpd < 365 and principal_outstanding > 0 then application_id end) \"Number of Loans\",\n", " sum(case when current_month_revised_dpd < 365 then principal_outstanding end) \"Total Principal Outstanding (In Crs)\",\n", " sum(case when current_month_revised_dpd = 0 then principal_outstanding end) \"Principal without overdues (In Crs)\",\n", " count(distinct case when current_month_revised_dpd < 365 and current_month_revised_dpd > 0 and principal_outstanding > 0 then application_id end) \"No of cases in arrears\",\n", " count(distinct case when current_month_revised_dpd > 364 then application_id end) \"No of loans written off\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 0 and current_month_revised_dpd <= 30) then principal_outstanding end) \"1-30\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 30 and current_month_revised_dpd <= 60) then principal_outstanding end) \"31-60\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 60 and current_month_revised_dpd <= 90) then principal_outstanding end) \"61-90\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 90 and current_month_revised_dpd <= 120) then principal_outstanding end) \"91-120\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 120 and current_month_revised_dpd <= 150) then principal_outstanding end) \"121-150\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 150 and current_month_revised_dpd <= 180) then principal_outstanding end) \"151-180\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 180 and current_month_revised_dpd <= 364) then principal_outstanding end) \"181-364\",\n", " 0 \"Greater than 364\"\n", "from portfolio_cuts_df\n", "group by 1;\n", "\n", "\"\"\"\n", "category_portfolio_cuts = duckdb.query(qry).df().fillna(0).sort_values(by='Number of Loans',ascending=False).reset_index(drop=True)\n", "category_portfolio_cuts['Total PAR (In Crs)'] = category_portfolio_cuts[[\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\"]].sum(axis=1)\n", "category_portfolio_cuts = category_portfolio_cuts[[\"Category\",\"Number of Loans\",\"Total Principal Outstanding (In Crs)\",\"Principal without overdues (In Crs)\",\"No of cases in arrears\",\n", " \"No of loans written off\",\"Total PAR (In Crs)\",\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\",\"Greater than 364\"]]\n", "\n", "# Add rbf as new row\n", "jify_row = ['Revenue Based Financing'] + fintech_df[['outstanding_count', 'principal_outstanding_amount', 'principal_outstanding_amount']].sum().values.tolist()\n", "jify_row.extend([0] * (len(category_portfolio_cuts.columns) - len(jify_row)))\n", "category_portfolio_cuts.loc[-1] = jify_row\n", "category_portfolio_cuts.reset_index(drop=True,inplace=True)\n", "\n", "totals = category_portfolio_cuts.sum(numeric_only=True)\n", "totals_row = pd.Series(['Total'] + totals.tolist(), index=category_portfolio_cuts.columns)\n", "category_portfolio_cuts = pd.concat([category_portfolio_cuts, totals_row.to_frame().T], ignore_index=True)\n", "\n", "category_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]] = category_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]].apply(lambda x: round(x / 1e7, 2))\n", "#category_portfolio_cuts" ] }, { "cell_type": "markdown", "id": "49130404", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Portfolio Cuts: Priority Sector Lending" ] }, { "cell_type": "code", "execution_count": 36, "id": "0f1b83f5-6a25-4943-80d3-25742ec734d8", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Priority Sector Buckets\n", "priority_sector_category_dict = {\n", " 'Solar/GE' : ['Solar and Rooftop Solutions', 'Electronic Battery', 'Electronic Vehicles'],\n", " 'Consumer(Home, Health, Oth)' : ['Cosmetics', 'Dental Care', 'Equipment Financing', 'IVF and Fertility Treatments', 'Insurance Premium Financing', 'Multispeciality Treatments', 'Stemcell banking and treatment','Home Downpayment', 'Modular Kitchen, wardrobes and Interior', 'Painting Services', 'Water Purifier', 'Electronic Products', 'Loose Furniture', 'Personal Loan', 'Jewelry'],\n", " 'Education' : ['Education', 'Entrance prep', 'K12 Coaching', 'K12 Edtech', 'K12 Education', 'LSP - Green Energy & Education', 'Upskilling Edtech'],\n", " 'Revenue Based Financing' : ['Revenue Based Financing']\n", "}\n", "\n", "def priority_sector_category(category):\n", " for priority_sector_category, category_list in priority_sector_category_dict.items():\n", " if category in category_list:\n", " return priority_sector_category\n", " return category\n", "\n", "\n", "portfolio_cuts_df['priority_sector_category'] = portfolio_cuts_df['merchant_category'].apply(priority_sector_category)" ] }, { "cell_type": "code", "execution_count": 37, "id": "b6fcbf7a", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "qry = \"\"\" \n", "select \n", " priority_sector_category \"Main Category\",\n", " count(distinct case when (application_status not in ('closed', 'cancelled', 'settled')) and current_month_revised_dpd < 365 and principal_outstanding > 0 then application_id end) \"Number of Loans\",\n", " sum(case when current_month_revised_dpd < 365 then principal_outstanding end) \"Total Principal Outstanding (In Crs)\",\n", " sum(case when current_month_revised_dpd = 0 then principal_outstanding end) \"Principal without overdues (In Crs)\",\n", " count(distinct case when current_month_revised_dpd < 365 and current_month_revised_dpd > 0 and principal_outstanding > 0 then application_id end) \"No of cases in arrears\",\n", " count(distinct case when current_month_revised_dpd > 364 then application_id end) \"No of loans written off\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 0 and current_month_revised_dpd <= 30) then principal_outstanding end) \"1-30\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 30 and current_month_revised_dpd <= 60) then principal_outstanding end) \"31-60\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 60 and current_month_revised_dpd <= 90) then principal_outstanding end) \"61-90\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 90 and current_month_revised_dpd <= 120) then principal_outstanding end) \"91-120\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 120 and current_month_revised_dpd <= 150) then principal_outstanding end) \"121-150\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 150 and current_month_revised_dpd <= 180) then principal_outstanding end) \"151-180\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 180 and current_month_revised_dpd <= 364) then principal_outstanding end) \"181-364\",\n", " 0 \"Greater than 364\"\n", "from portfolio_cuts_df\n", "group by 1;\n", "\n", "\"\"\"\n", "priority_sector_portfolio_cuts = duckdb.query(qry).df().fillna(0).sort_values(by='Number of Loans',ascending=False).reset_index(drop=True)\n", "priority_sector_portfolio_cuts['Total PAR (In Crs)'] = priority_sector_portfolio_cuts[[\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\"]].sum(axis=1)\n", "priority_sector_portfolio_cuts = priority_sector_portfolio_cuts[[\"Main Category\",\"Number of Loans\",\"Total Principal Outstanding (In Crs)\",\"Principal without overdues (In Crs)\",\"No of cases in arrears\",\n", " \"No of loans written off\",\"Total PAR (In Crs)\",\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\",\"Greater than 364\"]]\n", "\n", "# Add rbf as new row\n", "jify_row = ['Revenue Based Financing'] + fintech_df[['outstanding_count', 'principal_outstanding_amount', 'principal_outstanding_amount']].sum().values.tolist()\n", "jify_row.extend([0] * (len(priority_sector_portfolio_cuts.columns) - len(jify_row)))\n", "priority_sector_portfolio_cuts.loc[-1] = jify_row\n", "priority_sector_portfolio_cuts.reset_index(drop=True,inplace=True)\n", "\n", "totals = priority_sector_portfolio_cuts.sum(numeric_only=True)\n", "totals_row = pd.Series(['Total'] + totals.tolist(), index=priority_sector_portfolio_cuts.columns)\n", "priority_sector_portfolio_cuts = pd.concat([priority_sector_portfolio_cuts, totals_row.to_frame().T], ignore_index=True)\n", "\n", "priority_sector_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]] = priority_sector_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]].apply(lambda x: round(x / 1e7, 2))\n", "#priority_sector_portfolio_cuts" ] }, { "cell_type": "markdown", "id": "216f14a3", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Portfolio Cuts: IRR" ] }, { "cell_type": "code", "execution_count": 38, "id": "fed367be", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# IRR buckets\n", "irr_buckets = [-float('inf'), 0.16, 0.18, 0.20, 0.24, 0.30, 1]\n", "irr_labels = ['1) Up to 16%', '2) 16% to 18%', '3) 18% to 20%', '4) 20% to 24%', '6) 24% to 30%', '6) 30% and higher']\n", "portfolio_cuts_df['irr_buckets'] = pd.cut(portfolio_cuts_df['annualised_irr_rate'], bins=irr_buckets, labels=irr_labels, right=True)\n", "portfolio_cuts_df['irr_buckets'] = portfolio_cuts_df['irr_buckets'].fillna('1) Up to 16%')" ] }, { "cell_type": "code", "execution_count": 39, "id": "4009186a", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "qry = \"\"\" \n", "select \n", " irr_buckets \"IRR\",\n", " count(distinct case when (application_status not in ('closed', 'cancelled', 'settled')) and current_month_revised_dpd < 365 and principal_outstanding > 0 then application_id end) \"Number of Loans\",\n", " sum(case when current_month_revised_dpd < 365 then principal_outstanding end) \"Total Principal Outstanding (In Crs)\",\n", " sum(case when current_month_revised_dpd = 0 then principal_outstanding end) \"Principal without overdues (In Crs)\",\n", " count(distinct case when current_month_revised_dpd < 365 and current_month_revised_dpd > 0 and principal_outstanding > 0 then application_id end) \"No of cases in arrears\",\n", " count(distinct case when current_month_revised_dpd > 364 then application_id end) \"No of loans written off\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 0 and current_month_revised_dpd <= 30) then principal_outstanding end) \"1-30\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 30 and current_month_revised_dpd <= 60) then principal_outstanding end) \"31-60\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 60 and current_month_revised_dpd <= 90) then principal_outstanding end) \"61-90\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 90 and current_month_revised_dpd <= 120) then principal_outstanding end) \"91-120\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 120 and current_month_revised_dpd <= 150) then principal_outstanding end) \"121-150\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 150 and current_month_revised_dpd <= 180) then principal_outstanding end) \"151-180\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 180 and current_month_revised_dpd <= 364) then principal_outstanding end) \"181-364\",\n", " 0 \"Greater than 364\"\n", "from portfolio_cuts_df\n", "group by 1;\n", "\n", "\"\"\"\n", "irr_portfolio_cuts = duckdb.query(qry).df().apply(lambda col: col.fillna(0) if col.dtype in ['float64', 'int64'] else col).sort_values(by='IRR').reset_index(drop=True)\n", "irr_portfolio_cuts['Total PAR (In Crs)'] = irr_portfolio_cuts[[\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\"]].sum(axis=1)\n", "irr_portfolio_cuts = irr_portfolio_cuts[[\"IRR\",\"Number of Loans\",\"Total Principal Outstanding (In Crs)\",\"Principal without overdues (In Crs)\",\"No of cases in arrears\",\n", " \"No of loans written off\",\"Total PAR (In Crs)\",\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\",\"Greater than 364\"]]\n", "\n", "# add fintechs count and amount in 'IRR category'\n", "irr_portfolio_cuts.loc[irr_portfolio_cuts['IRR'] == '1) Up to 16%', 'Number of Loans'] += fintech_df.outstanding_count.loc[0:].sum()\n", "irr_portfolio_cuts.loc[irr_portfolio_cuts['IRR'] == '1) Up to 16%', 'Total Principal Outstanding (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "irr_portfolio_cuts.loc[irr_portfolio_cuts['IRR'] == '1) Up to 16%', 'Principal without overdues (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "\n", "totals = irr_portfolio_cuts.sum(numeric_only=True)\n", "totals_row = pd.Series(['Total'] + totals.tolist(), index=irr_portfolio_cuts.columns)\n", "irr_portfolio_cuts = pd.concat([irr_portfolio_cuts, totals_row.to_frame().T], ignore_index=True)\n", "\n", "irr_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]] = irr_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]].apply(lambda x: round(x / 1e7, 2))\n", "#irr_portfolio_cuts" ] }, { "cell_type": "markdown", "id": "4d4a82cf", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Portfolio Cuts: Bureau Score" ] }, { "cell_type": "code", "execution_count": 40, "id": "6a034717", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Bureau Score buckets\n", "score_buckets = [-1, 10, 300, 500, 600, 650, 700, 750, 800, float('inf')]\n", "score_labels = ['1) -1 to 10', '2) 11 to 300', '3) 301 to 500', '4) 501 to 600', '5) 601 to 650', '6) 651 to 700', '7) 701 to 750', '8) 751 to 800', '9) Greater than 800']\n", "portfolio_cuts_df['bureau_score_buckets'] = pd.cut(portfolio_cuts_df['bureau_score'], bins=score_buckets, labels=score_labels, right=True)\n", "\n", "# set 'Score NA' label where bureau_score is NaN\n", "portfolio_cuts_df['bureau_score_buckets'] = portfolio_cuts_df['bureau_score_buckets'].cat.add_categories('Score NA')\n", "portfolio_cuts_df['bureau_score_buckets'] = portfolio_cuts_df['bureau_score_buckets'].fillna('Score NA')" ] }, { "cell_type": "code", "execution_count": 41, "id": "fb25d099", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "qry = \"\"\" \n", "select \n", " bureau_score_buckets \"Bureau Score\",\n", " count(distinct case when (application_status not in ('closed', 'cancelled', 'settled')) and current_month_revised_dpd < 365 and principal_outstanding > 0 then application_id end) \"Number of Loans\",\n", " sum(case when current_month_revised_dpd < 365 then principal_outstanding end) \"Total Principal Outstanding (In Crs)\",\n", " sum(case when current_month_revised_dpd = 0 then principal_outstanding end) \"Principal without overdues (In Crs)\",\n", " count(distinct case when current_month_revised_dpd < 365 and current_month_revised_dpd > 0 and principal_outstanding > 0 then application_id end) \"No of cases in arrears\",\n", " count(distinct case when current_month_revised_dpd > 364 then application_id end) \"No of loans written off\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 0 and current_month_revised_dpd <= 30) then principal_outstanding end) \"1-30\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 30 and current_month_revised_dpd <= 60) then principal_outstanding end) \"31-60\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 60 and current_month_revised_dpd <= 90) then principal_outstanding end) \"61-90\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 90 and current_month_revised_dpd <= 120) then principal_outstanding end) \"91-120\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 120 and current_month_revised_dpd <= 150) then principal_outstanding end) \"121-150\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 150 and current_month_revised_dpd <= 180) then principal_outstanding end) \"151-180\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 180 and current_month_revised_dpd <= 364) then principal_outstanding end) \"181-364\",\n", " 0 \"Greater than 364\"\n", "from portfolio_cuts_df\n", "group by 1;\n", "\n", "\"\"\"\n", "bureau_score_portfolio_cuts = duckdb.query(qry).df().apply(lambda col: col.fillna(0) if col.dtype in ['float64', 'int64'] else col).sort_values(by='Bureau Score').reset_index(drop=True)\n", "bureau_score_portfolio_cuts['Total PAR (In Crs)'] = bureau_score_portfolio_cuts[[\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\"]].sum(axis=1)\n", "bureau_score_portfolio_cuts = bureau_score_portfolio_cuts[[\"Bureau Score\",\"Number of Loans\",\"Total Principal Outstanding (In Crs)\",\"Principal without overdues (In Crs)\",\"No of cases in arrears\",\n", " \"No of loans written off\",\"Total PAR (In Crs)\",\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\",\"Greater than 364\"]]\n", "\n", "# add fintechs count and amount in 'Bureau Score category'\n", "bureau_score_portfolio_cuts.loc[bureau_score_portfolio_cuts['Bureau Score'] == 'Score NA', 'Number of Loans'] += fintech_df.outstanding_count.loc[0:].sum()\n", "bureau_score_portfolio_cuts.loc[bureau_score_portfolio_cuts['Bureau Score'] == 'Score NA', 'Total Principal Outstanding (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "bureau_score_portfolio_cuts.loc[bureau_score_portfolio_cuts['Bureau Score'] == 'Score NA', 'Principal without overdues (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "\n", "totals = bureau_score_portfolio_cuts.sum(numeric_only=True)\n", "totals_row = pd.Series(['Total'] + totals.tolist(), index=bureau_score_portfolio_cuts.columns)\n", "bureau_score_portfolio_cuts = pd.concat([bureau_score_portfolio_cuts, totals_row.to_frame().T], ignore_index=True)\n", "\n", "bureau_score_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]] = bureau_score_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]].apply(lambda x: round(x / 1e7, 2))\n", "#bureau_score_portfolio_cuts" ] }, { "cell_type": "markdown", "id": "2c264571", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Portfolio Cuts: Employee Status" ] }, { "cell_type": "code", "execution_count": 42, "id": "85c7de51", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Employee Status buckets\n", "emp_status_dict = {\n", " 'Salaried' : ['SALARIED', 'PENSIONER', 'UNEMPLOYED', np.nan],\n", " 'Self Employed' : ['BUSINESS', 'HOUSEWIFE', 'Self Employed - Business', 'Self Employed - Professional']\n", "}\n", "\n", "def emp_status_category(category):\n", " for emp_status_category, category_list in emp_status_dict.items():\n", " if category in category_list:\n", " return emp_status_category\n", " return category\n", "\n", "\n", "portfolio_cuts_df['employment_status_buckets'] = portfolio_cuts_df['employee_status'].apply(emp_status_category)" ] }, { "cell_type": "code", "execution_count": 43, "id": "e8e9b2a5", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "qry = \"\"\" \n", "select \n", " employment_status_buckets \"Customer Profile\",\n", " count(distinct case when (application_status not in ('closed', 'cancelled', 'settled')) and current_month_revised_dpd < 365 and principal_outstanding > 0 then application_id end) \"Number of Loans\",\n", " sum(case when current_month_revised_dpd < 365 then principal_outstanding end) \"Total Principal Outstanding (In Crs)\",\n", " sum(case when current_month_revised_dpd = 0 then principal_outstanding end) \"Principal without overdues (In Crs)\",\n", " count(distinct case when current_month_revised_dpd < 365 and current_month_revised_dpd > 0 and principal_outstanding > 0 then application_id end) \"No of cases in arrears\",\n", " count(distinct case when current_month_revised_dpd > 364 then application_id end) \"No of loans written off\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 0 and current_month_revised_dpd <= 30) then principal_outstanding end) \"1-30\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 30 and current_month_revised_dpd <= 60) then principal_outstanding end) \"31-60\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 60 and current_month_revised_dpd <= 90) then principal_outstanding end) \"61-90\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 90 and current_month_revised_dpd <= 120) then principal_outstanding end) \"91-120\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 120 and current_month_revised_dpd <= 150) then principal_outstanding end) \"121-150\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 150 and current_month_revised_dpd <= 180) then principal_outstanding end) \"151-180\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 180 and current_month_revised_dpd <= 364) then principal_outstanding end) \"181-364\",\n", " 0 \"Greater than 364\"\n", "from portfolio_cuts_df\n", "group by 1;\n", "\n", "\"\"\"\n", "emp_status_portfolio_cuts = duckdb.query(qry).df().fillna(0).sort_values(by='Number of Loans',ascending=False).reset_index(drop=True)\n", "emp_status_portfolio_cuts['Total PAR (In Crs)'] = emp_status_portfolio_cuts[[\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\"]].sum(axis=1)\n", "emp_status_portfolio_cuts = emp_status_portfolio_cuts[[\"Customer Profile\",\"Number of Loans\",\"Total Principal Outstanding (In Crs)\",\"Principal without overdues (In Crs)\",\"No of cases in arrears\",\n", " \"No of loans written off\",\"Total PAR (In Crs)\",\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\",\"Greater than 364\"]]\n", "\n", "# add fintechs count and amount in 'Customer Profile category'\n", "emp_status_portfolio_cuts.loc[emp_status_portfolio_cuts['Customer Profile'] == 'Self Employed', 'Number of Loans'] += fintech_df.outstanding_count.loc[0:].sum()\n", "emp_status_portfolio_cuts.loc[emp_status_portfolio_cuts['Customer Profile'] == 'Self Employed', 'Total Principal Outstanding (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "emp_status_portfolio_cuts.loc[emp_status_portfolio_cuts['Customer Profile'] == 'Self Employed', 'Principal without overdues (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "\n", "totals = emp_status_portfolio_cuts.sum(numeric_only=True)\n", "totals_row = pd.Series(['Total'] + totals.tolist(), index=emp_status_portfolio_cuts.columns)\n", "emp_status_portfolio_cuts = pd.concat([emp_status_portfolio_cuts, totals_row.to_frame().T], ignore_index=True)\n", "\n", "emp_status_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]] = emp_status_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]].apply(lambda x: round(x / 1e7, 2))\n", "#emp_status_portfolio_cuts" ] }, { "cell_type": "markdown", "id": "08531edc", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Portfolio Cuts: Monthly Income" ] }, { "cell_type": "code", "execution_count": 44, "id": "66732a71", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Monthly Income buckets\n", "income_buckets = [-float('inf'), 15000, 30000, 60000, float('inf')]\n", "income_labels = ['1) Below 15k', '2) 15k to 30k', '3) 30k to 60k', '4) 60k and higher']\n", "portfolio_cuts_df['monthly_income_buckets'] = pd.cut(portfolio_cuts_df['monthly_income'], bins=income_buckets, labels=income_labels, right=True)" ] }, { "cell_type": "code", "execution_count": 45, "id": "09603c23", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "qry = \"\"\" \n", "select \n", " monthly_income_buckets \"Customer Income\",\n", " count(distinct case when (application_status not in ('closed', 'cancelled', 'settled')) and current_month_revised_dpd < 365 and principal_outstanding > 0 then application_id end) \"Number of Loans\",\n", " sum(case when current_month_revised_dpd < 365 then principal_outstanding end) \"Total Principal Outstanding (In Crs)\",\n", " sum(case when current_month_revised_dpd = 0 then principal_outstanding end) \"Principal without overdues (In Crs)\",\n", " count(distinct case when current_month_revised_dpd < 365 and current_month_revised_dpd > 0 and principal_outstanding > 0 then application_id end) \"No of cases in arrears\",\n", " count(distinct case when current_month_revised_dpd > 364 then application_id end) \"No of loans written off\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 0 and current_month_revised_dpd <= 30) then principal_outstanding end) \"1-30\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 30 and current_month_revised_dpd <= 60) then principal_outstanding end) \"31-60\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 60 and current_month_revised_dpd <= 90) then principal_outstanding end) \"61-90\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 90 and current_month_revised_dpd <= 120) then principal_outstanding end) \"91-120\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 120 and current_month_revised_dpd <= 150) then principal_outstanding end) \"121-150\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 150 and current_month_revised_dpd <= 180) then principal_outstanding end) \"151-180\",\n", " sum(case when (write_off_flag = 0) and (current_month_revised_dpd > 180 and current_month_revised_dpd <= 364) then principal_outstanding end) \"181-364\",\n", " 0 \"Greater than 364\"\n", "from portfolio_cuts_df\n", "group by 1;\n", "\n", "\"\"\"\n", "monthly_income_portfolio_cuts = duckdb.query(qry).df().apply(lambda col: col.fillna(0) if col.dtype in ['float64', 'int64'] else col).sort_values(by='Customer Income').reset_index(drop=True)\n", "monthly_income_portfolio_cuts['Total PAR (In Crs)'] = monthly_income_portfolio_cuts[[\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\"]].sum(axis=1)\n", "monthly_income_portfolio_cuts = monthly_income_portfolio_cuts[[\"Customer Income\",\"Number of Loans\",\"Total Principal Outstanding (In Crs)\",\"Principal without overdues (In Crs)\",\"No of cases in arrears\",\n", " \"No of loans written off\",\"Total PAR (In Crs)\",\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\",\"Greater than 364\"]]\n", "\n", "# add fintechs count and amount in 'Customer Income category'\n", "monthly_income_portfolio_cuts.loc[monthly_income_portfolio_cuts['Customer Income'] == '4) 60k and higher', 'Number of Loans'] += fintech_df.outstanding_count.loc[0:].sum()\n", "monthly_income_portfolio_cuts.loc[monthly_income_portfolio_cuts['Customer Income'] == '4) 60k and higher', 'Total Principal Outstanding (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "monthly_income_portfolio_cuts.loc[monthly_income_portfolio_cuts['Customer Income'] == '4) 60k and higher', 'Principal without overdues (In Crs)'] += fintech_df.principal_outstanding_amount.loc[0:].sum()\n", "\n", "totals = monthly_income_portfolio_cuts.sum(numeric_only=True)\n", "totals_row = pd.Series(['Total'] + totals.tolist(), index=monthly_income_portfolio_cuts.columns)\n", "monthly_income_portfolio_cuts = pd.concat([monthly_income_portfolio_cuts, totals_row.to_frame().T], ignore_index=True)\n", "\n", "monthly_income_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]] = monthly_income_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]].apply(lambda x: round(x / 1e7, 2))\n", "#monthly_income_portfolio_cuts" ] }, { "cell_type": "markdown", "id": "6e60dccb", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Portfolio Cuts: Counts" ] }, { "cell_type": "code", "execution_count": 46, "id": "3b61194b", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "text/html": [ "
\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", " \n", " \n", " \n", " \n", " \n", " \n", "
CategoryNumber of LoansPrincipal without overdues (In Crs)Total PAR (In Crs)1-3031-6061-9091-120121-150151-180181-364Greater than 364
0All4004364635811031192226221290
\n", "
" ], "text/plain": [ " Category Number of Loans Principal without overdues (In Crs) \\\n", "0 All 4004 3646 \n", "\n", " Total PAR (In Crs) 1-30 31-60 61-90 91-120 121-150 151-180 181-364 \\\n", "0 358 110 31 19 22 26 22 129 \n", "\n", " Greater than 364 \n", "0 0 " ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "qry = \"\"\" \n", "select\n", " 'All' as \"Category\",\n", " count(distinct case when (application_status not in ('closed', 'cancelled')) and write_off_flag = 0 and principal_outstanding > 0 then application_id end) \"Number of Loans\",\n", " count(case when write_off_flag = 0 and current_month_revised_dpd = 0 and principal_outstanding > 0 then application_id end) \"Principal without overdues (In Crs)\",\n", " count(distinct case when write_off_flag = 0 and current_month_revised_dpd > 0 and principal_outstanding > 0 then application_id end) \"Total PAR (In Crs)\",\n", " count(case when (write_off_flag = 0) and (current_month_revised_dpd > 0 and current_month_revised_dpd <= 30) then application_id end) \"1-30\",\n", " count(case when (write_off_flag = 0) and (current_month_revised_dpd > 30 and current_month_revised_dpd <= 60) then application_id end) \"31-60\",\n", " count(case when (write_off_flag = 0) and (current_month_revised_dpd > 60 and current_month_revised_dpd <= 90) then application_id end) \"61-90\",\n", " count(case when (write_off_flag = 0) and (current_month_revised_dpd > 90 and current_month_revised_dpd <= 120) then application_id end) \"91-120\",\n", " count(case when (write_off_flag = 0) and (current_month_revised_dpd > 120 and current_month_revised_dpd <= 150) then application_id end) \"121-150\",\n", " count(case when (write_off_flag = 0) and (current_month_revised_dpd > 150 and current_month_revised_dpd <= 180) then application_id end) \"151-180\",\n", " count(case when (write_off_flag = 0) and (current_month_revised_dpd > 180 and current_month_revised_dpd <= 364) then application_id end) \"181-364\",\n", " 0 \"Greater than 364\"\n", "from portfolio_cuts_df;\n", "\"\"\"\n", "count_portfolio_cuts = duckdb.query(qry).df().fillna(0)\n", "count_portfolio_cuts" ] }, { "cell_type": "markdown", "id": "de24331a", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Export All dataframe to CSV" ] }, { "cell_type": "code", "execution_count": null, "id": "8898495e", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "file_name = f'offbook_portfolio_cuts_{analysis_date}.xlsx'\n", "empty_df = pd.DataFrame([[np.nan] * 1] * 3, columns=['']) # Empty separator dataframe\n", "\n", "# Create a Pandas Excel writer\n", "with pd.ExcelWriter(f'./script/merchant_risk/output/{file_name}', engine='xlsxwriter') as writer:\n", " \n", " # Write the first DataFrame\n", " states_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', index=False)\n", " \n", " # Write the separator\n", " empty_df.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts)+1, header=False, index=False)\n", "\n", " # Write the second DataFrame\n", " tenure_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + 2, index=False)\n", " empty_df.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + 3, header=False, index=False)\n", "\n", " # Write the third DataFrame\n", " ticket_size_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + 4, index=False)\n", " empty_df.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + 5, header=False, index=False)\n", "\n", " # Write the next DataFrames in the same way\n", " category_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + 6, index=False)\n", " empty_df.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(category_portfolio_cuts) + 7, header=False, index=False)\n", "\n", " priority_sector_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(category_portfolio_cuts) + len(empty_df) + 8, index=False)\n", " empty_df.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(category_portfolio_cuts) + len(empty_df) + len(priority_sector_portfolio_cuts) + 9, header=False, index=False)\n", "\n", " irr_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(category_portfolio_cuts) + len(empty_df) + len(priority_sector_portfolio_cuts) + len(empty_df) + 10, index=False)\n", " empty_df.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(category_portfolio_cuts) + len(empty_df) + len(priority_sector_portfolio_cuts) + len(empty_df) + len(irr_portfolio_cuts) + 11, header=False, index=False)\n", "\n", " bureau_score_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(category_portfolio_cuts) + len(empty_df) + len(priority_sector_portfolio_cuts) + len(empty_df) + len(irr_portfolio_cuts) + len(empty_df) + 12, index=False)\n", " empty_df.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(category_portfolio_cuts) + len(empty_df) + len(priority_sector_portfolio_cuts) + len(empty_df) + len(irr_portfolio_cuts) + len(empty_df) + len(bureau_score_portfolio_cuts) + 13, header=False, index=False)\n", "\n", " emp_status_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(category_portfolio_cuts) + len(empty_df) + len(priority_sector_portfolio_cuts) + len(empty_df) + len(irr_portfolio_cuts) + len(empty_df) + len(bureau_score_portfolio_cuts) + len(empty_df) + 14, index=False)\n", " empty_df.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(category_portfolio_cuts) + len(empty_df) + len(priority_sector_portfolio_cuts) + len(empty_df) + len(irr_portfolio_cuts) + len(empty_df) + len(bureau_score_portfolio_cuts) + len(empty_df) + len(emp_status_portfolio_cuts) + 15, index=False)\n", "\n", " monthly_income_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(states_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(category_portfolio_cuts) + len(empty_df) + len(priority_sector_portfolio_cuts) + len(empty_df) + len(irr_portfolio_cuts) + len(empty_df) + len(bureau_score_portfolio_cuts) + len(empty_df) + len(emp_status_portfolio_cuts) + len(empty_df) + 16, index=False)\n", "\n", " count_portfolio_cuts.to_excel(writer, sheet_name='Counts', 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 }