02_monthly_card_data_processing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Monthly Card Data Processing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook processes 12 months of scraped card transaction data from a website. It filters irrelevant columns, unmerges cells, aggregates volume and value per transaction mode, removes specific banks, tags remaining banks, and cleans empty rows/columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing required libraries\n",
    "import os\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function to perform necessary cleaning\n",
    "def clean_and_save_df(df, output_name, output_directory):\n",
    "    # Rename columns to A to AC as in Excel\n",
    "    df.columns = [chr(ord('A') + i // 26 - 1) + chr(ord('A') + i % 26) if i >= 26 else chr(ord('A') + i) for i in range(29)]\n",
    "\n",
    "    # Drop top 9 rows\n",
    "    df = df.drop(df.index[:9]).reset_index(drop=True)\n",
    "\n",
    "    # Get the starting index for deleting bottom rows from payment banks\n",
    "    del_from_payment_banks_index = df.index[df['B'].str.contains('Payment Banks', na=False)].tolist()\n",
    "\n",
    "    # Find the maximum index in the list\n",
    "    max_index = max(del_from_payment_banks_index, default=-1)\n",
    "\n",
    "    # Replace df with the subset of rows excluding del_from_index and all rows below it\n",
    "    df = df.loc[:max_index].reset_index(drop=True)\n",
    "\n",
    "    # Remove rows that had only side heading for bank type\n",
    "    df = df.dropna(thresh=df.shape[1] - 5).reset_index(drop=True)\n",
    "\n",
    "    # To remove credit cards - Cash Withdrawal and other Infrastructure data\n",
    "    columns_to_delete = ['A', 'B', 'D', 'E', 'F', 'G', 'H', 'I', 'R', 'S']\n",
    "    df = df.drop(columns=columns_to_delete, axis=1)\n",
    "\n",
    "    # To remove debit cards - Cash Withdrawal data\n",
    "    df = df.iloc[:, :-4]\n",
    "\n",
    "    # Rename columns to follow a standardized naming convention for better clarity and consistency\n",
    "    df.columns = ['bank_name', 'credit_cards', 'debit_cards', 'cc_pos_vol(in actuals)',\n",
    "                  'cc_pos_value(in 1000Rs)', 'cc_online_vol(in actuals)', 'cc_online_value(in 1000Rs)',\n",
    "                  'cc_others_vol(in actuals)', 'cc_others_value(in 1000Rs)', 'dc_pos_vol(in actuals)',\n",
    "                  'dc_pos_value(in 1000Rs)', 'dc_online_vol(in actuals)', 'dc_online_value(in 1000Rs)',\n",
    "                  'dc_others_vol(in actuals)', 'dc_others_value(in 1000Rs)']\n",
    "\n",
    "    # Lists of bank type and their corresponding banks\n",
    "    # List of Public Sector Banks\n",
    "    public_sector_banks = [\n",
    "        'BANK OF BARODA', 'BANK OF INDIA', 'BANK OF MAHARASHTRA', 'CANARA BANK', \n",
    "        'CENTRAL BANK OF INDIA', 'INDIAN BANK', 'INDIAN OVERSEAS BANK', 'PUNJAB AND SIND BANK', \n",
    "        'PUNJAB NATIONAL BANK', 'STATE BANK OF INDIA', 'UCO BANK', 'UNION BANK OF INDIA'\n",
    "    ]\n",
    "\n",
    "    # List of Private Sector Banks\n",
    "    private_sector_banks = [\n",
    "        'AXIS BANK LTD', 'BANDHAN BANK LTD', 'CATHOLIC SYRIAN BANK LTD', 'CITY UNION BANK', \n",
    "        'CITY UNION BANK LTD.', 'CSB BANK LTD', 'CSB BANK LTD.', 'DCB BANK LTD',\n",
    "        'DHANALAKSHMI BANK LTD', 'FEDERAL BANK LTD', 'HDFC BANK LTD', 'ICICI BANK LTD', \n",
    "        'IDBI BANK LTD', 'IDBI LTD', 'IDFC Bank Limited', 'IDFC FIRST BANK LTD', 'INDUSIND BANK LTD', \n",
    "        'JAMMU AND KASHMIR BANK', 'JAMMU AND KASHMIR BANK LTD', 'KARNATAKA BANK LTD', 'KARUR VYSYA BANK LTD', \n",
    "        'KOTAK MAHINDRA BANK LTD', 'NAINITAL BANK LTD', 'RATNAKAR BANK LIMITED', 'RBL BANK LTD', 'SOUTH INDIAN BANK', \n",
    "        'TAMILNAD MERCANTILE BANK LTD', 'YES BANK LTD'\n",
    "    ]\n",
    "\n",
    "    # List of Foreign Banks\n",
    "    foreign_banks = [\n",
    "        'AMERICAN EXPRESS','AMERICAN EXPRESS BANKING CORPORATION','BANK OF AMERICA','BARCLAYS BANK PLC',\n",
    "        'CITI BANK','DBS BANK','DBS INDIA BANK LTD','DEUTSCHE BANK LTD','HONGKONG AND SHANGHAI BKG CORPN',\n",
    "        'HSBC LTD','SBM Bank India','SBM BANK INDIA LTD','STANDARD CHARTERED BANK LTD','WOORI BANK'\n",
    "    ]\n",
    "\n",
    "    # Assign Bank Type based on bank names\n",
    "    df.loc[df['bank_name'].isin(public_sector_banks), 'Bank Type'] = 'Public Sector Banks'\n",
    "    df.loc[df['bank_name'].isin(private_sector_banks), 'Bank Type'] = 'Private Sector Banks'\n",
    "    df.loc[df['bank_name'].isin(foreign_banks), 'Bank Type'] = 'Foreign Banks'\n",
    "\n",
    "    # Reorder columns\n",
    "    df = df[['bank_name', 'Bank Type', 'credit_cards', 'debit_cards', 'cc_pos_vol(in actuals)',\n",
    "             'cc_pos_value(in 1000Rs)', 'cc_online_vol(in actuals)', 'cc_online_value(in 1000Rs)',\n",
    "             'cc_others_vol(in actuals)', 'cc_others_value(in 1000Rs)', 'dc_pos_vol(in actuals)',\n",
    "             'dc_pos_value(in 1000Rs)', 'dc_online_vol(in actuals)', 'dc_online_value(in 1000Rs)',\n",
    "             'dc_others_vol(in actuals)', 'dc_others_value(in 1000Rs)']]\n",
    "\n",
    "    # Save the DataFrame as a CSV file\n",
    "    output_file = os.path.join(output_directory, f\"{output_name}.csv\")\n",
    "\n",
    "    while os.path.exists(output_file):\n",
    "        output_file = os.path.join(output_directory, f\"{output_name}.csv\")\n",
    "\n",
    "    df.to_csv(output_file, index=False)\n",
    "    print(f\"DataFrame saved as: {output_file}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame saved as: cleaned_data/2022APRIL.csv\n",
      "DataFrame saved as: cleaned_data/2022AUGUST.csv\n",
      "DataFrame saved as: cleaned_data/2022DECEMBER.csv\n",
      "DataFrame saved as: cleaned_data/2022JULY.csv\n",
      "DataFrame saved as: cleaned_data/2022JUNE.csv\n",
      "DataFrame saved as: cleaned_data/2022MAY.csv\n",
      "DataFrame saved as: cleaned_data/2022NOVEMBER.csv\n",
      "DataFrame saved as: cleaned_data/2022OCTOBER.csv\n",
      "DataFrame saved as: cleaned_data/2022SEPT.csv\n",
      "DataFrame saved as: cleaned_data/2023FEBRUARY.csv\n",
      "DataFrame saved as: cleaned_data/2023JAN.csv\n",
      "DataFrame saved as: cleaned_data/2023MARCH.csv\n"
     ]
    }
   ],
   "source": [
    "# source and destination directories\n",
    "source_dir = \"cleaning_data/\"\n",
    "destination_dir = \"cleaned_data/\"\n",
    "# List all Excel files in the source directory\n",
    "excel_files = [file for file in os.listdir(source_dir) if file.endswith('.XLSX')]\n",
    "# Loop through each Excel file\n",
    "for excel_file in excel_files:\n",
    "    # Construct the full file path\n",
    "    excel_path = os.path.join(source_dir, excel_file)\n",
    "\n",
    "    # Read Excel file into a DataFrame\n",
    "    df = pd.read_excel(excel_path)\n",
    "\n",
    "    # Extract the file name without extension for output\n",
    "    output_name = os.path.splitext(excel_file)[0]\n",
    "\n",
    "    # Call the processing function\n",
    "    clean_and_save_df(df, output_name, destination_dir)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}