{ "cells": [ { "cell_type": "code", "execution_count": 1, "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\n", "#pd.set_option('display.float_format', '₹ {:,.2f}'.format)" ] }, { "cell_type": "markdown", "id": "4393c50d", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Import base file and add new required columns" ] }, { "cell_type": "code", "execution_count": null, "id": "8120b6c5", "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": null, "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'][['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", "\n", "portfolio_cuts_df['bureau_score'] = portfolio_cuts_df['bureau_score'].astype('float64')\n", "portfolio_cuts_df['monthly_income'] = portfolio_cuts_df['monthly_income'].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 = portfolio_cuts_df[portfolio_cuts_df['application_status'] != 'settled']\n", "# portfolio_cuts_df.head()" ] }, { "cell_type": "code", "execution_count": 4, "id": "a757b2f0-263e-4914-bc17-c341ea95a46d", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "3aded6a5e6044f488a5f3997ed5ddccd", "version_major": 2, "version_minor": 0 }, "text/plain": [ "Pandas Apply: 0%| | 0/65592 [00:00 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", "\n", "#states_portfolio_cuts[\"Total Principal Outstanding (In Crs)\"].sum()\n", "# states_portfolio_cuts" ] }, { "cell_type": "code", "execution_count": null, "id": "33706b6f", "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", " \n", " \n", " \n", " \n", " \n", " \n", "
merchant_categoryprincipal_outstanding_amountoutstanding_count
0Earned Wage102380301529
1Fundly117152539
2Solar Channel Financing3212893421
3Healthcare Channel Financing00
4Fundly offbook00
\n", "
" ], "text/plain": [ " merchant_category principal_outstanding_amount \\\n", "0 Earned Wage 10238030 \n", "1 Fundly 1171525 \n", "2 Solar Channel Financing 32128934 \n", "3 Healthcare Channel Financing 0 \n", "4 Fundly offbook 0 \n", "\n", " outstanding_count \n", "0 1529 \n", "1 39 \n", "2 21 \n", "3 0 \n", "4 0 " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "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" ] }, { "cell_type": "code", "execution_count": null, "id": "49da88fa", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "jify_state_df = pd.read_excel('./script/merchant_risk/input/jify_state.xlsx')\n", "jify_state_df['State'] = jify_state_df['State'].apply(lambda x: 'Uttaranchal/Uttarakhand' if x == 'Uttarakhand' else x)\n", "#jify_state_df" ] }, { "cell_type": "code", "execution_count": 9, "id": "11d1b5aa", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# fix differences in jify dataframe\n", "jify_state_df.at[0,'Number of Loans'] += (fintech_df.loc[fintech_df['merchant_category'] == 'Earned Wage','outstanding_count'].sum() - jify_state_df['Number of Loans'].sum())\n", "jify_state_df.at[0,'Total Principal Outstanding (In Crs)'] += (fintech_df.loc[fintech_df['merchant_category'] == 'Earned Wage','principal_outstanding_amount'].sum() - jify_state_df['Total Principal Outstanding (In Crs)'].sum())\n", "jify_state_df.at[0,'Principal without overdues (In Crs)'] += (fintech_df.loc[fintech_df['merchant_category'] == 'Earned Wage','principal_outstanding_amount'].sum() - jify_state_df['Principal without overdues (In Crs)'].sum())" ] }, { "cell_type": "code", "execution_count": 10, "id": "12b99057", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# now merge jify state df with our original state portfolio cuts df\n", "\n", "states_portfolio_cuts = pd.merge(states_portfolio_cuts,jify_state_df,on='State',how='left')\n", "\n", "states_portfolio_cuts['Number of Loans'] = states_portfolio_cuts['Number of Loans_x'] + states_portfolio_cuts['Number of Loans_y']\n", "states_portfolio_cuts['Total Principal Outstanding (In Crs)'] = states_portfolio_cuts['Total Principal Outstanding (In Crs)_x'] + states_portfolio_cuts['Total Principal Outstanding (In Crs)_y']\n", "states_portfolio_cuts['Principal without overdues (In Crs)'] = states_portfolio_cuts['Principal without overdues (In Crs)_x'] + states_portfolio_cuts['Principal without overdues (In Crs)_y']\n", "\n", "states_portfolio_cuts.drop(columns=['Number of Loans_x','Number of Loans_y','Total Principal Outstanding (In Crs)_x','Total Principal Outstanding (In Crs)_y','Principal without overdues (In Crs)_x','Principal without overdues (In Crs)_y'],axis=1,inplace=True)\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 other fintechs(fundly, channel financing etc) count and amount in 'Other category'\n", "states_portfolio_cuts.loc[states_portfolio_cuts['State'] == 'Others', 'Number of Loans'] += fintech_df.outstanding_count.loc[1:].sum()\n", "states_portfolio_cuts.loc[states_portfolio_cuts['State'] == 'Others', 'Total Principal Outstanding (In Crs)'] += fintech_df.principal_outstanding_amount.loc[1:].sum()\n", "states_portfolio_cuts.loc[states_portfolio_cuts['State'] == 'Others', 'Principal without overdues (In Crs)'] += fintech_df.principal_outstanding_amount.loc[1:].sum()\n", "\n", "# create total rows\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", "\n", "# states_portfolio_cuts" ] }, { "cell_type": "markdown", "id": "68e01595", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Portfolio Cuts : Tenure" ] }, { "cell_type": "code", "execution_count": 11, "id": "0248117d-2780-442d-b612-d80632bd7f3c", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Tenure buckets\n", "tenure_buckets = [-1, 6, 12, 24, 36, 60, float('inf')]\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', '6) Greater than 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": 12, "id": "420e6b7a", "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", " \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", " \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", "
TenureNumber of LoansTotal Principal Outstanding (In Crs)Principal without overdues (In Crs)No of cases in arrearsNo of loans written offTotal PAR (In Crs)1-3031-6061-9091-120121-150151-180181-364Greater than 364
01) 0 to 6 months249416.2316.10411330.130.040.030.010.000.000.000.060
12) 7 to 12 months277918.2716.643247351.630.220.060.110.070.160.060.960
23) 13 to 24 months353025.6423.433614212.210.560.210.190.130.120.110.890
34) 25 to 36 months302556.3752.49201913.882.110.140.320.300.200.070.740
45) 37 to 60 months353890.4782.54273617.933.840.940.970.320.660.280.930
56) Greater than 60 months30.050.05000.000.000.000.000.000.000.000.000
6Total15,369.00207.04191.261,200.001,441.0015.786.761.371.590.821.140.523.590.00
\n", "
" ], "text/plain": [ " Tenure Number of Loans \\\n", "0 1) 0 to 6 months 2494 \n", "1 2) 7 to 12 months 2779 \n", "2 3) 13 to 24 months 3530 \n", "3 4) 25 to 36 months 3025 \n", "4 5) 37 to 60 months 3538 \n", "5 6) Greater than 60 months 3 \n", "6 Total 15,369.00 \n", "\n", " Total Principal Outstanding (In Crs) Principal without overdues (In Crs) \\\n", "0 16.23 16.10 \n", "1 18.27 16.64 \n", "2 25.64 23.43 \n", "3 56.37 52.49 \n", "4 90.47 82.54 \n", "5 0.05 0.05 \n", "6 207.04 191.26 \n", "\n", " No of cases in arrears No of loans written off Total PAR (In Crs) 1-30 \\\n", "0 41 133 0.13 0.04 \n", "1 324 735 1.63 0.22 \n", "2 361 421 2.21 0.56 \n", "3 201 91 3.88 2.11 \n", "4 273 61 7.93 3.84 \n", "5 0 0 0.00 0.00 \n", "6 1,200.00 1,441.00 15.78 6.76 \n", "\n", " 31-60 61-90 91-120 121-150 151-180 181-364 Greater than 364 \n", "0 0.03 0.01 0.00 0.00 0.00 0.06 0 \n", "1 0.06 0.11 0.07 0.16 0.06 0.96 0 \n", "2 0.21 0.19 0.13 0.12 0.11 0.89 0 \n", "3 0.14 0.32 0.30 0.20 0.07 0.74 0 \n", "4 0.94 0.97 0.32 0.66 0.28 0.93 0 \n", "5 0.00 0.00 0.00 0.00 0.00 0.00 0 \n", "6 1.37 1.59 0.82 1.14 0.52 3.59 0.00 " ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "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().fillna(0).sort_values(by=\"Tenure\").reset_index(drop=True)\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", "\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 all 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": 13, "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", "# 0 to 50,000 | 50,001 to 1,00,000 and so on..\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": 14, "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 Solar CF in 500k to 1000K category, \n", "ticket_size_portfolio_cuts.loc[ticket_size_portfolio_cuts['Loan Ticket size'] == '7) ₹ 500k to 1000k', 'Number of Loans'] += fintech_df.loc[fintech_df['merchant_category'] == 'Solar Channel Financing','outstanding_count'].sum()\n", "ticket_size_portfolio_cuts.loc[ticket_size_portfolio_cuts['Loan Ticket size'] == '7) ₹ 500k to 1000k', 'Total Principal Outstanding (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'] == 'Solar Channel Financing','principal_outstanding_amount'].sum()\n", "ticket_size_portfolio_cuts.loc[ticket_size_portfolio_cuts['Loan Ticket size'] == '7) ₹ 500k to 1000k', 'Principal without overdues (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'] == 'Solar Channel Financing','principal_outstanding_amount'].sum()\n", "\n", "#other 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.loc[fintech_df['merchant_category'] != 'Solar Channel Financing','outstanding_count'].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.loc[fintech_df['merchant_category'] != 'Solar Channel Financing','principal_outstanding_amount'].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.loc[fintech_df['merchant_category'] != 'Solar Channel Financing','principal_outstanding_amount'].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": 15, "id": "c0cf7736-46b9-4126-acfb-091534d76a75", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Main category buckets\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": 16, "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 Healthcare CF in 'Healthcare' category\n", "category_portfolio_cuts.loc[category_portfolio_cuts['Category'] == 'Healthcare', 'Number of Loans'] += fintech_df.loc[fintech_df['merchant_category'] == 'Healthcare Channel Financing','outstanding_count'].sum()\n", "category_portfolio_cuts.loc[category_portfolio_cuts['Category'] == 'Healthcare', 'Total Principal Outstanding (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'] == 'Healthcare Channel Financing','principal_outstanding_amount'].sum()\n", "category_portfolio_cuts.loc[category_portfolio_cuts['Category'] == 'Healthcare', 'Principal without overdues (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'] == 'Healthcare Channel Financing','principal_outstanding_amount'].sum()\n", "\n", "# add Solar CF in 'Solar' category\n", "category_portfolio_cuts.loc[category_portfolio_cuts['Category'] == 'Solar/GE', 'Number of Loans'] += fintech_df.loc[fintech_df['merchant_category'] == 'Solar Channel Financing','outstanding_count'].sum()\n", "category_portfolio_cuts.loc[category_portfolio_cuts['Category'] == 'Solar/GE', 'Total Principal Outstanding (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'] == 'Solar Channel Financing','principal_outstanding_amount'].sum()\n", "category_portfolio_cuts.loc[category_portfolio_cuts['Category'] == 'Solar/GE', 'Principal without overdues (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'] == 'Solar Channel Financing','principal_outstanding_amount'].sum()\n", "\n", "# add Fundly in 'Revenue Based Financing' category\n", "category_portfolio_cuts.loc[category_portfolio_cuts['Category'] == 'Revenue Based Financing', 'Number of Loans'] += fintech_df.loc[fintech_df['merchant_category'].str.contains('Fundly'),'outstanding_count'].sum()\n", "category_portfolio_cuts.loc[category_portfolio_cuts['Category'] == 'Revenue Based Financing', 'Total Principal Outstanding (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'].str.contains('Fundly'),'principal_outstanding_amount'].sum()\n", "category_portfolio_cuts.loc[category_portfolio_cuts['Category'] == 'Revenue Based Financing', 'Principal without overdues (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'].str.contains('Fundly'),'principal_outstanding_amount'].sum()\n", "\n", "# Add jify as new row\n", "jify_row = ['Earned Wage'] + jify_state_df[['Number of Loans', 'Total Principal Outstanding (In Crs)', 'Principal without overdues (In Crs)']].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": 17, "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": 18, "id": "b6fcbf7a", "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", " \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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Main CategoryNumber of LoansTotal Principal Outstanding (In Crs)Principal without overdues (In Crs)No of cases in arrearsNo of loans written offTotal PAR (In Crs)1-3031-6061-9091-120121-150151-180181-364Greater than 364
0Solar/GE6,307.00132.83125.612601777.223.780.950.920.350.610.060.550
1Education5,475.0044.5338.556376275.982.430.340.430.270.230.361.930
2Consumer(Home, Health, Oth)2,019.0028.5425.963036362.580.540.090.230.200.300.101.110
3Revenue Based Financing39.000.120.12010.000.000.000.000.000.000.000.000
4Earned Wage1,529.001.021.02000.000.000.000.000.000.000.000.000
5Total15,369.00207.04191.261,200.001,441.0015.786.761.371.590.821.140.523.590.00
\n", "
" ], "text/plain": [ " Main Category Number of Loans \\\n", "0 Solar/GE 6,307.00 \n", "1 Education 5,475.00 \n", "2 Consumer(Home, Health, Oth) 2,019.00 \n", "3 Revenue Based Financing 39.00 \n", "4 Earned Wage 1,529.00 \n", "5 Total 15,369.00 \n", "\n", " Total Principal Outstanding (In Crs) Principal without overdues (In Crs) \\\n", "0 132.83 125.61 \n", "1 44.53 38.55 \n", "2 28.54 25.96 \n", "3 0.12 0.12 \n", "4 1.02 1.02 \n", "5 207.04 191.26 \n", "\n", " No of cases in arrears No of loans written off Total PAR (In Crs) 1-30 \\\n", "0 260 177 7.22 3.78 \n", "1 637 627 5.98 2.43 \n", "2 303 636 2.58 0.54 \n", "3 0 1 0.00 0.00 \n", "4 0 0 0.00 0.00 \n", "5 1,200.00 1,441.00 15.78 6.76 \n", "\n", " 31-60 61-90 91-120 121-150 151-180 181-364 Greater than 364 \n", "0 0.95 0.92 0.35 0.61 0.06 0.55 0 \n", "1 0.34 0.43 0.27 0.23 0.36 1.93 0 \n", "2 0.09 0.23 0.20 0.30 0.10 1.11 0 \n", "3 0.00 0.00 0.00 0.00 0.00 0.00 0 \n", "4 0.00 0.00 0.00 0.00 0.00 0.00 0 \n", "5 1.37 1.59 0.82 1.14 0.52 3.59 0.00 " ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "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 Healthcare CF in 'Consumer(Home, Health, Oth)' category\n", "priority_sector_portfolio_cuts.loc[priority_sector_portfolio_cuts['Main Category'] == 'Consumer(Home, Health, Oth)', 'Number of Loans'] += fintech_df.loc[fintech_df['merchant_category'] == 'Healthcare Channel Financing','outstanding_count'].sum()\n", "priority_sector_portfolio_cuts.loc[priority_sector_portfolio_cuts['Main Category'] == 'Consumer(Home, Health, Oth)', 'Total Principal Outstanding (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'] == 'Healthcare Channel Financing','principal_outstanding_amount'].sum()\n", "priority_sector_portfolio_cuts.loc[priority_sector_portfolio_cuts['Main Category'] == 'Consumer(Home, Health, Oth)', 'Principal without overdues (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'] == 'Healthcare Channel Financing','principal_outstanding_amount'].sum()\n", "\n", "# add Solar CF in 'Solar' category\n", "priority_sector_portfolio_cuts.loc[priority_sector_portfolio_cuts['Main Category'] == 'Solar/GE', 'Number of Loans'] += fintech_df.loc[fintech_df['merchant_category'] == 'Solar Channel Financing','outstanding_count'].sum()\n", "priority_sector_portfolio_cuts.loc[priority_sector_portfolio_cuts['Main Category'] == 'Solar/GE', 'Total Principal Outstanding (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'] == 'Solar Channel Financing','principal_outstanding_amount'].sum()\n", "priority_sector_portfolio_cuts.loc[priority_sector_portfolio_cuts['Main Category'] == 'Solar/GE', 'Principal without overdues (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'] == 'Solar Channel Financing','principal_outstanding_amount'].sum()\n", "\n", "# add Fundly in 'Revenue Based Financing' category\n", "priority_sector_portfolio_cuts.loc[priority_sector_portfolio_cuts['Main Category'] == 'Revenue Based Financing', 'Number of Loans'] += fintech_df.loc[fintech_df['merchant_category'].str.contains('Fundly'),'outstanding_count'].sum()\n", "priority_sector_portfolio_cuts.loc[priority_sector_portfolio_cuts['Main Category'] == 'Revenue Based Financing', 'Total Principal Outstanding (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'].str.contains('Fundly'),'principal_outstanding_amount'].sum()\n", "priority_sector_portfolio_cuts.loc[priority_sector_portfolio_cuts['Main Category'] == 'Revenue Based Financing', 'Principal without overdues (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'].str.contains('Fundly'),'principal_outstanding_amount'].sum()\n", "\n", "# Add jify as new row\n", "jify_row = ['Earned Wage'] + jify_state_df[['Number of Loans', 'Total Principal Outstanding (In Crs)', 'Principal without overdues (In Crs)']].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": 19, "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": 20, "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 <= 365) 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 other fintechs count and amount in '0 to 6 months 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": 21, "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', '11 to 300', '2) 301 to 500', '3) 501 to 600', '4) 601 to 650', '5) 651 to 700', '6) 701 to 750', '7) 751 to 800', '8) 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": 22, "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 <= 365) 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", "#bureau_score_portfolio_cuts" ] }, { "cell_type": "code", "execution_count": null, "id": "394c4e0d", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "jify_score_df = pd.read_excel('./script/merchant_risk/input/jify_score.xlsx')\n", "\n", "# # fix differences in jify dataframe\n", "jify_score_df.at[1,'Number of Loans'] += (fintech_df.loc[fintech_df['merchant_category'] == 'Earned Wage','outstanding_count'].sum() - jify_score_df['Number of Loans'].sum())\n", "jify_score_df.at[1,'Total Principal Outstanding (In Crs)'] += (fintech_df.loc[fintech_df['merchant_category'] == 'Earned Wage','principal_outstanding_amount'].sum() - jify_score_df['Total Principal Outstanding (In Crs)'].sum())\n", "jify_score_df.at[1,'Principal without overdues (In Crs)'] += (fintech_df.loc[fintech_df['merchant_category'] == 'Earned Wage','principal_outstanding_amount'].sum() - jify_score_df['Principal without overdues (In Crs)'].sum())\n", "\n", "# jify_score_df" ] }, { "cell_type": "code", "execution_count": 24, "id": "502ba00f", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# now merge jify score df with our original score portfolio cuts df\n", "\n", "bureau_score_portfolio_cuts = pd.merge(bureau_score_portfolio_cuts,jify_score_df,on='Bureau Score',how='left')\n", "\n", "bureau_score_portfolio_cuts['Number of Loans'] = bureau_score_portfolio_cuts['Number of Loans_x'] + bureau_score_portfolio_cuts['Number of Loans_y']\n", "bureau_score_portfolio_cuts['Total Principal Outstanding (In Crs)'] = bureau_score_portfolio_cuts['Total Principal Outstanding (In Crs)_x'] + bureau_score_portfolio_cuts['Total Principal Outstanding (In Crs)_y']\n", "bureau_score_portfolio_cuts['Principal without overdues (In Crs)'] = bureau_score_portfolio_cuts['Principal without overdues (In Crs)_x'] + bureau_score_portfolio_cuts['Principal without overdues (In Crs)_y']\n", "\n", "bureau_score_portfolio_cuts.drop(columns=['Number of Loans_x','Number of Loans_y','Total Principal Outstanding (In Crs)_x','Total Principal Outstanding (In Crs)_y','Principal without overdues (In Crs)_x','Principal without overdues (In Crs)_y'],axis=1,inplace=True)\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 other fintechs(fundly, channel financing etc) count and amount in 'Score NA category'\n", "bureau_score_portfolio_cuts.loc[bureau_score_portfolio_cuts['Bureau Score'] == 'Score NA', 'Number of Loans'] += fintech_df.outstanding_count.loc[1:].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[1:].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[1:].sum()\n", "\n", "# # create total row and append to original dataframe\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": 25, "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": 26, "id": "e8e9b2a5", "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", " \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", "
Customer ProfileNumber of LoansTotal Principal Outstanding (In Crs)Principal without overdues (In Crs)No of cases in arrearsNo of loans written offTotal PAR (In Crs)1-3031-6061-9091-120121-150151-180181-364Greater than 364
0Salaried1008399.4992.746639036.752.740.380.580.350.280.422.000
1Self Employed5286107.5598.525375389.034.020.991.000.470.860.101.590
2Total15,369.00207.04191.261,200.001,441.0015.786.761.371.590.821.140.523.590.00
\n", "
" ], "text/plain": [ " Customer Profile Number of Loans Total Principal Outstanding (In Crs) \\\n", "0 Salaried 10083 99.49 \n", "1 Self Employed 5286 107.55 \n", "2 Total 15,369.00 207.04 \n", "\n", " Principal without overdues (In Crs) No of cases in arrears \\\n", "0 92.74 663 \n", "1 98.52 537 \n", "2 191.26 1,200.00 \n", "\n", " No of loans written off Total PAR (In Crs) 1-30 31-60 61-90 91-120 121-150 \\\n", "0 903 6.75 2.74 0.38 0.58 0.35 0.28 \n", "1 538 9.03 4.02 0.99 1.00 0.47 0.86 \n", "2 1,441.00 15.78 6.76 1.37 1.59 0.82 1.14 \n", "\n", " 151-180 181-364 Greater than 364 \n", "0 0.42 2.00 0 \n", "1 0.10 1.59 0 \n", "2 0.52 3.59 0.00 " ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "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 <= 365) 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 Earned wage & HCF count and amount in 'Salaried category'\n", "emp_status_portfolio_cuts.loc[emp_status_portfolio_cuts['Customer Profile'] == 'Salaried', 'Number of Loans'] += fintech_df.loc[fintech_df['merchant_category'].isin(['Earned Wage']),'outstanding_count'].sum()\n", "emp_status_portfolio_cuts.loc[emp_status_portfolio_cuts['Customer Profile'] == 'Salaried', 'Total Principal Outstanding (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'].isin(['Earned Wage']),'principal_outstanding_amount'].sum()\n", "emp_status_portfolio_cuts.loc[emp_status_portfolio_cuts['Customer Profile'] == 'Salaried', 'Principal without overdues (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'].isin(['Earned Wage']),'principal_outstanding_amount'].sum()\n", "\n", "# add everything else in 'Self-employed category'\n", "emp_status_portfolio_cuts.loc[emp_status_portfolio_cuts['Customer Profile'] == 'Self Employed', 'Number of Loans'] += fintech_df.loc[~fintech_df['merchant_category'].isin(['Earned Wage']),'outstanding_count'].sum()\n", "emp_status_portfolio_cuts.loc[emp_status_portfolio_cuts['Customer Profile'] == 'Self Employed', 'Total Principal Outstanding (In Crs)'] += fintech_df.loc[~fintech_df['merchant_category'].isin(['Earned Wage']),'principal_outstanding_amount'].sum()\n", "emp_status_portfolio_cuts.loc[emp_status_portfolio_cuts['Customer Profile'] == 'Self Employed', 'Principal without overdues (In Crs)'] += fintech_df.loc[~fintech_df['merchant_category'].isin(['Earned Wage']),'principal_outstanding_amount'].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": 27, "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 and equal to 15k', '2) 15k to 30k', '3) 30k to 60k', '4) Greater than 60k']\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": 28, "id": "09603c23", "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", " \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", " \n", "
Customer IncomeNumber of LoansTotal Principal Outstanding (In Crs)Principal without overdues (In Crs)No of cases in arrearsNo of loans written offTotal PAR (In Crs)1-3031-6061-9091-120121-150151-180181-364Greater than 364
01) Below and equal to 15k1621.351.1927680.160.030.000.050.000.000.010.070
12) 15k to 30k473827.9124.404606973.511.280.290.290.170.190.231.070
23) 30k to 60k532470.8965.744325115.142.350.270.350.360.290.151.380
34) Greater than 60k5145106.8999.922811656.973.110.810.900.290.670.141.060
4Total15,369.00207.04191.261,200.001,441.0015.786.761.371.590.821.140.523.590.00
\n", "
" ], "text/plain": [ " Customer Income Number of Loans \\\n", "0 1) Below and equal to 15k 162 \n", "1 2) 15k to 30k 4738 \n", "2 3) 30k to 60k 5324 \n", "3 4) Greater than 60k 5145 \n", "4 Total 15,369.00 \n", "\n", " Total Principal Outstanding (In Crs) Principal without overdues (In Crs) \\\n", "0 1.35 1.19 \n", "1 27.91 24.40 \n", "2 70.89 65.74 \n", "3 106.89 99.92 \n", "4 207.04 191.26 \n", "\n", " No of cases in arrears No of loans written off Total PAR (In Crs) 1-30 \\\n", "0 27 68 0.16 0.03 \n", "1 460 697 3.51 1.28 \n", "2 432 511 5.14 2.35 \n", "3 281 165 6.97 3.11 \n", "4 1,200.00 1,441.00 15.78 6.76 \n", "\n", " 31-60 61-90 91-120 121-150 151-180 181-364 Greater than 364 \n", "0 0.00 0.05 0.00 0.00 0.01 0.07 0 \n", "1 0.29 0.29 0.17 0.19 0.23 1.07 0 \n", "2 0.27 0.35 0.36 0.29 0.15 1.38 0 \n", "3 0.81 0.90 0.29 0.67 0.14 1.06 0 \n", "4 1.37 1.59 0.82 1.14 0.52 3.59 0.00 " ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "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 <= 365) 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 Earned wage & HCF count and amount in '15k to 30k category'\n", "monthly_income_portfolio_cuts.loc[monthly_income_portfolio_cuts['Customer Income'] == '2) 15k to 30k', 'Number of Loans'] += fintech_df.loc[fintech_df['merchant_category'].isin(['Earned Wage','Healthcare Channel Financing']),'outstanding_count'].sum()\n", "monthly_income_portfolio_cuts.loc[monthly_income_portfolio_cuts['Customer Income'] == '2) 15k to 30k', 'Total Principal Outstanding (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'].isin(['Earned Wage','Healthcare Channel Financing']),'principal_outstanding_amount'].sum()\n", "monthly_income_portfolio_cuts.loc[monthly_income_portfolio_cuts['Customer Income'] == '2) 15k to 30k', 'Principal without overdues (In Crs)'] += fintech_df.loc[fintech_df['merchant_category'].isin(['Earned Wage','Healthcare Channel Financing']),'principal_outstanding_amount'].sum()\n", "\n", "# add everything else in 'Greater than 60k category'\n", "monthly_income_portfolio_cuts.loc[monthly_income_portfolio_cuts['Customer Income'] == '4) Greater than 60k', 'Number of Loans'] += fintech_df.loc[~fintech_df['merchant_category'].isin(['Earned Wage','Healthcare Channel Financing']),'outstanding_count'].sum()\n", "monthly_income_portfolio_cuts.loc[monthly_income_portfolio_cuts['Customer Income'] == '4) Greater than 60k', 'Total Principal Outstanding (In Crs)'] += fintech_df.loc[~fintech_df['merchant_category'].isin(['Earned Wage','Healthcare Channel Financing']),'principal_outstanding_amount'].sum()\n", "monthly_income_portfolio_cuts.loc[monthly_income_portfolio_cuts['Customer Income'] == '4) Greater than 60k', 'Principal without overdues (In Crs)'] += fintech_df.loc[~fintech_df['merchant_category'].isin(['Earned Wage','Healthcare Channel Financing']),'principal_outstanding_amount'].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": "fa5fc1ab", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Portfolio Cuts: Count" ] }, { "cell_type": "code", "execution_count": 29, "id": "b2661a3d", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "qry = \"\"\" \n", "select\n", " 'All' as \"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", " count(case when current_month_revised_dpd < 365 and current_month_revised_dpd = 0 and principal_outstanding > 0 then application_id 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) \"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 <= 365) 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)" ] }, { "cell_type": "code", "execution_count": 30, "id": "a9e9ba60", "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
0All153691416912003771031006474564270
\n", "
" ], "text/plain": [ " Category Number of Loans Principal without overdues (In Crs) \\\n", "0 All 15369 14169 \n", "\n", " Total PAR (In Crs) 1-30 31-60 61-90 91-120 121-150 151-180 181-364 \\\n", "0 1200 377 103 100 64 74 56 427 \n", "\n", " Greater than 364 \n", "0 0 " ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "count_portfolio_cuts['Number of Loans'] = count_portfolio_cuts['Number of Loans'] + fintech_df['outstanding_count'].sum()\n", "count_portfolio_cuts['Principal without overdues (In Crs)'] = count_portfolio_cuts['Principal without overdues (In Crs)'] + fintech_df['outstanding_count'].sum()\n", "count_portfolio_cuts" ] }, { "cell_type": "markdown", "id": "b5ff81ad", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Export All dataframe to CSV" ] }, { "cell_type": "code", "execution_count": null, "id": "14f0735f", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "file_name = f'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 = states_portfolio_cuts.apply(lambda x: round(x / 10**7,2) if not x.name.startswith('N') and x.dtype in ['float64', 'int64'] else x)\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)\n" ] }, { "cell_type": "code", "execution_count": null, "id": "44da591f", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "portfolio_cuts_df.to_csv(f'./script/merchant_risk/output/portfolio_cuts_raw_data_{analysis_date}.csv',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 }