Week 12 Challenge.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load data\n",
    "df = pd.ExcelFile(\"PD week 12 input.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# there are 3 sheets to look\n",
    "total_sales = pd.read_excel(df, \"Total Sales\")\n",
    "percentage_sales = pd.read_excel(df, \"Percentage of Sales\")\n",
    "lookup = pd.read_excel(df, \"Lookup Table\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# final output requires the Date to be in the year week number format\n",
    "percentage_sales[\"week_of_year\"] = percentage_sales[\"Week Commencing\"].apply(lambda xx: datetime.datetime.strptime(str(xx)[:10], \"%Y-%m-%d\").isocalendar()[1])\n",
    "percentage_sales[\"year\"] = percentage_sales[\"Week Commencing\"].apply(lambda xx: str(xx)[:4])\n",
    "percentage_sales[\"Year Week Number\"] = percentage_sales[[\"year\", \"week_of_year\"]].apply(lambda xx: int(str(xx[0]) + '{0}'.format(str(xx[1]).zfill(2))), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove product sizes that make up 0% of sales\n",
    "percentage_sales = percentage_sales[percentage_sales[\"Percentage of Sales\"] > 0.0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# In the Lookup Table, it seems the Product ID and Size have been erroneously concatenated. These need to be separated.\n",
    "\n",
    "# but why come up with potentially incorrect logic to separate them? let's just concatenate product ID and size from another tab :)\n",
    "percentage_sales[\"id\"] = percentage_sales[\"Product ID\"] + percentage_sales[\"Size\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge!\n",
    "merge_1 = pd.merge(percentage_sales, lookup, how=\"left\", left_on=\"id\", right_on=\"Product\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# You'll need to do some cleaning of the Scent fields to join together the Total Sales and the Lookup Table.\n",
    "total_sales[\"Scent\"] = total_sales[\"Scent\"].apply(lambda x: \"\".join(x.split()).capitalize())\n",
    "merge_1[\"Scent\"] = merge_1[\"Scent\"].apply(lambda x: \"\".join(x.split()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge!\n",
    "merge_2 = pd.merge(merge_1, total_sales, how=\"left\", left_on=[\"Scent\", \"Year Week Number\"], right_on=[\"Scent\", \"Year Week Number\"])\n",
    "\n",
    "# compute the weekly sale\n",
    "merge_2[\"Sales\"] = merge_2[[\"Total Scent Sales\", \"Percentage of Sales\"]].apply(lambda x: round(x[0] * x[1], 2), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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 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>Week Commencing</th>\n",
       "      <th>Size</th>\n",
       "      <th>Product Type</th>\n",
       "      <th>Percentage of Sales</th>\n",
       "      <th>week_of_year</th>\n",
       "      <th>year</th>\n",
       "      <th>Year Week Number</th>\n",
       "      <th>id</th>\n",
       "      <th>Scent</th>\n",
       "      <th>Product</th>\n",
       "      <th>Total Scent Sales</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>0.5l</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.33</td>\n",
       "      <td>2</td>\n",
       "      <td>2020</td>\n",
       "      <td>202002</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>3537.73</td>\n",
       "      <td>1167.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>100g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.13</td>\n",
       "      <td>2</td>\n",
       "      <td>2020</td>\n",
       "      <td>202002</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>3537.73</td>\n",
       "      <td>459.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>250ml</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.20</td>\n",
       "      <td>2</td>\n",
       "      <td>2020</td>\n",
       "      <td>202002</td>\n",
       "      <td>0c60c126250ml</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c126250ml</td>\n",
       "      <td>3537.73</td>\n",
       "      <td>707.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>50g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.34</td>\n",
       "      <td>2</td>\n",
       "      <td>2020</td>\n",
       "      <td>202002</td>\n",
       "      <td>0c60c12650g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c12650g</td>\n",
       "      <td>3537.73</td>\n",
       "      <td>1202.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>0.5l</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.80</td>\n",
       "      <td>3</td>\n",
       "      <td>2020</td>\n",
       "      <td>202003</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>8264.29</td>\n",
       "      <td>6611.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>100g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.04</td>\n",
       "      <td>3</td>\n",
       "      <td>2020</td>\n",
       "      <td>202003</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>8264.29</td>\n",
       "      <td>330.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>50g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.16</td>\n",
       "      <td>3</td>\n",
       "      <td>2020</td>\n",
       "      <td>202003</td>\n",
       "      <td>0c60c12650g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c12650g</td>\n",
       "      <td>8264.29</td>\n",
       "      <td>1322.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>0.5l</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.60</td>\n",
       "      <td>4</td>\n",
       "      <td>2020</td>\n",
       "      <td>202004</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>9981.68</td>\n",
       "      <td>5989.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>100g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.30</td>\n",
       "      <td>4</td>\n",
       "      <td>2020</td>\n",
       "      <td>202004</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>9981.68</td>\n",
       "      <td>2994.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>250ml</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.06</td>\n",
       "      <td>4</td>\n",
       "      <td>2020</td>\n",
       "      <td>202004</td>\n",
       "      <td>0c60c126250ml</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c126250ml</td>\n",
       "      <td>9981.68</td>\n",
       "      <td>598.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>50g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.04</td>\n",
       "      <td>4</td>\n",
       "      <td>2020</td>\n",
       "      <td>202004</td>\n",
       "      <td>0c60c12650g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c12650g</td>\n",
       "      <td>9981.68</td>\n",
       "      <td>399.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>0.5l</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.45</td>\n",
       "      <td>5</td>\n",
       "      <td>2020</td>\n",
       "      <td>202005</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>5029.89</td>\n",
       "      <td>2263.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>100g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.50</td>\n",
       "      <td>5</td>\n",
       "      <td>2020</td>\n",
       "      <td>202005</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>5029.89</td>\n",
       "      <td>2514.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>250ml</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.02</td>\n",
       "      <td>5</td>\n",
       "      <td>2020</td>\n",
       "      <td>202005</td>\n",
       "      <td>0c60c126250ml</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c126250ml</td>\n",
       "      <td>5029.89</td>\n",
       "      <td>100.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-01-27</td>\n",
       "      <td>50g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.03</td>\n",
       "      <td>5</td>\n",
       "      <td>2020</td>\n",
       "      <td>202005</td>\n",
       "      <td>0c60c12650g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c12650g</td>\n",
       "      <td>5029.89</td>\n",
       "      <td>150.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-02-03</td>\n",
       "      <td>0.5l</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.01</td>\n",
       "      <td>6</td>\n",
       "      <td>2020</td>\n",
       "      <td>202006</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>9835.40</td>\n",
       "      <td>98.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-02-03</td>\n",
       "      <td>100g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.04</td>\n",
       "      <td>6</td>\n",
       "      <td>2020</td>\n",
       "      <td>202006</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>9835.40</td>\n",
       "      <td>393.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-02-03</td>\n",
       "      <td>250ml</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.75</td>\n",
       "      <td>6</td>\n",
       "      <td>2020</td>\n",
       "      <td>202006</td>\n",
       "      <td>0c60c126250ml</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c126250ml</td>\n",
       "      <td>9835.40</td>\n",
       "      <td>7376.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-02-03</td>\n",
       "      <td>50g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.20</td>\n",
       "      <td>6</td>\n",
       "      <td>2020</td>\n",
       "      <td>202006</td>\n",
       "      <td>0c60c12650g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c12650g</td>\n",
       "      <td>9835.40</td>\n",
       "      <td>1967.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>0.5l</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.07</td>\n",
       "      <td>7</td>\n",
       "      <td>2020</td>\n",
       "      <td>202007</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>1572.08</td>\n",
       "      <td>110.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>100g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.13</td>\n",
       "      <td>7</td>\n",
       "      <td>2020</td>\n",
       "      <td>202007</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>1572.08</td>\n",
       "      <td>204.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>250ml</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.30</td>\n",
       "      <td>7</td>\n",
       "      <td>2020</td>\n",
       "      <td>202007</td>\n",
       "      <td>0c60c126250ml</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c126250ml</td>\n",
       "      <td>1572.08</td>\n",
       "      <td>471.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-02-10</td>\n",
       "      <td>50g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.50</td>\n",
       "      <td>7</td>\n",
       "      <td>2020</td>\n",
       "      <td>202007</td>\n",
       "      <td>0c60c12650g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c12650g</td>\n",
       "      <td>1572.08</td>\n",
       "      <td>786.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>0.5l</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.33</td>\n",
       "      <td>8</td>\n",
       "      <td>2020</td>\n",
       "      <td>202008</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c1260.5l</td>\n",
       "      <td>5499.12</td>\n",
       "      <td>1814.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0c60c126</td>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>100g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.13</td>\n",
       "      <td>8</td>\n",
       "      <td>2020</td>\n",
       "      <td>202008</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>Orange</td>\n",
       "      <td>0c60c126100g</td>\n",
       "      <td>5499.12</td>\n",
       "      <td>714.89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Product ID Week Commencing   Size Product Type  Percentage of Sales  \\\n",
       "0    0c60c126      2020-01-06   0.5l       Liquid                 0.33   \n",
       "1    0c60c126      2020-01-06   100g          Bar                 0.13   \n",
       "2    0c60c126      2020-01-06  250ml       Liquid                 0.20   \n",
       "3    0c60c126      2020-01-06    50g          Bar                 0.34   \n",
       "4    0c60c126      2020-01-13   0.5l       Liquid                 0.80   \n",
       "5    0c60c126      2020-01-13   100g          Bar                 0.04   \n",
       "6    0c60c126      2020-01-13    50g          Bar                 0.16   \n",
       "7    0c60c126      2020-01-20   0.5l       Liquid                 0.60   \n",
       "8    0c60c126      2020-01-20   100g          Bar                 0.30   \n",
       "9    0c60c126      2020-01-20  250ml       Liquid                 0.06   \n",
       "10   0c60c126      2020-01-20    50g          Bar                 0.04   \n",
       "11   0c60c126      2020-01-27   0.5l       Liquid                 0.45   \n",
       "12   0c60c126      2020-01-27   100g          Bar                 0.50   \n",
       "13   0c60c126      2020-01-27  250ml       Liquid                 0.02   \n",
       "14   0c60c126      2020-01-27    50g          Bar                 0.03   \n",
       "15   0c60c126      2020-02-03   0.5l       Liquid                 0.01   \n",
       "16   0c60c126      2020-02-03   100g          Bar                 0.04   \n",
       "17   0c60c126      2020-02-03  250ml       Liquid                 0.75   \n",
       "18   0c60c126      2020-02-03    50g          Bar                 0.20   \n",
       "19   0c60c126      2020-02-10   0.5l       Liquid                 0.07   \n",
       "20   0c60c126      2020-02-10   100g          Bar                 0.13   \n",
       "21   0c60c126      2020-02-10  250ml       Liquid                 0.30   \n",
       "22   0c60c126      2020-02-10    50g          Bar                 0.50   \n",
       "23   0c60c126      2020-02-17   0.5l       Liquid                 0.33   \n",
       "24   0c60c126      2020-02-17   100g          Bar                 0.13   \n",
       "\n",
       "    week_of_year  year  Year Week Number             id   Scent  \\\n",
       "0              2  2020            202002   0c60c1260.5l  Orange   \n",
       "1              2  2020            202002   0c60c126100g  Orange   \n",
       "2              2  2020            202002  0c60c126250ml  Orange   \n",
       "3              2  2020            202002    0c60c12650g  Orange   \n",
       "4              3  2020            202003   0c60c1260.5l  Orange   \n",
       "5              3  2020            202003   0c60c126100g  Orange   \n",
       "6              3  2020            202003    0c60c12650g  Orange   \n",
       "7              4  2020            202004   0c60c1260.5l  Orange   \n",
       "8              4  2020            202004   0c60c126100g  Orange   \n",
       "9              4  2020            202004  0c60c126250ml  Orange   \n",
       "10             4  2020            202004    0c60c12650g  Orange   \n",
       "11             5  2020            202005   0c60c1260.5l  Orange   \n",
       "12             5  2020            202005   0c60c126100g  Orange   \n",
       "13             5  2020            202005  0c60c126250ml  Orange   \n",
       "14             5  2020            202005    0c60c12650g  Orange   \n",
       "15             6  2020            202006   0c60c1260.5l  Orange   \n",
       "16             6  2020            202006   0c60c126100g  Orange   \n",
       "17             6  2020            202006  0c60c126250ml  Orange   \n",
       "18             6  2020            202006    0c60c12650g  Orange   \n",
       "19             7  2020            202007   0c60c1260.5l  Orange   \n",
       "20             7  2020            202007   0c60c126100g  Orange   \n",
       "21             7  2020            202007  0c60c126250ml  Orange   \n",
       "22             7  2020            202007    0c60c12650g  Orange   \n",
       "23             8  2020            202008   0c60c1260.5l  Orange   \n",
       "24             8  2020            202008   0c60c126100g  Orange   \n",
       "\n",
       "          Product  Total Scent Sales    Sales  \n",
       "0    0c60c1260.5l            3537.73  1167.45  \n",
       "1    0c60c126100g            3537.73   459.90  \n",
       "2   0c60c126250ml            3537.73   707.55  \n",
       "3     0c60c12650g            3537.73  1202.83  \n",
       "4    0c60c1260.5l            8264.29  6611.43  \n",
       "5    0c60c126100g            8264.29   330.57  \n",
       "6     0c60c12650g            8264.29  1322.29  \n",
       "7    0c60c1260.5l            9981.68  5989.01  \n",
       "8    0c60c126100g            9981.68  2994.50  \n",
       "9   0c60c126250ml            9981.68   598.90  \n",
       "10    0c60c12650g            9981.68   399.27  \n",
       "11   0c60c1260.5l            5029.89  2263.45  \n",
       "12   0c60c126100g            5029.89  2514.95  \n",
       "13  0c60c126250ml            5029.89   100.60  \n",
       "14    0c60c12650g            5029.89   150.90  \n",
       "15   0c60c1260.5l            9835.40    98.35  \n",
       "16   0c60c126100g            9835.40   393.42  \n",
       "17  0c60c126250ml            9835.40  7376.55  \n",
       "18    0c60c12650g            9835.40  1967.08  \n",
       "19   0c60c1260.5l            1572.08   110.05  \n",
       "20   0c60c126100g            1572.08   204.37  \n",
       "21  0c60c126250ml            1572.08   471.62  \n",
       "22    0c60c12650g            1572.08   786.04  \n",
       "23   0c60c1260.5l            5499.12  1814.71  \n",
       "24   0c60c126100g            5499.12   714.89  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_2.head(25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_output = merge_2[[\"Year Week Number\", \"Scent\", \"Size\", \"Product Type\", \"Sales\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>Year Week Number</th>\n",
       "      <th>Scent</th>\n",
       "      <th>Size</th>\n",
       "      <th>Product Type</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>202002</td>\n",
       "      <td>Coconut</td>\n",
       "      <td>0.5l</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>0.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>202002</td>\n",
       "      <td>Coconut</td>\n",
       "      <td>100g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>0.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>202002</td>\n",
       "      <td>Coconut</td>\n",
       "      <td>250ml</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>15.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>202002</td>\n",
       "      <td>Coconut</td>\n",
       "      <td>50g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>4.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>202002</td>\n",
       "      <td>Honey</td>\n",
       "      <td>0.5l</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>1141.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>202002</td>\n",
       "      <td>Honey</td>\n",
       "      <td>100g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>449.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>202002</td>\n",
       "      <td>Honey</td>\n",
       "      <td>250ml</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>691.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>202002</td>\n",
       "      <td>Honey</td>\n",
       "      <td>50g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>1176.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>202002</td>\n",
       "      <td>Lavendar</td>\n",
       "      <td>0.5l</td>\n",
       "      <td>Liquid</td>\n",
       "      <td>18.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>202002</td>\n",
       "      <td>Lavendar</td>\n",
       "      <td>100g</td>\n",
       "      <td>Bar</td>\n",
       "      <td>74.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Year Week Number     Scent   Size Product Type    Sales\n",
       "38             202002   Coconut   0.5l       Liquid     0.20\n",
       "39             202002   Coconut   100g          Bar     0.81\n",
       "40             202002   Coconut  250ml       Liquid    15.28\n",
       "41             202002   Coconut    50g          Bar     4.07\n",
       "115            202002     Honey   0.5l       Liquid  1141.49\n",
       "116            202002     Honey   100g          Bar   449.68\n",
       "117            202002     Honey  250ml       Liquid   691.81\n",
       "118            202002     Honey    50g          Bar  1176.08\n",
       "153            202002  Lavendar   0.5l       Liquid    18.70\n",
       "154            202002  Lavendar   100g          Bar    74.79"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_output.sort_values([\"Year Week Number\", \"Scent\"]).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(307, 5)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# validate the row count\n",
    "final_output.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}