week-1/preppin_data_2023_01.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./data/input/PD_2023_Wk_1_Input.csv')\n",
    "df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\josev\\AppData\\Local\\Temp\\ipykernel_16340\\3986880112.py:10: 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(Day = 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>Transaction Code</th>\n",
       "      <th>Value</th>\n",
       "      <th>Customer Code</th>\n",
       "      <th>Online or In-Person</th>\n",
       "      <th>Bank</th>\n",
       "      <th>Day</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>In-Person</td>\n",
       "      <td>DTB</td>\n",
       "      <td>Monday</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>In-Person</td>\n",
       "      <td>DS</td>\n",
       "      <td>Wednesday</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>Online</td>\n",
       "      <td>DSB</td>\n",
       "      <td>Friday</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>In-Person</td>\n",
       "      <td>DS</td>\n",
       "      <td>Friday</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>In-Person</td>\n",
       "      <td>DSB</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Transaction Code  Value  Customer Code Online or In-Person Bank        Day\n",
       "0  DTB-716-679-576   1448         100001           In-Person  DTB     Monday\n",
       "1   DS-795-814-303   7839         100001           In-Person   DS  Wednesday\n",
       "2  DSB-807-592-406   5520         100005              Online  DSB     Friday\n",
       "3   DS-367-545-264   7957         100007           In-Person   DS     Friday\n",
       "4  DSB-474-374-857   5375         100000           In-Person  DSB   Saturday"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a new field 'Bank' containing the Bank name from the Transaction Code field\n",
    "df = (\n",
    "    df\n",
    "    .assign(Bank = lambda x: x['Transaction Code'].str.split('-').str[0])\n",
    "    .replace(\n",
    "        {\n",
    "            'Online or In-Person': {1: 'Online', 2: 'In-Person'}\n",
    "        }\n",
    "    )\n",
    "    .assign(Day = lambda x: pd.to_datetime(x['Transaction Date']))\n",
    "    .assign(Day = lambda x: x['Day'].dt.day_name())\n",
    "    # Drop the original date column\n",
    "    .drop(columns = ['Transaction Date'])\n",
    ")\n",
    "\n",
    "# Show the first 5 rows of the output\n",
    "df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Calculations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Otput 1: Sum of the transaction values by Bank\n",
    "( df\n",
    "    .groupby('Bank', as_index=False)\n",
    "    ['Value'].sum()\n",
    "    .to_csv(path_or_buf='./data/output/output_2023_01_1.csv', index=False, quoting=1, quotechar='\"', sep=';')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Output 2: Sum of the transaction values by Bank, Day and Transaction Type\n",
    "( df\n",
    "    .groupby(['Bank', 'Day', 'Online or In-Person'], as_index=False)\n",
    "    ['Value'].sum()\n",
    "    .to_csv(path_or_buf='./data/output/output_2023_01_2.csv', index=False, quoting=1, quotechar='\"', sep=';')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Output 3: Sum of the transaction values by Bank and Customer\n",
    "( df\n",
    "    .groupby(['Bank', 'Customer Code'], as_index=False)\n",
    "    ['Value'].sum()\n",
    "    .to_csv(path_or_buf='./data/output/output_2023_01_3.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
}