Sales by Day of the Week.ipynb

{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "# Calculating Sales by Day of the Week using Pandas\n",
    "\n",
    "Understanding sales trends is crucial for any business striving to maximize its performance and cater to customer needs effectively. One of the most valuable insights comes from analyzing how sales vary across different categories and days of the week.\n",
    "\n",
    "In this blog, we will walk through a practical example of generating a sales report from raw data using Pandas. This report will break down sales by product categories, such as books, phones, and glasses, and map them to the specific day of the week they were ordered. By doing so, we aim to uncover patterns in purchasing behavior that can help businesses make informed decisions, such as optimizing inventory, launching day-specific promotions, or identifying underperforming product lines.\n",
    "\n",
    "#### Table: Orders\n",
    "| Column Name  | Type    |\n",
    "|--------------|---------|\n",
    "| order_id     | int     |\n",
    "| customer_id  | int     |\n",
    "| order_date   | date    |\n",
    "| item_id      | varchar |\n",
    "| quantity     | int     |\n",
    "\n",
    "- (order_id, item_id) is the primary key for this table.\n",
    "- This table contains information about orders placed, where `order_date` is the date `item_id` was ordered by the customer with `customer_id`.\n",
    "\n",
    "#### Table: Items\n",
    "| Column Name   | Type    |\n",
    "|---------------|---------|\n",
    "| item_id       | varchar |\n",
    "| item_name     | varchar |\n",
    "| item_category | varchar |\n",
    "\n",
    "- `item_id` is the primary key for this table.\n",
    "- This table contains details about items, including their name and category.\n",
    "\n",
    "---\n",
    "\n",
    "### Problem Statement\n",
    "\n",
    "As a business owner, you would like to generate a sales report that summarizes how many units in each category were ordered on each day of the week.\n",
    "\n",
    "#### Expected Output\n",
    "\n",
    "The output should display the number of units sold for each item category, organized by the day of the week. The result table should be sorted by category.\n",
    "\n",
    "##### Input:\n",
    "\n",
    "**Orders Table:**\n",
    "| order_id | customer_id | order_date | item_id | quantity |\n",
    "|----------|-------------|------------|---------|----------|\n",
    "| 1        | 1           | 2020-06-01 | 1       | 10       |\n",
    "| 2        | 1           | 2020-06-08 | 2       | 10       |\n",
    "| 3        | 2           | 2020-06-02 | 1       | 5        |\n",
    "| 4        | 3           | 2020-06-03 | 3       | 5        |\n",
    "| 5        | 4           | 2020-06-04 | 4       | 1        |\n",
    "| 6        | 4           | 2020-06-05 | 5       | 5        |\n",
    "| 7        | 5           | 2020-06-05 | 1       | 10       |\n",
    "| 8        | 5           | 2020-06-14 | 4       | 5        |\n",
    "| 9        | 5           | 2020-06-21 | 3       | 5        |\n",
    "\n",
    "**Items Table:**\n",
    "| item_id | item_name       | item_category |\n",
    "|---------|-----------------|---------------|\n",
    "| 1       | LC Alg. Book    | Book          |\n",
    "| 2       | LC DB. Book     | Book          |\n",
    "| 3       | LC Smartphone   | Phone         |\n",
    "| 4       | LC Phone 2020   | Phone         |\n",
    "| 5       | LC SmartGlass   | Glasses       |\n",
    "| 6       | LC T-Shirt XL   | T-Shirt       |\n",
    "\n",
    "##### Output:\n",
    "\n",
    "| Category | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |\n",
    "|----------|--------|---------|-----------|----------|--------|----------|--------|\n",
    "| Book     | 20     | 5       | 0         | 0        | 10     | 0        | 0      |\n",
    "| Glasses  | 0      | 0       | 0         | 0        | 5      | 0        | 0      |\n",
    "| Phone    | 0      | 0       | 5         | 1        | 0      | 0        | 10     |\n",
    "| T-Shirt  | 0      | 0       | 0         | 0        | 0      | 0        | 0      |\n",
    "\n",
    "---\n",
    "\n",
    "#### Explanation:\n",
    "\n",
    "1. **Monday**:\n",
    "   - On 2020-06-01 and 2020-06-08, 20 units of category **Book** (10 + 10) were sold.\n",
    "\n",
    "2. **Tuesday**:\n",
    "   - On 2020-06-02, 5 units of category **Book** were sold.\n",
    "\n",
    "3. **Wednesday**:\n",
    "   - On 2020-06-03, 5 units of category **Phone** were sold.\n",
    "\n",
    "4. **Thursday**:\n",
    "   - On 2020-06-04, 1 unit of category **Phone** was sold.\n",
    "\n",
    "5. **Friday**:\n",
    "   - On 2020-06-05, 10 units of category **Book** and 5 units of category **Glasses** were sold.\n",
    "\n",
    "6. **Sunday**:\n",
    "   - On 2020-06-14 and 2020-06-21, 10 units of category **Phone** (5 + 5) were sold.\n",
    "\n",
    "7. No sales occurred for the **T-Shirt** category.\n"
   ],
   "id": "679c6fa41bbfe4"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T07:18:53.882366Z",
     "start_time": "2025-01-23T07:18:53.876748Z"
    }
   },
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "\n",
    "data = [[1, 1, '2020-06-01', 1, 10], \n",
    "        [2, 1, '2020-06-08', 2, 10], \n",
    "        [3, 2, '2020-06-02', 1, 5], \n",
    "        [4, 3, '2020-06-03', 3, 5], \n",
    "        [5, 4, '2020-06-04', 4, 1], \n",
    "        [6, 4, '2020-06-05', 5, 5], \n",
    "        [7, 5, '2020-06-05', 1, 10], \n",
    "        [8, 5, '2020-06-14', 4, 5], \n",
    "        [9, 5, '2020-06-21', 3, 5]]\n",
    "orders = pd.DataFrame(data, \n",
    "          columns=['order_id', \n",
    "                   'customer_id', \n",
    "                   'order_date', \n",
    "                   'item_id', \n",
    "                   'quantity']).astype({'order_id':'Int64', \n",
    "                   'customer_id':'Int64', \n",
    "                   'order_date':'datetime64[ns]', \n",
    "                   'item_id':'object', 'quantity':'Int64'})\n",
    "display(orders)"
   ],
   "id": "56ad5e1d-1bea-4fb3-9faf-e0609576d65f",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   order_id  customer_id order_date item_id  quantity\n",
       "0         1            1 2020-06-01       1        10\n",
       "1         2            1 2020-06-08       2        10\n",
       "2         3            2 2020-06-02       1         5\n",
       "3         4            3 2020-06-03       3         5\n",
       "4         5            4 2020-06-04       4         1\n",
       "5         6            4 2020-06-05       5         5\n",
       "6         7            5 2020-06-05       1        10\n",
       "7         8            5 2020-06-14       4         5\n",
       "8         9            5 2020-06-21       3         5"
      ],
      "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>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>item_id</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-06-08</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-06-02</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-06-03</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2020-06-05</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-05</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-14</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-21</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 24
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T07:18:53.906007Z",
     "start_time": "2025-01-23T07:18:53.901950Z"
    }
   },
   "cell_type": "code",
   "source": [
    "data = [[1, 'LC Alg. Book', 'Book'], \n",
    "        [2, 'LC DB. Book', 'Book'], \n",
    "        [3, 'LC SmarthPhone', 'Phone'], \n",
    "        [4, 'LC Phone 2020', 'Phone'], \n",
    "        [5, 'LC SmartGlass', 'Glasses'], \n",
    "        [6, 'LC T-Shirt XL', 'T-shirt']]\n",
    "items = pd.DataFrame(data, \n",
    "        columns=['item_id', \n",
    "                 'item_name', \n",
    "                 'item_category']).astype({'item_id':'object', \n",
    "                 'item_name':'object', \n",
    "                 'item_category':'object'})\n",
    "display(items)"
   ],
   "id": "e2da834b416f25a8",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "  item_id       item_name item_category\n",
       "0       1    LC Alg. Book          Book\n",
       "1       2     LC DB. Book          Book\n",
       "2       3  LC SmarthPhone         Phone\n",
       "3       4   LC Phone 2020         Phone\n",
       "4       5   LC SmartGlass       Glasses\n",
       "5       6   LC T-Shirt XL       T-shirt"
      ],
      "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>item_id</th>\n",
       "      <th>item_name</th>\n",
       "      <th>item_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>LC DB. Book</td>\n",
       "      <td>Book</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>LC SmarthPhone</td>\n",
       "      <td>Phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>LC Phone 2020</td>\n",
       "      <td>Phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>LC SmartGlass</td>\n",
       "      <td>Glasses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>LC T-Shirt XL</td>\n",
       "      <td>T-shirt</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 25
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 1. Merge Orders with Items**\n",
    "- This line merges the orders DataFrame with the items DataFrame using a left join. Each row in the orders table will now include information about the corresponding item from the items table, such as its name and category. This is necessary to associate the item category with each order for further analysis."
   ],
   "id": "9f8e493aa429c705"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T07:18:53.913782Z",
     "start_time": "2025-01-23T07:18:53.909073Z"
    }
   },
   "cell_type": "code",
   "source": [
    "orders = orders.merge(items, how=\"left\")\n",
    "display(orders)"
   ],
   "id": "a9e9dbe5332ab385",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   order_id  customer_id order_date item_id  quantity       item_name  \\\n",
       "0         1            1 2020-06-01       1        10    LC Alg. Book   \n",
       "1         2            1 2020-06-08       2        10     LC DB. Book   \n",
       "2         3            2 2020-06-02       1         5    LC Alg. Book   \n",
       "3         4            3 2020-06-03       3         5  LC SmarthPhone   \n",
       "4         5            4 2020-06-04       4         1   LC Phone 2020   \n",
       "5         6            4 2020-06-05       5         5   LC SmartGlass   \n",
       "6         7            5 2020-06-05       1        10    LC Alg. Book   \n",
       "7         8            5 2020-06-14       4         5   LC Phone 2020   \n",
       "8         9            5 2020-06-21       3         5  LC SmarthPhone   \n",
       "\n",
       "  item_category  \n",
       "0          Book  \n",
       "1          Book  \n",
       "2          Book  \n",
       "3         Phone  \n",
       "4         Phone  \n",
       "5       Glasses  \n",
       "6          Book  \n",
       "7         Phone  \n",
       "8         Phone  "
      ],
      "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>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>item_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_name</th>\n",
       "      <th>item_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-06-08</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>LC DB. Book</td>\n",
       "      <td>Book</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-06-02</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-06-03</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>LC SmarthPhone</td>\n",
       "      <td>Phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>LC Phone 2020</td>\n",
       "      <td>Phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2020-06-05</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>LC SmartGlass</td>\n",
       "      <td>Glasses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-05</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-14</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>LC Phone 2020</td>\n",
       "      <td>Phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-21</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>LC SmarthPhone</td>\n",
       "      <td>Phone</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 26
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 2. Extract Day Name from Order Date**\n",
    "- The order_date column is converted into the day name (e.g., \"Monday\", \"Tuesday\"). A new column called day is added to the orders DataFrame to store this information, allowing us to group data by the day of the week later."
   ],
   "id": "cd488a10ff9b1017"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T07:18:53.918693Z",
     "start_time": "2025-01-23T07:18:53.914504Z"
    }
   },
   "cell_type": "code",
   "source": [
    "orders[\"day\"] = orders[\"order_date\"].dt.day_name()\n",
    "display(orders)\n"
   ],
   "id": "83e069198c464825",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   order_id  customer_id order_date item_id  quantity       item_name  \\\n",
       "0         1            1 2020-06-01       1        10    LC Alg. Book   \n",
       "1         2            1 2020-06-08       2        10     LC DB. Book   \n",
       "2         3            2 2020-06-02       1         5    LC Alg. Book   \n",
       "3         4            3 2020-06-03       3         5  LC SmarthPhone   \n",
       "4         5            4 2020-06-04       4         1   LC Phone 2020   \n",
       "5         6            4 2020-06-05       5         5   LC SmartGlass   \n",
       "6         7            5 2020-06-05       1        10    LC Alg. Book   \n",
       "7         8            5 2020-06-14       4         5   LC Phone 2020   \n",
       "8         9            5 2020-06-21       3         5  LC SmarthPhone   \n",
       "\n",
       "  item_category        day  \n",
       "0          Book     Monday  \n",
       "1          Book     Monday  \n",
       "2          Book    Tuesday  \n",
       "3         Phone  Wednesday  \n",
       "4         Phone   Thursday  \n",
       "5       Glasses     Friday  \n",
       "6          Book     Friday  \n",
       "7         Phone     Sunday  \n",
       "8         Phone     Sunday  "
      ],
      "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>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>item_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_name</th>\n",
       "      <th>item_category</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-06-08</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>LC DB. Book</td>\n",
       "      <td>Book</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-06-02</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-06-03</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>LC SmarthPhone</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>LC Phone 2020</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2020-06-05</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>LC SmartGlass</td>\n",
       "      <td>Glasses</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-05</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-14</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>LC Phone 2020</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-21</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>LC SmarthPhone</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 27
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 3. Get Unique Item Categories**\n",
    "- Here, the unique values from the item_category column in the items DataFrame are extracted, sorted, and converted into a list. This list ensures that all categories are represented in the final analysis, even if some categories do not appear in the orders data."
   ],
   "id": "430b04aff5f63d82"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T07:18:53.921212Z",
     "start_time": "2025-01-23T07:18:53.919337Z"
    }
   },
   "cell_type": "code",
   "source": [
    "unique_categories = sorted(items[\"item_category\"].unique().tolist())\n",
    "print(unique_categories)"
   ],
   "id": "13bdbcaae4478bc7",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Book', 'Glasses', 'Phone', 'T-shirt']\n"
     ]
    }
   ],
   "execution_count": 28
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 4. Set Item Category as an Ordered Categorical Variable**\n",
    "- The item_category column in the orders DataFrame is converted into a categorical data type. The set_categories method ensures that the categories are explicitly ordered based on the unique_categories list. This step ensures consistency in the category ordering during the analysis."
   ],
   "id": "95fd038442db135f"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T07:18:53.926783Z",
     "start_time": "2025-01-23T07:18:53.922490Z"
    }
   },
   "cell_type": "code",
   "source": [
    "orders[\"item_category\"] = orders[\"item_category\"].astype(\"category\").cat.set_categories(unique_categories)\n",
    "display(orders)"
   ],
   "id": "a56129124096dd0b",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   order_id  customer_id order_date item_id  quantity       item_name  \\\n",
       "0         1            1 2020-06-01       1        10    LC Alg. Book   \n",
       "1         2            1 2020-06-08       2        10     LC DB. Book   \n",
       "2         3            2 2020-06-02       1         5    LC Alg. Book   \n",
       "3         4            3 2020-06-03       3         5  LC SmarthPhone   \n",
       "4         5            4 2020-06-04       4         1   LC Phone 2020   \n",
       "5         6            4 2020-06-05       5         5   LC SmartGlass   \n",
       "6         7            5 2020-06-05       1        10    LC Alg. Book   \n",
       "7         8            5 2020-06-14       4         5   LC Phone 2020   \n",
       "8         9            5 2020-06-21       3         5  LC SmarthPhone   \n",
       "\n",
       "  item_category        day  \n",
       "0          Book     Monday  \n",
       "1          Book     Monday  \n",
       "2          Book    Tuesday  \n",
       "3         Phone  Wednesday  \n",
       "4         Phone   Thursday  \n",
       "5       Glasses     Friday  \n",
       "6          Book     Friday  \n",
       "7         Phone     Sunday  \n",
       "8         Phone     Sunday  "
      ],
      "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>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>item_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_name</th>\n",
       "      <th>item_category</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-06-08</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>LC DB. Book</td>\n",
       "      <td>Book</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-06-02</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-06-03</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>LC SmarthPhone</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>LC Phone 2020</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2020-06-05</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>LC SmartGlass</td>\n",
       "      <td>Glasses</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-05</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-14</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>LC Phone 2020</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-21</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>LC SmarthPhone</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 29
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 5. Set Day of the Week as an Ordered Categorical Variable**\n",
    "- Similarly, the day column is converted into a categorical data type. The set_categories method sets the days of the week in chronological order. This ensures that when the data is grouped or sorted, the days will appear in the correct sequence from Monday to Sunday."
   ],
   "id": "8c04a96d476636ff"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T07:18:53.931606Z",
     "start_time": "2025-01-23T07:18:53.927269Z"
    }
   },
   "cell_type": "code",
   "source": [
    "orders[\"day\"] = orders[\"day\"].astype(\"category\").cat.set_categories(['Monday', \n",
    "                   'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'])\n",
    "display(orders)"
   ],
   "id": "9b028ede258f7cd9",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   order_id  customer_id order_date item_id  quantity       item_name  \\\n",
       "0         1            1 2020-06-01       1        10    LC Alg. Book   \n",
       "1         2            1 2020-06-08       2        10     LC DB. Book   \n",
       "2         3            2 2020-06-02       1         5    LC Alg. Book   \n",
       "3         4            3 2020-06-03       3         5  LC SmarthPhone   \n",
       "4         5            4 2020-06-04       4         1   LC Phone 2020   \n",
       "5         6            4 2020-06-05       5         5   LC SmartGlass   \n",
       "6         7            5 2020-06-05       1        10    LC Alg. Book   \n",
       "7         8            5 2020-06-14       4         5   LC Phone 2020   \n",
       "8         9            5 2020-06-21       3         5  LC SmarthPhone   \n",
       "\n",
       "  item_category        day  \n",
       "0          Book     Monday  \n",
       "1          Book     Monday  \n",
       "2          Book    Tuesday  \n",
       "3         Phone  Wednesday  \n",
       "4         Phone   Thursday  \n",
       "5       Glasses     Friday  \n",
       "6          Book     Friday  \n",
       "7         Phone     Sunday  \n",
       "8         Phone     Sunday  "
      ],
      "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>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>item_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_name</th>\n",
       "      <th>item_category</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-06-08</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>LC DB. Book</td>\n",
       "      <td>Book</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-06-02</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-06-03</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>LC SmarthPhone</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>LC Phone 2020</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2020-06-05</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>LC SmartGlass</td>\n",
       "      <td>Glasses</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-05</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>LC Alg. Book</td>\n",
       "      <td>Book</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-14</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>LC Phone 2020</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-06-21</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>LC SmarthPhone</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 30
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 6. Create a Pivot Table**\n",
    "- A pivot table is created to summarize the data. Each row corresponds to an item_category, and each column represents a day of the week. The table calculates the total quantity of items sold for each category on each day using the sum aggregation function. The observed=False ensures that all categories and days are included, even if no data exists for them."
   ],
   "id": "e036a6d3be215664"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T07:18:53.938355Z",
     "start_time": "2025-01-23T07:18:53.932127Z"
    }
   },
   "cell_type": "code",
   "source": [
    "orders = pd.pivot_table(orders, \n",
    "                        index=\"item_category\",\n",
    "                        columns=\"day\",\n",
    "                        values=\"quantity\",\n",
    "                        aggfunc=\"sum\",\n",
    "                        observed=False)\n",
    "display(orders)"
   ],
   "id": "fe020b6f5e300363",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "day            Monday  Tuesday  Wednesday  Thursday  Friday  Saturday  Sunday\n",
       "item_category                                                                \n",
       "Book               20        5          0         0      10         0       0\n",
       "Glasses             0        0          0         0       5         0       0\n",
       "Phone               0        0          5         1       0         0      10\n",
       "T-shirt             0        0          0         0       0         0       0"
      ],
      "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>day</th>\n",
       "      <th>Monday</th>\n",
       "      <th>Tuesday</th>\n",
       "      <th>Wednesday</th>\n",
       "      <th>Thursday</th>\n",
       "      <th>Friday</th>\n",
       "      <th>Saturday</th>\n",
       "      <th>Sunday</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Book</th>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Glasses</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Phone</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>T-shirt</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 31
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 7. Reset the Index and Rename Columns**\n",
    "- The pivot table's index (which represents item_category) is reset to a regular column named category. This makes the table easier to work with and aligns with the desired output format."
   ],
   "id": "a7680dc249079d0c"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T07:18:53.942694Z",
     "start_time": "2025-01-23T07:18:53.938961Z"
    }
   },
   "cell_type": "code",
   "source": [
    "orders = orders.reset_index(names=\"category\")\n",
    "display(orders)"
   ],
   "id": "42e33f29d1cdb6a6",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "day category  Monday  Tuesday  Wednesday  Thursday  Friday  Saturday  Sunday\n",
       "0       Book      20        5          0         0      10         0       0\n",
       "1    Glasses       0        0          0         0       5         0       0\n",
       "2      Phone       0        0          5         1       0         0      10\n",
       "3    T-shirt       0        0          0         0       0         0       0"
      ],
      "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>day</th>\n",
       "      <th>category</th>\n",
       "      <th>Monday</th>\n",
       "      <th>Tuesday</th>\n",
       "      <th>Wednesday</th>\n",
       "      <th>Thursday</th>\n",
       "      <th>Friday</th>\n",
       "      <th>Saturday</th>\n",
       "      <th>Sunday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Book</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Glasses</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Phone</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>T-shirt</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 32
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 8. Sort by Category**\n",
    "- Finally, the data is sorted alphabetically by the category column. This ensures the output is neatly ordered and easier to interpret."
   ],
   "id": "ef75cc36ebd2ae82"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T07:18:53.946702Z",
     "start_time": "2025-01-23T07:18:53.943161Z"
    }
   },
   "cell_type": "code",
   "source": [
    "orders = orders.sort_values(by=[\"category\"])\n",
    "display(orders)"
   ],
   "id": "ad10ca080d571408",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "day category  Monday  Tuesday  Wednesday  Thursday  Friday  Saturday  Sunday\n",
       "0       Book      20        5          0         0      10         0       0\n",
       "1    Glasses       0        0          0         0       5         0       0\n",
       "2      Phone       0        0          5         1       0         0      10\n",
       "3    T-shirt       0        0          0         0       0         0       0"
      ],
      "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>day</th>\n",
       "      <th>category</th>\n",
       "      <th>Monday</th>\n",
       "      <th>Tuesday</th>\n",
       "      <th>Wednesday</th>\n",
       "      <th>Thursday</th>\n",
       "      <th>Friday</th>\n",
       "      <th>Saturday</th>\n",
       "      <th>Sunday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Book</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Glasses</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Phone</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>T-shirt</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 33
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "References: [1] https://leetcode.com/problems/sales-by-day-of-the-week/description/?lang=pythondata",
   "id": "216727ba113cb60e"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}