{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "95dca539-4902-48c8-9fd9-98058645a07c",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Merchant Risk before adjustment Script\n",
    "\n",
    "This script takes the total amount paid by customer(excl. bounce,penal charges) & adjusts the amount from the start emi to either upto the end emi or until full total amount paid is not utilized."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d912681-7072-4f60-9d90-647d91acf6b5",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Import Libraries & files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ffea500d",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "import numpy as np\n",
    "from dotenv import load_dotenv\n",
    "from sqlalchemy import create_engine\n",
    "from script.merchant_risk.notebooks.sql_query_file import master_query_function\n",
    "\n",
    "import warnings,os, ast, swifter\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": "8ee3a9ac",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name 'date_arg' is not defined\n"
     ]
    }
   ],
   "source": [
    "load_dotenv()\n",
    "\n",
    "connection_string = f\"mysql+pymysql://{os.getenv('MYSQL_USER')}:{os.getenv('MYSQL_PASSWORD')}@{os.getenv('MYSQL_HOST')}:9090/{os.getenv('MYSQL_DB')}\"\n",
    "\n",
    "engine = create_engine(connection_string)\n",
    "try:\n",
    "    master_data_query = master_query_function(date_arg)\n",
    "    with engine.connect() as connection:\n",
    "        df = pd.read_sql(master_data_query, connection)\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "1609d5e1",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "cc4078cc328642309bf7e8cee6bc5a1a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Pandas Apply:   0%|          | 0/650 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a3998f0e9ec44b26803cf8307d0d73d5",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Pandas Apply:   0%|          | 0/616 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_master = df.copy()\n",
    "#df_master = pd.read_csv('../input/master_data.csv',parse_dates=['analysis_date','disbursement_date','first_emi_date','loan_close_date','last_emi_date'], \n",
    "#                        dayfirst=True)\n",
    "\n",
    "columns_for_string_to_int = ['application_id','emi_amount','approved_amount','upfront_from_customer','emi_amount_expected',\n",
    "                             'net_disbursement_amount','loan_principal','total_amount_paid_by_customer','amount_paid_for_emi']\n",
    "\n",
    "# Apply the operation to each column\n",
    "for column in columns_for_string_to_int:\n",
    "    df_master[column] = df_master[column].replace({',': ''}, regex=True).fillna(0).astype(float)\n",
    "\n",
    "def is_valid_literal(val):\n",
    "\n",
    "    if val is None or not isinstance(val, str) or val.strip() == '':\n",
    "\n",
    "        return False\n",
    "\n",
    "    try:\n",
    "\n",
    "        ast.literal_eval(val)\n",
    "\n",
    "        return True\n",
    "\n",
    "    except (ValueError, SyntaxError):\n",
    "\n",
    "        return False\n",
    " \n",
    "# Filter only rows with valid string literals\n",
    "\n",
    "df_master = df_master[df_master['emi_date_wise_amount_list'].swifter.apply(is_valid_literal)]\n",
    " \n",
    "# Now safely evaluate the string\n",
    "\n",
    "df_master['emi_date_wise_amount_list'] = df_master['emi_date_wise_amount_list'].swifter.apply(ast.literal_eval)\n",
    " \n",
    "#df_master['emi_date_wise_amount_list'] = df_master['emi_date_wise_amount_list'].swifter.apply(ast.literal_eval)\n",
    "\n",
    "def sort_dict_by_date(d):\n",
    "    return {k: v for k, v in sorted(d.items(), key=lambda item: datetime.strptime(item[0], '%d-%m-%Y'))}\n",
    "\n",
    "# Apply sorting to each row in the 'emi_date_wise_amount_list' column\n",
    "df_master['emi_date_wise_amount_list'] = df_master['emi_date_wise_amount_list'].apply(sort_dict_by_date)\n",
    "\n",
    "# df_master.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e184a798",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### POS Calculation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "0aba462b",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Calculate Principal Outstanding\n",
    "def pos_calculation(row):\n",
    "    loan_principal = row['loan_principal']\n",
    "    irr_rate = float(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",
    "    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",
    "            # Calculate the interest component for this period\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",
    "            # Calculate the principal component\n",
    "            principal_component = max(int(emi_amount) - interest_component,0)\n",
    "            principal_paid = round(min(total_amount_paid, principal_component),0) # principal component = emi amount - interest comp\n",
    "            total_amount_paid = total_amount_paid - principal_paid # Subract principal component from total_amount\n",
    "            \n",
    "            total_interest_paid += interest_paid # keep adding interest paid\n",
    "            total_principal_paid += principal_paid # keep adding principal paid\n",
    "\n",
    "            ending_balance = beginning_balance - principal_paid\n",
    "            beginning_balance = ending_balance\n",
    "\n",
    "            # If interest comp + principal comp = emi_amount(i.e if emi fully paid) then increment count_of_emi_paid \n",
    "            if (round(interest_paid + principal_paid, 0) >= (emi_amount*0.9)) and (emi_amount > 0):\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": 87,
   "id": "19907d8c",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "bd8876fef46e4175bf3a19ec14b4b4e4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Pandas Apply:   0%|          | 0/616 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_master[['total_interest_paid','total_principal_paid','principal_outstanding','count_of_emi_paid']] = df_master.swifter.apply(pos_calculation ,axis=1)\n",
    "df_master[\"principal_outstanding\"] = df_master[\"principal_outstanding\"].round().clip(lower=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9168695",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Calculate first_unpaid_emi_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "239cbe1e",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# From emi_dates_list extract (count_of_emi_paid + 1)th date as first_unpaid_emi_date\n",
    "def first_unpaid_emi_date(row):\n",
    "    emi_dates_list = row['emi_date_wise_amount_list']\n",
    "    count_of_emi_paid = int(row['count_of_emi_paid'])\n",
    "    unpaid_date = None\n",
    "\n",
    "    # Convert the dictionary keys to a list\n",
    "    emi_dates_keys = [k for k, v in emi_dates_list.items() if v > 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",
    "df_master['first_unpaid_emi_date'] = df_master.apply(first_unpaid_emi_date ,axis=1)\n",
    "df_master['first_unpaid_emi_date'] = pd.to_datetime(df_master['first_unpaid_emi_date'], errors='coerce')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e65396f6",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Calculate curent_dpd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "c614189c",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "mask = (\n",
    "    (df_master['principal_outstanding'] > 0) &\n",
    "    (df_master['first_emi_date'] < df_master['analysis_date']) &\n",
    "    (~df_master['application_status'].isin(['closed', 'cancelled', 'settled'])) &\n",
    "    (df_master['first_unpaid_emi_date'] < df_master['analysis_date'])\n",
    ")\n",
    "\n",
    "df_master['analysis_date'] = pd.to_datetime(df_master['analysis_date'], errors='coerce')\n",
    "df_master['current_dpd'] = 0\n",
    "df_master.loc[mask, 'current_dpd'] = (\n",
    "    df_master.loc[mask, 'analysis_date'] - df_master.loc[mask, 'first_unpaid_emi_date']\n",
    ").dt.days"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "483495f7",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Lender POS & DPD change"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "996df65e",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: './script/merchant_risk/input/lender_pos_dpd_change.csv'",
     "output_type": "error",
     "traceback": [
      "\u001B[0;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[0;31mFileNotFoundError\u001B[0m                         Traceback (most recent call last)",
      "Cell \u001B[0;32mIn[90], line 4\u001B[0m\n\u001B[1;32m      1\u001B[0m \u001B[38;5;66;03m# POS & DPD for these lenders (Singularity Creditworld Private Limited, CAPRI GLOBAL CAPITAL LIMITED, Vivriti Capital Limited) is not available\u001B[39;00m\n\u001B[1;32m      2\u001B[0m \u001B[38;5;66;03m#  in our system, we get in excel file from respective lenders.\u001B[39;00m\n\u001B[0;32m----> 4\u001B[0m lender_df \u001B[38;5;241m=\u001B[39m \u001B[43mpd\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mread_csv\u001B[49m\u001B[43m(\u001B[49m\u001B[38;5;124;43m'\u001B[39;49m\u001B[38;5;124;43m./script/merchant_risk/input/lender_pos_dpd_change.csv\u001B[39;49m\u001B[38;5;124;43m'\u001B[39;49m\u001B[43m,\u001B[49m\u001B[43mparse_dates\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43m[\u001B[49m\u001B[38;5;124;43m'\u001B[39;49m\u001B[38;5;124;43mfirst_emi_date\u001B[39;49m\u001B[38;5;124;43m'\u001B[39;49m\u001B[43m,\u001B[49m\u001B[38;5;124;43m'\u001B[39;49m\u001B[38;5;124;43mlast_emi_date\u001B[39;49m\u001B[38;5;124;43m'\u001B[39;49m\u001B[43m]\u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m      6\u001B[0m lender_df[\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mfirst_emi_date\u001B[39m\u001B[38;5;124m'\u001B[39m] \u001B[38;5;241m=\u001B[39m pd\u001B[38;5;241m.\u001B[39mto_datetime(lender_df[\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mfirst_emi_date\u001B[39m\u001B[38;5;124m'\u001B[39m], dayfirst\u001B[38;5;241m=\u001B[39m\u001B[38;5;28;01mTrue\u001B[39;00m, errors\u001B[38;5;241m=\u001B[39m\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mcoerce\u001B[39m\u001B[38;5;124m'\u001B[39m)\n\u001B[1;32m      7\u001B[0m lender_df[\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mlast_emi_date\u001B[39m\u001B[38;5;124m'\u001B[39m] \u001B[38;5;241m=\u001B[39m pd\u001B[38;5;241m.\u001B[39mto_datetime(lender_df[\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mlast_emi_date\u001B[39m\u001B[38;5;124m'\u001B[39m], dayfirst\u001B[38;5;241m=\u001B[39m\u001B[38;5;28;01mTrue\u001B[39;00m, errors\u001B[38;5;241m=\u001B[39m\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mcoerce\u001B[39m\u001B[38;5;124m'\u001B[39m)\n",
      "File \u001B[0;32m~/Sites/cf-python-scripts/venv/lib/python3.9/site-packages/pandas/io/parsers/readers.py:1026\u001B[0m, in \u001B[0;36mread_csv\u001B[0;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, date_format, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, on_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options, dtype_backend)\u001B[0m\n\u001B[1;32m   1013\u001B[0m kwds_defaults \u001B[38;5;241m=\u001B[39m _refine_defaults_read(\n\u001B[1;32m   1014\u001B[0m     dialect,\n\u001B[1;32m   1015\u001B[0m     delimiter,\n\u001B[0;32m   (...)\u001B[0m\n\u001B[1;32m   1022\u001B[0m     dtype_backend\u001B[38;5;241m=\u001B[39mdtype_backend,\n\u001B[1;32m   1023\u001B[0m )\n\u001B[1;32m   1024\u001B[0m kwds\u001B[38;5;241m.\u001B[39mupdate(kwds_defaults)\n\u001B[0;32m-> 1026\u001B[0m \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[43m_read\u001B[49m\u001B[43m(\u001B[49m\u001B[43mfilepath_or_buffer\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mkwds\u001B[49m\u001B[43m)\u001B[49m\n",
      "File \u001B[0;32m~/Sites/cf-python-scripts/venv/lib/python3.9/site-packages/pandas/io/parsers/readers.py:620\u001B[0m, in \u001B[0;36m_read\u001B[0;34m(filepath_or_buffer, kwds)\u001B[0m\n\u001B[1;32m    617\u001B[0m _validate_names(kwds\u001B[38;5;241m.\u001B[39mget(\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mnames\u001B[39m\u001B[38;5;124m\"\u001B[39m, \u001B[38;5;28;01mNone\u001B[39;00m))\n\u001B[1;32m    619\u001B[0m \u001B[38;5;66;03m# Create the parser.\u001B[39;00m\n\u001B[0;32m--> 620\u001B[0m parser \u001B[38;5;241m=\u001B[39m \u001B[43mTextFileReader\u001B[49m\u001B[43m(\u001B[49m\u001B[43mfilepath_or_buffer\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;241;43m*\u001B[39;49m\u001B[38;5;241;43m*\u001B[39;49m\u001B[43mkwds\u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m    622\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m chunksize \u001B[38;5;129;01mor\u001B[39;00m iterator:\n\u001B[1;32m    623\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m parser\n",
      "File \u001B[0;32m~/Sites/cf-python-scripts/venv/lib/python3.9/site-packages/pandas/io/parsers/readers.py:1620\u001B[0m, in \u001B[0;36mTextFileReader.__init__\u001B[0;34m(self, f, engine, **kwds)\u001B[0m\n\u001B[1;32m   1617\u001B[0m     \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39moptions[\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mhas_index_names\u001B[39m\u001B[38;5;124m\"\u001B[39m] \u001B[38;5;241m=\u001B[39m kwds[\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mhas_index_names\u001B[39m\u001B[38;5;124m\"\u001B[39m]\n\u001B[1;32m   1619\u001B[0m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mhandles: IOHandles \u001B[38;5;241m|\u001B[39m \u001B[38;5;28;01mNone\u001B[39;00m \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;01mNone\u001B[39;00m\n\u001B[0;32m-> 1620\u001B[0m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_engine \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_make_engine\u001B[49m\u001B[43m(\u001B[49m\u001B[43mf\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mengine\u001B[49m\u001B[43m)\u001B[49m\n",
      "File \u001B[0;32m~/Sites/cf-python-scripts/venv/lib/python3.9/site-packages/pandas/io/parsers/readers.py:1880\u001B[0m, in \u001B[0;36mTextFileReader._make_engine\u001B[0;34m(self, f, engine)\u001B[0m\n\u001B[1;32m   1878\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mb\u001B[39m\u001B[38;5;124m\"\u001B[39m \u001B[38;5;129;01mnot\u001B[39;00m \u001B[38;5;129;01min\u001B[39;00m mode:\n\u001B[1;32m   1879\u001B[0m         mode \u001B[38;5;241m+\u001B[39m\u001B[38;5;241m=\u001B[39m \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mb\u001B[39m\u001B[38;5;124m\"\u001B[39m\n\u001B[0;32m-> 1880\u001B[0m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mhandles \u001B[38;5;241m=\u001B[39m \u001B[43mget_handle\u001B[49m\u001B[43m(\u001B[49m\n\u001B[1;32m   1881\u001B[0m \u001B[43m    \u001B[49m\u001B[43mf\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1882\u001B[0m \u001B[43m    \u001B[49m\u001B[43mmode\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1883\u001B[0m \u001B[43m    \u001B[49m\u001B[43mencoding\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43moptions\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mget\u001B[49m\u001B[43m(\u001B[49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[38;5;124;43mencoding\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;28;43;01mNone\u001B[39;49;00m\u001B[43m)\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1884\u001B[0m \u001B[43m    \u001B[49m\u001B[43mcompression\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43moptions\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mget\u001B[49m\u001B[43m(\u001B[49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[38;5;124;43mcompression\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;28;43;01mNone\u001B[39;49;00m\u001B[43m)\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1885\u001B[0m \u001B[43m    \u001B[49m\u001B[43mmemory_map\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43moptions\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mget\u001B[49m\u001B[43m(\u001B[49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[38;5;124;43mmemory_map\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;28;43;01mFalse\u001B[39;49;00m\u001B[43m)\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1886\u001B[0m \u001B[43m    \u001B[49m\u001B[43mis_text\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mis_text\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1887\u001B[0m \u001B[43m    \u001B[49m\u001B[43merrors\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43moptions\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mget\u001B[49m\u001B[43m(\u001B[49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[38;5;124;43mencoding_errors\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[38;5;124;43mstrict\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[43m)\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1888\u001B[0m \u001B[43m    \u001B[49m\u001B[43mstorage_options\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43moptions\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mget\u001B[49m\u001B[43m(\u001B[49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[38;5;124;43mstorage_options\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;28;43;01mNone\u001B[39;49;00m\u001B[43m)\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1889\u001B[0m \u001B[43m\u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m   1890\u001B[0m \u001B[38;5;28;01massert\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mhandles \u001B[38;5;129;01mis\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m \u001B[38;5;28;01mNone\u001B[39;00m\n\u001B[1;32m   1891\u001B[0m f \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mhandles\u001B[38;5;241m.\u001B[39mhandle\n",
      "File \u001B[0;32m~/Sites/cf-python-scripts/venv/lib/python3.9/site-packages/pandas/io/common.py:873\u001B[0m, in \u001B[0;36mget_handle\u001B[0;34m(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)\u001B[0m\n\u001B[1;32m    868\u001B[0m \u001B[38;5;28;01melif\u001B[39;00m \u001B[38;5;28misinstance\u001B[39m(handle, \u001B[38;5;28mstr\u001B[39m):\n\u001B[1;32m    869\u001B[0m     \u001B[38;5;66;03m# Check whether the filename is to be opened in binary mode.\u001B[39;00m\n\u001B[1;32m    870\u001B[0m     \u001B[38;5;66;03m# Binary mode does not support 'encoding' and 'newline'.\u001B[39;00m\n\u001B[1;32m    871\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m ioargs\u001B[38;5;241m.\u001B[39mencoding \u001B[38;5;129;01mand\u001B[39;00m \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mb\u001B[39m\u001B[38;5;124m\"\u001B[39m \u001B[38;5;129;01mnot\u001B[39;00m \u001B[38;5;129;01min\u001B[39;00m ioargs\u001B[38;5;241m.\u001B[39mmode:\n\u001B[1;32m    872\u001B[0m         \u001B[38;5;66;03m# Encoding\u001B[39;00m\n\u001B[0;32m--> 873\u001B[0m         handle \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mopen\u001B[39;49m\u001B[43m(\u001B[49m\n\u001B[1;32m    874\u001B[0m \u001B[43m            \u001B[49m\u001B[43mhandle\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m    875\u001B[0m \u001B[43m            \u001B[49m\u001B[43mioargs\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mmode\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m    876\u001B[0m \u001B[43m            \u001B[49m\u001B[43mencoding\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mioargs\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mencoding\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m    877\u001B[0m \u001B[43m            \u001B[49m\u001B[43merrors\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43merrors\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m    878\u001B[0m \u001B[43m            \u001B[49m\u001B[43mnewline\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[43m,\u001B[49m\n\u001B[1;32m    879\u001B[0m \u001B[43m        \u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m    880\u001B[0m     \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[1;32m    881\u001B[0m         \u001B[38;5;66;03m# Binary mode\u001B[39;00m\n\u001B[1;32m    882\u001B[0m         handle \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mopen\u001B[39m(handle, ioargs\u001B[38;5;241m.\u001B[39mmode)\n",
      "\u001B[0;31mFileNotFoundError\u001B[0m: [Errno 2] No such file or directory: './script/merchant_risk/input/lender_pos_dpd_change.csv'"
     ]
    }
   ],
   "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": null,
   "id": "0689d935",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# set application_id as index for update() method to work\n",
    "df_master.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",
    "df_master.update(lender_df[['principal_outstanding', 'current_dpd']])\n",
    "\n",
    "df_master.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "420d36fd",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "df_master['first_unpaid_emi_date'] = np.where(df_master['current_dpd'] == 0, pd.NaT, df_master['first_unpaid_emi_date'])\n",
    "df_master['first_unpaid_emi_date'] = pd.to_datetime(df_master['first_unpaid_emi_date'], errors='coerce')\n",
    "df_master['first_unpaid_emi_date'] = df_master['first_unpaid_emi_date'].apply(lambda x: pd.NaT if x > df_master['analysis_date'][0] else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1eb7787f",
   "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 = (df_master['application_id'].isin(lender_df.index)) & (df_master['current_dpd'] > 0)\n",
    "\n",
    "df_master.loc[mask, 'first_unpaid_emi_date'] = df_master.loc[mask, 'analysis_date'] - pd.to_timedelta(df_master.loc[mask, 'current_dpd'], unit='D')\n",
    "\n",
    "df_master['first_emi_date'] = pd.to_datetime(df_master['first_emi_date'], errors='coerce')\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",
    "df_master.loc[mask, 'count_of_emi_paid'] = (\n",
    "    (df_master.loc[mask, 'first_unpaid_emi_date'].dt.year - df_master.loc[mask, 'first_emi_date'].dt.year) * 12 +\n",
    "    (df_master.loc[mask, 'first_unpaid_emi_date'].dt.month - df_master.loc[mask, 'first_emi_date'].dt.month)\n",
    ").clip(lower=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7408b1e1",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Change lender from K.M to Mufin/HLF-PTC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "111a906a",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# some application_id are of Mufin & HLF-PTC lenders, but in system those are not changed & marked as K.M. Global cases.\n",
    "lender_change_df = pd.read_excel('./script/merchant_risk/input/lender_name_change_file.xlsx')\n",
    "df_master = pd.merge(df_master,lender_change_df,on='application_id',how='left')\n",
    "df_master['lender_name_x'] = df_master.apply(lambda row: row['lender_name_y'] if pd.notna(row['lender_name_y']) else row['lender_name_x'], axis=1)\n",
    "df_master = df_master.rename(columns={'lender_name_x':'lender_name'}).drop(columns='lender_name_y',axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b338e85",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Handle logic for Settlement cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "213ec2b1",
   "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 = df_master['application_id'].isin(settlement_ids) & (df_master['current_dpd'] > 0)\n",
    "\n",
    "# Apply all updates at once\n",
    "df_master.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",
    "df_master.loc[mask, 'loan_close_date'] = df_master.loc[mask, 'application_id'].map(settlement_map)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9181a672",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Handle logic for merchants with full collection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd2c33e8",
   "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": null,
   "id": "e0481e11",
   "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 = df_master['merchant_name'].isin(merchant_list)\n",
    "\n",
    "df_master.loc[mask, ['current_dpd', 'principal_outstanding', 'first_unpaid_emi_date']] = [0, 0, pd.NaT]\n",
    "df_master.loc[mask, 'count_of_emi_paid'] = df_master.loc[mask, 'remaining_emi']\n",
    "df_master.loc[mask, 'total_principal_paid'] = df_master.loc[mask, 'emi_amount_expected']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "217d6294",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Change Pride lender POS to 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "122a1288",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "df_master.loc[\n",
    "    (df_master['lender_name'] == 'Pride Financial Services Private Limited') & \n",
    "    (df_master['current_dpd'] == 0), \n",
    "    'principal_outstanding'\n",
    "] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e2a42a3",
   "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",
    "df_master['principal_outstanding'] = df_master.apply(pos_fix, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca70c531",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Handle logic for fraud cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efd887cb",
   "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 = df_master['application_id'].isin(fraud_ids) & (df_master['current_dpd'] > 0)\n",
    "\n",
    "# Set current_dpd to 365 for those cases\n",
    "df_master.loc[mask, 'current_dpd'] = 365"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14712aab",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Add DPD buckets, Write off flag & Write off date columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6816e43b",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Add DPD Buckets column\n",
    "\n",
    "conditions = [\n",
    "    df_master['current_dpd'] == 0,\n",
    "    df_master['current_dpd'] <= 30,\n",
    "    df_master['current_dpd'] <= 60,\n",
    "    df_master['current_dpd'] <= 90,\n",
    "    df_master['current_dpd'] <= 120,\n",
    "    df_master['current_dpd'] <= 150,\n",
    "    df_master['current_dpd'] <= 180,\n",
    "    df_master['current_dpd'] <= 364,\n",
    "    df_master['current_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",
    "df_master['dpd_buckets'] = np.select(conditions, labels, default=\"Unknown\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4b7891e",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Calculate Write off flag and Write off date\n",
    "\n",
    "# normal write off\n",
    "df_master['write_off_flag'] = df_master['current_dpd'].apply(lambda x: 1 if x > 364 else 0)\n",
    "\n",
    "df_master['write_off_date'] = df_master.apply(\n",
    "    lambda row: (row['first_unpaid_emi_date'] + pd.Timedelta(days=364)).date()\n",
    "    if row['current_dpd'] > 364\n",
    "    else pd.NaT,\n",
    "    axis=1\n",
    ")\n",
    "\n",
    "# merchant with full collection\n",
    "df_master.loc[df_master['merchant_name'].isin(merchant_list), ['write_off_date', 'write_off_flag']] = [pd.NaT, 0]\n",
    "\n",
    "# settlement cases\n",
    "df_master.loc[df_master['application_status'] == 'settled', ['write_off_date', 'write_off_flag']] = [pd.NaT, 2]\n",
    "\n",
    "# fraud cases\n",
    "df_master.loc[(df_master['application_id'].isin(fraud_ids)) & (df_master['current_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",
    "df_master.loc[(df_master['application_id'] == 236220), \n",
    "                      ['write_off_date', 'write_off_flag','current_dpd','dpd_buckets']] = [datetime.strptime(\"31-03-2025\", \"%d-%m-%Y\").date(), 1, 365, \"9. 365+\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eae23e7c",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Extract CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27518d6e",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "for cols in ['analysis_date','disbursement_date','first_emi_date','loan_close_date','write_off_date','last_emi_date']:\n",
    "    df_master[cols] = pd.to_datetime(df_master[cols], errors='coerce')\n",
    "\n",
    "analysis_date = datetime.strftime(df_master['analysis_date'][0],'%b-%Y')\n",
    "df_master.to_csv(f'./script/merchant_risk/output/merchant_risk_before_adj_{analysis_date}.csv',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "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.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}