Friday_Purchases_II.ipynb

{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "# Friday Purchases II using Pandas\n",
    "\n",
    "Fridays have always been a popular day for shopping, whether it's payday splurges, weekend preparations, or early holiday shopping in November. But how much do people actually spend on Fridays, and how does it vary throughout the month?\n",
    "\n",
    "In this analysis, we take a closer look at consumer spending trends on Fridays in November 2023. By filtering transaction data to isolate purchases made on Fridays, we aim to uncover patterns in weekly spending and determine whether certain Fridays see more significant activity than others.\n",
    "\n",
    "Using Python and Pandas, we efficiently extract all Fridays from the month, categorize them by their respective weeks, and calculate total spending for each Friday. The result? A structured dataset that reveals intriguing insights into Friday spending behavior.\n",
    "\n",
    "Join us as we walk through the process, from data filtering to aggregation, and discover how purchase patterns shift across different weeks. Whether you're a data enthusiast, a business analyst, or just curious about consumer behavior, this deep dive will provide valuable takeaways!\n",
    "\n",
    "**Table: Purchases**\n",
    "\n",
    "| Column Name   | Type |\n",
    "|---------------|------|\n",
    "| user_id       | int  |\n",
    "| purchase_date | date |\n",
    "| amount_spend  | int  |\n",
    "\n",
    "(user_id, purchase_date, amount_spend) is the primary key (combination of columns with unique values) for this table.\n",
    "purchase_date will range from November 1, 2023, to November 30, 2023, inclusive of both dates.\n",
    "Each row contains user id, purchase date, and amount spend.\n",
    "Write a solution to calculate the total spending by users on each Friday of every week in November 2023. If there are no purchases on a particular Friday of a week, it will be considered as 0.\n",
    "\n",
    "Return the result table ordered by week of month in ascending order.\n",
    "\n",
    "The result format is in the following example.\n",
    "\n",
    "\n",
    "Example 1:\n",
    "\n",
    "Input:\n",
    "**Purchases table:**\n",
    "\n",
    "| user_id | purchase_date | amount_spend |\n",
    "|---------|---------------|--------------|\n",
    "| 11      | 2023-11-07    | 1126         |\n",
    "| 15      | 2023-11-30    | 7473         |\n",
    "| 17      | 2023-11-14    | 2414         |\n",
    "| 12      | 2023-11-24    | 9692         |\n",
    "| 8       | 2023-11-03    | 5117         |\n",
    "| 1       | 2023-11-16    | 5241         |\n",
    "| 10      | 2023-11-12    | 8266         |\n",
    "| 13      | 2023-11-24    | 12000        |\n",
    "\n",
    "Output:\n",
    "\n",
    "| week_of_month | purchase_date | total_amount |\n",
    "|---------------|---------------|--------------|\n",
    "| 1             | 2023-11-03    | 5117         |\n",
    "| 2             | 2023-11-10    | 0            |\n",
    "| 3             | 2023-11-17    | 0            |\n",
    "| 4             | 2023-11-24    | 21692        |\n",
    "\n",
    "Explanation:\n",
    "- During the first week of November 2023, transactions amounting to \\$5,117 occurred on Friday, 2023-11-03.\n",
    "- For the second week of November 2023, there were no transactions on Friday, 2023-11-10, resulting in a value of 0 in the output table for that day.\n",
    "- Similarly, during the third week of November 2023, there were no transactions on Friday, 2023-11-17, reflected as 0 in the output table for that specific day.\n",
    "- In the fourth week of November 2023, two transactions took place on Friday, 2023-11-24, amounting to \\$12,000 and \\$9,692 respectively, summing up to a total of \\$21,692.\n",
    "Output table is ordered by week_of_month in ascending order."
   ],
   "id": "704b9b4202ce9af3"
  },
  {
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:48.572304Z",
     "start_time": "2025-03-14T17:55:48.553310Z"
    }
   },
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "\n",
    "data = [[11, '2023-11-07', 1126],\n",
    "        [15, '2023-11-30', 7473],\n",
    "        [17, '2023-11-14', 2414],\n",
    "        [12, '2023-11-24', 9692],\n",
    "        [8, '2023-11-03', 5117],\n",
    "        [1, '2023-11-16', 5241],\n",
    "        [10, '2023-11-12', 8266],\n",
    "        [13, '2023-11-24', 12000]]\n",
    "purchases = pd.DataFrame(data,\n",
    "                         columns=['user_id',\n",
    "                                  'purchase_date',\n",
    "                                  'amount_spend']).astype({\n",
    "                                  'user_id':'Int64',\n",
    "                                  'purchase_date':'datetime64[ns]',\n",
    "                                  'amount_spend':'Int64'})\n",
    "display(purchases)"
   ],
   "id": "initial_id",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   user_id purchase_date  amount_spend\n",
       "0       11    2023-11-07          1126\n",
       "1       15    2023-11-30          7473\n",
       "2       17    2023-11-14          2414\n",
       "3       12    2023-11-24          9692\n",
       "4        8    2023-11-03          5117\n",
       "5        1    2023-11-16          5241\n",
       "6       10    2023-11-12          8266\n",
       "7       13    2023-11-24         12000"
      ],
      "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>user_id</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>amount_spend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>2023-11-07</td>\n",
       "      <td>1126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15</td>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>7473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17</td>\n",
       "      <td>2023-11-14</td>\n",
       "      <td>2414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>9692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>5117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>2023-11-16</td>\n",
       "      <td>5241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10</td>\n",
       "      <td>2023-11-12</td>\n",
       "      <td>8266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>13</td>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>12000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 28
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 1: Generate a Date Range for November 2023**\n",
    "- This creates a range of dates from November 1, 2023, to November 30, 2023, with a daily frequency.\n",
    "- A DataFrame df is created with a column purchase_date containing all these dates."
   ],
   "id": "c342ee40324faae8"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:48.657763Z",
     "start_time": "2025-03-14T17:55:48.630243Z"
    }
   },
   "cell_type": "code",
   "source": [
    "date_range = pd.date_range(start='2023-11-01', end='2023-11-30', freq='D')\n",
    "df = pd.DataFrame({'purchase_date': date_range})\n",
    "display(df.head())\n",
    "display(df.tail())"
   ],
   "id": "d419156645195317",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "  purchase_date\n",
       "0    2023-11-01\n",
       "1    2023-11-02\n",
       "2    2023-11-03\n",
       "3    2023-11-04\n",
       "4    2023-11-05"
      ],
      "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>purchase_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-11-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-11-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-11-05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "   purchase_date\n",
       "25    2023-11-26\n",
       "26    2023-11-27\n",
       "27    2023-11-28\n",
       "28    2023-11-29\n",
       "29    2023-11-30"
      ],
      "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>purchase_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2023-11-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2023-11-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2023-11-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2023-11-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2023-11-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 29
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 2: Extract the Day of the Week**\n",
    "- A new column day is added to df, containing the name of the day (e.g., \"Monday\", \"Tuesday\", etc.) for each date."
   ],
   "id": "e5e194d85ca849de"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:48.771940Z",
     "start_time": "2025-03-14T17:55:48.758Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df[\"day\"] = df[\"purchase_date\"].dt.day_name()\n",
    "display(df.head(7))"
   ],
   "id": "86c43de00e3e1c2b",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "  purchase_date        day\n",
       "0    2023-11-01  Wednesday\n",
       "1    2023-11-02   Thursday\n",
       "2    2023-11-03     Friday\n",
       "3    2023-11-04   Saturday\n",
       "4    2023-11-05     Sunday\n",
       "5    2023-11-06     Monday\n",
       "6    2023-11-07    Tuesday"
      ],
      "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>purchase_date</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-11-01</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-11-02</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-11-04</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-11-05</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2023-11-06</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2023-11-07</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 30
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 3: Filter for Fridays Only**\n",
    "- The DataFrame df is filtered to retain only the rows where the day is \"Friday\"."
   ],
   "id": "9a6511b4a94a909"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:48.918998Z",
     "start_time": "2025-03-14T17:55:48.907324Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df = df[df[\"day\"]==\"Friday\"]\n",
    "display(df)"
   ],
   "id": "26ce423c5a12f07f",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   purchase_date     day\n",
       "2     2023-11-03  Friday\n",
       "9     2023-11-10  Friday\n",
       "16    2023-11-17  Friday\n",
       "23    2023-11-24  Friday"
      ],
      "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>purchase_date</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2023-11-10</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2023-11-17</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 31
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 4: Assign a Week Number to Each Friday**\n",
    "- A new column week_of_month is created, numbering each Friday sequentially (1 for the first Friday, 2 for the second, etc.)."
   ],
   "id": "5c8da78c21776d51"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:49.135147Z",
     "start_time": "2025-03-14T17:55:49.121335Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df[\"week_of_month\"] = [i+1 for i in range(df.shape[0])]\n",
    "display(df)"
   ],
   "id": "b66b390aad2dc443",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   purchase_date     day  week_of_month\n",
       "2     2023-11-03  Friday              1\n",
       "9     2023-11-10  Friday              2\n",
       "16    2023-11-17  Friday              3\n",
       "23    2023-11-24  Friday              4"
      ],
      "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>purchase_date</th>\n",
       "      <th>day</th>\n",
       "      <th>week_of_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>Friday</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2023-11-10</td>\n",
       "      <td>Friday</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2023-11-17</td>\n",
       "      <td>Friday</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>Friday</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 32
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 5: Convert Dates to String Format**\n",
    "- The purchase_date column is converted from datetime format to string format."
   ],
   "id": "f4fb09083a46abc1"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:49.267414Z",
     "start_time": "2025-03-14T17:55:49.255444Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df[\"purchase_date\"] = df[\"purchase_date\"].astype(str)\n",
    "display(df)"
   ],
   "id": "cde67aaf11038826",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   purchase_date     day  week_of_month\n",
       "2     2023-11-03  Friday              1\n",
       "9     2023-11-10  Friday              2\n",
       "16    2023-11-17  Friday              3\n",
       "23    2023-11-24  Friday              4"
      ],
      "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>purchase_date</th>\n",
       "      <th>day</th>\n",
       "      <th>week_of_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>Friday</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2023-11-10</td>\n",
       "      <td>Friday</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2023-11-17</td>\n",
       "      <td>Friday</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>Friday</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 33
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 6: Create a List of Fridays**\n",
    "- A list friday_list is created, containing only the string representations of the Fridays in November."
   ],
   "id": "58bff7b69494b7cf"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:49.399295Z",
     "start_time": "2025-03-14T17:55:49.388881Z"
    }
   },
   "cell_type": "code",
   "source": [
    "friday_list = df[\"purchase_date\"].tolist()\n",
    "print(friday_list)"
   ],
   "id": "ea62400d4c83506",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['2023-11-03', '2023-11-10', '2023-11-17', '2023-11-24']\n"
     ]
    }
   ],
   "execution_count": 34
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 7: Filter Purchases for Fridays Only**\n",
    "- The purchase_date column in purchases is converted to a string format.\n",
    "- The purchases DataFrame is filtered to keep only those rows where purchase_date is in friday_list, meaning only purchases made on Fridays are retained."
   ],
   "id": "7d06245c18ab759c"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:49.554187Z",
     "start_time": "2025-03-14T17:55:49.526757Z"
    }
   },
   "cell_type": "code",
   "source": [
    "purchases[\"purchase_date\"] = purchases[\"purchase_date\"].astype(str)\n",
    "purchases = purchases[purchases[\"purchase_date\"].astype(str).isin(friday_list)]\n",
    "display(purchases)"
   ],
   "id": "9e516e6736ed9e1c",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   user_id purchase_date  amount_spend\n",
       "3       12    2023-11-24          9692\n",
       "4        8    2023-11-03          5117\n",
       "7       13    2023-11-24         12000"
      ],
      "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>user_id</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>amount_spend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>9692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>5117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>13</td>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>12000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 35
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 8: Maintain Friday Order in Purchases**\n",
    "- The purchase_date column is converted into a categorical variable, with categories explicitly set to friday_list.\n",
    "- This ensures that the Fridays appear in the correct order when sorting or grouping."
   ],
   "id": "e7afac5a79a8c8ae"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:49.672131Z",
     "start_time": "2025-03-14T17:55:49.645166Z"
    }
   },
   "cell_type": "code",
   "source": [
    "purchases[\"purchase_date\"] = purchases[\"purchase_date\"].astype(\"category\").cat.set_categories(friday_list)\n",
    "display(purchases)"
   ],
   "id": "60964339e4d8f7a8",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   user_id purchase_date  amount_spend\n",
       "3       12    2023-11-24          9692\n",
       "4        8    2023-11-03          5117\n",
       "7       13    2023-11-24         12000"
      ],
      "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>user_id</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>amount_spend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>9692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>5117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>13</td>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>12000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 36
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 9: Group Purchases by Date and Sum the Amounts**\n",
    "- The purchases DataFrame is grouped by purchase_date, and the total amount_spend is summed for each Friday.\n",
    "- The result is a DataFrame with purchase_date and total amount spent per Friday."
   ],
   "id": "4732f469d6193efd"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:49.766110Z",
     "start_time": "2025-03-14T17:55:49.754615Z"
    }
   },
   "cell_type": "code",
   "source": [
    "purchases = purchases.groupby([\"purchase_date\"], observed=False)[\"amount_spend\"].sum().reset_index()\n",
    "display(purchases)"
   ],
   "id": "f71e015b92d85249",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "  purchase_date  amount_spend\n",
       "0    2023-11-03          5117\n",
       "1    2023-11-10             0\n",
       "2    2023-11-17             0\n",
       "3    2023-11-24         21692"
      ],
      "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>purchase_date</th>\n",
       "      <th>amount_spend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>5117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-11-10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-11-17</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>21692</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 37
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 10: Rename Column for Clarity**\n",
    "- The column amount_spend is renamed to total_amount for better readability"
   ],
   "id": "a93d03450f79c3df"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:49.890465Z",
     "start_time": "2025-03-14T17:55:49.872220Z"
    }
   },
   "cell_type": "code",
   "source": [
    "purchases = purchases.rename(columns={\"amount_spend\": \"total_amount\"})\n",
    "display(purchases)"
   ],
   "id": "7b97f7bfea12c68",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "  purchase_date  total_amount\n",
       "0    2023-11-03          5117\n",
       "1    2023-11-10             0\n",
       "2    2023-11-17             0\n",
       "3    2023-11-24         21692"
      ],
      "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>purchase_date</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>5117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-11-10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-11-17</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>21692</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 38
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 11: Merge with the Original Friday DataFrame**\n",
    "- The DataFrame purchases is merged with df (which contains week_of_month), ensuring that each row also has the corresponding week number."
   ],
   "id": "6a5e3a6882fa50b5"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:50.072290Z",
     "start_time": "2025-03-14T17:55:50.043910Z"
    }
   },
   "cell_type": "code",
   "source": [
    "purchases = purchases.merge(df, how=\"left\")\n",
    "display(purchases)"
   ],
   "id": "bd108b7a8256218f",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "  purchase_date  total_amount     day  week_of_month\n",
       "0    2023-11-03          5117  Friday              1\n",
       "1    2023-11-10             0  Friday              2\n",
       "2    2023-11-17             0  Friday              3\n",
       "3    2023-11-24         21692  Friday              4"
      ],
      "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>purchase_date</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>day</th>\n",
       "      <th>week_of_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>5117</td>\n",
       "      <td>Friday</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-11-10</td>\n",
       "      <td>0</td>\n",
       "      <td>Friday</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-11-17</td>\n",
       "      <td>0</td>\n",
       "      <td>Friday</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>21692</td>\n",
       "      <td>Friday</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 39
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 12: Select Final Columns**\n",
    "- The final DataFrame retains only three columns:\n",
    "- week_of_month (week number of the month for the Friday)\n",
    "- purchase_date (date of the Friday)\n",
    "- total_amount (total spend on that Friday)"
   ],
   "id": "d8f4192ba6754a17"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-03-14T17:55:50.221039Z",
     "start_time": "2025-03-14T17:55:50.195412Z"
    }
   },
   "cell_type": "code",
   "source": [
    "purchases = purchases[['week_of_month', 'purchase_date', 'total_amount']]\n",
    "display(purchases)"
   ],
   "id": "9be3240fd9e91527",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   week_of_month purchase_date  total_amount\n",
       "0              1    2023-11-03          5117\n",
       "1              2    2023-11-10             0\n",
       "2              3    2023-11-17             0\n",
       "3              4    2023-11-24         21692"
      ],
      "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>week_of_month</th>\n",
       "      <th>purchase_date</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2023-11-03</td>\n",
       "      <td>5117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-11-10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2023-11-17</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2023-11-24</td>\n",
       "      <td>21692</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 40
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "Reference: [1] https://leetcode.com/problems/friday-purchases-ii/description/",
   "id": "84963662d6e3388b"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}