setup.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "import numpy\n",
    "import datetime\n",
    "\n",
    "sales = pandas.read_csv(\"Data Model - Pizza Sales.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['order_details_id', 'order_id', 'pizza_id', 'quantity', 'order_date',\n",
       "       'order_time', 'unit_price', 'total_price', 'pizza_size',\n",
       "       'pizza_category', 'pizza_ingredients', 'pizza_name'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[    1     2     3 ... 48618 48619 48620]\n",
      "[    1     2     3 ... 21348 21349 21350]\n",
      "['hawaiian_m' 'classic_dlx_m' 'five_cheese_l' 'ital_supr_l' 'mexicana_m'\n",
      " 'thai_ckn_l' 'ital_supr_m' 'prsc_argla_l' 'bbq_ckn_s' 'the_greek_s'\n",
      " 'spinach_supr_s' 'classic_dlx_s' 'green_garden_s' 'ital_cpcllo_l'\n",
      " 'ital_supr_s' 'mexicana_s' 'spicy_ital_l' 'spin_pesto_l' 'veggie_veg_s'\n",
      " 'mexicana_l' 'southw_ckn_l' 'bbq_ckn_l' 'cali_ckn_l' 'cali_ckn_m'\n",
      " 'pepperoni_l' 'cali_ckn_s' 'ckn_pesto_l' 'big_meat_s' 'soppressata_l'\n",
      " 'four_cheese_l' 'napolitana_s' 'calabrese_m' 'four_cheese_m'\n",
      " 'ital_veggie_s' 'mediterraneo_m' 'peppr_salami_s' 'spinach_fet_l'\n",
      " 'napolitana_l' 'sicilian_l' 'ital_cpcllo_m' 'southw_ckn_s' 'bbq_ckn_m'\n",
      " 'pepperoni_m' 'prsc_argla_s' 'sicilian_m' 'veggie_veg_l' 'ckn_alfredo_s'\n",
      " 'pepperoni_s' 'green_garden_l' 'green_garden_m' 'pep_msh_pep_l'\n",
      " 'hawaiian_s' 'peppr_salami_m' 'ckn_alfredo_m' 'peppr_salami_l'\n",
      " 'spin_pesto_s' 'thai_ckn_m' 'classic_dlx_l' 'ckn_pesto_m' 'the_greek_xl'\n",
      " 'hawaiian_l' 'pep_msh_pep_s' 'spinach_supr_m' 'prsc_argla_m'\n",
      " 'mediterraneo_l' 'southw_ckn_m' 'pep_msh_pep_m' 'sicilian_s'\n",
      " 'spicy_ital_s' 'thai_ckn_s' 'spinach_supr_l' 'ital_veggie_l'\n",
      " 'veggie_veg_m' 'the_greek_m' 'ckn_pesto_s' 'spinach_fet_s' 'spicy_ital_m'\n",
      " 'ital_veggie_m' 'ital_cpcllo_s' 'mediterraneo_s' 'spinach_fet_m'\n",
      " 'napolitana_m' 'spin_pesto_m' 'brie_carre_s' 'ckn_alfredo_l'\n",
      " 'calabrese_s' 'the_greek_l' 'soppressata_m' 'soppressata_s' 'calabrese_l'\n",
      " 'the_greek_xxl']\n",
      "[1 2 3 4]\n",
      "['1/1/2015' '1/2/2015' '1/3/2015' '1/4/2015' '1/5/2015' '1/6/2015'\n",
      " '1/7/2015' '1/8/2015' '1/9/2015' '1/10/2015' '1/11/2015' '1/12/2015'\n",
      " '1/13/2015' '1/14/2015' '1/15/2015' '1/16/2015' '1/17/2015' '1/18/2015'\n",
      " '1/19/2015' '1/20/2015' '1/21/2015' '1/22/2015' '1/23/2015' '1/24/2015'\n",
      " '1/25/2015' '1/26/2015' '1/27/2015' '1/28/2015' '1/29/2015' '1/30/2015'\n",
      " '1/31/2015' '2/1/2015' '2/2/2015' '2/3/2015' '2/4/2015' '2/5/2015'\n",
      " '2/6/2015' '2/7/2015' '2/8/2015' '2/9/2015' '2/10/2015' '2/11/2015'\n",
      " '2/12/2015' '2/13/2015' '2/14/2015' '2/15/2015' '2/16/2015' '2/17/2015'\n",
      " '2/18/2015' '2/19/2015' '2/20/2015' '2/21/2015' '2/22/2015' '2/23/2015'\n",
      " '2/24/2015' '2/25/2015' '2/26/2015' '2/27/2015' '2/28/2015' '3/1/2015'\n",
      " '3/2/2015' '3/3/2015' '3/4/2015' '3/5/2015' '3/6/2015' '3/7/2015'\n",
      " '3/8/2015' '3/9/2015' '3/10/2015' '3/11/2015' '3/12/2015' '3/13/2015'\n",
      " '3/14/2015' '3/15/2015' '3/16/2015' '3/17/2015' '3/18/2015' '3/19/2015'\n",
      " '3/20/2015' '3/21/2015' '3/22/2015' '3/23/2015' '3/24/2015' '3/25/2015'\n",
      " '3/26/2015' '3/27/2015' '3/28/2015' '3/29/2015' '3/30/2015' '3/31/2015'\n",
      " '4/1/2015' '4/2/2015' '4/3/2015' '4/4/2015' '4/5/2015' '4/6/2015'\n",
      " '4/7/2015' '4/8/2015' '4/9/2015' '4/10/2015' '4/11/2015' '4/12/2015'\n",
      " '4/13/2015' '4/14/2015' '4/15/2015' '4/16/2015' '4/17/2015' '4/18/2015'\n",
      " '4/19/2015' '4/20/2015' '4/21/2015' '4/22/2015' '4/23/2015' '4/24/2015'\n",
      " '4/25/2015' '4/26/2015' '4/27/2015' '4/28/2015' '4/29/2015' '4/30/2015'\n",
      " '5/1/2015' '5/2/2015' '5/3/2015' '5/4/2015' '5/5/2015' '5/6/2015'\n",
      " '5/7/2015' '5/8/2015' '5/9/2015' '5/10/2015' '5/11/2015' '5/12/2015'\n",
      " '5/13/2015' '5/14/2015' '5/15/2015' '5/16/2015' '5/17/2015' '5/18/2015'\n",
      " '5/19/2015' '5/20/2015' '5/21/2015' '5/22/2015' '5/23/2015' '5/24/2015'\n",
      " '5/25/2015' '5/26/2015' '5/27/2015' '5/28/2015' '5/29/2015' '5/30/2015'\n",
      " '5/31/2015' '6/1/2015' '6/2/2015' '6/3/2015' '6/4/2015' '6/5/2015'\n",
      " '6/6/2015' '6/7/2015' '6/8/2015' '6/9/2015' '6/10/2015' '6/11/2015'\n",
      " '6/12/2015' '6/13/2015' '6/14/2015' '6/15/2015' '6/16/2015' '6/17/2015'\n",
      " '6/18/2015' '6/19/2015' '6/20/2015' '6/21/2015' '6/22/2015' '6/23/2015'\n",
      " '6/24/2015' '6/25/2015' '6/26/2015' '6/27/2015' '6/28/2015' '6/29/2015'\n",
      " '6/30/2015' '7/1/2015' '7/2/2015' '7/3/2015' '7/4/2015' '7/5/2015'\n",
      " '7/6/2015' '7/7/2015' '7/8/2015' '7/9/2015' '7/10/2015' '7/11/2015'\n",
      " '7/12/2015' '7/13/2015' '7/14/2015' '7/15/2015' '7/16/2015' '7/17/2015'\n",
      " '7/18/2015' '7/19/2015' '7/20/2015' '7/21/2015' '7/22/2015' '7/23/2015'\n",
      " '7/24/2015' '7/25/2015' '7/26/2015' '7/27/2015' '7/28/2015' '7/29/2015'\n",
      " '7/30/2015' '7/31/2015' '8/1/2015' '8/2/2015' '8/3/2015' '8/4/2015'\n",
      " '8/5/2015' '8/6/2015' '8/7/2015' '8/8/2015' '8/9/2015' '8/10/2015'\n",
      " '8/11/2015' '8/12/2015' '8/13/2015' '8/14/2015' '8/15/2015' '8/16/2015'\n",
      " '8/17/2015' '8/18/2015' '8/19/2015' '8/20/2015' '8/21/2015' '8/22/2015'\n",
      " '8/23/2015' '8/24/2015' '8/25/2015' '8/26/2015' '8/27/2015' '8/28/2015'\n",
      " '8/29/2015' '8/30/2015' '8/31/2015' '9/1/2015' '9/2/2015' '9/3/2015'\n",
      " '9/4/2015' '9/5/2015' '9/6/2015' '9/7/2015' '9/8/2015' '9/9/2015'\n",
      " '9/10/2015' '9/11/2015' '9/12/2015' '9/13/2015' '9/14/2015' '9/15/2015'\n",
      " '9/16/2015' '9/17/2015' '9/18/2015' '9/19/2015' '9/20/2015' '9/21/2015'\n",
      " '9/22/2015' '9/23/2015' '9/26/2015' '9/27/2015' '9/28/2015' '9/29/2015'\n",
      " '9/30/2015' '10/1/2015' '10/2/2015' '10/3/2015' '10/4/2015' '10/6/2015'\n",
      " '10/7/2015' '10/8/2015' '10/9/2015' '10/10/2015' '10/11/2015'\n",
      " '10/13/2015' '10/14/2015' '10/15/2015' '10/16/2015' '10/17/2015'\n",
      " '10/18/2015' '10/20/2015' '10/21/2015' '10/22/2015' '10/23/2015'\n",
      " '10/24/2015' '10/25/2015' '10/27/2015' '10/28/2015' '10/29/2015'\n",
      " '10/30/2015' '10/31/2015' '11/1/2015' '11/2/2015' '11/3/2015' '11/4/2015'\n",
      " '11/5/2015' '11/6/2015' '11/7/2015' '11/8/2015' '11/9/2015' '11/10/2015'\n",
      " '11/11/2015' '11/12/2015' '11/13/2015' '11/14/2015' '11/15/2015'\n",
      " '11/16/2015' '11/17/2015' '11/18/2015' '11/19/2015' '11/20/2015'\n",
      " '11/21/2015' '11/22/2015' '11/23/2015' '11/24/2015' '11/25/2015'\n",
      " '11/26/2015' '11/27/2015' '11/28/2015' '11/29/2015' '11/30/2015'\n",
      " '12/1/2015' '12/2/2015' '12/3/2015' '12/4/2015' '12/5/2015' '12/6/2015'\n",
      " '12/7/2015' '12/8/2015' '12/9/2015' '12/10/2015' '12/11/2015'\n",
      " '12/12/2015' '12/13/2015' '12/14/2015' '12/15/2015' '12/16/2015'\n",
      " '12/17/2015' '12/18/2015' '12/19/2015' '12/20/2015' '12/21/2015'\n",
      " '12/22/2015' '12/23/2015' '12/24/2015' '12/26/2015' '12/27/2015'\n",
      " '12/28/2015' '12/29/2015' '12/30/2015' '12/31/2015']\n",
      "['11:38:36' '11:57:40' '12:12:28' ... '20:51:07' '21:14:37' '22:09:54']\n",
      "[13.25 16.   18.5  20.75 16.5  12.75 12.   12.5  20.5  20.25 16.75 15.25\n",
      " 17.95 16.25 14.75  9.75 17.5  10.5  25.5  11.   14.5  12.25 21.   23.65\n",
      " 35.95]\n",
      "[13.25 16.   18.5  20.75 16.5  12.75 12.   12.5  20.5  20.25 16.75 15.25\n",
      " 17.95 16.25 14.75 32.    9.75 17.5  10.5  25.5  11.   14.5  12.25 21.\n",
      " 62.25 41.5  50.25 23.65 24.   33.5  35.9  25.   19.5  37.5  37.   41.\n",
      " 33.   40.5  24.5  47.3  22.   35.95 29.   29.5  26.5  48.75 32.5  30.5\n",
      " 48.   51.   36.   35.   83.   61.5  49.5  55.5 ]\n",
      "['M' 'L' 'S' 'XL' 'XXL']\n",
      "['Classic' 'Veggie' 'Supreme' 'Chicken']\n",
      "['Sliced Ham, Pineapple, Mozzarella Cheese'\n",
      " 'Pepperoni, Mushrooms, Red Onions, Red Peppers, Bacon'\n",
      " 'Mozzarella Cheese, Provolone Cheese, Smoked Gouda Cheese, Romano Cheese, Blue Cheese, Garlic'\n",
      " 'Calabrese Salami, Capocollo, Tomatoes, Red Onions, Green Olives, Garlic'\n",
      " 'Tomatoes, Red Peppers, Jalapeno Peppers, Red Onions, Cilantro, Corn, Chipotle Sauce, Garlic'\n",
      " 'Chicken, Pineapple, Tomatoes, Red Peppers, Thai Sweet Chilli Sauce'\n",
      " 'Prosciutto di San Daniele, Arugula, Mozzarella Cheese'\n",
      " 'Barbecued Chicken, Red Peppers, Green Peppers, Tomatoes, Red Onions, Barbecue Sauce'\n",
      " 'Kalamata Olives, Feta Cheese, Tomatoes, Garlic, Beef Chuck Roast, Red Onions'\n",
      " 'Spinach, Red Onions, Pepperoni, Tomatoes, Artichokes, Kalamata Olives, Garlic, Asiago Cheese'\n",
      " 'Spinach, Mushrooms, Tomatoes, Green Olives, Feta Cheese'\n",
      " 'Capocollo, Red Peppers, Tomatoes, Goat Cheese, Garlic, Oregano'\n",
      " 'Capocollo, Tomatoes, Goat Cheese, Artichokes, Peperoncini verdi, Garlic'\n",
      " 'Spinach, Artichokes, Tomatoes, Sun-dried Tomatoes, Garlic, Pesto Sauce'\n",
      " 'Mushrooms, Tomatoes, Red Peppers, Green Peppers, Red Onions, Zucchini, Spinach, Garlic'\n",
      " 'Chicken, Tomatoes, Red Peppers, Red Onions, Jalapeno Peppers, Corn, Cilantro, Chipotle Sauce'\n",
      " 'Chicken, Artichoke, Spinach, Garlic, Jalapeno Peppers, Fontina Cheese, Gouda Cheese'\n",
      " 'Mozzarella Cheese, Pepperoni'\n",
      " 'Chicken, Tomatoes, Red Peppers, Spinach, Garlic, Pesto Sauce'\n",
      " 'Bacon, Pepperoni, Italian Sausage, Chorizo Sausage'\n",
      " 'Soppressata Salami, Fontina Cheese, Mozzarella Cheese, Mushrooms, Garlic'\n",
      " 'Ricotta Cheese, Gorgonzola Piccante Cheese, Mozzarella Cheese, Parmigiano Reggiano Cheese, Garlic'\n",
      " 'Tomatoes, Anchovies, Green Olives, Red Onions, Garlic'\n",
      " '?duja Salami, Pancetta, Tomatoes, Red Onions, Friggitello Peppers, Garlic'\n",
      " 'Eggplant, Artichokes, Tomatoes, Zucchini, Red Peppers, Garlic, Pesto Sauce'\n",
      " 'Spinach, Artichokes, Kalamata Olives, Sun-dried Tomatoes, Feta Cheese, Plum Tomatoes, Red Onions'\n",
      " 'Genoa Salami, Capocollo, Pepperoni, Tomatoes, Asiago Cheese, Garlic'\n",
      " 'Spinach, Mushrooms, Red Onions, Feta Cheese, Garlic'\n",
      " 'Coarse Sicilian Salami, Tomatoes, Green Olives, Luganega Sausage, Onions, Garlic'\n",
      " 'Chicken, Red Onions, Red Peppers, Mushrooms, Asiago Cheese, Alfredo Sauce'\n",
      " 'Pepperoni, Mushrooms, Green Peppers'\n",
      " 'Brie Carre Cheese, Prosciutto, Caramelized Onions, Pears, Thyme, Garlic']\n",
      "['The Hawaiian Pizza' 'The Classic Deluxe Pizza' 'The Five Cheese Pizza'\n",
      " 'The Italian Supreme Pizza' 'The Mexicana Pizza' 'The Thai Chicken Pizza'\n",
      " 'The Prosciutto and Arugula Pizza' 'The Barbecue Chicken Pizza'\n",
      " 'The Greek Pizza' 'The Spinach Supreme Pizza' 'The Green Garden Pizza'\n",
      " 'The Italian Capocollo Pizza' 'The Spicy Italian Pizza'\n",
      " 'The Spinach Pesto Pizza' 'The Vegetables + Vegetables Pizza'\n",
      " 'The Southwest Chicken Pizza' 'The California Chicken Pizza'\n",
      " 'The Pepperoni Pizza' 'The Chicken Pesto Pizza' 'The Big Meat Pizza'\n",
      " 'The Soppressata Pizza' 'The Four Cheese Pizza' 'The Napolitana Pizza'\n",
      " 'The Calabrese Pizza' 'The Italian Vegetables Pizza'\n",
      " 'The Mediterranean Pizza' 'The Pepper Salami Pizza'\n",
      " 'The Spinach and Feta Pizza' 'The Sicilian Pizza'\n",
      " 'The Chicken Alfredo Pizza' 'The Pepperoni, Mushroom, and Peppers Pizza'\n",
      " 'The Brie Carre Pizza']\n"
     ]
    }
   ],
   "source": [
    "for column_id in sales.columns:\n",
    "    print(sales[column_id].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert order dates to a nicer format\n",
    "def changeDateFormat(date: str) -> str:\n",
    "    datetime_obj = datetime.datetime.strptime(date, \"%m/%d/%Y\")\n",
    "    return datetime_obj.strftime(\"%Y-%m-%d\")\n",
    "\n",
    "sales[\"order_date\"] = sales['order_date'].apply(changeDateFormat)\n",
    "\n",
    "#convert order dates to an indexed list for graphing/counting purposes\n",
    "def dateToIndex(date: str) -> int:\n",
    "    datetime_obj = datetime.datetime.strptime(date, \"%Y-%m-%d\")\n",
    "    return datetime_obj.timetuple().tm_yday\n",
    "\n",
    "def dateToWeekday(date: str) -> str:\n",
    "    datetime_obj = datetime.datetime.strptime(date, \"%Y-%m-%d\")\n",
    "    return [\"Monday\", \"Tuesday\", \"Wednesday\", \"Thursday\", \"Friday\", \"Saturday\", \"Sunday\"][datetime_obj.weekday()]\n",
    "\n",
    "sales[\"order_date_index\"] = sales[\"order_date\"].apply(dateToIndex)\n",
    "sales[\"order_date_weekday\"] = sales[\"order_date\"].apply(dateToWeekday)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>item_count</th>\n",
       "      <th>unique_item_count</th>\n",
       "      <th>total_price</th>\n",
       "      <th>order_date_index</th>\n",
       "      <th>order_date</th>\n",
       "      <th>order_date_weekday</th>\n",
       "      <th>order_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>13.25</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-01-01</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>11:38:36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>92.00</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-01-01</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>11:57:40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>37.25</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-01-01</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>12:12:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>16.50</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-01-01</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>12:16:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>16.50</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-01-01</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>12:21:30</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21346</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>62.25</td>\n",
       "      <td>365</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>20:51:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21347</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>66.50</td>\n",
       "      <td>365</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>21:14:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21348</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>46.70</td>\n",
       "      <td>365</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>21:23:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21349</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>20.25</td>\n",
       "      <td>365</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>22:09:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21350</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>12.75</td>\n",
       "      <td>365</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>23:02:05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21350 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       item_count  unique_item_count  total_price  order_date_index  \\\n",
       "1               1                  1        13.25                 1   \n",
       "2               5                  5        92.00                 1   \n",
       "3               2                  2        37.25                 1   \n",
       "4               1                  1        16.50                 1   \n",
       "5               1                  1        16.50                 1   \n",
       "...           ...                ...          ...               ...   \n",
       "21346           4                  4        62.25               365   \n",
       "21347           4                  4        66.50               365   \n",
       "21348           3                  3        46.70               365   \n",
       "21349           1                  1        20.25               365   \n",
       "21350           1                  1        12.75               365   \n",
       "\n",
       "       order_date order_date_weekday order_time  \n",
       "1      2015-01-01           Thursday   11:38:36  \n",
       "2      2015-01-01           Thursday   11:57:40  \n",
       "3      2015-01-01           Thursday   12:12:28  \n",
       "4      2015-01-01           Thursday   12:16:31  \n",
       "5      2015-01-01           Thursday   12:21:30  \n",
       "...           ...                ...        ...  \n",
       "21346  2015-12-31           Thursday   20:51:07  \n",
       "21347  2015-12-31           Thursday   21:14:37  \n",
       "21348  2015-12-31           Thursday   21:23:10  \n",
       "21349  2015-12-31           Thursday   22:09:54  \n",
       "21350  2015-12-31           Thursday   23:02:05  \n",
       "\n",
       "[21350 rows x 7 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# build a dataframe that coalesces individual sales into their respective orders\n",
    "orders = pandas.DataFrame(columns = [\"item_count\", \"unique_item_count\", \"total_price\", \"order_date_index\", \"order_date\", \"order_date_weekday\", \"order_time\"])\n",
    "\n",
    "order_row = None\n",
    "order_id = None\n",
    "for row_index in sales.index:\n",
    "    item = sales.loc[row_index]\n",
    "    if order_row is None or order_id != item[\"order_id\"]:\n",
    "        if not order_row is None:\n",
    "            orders.loc[order_id] = order_row\n",
    "\n",
    "        order_id = item[\"order_id\"]\n",
    "        order_row = {\n",
    "            \"item_count\": 0,\n",
    "            \"unique_item_count\": 0,\n",
    "            \"total_price\": 0,\n",
    "            \"order_date_index\": item[\"order_date_index\"],\n",
    "            \"order_date\": item[\"order_date\"],\n",
    "            \"order_date_weekday\": item[\"order_date_weekday\"],\n",
    "            \"order_time\": item[\"order_time\"]\n",
    "        }\n",
    "    order_row[\"item_count\"] += item[\"quantity\"]\n",
    "    order_row[\"unique_item_count\"] += 1\n",
    "    order_row[\"total_price\"] += item[\"total_price\"]\n",
    "orders.loc[order_id] = order_row\n",
    "\n",
    "orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>order_count</th>\n",
       "      <th>item_count</th>\n",
       "      <th>revenue</th>\n",
       "      <th>date</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>69</td>\n",
       "      <td>162</td>\n",
       "      <td>2713.85</td>\n",
       "      <td>2015-01-01</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>67</td>\n",
       "      <td>165</td>\n",
       "      <td>2731.90</td>\n",
       "      <td>2015-01-02</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>66</td>\n",
       "      <td>158</td>\n",
       "      <td>2662.40</td>\n",
       "      <td>2015-01-03</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>52</td>\n",
       "      <td>106</td>\n",
       "      <td>1755.45</td>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>54</td>\n",
       "      <td>125</td>\n",
       "      <td>2065.95</td>\n",
       "      <td>2015-01-05</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>35</td>\n",
       "      <td>89</td>\n",
       "      <td>1419.00</td>\n",
       "      <td>2015-12-27</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>39</td>\n",
       "      <td>102</td>\n",
       "      <td>1637.20</td>\n",
       "      <td>2015-12-28</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>363</th>\n",
       "      <td>27</td>\n",
       "      <td>80</td>\n",
       "      <td>1353.25</td>\n",
       "      <td>2015-12-29</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>364</th>\n",
       "      <td>32</td>\n",
       "      <td>82</td>\n",
       "      <td>1337.80</td>\n",
       "      <td>2015-12-30</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>73</td>\n",
       "      <td>178</td>\n",
       "      <td>2916.00</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>358 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     order_count  item_count  revenue        date    weekday\n",
       "1             69         162  2713.85  2015-01-01   Thursday\n",
       "2             67         165  2731.90  2015-01-02     Friday\n",
       "3             66         158  2662.40  2015-01-03   Saturday\n",
       "4             52         106  1755.45  2015-01-04     Sunday\n",
       "5             54         125  2065.95  2015-01-05     Monday\n",
       "..           ...         ...      ...         ...        ...\n",
       "361           35          89  1419.00  2015-12-27     Sunday\n",
       "362           39         102  1637.20  2015-12-28     Monday\n",
       "363           27          80  1353.25  2015-12-29    Tuesday\n",
       "364           32          82  1337.80  2015-12-30  Wednesday\n",
       "365           73         178  2916.00  2015-12-31   Thursday\n",
       "\n",
       "[358 rows x 5 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#create a separate dataframe to help count daily statistics\n",
    "daily_sales = pandas.DataFrame(columns = [\"order_count\", \"item_count\", \"revenue\", \"date\", \"weekday\"])\n",
    "\n",
    "day_row = None\n",
    "order_date_index = None\n",
    "for row_index in orders.index:\n",
    "    order = orders.loc[row_index]\n",
    "    if day_row is None or order_date_index != order[\"order_date_index\"]:\n",
    "        if not day_row is None:\n",
    "            daily_sales.loc[order_date_index] = day_row\n",
    "\n",
    "        order_date_index = order[\"order_date_index\"]\n",
    "        day_row = {\n",
    "            \"item_count\": 0,\n",
    "            \"order_count\": 0,\n",
    "            \"revenue\": 0,\n",
    "            \"date\": order[\"order_date\"],\n",
    "            \"weekday\": order[\"order_date_weekday\"]\n",
    "        }\n",
    "    day_row[\"item_count\"] += order[\"item_count\"]\n",
    "    day_row[\"order_count\"] += 1\n",
    "    day_row[\"revenue\"] += order[\"total_price\"]\n",
    "daily_sales.loc[order_date_index] = day_row\n",
    "\n",
    "#remove all the days with no sales\n",
    "daily_sales = daily_sales.drop(daily_sales.index[daily_sales[\"item_count\"] == 0])\n",
    "\n",
    "daily_sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>price_S</th>\n",
       "      <th>price_M</th>\n",
       "      <th>price_L</th>\n",
       "      <th>price_XL</th>\n",
       "      <th>price_XXL</th>\n",
       "      <th>ingredients</th>\n",
       "      <th>quantity_sold</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>The Hawaiian Pizza</th>\n",
       "      <td>10.50</td>\n",
       "      <td>13.25</td>\n",
       "      <td>16.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sliced Ham, Pineapple, Mozzarella Cheese</td>\n",
       "      <td>2422</td>\n",
       "      <td>Classic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Classic Deluxe Pizza</th>\n",
       "      <td>12.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>20.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pepperoni, Mushrooms, Red Onions, Red Peppers,...</td>\n",
       "      <td>2453</td>\n",
       "      <td>Classic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Five Cheese Pizza</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mozzarella Cheese, Provolone Cheese, Smoked Go...</td>\n",
       "      <td>1409</td>\n",
       "      <td>Veggie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Italian Supreme Pizza</th>\n",
       "      <td>12.50</td>\n",
       "      <td>16.50</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Calabrese Salami, Capocollo, Tomatoes, Red Oni...</td>\n",
       "      <td>1884</td>\n",
       "      <td>Supreme</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Mexicana Pizza</th>\n",
       "      <td>12.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>20.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tomatoes, Red Peppers, Jalapeno Peppers, Red O...</td>\n",
       "      <td>1484</td>\n",
       "      <td>Veggie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Thai Chicken Pizza</th>\n",
       "      <td>12.75</td>\n",
       "      <td>16.75</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Chicken, Pineapple, Tomatoes, Red Peppers, Tha...</td>\n",
       "      <td>2371</td>\n",
       "      <td>Chicken</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Prosciutto and Arugula Pizza</th>\n",
       "      <td>12.50</td>\n",
       "      <td>16.50</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Prosciutto di San Daniele, Arugula, Mozzarella...</td>\n",
       "      <td>1457</td>\n",
       "      <td>Supreme</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Barbecue Chicken Pizza</th>\n",
       "      <td>12.75</td>\n",
       "      <td>16.75</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Barbecued Chicken, Red Peppers, Green Peppers,...</td>\n",
       "      <td>2432</td>\n",
       "      <td>Chicken</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Greek Pizza</th>\n",
       "      <td>12.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>20.50</td>\n",
       "      <td>25.5</td>\n",
       "      <td>35.95</td>\n",
       "      <td>Kalamata Olives, Feta Cheese, Tomatoes, Garlic...</td>\n",
       "      <td>1420</td>\n",
       "      <td>Classic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Spinach Supreme Pizza</th>\n",
       "      <td>12.50</td>\n",
       "      <td>16.50</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Spinach, Red Onions, Pepperoni, Tomatoes, Arti...</td>\n",
       "      <td>950</td>\n",
       "      <td>Supreme</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Green Garden Pizza</th>\n",
       "      <td>12.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>20.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Spinach, Mushrooms, Tomatoes, Green Olives, Fe...</td>\n",
       "      <td>997</td>\n",
       "      <td>Veggie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Italian Capocollo Pizza</th>\n",
       "      <td>12.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>20.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Capocollo, Red Peppers, Tomatoes, Goat Cheese,...</td>\n",
       "      <td>1438</td>\n",
       "      <td>Classic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Spicy Italian Pizza</th>\n",
       "      <td>12.50</td>\n",
       "      <td>16.50</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Capocollo, Tomatoes, Goat Cheese, Artichokes, ...</td>\n",
       "      <td>1924</td>\n",
       "      <td>Supreme</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Spinach Pesto Pizza</th>\n",
       "      <td>12.50</td>\n",
       "      <td>16.50</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Spinach, Artichokes, Tomatoes, Sun-dried Tomat...</td>\n",
       "      <td>970</td>\n",
       "      <td>Veggie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Vegetables + Vegetables Pizza</th>\n",
       "      <td>12.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>20.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mushrooms, Tomatoes, Red Peppers, Green Pepper...</td>\n",
       "      <td>1526</td>\n",
       "      <td>Veggie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Southwest Chicken Pizza</th>\n",
       "      <td>12.75</td>\n",
       "      <td>16.75</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Chicken, Tomatoes, Red Peppers, Red Onions, Ja...</td>\n",
       "      <td>1917</td>\n",
       "      <td>Chicken</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The California Chicken Pizza</th>\n",
       "      <td>12.75</td>\n",
       "      <td>16.75</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Chicken, Artichoke, Spinach, Garlic, Jalapeno ...</td>\n",
       "      <td>2370</td>\n",
       "      <td>Chicken</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Pepperoni Pizza</th>\n",
       "      <td>9.75</td>\n",
       "      <td>12.50</td>\n",
       "      <td>15.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mozzarella Cheese, Pepperoni</td>\n",
       "      <td>2418</td>\n",
       "      <td>Classic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Chicken Pesto Pizza</th>\n",
       "      <td>12.75</td>\n",
       "      <td>16.75</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Chicken, Tomatoes, Red Peppers, Spinach, Garli...</td>\n",
       "      <td>973</td>\n",
       "      <td>Chicken</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Big Meat Pizza</th>\n",
       "      <td>12.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bacon, Pepperoni, Italian Sausage, Chorizo Sau...</td>\n",
       "      <td>1914</td>\n",
       "      <td>Classic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Soppressata Pizza</th>\n",
       "      <td>12.50</td>\n",
       "      <td>16.50</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Soppressata Salami, Fontina Cheese, Mozzarella...</td>\n",
       "      <td>961</td>\n",
       "      <td>Supreme</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Four Cheese Pizza</th>\n",
       "      <td>NaN</td>\n",
       "      <td>14.75</td>\n",
       "      <td>17.95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ricotta Cheese, Gorgonzola Piccante Cheese, Mo...</td>\n",
       "      <td>1902</td>\n",
       "      <td>Veggie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Napolitana Pizza</th>\n",
       "      <td>12.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>20.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tomatoes, Anchovies, Green Olives, Red Onions,...</td>\n",
       "      <td>1464</td>\n",
       "      <td>Classic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Calabrese Pizza</th>\n",
       "      <td>12.25</td>\n",
       "      <td>16.25</td>\n",
       "      <td>20.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>?duja Salami, Pancetta, Tomatoes, Red Onions, ...</td>\n",
       "      <td>937</td>\n",
       "      <td>Supreme</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Italian Vegetables Pizza</th>\n",
       "      <td>12.75</td>\n",
       "      <td>16.75</td>\n",
       "      <td>21.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Eggplant, Artichokes, Tomatoes, Zucchini, Red ...</td>\n",
       "      <td>981</td>\n",
       "      <td>Veggie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Mediterranean Pizza</th>\n",
       "      <td>12.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>20.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Spinach, Artichokes, Kalamata Olives, Sun-drie...</td>\n",
       "      <td>934</td>\n",
       "      <td>Veggie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Pepper Salami Pizza</th>\n",
       "      <td>12.50</td>\n",
       "      <td>16.50</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Genoa Salami, Capocollo, Pepperoni, Tomatoes, ...</td>\n",
       "      <td>1446</td>\n",
       "      <td>Supreme</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Spinach and Feta Pizza</th>\n",
       "      <td>12.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>20.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Spinach, Mushrooms, Red Onions, Feta Cheese, G...</td>\n",
       "      <td>1446</td>\n",
       "      <td>Veggie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Sicilian Pizza</th>\n",
       "      <td>12.25</td>\n",
       "      <td>16.25</td>\n",
       "      <td>20.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Coarse Sicilian Salami, Tomatoes, Green Olives...</td>\n",
       "      <td>1938</td>\n",
       "      <td>Supreme</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Chicken Alfredo Pizza</th>\n",
       "      <td>12.75</td>\n",
       "      <td>16.75</td>\n",
       "      <td>20.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Chicken, Red Onions, Red Peppers, Mushrooms, A...</td>\n",
       "      <td>987</td>\n",
       "      <td>Chicken</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Pepperoni, Mushroom, and Peppers Pizza</th>\n",
       "      <td>11.00</td>\n",
       "      <td>14.50</td>\n",
       "      <td>17.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pepperoni, Mushrooms, Green Peppers</td>\n",
       "      <td>1359</td>\n",
       "      <td>Classic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Brie Carre Pizza</th>\n",
       "      <td>23.65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Brie Carre Cheese, Prosciutto, Caramelized Oni...</td>\n",
       "      <td>490</td>\n",
       "      <td>Supreme</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            price_S  price_M  price_L  \\\n",
       "The Hawaiian Pizza                            10.50    13.25    16.50   \n",
       "The Classic Deluxe Pizza                      12.00    16.00    20.50   \n",
       "The Five Cheese Pizza                           NaN      NaN    18.50   \n",
       "The Italian Supreme Pizza                     12.50    16.50    20.75   \n",
       "The Mexicana Pizza                            12.00    16.00    20.25   \n",
       "The Thai Chicken Pizza                        12.75    16.75    20.75   \n",
       "The Prosciutto and Arugula Pizza              12.50    16.50    20.75   \n",
       "The Barbecue Chicken Pizza                    12.75    16.75    20.75   \n",
       "The Greek Pizza                               12.00    16.00    20.50   \n",
       "The Spinach Supreme Pizza                     12.50    16.50    20.75   \n",
       "The Green Garden Pizza                        12.00    16.00    20.25   \n",
       "The Italian Capocollo Pizza                   12.00    16.00    20.50   \n",
       "The Spicy Italian Pizza                       12.50    16.50    20.75   \n",
       "The Spinach Pesto Pizza                       12.50    16.50    20.75   \n",
       "The Vegetables + Vegetables Pizza             12.00    16.00    20.25   \n",
       "The Southwest Chicken Pizza                   12.75    16.75    20.75   \n",
       "The California Chicken Pizza                  12.75    16.75    20.75   \n",
       "The Pepperoni Pizza                            9.75    12.50    15.25   \n",
       "The Chicken Pesto Pizza                       12.75    16.75    20.75   \n",
       "The Big Meat Pizza                            12.00      NaN      NaN   \n",
       "The Soppressata Pizza                         12.50    16.50    20.75   \n",
       "The Four Cheese Pizza                           NaN    14.75    17.95   \n",
       "The Napolitana Pizza                          12.00    16.00    20.50   \n",
       "The Calabrese Pizza                           12.25    16.25    20.25   \n",
       "The Italian Vegetables Pizza                  12.75    16.75    21.00   \n",
       "The Mediterranean Pizza                       12.00    16.00    20.25   \n",
       "The Pepper Salami Pizza                       12.50    16.50    20.75   \n",
       "The Spinach and Feta Pizza                    12.00    16.00    20.25   \n",
       "The Sicilian Pizza                            12.25    16.25    20.25   \n",
       "The Chicken Alfredo Pizza                     12.75    16.75    20.75   \n",
       "The Pepperoni, Mushroom, and Peppers Pizza    11.00    14.50    17.50   \n",
       "The Brie Carre Pizza                          23.65      NaN      NaN   \n",
       "\n",
       "                                            price_XL  price_XXL  \\\n",
       "The Hawaiian Pizza                               NaN        NaN   \n",
       "The Classic Deluxe Pizza                         NaN        NaN   \n",
       "The Five Cheese Pizza                            NaN        NaN   \n",
       "The Italian Supreme Pizza                        NaN        NaN   \n",
       "The Mexicana Pizza                               NaN        NaN   \n",
       "The Thai Chicken Pizza                           NaN        NaN   \n",
       "The Prosciutto and Arugula Pizza                 NaN        NaN   \n",
       "The Barbecue Chicken Pizza                       NaN        NaN   \n",
       "The Greek Pizza                                 25.5      35.95   \n",
       "The Spinach Supreme Pizza                        NaN        NaN   \n",
       "The Green Garden Pizza                           NaN        NaN   \n",
       "The Italian Capocollo Pizza                      NaN        NaN   \n",
       "The Spicy Italian Pizza                          NaN        NaN   \n",
       "The Spinach Pesto Pizza                          NaN        NaN   \n",
       "The Vegetables + Vegetables Pizza                NaN        NaN   \n",
       "The Southwest Chicken Pizza                      NaN        NaN   \n",
       "The California Chicken Pizza                     NaN        NaN   \n",
       "The Pepperoni Pizza                              NaN        NaN   \n",
       "The Chicken Pesto Pizza                          NaN        NaN   \n",
       "The Big Meat Pizza                               NaN        NaN   \n",
       "The Soppressata Pizza                            NaN        NaN   \n",
       "The Four Cheese Pizza                            NaN        NaN   \n",
       "The Napolitana Pizza                             NaN        NaN   \n",
       "The Calabrese Pizza                              NaN        NaN   \n",
       "The Italian Vegetables Pizza                     NaN        NaN   \n",
       "The Mediterranean Pizza                          NaN        NaN   \n",
       "The Pepper Salami Pizza                          NaN        NaN   \n",
       "The Spinach and Feta Pizza                       NaN        NaN   \n",
       "The Sicilian Pizza                               NaN        NaN   \n",
       "The Chicken Alfredo Pizza                        NaN        NaN   \n",
       "The Pepperoni, Mushroom, and Peppers Pizza       NaN        NaN   \n",
       "The Brie Carre Pizza                             NaN        NaN   \n",
       "\n",
       "                                                                                  ingredients  \\\n",
       "The Hawaiian Pizza                                   Sliced Ham, Pineapple, Mozzarella Cheese   \n",
       "The Classic Deluxe Pizza                    Pepperoni, Mushrooms, Red Onions, Red Peppers,...   \n",
       "The Five Cheese Pizza                       Mozzarella Cheese, Provolone Cheese, Smoked Go...   \n",
       "The Italian Supreme Pizza                   Calabrese Salami, Capocollo, Tomatoes, Red Oni...   \n",
       "The Mexicana Pizza                          Tomatoes, Red Peppers, Jalapeno Peppers, Red O...   \n",
       "The Thai Chicken Pizza                      Chicken, Pineapple, Tomatoes, Red Peppers, Tha...   \n",
       "The Prosciutto and Arugula Pizza            Prosciutto di San Daniele, Arugula, Mozzarella...   \n",
       "The Barbecue Chicken Pizza                  Barbecued Chicken, Red Peppers, Green Peppers,...   \n",
       "The Greek Pizza                             Kalamata Olives, Feta Cheese, Tomatoes, Garlic...   \n",
       "The Spinach Supreme Pizza                   Spinach, Red Onions, Pepperoni, Tomatoes, Arti...   \n",
       "The Green Garden Pizza                      Spinach, Mushrooms, Tomatoes, Green Olives, Fe...   \n",
       "The Italian Capocollo Pizza                 Capocollo, Red Peppers, Tomatoes, Goat Cheese,...   \n",
       "The Spicy Italian Pizza                     Capocollo, Tomatoes, Goat Cheese, Artichokes, ...   \n",
       "The Spinach Pesto Pizza                     Spinach, Artichokes, Tomatoes, Sun-dried Tomat...   \n",
       "The Vegetables + Vegetables Pizza           Mushrooms, Tomatoes, Red Peppers, Green Pepper...   \n",
       "The Southwest Chicken Pizza                 Chicken, Tomatoes, Red Peppers, Red Onions, Ja...   \n",
       "The California Chicken Pizza                Chicken, Artichoke, Spinach, Garlic, Jalapeno ...   \n",
       "The Pepperoni Pizza                                              Mozzarella Cheese, Pepperoni   \n",
       "The Chicken Pesto Pizza                     Chicken, Tomatoes, Red Peppers, Spinach, Garli...   \n",
       "The Big Meat Pizza                          Bacon, Pepperoni, Italian Sausage, Chorizo Sau...   \n",
       "The Soppressata Pizza                       Soppressata Salami, Fontina Cheese, Mozzarella...   \n",
       "The Four Cheese Pizza                       Ricotta Cheese, Gorgonzola Piccante Cheese, Mo...   \n",
       "The Napolitana Pizza                        Tomatoes, Anchovies, Green Olives, Red Onions,...   \n",
       "The Calabrese Pizza                         ?duja Salami, Pancetta, Tomatoes, Red Onions, ...   \n",
       "The Italian Vegetables Pizza                Eggplant, Artichokes, Tomatoes, Zucchini, Red ...   \n",
       "The Mediterranean Pizza                     Spinach, Artichokes, Kalamata Olives, Sun-drie...   \n",
       "The Pepper Salami Pizza                     Genoa Salami, Capocollo, Pepperoni, Tomatoes, ...   \n",
       "The Spinach and Feta Pizza                  Spinach, Mushrooms, Red Onions, Feta Cheese, G...   \n",
       "The Sicilian Pizza                          Coarse Sicilian Salami, Tomatoes, Green Olives...   \n",
       "The Chicken Alfredo Pizza                   Chicken, Red Onions, Red Peppers, Mushrooms, A...   \n",
       "The Pepperoni, Mushroom, and Peppers Pizza                Pepperoni, Mushrooms, Green Peppers   \n",
       "The Brie Carre Pizza                        Brie Carre Cheese, Prosciutto, Caramelized Oni...   \n",
       "\n",
       "                                            quantity_sold category  \n",
       "The Hawaiian Pizza                                   2422  Classic  \n",
       "The Classic Deluxe Pizza                             2453  Classic  \n",
       "The Five Cheese Pizza                                1409   Veggie  \n",
       "The Italian Supreme Pizza                            1884  Supreme  \n",
       "The Mexicana Pizza                                   1484   Veggie  \n",
       "The Thai Chicken Pizza                               2371  Chicken  \n",
       "The Prosciutto and Arugula Pizza                     1457  Supreme  \n",
       "The Barbecue Chicken Pizza                           2432  Chicken  \n",
       "The Greek Pizza                                      1420  Classic  \n",
       "The Spinach Supreme Pizza                             950  Supreme  \n",
       "The Green Garden Pizza                                997   Veggie  \n",
       "The Italian Capocollo Pizza                          1438  Classic  \n",
       "The Spicy Italian Pizza                              1924  Supreme  \n",
       "The Spinach Pesto Pizza                               970   Veggie  \n",
       "The Vegetables + Vegetables Pizza                    1526   Veggie  \n",
       "The Southwest Chicken Pizza                          1917  Chicken  \n",
       "The California Chicken Pizza                         2370  Chicken  \n",
       "The Pepperoni Pizza                                  2418  Classic  \n",
       "The Chicken Pesto Pizza                               973  Chicken  \n",
       "The Big Meat Pizza                                   1914  Classic  \n",
       "The Soppressata Pizza                                 961  Supreme  \n",
       "The Four Cheese Pizza                                1902   Veggie  \n",
       "The Napolitana Pizza                                 1464  Classic  \n",
       "The Calabrese Pizza                                   937  Supreme  \n",
       "The Italian Vegetables Pizza                          981   Veggie  \n",
       "The Mediterranean Pizza                               934   Veggie  \n",
       "The Pepper Salami Pizza                              1446  Supreme  \n",
       "The Spinach and Feta Pizza                           1446   Veggie  \n",
       "The Sicilian Pizza                                   1938  Supreme  \n",
       "The Chicken Alfredo Pizza                             987  Chicken  \n",
       "The Pepperoni, Mushroom, and Peppers Pizza           1359  Classic  \n",
       "The Brie Carre Pizza                                  490  Supreme  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# make a dataframe to keep track of statistics on the types of pizza\n",
    "pizza_properties = pandas.DataFrame(columns = [\"price_S\", \"price_M\", \"price_L\", \"price_XL\", \"price_XXL\", \"ingredients\", \"quantity_sold\", \"category\"])\n",
    "\n",
    "for pizza_name in sales[\"pizza_name\"].unique():\n",
    "    pizza_sales = sales[sales[\"pizza_name\"] == pizza_name]\n",
    "    pizza_row = {\n",
    "        \"price_S\": numpy.nan,\n",
    "        \"price_M\": numpy.nan,\n",
    "        \"price_L\": numpy.nan,\n",
    "        \"price_XL\": numpy.nan,\n",
    "        \"price_XXL\": numpy.nan,\n",
    "        \"ingredients\": pizza_sales.iloc[0][\"pizza_ingredients\"],\n",
    "        \"quantity_sold\": 0,\n",
    "        \"category\": pizza_sales.iloc[0][\"pizza_category\"]\n",
    "    }\n",
    "    for sale_index in pizza_sales.index:\n",
    "        sale = pizza_sales.loc[sale_index]\n",
    "        pizza_row[\"price_%s\" %(sale[\"pizza_size\"])] = sale[\"unit_price\"]\n",
    "        pizza_row[\"quantity_sold\"] += sale[\"quantity\"]\n",
    "    pizza_properties.loc[pizza_name] = pizza_row\n",
    "\n",
    "pizza_properties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>quantity_sold</th>\n",
       "      <th>pizza_types_containing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Pepperoni</th>\n",
       "      <td>10540</td>\n",
       "      <td>'The Pepperoni, Mushroom, and Peppers Pizza', ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bacon</th>\n",
       "      <td>4367</td>\n",
       "      <td>'The Classic Deluxe Pizza', 'The Big Meat Pizza'</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Calabrese Salami</th>\n",
       "      <td>1884</td>\n",
       "      <td>'The Italian Supreme Pizza'</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Soppressata Salami</th>\n",
       "      <td>961</td>\n",
       "      <td>'The Soppressata Pizza'</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asiago Cheese</th>\n",
       "      <td>3383</td>\n",
       "      <td>'The Chicken Alfredo Pizza', 'The Pepper Salam...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kalamata Olives</th>\n",
       "      <td>3304</td>\n",
       "      <td>'The Mediterranean Pizza', 'The Greek Pizza', ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Anchovies</th>\n",
       "      <td>1464</td>\n",
       "      <td>'The Napolitana Pizza'</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coarse Sicilian Salami</th>\n",
       "      <td>1938</td>\n",
       "      <td>'The Sicilian Pizza'</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Barbecued Chicken</th>\n",
       "      <td>2432</td>\n",
       "      <td>'The Barbecue Chicken Pizza'</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chipotle Sauce</th>\n",
       "      <td>3401</td>\n",
       "      <td>'The Southwest Chicken Pizza', 'The Mexicana P...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        quantity_sold  \\\n",
       "Pepperoni                       10540   \n",
       "Bacon                            4367   \n",
       "Calabrese Salami                 1884   \n",
       "Soppressata Salami                961   \n",
       "Asiago Cheese                    3383   \n",
       "...                               ...   \n",
       "Kalamata Olives                  3304   \n",
       "Anchovies                        1464   \n",
       "Coarse Sicilian Salami           1938   \n",
       "Barbecued Chicken                2432   \n",
       "Chipotle Sauce                   3401   \n",
       "\n",
       "                                                   pizza_types_containing  \n",
       "Pepperoni               'The Pepperoni, Mushroom, and Peppers Pizza', ...  \n",
       "Bacon                    'The Classic Deluxe Pizza', 'The Big Meat Pizza'  \n",
       "Calabrese Salami                              'The Italian Supreme Pizza'  \n",
       "Soppressata Salami                                'The Soppressata Pizza'  \n",
       "Asiago Cheese           'The Chicken Alfredo Pizza', 'The Pepper Salam...  \n",
       "...                                                                   ...  \n",
       "Kalamata Olives         'The Mediterranean Pizza', 'The Greek Pizza', ...  \n",
       "Anchovies                                          'The Napolitana Pizza'  \n",
       "Coarse Sicilian Salami                               'The Sicilian Pizza'  \n",
       "Barbecued Chicken                            'The Barbecue Chicken Pizza'  \n",
       "Chipotle Sauce          'The Southwest Chicken Pizza', 'The Mexicana P...  \n",
       "\n",
       "[65 rows x 2 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# make another dataframe to keep track of ingredients, which pizzas they are in, and how many pizzas are sold with those ingredients\n",
    "ingredient_set = set()\n",
    "for ingredients_list in pizza_properties[\"ingredients\"]:\n",
    "    ingredient_set.update(ingredients_list.split(\", \"))\n",
    "ingredient_set\n",
    "\n",
    "ingredient_properties = pandas.DataFrame(columns = [\"quantity_sold\", \"pizza_types_containing\"])\n",
    "\n",
    "for ingredient in ingredient_set:\n",
    "    unique_pizzas = set()\n",
    "    for pizza_name in pizza_properties.index:\n",
    "        if ingredient in pizza_properties.loc[pizza_name][\"ingredients\"]:\n",
    "            unique_pizzas.add(pizza_name)\n",
    "    sales_with_this_ingredient = sales[sales[\"pizza_name\"].apply(lambda pizza_name: pizza_name in unique_pizzas)]\n",
    "    ingredient_properties.loc[ingredient] = [sales_with_this_ingredient[\"quantity\"].sum(), str(unique_pizzas).rstrip(\"}\").lstrip(\"{\")]\n",
    "\n",
    "ingredient_properties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.to_csv(\"sales.csv\")\n",
    "daily_sales.to_csv(\"daily_sales.csv\")\n",
    "orders.to_csv(\"orders.csv\")\n",
    "pizza_properties.to_csv(\"pizza_properties.csv\")\n",
    "ingredient_properties.to_csv(\"ingredient_properties.csv\")"
   ]
  }
 ],
 "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.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}