{ "cells": [ { "cell_type": "code", "execution_count": 1, "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", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Import base file and add new required columns" ] }, { "cell_type": "code", "execution_count": null, "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": 3, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "portfolio_cuts_df = pd.read_csv(file_path,parse_dates=['analysis_date','disbursement_date'], dayfirst=True)\n", "analysis_date = datetime.strftime(portfolio_cuts_df['analysis_date'][0],'%b-%Y')\n", "portfolio_cuts_df = portfolio_cuts_df[portfolio_cuts_df['merchant_category'] == 'Solar and Rooftop Solutions'][['application_id','merchant_name','revised_type_of_customer','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']" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# # Change type of customer as per new tagging\n", "# solar_tagging_df = pd.read_csv('../input/solar_cases_tagging.csv')\n", "# # solar_tagging_df['application_id'] = solar_tagging_df['application_id'].str.replace(',','').astype('int64')\n", "# portfolio_cuts_df = pd.merge(portfolio_cuts_df,solar_tagging_df[['application_id','new_tagging']],how='left',on='application_id').drop(columns='type_of_customer',axis=1).rename(columns={'new_tagging':'type_of_customer'})" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# 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['remaining_emi'] = portfolio_cuts_df['remaining_emi'].astype('float64').fillna(0)\n", "# portfolio_cuts_df['emi_amount'] = portfolio_cuts_df['emi_amount'].astype('float64').fillna(0)\n", "# portfolio_cuts_df['net_disbursement_amount'] = portfolio_cuts_df['net_disbursement_amount'].astype('float64').fillna(0)\n", "# portfolio_cuts_df['hold_amount'] = portfolio_cuts_df['hold_amount'].astype('float64').fillna(0)\n", "\n", "# portfolio_cuts_df['annualised_irr_rate'] = portfolio_cuts_df.swifter.apply(\n", "# lambda row: 0.175/12 if row['merchant_name'] == 'Klub Works Private Limited' \n", "# else npf.rate(row['remaining_emi'], row['emi_amount'], -row['net_disbursement_amount'], -row['hold_amount']) * 12,\n", "# axis=1\n", "# )" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "cc7017ea8f024cbcb2bea01becb2fb61", "version_major": 2, "version_minor": 0 }, "text/plain": [ "Pandas Apply: 0%| | 0/9692 [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 df\n", " group by 1;\n", " \"\"\"\n", " geography_portfolio_cuts = duckdb.query(qry).df().fillna(0).sort_values(by=\"Total Principal Outstanding (In Crs)\",ascending=False).reset_index(drop=True)\n", " geography_portfolio_cuts['Total PAR (In Crs)'] = geography_portfolio_cuts[[\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\"]].sum(axis=1)\n", " geography_portfolio_cuts = geography_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", " totals = geography_portfolio_cuts.sum(numeric_only=True)\n", " totals_row = pd.Series(['Total'] + totals.tolist(), index=geography_portfolio_cuts.columns)\n", " geography_portfolio_cuts = pd.concat([geography_portfolio_cuts, totals_row.to_frame().T], ignore_index=True)\n", " geography_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]] = geography_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]].apply(lambda x: round(x / 1e7, 2))\n", " # geography_portfolio_cuts.sort_values(by = \"Total Principal Outstanding (In Crs)\", ascending = False, inplace = True)\n", "\n", "\n", "\n", "\n", " # Tenure\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", " df['tenure_buckets'] = pd.cut(df['remaining_emi'], bins=tenure_buckets, labels=tenure_labels, right=True)\n", " 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 df\n", " group by 1;\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", " 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", " 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", "\n", "\n", "\n", "\n", " # Ticket size (loan amount wise distribution)\n", " 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", " df['ticket_size_buckets'] = pd.cut(df['net_disbursement_amount'], bins=ticket_size_buckets, labels=ticket_size_labels, right=True)\n", " df['ticket_size_buckets'] = df['ticket_size_buckets'].fillna('1) ₹ 0 to 50k')\n", " 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 df\n", " group by 1;\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", " 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", " 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", "\n", "\n", "\n", "\n", " # IRR( monthly irr)\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%', '7) 30% and higher']\n", " df['irr_buckets'] = pd.cut(df['annualised_irr_rate'], bins=irr_buckets, labels=irr_labels, right=True)\n", " df['irr_buckets'] = df['irr_buckets'].fillna('1) Up to 16%')\n", " 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 df\n", " group by 1;\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", " 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", " 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", "\n", " \n", "\n", "\n", " # Bureau Score\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", " df['bureau_score_buckets'] = pd.cut(df['bureau_score'], bins=score_buckets, labels=score_labels, right=True)\n", " df['bureau_score_buckets'] = df['bureau_score_buckets'].cat.add_categories('Score NA')\n", " df['bureau_score_buckets'] = df['bureau_score_buckets'].fillna('Score NA')\n", " 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 df\n", " group by 1;\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", " 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", " 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", "\n", "\n", "\n", "\n", " # Employee Status\n", " emp_status_dict = {\n", " 'Salaried' : ['SALARIED', 'PENSIONER', 'UNEMPLOYED'],\n", " 'Self Employed' : ['BUSINESS', 'HOUSEWIFE', 'Self Employed - Business', 'Self Employed - Professional',np.nan]\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", " df['employment_status_buckets'] = df['employee_status'].apply(emp_status_category)\n", " 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 df\n", " group by 1;\n", " \"\"\"\n", " emp_status_portfolio_cuts = duckdb.query(qry).df().fillna(0).sort_values(by='Total Principal Outstanding (In Crs)',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", " 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", " 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", "\n", "\n", "\n", "\n", " # Monthly Income \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", " df['monthly_income_buckets'] = pd.cut(df['monthly_income'], bins=income_buckets, labels=income_labels, right=True)\n", " 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 df\n", " group by 1;\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", " 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", " 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", " \n", "\n", "\n", "\n", " # Counts\n", " cat = file_name.split('_portfolio_cuts_script_output.xlsx')[0]\n", " qry = f\"\"\" \n", " select\n", " '{cat}' 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 = 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) \"0-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 df;\n", " \"\"\"\n", " count_portfolio_cuts = duckdb.query(qry).df().fillna(0)\n", "\n", "\n", "\n", "\n", " # Save all dataframes to CSV\n", " empty_df = pd.DataFrame([[np.nan] * 1] * 3, columns=['']) # Empty separator dataframe\n", "\n", " with pd.ExcelWriter(f'./script/merchant_risk/output/{file_name}', engine='xlsxwriter') as writer:\n", " geography_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', index=False)\n", " empty_df.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_portfolio_cuts)+1, header=False, index=False)\n", " tenure_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_portfolio_cuts) + len(empty_df) + 2, index=False)\n", " empty_df.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + 3, header=False, index=False)\n", " ticket_size_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_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(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + 5, header=False, index=False)\n", " irr_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_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(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(irr_portfolio_cuts) + 11, header=False, index=False)\n", " bureau_score_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_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(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(irr_portfolio_cuts) + len(empty_df) + len(bureau_score_portfolio_cuts) + 13, header=False, index=False)\n", " emp_status_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_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(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_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", " monthly_income_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_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", " count_portfolio_cuts.to_excel(writer, sheet_name='Counts', index=False)\n", "\n", " except Exception as e:\n", " print(\"Error Occured: \",e)" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "residential_cuts_df = portfolio_cuts_df[portfolio_cuts_df['revised_type_of_customer'] == 'Residential']\n", "rural_cuts_df = portfolio_cuts_df[portfolio_cuts_df['revised_type_of_customer'] == 'Rural C&I MSME(Attachakki, Oil Expeller etc.)']" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "resi_rural_cuts_function(residential_cuts_df,f'residential_portfolio_cuts_script_{analysis_date}.xlsx')\n", "resi_rural_cuts_function(rural_cuts_df,f'rural_portfolio_cuts_script_{analysis_date}.xlsx')" ] }, { "cell_type": "markdown", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### MSME & Overall Solar Cuts" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "def msme_cuts_function(df,file_name,fintech_df):\n", "\n", " try:\n", " # Geography\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 & Kashmir', 'Jammu and Kashmir','Uttarakhand', 'Daman and Diu']\n", "\n", " df['state_buckets'] = df['current_address_state'].apply(lambda x: 'Others' if (x in other_states_list or pd.isna(x)) else x)\n", " 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 df\n", " group by 1;\n", " \"\"\"\n", " geography_portfolio_cuts = duckdb.query(qry).df().fillna(0).sort_values(by=\"Total Principal Outstanding (In Crs)\",ascending=False).reset_index(drop=True)\n", " geography_portfolio_cuts['Total PAR (In Crs)'] = geography_portfolio_cuts[[\"1-30\",\"31-60\",\"61-90\",\"91-120\",\"121-150\",\"151-180\",\"181-364\"]].sum(axis=1)\n", " geography_portfolio_cuts = geography_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", " # add other solar channel financing count and amount in 'Other category'\n", " geography_portfolio_cuts.loc[geography_portfolio_cuts['State'] == 'Others', 'Number of Loans'] += fintech_df.outstanding_count.loc[1:].sum()\n", " geography_portfolio_cuts.loc[geography_portfolio_cuts['State'] == 'Others', 'Total Principal Outstanding (In Crs)'] += fintech_df.principal_outstanding_amount.loc[1:].sum()\n", " geography_portfolio_cuts.loc[geography_portfolio_cuts['State'] == 'Others', 'Principal without overdues (In Crs)'] += fintech_df.principal_outstanding_amount.loc[1:].sum()\n", " totals = geography_portfolio_cuts.sum(numeric_only=True)\n", " totals_row = pd.Series(['Total'] + totals.tolist(), index=geography_portfolio_cuts.columns)\n", " geography_portfolio_cuts = pd.concat([geography_portfolio_cuts, totals_row.to_frame().T], ignore_index=True)\n", " geography_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]] = geography_portfolio_cuts.iloc[:, [2, 3, 6, 7, 8, 9, 10, 11, 12, 13]].apply(lambda x: round(x / 1e7, 2))\n", "\n", "\n", "\n", "\n", " # Tenure\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", " df['tenure_buckets'] = pd.cut(df['remaining_emi'], bins=tenure_buckets, labels=tenure_labels, right=True)\n", " 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 df\n", " group by 1;\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", " # add solar channel financing 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", " 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", " 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", "\n", "\n", "\n", "\n", " # Ticket size (loan amount wise distribution)\n", " 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", " df['ticket_size_buckets'] = pd.cut(df['net_disbursement_amount'], bins=ticket_size_buckets, labels=ticket_size_labels, right=True)\n", " df['ticket_size_buckets'] = df['ticket_size_buckets'].fillna('1) ₹ 0 to 50k')\n", " 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 df\n", " group by 1;\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", " # 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.outstanding_count.loc[0:].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.principal_outstanding_amount.loc[0:].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.principal_outstanding_amount.loc[0:].sum()\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", " 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", "\n", "\n", "\n", "\n", " # IRR( monthly irr)\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%', '5) 24% to 30%', '6) 30% and higher']\n", " df['irr_buckets'] = pd.cut(df['annualised_irr_rate'], bins=irr_buckets, labels=irr_labels, right=True)\n", " df['irr_buckets'] = df['irr_buckets'].fillna('1) Up to 16%')\n", "\n", " 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 df\n", " group by 1;\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", " # add SCF 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", " 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", " 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", "\n", " \n", "\n", "\n", " # Bureau Score\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", " df['bureau_score_buckets'] = pd.cut(df['bureau_score'], bins=score_buckets, labels=score_labels, right=True)\n", " df['bureau_score_buckets'] = df['bureau_score_buckets'].cat.add_categories('Score NA')\n", " df['bureau_score_buckets'] = df['bureau_score_buckets'].fillna('Score NA')\n", " 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 df\n", " group by 1;\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", " # add SCF 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", " 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", " 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", "\n", "\n", "\n", "\n", " # Employee Status\n", " emp_status_dict = {\n", " 'Salaried' : ['SALARIED', 'PENSIONER', 'UNEMPLOYED'],\n", " 'Self Employed' : ['BUSINESS', 'HOUSEWIFE', 'Self Employed - Business', 'Self Employed - Professional', np.nan]\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", " df['employment_status_buckets'] = df['employee_status'].apply(emp_status_category)\n", " 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 df\n", " group by 1;\n", " \"\"\"\n", " emp_status_portfolio_cuts = duckdb.query(qry).df().fillna(0).sort_values(by='Total Principal Outstanding (In Crs)',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", " # add SCF 'Self Employed 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", " 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", " 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", "\n", "\n", "\n", "\n", " # Monthly Income \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", " df['monthly_income_buckets'] = pd.cut(df['monthly_income'], bins=income_buckets, labels=income_labels, right=True)\n", " 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 df\n", " group by 1;\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", " # add SCF in '60k and highercategory'\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", " 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", " 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", " \n", "\n", " # Counts\n", " if file_name.startswith('solar'):\n", " qry = f\"\"\" \n", " select\n", " 'Solar' 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 = 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 <= 364) then application_id end) \"181-364\",\n", " 0 \"Greater than 364\"\n", " from df;\n", " \"\"\"\n", " else:\n", " qry = f\"\"\" \n", " select\n", " 'MSME' 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 = 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 <= 364) then application_id end) \"181-364\",\n", " 0 \"Greater than 364\"\n", " from df;\n", " \"\"\"\n", " count_portfolio_cuts = duckdb.query(qry).df().fillna(0)\n", " # Add SCF\n", " 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", "\n", "\n", "\n", "\n", " # Save all dataframes to CSV\n", " empty_df = pd.DataFrame([[np.nan] * 1] * 3, columns=['']) # Empty separator dataframe\n", " with pd.ExcelWriter(f'./script/merchant_risk/output/{file_name}', engine='xlsxwriter') as writer:\n", " geography_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', index=False)\n", " empty_df.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_portfolio_cuts)+1, header=False, index=False)\n", " tenure_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_portfolio_cuts) + len(empty_df) + 2, index=False)\n", " empty_df.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + 3, header=False, index=False)\n", " ticket_size_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_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(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + 5, header=False, index=False)\n", " irr_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_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(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(irr_portfolio_cuts) + 11, header=False, index=False)\n", " bureau_score_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_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(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_portfolio_cuts) + len(empty_df) + len(irr_portfolio_cuts) + len(empty_df) + len(bureau_score_portfolio_cuts) + 13, header=False, index=False)\n", " emp_status_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_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(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_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", " monthly_income_portfolio_cuts.to_excel(writer, sheet_name='Sheet1', startrow=len(geography_portfolio_cuts) + len(empty_df) + len(tenure_portfolio_cuts) + len(empty_df) + len(ticket_size_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", " count_portfolio_cuts.to_excel(writer, sheet_name='Counts', index=False)\n", "\n", " except Exception as e:\n", " print(\"Error Occured: \",e)" ] }, { "cell_type": "code", "execution_count": null, "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", "
merchant_categoryprincipal_outstanding_amountoutstanding_count
2Solar Channel Financing3212893421
\n", "
" ], "text/plain": [ " merchant_category principal_outstanding_amount outstanding_count\n", "2 Solar Channel Financing 32128934 21" ] }, "execution_count": 12, "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 = fintech_df[fintech_df['merchant_category']=='Solar Channel Financing']\n", "fintech_df" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "msme_cuts_df = portfolio_cuts_df[portfolio_cuts_df['revised_type_of_customer'] == 'Urban C&I MSME(Factories, Hospitals, Hotels, Offices, etc.)']\n", "msme_cuts_function(msme_cuts_df,f'msme_portfolio_cuts_script_{analysis_date}.xlsx',fintech_df)" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Overall Solar portfolio cuts\n", "\n", "msme_cuts_function(portfolio_cuts_df,f'solar_portfolio_cuts_script_{analysis_date}.xlsx',fintech_df)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.3" } }, "nbformat": 4, "nbformat_minor": 2 }