01_AB_Test_Matched_Pairs_Part1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Project: A/B Test a New Menu Launch (Part1)\n",
    "\n",
    "### by Sooyeon Won\n",
    "\n",
    "### Keywords\n",
    "- Analytical Framework\n",
    "- Data Cleaning \n",
    "- Data Exploration \n",
    "- A/B tests Design and Analysis "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Table of Contents\n",
    "<ul>\n",
    "<li><a href=\"#Intro\">Introduction (Part1)</a></li>\n",
    "<li><a href=\"#Wrangling\">Data Wrangling (Part1)</a></li>\n",
    "<li><a href=\"#Exploration\">Data Exploration (Part2)</a></li>\n",
    "<li><a href=\"#Analysis\">Data Analysis (Part3)</a></li>\n",
    "<li><a href=\"#Conclusion\">Conclusion (Part3)</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='Intro'></a>\n",
    "## Introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This project is originally from [Udacity Predictive Analytics Nanodegree](https://www.udacity.com/course/predictive-analytics-for-business-nanodegree--nd008). Initially, this project is analysed using [Alteryx](Alteryx.com). As reviewing the previous lessons, I analysed the same project using python for this time. Though I followed the same workflow before, I couldn't reach the exact same results yet. However, the analysis will be continously updated. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This analysis is regarding an A/B Test for deciding a Menu Launch. \n",
    "A chain of coffee shops(Round Roasters - A coffee restaurant in the United States of America) is considering launching a new menu. The executive team conducted a market test with a new menu and needs to figure whether the new menu can drive enough sales to offset the cost of marketing the new menu. <br>\n",
    " \n",
    "To minimize risk, the management team decides to test the changes in two cities with new television advertising. Denver and Chicago cities were chosen to participate in this test because the stores in these two cities (or markets) perform similarly to all stores across the entire chain of stores; performance in these two markets would be a good proxy to predict how well the updated menu performs. <br>\n",
    "\n",
    "The test ran for a period of 12 weeks **(2016-April-29 to 2016-July-21)** where five stores in each of the test markets offered the updated menu along with television advertising. The comparative period is the test period, but for last year **(2015-April-29 to 2015-July-21)**.\n",
    "\n",
    "\n",
    "I design an A/B test and analyze the results of the experiment to determine whether the menu changes should be applied to all stores. \n",
    "The predicted impact to profitability should be enough to justify the increased marketing budget: at least 18% increase in profit growth compared to the comparative period while compared to the control stores; otherwise known as incremental lift. In the data, profit is represented in the gross_margin variable.\n",
    "\n",
    "For the analysis, three different datasets are provided. \n",
    "- **(Transaction Data)** Transaction data for all stores from 2015-January-21 to 2016-August-18 \n",
    "- **(All Stores Data)** A list of all Round Roasters stores \n",
    "- **(Treatment Stores Data)** A list of the 10 stores (5 in each market) that were used as test markets. \n",
    "\n",
    "Finally, I wrote up a recommendation on whether the chain should introduce the new menu. \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='Wrangling'></a>\n",
    "## Data Wrangling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Assessment & Cleaning\n",
    "\n",
    "1. Transaction Data\n",
    "2. All Stores Data\n",
    "3. Treatment Stores Data\n",
    "4. Tidiness"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the relevant libraries \n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn import preprocessing\n",
    "import matplotlib.pyplot as plt \n",
    "plt.rc(\"font\", size=14)\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.model_selection import train_test_split\n",
    "import seaborn as sns\n",
    "sns.set()\n",
    "sns.set(style=\"whitegrid\", color_codes=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the provided datasets\n",
    "transaction_data = pd.read_csv('RoundRoastersTransactions.csv') # Transaction Data\n",
    "treatment_stores = pd.read_csv('treatment-stores.csv')  # Treatment Stores Data\n",
    "all_stores = pd.read_csv('round-roaster-stores.csv') # All Stores Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Transaction Data\n",
    "<br>\n",
    "<li><a href=\"#assess_transaction\"> Data Assessment (Transaction Data)</a></li>\n",
    "<li><a href=\"#cleaning_transaction\"> Data Cleaning (Transaction Data)</a></li>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>StoreID</th>\n",
       "      <th>Invoice Number</th>\n",
       "      <th>Invoice Date</th>\n",
       "      <th>SKU</th>\n",
       "      <th>Category</th>\n",
       "      <th>Product</th>\n",
       "      <th>QTY</th>\n",
       "      <th>Size</th>\n",
       "      <th>Gross Margin</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10018</td>\n",
       "      <td>16296643</td>\n",
       "      <td>2015-01-21</td>\n",
       "      <td>1043</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Mocha</td>\n",
       "      <td>3</td>\n",
       "      <td>L</td>\n",
       "      <td>6.7365</td>\n",
       "      <td>14.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10018</td>\n",
       "      <td>16296643</td>\n",
       "      <td>2015-01-21</td>\n",
       "      <td>2001</td>\n",
       "      <td>Pastry</td>\n",
       "      <td>Croissant</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.1000</td>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10018</td>\n",
       "      <td>16297717</td>\n",
       "      <td>2015-01-21</td>\n",
       "      <td>1021</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>3</td>\n",
       "      <td>S</td>\n",
       "      <td>4.1850</td>\n",
       "      <td>8.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10018</td>\n",
       "      <td>16297717</td>\n",
       "      <td>2015-01-21</td>\n",
       "      <td>1022</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>4</td>\n",
       "      <td>M</td>\n",
       "      <td>5.9800</td>\n",
       "      <td>11.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10018</td>\n",
       "      <td>16297717</td>\n",
       "      <td>2015-01-21</td>\n",
       "      <td>1023</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>3</td>\n",
       "      <td>L</td>\n",
       "      <td>4.7850</td>\n",
       "      <td>9.57</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID  Invoice Number Invoice Date   SKU  Category    Product  QTY Size  \\\n",
       "0    10018        16296643   2015-01-21  1043  Espresso      Mocha    3    L   \n",
       "1    10018        16296643   2015-01-21  2001    Pastry  Croissant    1  NaN   \n",
       "2    10018        16297717   2015-01-21  1021  Espresso   Espresso    3    S   \n",
       "3    10018        16297717   2015-01-21  1022  Espresso   Espresso    4    M   \n",
       "4    10018        16297717   2015-01-21  1023  Espresso   Espresso    3    L   \n",
       "\n",
       "   Gross Margin  Sales  \n",
       "0        6.7365  14.97  \n",
       "1        1.1000   2.75  \n",
       "2        4.1850   8.37  \n",
       "3        5.9800  11.96  \n",
       "4        4.7850   9.57  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transaction_data.head() # Glance at the dataset(1) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4332333, 10)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transaction_data.shape  # Glance at the dataset(2). The dataset contains 4,332,333 datapoints with 10 fields."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4332333 entries, 0 to 4332332\n",
      "Data columns (total 10 columns):\n",
      " #   Column          Dtype  \n",
      "---  ------          -----  \n",
      " 0   StoreID         int64  \n",
      " 1   Invoice Number  int64  \n",
      " 2   Invoice Date    object \n",
      " 3   SKU             int64  \n",
      " 4   Category        object \n",
      " 5   Product         object \n",
      " 6   QTY             int64  \n",
      " 7   Size            object \n",
      " 8   Gross Margin    float64\n",
      " 9   Sales           float64\n",
      "dtypes: float64(2), int64(4), object(4)\n",
      "memory usage: 330.5+ MB\n"
     ]
    }
   ],
   "source": [
    "transaction_data.info()  # Check the datatype of each field and missing values in each field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(transaction_data.duplicated()) # The data do not contain duplicated records."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Brief Descriptions of Input variables:\n",
    "1. **StoreID**  (numeric) ID number for each store of Round Roasters\n",
    "2. **Invoice Number** (numeric) \n",
    "3. **Invoice Date** (numeric) \n",
    "4. **SKU** (numeric): SKU is abbreviation of Stock Keeping Unit. It is a scannable bar code, most often seen printed on product labels in a retail store. The label allows vendors to automatically track the movement of inventory.\n",
    "5. **Category** (Categorical: 'Espresso', 'Pastry', 'Milk', 'Coffee', 'Sandwich', 'Wine' ): Product category\n",
    "6. **Product** (Categorical: 'Mocha', 'Croissant', 'Espresso', 'Latte', 'Cappuccino', 'Hot Chocolate', 'Danish', 'Drip', 'Caprese', 'Pinot Grigio', 'Cabernet Sauvignon', 'Croque Madame' ): Name of product purchased\n",
    "7. **QTY** (numeric) Quantity of products purchased\n",
    "8. **Size** (Categorical:  'S', 'M', 'L'): Size of each product purchased \n",
    "9. **Gross Margin** (numeric): Gross Margin of each product\n",
    "10. **Sales** (numeric): Sales price of each product"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>StoreID</th>\n",
       "      <th>Invoice Number</th>\n",
       "      <th>Invoice Date</th>\n",
       "      <th>SKU</th>\n",
       "      <th>Category</th>\n",
       "      <th>Product</th>\n",
       "      <th>QTY</th>\n",
       "      <th>Size</th>\n",
       "      <th>Gross Margin</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10018</td>\n",
       "      <td>16296643</td>\n",
       "      <td>2015-01-21</td>\n",
       "      <td>2001</td>\n",
       "      <td>Pastry</td>\n",
       "      <td>Croissant</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.10</td>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10018</td>\n",
       "      <td>16297717</td>\n",
       "      <td>2015-01-21</td>\n",
       "      <td>2001</td>\n",
       "      <td>Pastry</td>\n",
       "      <td>Croissant</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.40</td>\n",
       "      <td>11.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10018</td>\n",
       "      <td>16298672</td>\n",
       "      <td>2015-01-21</td>\n",
       "      <td>2001</td>\n",
       "      <td>Pastry</td>\n",
       "      <td>Croissant</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.10</td>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10018</td>\n",
       "      <td>16300868</td>\n",
       "      <td>2015-01-21</td>\n",
       "      <td>2001</td>\n",
       "      <td>Pastry</td>\n",
       "      <td>Croissant</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.20</td>\n",
       "      <td>5.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>10018</td>\n",
       "      <td>16300868</td>\n",
       "      <td>2015-01-21</td>\n",
       "      <td>2002</td>\n",
       "      <td>Pastry</td>\n",
       "      <td>Danish</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.05</td>\n",
       "      <td>3.50</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4332273</th>\n",
       "      <td>9968</td>\n",
       "      <td>45357910</td>\n",
       "      <td>2016-07-20</td>\n",
       "      <td>2001</td>\n",
       "      <td>Pastry</td>\n",
       "      <td>Croissant</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.40</td>\n",
       "      <td>11.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4332278</th>\n",
       "      <td>9968</td>\n",
       "      <td>45358218</td>\n",
       "      <td>2016-07-20</td>\n",
       "      <td>2001</td>\n",
       "      <td>Pastry</td>\n",
       "      <td>Croissant</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.30</td>\n",
       "      <td>8.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4332283</th>\n",
       "      <td>9968</td>\n",
       "      <td>45358287</td>\n",
       "      <td>2016-07-20</td>\n",
       "      <td>2001</td>\n",
       "      <td>Pastry</td>\n",
       "      <td>Croissant</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.30</td>\n",
       "      <td>8.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4332287</th>\n",
       "      <td>9968</td>\n",
       "      <td>45358830</td>\n",
       "      <td>2016-07-21</td>\n",
       "      <td>2001</td>\n",
       "      <td>Pastry</td>\n",
       "      <td>Croissant</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.40</td>\n",
       "      <td>11.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4332332</th>\n",
       "      <td>9968</td>\n",
       "      <td>45360705</td>\n",
       "      <td>2016-07-21</td>\n",
       "      <td>2001</td>\n",
       "      <td>Pastry</td>\n",
       "      <td>Croissant</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.30</td>\n",
       "      <td>8.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>942725 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         StoreID  Invoice Number Invoice Date   SKU Category    Product  QTY  \\\n",
       "1          10018        16296643   2015-01-21  2001   Pastry  Croissant    1   \n",
       "6          10018        16297717   2015-01-21  2001   Pastry  Croissant    4   \n",
       "9          10018        16298672   2015-01-21  2001   Pastry  Croissant    1   \n",
       "24         10018        16300868   2015-01-21  2001   Pastry  Croissant    2   \n",
       "25         10018        16300868   2015-01-21  2002   Pastry     Danish    1   \n",
       "...          ...             ...          ...   ...      ...        ...  ...   \n",
       "4332273     9968        45357910   2016-07-20  2001   Pastry  Croissant    4   \n",
       "4332278     9968        45358218   2016-07-20  2001   Pastry  Croissant    3   \n",
       "4332283     9968        45358287   2016-07-20  2001   Pastry  Croissant    3   \n",
       "4332287     9968        45358830   2016-07-21  2001   Pastry  Croissant    4   \n",
       "4332332     9968        45360705   2016-07-21  2001   Pastry  Croissant    3   \n",
       "\n",
       "        Size  Gross Margin  Sales  \n",
       "1        NaN          1.10   2.75  \n",
       "6        NaN          4.40  11.00  \n",
       "9        NaN          1.10   2.75  \n",
       "24       NaN          2.20   5.50  \n",
       "25       NaN          1.05   3.50  \n",
       "...      ...           ...    ...  \n",
       "4332273  NaN          4.40  11.00  \n",
       "4332278  NaN          3.30   8.25  \n",
       "4332283  NaN          3.30   8.25  \n",
       "4332287  NaN          4.40  11.00  \n",
       "4332332  NaN          3.30   8.25  \n",
       "\n",
       "[942725 rows x 10 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the transaction records with missing values\n",
    "null_data = transaction_data[transaction_data.isnull().any(axis=1)]\n",
    "null_data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "942725\n"
     ]
    }
   ],
   "source": [
    "# It seems that the column \"Size\" includes the most missing values. Let us check how many.\n",
    "print(sum(transaction_data.Size.isnull())) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Espresso    3198571\n",
       "Pastry       921588\n",
       "Milk         180229\n",
       "Sandwich      19621\n",
       "Coffee        10808\n",
       "Wine           1516\n",
       "Name: Category, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transaction_data.Category.value_counts() # Original Data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Pastry      921588\n",
       "Sandwich     19621\n",
       "Wine          1516\n",
       "Name: Category, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_data.Category.value_counts() # null_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='assess_transaction'></a>\n",
    "#### Data Assessment (Transaction Data)\n",
    "First, I loaded the transaction dataset. It has 4,332,333 datapoints and 10 features. The column: \"Size\" in the large dataset contains\n",
    "942,725 missing values. Product categories: \"Pastry\", \"Sandwich\", and \"Wine\" has the missing value of Size. Products in those categories probably have one-size only. In addition, I have checked the datatypes of each field. I made the following notes to clean the data. \n",
    "- (1) Some column names contain 'space( )' between the words. It should be replaced with 'underscore' for convenient usage of pandas\n",
    "- (2) The columns **\"StoreID\", \"Invoice Number\", \"SKU\"**,  should have an \"object\" type, rather than \"int64\". However, I kept the initial datatype of StoreID for the sake of convenience. \n",
    "- (3) **'Invoice Date'** should have datetime datatype.  \n",
    "- (4) The missing values in the column **'Size'** could be imputed \"One-Size Only\".<br>\n",
    "- (5) Since the dataset includs daily transaction histories. it is very large. The Gross Margin and Sales can be aggregated per week. \n",
    "Based on the data assessments, the initial transaction dataset is modified as below. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='cleaning_transaction'></a>\n",
    "#### Data Cleaning (Transaction Data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a copy of the original dataset. It works as a checkpoint.\n",
    "transaction_cleaned = transaction_data.copy() \n",
    "# (1) Change the column names with underscore\n",
    "transaction_cleaned.columns = transaction_cleaned.columns.str.replace(\" \", \"_\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# (2)-(3) Change the datatype of the columns \n",
    "transaction_cleaned.Invoice_Number=transaction_cleaned.Invoice_Number.apply(str)\n",
    "transaction_cleaned.SKU=transaction_cleaned.SKU.apply(str)\n",
    "transaction_cleaned.Invoice_Date=pd.to_datetime(transaction_cleaned.Invoice_Date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# (4) Fill up the missing datapoints with \"One-Size\"\n",
    "values = {'Size': 'One-Size'} \n",
    "transaction_cleaned.fillna(value=values, inplace = True); "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4332333 entries, 0 to 4332332\n",
      "Data columns (total 10 columns):\n",
      " #   Column          Dtype         \n",
      "---  ------          -----         \n",
      " 0   StoreID         int64         \n",
      " 1   Invoice_Number  object        \n",
      " 2   Invoice_Date    datetime64[ns]\n",
      " 3   SKU             object        \n",
      " 4   Category        object        \n",
      " 5   Product         object        \n",
      " 6   QTY             int64         \n",
      " 7   Size            object        \n",
      " 8   Gross_Margin    float64       \n",
      " 9   Sales           float64       \n",
      "dtypes: datetime64[ns](1), float64(2), int64(2), object(5)\n",
      "memory usage: 330.5+ MB\n"
     ]
    }
   ],
   "source": [
    "transaction_cleaned.info() # All datatypes are properly updated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "print(sum(transaction_cleaned.Size.isnull())) # No Missing values "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. All Stores Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>StoreID</th>\n",
       "      <th>Sq_Ft</th>\n",
       "      <th>AvgMonthSales</th>\n",
       "      <th>Right_Name</th>\n",
       "      <th>Phone Number</th>\n",
       "      <th>Street Combined</th>\n",
       "      <th>Street 1</th>\n",
       "      <th>Street 2</th>\n",
       "      <th>Street 3</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Postal Code</th>\n",
       "      <th>Region</th>\n",
       "      <th>Country</th>\n",
       "      <th>Coordinates</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Timezone</th>\n",
       "      <th>Current Timezone Offset</th>\n",
       "      <th>Olson Timezone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10018</td>\n",
       "      <td>1183</td>\n",
       "      <td>18000</td>\n",
       "      <td>Bellflower &amp; Spring</td>\n",
       "      <td>562-420-1317</td>\n",
       "      <td>2890 N Bellflower Blvd, #A-1, The Los Altos Ma...</td>\n",
       "      <td>2890 N Bellflower Blvd</td>\n",
       "      <td>#A-1</td>\n",
       "      <td>The Los Altos Marketplace</td>\n",
       "      <td>Long Beach</td>\n",
       "      <td>CA</td>\n",
       "      <td>908151125</td>\n",
       "      <td>West</td>\n",
       "      <td>US</td>\n",
       "      <td>(33.8085823059082, -118.124931335449)</td>\n",
       "      <td>33.808582</td>\n",
       "      <td>-118.124931</td>\n",
       "      <td>Pacific Standard Time</td>\n",
       "      <td>-480</td>\n",
       "      <td>GMT-08:00 America/Los_Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10068</td>\n",
       "      <td>1198</td>\n",
       "      <td>16000</td>\n",
       "      <td>Foothill &amp; Boston, La Crescenta</td>\n",
       "      <td>818-541-7693</td>\n",
       "      <td>3747 Foothill Boulevard, A</td>\n",
       "      <td>3747 Foothill Boulevard</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>La Cresenta</td>\n",
       "      <td>CA</td>\n",
       "      <td>912141700</td>\n",
       "      <td>West</td>\n",
       "      <td>US</td>\n",
       "      <td>(34.2375450134277, -118.26114654541)</td>\n",
       "      <td>34.237545</td>\n",
       "      <td>-118.261147</td>\n",
       "      <td>Pacific Standard Time</td>\n",
       "      <td>-480</td>\n",
       "      <td>GMT-08:00 America/Los_Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10118</td>\n",
       "      <td>1204</td>\n",
       "      <td>13000</td>\n",
       "      <td>Magic Mountain &amp; Tourney</td>\n",
       "      <td>661-260-0844</td>\n",
       "      <td>25349 Wayne Mills Place, Tourney Retail Plaza</td>\n",
       "      <td>25349 Wayne Mills Place</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tourney Retail Plaza</td>\n",
       "      <td>Valencia</td>\n",
       "      <td>CA</td>\n",
       "      <td>913551827</td>\n",
       "      <td>West</td>\n",
       "      <td>US</td>\n",
       "      <td>(34.4237022399902, -118.579261779785)</td>\n",
       "      <td>34.423702</td>\n",
       "      <td>-118.579262</td>\n",
       "      <td>Pacific Standard Time</td>\n",
       "      <td>-480</td>\n",
       "      <td>GMT-08:00 America/Los_Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10168</td>\n",
       "      <td>1195</td>\n",
       "      <td>19000</td>\n",
       "      <td>Alameda &amp; Shelton</td>\n",
       "      <td>(818) 557-6604</td>\n",
       "      <td>1190 Alameda Avenue, Suite G-2</td>\n",
       "      <td>1190 Alameda Avenue</td>\n",
       "      <td>Suite G-2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Burbank</td>\n",
       "      <td>CA</td>\n",
       "      <td>915062806</td>\n",
       "      <td>West</td>\n",
       "      <td>US</td>\n",
       "      <td>(34.1625328063965, -118.314529418945)</td>\n",
       "      <td>34.162533</td>\n",
       "      <td>-118.314529</td>\n",
       "      <td>Pacific Standard Time</td>\n",
       "      <td>-480</td>\n",
       "      <td>GMT-08:00 America/Los_Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10218</td>\n",
       "      <td>1193</td>\n",
       "      <td>15000</td>\n",
       "      <td>Victoria Gardens</td>\n",
       "      <td>909-646-8562</td>\n",
       "      <td>12466 North Main Street, Space 3340, Victoria ...</td>\n",
       "      <td>12466 North Main Street</td>\n",
       "      <td>Space 3340</td>\n",
       "      <td>Victoria Gardens</td>\n",
       "      <td>Rancho Cucamonga</td>\n",
       "      <td>CA</td>\n",
       "      <td>917398886</td>\n",
       "      <td>West</td>\n",
       "      <td>US</td>\n",
       "      <td>(34.1120910644531, -117.533462524414)</td>\n",
       "      <td>34.112091</td>\n",
       "      <td>-117.533462</td>\n",
       "      <td>Pacific Standard Time</td>\n",
       "      <td>-480</td>\n",
       "      <td>GMT-08:00 America/Los_Angeles</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID  Sq_Ft  AvgMonthSales                       Right_Name  \\\n",
       "0    10018   1183          18000              Bellflower & Spring   \n",
       "1    10068   1198          16000  Foothill & Boston, La Crescenta   \n",
       "2    10118   1204          13000         Magic Mountain & Tourney   \n",
       "3    10168   1195          19000                Alameda & Shelton   \n",
       "4    10218   1193          15000                 Victoria Gardens   \n",
       "\n",
       "     Phone Number                                    Street Combined  \\\n",
       "0    562-420-1317  2890 N Bellflower Blvd, #A-1, The Los Altos Ma...   \n",
       "1    818-541-7693                         3747 Foothill Boulevard, A   \n",
       "2    661-260-0844      25349 Wayne Mills Place, Tourney Retail Plaza   \n",
       "3  (818) 557-6604                     1190 Alameda Avenue, Suite G-2   \n",
       "4    909-646-8562  12466 North Main Street, Space 3340, Victoria ...   \n",
       "\n",
       "                  Street 1    Street 2                   Street 3  \\\n",
       "0   2890 N Bellflower Blvd        #A-1  The Los Altos Marketplace   \n",
       "1  3747 Foothill Boulevard           A                        NaN   \n",
       "2  25349 Wayne Mills Place         NaN       Tourney Retail Plaza   \n",
       "3      1190 Alameda Avenue   Suite G-2                        NaN   \n",
       "4  12466 North Main Street  Space 3340           Victoria Gardens   \n",
       "\n",
       "               City State  Postal Code Region Country  \\\n",
       "0        Long Beach    CA    908151125   West      US   \n",
       "1       La Cresenta    CA    912141700   West      US   \n",
       "2          Valencia    CA    913551827   West      US   \n",
       "3           Burbank    CA    915062806   West      US   \n",
       "4  Rancho Cucamonga    CA    917398886   West      US   \n",
       "\n",
       "                             Coordinates   Latitude   Longitude  \\\n",
       "0  (33.8085823059082, -118.124931335449)  33.808582 -118.124931   \n",
       "1   (34.2375450134277, -118.26114654541)  34.237545 -118.261147   \n",
       "2  (34.4237022399902, -118.579261779785)  34.423702 -118.579262   \n",
       "3  (34.1625328063965, -118.314529418945)  34.162533 -118.314529   \n",
       "4  (34.1120910644531, -117.533462524414)  34.112091 -117.533462   \n",
       "\n",
       "                Timezone  Current Timezone Offset  \\\n",
       "0  Pacific Standard Time                     -480   \n",
       "1  Pacific Standard Time                     -480   \n",
       "2  Pacific Standard Time                     -480   \n",
       "3  Pacific Standard Time                     -480   \n",
       "4  Pacific Standard Time                     -480   \n",
       "\n",
       "                  Olson Timezone  \n",
       "0  GMT-08:00 America/Los_Angeles  \n",
       "1  GMT-08:00 America/Los_Angeles  \n",
       "2  GMT-08:00 America/Los_Angeles  \n",
       "3  GMT-08:00 America/Los_Angeles  \n",
       "4  GMT-08:00 America/Los_Angeles  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_stores.head() # Glance at the dataset(1) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 133 entries, 0 to 132\n",
      "Data columns (total 20 columns):\n",
      " #   Column                   Non-Null Count  Dtype  \n",
      "---  ------                   --------------  -----  \n",
      " 0   StoreID                  133 non-null    int64  \n",
      " 1   Sq_Ft                    133 non-null    int64  \n",
      " 2   AvgMonthSales            133 non-null    int64  \n",
      " 3   Right_Name               133 non-null    object \n",
      " 4   Phone Number             130 non-null    object \n",
      " 5   Street Combined          133 non-null    object \n",
      " 6   Street 1                 133 non-null    object \n",
      " 7   Street 2                 37 non-null     object \n",
      " 8   Street 3                 25 non-null     object \n",
      " 9   City                     133 non-null    object \n",
      " 10  State                    133 non-null    object \n",
      " 11  Postal Code              133 non-null    int64  \n",
      " 12  Region                   133 non-null    object \n",
      " 13  Country                  133 non-null    object \n",
      " 14  Coordinates              133 non-null    object \n",
      " 15  Latitude                 133 non-null    float64\n",
      " 16  Longitude                133 non-null    float64\n",
      " 17  Timezone                 133 non-null    object \n",
      " 18  Current Timezone Offset  133 non-null    int64  \n",
      " 19  Olson Timezone           133 non-null    object \n",
      "dtypes: float64(2), int64(5), object(13)\n",
      "memory usage: 20.9+ KB\n"
     ]
    }
   ],
   "source": [
    "all_stores.info() # Glance at the dataset(2) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Note:** The last datasets are the list of all Round Roaster stores. The columns 'Phone Number' and 'Street 2, 3' include missing values, but they are not relevant to the analysis. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Treatment Stores Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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 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>StoreID</th>\n",
       "      <th>Sq_Ft</th>\n",
       "      <th>AvgMonthSales</th>\n",
       "      <th>Phone Number</th>\n",
       "      <th>Street Combined</th>\n",
       "      <th>Street 1</th>\n",
       "      <th>Street 2</th>\n",
       "      <th>Street 3</th>\n",
       "      <th>City</th>\n",
       "      <th>Postal Code</th>\n",
       "      <th>Region</th>\n",
       "      <th>Country</th>\n",
       "      <th>Coordinates</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Timezone</th>\n",
       "      <th>Current Timezone Offset</th>\n",
       "      <th>Olson Timezone</th>\n",
       "      <th>Name</th>\n",
       "      <th>Right_State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1664</td>\n",
       "      <td>1475</td>\n",
       "      <td>11000</td>\n",
       "      <td>8478428048</td>\n",
       "      <td>101 W. Main St., Barrington Village Center</td>\n",
       "      <td>101 W. Main St.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Barrington Village Center</td>\n",
       "      <td>Barrington</td>\n",
       "      <td>60010</td>\n",
       "      <td>Central</td>\n",
       "      <td>US</td>\n",
       "      <td>(42.1540565490723, -88.1362915039063)</td>\n",
       "      <td>42.154057</td>\n",
       "      <td>-88.136291</td>\n",
       "      <td>Central Standard Time</td>\n",
       "      <td>-360</td>\n",
       "      <td>GMT-06:00 America/Chicago</td>\n",
       "      <td>Barrington</td>\n",
       "      <td>IL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1675</td>\n",
       "      <td>1472</td>\n",
       "      <td>15000</td>\n",
       "      <td>8472531188</td>\n",
       "      <td>90 East Northwest Highway</td>\n",
       "      <td>90 East Northwest Highway</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mount Prospect</td>\n",
       "      <td>60056</td>\n",
       "      <td>Central</td>\n",
       "      <td>US</td>\n",
       "      <td>(42.0633544921875, -87.9355773925781)</td>\n",
       "      <td>42.063354</td>\n",
       "      <td>-87.935577</td>\n",
       "      <td>Central Standard Time</td>\n",
       "      <td>-360</td>\n",
       "      <td>GMT-06:00 America/Chicago</td>\n",
       "      <td>Northwest Hwy &amp; Elmhurst Rd</td>\n",
       "      <td>IL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1696</td>\n",
       "      <td>1471</td>\n",
       "      <td>10000</td>\n",
       "      <td>2242232528</td>\n",
       "      <td>1261 East Higgins Road</td>\n",
       "      <td>1261 East Higgins Road</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Schaumburg</td>\n",
       "      <td>60173</td>\n",
       "      <td>Central</td>\n",
       "      <td>US</td>\n",
       "      <td>(42.039363861084, -88.048828125)</td>\n",
       "      <td>42.039364</td>\n",
       "      <td>-88.048828</td>\n",
       "      <td>Central Standard Time</td>\n",
       "      <td>-360</td>\n",
       "      <td>GMT-06:00 America/Chicago</td>\n",
       "      <td>Higgins &amp; Meacham</td>\n",
       "      <td>IL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1700</td>\n",
       "      <td>1465</td>\n",
       "      <td>15000</td>\n",
       "      <td>(224) 500-9575</td>\n",
       "      <td>17 W 633 Roosevelt Road</td>\n",
       "      <td>17 W 633 Roosevelt Road</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Oakbrook Terrace</td>\n",
       "      <td>60181</td>\n",
       "      <td>Central</td>\n",
       "      <td>US</td>\n",
       "      <td>(41.8600807189941, -87.9739685058594)</td>\n",
       "      <td>41.860081</td>\n",
       "      <td>-87.973969</td>\n",
       "      <td>Central Standard Time</td>\n",
       "      <td>-360</td>\n",
       "      <td>GMT-06:00 America/Chicago</td>\n",
       "      <td>Roosevelt &amp; Summit</td>\n",
       "      <td>IL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1712</td>\n",
       "      <td>1456</td>\n",
       "      <td>19000</td>\n",
       "      <td>708-403-1461</td>\n",
       "      <td>15858 South LaGrange Road</td>\n",
       "      <td>15858 South LaGrange Road</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Orland Park</td>\n",
       "      <td>60462</td>\n",
       "      <td>Central</td>\n",
       "      <td>US</td>\n",
       "      <td>(41.603816986084, -87.8534317016602)</td>\n",
       "      <td>41.603817</td>\n",
       "      <td>-87.853432</td>\n",
       "      <td>Central Standard Time</td>\n",
       "      <td>-360</td>\n",
       "      <td>GMT-06:00 America/Chicago</td>\n",
       "      <td>159th &amp; LaGrange</td>\n",
       "      <td>IL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID  Sq_Ft  AvgMonthSales    Phone Number  \\\n",
       "0     1664   1475          11000      8478428048   \n",
       "1     1675   1472          15000      8472531188   \n",
       "2     1696   1471          10000      2242232528   \n",
       "3     1700   1465          15000  (224) 500-9575   \n",
       "4     1712   1456          19000    708-403-1461   \n",
       "\n",
       "                              Street Combined                   Street 1  \\\n",
       "0  101 W. Main St., Barrington Village Center            101 W. Main St.   \n",
       "1                   90 East Northwest Highway  90 East Northwest Highway   \n",
       "2                      1261 East Higgins Road     1261 East Higgins Road   \n",
       "3                     17 W 633 Roosevelt Road    17 W 633 Roosevelt Road   \n",
       "4                   15858 South LaGrange Road  15858 South LaGrange Road   \n",
       "\n",
       "   Street 2                   Street 3              City  Postal Code  \\\n",
       "0       NaN  Barrington Village Center        Barrington        60010   \n",
       "1       NaN                        NaN    Mount Prospect        60056   \n",
       "2       NaN                        NaN        Schaumburg        60173   \n",
       "3       NaN                        NaN  Oakbrook Terrace        60181   \n",
       "4       NaN                        NaN       Orland Park        60462   \n",
       "\n",
       "    Region Country                            Coordinates   Latitude  \\\n",
       "0  Central      US  (42.1540565490723, -88.1362915039063)  42.154057   \n",
       "1  Central      US  (42.0633544921875, -87.9355773925781)  42.063354   \n",
       "2  Central      US       (42.039363861084, -88.048828125)  42.039364   \n",
       "3  Central      US  (41.8600807189941, -87.9739685058594)  41.860081   \n",
       "4  Central      US   (41.603816986084, -87.8534317016602)  41.603817   \n",
       "\n",
       "   Longitude               Timezone  Current Timezone Offset  \\\n",
       "0 -88.136291  Central Standard Time                     -360   \n",
       "1 -87.935577  Central Standard Time                     -360   \n",
       "2 -88.048828  Central Standard Time                     -360   \n",
       "3 -87.973969  Central Standard Time                     -360   \n",
       "4 -87.853432  Central Standard Time                     -360   \n",
       "\n",
       "              Olson Timezone                         Name Right_State  \n",
       "0  GMT-06:00 America/Chicago                   Barrington          IL  \n",
       "1  GMT-06:00 America/Chicago  Northwest Hwy & Elmhurst Rd          IL  \n",
       "2  GMT-06:00 America/Chicago            Higgins & Meacham          IL  \n",
       "3  GMT-06:00 America/Chicago           Roosevelt & Summit          IL  \n",
       "4  GMT-06:00 America/Chicago             159th & LaGrange          IL  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "treatment_stores.head() # Glance at the dataset(1) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 20 columns):\n",
      " #   Column                   Non-Null Count  Dtype  \n",
      "---  ------                   --------------  -----  \n",
      " 0   StoreID                  10 non-null     int64  \n",
      " 1   Sq_Ft                    10 non-null     int64  \n",
      " 2   AvgMonthSales            10 non-null     int64  \n",
      " 3   Phone Number             9 non-null      object \n",
      " 4   Street Combined          10 non-null     object \n",
      " 5   Street 1                 10 non-null     object \n",
      " 6   Street 2                 1 non-null      float64\n",
      " 7   Street 3                 1 non-null      object \n",
      " 8   City                     10 non-null     object \n",
      " 9   Postal Code              10 non-null     int64  \n",
      " 10  Region                   10 non-null     object \n",
      " 11  Country                  10 non-null     object \n",
      " 12  Coordinates              10 non-null     object \n",
      " 13  Latitude                 10 non-null     float64\n",
      " 14  Longitude                10 non-null     float64\n",
      " 15  Timezone                 10 non-null     object \n",
      " 16  Current Timezone Offset  10 non-null     int64  \n",
      " 17  Olson Timezone           10 non-null     object \n",
      " 18  Name                     10 non-null     object \n",
      " 19  Right_State              10 non-null     object \n",
      "dtypes: float64(3), int64(5), object(12)\n",
      "memory usage: 1.7+ KB\n"
     ]
    }
   ],
   "source": [
    "treatment_stores.info() # Glance at the dataset(2) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1664, 1675, 1696, 1700, 1712, 2288, 2293, 2301, 2322, 2341],\n",
       "      dtype=int64)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "treatment_stores.StoreID.unique() # Store ID of treatments "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Note:** 10 Stores with Store ID (1664, 1675, 1696, 1700, 1712, 2288, 2293, 2301, 2322, 2341) are chosen as treatment group by the company. The columns 'Phone Number' and 'Street 2, 3' include missing values, but they are not relevant to the analysis. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Tidiness"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get a handle on the problem, I modified the given datasets. To make data set tidy provides a way to organise data values within a dataset.<br> [Reference: Tidy Data](https://cran.r-project.org/web/packages/tidyr/vignettes/tidy-data.html) \n",
    "\n",
    "**Assessment of Tidiness**\n",
    "1. Tranaction dataset contains too much information. Only the relevant period of records should be filtered. \n",
    "2. Since the test ran for the week-based periods, the data should be aggregated at the weekly level accordingly. \n",
    "3. \"Traffic records\" and \"Gross Margin and Sales\" information should be separated from the Tranaction dataset.\n",
    "4. Both \"treatment_stores\" and \"control_stores\" contain stores information. Thus it is better to combine them after labeling. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data Cleaning (Tidiness) \n",
    "\n",
    "1. Filter datapoints  \n",
    "\n",
    "> To use as control variables, we need to create trend and seasonality variables.  To do this, I need at least 52 weeks (ca. 1 year) of data, plus the number of weeks I select to calculate trend, before the beginning of the test start date. For example, when we use 6 weeks to calculate the trend, then we need 58 (= 52 + 6) weeks prior to the test start date. In this project, the company asked me to analyse 12 weeks to calculate trend, so I need 64 weeks of data prior to the test start date. Additionally, since the test lasts for 12 weeks, this indicates I need a total 76 weeks (12 + 12 + 52 weeks) of data. Therefore, I filter 76 weeks from the end of the test period 2016-July-21. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the relevant libraries \n",
    "import datetime \n",
    "from datetime import timedelta "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Test start date is 2015-02-06 00:00:00\n"
     ]
    }
   ],
   "source": [
    "# Calculate the start date of test\n",
    "test_end_date = '2016-07-22'\n",
    "end_date = datetime.datetime.strptime(test_end_date, '%Y-%m-%d') \n",
    "start_date = end_date - datetime.timedelta(weeks=76)\n",
    "print('Test start date is', start_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Using the start and end date, the previous dateset is filtered. \n",
    "filtered_df = transaction_cleaned.query('Invoice_Date >=\"2015-02-06\" and Invoice_Date <\"2016-07-22\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>StoreID</th>\n",
       "      <th>Invoice_Number</th>\n",
       "      <th>Invoice_Date</th>\n",
       "      <th>SKU</th>\n",
       "      <th>Category</th>\n",
       "      <th>Product</th>\n",
       "      <th>QTY</th>\n",
       "      <th>Size</th>\n",
       "      <th>Gross_Margin</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>628</th>\n",
       "      <td>10018</td>\n",
       "      <td>16551067</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>1013</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Cappuccino</td>\n",
       "      <td>2</td>\n",
       "      <td>L</td>\n",
       "      <td>4.7880</td>\n",
       "      <td>7.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>629</th>\n",
       "      <td>10018</td>\n",
       "      <td>16551067</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>1022</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>1</td>\n",
       "      <td>M</td>\n",
       "      <td>1.4950</td>\n",
       "      <td>2.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>630</th>\n",
       "      <td>10018</td>\n",
       "      <td>16551067</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>1023</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>3</td>\n",
       "      <td>L</td>\n",
       "      <td>4.7850</td>\n",
       "      <td>9.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>631</th>\n",
       "      <td>10018</td>\n",
       "      <td>16551067</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>1041</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Mocha</td>\n",
       "      <td>1</td>\n",
       "      <td>S</td>\n",
       "      <td>1.7955</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>10018</td>\n",
       "      <td>16551067</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>1042</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Mocha</td>\n",
       "      <td>3</td>\n",
       "      <td>M</td>\n",
       "      <td>6.0615</td>\n",
       "      <td>13.47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     StoreID Invoice_Number Invoice_Date   SKU  Category     Product  QTY  \\\n",
       "628    10018       16551067   2015-02-06  1013  Espresso  Cappuccino    2   \n",
       "629    10018       16551067   2015-02-06  1022  Espresso    Espresso    1   \n",
       "630    10018       16551067   2015-02-06  1023  Espresso    Espresso    3   \n",
       "631    10018       16551067   2015-02-06  1041  Espresso       Mocha    1   \n",
       "632    10018       16551067   2015-02-06  1042  Espresso       Mocha    3   \n",
       "\n",
       "    Size  Gross_Margin  Sales  \n",
       "628    L        4.7880   7.98  \n",
       "629    M        1.4950   2.99  \n",
       "630    L        4.7850   9.57  \n",
       "631    S        1.7955   3.99  \n",
       "632    M        6.0615  13.47  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Aggregate the transaction data weekly-based  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\wonso\\anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:1596: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self.obj[key] = _infer_fill_value(value)\n",
      "C:\\Users\\wonso\\anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:1745: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  isetter(ilocs[0], value)\n"
     ]
    }
   ],
   "source": [
    "start_date = '2015-02-06'\n",
    "filtered_df.loc[:, 'DateTimeDiff'] = filtered_df['Invoice_Date'] - datetime.datetime.strptime(start_date, '%Y-%m-%d') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\wonso\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n",
      "C:\\Users\\wonso\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  after removing the cwd from sys.path.\n",
      "C:\\Users\\wonso\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \n",
      "C:\\Users\\wonso\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  import sys\n"
     ]
    }
   ],
   "source": [
    "# Create the Week number \n",
    "import datetime as dt \n",
    "filtered_df['DateTimeDiff']=filtered_df['DateTimeDiff'].dt.days \n",
    "filtered_df['Week']=filtered_df['DateTimeDiff']//7 +1 \n",
    "days_to_shift = pd.TimedeltaIndex(7*(filtered_df['Week']-1), unit=\"D\")\n",
    "filtered_df['Week_Start'] = datetime.datetime.strptime(start_date, '%Y-%m-%d') + days_to_shift \n",
    "filtered_df['Week_End']  = filtered_df['Week_Start'] + timedelta(days=6) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>StoreID</th>\n",
       "      <th>Invoice_Number</th>\n",
       "      <th>Invoice_Date</th>\n",
       "      <th>SKU</th>\n",
       "      <th>Category</th>\n",
       "      <th>Product</th>\n",
       "      <th>QTY</th>\n",
       "      <th>Size</th>\n",
       "      <th>Gross_Margin</th>\n",
       "      <th>Sales</th>\n",
       "      <th>DateTimeDiff</th>\n",
       "      <th>Week</th>\n",
       "      <th>Week_Start</th>\n",
       "      <th>Week_End</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>628</th>\n",
       "      <td>10018</td>\n",
       "      <td>16551067</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>1013</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Cappuccino</td>\n",
       "      <td>2</td>\n",
       "      <td>L</td>\n",
       "      <td>4.7880</td>\n",
       "      <td>7.98</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>2015-02-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>629</th>\n",
       "      <td>10018</td>\n",
       "      <td>16551067</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>1022</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>1</td>\n",
       "      <td>M</td>\n",
       "      <td>1.4950</td>\n",
       "      <td>2.99</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>2015-02-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>630</th>\n",
       "      <td>10018</td>\n",
       "      <td>16551067</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>1023</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>3</td>\n",
       "      <td>L</td>\n",
       "      <td>4.7850</td>\n",
       "      <td>9.57</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>2015-02-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>631</th>\n",
       "      <td>10018</td>\n",
       "      <td>16551067</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>1041</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Mocha</td>\n",
       "      <td>1</td>\n",
       "      <td>S</td>\n",
       "      <td>1.7955</td>\n",
       "      <td>3.99</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>2015-02-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>10018</td>\n",
       "      <td>16551067</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>1042</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Mocha</td>\n",
       "      <td>3</td>\n",
       "      <td>M</td>\n",
       "      <td>6.0615</td>\n",
       "      <td>13.47</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>2015-02-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     StoreID Invoice_Number Invoice_Date   SKU  Category     Product  QTY  \\\n",
       "628    10018       16551067   2015-02-06  1013  Espresso  Cappuccino    2   \n",
       "629    10018       16551067   2015-02-06  1022  Espresso    Espresso    1   \n",
       "630    10018       16551067   2015-02-06  1023  Espresso    Espresso    3   \n",
       "631    10018       16551067   2015-02-06  1041  Espresso       Mocha    1   \n",
       "632    10018       16551067   2015-02-06  1042  Espresso       Mocha    3   \n",
       "\n",
       "    Size  Gross_Margin  Sales  DateTimeDiff  Week Week_Start   Week_End  \n",
       "628    L        4.7880   7.98             0     1 2015-02-06 2015-02-12  \n",
       "629    M        1.4950   2.99             0     1 2015-02-06 2015-02-12  \n",
       "630    L        4.7850   9.57             0     1 2015-02-06 2015-02-12  \n",
       "631    S        1.7955   3.99             0     1 2015-02-06 2015-02-12  \n",
       "632    M        6.0615  13.47             0     1 2015-02-06 2015-02-12  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Separate the information: \"Traffic records\" and \"Gross Margin and Sales\" "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- **Weekly Traffic Records**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "StoreID  Week  Week_Start  Week_End  \n",
       "1508     1     2015-02-06  2015-02-12    183\n",
       "         2     2015-02-13  2015-02-19    273\n",
       "         3     2015-02-20  2015-02-26    153\n",
       "         4     2015-02-27  2015-03-05    210\n",
       "         5     2015-03-06  2015-03-12    239\n",
       "                                        ... \n",
       "12836    72    2016-06-17  2016-06-23    212\n",
       "         73    2016-06-24  2016-06-30    248\n",
       "         74    2016-07-01  2016-07-07    188\n",
       "         75    2016-07-08  2016-07-14    311\n",
       "         76    2016-07-15  2016-07-21    312\n",
       "Name: Invoice_Number, Length: 10108, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The Number of Unique Invoices per Store and per Week\n",
    "filtered_df.groupby(['StoreID', 'Week',\"Week_Start\", \"Week_End\"])['Invoice_Number'].nunique() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>StoreID</th>\n",
       "      <th>Week</th>\n",
       "      <th>Week_Start</th>\n",
       "      <th>Week_End</th>\n",
       "      <th>Invoice_Number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1508</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>2015-02-12</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1508</td>\n",
       "      <td>2</td>\n",
       "      <td>2015-02-13</td>\n",
       "      <td>2015-02-19</td>\n",
       "      <td>273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1508</td>\n",
       "      <td>3</td>\n",
       "      <td>2015-02-20</td>\n",
       "      <td>2015-02-26</td>\n",
       "      <td>153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1508</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-02-27</td>\n",
       "      <td>2015-03-05</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1508</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-03-06</td>\n",
       "      <td>2015-03-12</td>\n",
       "      <td>239</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID  Week Week_Start   Week_End  Invoice_Number\n",
       "0     1508     1 2015-02-06 2015-02-12             183\n",
       "1     1508     2 2015-02-13 2015-02-19             273\n",
       "2     1508     3 2015-02-20 2015-02-26             153\n",
       "3     1508     4 2015-02-27 2015-03-05             210\n",
       "4     1508     5 2015-03-06 2015-03-12             239"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Change the above 'groupby' output into DataFrame\n",
    "traffic_df=filtered_df.reset_index().groupby(['StoreID', 'Week',\"Week_Start\", \"Week_End\"],as_index=False)['Invoice_Number'].nunique()\n",
    "traffic_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- **Gross Margin and Sales**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\wonso\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  \n"
     ]
    },
    {
     "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Gross_Margin</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StoreID</th>\n",
       "      <th>Week</th>\n",
       "      <th>Week_Start</th>\n",
       "      <th>Week_End</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">1508</th>\n",
       "      <th>1</th>\n",
       "      <th>2015-02-06</th>\n",
       "      <th>2015-02-12</th>\n",
       "      <td>1429.4390</td>\n",
       "      <td>3124.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>2015-02-13</th>\n",
       "      <th>2015-02-19</th>\n",
       "      <td>2031.2580</td>\n",
       "      <td>4360.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>2015-02-20</th>\n",
       "      <th>2015-02-26</th>\n",
       "      <td>1022.0430</td>\n",
       "      <td>2217.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <th>2015-02-27</th>\n",
       "      <th>2015-03-05</th>\n",
       "      <td>1372.2495</td>\n",
       "      <td>2996.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>2015-03-06</th>\n",
       "      <th>2015-03-12</th>\n",
       "      <td>1698.8590</td>\n",
       "      <td>3704.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">12836</th>\n",
       "      <th>72</th>\n",
       "      <th>2016-06-17</th>\n",
       "      <th>2016-06-23</th>\n",
       "      <td>1326.5520</td>\n",
       "      <td>2781.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <th>2016-06-24</th>\n",
       "      <th>2016-06-30</th>\n",
       "      <td>1728.0585</td>\n",
       "      <td>3654.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <th>2016-07-01</th>\n",
       "      <th>2016-07-07</th>\n",
       "      <td>1339.2400</td>\n",
       "      <td>2861.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <th>2016-07-08</th>\n",
       "      <th>2016-07-14</th>\n",
       "      <td>2178.2930</td>\n",
       "      <td>4601.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <th>2016-07-15</th>\n",
       "      <th>2016-07-21</th>\n",
       "      <td>1905.1625</td>\n",
       "      <td>4053.40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10108 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Gross_Margin    Sales\n",
       "StoreID Week Week_Start Week_End                         \n",
       "1508    1    2015-02-06 2015-02-12     1429.4390  3124.61\n",
       "        2    2015-02-13 2015-02-19     2031.2580  4360.54\n",
       "        3    2015-02-20 2015-02-26     1022.0430  2217.27\n",
       "        4    2015-02-27 2015-03-05     1372.2495  2996.95\n",
       "        5    2015-03-06 2015-03-12     1698.8590  3704.73\n",
       "...                                          ...      ...\n",
       "12836   72   2016-06-17 2016-06-23     1326.5520  2781.26\n",
       "        73   2016-06-24 2016-06-30     1728.0585  3654.48\n",
       "        74   2016-07-01 2016-07-07     1339.2400  2861.75\n",
       "        75   2016-07-08 2016-07-14     2178.2930  4601.37\n",
       "        76   2016-07-15 2016-07-21     1905.1625  4053.40\n",
       "\n",
       "[10108 rows x 2 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Gross Margin and Sales per Store and per Week\n",
    "filtered_df.groupby(['StoreID', 'Week',\"Week_Start\", \"Week_End\"])['Gross_Margin', 'Sales'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\wonso\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  \n"
     ]
    },
    {
     "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>StoreID</th>\n",
       "      <th>Week</th>\n",
       "      <th>Week_Start</th>\n",
       "      <th>Week_End</th>\n",
       "      <th>Gross_Margin</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1508</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-02-06</td>\n",
       "      <td>2015-02-12</td>\n",
       "      <td>1429.4390</td>\n",
       "      <td>3124.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1508</td>\n",
       "      <td>2</td>\n",
       "      <td>2015-02-13</td>\n",
       "      <td>2015-02-19</td>\n",
       "      <td>2031.2580</td>\n",
       "      <td>4360.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1508</td>\n",
       "      <td>3</td>\n",
       "      <td>2015-02-20</td>\n",
       "      <td>2015-02-26</td>\n",
       "      <td>1022.0430</td>\n",
       "      <td>2217.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1508</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-02-27</td>\n",
       "      <td>2015-03-05</td>\n",
       "      <td>1372.2495</td>\n",
       "      <td>2996.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1508</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-03-06</td>\n",
       "      <td>2015-03-12</td>\n",
       "      <td>1698.8590</td>\n",
       "      <td>3704.73</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID  Week Week_Start   Week_End  Gross_Margin    Sales\n",
       "0     1508     1 2015-02-06 2015-02-12     1429.4390  3124.61\n",
       "1     1508     2 2015-02-13 2015-02-19     2031.2580  4360.54\n",
       "2     1508     3 2015-02-20 2015-02-26     1022.0430  2217.27\n",
       "3     1508     4 2015-02-27 2015-03-05     1372.2495  2996.95\n",
       "4     1508     5 2015-03-06 2015-03-12     1698.8590  3704.73"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Change the above 'groupby' output into DataFrame\n",
    "weekly_sales_df=filtered_df.reset_index().groupby(['StoreID', 'Week',\"Week_Start\", \"Week_End\"],as_index=False)['Gross_Margin', 'Sales'].sum()\n",
    "weekly_sales_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. Combine \"treatment_stores\" and \"control_stores\" after labeling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "treatment_stores['Group'] = 'treatment'\n",
    "all_stores['Group'] = 'control'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['StoreID', 'Sq_Ft', 'AvgMonthSales', 'Phone Number', 'Street Combined',\n",
       "       'Street 1', 'Street 2', 'Street 3', 'City', 'Postal Code', 'Region',\n",
       "       'Country', 'Coordinates', 'Latitude', 'Longitude', 'Timezone',\n",
       "       'Current Timezone Offset', 'Olson Timezone', 'Name', 'Right_State',\n",
       "       'Group'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "treatment_stores.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['StoreID', 'Sq_Ft', 'AvgMonthSales', 'Right_Name', 'Phone Number',\n",
       "       'Street Combined', 'Street 1', 'Street 2', 'Street 3', 'City', 'State',\n",
       "       'Postal Code', 'Region', 'Country', 'Coordinates', 'Latitude',\n",
       "       'Longitude', 'Timezone', 'Current Timezone Offset', 'Olson Timezone',\n",
       "       'Group'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_stores.columns "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename the columns with different names \n",
    "treatment_stores.rename(columns={\"Right_State\": \"State\"}, inplace = True)\n",
    "all_stores.rename(columns={\"Right_Name\": \"Name\"}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the unnecessary columns from treatment_stores and control_stores dataframes \n",
    "treatment_stores.drop(['Phone Number', 'Street Combined', 'Street 1', 'Street 2', 'Street 3', 'Postal Code', 'Country', 'Coordinates', 'Latitude',\n",
    "       'Longitude', 'Timezone', 'Current Timezone Offset', 'Olson Timezone' ],axis =1, inplace = True)\n",
    "all_stores.drop(['Phone Number', 'Street Combined', 'Street 1', 'Street 2', 'Street 3',  'Postal Code', 'Country', 'Coordinates', 'Latitude',\n",
    "       'Longitude', 'Timezone', 'Current Timezone Offset', 'Olson Timezone' ],axis =1, inplace =True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "stores_df =  all_stores.append(treatment_stores, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 143 entries, 0 to 142\n",
      "Data columns (total 8 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   StoreID        143 non-null    int64 \n",
      " 1   Sq_Ft          143 non-null    int64 \n",
      " 2   AvgMonthSales  143 non-null    int64 \n",
      " 3   Name           143 non-null    object\n",
      " 4   City           143 non-null    object\n",
      " 5   State          143 non-null    object\n",
      " 6   Region         143 non-null    object\n",
      " 7   Group          143 non-null    object\n",
      "dtypes: int64(3), object(5)\n",
      "memory usage: 9.1+ KB\n"
     ]
    }
   ],
   "source": [
    "stores_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> After cleaning up the data, we have three datasets. \n",
    "> - **traffic_df:** It contains the number of traffic information. The number of traffic is aggregated per week per store. \n",
    "> - **weekly_sales_df:** It contains weekly gross margin and sales information per store. \n",
    "> - **stores_df:** It includes all stores of the company. Each store is labelled  as treatment or control. \n", 
    "> Each store represents each unit. Since we have relatively small size of sample units (133 Stores in total), we need to select matched-pair design for this analysis, rather than randomized design. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "traffic_df.to_csv('traffic_df.csv', index = False)\n",
    "weekly_sales_df.to_csv('weekly_sales_df.csv', index = False)\n",
    "stores_df.to_csv('stores_df.csv', index = False)"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}