week-5/preppin_data_2023_05.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Transaction Code</th>\n",
       "      <th>Value</th>\n",
       "      <th>Customer Code</th>\n",
       "      <th>Online or In-Person</th>\n",
       "      <th>Transaction Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DTB-716-679-576</td>\n",
       "      <td>1448</td>\n",
       "      <td>100001</td>\n",
       "      <td>2</td>\n",
       "      <td>20/03/2023 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DS-795-814-303</td>\n",
       "      <td>7839</td>\n",
       "      <td>100001</td>\n",
       "      <td>2</td>\n",
       "      <td>15/11/2023 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DSB-807-592-406</td>\n",
       "      <td>5520</td>\n",
       "      <td>100005</td>\n",
       "      <td>1</td>\n",
       "      <td>14/07/2023 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DS-367-545-264</td>\n",
       "      <td>7957</td>\n",
       "      <td>100007</td>\n",
       "      <td>2</td>\n",
       "      <td>18/08/2023 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DSB-474-374-857</td>\n",
       "      <td>5375</td>\n",
       "      <td>100000</td>\n",
       "      <td>2</td>\n",
       "      <td>26/08/2023 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Transaction Code  Value  Customer Code  Online or In-Person  \\\n",
       "0  DTB-716-679-576   1448         100001                    2   \n",
       "1   DS-795-814-303   7839         100001                    2   \n",
       "2  DSB-807-592-406   5520         100005                    1   \n",
       "3   DS-367-545-264   7957         100007                    2   \n",
       "4  DSB-474-374-857   5375         100000                    2   \n",
       "\n",
       "      Transaction Date  \n",
       "0  20/03/2023 00:00:00  \n",
       "1  15/11/2023 00:00:00  \n",
       "2  14/07/2023 00:00:00  \n",
       "3  18/08/2023 00:00:00  \n",
       "4  26/08/2023 00:00:00  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./data/input/PD 2023 Wk 1 Input.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\josev\\AppData\\Local\\Temp\\ipykernel_16224\\2768670467.py:7: UserWarning: Parsing dates in %d/%m/%Y %H:%M:%S format when dayfirst=False (the default) was specified. Pass `dayfirst=True` or specify a format to silence this warning.\n",
      "  .assign(Date = lambda x: pd.to_datetime(x['Transaction Date']))\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Bank</th>\n",
       "      <th>Month</th>\n",
       "      <th>Month_Num</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DS</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "      <td>50207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DS</td>\n",
       "      <td>February</td>\n",
       "      <td>2</td>\n",
       "      <td>31204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DS</td>\n",
       "      <td>March</td>\n",
       "      <td>3</td>\n",
       "      <td>36799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DS</td>\n",
       "      <td>April</td>\n",
       "      <td>4</td>\n",
       "      <td>40785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DS</td>\n",
       "      <td>May</td>\n",
       "      <td>5</td>\n",
       "      <td>38715</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Bank     Month  Month_Num  Value\n",
       "0   DS   January          1  50207\n",
       "1   DS  February          2  31204\n",
       "2   DS     March          3  36799\n",
       "3   DS     April          4  40785\n",
       "4   DS       May          5  38715"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a new field 'Bank' containing the Bank name from the Transaction Code field\n",
    "monthly_transactions = ( df\n",
    "    # Create the bank code column\n",
    "    .assign(Bank = lambda x: x['Transaction Code'].str.split('-').str[0])\n",
    "    # Change transaction date to the month of the transaction\n",
    "    .assign(Date = lambda x: pd.to_datetime(x['Transaction Date']))\n",
    "    .assign(Month = lambda x: x['Date'].dt.month_name())\n",
    "    .assign(Month_Num = lambda x: x['Date'].dt.month)\n",
    "    .drop(columns = ['Transaction Date', 'Date'])\n",
    "    # Calculate the total value of transactions per bank per month\n",
    "    .groupby(['Bank', 'Month', 'Month_Num'], as_index = False)\n",
    "    ['Value'].sum()\n",
    "    # Sort the data by bank and month\n",
    "    .sort_values(['Bank', 'Month_Num'])\n",
    "    .reset_index(drop = True)\n",
    "    # Keep only the required columns\n",
    "    [['Bank', 'Month', 'Month_Num', 'Value']]\n",
    ")\n",
    "\n",
    "# Show the first 5 rows of the prepared data\n",
    "monthly_transactions.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Calculations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "( monthly_transactions\n",
    "    # Rank the banks by the total value of transactions per month\n",
    "    .assign(Rank=lambda x: x.groupby('Month')['Value'].rank(method='first', ascending=False).astype(int))\n",
    "    # Add a new column for average transaction value\n",
    "    .assign(Avg_Transaction_Value=lambda x: x.groupby('Rank')['Value'].transform('mean'))\n",
    "    # Add a new column for average rank by bank\n",
    "    .assign(Avg_Rank=lambda x: x.groupby('Bank')['Rank'].transform('mean'))\n",
    "    # Sort the data by month and rank\n",
    "    .sort_values(['Month_Num', 'Rank'])\n",
    "    .reset_index(drop=True)\n",
    "    # Keep only the required columns\n",
    "    [['Month', 'Bank', 'Value', 'Rank', 'Avg_Rank', 'Avg_Transaction_Value']]\n",
    "    # Rename the columns\n",
    "    .rename(columns={\n",
    "        'Rank': 'Bank Rank per Month',\n",
    "        'Month': 'Transaction Date',\n",
    "        'Avg_Transaction_Value': 'Avg Transaction Value per Value',\n",
    "        'Avg_Rank': 'Avg Rank by Bank'\n",
    "    })\n",
    "    .to_csv('./data/output/output_2023_05.csv', index=False, quoting=1, quotechar='\"', sep=';')\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "preppin-data",
   "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.10.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}