Grouping_Aggregating_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "174f6f68",
   "metadata": {},
   "source": [
    "# Grouping Data & Aggregating Variables\n",
    "## This script contains the following points;\n",
    "## 1. Importing Data\n",
    "## 2. Grouping Data with pandas\n",
    "    2.1 Grouping Subset Data \n",
    "    2.2 Apply Subset Groups Into The Main Data Frame *\n",
    "    2.3 Creating the Loyalty flag - Aggragating Data for Different Customer Types\n",
    "    2.4 Basic Product Price Statistics for Customer Loyalty Categories *\n",
    "    2.5 Spending Categories (above and below average price per product) * \n",
    "    2.6 Frequent and Non-Frequent Customers *\n",
    "## 3. Exporting data *\n",
    "\n",
    "    * requested tasks\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4653f5d",
   "metadata": {},
   "source": [
    "## 1. Importing Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ac2952ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "58dbae19",
   "metadata": {},
   "outputs": [],
   "source": [
    "path = r'C:\\Users\\senja\\OneDrive\\Documents\\5. Learning\\4. Data Analytics Immersion\\Instacart Basket Analysis'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ab9a2780",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\senja\\\\OneDrive\\\\Documents\\\\5. Learning\\\\4. Data Analytics Immersion\\\\Instacart Basket Analysis'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "33047f6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import updated merged pkl file\n",
    "\n",
    "ords_prods_merge = pd.read_pickle(os.path.join(path, '02 Data', 'Prepared Data', 'updated_merged.pkl'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e98cfa15",
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>prices</th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_number</th>\n",
       "      <th>orders_day_of_week</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "      <th>_merge</th>\n",
       "      <th>price_range_loc</th>\n",
       "      <th>Busiest day</th>\n",
       "      <th>Busiest days</th>\n",
       "      <th>busiest_period_of_day_range</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3139998</td>\n",
       "      <td>138</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1977647</td>\n",
       "      <td>138</td>\n",
       "      <td>30</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Average Orders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>389851</td>\n",
       "      <td>709</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>6.0</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>652770</td>\n",
       "      <td>764</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1813452</td>\n",
       "      <td>764</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>9.0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id                product_name  aisle_id  department_id  prices  \\\n",
       "0           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "1           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "2           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "3           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "4           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "\n",
       "   order_id  user_id  order_number  orders_day_of_week  order_hour_of_day  \\\n",
       "0   3139998      138            28                   6                 11   \n",
       "1   1977647      138            30                   6                 17   \n",
       "2    389851      709             2                   0                 21   \n",
       "3    652770      764             1                   3                 13   \n",
       "4   1813452      764             3                   4                 17   \n",
       "\n",
       "   days_since_prior_order  add_to_cart_order  reordered _merge  \\\n",
       "0                     3.0                  5          0   both   \n",
       "1                    20.0                  1          1   both   \n",
       "2                     6.0                 20          0   both   \n",
       "3                     0.0                 10          0   both   \n",
       "4                     9.0                 11          1   both   \n",
       "\n",
       "     price_range_loc     Busiest day    Busiest days  \\\n",
       "0  Mid-range product  Regularly busy  Regularly busy   \n",
       "1  Mid-range product  Regularly busy  Regularly busy   \n",
       "2  Mid-range product     Busiest day    Busiest days   \n",
       "3  Mid-range product  Regularly busy    Slowest days   \n",
       "4  Mid-range product      Least busy    Slowest days   \n",
       "\n",
       "  busiest_period_of_day_range  \n",
       "0                 Most Orders  \n",
       "1              Average Orders  \n",
       "2              Average Orders  \n",
       "3                 Most Orders  \n",
       "4              Average Orders  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check the columnns\n",
    "\n",
    "ords_prods_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "367d1063",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(32434212, 18)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ords_prods_merge.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "88f60837",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a subset \n",
    "\n",
    "df = ords_prods_merge[:1000000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b698fdcc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000000, 18)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "92997a60",
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>prices</th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_number</th>\n",
       "      <th>orders_day_of_week</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "      <th>_merge</th>\n",
       "      <th>price_range_loc</th>\n",
       "      <th>Busiest day</th>\n",
       "      <th>Busiest days</th>\n",
       "      <th>busiest_period_of_day_range</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3139998</td>\n",
       "      <td>138</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1977647</td>\n",
       "      <td>138</td>\n",
       "      <td>30</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Average Orders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>389851</td>\n",
       "      <td>709</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>6.0</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>652770</td>\n",
       "      <td>764</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1813452</td>\n",
       "      <td>764</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>9.0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id                product_name  aisle_id  department_id  prices  \\\n",
       "0           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "1           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "2           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "3           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "4           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "\n",
       "   order_id  user_id  order_number  orders_day_of_week  order_hour_of_day  \\\n",
       "0   3139998      138            28                   6                 11   \n",
       "1   1977647      138            30                   6                 17   \n",
       "2    389851      709             2                   0                 21   \n",
       "3    652770      764             1                   3                 13   \n",
       "4   1813452      764             3                   4                 17   \n",
       "\n",
       "   days_since_prior_order  add_to_cart_order  reordered _merge  \\\n",
       "0                     3.0                  5          0   both   \n",
       "1                    20.0                  1          1   both   \n",
       "2                     6.0                 20          0   both   \n",
       "3                     0.0                 10          0   both   \n",
       "4                     9.0                 11          1   both   \n",
       "\n",
       "     price_range_loc     Busiest day    Busiest days  \\\n",
       "0  Mid-range product  Regularly busy  Regularly busy   \n",
       "1  Mid-range product  Regularly busy  Regularly busy   \n",
       "2  Mid-range product     Busiest day    Busiest days   \n",
       "3  Mid-range product  Regularly busy    Slowest days   \n",
       "4  Mid-range product      Least busy    Slowest days   \n",
       "\n",
       "  busiest_period_of_day_range  \n",
       "0                 Most Orders  \n",
       "1              Average Orders  \n",
       "2              Average Orders  \n",
       "3                 Most Orders  \n",
       "4              Average Orders  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69f11ad3",
   "metadata": {},
   "source": [
    "## 2. Grouping Data with pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3623fee",
   "metadata": {},
   "source": [
    "### 2.1 Grouping Subset Data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "62d7519c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x0000025D8D98FD00>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# group the subset data\n",
    "\n",
    "df.groupby('product_name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c8e56bed",
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>order_number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>department_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15.562844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17.518895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16.907152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17.537934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>14.894521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16.658449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>17.072954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>14.942021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>15.447580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>18.681852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>15.352278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>14.327957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>16.578690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>16.666369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16.153766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17.768070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>15.602964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19.674252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>16.924344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>16.292631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>25.535596</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              order_number\n",
       "                      mean\n",
       "department_id             \n",
       "1                15.562844\n",
       "2                17.518895\n",
       "3                16.907152\n",
       "4                17.537934\n",
       "5                14.894521\n",
       "6                16.658449\n",
       "7                17.072954\n",
       "8                14.942021\n",
       "9                15.447580\n",
       "10               18.681852\n",
       "11               15.352278\n",
       "12               14.327957\n",
       "13               16.578690\n",
       "14               16.666369\n",
       "15               16.153766\n",
       "16               17.768070\n",
       "17               15.602964\n",
       "18               19.674252\n",
       "19               16.924344\n",
       "20               16.292631\n",
       "21               25.535596"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " # group the “department_id\" and apply mean values for the “order_number” \n",
    "    \n",
    "df.groupby('department_id').agg({'order_number': ['mean']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "bd7f6196",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-1-a2ff64739bc6>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# aggregation without use of the agg() function\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgroupby\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'department_id'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'order_number'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmean\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'df' is not defined"
     ]
    }
   ],
   "source": [
    "# aggregation without use of the agg() function\n",
    "\n",
    "df.groupby('department_id')['order_number'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f87c217c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "department_id\n",
       "1     15.562844\n",
       "2     17.518895\n",
       "3     16.907152\n",
       "4     17.537934\n",
       "5     14.894521\n",
       "6     16.658449\n",
       "7     17.072954\n",
       "8     14.942021\n",
       "9     15.447580\n",
       "10    18.681852\n",
       "11    15.352278\n",
       "12    14.327957\n",
       "13    16.578690\n",
       "14    16.666369\n",
       "15    16.153766\n",
       "16    17.768070\n",
       "17    15.602964\n",
       "18    19.674252\n",
       "19    16.924344\n",
       "20    16.292631\n",
       "21    25.535596\n",
       "Name: order_number, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the use of column name within square brackets \n",
    "\n",
    "df.groupby('department_id').order_number.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "5ab7a72e",
   "metadata": {
    "scrolled": false
   },
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">order_number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>department_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15.562844</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17.518895</td>\n",
       "      <td>1</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16.907152</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17.537934</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>14.894521</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16.658449</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>17.072954</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>14.942021</td>\n",
       "      <td>1</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>15.447580</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>18.681852</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>15.352278</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>14.327957</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>16.578690</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>16.666369</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16.153766</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17.768070</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>15.602964</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19.674252</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>16.924344</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>16.292631</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>25.535596</td>\n",
       "      <td>1</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              order_number        \n",
       "                      mean min max\n",
       "department_id                     \n",
       "1                15.562844   1  99\n",
       "2                17.518895   1  96\n",
       "3                16.907152   1  99\n",
       "4                17.537934   1  99\n",
       "5                14.894521   1  99\n",
       "6                16.658449   1  99\n",
       "7                17.072954   1  99\n",
       "8                14.942021   1  98\n",
       "9                15.447580   1  99\n",
       "10               18.681852   1  99\n",
       "11               15.352278   1  99\n",
       "12               14.327957   1  99\n",
       "13               16.578690   1  99\n",
       "14               16.666369   1  99\n",
       "15               16.153766   1  99\n",
       "16               17.768070   1  99\n",
       "17               15.602964   1  99\n",
       "18               19.674252   1  99\n",
       "19               16.924344   1  99\n",
       "20               16.292631   1  99\n",
       "21               25.535596   1  98"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# performing multiple aggregations\n",
    "\n",
    "df.groupby('department_id').agg({'order_number': ['mean', 'min', 'max']})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07c8f827",
   "metadata": {},
   "source": [
    "### 2.2 Apply Subset Groups Into The Main Data Frame *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "63623212",
   "metadata": {
    "scrolled": true
   },
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">order_number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>department_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15.457687</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17.277920</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17.179756</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17.811403</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>15.213779</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16.439806</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>17.225773</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>15.340520</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>15.895474</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20.197148</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>16.170828</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>15.887622</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>16.583304</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>16.757377</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16.165037</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17.663250</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>15.694469</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19.310397</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>17.177343</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>16.473447</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22.902379</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              order_number        \n",
       "                      mean min max\n",
       "department_id                     \n",
       "1                15.457687   1  99\n",
       "2                17.277920   1  99\n",
       "3                17.179756   1  99\n",
       "4                17.811403   1  99\n",
       "5                15.213779   1  99\n",
       "6                16.439806   1  99\n",
       "7                17.225773   1  99\n",
       "8                15.340520   1  99\n",
       "9                15.895474   1  99\n",
       "10               20.197148   1  99\n",
       "11               16.170828   1  99\n",
       "12               15.887622   1  99\n",
       "13               16.583304   1  99\n",
       "14               16.757377   1  99\n",
       "15               16.165037   1  99\n",
       "16               17.663250   1  99\n",
       "17               15.694469   1  99\n",
       "18               19.310397   1  99\n",
       "19               17.177343   1  99\n",
       "20               16.473447   1  99\n",
       "21               22.902379   1  99"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# performing multiple aggregations as on subset data\n",
    "\n",
    "ords_prods_merge.groupby('department_id').agg({'order_number': ['mean', 'min', 'max']})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e6233b3",
   "metadata": {},
   "source": [
    "### Analyze the result. How do the results for the entire dataframe differ from those of the subset. \n",
    "\n",
    "#### The mean values are slightly different in many department categories but generally the sample means are quite close to actual means as on the entire data frame. The means are usually greater in the data frame which covers the entire data. This is expected as there are more than 31M  more data rows in the main data frame and therefore data values are expected to be more widely spread out. However, there are few execptions, the mean for department 1, 2, 6, 16, 18 and 21 are greater in the subset data frame. The difference between the department id 2 and 21 can be explained due to the fact that the max order numbers are 96 and 98 in subset whereas they are 99 on the entire data frame. As the subset data frame includes \"only\" 1M rows, and it is a sample it won't be equal to the entire dataframe, and therefore the sample mean might not be systematically smaller or larger than the entire data frame. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ee6e5e1b",
   "metadata": {
    "scrolled": true
   },
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">prices</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>#2 Coffee Filters</th>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#2 Cone White Coffee Filters</th>\n",
       "      <td>5.7</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#2 Mechanical Pencils</th>\n",
       "      <td>9.2</td>\n",
       "      <td>9.2</td>\n",
       "      <td>9.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#4 Natural Brown Coffee Filters</th>\n",
       "      <td>2.5</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#NAME?</th>\n",
       "      <td>3.6</td>\n",
       "      <td>3.6</td>\n",
       "      <td>3.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>with Xylitol Unwrapped Original Flavor 50 Sticks Sugar Free Gum</th>\n",
       "      <td>5.4</td>\n",
       "      <td>5.4</td>\n",
       "      <td>5.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>with Xylitol Unwrapped Spearmint 50 Sticks Sugar Free Gum</th>\n",
       "      <td>5.2</td>\n",
       "      <td>5.2</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>with Xylitol Watermelon Twist 18 Sticks Sugar Free Gum</th>\n",
       "      <td>3.5</td>\n",
       "      <td>3.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>with a Splash of Mango Coconut Water</th>\n",
       "      <td>2.6</td>\n",
       "      <td>2.6</td>\n",
       "      <td>2.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>with a Splash of Pineapple Coconut Water</th>\n",
       "      <td>4.2</td>\n",
       "      <td>4.2</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49661 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   prices            \n",
       "                                                     mean   min   max\n",
       "product_name                                                         \n",
       "#2 Coffee Filters                                    11.0  11.0  11.0\n",
       "#2 Cone White Coffee Filters                          5.7   5.7   5.7\n",
       "#2 Mechanical Pencils                                 9.2   9.2   9.2\n",
       "#4 Natural Brown Coffee Filters                       2.5   2.5   2.5\n",
       "#NAME?                                                3.6   3.6   3.6\n",
       "...                                                   ...   ...   ...\n",
       "with Xylitol Unwrapped Original Flavor 50 Stick...    5.4   5.4   5.4\n",
       "with Xylitol Unwrapped Spearmint 50 Sticks Suga...    5.2   5.2   5.2\n",
       "with Xylitol Watermelon Twist 18 Sticks Sugar F...    3.5   3.5   3.5\n",
       "with a Splash of Mango Coconut Water                  2.6   2.6   2.6\n",
       "with a Splash of Pineapple Coconut Water              4.2   4.2   4.2\n",
       "\n",
       "[49661 rows x 3 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# performing multiple aggregations - means, max and min for prices based on the product name\n",
    "\n",
    "ords_prods_merge.groupby('product_name').agg({'prices': ['mean', 'min', 'max']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "3000c5e8",
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>orders_day_of_week</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>#2 Coffee Filters</th>\n",
       "      <td>2.696268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#2 Cone White Coffee Filters</th>\n",
       "      <td>3.384615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#2 Mechanical Pencils</th>\n",
       "      <td>2.875000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#4 Natural Brown Coffee Filters</th>\n",
       "      <td>2.755187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#NAME?</th>\n",
       "      <td>2.724138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>with Xylitol Unwrapped Original Flavor 50 Sticks Sugar Free Gum</th>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>with Xylitol Unwrapped Spearmint 50 Sticks Sugar Free Gum</th>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>with Xylitol Watermelon Twist 18 Sticks Sugar Free Gum</th>\n",
       "      <td>2.867647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>with a Splash of Mango Coconut Water</th>\n",
       "      <td>3.260870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>with a Splash of Pineapple Coconut Water</th>\n",
       "      <td>3.037736</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49661 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   orders_day_of_week\n",
       "                                                                 mean\n",
       "product_name                                                         \n",
       "#2 Coffee Filters                                            2.696268\n",
       "#2 Cone White Coffee Filters                                 3.384615\n",
       "#2 Mechanical Pencils                                        2.875000\n",
       "#4 Natural Brown Coffee Filters                              2.755187\n",
       "#NAME?                                                       2.724138\n",
       "...                                                               ...\n",
       "with Xylitol Unwrapped Original Flavor 50 Stick...           3.000000\n",
       "with Xylitol Unwrapped Spearmint 50 Sticks Suga...           3.000000\n",
       "with Xylitol Watermelon Twist 18 Sticks Sugar F...           2.867647\n",
       "with a Splash of Mango Coconut Water                         3.260870\n",
       "with a Splash of Pineapple Coconut Water                     3.037736\n",
       "\n",
       "[49661 rows x 1 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# performing multiple aggregations - mean for days based on the product name (min and max are known)\n",
    "\n",
    "ords_prods_merge.groupby('product_name').agg({'orders_day_of_week': ['mean']})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5dc0135",
   "metadata": {},
   "source": [
    "### 2.3 Creating the Loyalty flag -  Aggregating Data for Different Customer Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "7a3a377c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# establish flag criteria - create “max_order” column, group “user_id” column, generate maximum orders on “order_number”\n",
    "\n",
    "ords_prods_merge['max_order'] = ords_prods_merge.groupby(['user_id'])['order_number'].transform(np.max)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "924ff47b",
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>prices</th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_number</th>\n",
       "      <th>orders_day_of_week</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "      <th>_merge</th>\n",
       "      <th>price_range_loc</th>\n",
       "      <th>Busiest day</th>\n",
       "      <th>Busiest days</th>\n",
       "      <th>busiest_period_of_day_range</th>\n",
       "      <th>max_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3139998</td>\n",
       "      <td>138</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1977647</td>\n",
       "      <td>138</td>\n",
       "      <td>30</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>389851</td>\n",
       "      <td>709</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>6.0</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>652770</td>\n",
       "      <td>764</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1813452</td>\n",
       "      <td>764</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>9.0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id                product_name  aisle_id  department_id  prices  \\\n",
       "0           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "1           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "2           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "3           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "4           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "\n",
       "   order_id  user_id  order_number  orders_day_of_week  order_hour_of_day  \\\n",
       "0   3139998      138            28                   6                 11   \n",
       "1   1977647      138            30                   6                 17   \n",
       "2    389851      709             2                   0                 21   \n",
       "3    652770      764             1                   3                 13   \n",
       "4   1813452      764             3                   4                 17   \n",
       "\n",
       "   days_since_prior_order  add_to_cart_order  reordered _merge  \\\n",
       "0                     3.0                  5          0   both   \n",
       "1                    20.0                  1          1   both   \n",
       "2                     6.0                 20          0   both   \n",
       "3                     0.0                 10          0   both   \n",
       "4                     9.0                 11          1   both   \n",
       "\n",
       "     price_range_loc     Busiest day    Busiest days  \\\n",
       "0  Mid-range product  Regularly busy  Regularly busy   \n",
       "1  Mid-range product  Regularly busy  Regularly busy   \n",
       "2  Mid-range product     Busiest day    Busiest days   \n",
       "3  Mid-range product  Regularly busy    Slowest days   \n",
       "4  Mid-range product      Least busy    Slowest days   \n",
       "\n",
       "  busiest_period_of_day_range  max_order  \n",
       "0                 Most Orders         32  \n",
       "1              Average Orders         32  \n",
       "2              Average Orders          5  \n",
       "3                 Most Orders          3  \n",
       "4              Average Orders          3  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ords_prods_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "bd6fb801",
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>prices</th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_number</th>\n",
       "      <th>orders_day_of_week</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "      <th>_merge</th>\n",
       "      <th>price_range_loc</th>\n",
       "      <th>Busiest day</th>\n",
       "      <th>Busiest days</th>\n",
       "      <th>busiest_period_of_day_range</th>\n",
       "      <th>max_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3139998</td>\n",
       "      <td>138</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1977647</td>\n",
       "      <td>138</td>\n",
       "      <td>30</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>389851</td>\n",
       "      <td>709</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>6.0</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>652770</td>\n",
       "      <td>764</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1813452</td>\n",
       "      <td>764</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>9.0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>602103</td>\n",
       "      <td>10831</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>23.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>49629</td>\n",
       "      <td>11119</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>317888</td>\n",
       "      <td>11186</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>682486</td>\n",
       "      <td>11243</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2861386</td>\n",
       "      <td>11448</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    product_id                product_name  aisle_id  department_id  prices  \\\n",
       "0            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "1            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "2            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "3            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "4            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "..         ...                         ...       ...            ...     ...   \n",
       "95           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "96           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "97           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "98           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "99           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "\n",
       "    order_id  user_id  order_number  orders_day_of_week  order_hour_of_day  \\\n",
       "0    3139998      138            28                   6                 11   \n",
       "1    1977647      138            30                   6                 17   \n",
       "2     389851      709             2                   0                 21   \n",
       "3     652770      764             1                   3                 13   \n",
       "4    1813452      764             3                   4                 17   \n",
       "..       ...      ...           ...                 ...                ...   \n",
       "95    602103    10831             8                   3                 11   \n",
       "96     49629    11119            18                   1                 14   \n",
       "97    317888    11186            13                   5                 16   \n",
       "98    682486    11243            16                   3                 13   \n",
       "99   2861386    11448            10                   2                 15   \n",
       "\n",
       "    days_since_prior_order  add_to_cart_order  reordered _merge  \\\n",
       "0                      3.0                  5          0   both   \n",
       "1                     20.0                  1          1   both   \n",
       "2                      6.0                 20          0   both   \n",
       "3                      0.0                 10          0   both   \n",
       "4                      9.0                 11          1   both   \n",
       "..                     ...                ...        ...    ...   \n",
       "95                    23.0                  5          0   both   \n",
       "96                    30.0                  1          0   both   \n",
       "97                     2.0                  8          0   both   \n",
       "98                     0.0                  2          0   both   \n",
       "99                     3.0                  6          0   both   \n",
       "\n",
       "      price_range_loc     Busiest day    Busiest days  \\\n",
       "0   Mid-range product  Regularly busy  Regularly busy   \n",
       "1   Mid-range product  Regularly busy  Regularly busy   \n",
       "2   Mid-range product     Busiest day    Busiest days   \n",
       "3   Mid-range product  Regularly busy    Slowest days   \n",
       "4   Mid-range product      Least busy    Slowest days   \n",
       "..                ...             ...             ...   \n",
       "95  Mid-range product  Regularly busy    Slowest days   \n",
       "96  Mid-range product  Regularly busy    Busiest days   \n",
       "97  Mid-range product  Regularly busy  Regularly busy   \n",
       "98  Mid-range product  Regularly busy    Slowest days   \n",
       "99  Mid-range product  Regularly busy  Regularly busy   \n",
       "\n",
       "   busiest_period_of_day_range  max_order  \n",
       "0                  Most Orders         32  \n",
       "1               Average Orders         32  \n",
       "2               Average Orders          5  \n",
       "3                  Most Orders          3  \n",
       "4               Average Orders          3  \n",
       "..                         ...        ...  \n",
       "95                 Most Orders         10  \n",
       "96                 Most Orders         23  \n",
       "97                 Most Orders         26  \n",
       "98                 Most Orders         43  \n",
       "99                 Most Orders         21  \n",
       "\n",
       "[100 rows x 19 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the data \n",
    "\n",
    "ords_prods_merge.head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "47a93f2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.display.max_rows = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "564f8a59",
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>prices</th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_number</th>\n",
       "      <th>orders_day_of_week</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "      <th>_merge</th>\n",
       "      <th>price_range_loc</th>\n",
       "      <th>Busiest day</th>\n",
       "      <th>Busiest days</th>\n",
       "      <th>busiest_period_of_day_range</th>\n",
       "      <th>max_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3139998</td>\n",
       "      <td>138</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1977647</td>\n",
       "      <td>138</td>\n",
       "      <td>30</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>389851</td>\n",
       "      <td>709</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>6.0</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>652770</td>\n",
       "      <td>764</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1813452</td>\n",
       "      <td>764</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>9.0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1701441</td>\n",
       "      <td>777</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>26.0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1871483</td>\n",
       "      <td>825</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>30.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1290456</td>\n",
       "      <td>910</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>369558</td>\n",
       "      <td>1052</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>19.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>589712</td>\n",
       "      <td>1052</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1798115</td>\n",
       "      <td>1379</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>497193</td>\n",
       "      <td>1480</td>\n",
       "      <td>36</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1727826</td>\n",
       "      <td>1480</td>\n",
       "      <td>38</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2694698</td>\n",
       "      <td>1480</td>\n",
       "      <td>39</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3171753</td>\n",
       "      <td>1494</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>186761</td>\n",
       "      <td>1494</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>6.0</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>317121</td>\n",
       "      <td>1494</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>200390</td>\n",
       "      <td>1540</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Fewest Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>374436</td>\n",
       "      <td>1540</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2920553</td>\n",
       "      <td>1540</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Fewest Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2813049</td>\n",
       "      <td>1540</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Fewest Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2365998</td>\n",
       "      <td>1540</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Fewest Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1604797</td>\n",
       "      <td>1540</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>28.0</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Fewest Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2799665</td>\n",
       "      <td>1540</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2228851</td>\n",
       "      <td>1540</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2337422</td>\n",
       "      <td>1540</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2699008</td>\n",
       "      <td>1540</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1914830</td>\n",
       "      <td>1540</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>783503</td>\n",
       "      <td>1540</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2500516</td>\n",
       "      <td>1540</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>716845</td>\n",
       "      <td>1540</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "      <td>22</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1955535</td>\n",
       "      <td>1540</td>\n",
       "      <td>25</td>\n",
       "      <td>6</td>\n",
       "      <td>19</td>\n",
       "      <td>16.0</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1424793</td>\n",
       "      <td>1540</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Fewest Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>749498</td>\n",
       "      <td>1540</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2434812</td>\n",
       "      <td>1598</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1531889</td>\n",
       "      <td>1598</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>726947</td>\n",
       "      <td>1598</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>627615</td>\n",
       "      <td>1647</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2507439</td>\n",
       "      <td>1647</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>18.0</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1152609</td>\n",
       "      <td>1647</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1048339</td>\n",
       "      <td>1647</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>30.0</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2818381</td>\n",
       "      <td>1647</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2722928</td>\n",
       "      <td>2833</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1482338</td>\n",
       "      <td>2850</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2311344</td>\n",
       "      <td>2857</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>812943</td>\n",
       "      <td>3010</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>7.0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>256839</td>\n",
       "      <td>3029</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2192488</td>\n",
       "      <td>3395</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>30.0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>207661</td>\n",
       "      <td>3904</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1394659</td>\n",
       "      <td>4006</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2353526</td>\n",
       "      <td>4006</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2269226</td>\n",
       "      <td>4006</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>367316</td>\n",
       "      <td>4006</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1448367</td>\n",
       "      <td>4006</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>19989</td>\n",
       "      <td>4122</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2735922</td>\n",
       "      <td>4141</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>30.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2469422</td>\n",
       "      <td>4343</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3185558</td>\n",
       "      <td>4553</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "      <td>29.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>350540</td>\n",
       "      <td>4845</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>12.0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>513100</td>\n",
       "      <td>6866</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2081405</td>\n",
       "      <td>7275</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>23</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Fewest Orders</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2209807</td>\n",
       "      <td>7669</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>921798</td>\n",
       "      <td>7669</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2540226</td>\n",
       "      <td>7669</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2265792</td>\n",
       "      <td>7981</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3357714</td>\n",
       "      <td>7981</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Fewest Orders</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1186573</td>\n",
       "      <td>7981</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>23</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Fewest Orders</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3209740</td>\n",
       "      <td>7981</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>138748</td>\n",
       "      <td>7981</td>\n",
       "      <td>30</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2027157</td>\n",
       "      <td>8703</td>\n",
       "      <td>34</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3052954</td>\n",
       "      <td>8723</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1486756</td>\n",
       "      <td>8735</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>632514</td>\n",
       "      <td>9293</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>30.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1253470</td>\n",
       "      <td>9293</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>837303</td>\n",
       "      <td>9467</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2734881</td>\n",
       "      <td>9694</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2164394</td>\n",
       "      <td>9694</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>18</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3354551</td>\n",
       "      <td>9694</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1381165</td>\n",
       "      <td>9694</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>21</td>\n",
       "      <td>14.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2809438</td>\n",
       "      <td>9694</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>18</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>425352</td>\n",
       "      <td>9694</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>16.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2453441</td>\n",
       "      <td>9694</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>22</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1189695</td>\n",
       "      <td>9694</td>\n",
       "      <td>15</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1759231</td>\n",
       "      <td>9694</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>482648</td>\n",
       "      <td>9694</td>\n",
       "      <td>20</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2201348</td>\n",
       "      <td>9694</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>16.0</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3351592</td>\n",
       "      <td>9848</td>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>18</td>\n",
       "      <td>8.0</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2164672</td>\n",
       "      <td>10113</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>18</td>\n",
       "      <td>14.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1526735</td>\n",
       "      <td>10315</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2057892</td>\n",
       "      <td>10479</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>356685</td>\n",
       "      <td>10708</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>14.0</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1592736</td>\n",
       "      <td>10708</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>14.0</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3125810</td>\n",
       "      <td>10789</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>846661</td>\n",
       "      <td>10789</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>14.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2363761</td>\n",
       "      <td>10789</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>602103</td>\n",
       "      <td>10831</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>23.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>49629</td>\n",
       "      <td>11119</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>317888</td>\n",
       "      <td>11186</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>682486</td>\n",
       "      <td>11243</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2861386</td>\n",
       "      <td>11448</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    product_id                product_name  aisle_id  department_id  prices  \\\n",
       "0            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "1            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "2            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "3            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "4            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "5            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "6            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "7            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "8            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "9            1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "10           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "11           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "12           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "13           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "14           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "15           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "16           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "17           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "18           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "19           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "20           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "21           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "22           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "23           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "24           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "25           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "26           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "27           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "28           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "29           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "30           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "31           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "32           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "33           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "34           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "35           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "36           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "37           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "38           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "39           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "40           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "41           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "42           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "43           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "44           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "45           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "46           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "47           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "48           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "49           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "50           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "51           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "52           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "53           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "54           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "55           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "56           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "57           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "58           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "59           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "60           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "61           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "62           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "63           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "64           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "65           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "66           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "67           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "68           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "69           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "70           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "71           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "72           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "73           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "74           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "75           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "76           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "77           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "78           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "79           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "80           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "81           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "82           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "83           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "84           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "85           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "86           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "87           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "88           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "89           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "90           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "91           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "92           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "93           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "94           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "95           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "96           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "97           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "98           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "99           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "\n",
       "    order_id  user_id  order_number  orders_day_of_week  order_hour_of_day  \\\n",
       "0    3139998      138            28                   6                 11   \n",
       "1    1977647      138            30                   6                 17   \n",
       "2     389851      709             2                   0                 21   \n",
       "3     652770      764             1                   3                 13   \n",
       "4    1813452      764             3                   4                 17   \n",
       "5    1701441      777            16                   1                  7   \n",
       "6    1871483      825             3                   2                 14   \n",
       "7    1290456      910            12                   3                 10   \n",
       "8     369558     1052            10                   1                 20   \n",
       "9     589712     1052            15                   1                 12   \n",
       "10   1798115     1379             1                   5                 11   \n",
       "11    497193     1480            36                   3                 13   \n",
       "12   1727826     1480            38                   4                 13   \n",
       "13   2694698     1480            39                   2                 12   \n",
       "14   3171753     1494             6                   3                 10   \n",
       "15    186761     1494             7                   2                  8   \n",
       "16    317121     1494             9                   5                  8   \n",
       "17    200390     1540             4                   2                  0   \n",
       "18    374436     1540             5                   1                 21   \n",
       "19   2920553     1540             6                   1                  0   \n",
       "20   2813049     1540             7                   2                  2   \n",
       "21   2365998     1540             8                   1                 23   \n",
       "22   1604797     1540             9                   1                  3   \n",
       "23   2799665     1540            14                   1                 14   \n",
       "24   2228851     1540            15                   1                 14   \n",
       "25   2337422     1540            18                   0                 21   \n",
       "26   2699008     1540            19                   0                 22   \n",
       "27   1914830     1540            20                   0                 22   \n",
       "28    783503     1540            21                   0                 20   \n",
       "29   2500516     1540            22                   0                 22   \n",
       "30    716845     1540            24                   4                 22   \n",
       "31   1955535     1540            25                   6                 19   \n",
       "32   1424793     1540            27                   1                  1   \n",
       "33    749498     1540            28                   0                 21   \n",
       "34   2434812     1598             1                   2                 12   \n",
       "35   1531889     1598             2                   2                 10   \n",
       "36    726947     1598             3                   4                 17   \n",
       "37    627615     1647             1                   1                  9   \n",
       "38   2507439     1647             4                   0                 13   \n",
       "39   1152609     1647             6                   1                  9   \n",
       "40   1048339     1647             8                   0                 17   \n",
       "41   2818381     1647             9                   4                 15   \n",
       "42   2722928     2833             1                   0                 14   \n",
       "43   1482338     2850             7                   1                 16   \n",
       "44   2311344     2857            24                   1                  8   \n",
       "45    812943     3010             5                   3                 10   \n",
       "46    256839     3029             1                   2                 20   \n",
       "47   2192488     3395             4                   2                 10   \n",
       "48    207661     3904             1                   1                 14   \n",
       "49   1394659     4006             2                   1                 10   \n",
       "50   2353526     4006             3                   1                 10   \n",
       "51   2269226     4006             5                   3                 13   \n",
       "52    367316     4006             9                   2                 11   \n",
       "53   1448367     4006            12                   1                 11   \n",
       "54     19989     4122            11                   2                  7   \n",
       "55   2735922     4141             2                   0                  8   \n",
       "56   2469422     4343             1                   1                 16   \n",
       "57   3185558     4553             3                   5                 16   \n",
       "58    350540     4845             6                   0                 13   \n",
       "59    513100     6866             1                   4                 15   \n",
       "60   2081405     7275             5                   2                 23   \n",
       "61   2209807     7669             3                   3                 15   \n",
       "62    921798     7669             4                   1                 17   \n",
       "63   2540226     7669            20                   1                  8   \n",
       "64   2265792     7981             9                   2                 15   \n",
       "65   3357714     7981            13                   0                 23   \n",
       "66   1186573     7981            14                   2                 23   \n",
       "67   3209740     7981            24                   2                 13   \n",
       "68    138748     7981            30                   3                 16   \n",
       "69   2027157     8703            34                   4                 13   \n",
       "70   3052954     8723             4                   3                 10   \n",
       "71   1486756     8735             8                   4                 11   \n",
       "72    632514     9293             2                   0                 12   \n",
       "73   1253470     9293             3                   3                 13   \n",
       "74    837303     9467             1                   0                 17   \n",
       "75   2734881     9694             2                   0                 17   \n",
       "76   2164394     9694             4                   4                 18   \n",
       "77   3354551     9694             5                   4                 15   \n",
       "78   1381165     9694             6                   4                 21   \n",
       "79   2809438     9694             7                   4                 18   \n",
       "80    425352     9694            10                   4                  7   \n",
       "81   2453441     9694            14                   4                 22   \n",
       "82   1189695     9694            15                   4                 16   \n",
       "83   1759231     9694            16                   4                 15   \n",
       "84    482648     9694            20                   4                 13   \n",
       "85   2201348     9694            22                   4                 14   \n",
       "86   3351592     9848            17                   4                 18   \n",
       "87   2164672    10113             6                   4                 18   \n",
       "88   1526735    10315             1                   0                 16   \n",
       "89   2057892    10479            22                   1                 16   \n",
       "90    356685    10708             4                   2                 14   \n",
       "91   1592736    10708             8                   2                 14   \n",
       "92   3125810    10789            10                   0                 21   \n",
       "93    846661    10789            11                   0                 19   \n",
       "94   2363761    10789            16                   2                 16   \n",
       "95    602103    10831             8                   3                 11   \n",
       "96     49629    11119            18                   1                 14   \n",
       "97    317888    11186            13                   5                 16   \n",
       "98    682486    11243            16                   3                 13   \n",
       "99   2861386    11448            10                   2                 15   \n",
       "\n",
       "    days_since_prior_order  add_to_cart_order  reordered _merge  \\\n",
       "0                      3.0                  5          0   both   \n",
       "1                     20.0                  1          1   both   \n",
       "2                      6.0                 20          0   both   \n",
       "3                      0.0                 10          0   both   \n",
       "4                      9.0                 11          1   both   \n",
       "5                     26.0                  7          0   both   \n",
       "6                     30.0                  2          0   both   \n",
       "7                     30.0                  1          0   both   \n",
       "8                     19.0                  1          0   both   \n",
       "9                     15.0                  2          1   both   \n",
       "10                     0.0                  1          0   both   \n",
       "11                     7.0                  1          0   both   \n",
       "12                     7.0                  3          1   both   \n",
       "13                    12.0                  4          1   both   \n",
       "14                     2.0                  8          0   both   \n",
       "15                     6.0                 18          1   both   \n",
       "16                    10.0                 12          1   both   \n",
       "17                     7.0                 10          0   both   \n",
       "18                     6.0                  9          1   both   \n",
       "19                    14.0                  9          1   both   \n",
       "20                     8.0                  7          1   both   \n",
       "21                     6.0                  6          1   both   \n",
       "22                    28.0                  6          1   both   \n",
       "23                     7.0                 10          1   both   \n",
       "24                     7.0                  3          1   both   \n",
       "25                     0.0                  5          1   both   \n",
       "26                     7.0                 11          1   both   \n",
       "27                     7.0                 11          1   both   \n",
       "28                     7.0                  5          1   both   \n",
       "29                     7.0                 15          1   both   \n",
       "30                     3.0                  4          1   both   \n",
       "31                    16.0                  6          1   both   \n",
       "32                     5.0                  1          1   both   \n",
       "33                     6.0                  1          1   both   \n",
       "34                     0.0                  2          0   both   \n",
       "35                    21.0                  2          1   both   \n",
       "36                     2.0                  3          1   both   \n",
       "37                     0.0                  1          0   both   \n",
       "38                    18.0                  6          1   both   \n",
       "39                     8.0                  3          1   both   \n",
       "40                    30.0                 10          1   both   \n",
       "41                    25.0                  1          1   both   \n",
       "42                     0.0                 12          0   both   \n",
       "43                     9.0                 10          0   both   \n",
       "44                    10.0                  5          0   both   \n",
       "45                     7.0                 11          0   both   \n",
       "46                     0.0                  8          0   both   \n",
       "47                    30.0                  7          0   both   \n",
       "48                     0.0                  2          0   both   \n",
       "49                     7.0                  8          0   both   \n",
       "50                     7.0                  2          1   both   \n",
       "51                     7.0                  6          1   both   \n",
       "52                     5.0                  3          1   both   \n",
       "53                     4.0                  2          1   both   \n",
       "54                     8.0                  1          0   both   \n",
       "55                    30.0                  2          0   both   \n",
       "56                     0.0                  9          0   both   \n",
       "57                    29.0                  1          0   both   \n",
       "58                    12.0                  7          0   both   \n",
       "59                     0.0                 11          0   both   \n",
       "60                     4.0                  6          0   both   \n",
       "61                     5.0                  5          0   both   \n",
       "62                     5.0                  6          1   both   \n",
       "63                    13.0                  3          1   both   \n",
       "64                    10.0                  2          0   both   \n",
       "65                     6.0                  1          1   both   \n",
       "66                     9.0                  1          1   both   \n",
       "67                     6.0                  2          1   both   \n",
       "68                     8.0                  2          1   both   \n",
       "69                     1.0                 13          0   both   \n",
       "70                     7.0                  2          0   both   \n",
       "71                    21.0                  2          0   both   \n",
       "72                    30.0                  2          0   both   \n",
       "73                    30.0                  1          1   both   \n",
       "74                     0.0                 11          0   both   \n",
       "75                    30.0                  3          0   both   \n",
       "76                     8.0                  2          1   both   \n",
       "77                     7.0                  1          1   both   \n",
       "78                    14.0                  2          1   both   \n",
       "79                    28.0                  1          1   both   \n",
       "80                    16.0                  4          1   both   \n",
       "81                     1.0                  1          1   both   \n",
       "82                    14.0                  1          1   both   \n",
       "83                     7.0                  2          1   both   \n",
       "84                     8.0                  1          1   both   \n",
       "85                    16.0                  7          1   both   \n",
       "86                     8.0                 16          0   both   \n",
       "87                    14.0                  5          0   both   \n",
       "88                     0.0                  2          0   both   \n",
       "89                     8.0                  8          0   both   \n",
       "90                    14.0                 15          0   both   \n",
       "91                    14.0                  9          1   both   \n",
       "92                    10.0                  1          0   both   \n",
       "93                    14.0                  4          1   both   \n",
       "94                     4.0                  2          1   both   \n",
       "95                    23.0                  5          0   both   \n",
       "96                    30.0                  1          0   both   \n",
       "97                     2.0                  8          0   both   \n",
       "98                     0.0                  2          0   both   \n",
       "99                     3.0                  6          0   both   \n",
       "\n",
       "      price_range_loc     Busiest day    Busiest days  \\\n",
       "0   Mid-range product  Regularly busy  Regularly busy   \n",
       "1   Mid-range product  Regularly busy  Regularly busy   \n",
       "2   Mid-range product     Busiest day    Busiest days   \n",
       "3   Mid-range product  Regularly busy    Slowest days   \n",
       "4   Mid-range product      Least busy    Slowest days   \n",
       "5   Mid-range product  Regularly busy    Busiest days   \n",
       "6   Mid-range product  Regularly busy  Regularly busy   \n",
       "7   Mid-range product  Regularly busy    Slowest days   \n",
       "8   Mid-range product  Regularly busy    Busiest days   \n",
       "9   Mid-range product  Regularly busy    Busiest days   \n",
       "10  Mid-range product  Regularly busy  Regularly busy   \n",
       "11  Mid-range product  Regularly busy    Slowest days   \n",
       "12  Mid-range product      Least busy    Slowest days   \n",
       "13  Mid-range product  Regularly busy  Regularly busy   \n",
       "14  Mid-range product  Regularly busy    Slowest days   \n",
       "15  Mid-range product  Regularly busy  Regularly busy   \n",
       "16  Mid-range product  Regularly busy  Regularly busy   \n",
       "17  Mid-range product  Regularly busy  Regularly busy   \n",
       "18  Mid-range product  Regularly busy    Busiest days   \n",
       "19  Mid-range product  Regularly busy    Busiest days   \n",
       "20  Mid-range product  Regularly busy  Regularly busy   \n",
       "21  Mid-range product  Regularly busy    Busiest days   \n",
       "22  Mid-range product  Regularly busy    Busiest days   \n",
       "23  Mid-range product  Regularly busy    Busiest days   \n",
       "24  Mid-range product  Regularly busy    Busiest days   \n",
       "25  Mid-range product     Busiest day    Busiest days   \n",
       "26  Mid-range product     Busiest day    Busiest days   \n",
       "27  Mid-range product     Busiest day    Busiest days   \n",
       "28  Mid-range product     Busiest day    Busiest days   \n",
       "29  Mid-range product     Busiest day    Busiest days   \n",
       "30  Mid-range product      Least busy    Slowest days   \n",
       "31  Mid-range product  Regularly busy  Regularly busy   \n",
       "32  Mid-range product  Regularly busy    Busiest days   \n",
       "33  Mid-range product     Busiest day    Busiest days   \n",
       "34  Mid-range product  Regularly busy  Regularly busy   \n",
       "35  Mid-range product  Regularly busy  Regularly busy   \n",
       "36  Mid-range product      Least busy    Slowest days   \n",
       "37  Mid-range product  Regularly busy    Busiest days   \n",
       "38  Mid-range product     Busiest day    Busiest days   \n",
       "39  Mid-range product  Regularly busy    Busiest days   \n",
       "40  Mid-range product     Busiest day    Busiest days   \n",
       "41  Mid-range product      Least busy    Slowest days   \n",
       "42  Mid-range product     Busiest day    Busiest days   \n",
       "43  Mid-range product  Regularly busy    Busiest days   \n",
       "44  Mid-range product  Regularly busy    Busiest days   \n",
       "45  Mid-range product  Regularly busy    Slowest days   \n",
       "46  Mid-range product  Regularly busy  Regularly busy   \n",
       "47  Mid-range product  Regularly busy  Regularly busy   \n",
       "48  Mid-range product  Regularly busy    Busiest days   \n",
       "49  Mid-range product  Regularly busy    Busiest days   \n",
       "50  Mid-range product  Regularly busy    Busiest days   \n",
       "51  Mid-range product  Regularly busy    Slowest days   \n",
       "52  Mid-range product  Regularly busy  Regularly busy   \n",
       "53  Mid-range product  Regularly busy    Busiest days   \n",
       "54  Mid-range product  Regularly busy  Regularly busy   \n",
       "55  Mid-range product     Busiest day    Busiest days   \n",
       "56  Mid-range product  Regularly busy    Busiest days   \n",
       "57  Mid-range product  Regularly busy  Regularly busy   \n",
       "58  Mid-range product     Busiest day    Busiest days   \n",
       "59  Mid-range product      Least busy    Slowest days   \n",
       "60  Mid-range product  Regularly busy  Regularly busy   \n",
       "61  Mid-range product  Regularly busy    Slowest days   \n",
       "62  Mid-range product  Regularly busy    Busiest days   \n",
       "63  Mid-range product  Regularly busy    Busiest days   \n",
       "64  Mid-range product  Regularly busy  Regularly busy   \n",
       "65  Mid-range product     Busiest day    Busiest days   \n",
       "66  Mid-range product  Regularly busy  Regularly busy   \n",
       "67  Mid-range product  Regularly busy  Regularly busy   \n",
       "68  Mid-range product  Regularly busy    Slowest days   \n",
       "69  Mid-range product      Least busy    Slowest days   \n",
       "70  Mid-range product  Regularly busy    Slowest days   \n",
       "71  Mid-range product      Least busy    Slowest days   \n",
       "72  Mid-range product     Busiest day    Busiest days   \n",
       "73  Mid-range product  Regularly busy    Slowest days   \n",
       "74  Mid-range product     Busiest day    Busiest days   \n",
       "75  Mid-range product     Busiest day    Busiest days   \n",
       "76  Mid-range product      Least busy    Slowest days   \n",
       "77  Mid-range product      Least busy    Slowest days   \n",
       "78  Mid-range product      Least busy    Slowest days   \n",
       "79  Mid-range product      Least busy    Slowest days   \n",
       "80  Mid-range product      Least busy    Slowest days   \n",
       "81  Mid-range product      Least busy    Slowest days   \n",
       "82  Mid-range product      Least busy    Slowest days   \n",
       "83  Mid-range product      Least busy    Slowest days   \n",
       "84  Mid-range product      Least busy    Slowest days   \n",
       "85  Mid-range product      Least busy    Slowest days   \n",
       "86  Mid-range product      Least busy    Slowest days   \n",
       "87  Mid-range product      Least busy    Slowest days   \n",
       "88  Mid-range product     Busiest day    Busiest days   \n",
       "89  Mid-range product  Regularly busy    Busiest days   \n",
       "90  Mid-range product  Regularly busy  Regularly busy   \n",
       "91  Mid-range product  Regularly busy  Regularly busy   \n",
       "92  Mid-range product     Busiest day    Busiest days   \n",
       "93  Mid-range product     Busiest day    Busiest days   \n",
       "94  Mid-range product  Regularly busy  Regularly busy   \n",
       "95  Mid-range product  Regularly busy    Slowest days   \n",
       "96  Mid-range product  Regularly busy    Busiest days   \n",
       "97  Mid-range product  Regularly busy  Regularly busy   \n",
       "98  Mid-range product  Regularly busy    Slowest days   \n",
       "99  Mid-range product  Regularly busy  Regularly busy   \n",
       "\n",
       "   busiest_period_of_day_range  max_order  \n",
       "0                  Most Orders         32  \n",
       "1               Average Orders         32  \n",
       "2               Average Orders          5  \n",
       "3                  Most Orders          3  \n",
       "4               Average Orders          3  \n",
       "5               Average Orders         26  \n",
       "6                  Most Orders          9  \n",
       "7                  Most Orders         12  \n",
       "8               Average Orders         20  \n",
       "9                  Most Orders         20  \n",
       "10                 Most Orders         10  \n",
       "11                 Most Orders         43  \n",
       "12                 Most Orders         43  \n",
       "13                 Most Orders         43  \n",
       "14                 Most Orders         13  \n",
       "15              Average Orders         13  \n",
       "16              Average Orders         13  \n",
       "17               Fewest Orders         28  \n",
       "18              Average Orders         28  \n",
       "19               Fewest Orders         28  \n",
       "20               Fewest Orders         28  \n",
       "21               Fewest Orders         28  \n",
       "22               Fewest Orders         28  \n",
       "23                 Most Orders         28  \n",
       "24                 Most Orders         28  \n",
       "25              Average Orders         28  \n",
       "26              Average Orders         28  \n",
       "27              Average Orders         28  \n",
       "28              Average Orders         28  \n",
       "29              Average Orders         28  \n",
       "30              Average Orders         28  \n",
       "31              Average Orders         28  \n",
       "32               Fewest Orders         28  \n",
       "33              Average Orders         28  \n",
       "34                 Most Orders          4  \n",
       "35                 Most Orders          4  \n",
       "36              Average Orders          4  \n",
       "37              Average Orders         10  \n",
       "38                 Most Orders         10  \n",
       "39              Average Orders         10  \n",
       "40              Average Orders         10  \n",
       "41                 Most Orders         10  \n",
       "42                 Most Orders         10  \n",
       "43                 Most Orders         23  \n",
       "44              Average Orders         25  \n",
       "45                 Most Orders         13  \n",
       "46              Average Orders          5  \n",
       "47                 Most Orders          6  \n",
       "48                 Most Orders          3  \n",
       "49                 Most Orders         42  \n",
       "50                 Most Orders         42  \n",
       "51                 Most Orders         42  \n",
       "52                 Most Orders         42  \n",
       "53                 Most Orders         42  \n",
       "54              Average Orders         40  \n",
       "55              Average Orders          9  \n",
       "56                 Most Orders          4  \n",
       "57                 Most Orders          4  \n",
       "58                 Most Orders         14  \n",
       "59                 Most Orders          6  \n",
       "60               Fewest Orders          8  \n",
       "61                 Most Orders         21  \n",
       "62              Average Orders         21  \n",
       "63              Average Orders         21  \n",
       "64                 Most Orders         31  \n",
       "65               Fewest Orders         31  \n",
       "66               Fewest Orders         31  \n",
       "67                 Most Orders         31  \n",
       "68                 Most Orders         31  \n",
       "69                 Most Orders         99  \n",
       "70                 Most Orders         36  \n",
       "71                 Most Orders          9  \n",
       "72                 Most Orders          5  \n",
       "73                 Most Orders          5  \n",
       "74              Average Orders          5  \n",
       "75              Average Orders         23  \n",
       "76              Average Orders         23  \n",
       "77                 Most Orders         23  \n",
       "78              Average Orders         23  \n",
       "79              Average Orders         23  \n",
       "80              Average Orders         23  \n",
       "81              Average Orders         23  \n",
       "82                 Most Orders         23  \n",
       "83                 Most Orders         23  \n",
       "84                 Most Orders         23  \n",
       "85                 Most Orders         23  \n",
       "86              Average Orders         23  \n",
       "87              Average Orders         11  \n",
       "88                 Most Orders          5  \n",
       "89                 Most Orders         22  \n",
       "90                 Most Orders         21  \n",
       "91                 Most Orders         21  \n",
       "92              Average Orders         36  \n",
       "93              Average Orders         36  \n",
       "94                 Most Orders         36  \n",
       "95                 Most Orders         10  \n",
       "96                 Most Orders         23  \n",
       "97                 Most Orders         26  \n",
       "98                 Most Orders         43  \n",
       "99                 Most Orders         21  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ords_prods_merge.head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "8dbc02ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# deriving columns with loc()\n",
    "\n",
    "ords_prods_merge.loc[ords_prods_merge['max_order'] > 40, 'loyalty_flag'] = 'Loyal customer'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ab8823be",
   "metadata": {},
   "outputs": [],
   "source": [
    "ords_prods_merge.loc[(ords_prods_merge['max_order'] <= 40) & (ords_prods_merge['max_order'] > 10), 'loyalty_flag'] = 'Regular customer'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "73824a78",
   "metadata": {},
   "outputs": [],
   "source": [
    "ords_prods_merge.loc[ords_prods_merge['max_order'] <= 10, 'loyalty_flag'] = 'New customer'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "578920fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Regular customer    15891077\n",
       "Loyal customer      10293737\n",
       "New customer         6249398\n",
       "Name: loyalty_flag, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ords_prods_merge['loyalty_flag'].value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "0ae15ea6",
   "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>user_id</th>\n",
       "      <th>loyalty_flag</th>\n",
       "      <th>order_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>138</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>138</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>709</td>\n",
       "      <td>New customer</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>764</td>\n",
       "      <td>New customer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>764</td>\n",
       "      <td>New customer</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>777</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>825</td>\n",
       "      <td>New customer</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>910</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1052</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1052</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1379</td>\n",
       "      <td>New customer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1480</td>\n",
       "      <td>Loyal customer</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1480</td>\n",
       "      <td>Loyal customer</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1480</td>\n",
       "      <td>Loyal customer</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1494</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1494</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1494</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>1540</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>1598</td>\n",
       "      <td>New customer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>1598</td>\n",
       "      <td>New customer</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>1598</td>\n",
       "      <td>New customer</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>1647</td>\n",
       "      <td>New customer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>1647</td>\n",
       "      <td>New customer</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1647</td>\n",
       "      <td>New customer</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1647</td>\n",
       "      <td>New customer</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>1647</td>\n",
       "      <td>New customer</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2833</td>\n",
       "      <td>New customer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2850</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2857</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>3010</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>3029</td>\n",
       "      <td>New customer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>3395</td>\n",
       "      <td>New customer</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>3904</td>\n",
       "      <td>New customer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>4006</td>\n",
       "      <td>Loyal customer</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>4006</td>\n",
       "      <td>Loyal customer</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>4006</td>\n",
       "      <td>Loyal customer</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>4006</td>\n",
       "      <td>Loyal customer</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>4006</td>\n",
       "      <td>Loyal customer</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>4122</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>4141</td>\n",
       "      <td>New customer</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>4343</td>\n",
       "      <td>New customer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>4553</td>\n",
       "      <td>New customer</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>4845</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>6866</td>\n",
       "      <td>New customer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    user_id      loyalty_flag  order_number\n",
       "0       138  Regular customer            28\n",
       "1       138  Regular customer            30\n",
       "2       709      New customer             2\n",
       "3       764      New customer             1\n",
       "4       764      New customer             3\n",
       "5       777  Regular customer            16\n",
       "6       825      New customer             3\n",
       "7       910  Regular customer            12\n",
       "8      1052  Regular customer            10\n",
       "9      1052  Regular customer            15\n",
       "10     1379      New customer             1\n",
       "11     1480    Loyal customer            36\n",
       "12     1480    Loyal customer            38\n",
       "13     1480    Loyal customer            39\n",
       "14     1494  Regular customer             6\n",
       "15     1494  Regular customer             7\n",
       "16     1494  Regular customer             9\n",
       "17     1540  Regular customer             4\n",
       "18     1540  Regular customer             5\n",
       "19     1540  Regular customer             6\n",
       "20     1540  Regular customer             7\n",
       "21     1540  Regular customer             8\n",
       "22     1540  Regular customer             9\n",
       "23     1540  Regular customer            14\n",
       "24     1540  Regular customer            15\n",
       "25     1540  Regular customer            18\n",
       "26     1540  Regular customer            19\n",
       "27     1540  Regular customer            20\n",
       "28     1540  Regular customer            21\n",
       "29     1540  Regular customer            22\n",
       "30     1540  Regular customer            24\n",
       "31     1540  Regular customer            25\n",
       "32     1540  Regular customer            27\n",
       "33     1540  Regular customer            28\n",
       "34     1598      New customer             1\n",
       "35     1598      New customer             2\n",
       "36     1598      New customer             3\n",
       "37     1647      New customer             1\n",
       "38     1647      New customer             4\n",
       "39     1647      New customer             6\n",
       "40     1647      New customer             8\n",
       "41     1647      New customer             9\n",
       "42     2833      New customer             1\n",
       "43     2850  Regular customer             7\n",
       "44     2857  Regular customer            24\n",
       "45     3010  Regular customer             5\n",
       "46     3029      New customer             1\n",
       "47     3395      New customer             4\n",
       "48     3904      New customer             1\n",
       "49     4006    Loyal customer             2\n",
       "50     4006    Loyal customer             3\n",
       "51     4006    Loyal customer             5\n",
       "52     4006    Loyal customer             9\n",
       "53     4006    Loyal customer            12\n",
       "54     4122  Regular customer            11\n",
       "55     4141      New customer             2\n",
       "56     4343      New customer             1\n",
       "57     4553      New customer             3\n",
       "58     4845  Regular customer             6\n",
       "59     6866      New customer             1"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the multiple columns\n",
    "\n",
    "ords_prods_merge[['user_id', 'loyalty_flag', 'order_number']].head(60)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80b0e27e",
   "metadata": {},
   "source": [
    "### 2.4 Basic Product Price Statistics for Customer Loyalty Categories *\n",
    "\n",
    "#### Check the basic statistics of the product prices for each loyalty category (Loyal Customer, Regular Customer, and New Customer). What you’re trying to determine is whether the prices of products purchased by loyal customers differ from those purchased by regular or new customers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "7617d2b7",
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">prices</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>loyalty_flag</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Loyal customer</th>\n",
       "      <td>10.388747</td>\n",
       "      <td>1.0</td>\n",
       "      <td>99999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New customer</th>\n",
       "      <td>13.294370</td>\n",
       "      <td>1.0</td>\n",
       "      <td>99999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Regular customer</th>\n",
       "      <td>12.496203</td>\n",
       "      <td>1.0</td>\n",
       "      <td>99999.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     prices              \n",
       "                       mean  min      max\n",
       "loyalty_flag                             \n",
       "Loyal customer    10.388747  1.0  99999.0\n",
       "New customer      13.294370  1.0  99999.0\n",
       "Regular customer  12.496203  1.0  99999.0"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ords_prods_merge.groupby('loyalty_flag').agg({'prices': ['mean', 'min', 'max']})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "284b552a",
   "metadata": {},
   "source": [
    "### 2.5 Spending Categories (above and below average price per product) *\n",
    "\n",
    "#### Create a spending flag for each user based on the average price across all their orders using the following criteria: If the mean of the prices of products purchased by a user is lower than 10, then flag them as a “Low spender.” If the mean of the prices of products purchased by a user is higher than or equal to 10, then flag them as a “High spender.”"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "53ce6d2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# establish spending flag criteria - create mean column, group “user_id” column, generate mean for “prices”\n",
    "\n",
    "ords_prods_merge['mean_price'] = ords_prods_merge.groupby(['user_id'])['prices'].transform(np.mean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "0f071a92",
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>prices</th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_number</th>\n",
       "      <th>orders_day_of_week</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>...</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "      <th>_merge</th>\n",
       "      <th>price_range_loc</th>\n",
       "      <th>Busiest day</th>\n",
       "      <th>Busiest days</th>\n",
       "      <th>busiest_period_of_day_range</th>\n",
       "      <th>max_order</th>\n",
       "      <th>loyalty_flag</th>\n",
       "      <th>mean_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3139998</td>\n",
       "      <td>138</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>32</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>6.935811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1977647</td>\n",
       "      <td>138</td>\n",
       "      <td>30</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>32</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>6.935811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>389851</td>\n",
       "      <td>709</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>...</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>5</td>\n",
       "      <td>New customer</td>\n",
       "      <td>7.930208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>652770</td>\n",
       "      <td>764</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>3</td>\n",
       "      <td>New customer</td>\n",
       "      <td>4.972414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1813452</td>\n",
       "      <td>764</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>...</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>3</td>\n",
       "      <td>New customer</td>\n",
       "      <td>4.972414</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id                product_name  aisle_id  department_id  prices  \\\n",
       "0           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "1           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "2           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "3           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "4           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "\n",
       "   order_id  user_id  order_number  orders_day_of_week  order_hour_of_day  \\\n",
       "0   3139998      138            28                   6                 11   \n",
       "1   1977647      138            30                   6                 17   \n",
       "2    389851      709             2                   0                 21   \n",
       "3    652770      764             1                   3                 13   \n",
       "4   1813452      764             3                   4                 17   \n",
       "\n",
       "   ...  add_to_cart_order  reordered  _merge    price_range_loc  \\\n",
       "0  ...                  5          0    both  Mid-range product   \n",
       "1  ...                  1          1    both  Mid-range product   \n",
       "2  ...                 20          0    both  Mid-range product   \n",
       "3  ...                 10          0    both  Mid-range product   \n",
       "4  ...                 11          1    both  Mid-range product   \n",
       "\n",
       "      Busiest day    Busiest days busiest_period_of_day_range max_order  \\\n",
       "0  Regularly busy  Regularly busy                 Most Orders        32   \n",
       "1  Regularly busy  Regularly busy              Average Orders        32   \n",
       "2     Busiest day    Busiest days              Average Orders         5   \n",
       "3  Regularly busy    Slowest days                 Most Orders         3   \n",
       "4      Least busy    Slowest days              Average Orders         3   \n",
       "\n",
       "       loyalty_flag mean_price  \n",
       "0  Regular customer   6.935811  \n",
       "1  Regular customer   6.935811  \n",
       "2      New customer   7.930208  \n",
       "3      New customer   4.972414  \n",
       "4      New customer   4.972414  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the data \n",
    "\n",
    "ords_prods_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "74ef5e1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# deriving columns with loc()\n",
    "\n",
    "ords_prods_merge.loc[ords_prods_merge['mean_price'] >= 10, 'Spender_flag'] = 'High spender'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "646d4a43",
   "metadata": {},
   "outputs": [],
   "source": [
    "ords_prods_merge.loc[ords_prods_merge['mean_price'] < 10, 'Spender_flag'] = 'Low spender'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "f3406385",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Low spender     31798850\n",
       "High spender      635362\n",
       "Name: Spender_flag, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ords_prods_merge['Spender_flag'].value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e40b8a9",
   "metadata": {},
   "source": [
    "### 2.6 Frequent and Non-Frequent Customers * \n",
    "\n",
    "#### To determine frequent versus non-frequent customers. Create an order frequency flag that marks the regularity of a user’s ordering behavior according to the median in the “days_since_prior_order” column. The criteria for the flag should be as follows: If the median of “days_since_prior_order” is higher than 20, then the customer should be labeled a “Non-frequent customer.” If the median is higher than 10 and lower than or equal to 20, then the customer should be labeled a “Regular customer.\" If the median is lower than or equal to 10, then the customer should be labeled a “Frequent customer.”"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "9060a0d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['product_id', 'product_name', 'aisle_id', 'department_id', 'prices',\n",
       "       'order_id', 'user_id', 'order_number', 'orders_day_of_week',\n",
       "       'order_hour_of_day', 'days_since_prior_order', 'add_to_cart_order',\n",
       "       'reordered', '_merge', 'price_range_loc', 'Busiest day', 'Busiest days',\n",
       "       'busiest_period_of_day_range', 'max_order', 'loyalty_flag',\n",
       "       'mean_price', 'Spender_flag'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ords_prods_merge.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "df3ecf60",
   "metadata": {},
   "outputs": [],
   "source": [
    "# establish order frequency flag criteria - create median column for order frequency, group “user_id” column, generate median for “prices”\n",
    "\n",
    "ords_prods_merge['median_order_frequency'] = ords_prods_merge.groupby(['user_id'])['days_since_prior_order'].transform(np.median)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "2b56a13d",
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>prices</th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_number</th>\n",
       "      <th>orders_day_of_week</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>...</th>\n",
       "      <th>_merge</th>\n",
       "      <th>price_range_loc</th>\n",
       "      <th>Busiest day</th>\n",
       "      <th>Busiest days</th>\n",
       "      <th>busiest_period_of_day_range</th>\n",
       "      <th>max_order</th>\n",
       "      <th>loyalty_flag</th>\n",
       "      <th>mean_price</th>\n",
       "      <th>Spender_flag</th>\n",
       "      <th>median_order_frequency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3139998</td>\n",
       "      <td>138</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>32</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>6.935811</td>\n",
       "      <td>Low spender</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1977647</td>\n",
       "      <td>138</td>\n",
       "      <td>30</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>...</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>32</td>\n",
       "      <td>Regular customer</td>\n",
       "      <td>6.935811</td>\n",
       "      <td>Low spender</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>389851</td>\n",
       "      <td>709</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>...</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Busiest day</td>\n",
       "      <td>Busiest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>5</td>\n",
       "      <td>New customer</td>\n",
       "      <td>7.930208</td>\n",
       "      <td>Low spender</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>652770</td>\n",
       "      <td>764</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>...</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Regularly busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Most Orders</td>\n",
       "      <td>3</td>\n",
       "      <td>New customer</td>\n",
       "      <td>4.972414</td>\n",
       "      <td>Low spender</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Chocolate Sandwich Cookies</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1813452</td>\n",
       "      <td>764</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>...</td>\n",
       "      <td>both</td>\n",
       "      <td>Mid-range product</td>\n",
       "      <td>Least busy</td>\n",
       "      <td>Slowest days</td>\n",
       "      <td>Average Orders</td>\n",
       "      <td>3</td>\n",
       "      <td>New customer</td>\n",
       "      <td>4.972414</td>\n",
       "      <td>Low spender</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id                product_name  aisle_id  department_id  prices  \\\n",
       "0           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "1           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "2           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "3           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "4           1  Chocolate Sandwich Cookies        61             19     5.8   \n",
       "\n",
       "   order_id  user_id  order_number  orders_day_of_week  order_hour_of_day  \\\n",
       "0   3139998      138            28                   6                 11   \n",
       "1   1977647      138            30                   6                 17   \n",
       "2    389851      709             2                   0                 21   \n",
       "3    652770      764             1                   3                 13   \n",
       "4   1813452      764             3                   4                 17   \n",
       "\n",
       "   ...  _merge    price_range_loc     Busiest day    Busiest days  \\\n",
       "0  ...    both  Mid-range product  Regularly busy  Regularly busy   \n",
       "1  ...    both  Mid-range product  Regularly busy  Regularly busy   \n",
       "2  ...    both  Mid-range product     Busiest day    Busiest days   \n",
       "3  ...    both  Mid-range product  Regularly busy    Slowest days   \n",
       "4  ...    both  Mid-range product      Least busy    Slowest days   \n",
       "\n",
       "  busiest_period_of_day_range max_order      loyalty_flag mean_price  \\\n",
       "0                 Most Orders        32  Regular customer   6.935811   \n",
       "1              Average Orders        32  Regular customer   6.935811   \n",
       "2              Average Orders         5      New customer   7.930208   \n",
       "3                 Most Orders         3      New customer   4.972414   \n",
       "4              Average Orders         3      New customer   4.972414   \n",
       "\n",
       "   Spender_flag median_order_frequency  \n",
       "0   Low spender                    8.0  \n",
       "1   Low spender                    8.0  \n",
       "2   Low spender                    6.0  \n",
       "3   Low spender                    9.0  \n",
       "4   Low spender                    9.0  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ords_prods_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "6497c8d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# deriving columns with loc()\n",
    "\n",
    "ords_prods_merge.loc[ords_prods_merge['median_order_frequency'] > 20, 'Frequency_flag'] = 'Non-frequent customer'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "97819efd",
   "metadata": {},
   "outputs": [],
   "source": [
    "ords_prods_merge.loc[(ords_prods_merge['median_order_frequency'] <= 20) & (ords_prods_merge['median_order_frequency'] > 10), 'Frequency_flag'] = 'Regular customer'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "5a8a60ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "ords_prods_merge.loc[ords_prods_merge['median_order_frequency'] <= 10, 'Frequency_flag'] = 'Frequent customer'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "e34db7ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Frequent customer        22814871\n",
       "Regular customer          6929354\n",
       "Non-frequent customer     2689987\n",
       "Name: Frequency_flag, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ords_prods_merge['Frequency_flag'].value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fa9211f",
   "metadata": {},
   "source": [
    "## 3. Exporting data *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "21e0e918",
   "metadata": {},
   "outputs": [],
   "source": [
    "ords_prods_merge.to_pickle(os.path.join(path, '02 Data','Prepared Data', 'grouped_merged.pkl'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cad58402",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}