{ "cells": [ { "cell_type": "markdown", "id": "ab0f5223", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Import libraries & files" ] }, { "cell_type": "code", "execution_count": 1, "id": "be2b6933", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "import pandas as pd\n", "from datetime import timedelta, datetime\n", "import numpy as np\n", "import fnmatch, os, warnings, ast, swifter\n", "\n", "warnings.filterwarnings('ignore')\n", "pd.set_option('display.max_columns', None)\n", "pd.set_option('display.max_rows', None)" ] }, { "cell_type": "code", "execution_count": null, "id": "287a14b1", "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_before_adj*'):\n", " file_path = os.path.join(output_folder, file)" ] }, { "cell_type": "code", "execution_count": 3, "id": "18acc88d", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "bd765a3584ed40ed86644dbd4732f27a", "version_major": 2, "version_minor": 0 }, "text/plain": [ "Pandas Apply: 0%| | 0/65792 [00:00 90 # select all 90+ dpd cases\n", "filter2 = combined_df['application_id'].isin(write_off_cases_as_of_mar23) # exclude write_off cases as of mar'24\n", "filter3 = ~combined_df['write_off_flag'].isin([3]) # exclude fraud cases\n", "\n", "hold_eligible_pool = combined_df[filter1 & ~filter2 & filter3]" ] }, { "cell_type": "code", "execution_count": 9, "id": "b157e837", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# App level adj\n", "app_level_adj = hold_eligible_pool[['application_id','merchant_name','date_of_adjustment','current_dpd','principal_outstanding','first_unpaid_emi_date']]\n", "app_level_adj['date_of_adjustment'] = app_level_adj['first_unpaid_emi_date'] + timedelta(days = 89)\n", "app_level_adj.sort_values(['merchant_name', 'current_dpd', 'principal_outstanding'], ascending=[True, False, True],inplace=True)\n", "app_level_adj = pd.merge(app_level_adj,merchant_wise_adj[['merchant_name','total_adjustment_amount']],on='merchant_name',how='left')" ] }, { "cell_type": "code", "execution_count": null, "id": "149da472", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# this func gets total adjustment amount from merchant_wise_adj and adjust it for each application id in app_level_adj of respective merchant\n", "# it keeps adjusting until adjustment amount has become 0, or all the application_ids have been adjusted\n", "\n", "def calculate_adjustments(merchant_group):\n", " current_hold = merchant_group['total_adjustment_amount'].iloc[0] # consider total hold amount for merchant\n", " adjustments = []\n", " amount_available = []\n", "\n", " for index,row in merchant_group.iterrows():\n", " if current_hold <= 0: # if no hold amount then amt to be adjusted = 0\n", " amt_to_be_adjusted = 0\n", " elif current_hold >= row['principal_outstanding']: # if total hold amount greater than pos then deduct pos from total hold amount\n", " amt_to_be_adjusted = row['principal_outstanding']\n", " current_hold -= row['principal_outstanding']\n", " else: # if total hold amount less than pos then assign total hold amount\n", " amt_to_be_adjusted = current_hold\n", " current_hold = 0 # no hold amount left after this adjustment\n", "\n", " # Append values in list\n", " adjustments.append(amt_to_be_adjusted) \n", " amount_available.append(current_hold)\n", "\n", " # Append list in dataframe\n", " merchant_group['amt_to_be_adjusted'] = adjustments\n", " merchant_group['amount_available'] = amount_available\n", " return merchant_group\n", "\n", "final_app_level_hold = app_level_adj.groupby('merchant_name').apply(calculate_adjustments).drop(columns=['total_adjustment_amount']).reset_index(drop=True)\n", "final_app_level_hold = final_app_level_hold[final_app_level_hold['amt_to_be_adjusted'] > 0] # Excl cases where allocated amount = 0\n", "final_app_level_hold[['date_of_adjustment']] = final_app_level_hold[['date_of_adjustment']].apply(pd.to_datetime)\n", "final_app_level_hold['adjustment_flag'] = 1\n", "\n", "# output for reference\n", "final_app_level_hold.to_csv(f'./script/merchant_risk/output/app_level_adjustment.csv',index=False)\n", "# final_app_level_hold['amt_to_be_adjusted'].sum()" ] }, { "cell_type": "code", "execution_count": 11, "id": "ae1205d0", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Merge final_app_level_hold with combined_df\n", "final_master_data = pd.merge(combined_df,final_app_level_hold[['application_id','date_of_adjustment','amt_to_be_adjusted','adjustment_flag']],on='application_id',how='outer')\n", "\n", "# get not null,max values from both columns\n", "final_master_data['date_of_adjustment'] = final_master_data['date_of_adjustment_x'].combine_first(final_master_data['date_of_adjustment_y'])\n", "final_master_data['amt_to_be_adjusted'] = final_master_data[['amt_to_be_adjusted_x', 'amt_to_be_adjusted_y']].max(axis=1) # get max values from both columns\n", "final_master_data['adjustment_flag'] = final_master_data['adjustment_flag_x'].combine_first(final_master_data['adjustment_flag_y'])\n", "\n", "# drop not required columns\n", "final_master_data.drop(columns=['date_of_adjustment_x','amt_to_be_adjusted_x','adjustment_flag_x','date_of_adjustment_y','amt_to_be_adjusted_y',\n", " 'adjustment_flag_y','total_interest_paid','total_principal_paid','principal_outstanding','count_of_emi_paid',\n", " 'first_unpaid_emi_date','current_dpd','write_off_flag','write_off_date'], inplace=True)" ] }, { "cell_type": "markdown", "id": "565d3074", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### POS Calculation" ] }, { "cell_type": "code", "execution_count": 12, "id": "1d4022be", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "def pos_calculation(row):\n", " loan_principal = row['loan_principal']\n", " irr_rate = row['irr_rate'] / 100\n", " total_amount_paid = row['amount_paid_for_emi']\n", " application_status = row['application_status']\n", " emi_date_amount_list = row['emi_date_wise_amount_list'] # List of varying EMI dates & amount\n", " adjusted_amount = row['amt_to_be_adjusted']\n", " remaining_emi = len(list(emi_date_amount_list.keys()))\n", " \n", " # Initialize values\n", " beginning_balance = loan_principal\n", " total_interest_paid = 0\n", " total_principal_paid = 0\n", " count_of_emi_paid = 0\n", " \n", " if application_status in ['closed', 'cancelled']:\n", " pos = 0\n", " else:\n", " for period in range(1, remaining_emi + 1): #loop through 1st period to (remaining_emi)th period\n", " if period <= len(emi_date_amount_list):\n", " emi_date, emi_amount = list(emi_date_amount_list.items())[period-1] #EMI Amount keeps varying throughout loan schedule, hence fetch EMI amount for particular period from emi list\n", " emi_amount = int(emi_amount)\n", " else:\n", " emi_date = pd.NaT\n", " emi_amount = 0\n", " \n", " # ---- Interest Calculation ----\n", " interest_component = emi_amount if (beginning_balance * irr_rate) > emi_amount else (beginning_balance * irr_rate)\n", " interest_paid = round(min(total_amount_paid, interest_component),0) \n", " total_amount_paid = total_amount_paid - interest_paid # Subract interest component from total_amount\n", " \n", " # ---- Principal Calculation ----\n", " principal_component = max(emi_amount - interest_component, 0)\n", " principal_available = total_amount_paid + adjusted_amount # consider amt_to_be_adjusted here\n", " principal_paid = round(min(principal_available, principal_component), 0)\n", " \n", " # Deduct principal from total_amount_paid first, then from adjusted_amount if needed\n", " if principal_paid <= total_amount_paid:\n", " total_amount_paid -= principal_paid\n", " else:\n", " adjusted_draw = principal_paid - total_amount_paid\n", " total_amount_paid = 0\n", " adjusted_amount -= adjusted_draw\n", " \n", " total_interest_paid += interest_paid\n", " total_principal_paid += principal_paid\n", "\n", " ending_balance = beginning_balance - principal_paid\n", " beginning_balance = ending_balance\n", "\n", " # Count of EMI Paid logic (if 90% of emi paid or principal paid > principal_comp then increment by count by 1)\n", " if ((\n", " (round(interest_paid + principal_paid, 0) >= (emi_amount*0.9)) \n", " or (round(principal_paid) >= round(principal_component)) # this condition for adjustment as adjustment is only happening in principal\n", " ) and (emi_amount > 0)): # (emi_amount<0) condition for morat cases\n", " count_of_emi_paid += 1 \n", " \n", " # Stop if balance is fully paid\n", " if beginning_balance <= 0:\n", " break\n", " \n", " # Calculate the position (remaining principal)\n", " pos = loan_principal - total_principal_paid\n", " \n", " # Return results\n", " return pd.Series([total_interest_paid, total_principal_paid, pos, count_of_emi_paid])\n" ] }, { "cell_type": "code", "execution_count": 13, "id": "2de09549", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "13240c6be1e847d4aaa74d75d48875c3", "version_major": 2, "version_minor": 0 }, "text/plain": [ "Pandas Apply: 0%| | 0/65792 [00:00 0] # get only those emi_date where emi amount > 0 (excl. morat emis)\n", "\n", " # Iterate through the keys based on the count of EMIs paid\n", " for i in range(len(emi_dates_keys)):\n", " if i == count_of_emi_paid:\n", " unpaid_date = datetime.strptime(emi_dates_keys[i],'%d-%m-%Y')\n", " break\n", " \n", " return unpaid_date\n", "\n", "\n", "final_master_data['first_unpaid_emi_date'] = final_master_data.apply(first_unpaid_emi_date ,axis=1)" ] }, { "cell_type": "code", "execution_count": 15, "id": "cbacf9e6", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Convert all date columns to datetime\n", "final_master_data[['analysis_date','first_emi_date','loan_close_date','first_unpaid_emi_date']] = final_master_data[\n", " ['analysis_date','first_emi_date','loan_close_date','first_unpaid_emi_date']\n", "].apply(pd.to_datetime)\n", "\n", "# Remove timezone from columns\n", "final_master_data['loan_close_date'] = final_master_data['loan_close_date'].dt.tz_localize(None)\n", "\n", "# Convert application_id column to int\n", "final_master_data['application_id'] = final_master_data['application_id'].astype(int)" ] }, { "cell_type": "markdown", "id": "ea76a5a3", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Calculate current_dpd" ] }, { "cell_type": "code", "execution_count": 16, "id": "03dd8bd3", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "mask = (\n", " (final_master_data['principal_outstanding'] > 0) &\n", " (final_master_data['first_emi_date'] < final_master_data['analysis_date']) &\n", " (~final_master_data['application_status'].isin(['closed', 'cancelled', 'settled'])) &\n", " (final_master_data['first_unpaid_emi_date'] < final_master_data['analysis_date'])\n", ")\n", "\n", "final_master_data['current_dpd'] = 0\n", "final_master_data.loc[mask, 'current_dpd'] = (\n", " final_master_data.loc[mask, 'analysis_date'] - final_master_data.loc[mask, 'first_unpaid_emi_date']\n", ").dt.days" ] }, { "cell_type": "markdown", "id": "08026cfa", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Lender POS & DPD change" ] }, { "cell_type": "code", "execution_count": null, "id": "75e6a31e", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# POS & DPD for these lenders (Singularity Creditworld Private Limited, CAPRI GLOBAL CAPITAL LIMITED, Vivriti Capital Limited) is not available\n", "# in our system, we get in excel file from respective lenders.\n", "\n", "lender_df = pd.read_csv('./script/merchant_risk/input/lender_pos_dpd_change.csv',parse_dates=['first_emi_date','last_emi_date'])\n", "\n", "lender_df['first_emi_date'] = pd.to_datetime(lender_df['first_emi_date'], dayfirst=True, errors='coerce')\n", "lender_df['last_emi_date'] = pd.to_datetime(lender_df['last_emi_date'], dayfirst=True, errors='coerce')\n", "\n", "# lender_df.head()" ] }, { "cell_type": "code", "execution_count": 18, "id": "4a69d1da", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# set application_id as index for update() method to work\n", "final_master_data.set_index('application_id', inplace=True)\n", "lender_df.set_index('app_id', inplace=True)\n", "\n", "# this will update pos of all ids specified in lender_df\n", "final_master_data.update(lender_df[['principal_outstanding', 'current_dpd']])\n", "\n", "final_master_data.reset_index(inplace=True)" ] }, { "cell_type": "code", "execution_count": 19, "id": "e2cf17b2", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "final_master_data['first_unpaid_emi_date'] = np.where(final_master_data['current_dpd'] == 0, pd.NaT, final_master_data['first_unpaid_emi_date'])\n", "final_master_data['first_unpaid_emi_date'] = pd.to_datetime(final_master_data['first_unpaid_emi_date'], errors='coerce')\n", "final_master_data['first_unpaid_emi_date'] = final_master_data['first_unpaid_emi_date'].apply(lambda x: pd.NaT if x > final_master_data['analysis_date'][0] else x)" ] }, { "cell_type": "code", "execution_count": 20, "id": "4ce48a49", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# calculate first_unpaid_emi_date for all application_id in lender sheet by subracting current_dpd from analysis_date\n", "\n", "mask = (final_master_data['application_id'].isin(lender_df.index)) & (final_master_data['current_dpd'] > 0)\n", "\n", "final_master_data.loc[mask, 'first_unpaid_emi_date'] = final_master_data.loc[mask, 'analysis_date'] - pd.to_timedelta(final_master_data.loc[mask, 'current_dpd'], unit='D')\n", "\n", "# calculate count_of_emi_paid for all application_id in lender sheet by getting month difference between first_unpaid_emi_date - first_emi_date\n", "final_master_data.loc[mask, 'count_of_emi_paid'] = (\n", " (final_master_data.loc[mask, 'first_unpaid_emi_date'].dt.year - final_master_data.loc[mask, 'first_emi_date'].dt.year) * 12 +\n", " (final_master_data.loc[mask, 'first_unpaid_emi_date'].dt.month - final_master_data.loc[mask, 'first_emi_date'].dt.month)\n", ").clip(lower=0)" ] }, { "cell_type": "markdown", "id": "ac18449b", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Handle logic for Settlement cases" ] }, { "cell_type": "code", "execution_count": null, "id": "85a5a9d2", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# settlement cases are loss cases which are in dpd, but customer agrees to pay some amount and \n", "# then we mark the case as settled as further collections is to be done. dpd for these cases will be 0\n", "\n", "# Load settlement data\n", "settlement_ids = pd.read_excel('./script/merchant_risk/input/adjustments_data.xlsx', sheet_name=1)['application_id']\n", "settlement_dates = pd.to_datetime(pd.read_excel('./script/merchant_risk/input/adjustments_data.xlsx', sheet_name=1)['settlement_date'])\n", "\n", "# Identify matched and relevant rows\n", "mask = final_master_data['application_id'].isin(settlement_ids) & (final_master_data['current_dpd'] > 0)\n", "\n", "# Apply all updates at once\n", "final_master_data.loc[mask, ['application_status', 'current_dpd', 'first_unpaid_emi_date']] = ['settled', 0, pd.NaT]\n", "\n", "# Create a mapping from application_id to settlement_date\n", "settlement_map = dict(zip(settlement_ids, settlement_dates))\n", "\n", "# Assign settlement dates only for matched rows\n", "final_master_data.loc[mask, 'loan_close_date'] = final_master_data.loc[mask, 'application_id'].map(settlement_map)" ] }, { "cell_type": "markdown", "id": "0e6eccc6", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Handle logic for merchants with full collection" ] }, { "cell_type": "code", "execution_count": 22, "id": "9cd6dd5c", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "merchant_list = [\"Pristyn Care (GHV)\",\"PRISTYN CARE - REIMBURSEMENT INSURANCE\",\"Klub Works Private Limited\",\n", " \"Hexa Health (Insurance Reimbursement)\",\"EasyAspataal (Insurance Reimbursement)\",\n", " \"Ayu Health (Insurance Reimbursement)\",\"PRISTYN CARE - BNPL\",\"EasyAspataal - BNPL\",\n", " \"CLAIMBUDDY (Insurance Reimbursement)\"]" ] }, { "cell_type": "code", "execution_count": 23, "id": "f3a96b8c", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Change columns for above merchants as all collection received directly from merchants, and collection is not shown in system\n", "\n", "mask = final_master_data['merchant_name'].isin(merchant_list)\n", "\n", "final_master_data.loc[mask, ['current_dpd', 'principal_outstanding', 'first_unpaid_emi_date']] = [0, 0, pd.NaT]\n", "final_master_data.loc[mask, 'count_of_emi_paid'] = final_master_data.loc[mask, 'remaining_emi']\n", "final_master_data.loc[mask, 'total_principal_paid'] = final_master_data.loc[mask, 'emi_amount_expected']" ] }, { "cell_type": "markdown", "id": "233fd53f", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Change Pride lender pos to 0" ] }, { "cell_type": "code", "execution_count": 24, "id": "4413906b", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "final_master_data.loc[\n", " (final_master_data['lender_name'] == 'Pride Financial Services Private Limited') & \n", " (final_master_data['current_dpd'] == 0), \n", " 'principal_outstanding'\n", "] = 0" ] }, { "cell_type": "code", "execution_count": 25, "id": "ebce1ad4", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# if remaining_emi == count_of_emi_paid then make pos = 0\n", "\n", "def pos_fix(row):\n", " return 0 if row['remaining_emi'] == row['count_of_emi_paid'] and row['principal_outstanding'] > 0 else row['principal_outstanding']\n", "\n", "final_master_data['principal_outstanding'] = final_master_data.apply(pos_fix, axis=1)" ] }, { "cell_type": "markdown", "id": "91f8288b", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Handle logic for fraud cases" ] }, { "cell_type": "code", "execution_count": null, "id": "3dc74bc5", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# push following cases to write off (confirmed by collections team)\n", "\n", "# Load fraud application IDs\n", "fraud_ids = pd.read_excel('./script/merchant_risk/input/adjustments_data.xlsx', sheet_name=2)['application_id']\n", "\n", "# Mask for fraud cases with current_dpd > 0\n", "mask = final_master_data['application_id'].isin(fraud_ids) & (final_master_data['current_dpd'] > 0)\n", "\n", "# Set current_dpd to 365 for those cases\n", "final_master_data.loc[mask, 'current_dpd'] = 365" ] }, { "cell_type": "markdown", "id": "18704158", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Get Revised DPD, writeback amount, write off flag & write off date columns" ] }, { "cell_type": "code", "execution_count": null, "id": "130c9757", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Rename current DPD column\n", "final_master_data.rename(columns={'current_dpd': 'current_month_original_dpd'}, inplace=True)\n", "\n", "# Load and prepare previous month data\n", "previous_month_master = (\n", " pd.read_csv('./script/merchant_risk/input/previous_month_mr.csv', parse_dates=['write_off_date'], dayfirst=True)\n", " .rename(columns={\n", " 'current_dpd': 'previous_month_dpd',\n", " 'principal_outstanding': 'previous_month_pos',\n", " 'write_off_date': 'previous_write_off_date'\n", " })[['application_id', 'previous_month_dpd', 'previous_month_pos', 'previous_write_off_date']]\n", ")\n", "\n", "previous_month_master['previous_write_off_date'] = pd.to_datetime(\n", " previous_month_master['previous_write_off_date'], dayfirst=True, errors='coerce'\n", ").dt.date\n", "\n", "\n", "# Merge and fill missing DPDs\n", "final_master_data = final_master_data.merge(previous_month_master, on='application_id', how='left')\n", "final_master_data['previous_month_dpd'].fillna(0, inplace=True)" ] }, { "cell_type": "code", "execution_count": 28, "id": "b9db6276", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# if any app_id in previous month had dpd >= 365 then current month dpd will also be the same.\n", "# if any app_id in previous month had dpd >= 91 and in current month the dpd is between 0 and 90 then current month dpd will be same as previous month\n", "\n", "def current_month_revised_dpd(row):\n", " current_month_dpd = row['current_month_original_dpd']\n", " app_status = row['application_status']\n", " previous_month_dpd = row['previous_month_dpd']\n", "\n", " if (previous_month_dpd >= 365) & (app_status != \"settled\"):\n", " return previous_month_dpd\n", " elif (previous_month_dpd >= 91) & (0 < current_month_dpd < 91) & (app_status != \"settled\"):\n", " return previous_month_dpd\n", " else:\n", " return current_month_dpd\n", "\n", "\n", "final_master_data['current_month_revised_dpd'] = final_master_data.apply(current_month_revised_dpd,axis=1)" ] }, { "cell_type": "code", "execution_count": 29, "id": "933fb7d3", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Add DPD Buckets column\n", "final_master_data.drop(columns='dpd_buckets',inplace=True)\n", "conditions = [\n", " final_master_data['current_month_revised_dpd'] == 0,\n", " final_master_data['current_month_revised_dpd'] <= 30,\n", " final_master_data['current_month_revised_dpd'] <= 60,\n", " final_master_data['current_month_revised_dpd'] <= 90,\n", " final_master_data['current_month_revised_dpd'] <= 120,\n", " final_master_data['current_month_revised_dpd'] <= 150,\n", " final_master_data['current_month_revised_dpd'] <= 180,\n", " final_master_data['current_month_revised_dpd'] <= 364,\n", " final_master_data['current_month_revised_dpd'] >= 365\n", "]\n", "\n", "labels = [\n", " \"1. Current\", \"2. 1-30\", \"3. 31-60\", \"4. 61-90\", \"5. 91-120\",\n", " \"6. 121-150\", \"7. 151-180\", \"8. 181-364\", \"9. 365+\"\n", "]\n", "\n", "final_master_data['dpd_buckets'] = np.select(conditions, labels, default=\"Unknown\")" ] }, { "cell_type": "code", "execution_count": 30, "id": "a4edefba", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# if any app_id in previous month had dpd >= 365 then get previous month pos - current month pos else return 0\n", "def writeback_amount(row):\n", " previous_month_dpd = row['previous_month_dpd']\n", " previous_month_pos = row['previous_month_pos']\n", " app_status = row['application_status']\n", " current_month_pos = row['principal_outstanding']\n", " \n", " if (previous_month_dpd >= 365) & (app_status != \"settled\"):\n", " return max(0,previous_month_pos - current_month_pos)\n", " else:\n", " return 0\n", "\n", "\n", "final_master_data['writeback_amount'] = final_master_data.apply(writeback_amount,axis=1)" ] }, { "cell_type": "code", "execution_count": 31, "id": "e0a0e717", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "# Calculate Write off flag and Write off date\n", "\n", "# normal write off\n", "final_master_data['write_off_flag'] = final_master_data['current_month_revised_dpd'].apply(lambda x: 1 if x > 364 else 0)\n", "\n", "final_master_data['write_off_date'] = final_master_data.apply(\n", " lambda row: row['previous_write_off_date']\n", " if pd.notnull(row['previous_write_off_date'])\n", " else (row['first_unpaid_emi_date'] + pd.Timedelta(days=364)).date()\n", " if row['current_month_revised_dpd'] > 364\n", " else pd.NaT,\n", " axis=1\n", ")\n", "\n", "# merchant with full collection\n", "final_master_data.loc[final_master_data['merchant_name'].isin(merchant_list), ['write_off_date', 'write_off_flag']] = [pd.NaT, 0]\n", "\n", "# settlement cases\n", "final_master_data.loc[final_master_data['application_status'] == 'settled', ['write_off_date', 'write_off_flag']] = [pd.NaT, 2]\n", "\n", "# fraud cases\n", "final_master_data.loc[(final_master_data['application_id'].isin(fraud_ids)) & (final_master_data['current_month_revised_dpd'] > 364), \n", " ['write_off_date', 'write_off_flag']] = [datetime.strptime(\"31-03-2025\", \"%d-%m-%Y\").date(), 3]\n", "\n", "# Marketing king online - vedfin case push to write off\n", "final_master_data.loc[(final_master_data['application_id'] == 236220), \n", " ['write_off_date', 'write_off_flag','current_month_original_dpd','current_month_revised_dpd','dpd_buckets']] = [datetime.strptime(\"31-03-2025\", \"%d-%m-%Y\").date(), 1, 365, 365,\"9. 365+\"]" ] }, { "cell_type": "markdown", "id": "e5921fd2", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Checks" ] }, { "cell_type": "code", "execution_count": 32, "id": "4880c3c8", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "text/plain": [ "np.float64(100800921.0)" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "final_master_data[(final_master_data['current_month_revised_dpd'] > 364)]['principal_outstanding'].sum()" ] }, { "cell_type": "code", "execution_count": 33, "id": "6b54f242", "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", "
dpd_bucketsprincipal_outstanding
01. Current1.888266e+09
12. 1-306.754621e+07
23. 31-601.157084e+07
34. 61-909.709413e+06
45. 91-1206.738613e+06
56. 121-1509.531165e+06
67. 151-1804.931361e+06
78. 181-3643.586167e+07
89. 365+1.008009e+08
\n", "
" ], "text/plain": [ " dpd_buckets principal_outstanding\n", "0 1. Current 1.888266e+09\n", "1 2. 1-30 6.754621e+07\n", "2 3. 31-60 1.157084e+07\n", "3 4. 61-90 9.709413e+06\n", "4 5. 91-120 6.738613e+06\n", "5 6. 121-150 9.531165e+06\n", "6 7. 151-180 4.931361e+06\n", "7 8. 181-364 3.586167e+07\n", "8 9. 365+ 1.008009e+08" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "final_master_data.groupby('dpd_buckets').agg({'principal_outstanding':'sum'}).reset_index()" ] }, { "cell_type": "code", "execution_count": 34, "id": "fcb26bc5", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "text/plain": [ "np.float64(1464732.0)" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "final_master_data['writeback_amount'].sum()" ] }, { "cell_type": "code", "execution_count": 35, "id": "10a3883f", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "text/plain": [ "principal_outstanding 2.025496e+09\n", "dtype: float64" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "final_master_data[final_master_data['write_off_flag'] == 0].agg({'principal_outstanding':'sum'})" ] }, { "cell_type": "markdown", "id": "e4a234af", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "#### Extract CSV" ] }, { "cell_type": "code", "execution_count": 36, "id": "f0868926", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "final_master_data.drop(columns=['previous_month_pos','previous_write_off_date'], inplace=True)" ] }, { "cell_type": "code", "execution_count": null, "id": "364489b4", "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "analysis_date = datetime.strftime(final_master_data['analysis_date'][0],'%b-%Y')\n", "final_master_data.to_csv(f'./script/merchant_risk/output/merchant_risk_after_adj_{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 }