Shipt DS Ops - ETL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import zipfile\n",
    "import pandasql as ps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "zf = zipfile.ZipFile('Data Scientist Ops Research Test 1.zip') \n",
    "\n",
    "orders = pd.read_csv(zf.open('Scientist Test/orders.csv'))\n",
    "delivery = pd.read_csv(zf.open('Scientist Test/delivery_windows.csv'))\n",
    "shopper = pd.read_csv(zf.open('Scientist Test/shopper_schedule.csv'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Understanding the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>shopper_id</th>\n",
       "      <th>delivery_window_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>892850508</td>\n",
       "      <td>5599879</td>\n",
       "      <td>29979371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>904485576</td>\n",
       "      <td>3539999</td>\n",
       "      <td>30324692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>916808463</td>\n",
       "      <td>5599879</td>\n",
       "      <td>30679690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>916825164</td>\n",
       "      <td>3539999</td>\n",
       "      <td>30679691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>915339858</td>\n",
       "      <td>4004300</td>\n",
       "      <td>30679684</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    order_id  shopper_id  delivery_window_id\n",
       "0  892850508     5599879            29979371\n",
       "1  904485576     3539999            30324692\n",
       "2  916808463     5599879            30679690\n",
       "3  916825164     3539999            30679691\n",
       "4  915339858     4004300            30679684"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3430, 3)"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_id              3430\n",
       "shopper_id             385\n",
       "delivery_window_id    2089\n",
       "dtype: int64"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_id              0\n",
       "shopper_id            0\n",
       "delivery_window_id    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "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>delivery_window_id</th>\n",
       "      <th>starts_at</th>\n",
       "      <th>market_name</th>\n",
       "      <th>neighborhood_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>30211212</td>\n",
       "      <td>2017-03-09T20:00:00Z</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>SoMa / Castro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29979371</td>\n",
       "      <td>2017-03-05T19:00:00Z</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>SoMa / Castro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29921412</td>\n",
       "      <td>2017-03-04T20:00:00Z</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>SoMa / Castro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29805494</td>\n",
       "      <td>2017-03-02T22:00:00Z</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>SoMa / Castro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30324692</td>\n",
       "      <td>2017-03-11T19:00:00Z</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>SoMa / Castro</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   delivery_window_id             starts_at    market_name neighborhood_name\n",
       "0            30211212  2017-03-09T20:00:00Z  San Francisco     SoMa / Castro\n",
       "1            29979371  2017-03-05T19:00:00Z  San Francisco     SoMa / Castro\n",
       "2            29921412  2017-03-04T20:00:00Z  San Francisco     SoMa / Castro\n",
       "3            29805494  2017-03-02T22:00:00Z  San Francisco     SoMa / Castro\n",
       "4            30324692  2017-03-11T19:00:00Z  San Francisco     SoMa / Castro"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delivery.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8448, 4)"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delivery.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "delivery_window_id    8448\n",
       "starts_at              768\n",
       "market_name              3\n",
       "neighborhood_name       11\n",
       "dtype: int64"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delivery.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "delivery_window_id    0\n",
       "starts_at             0\n",
       "market_name           0\n",
       "neighborhood_name     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delivery.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "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>shopper_id</th>\n",
       "      <th>delivery_window_id</th>\n",
       "      <th>abandoned</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3540919</td>\n",
       "      <td>30211212</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3618452</td>\n",
       "      <td>30211212</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3986107</td>\n",
       "      <td>30211212</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4004300</td>\n",
       "      <td>30211212</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4081488</td>\n",
       "      <td>30211212</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   shopper_id  delivery_window_id  abandoned\n",
       "0     3540919            30211212      False\n",
       "1     3618452            30211212      False\n",
       "2     3986107            30211212      False\n",
       "3     4004300            30211212      False\n",
       "4     4081488            30211212      False"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "shopper.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All tables can be joined on delivery_window_id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(80494, 3)"
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "shopper.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "shopper_id             590\n",
       "delivery_window_id    7849\n",
       "abandoned                2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "shopper.nunique() #multiple records of ind shoppers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "shopper_id            0\n",
       "delivery_window_id    0\n",
       "abandoned             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "shopper.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    60450\n",
       "True     20044\n",
       "Name: abandoned, dtype: int64"
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "shopper.abandoned.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. For every market and delivery window combination, **find the number of orders placed** \n",
    "and **number of unique shoppers who made themselves available to fulfill an order**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "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>market_name</th>\n",
       "      <th>delivery_window_id</th>\n",
       "      <th>no_orders_placed</th>\n",
       "      <th>no_unique_avail_shoppers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29678563</td>\n",
       "      <td>106</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29678564</td>\n",
       "      <td>98</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29678566</td>\n",
       "      <td>80</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29683435</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29683437</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29683509</td>\n",
       "      <td>40</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29683511</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29739874</td>\n",
       "      <td>44</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29739875</td>\n",
       "      <td>250</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29739876</td>\n",
       "      <td>159</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29739877</td>\n",
       "      <td>324</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29739878</td>\n",
       "      <td>270</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29739879</td>\n",
       "      <td>150</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29739880</td>\n",
       "      <td>110</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29739881</td>\n",
       "      <td>228</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29739882</td>\n",
       "      <td>204</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29739883</td>\n",
       "      <td>96</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29739885</td>\n",
       "      <td>43</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29739887</td>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29744746</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29744747</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29744751</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29744753</td>\n",
       "      <td>22</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29744755</td>\n",
       "      <td>20</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29744756</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29744819</td>\n",
       "      <td>90</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29744822</td>\n",
       "      <td>48</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29744824</td>\n",
       "      <td>94</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29744826</td>\n",
       "      <td>46</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29744830</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2059</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510639</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2060</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510641</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2061</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510642</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2062</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510643</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2063</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510655</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2064</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510656</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2065</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510657</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2066</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510659</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2067</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510660</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2068</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510661</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2069</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510662</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2070</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510663</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2071</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510664</td>\n",
       "      <td>24</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2072</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510665</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2073</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510666</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2074</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510667</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2075</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510949</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2076</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510950</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2077</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31510955</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2078</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569598</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2079</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569601</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2080</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569605</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2081</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569606</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2082</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569622</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2083</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569624</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2084</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569625</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2085</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569627</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2086</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569628</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2087</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569629</td>\n",
       "      <td>24</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2088</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569914</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2089 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        market_name  delivery_window_id  no_orders_placed  \\\n",
       "0       New York NY            29678563               106   \n",
       "1       New York NY            29678564                98   \n",
       "2       New York NY            29678566                80   \n",
       "3       New York NY            29683435                 8   \n",
       "4       New York NY            29683437                 6   \n",
       "5       New York NY            29683509                40   \n",
       "6       New York NY            29683511                34   \n",
       "7       New York NY            29739874                44   \n",
       "8       New York NY            29739875               250   \n",
       "9       New York NY            29739876               159   \n",
       "10      New York NY            29739877               324   \n",
       "11      New York NY            29739878               270   \n",
       "12      New York NY            29739879               150   \n",
       "13      New York NY            29739880               110   \n",
       "14      New York NY            29739881               228   \n",
       "15      New York NY            29739882               204   \n",
       "16      New York NY            29739883                96   \n",
       "17      New York NY            29739885                43   \n",
       "18      New York NY            29739887                32   \n",
       "19      New York NY            29744746                 9   \n",
       "20      New York NY            29744747                10   \n",
       "21      New York NY            29744751                 6   \n",
       "22      New York NY            29744753                22   \n",
       "23      New York NY            29744755                20   \n",
       "24      New York NY            29744756                 8   \n",
       "25      New York NY            29744819                90   \n",
       "26      New York NY            29744822                48   \n",
       "27      New York NY            29744824                94   \n",
       "28      New York NY            29744826                46   \n",
       "29      New York NY            29744830                34   \n",
       "...             ...                 ...               ...   \n",
       "2059  San Francisco            31510639                 9   \n",
       "2060  San Francisco            31510641                 7   \n",
       "2061  San Francisco            31510642                 7   \n",
       "2062  San Francisco            31510643                 8   \n",
       "2063  San Francisco            31510655                12   \n",
       "2064  San Francisco            31510656                 7   \n",
       "2065  San Francisco            31510657                14   \n",
       "2066  San Francisco            31510659                18   \n",
       "2067  San Francisco            31510660                 6   \n",
       "2068  San Francisco            31510661                 6   \n",
       "2069  San Francisco            31510662                12   \n",
       "2070  San Francisco            31510663                12   \n",
       "2071  San Francisco            31510664                24   \n",
       "2072  San Francisco            31510665                10   \n",
       "2073  San Francisco            31510666                 8   \n",
       "2074  San Francisco            31510667                20   \n",
       "2075  San Francisco            31510949                13   \n",
       "2076  San Francisco            31510950                12   \n",
       "2077  San Francisco            31510955                10   \n",
       "2078  San Francisco            31569598                 4   \n",
       "2079  San Francisco            31569601                 5   \n",
       "2080  San Francisco            31569605                 8   \n",
       "2081  San Francisco            31569606                 5   \n",
       "2082  San Francisco            31569622                 8   \n",
       "2083  San Francisco            31569624                 8   \n",
       "2084  San Francisco            31569625                12   \n",
       "2085  San Francisco            31569627                 3   \n",
       "2086  San Francisco            31569628                 4   \n",
       "2087  San Francisco            31569629                24   \n",
       "2088  San Francisco            31569914                 5   \n",
       "\n",
       "      no_unique_avail_shoppers  \n",
       "0                           53  \n",
       "1                           49  \n",
       "2                           40  \n",
       "3                            8  \n",
       "4                            6  \n",
       "5                           40  \n",
       "6                           34  \n",
       "7                           44  \n",
       "8                           50  \n",
       "9                           53  \n",
       "10                          54  \n",
       "11                          54  \n",
       "12                          50  \n",
       "13                          55  \n",
       "14                          57  \n",
       "15                          51  \n",
       "16                          48  \n",
       "17                          43  \n",
       "18                          32  \n",
       "19                           9  \n",
       "20                          10  \n",
       "21                           6  \n",
       "22                          11  \n",
       "23                          10  \n",
       "24                           8  \n",
       "25                          45  \n",
       "26                          48  \n",
       "27                          47  \n",
       "28                          46  \n",
       "29                          34  \n",
       "...                        ...  \n",
       "2059                         9  \n",
       "2060                         7  \n",
       "2061                         7  \n",
       "2062                         8  \n",
       "2063                         6  \n",
       "2064                         7  \n",
       "2065                         7  \n",
       "2066                         6  \n",
       "2067                         6  \n",
       "2068                         6  \n",
       "2069                         6  \n",
       "2070                         6  \n",
       "2071                         6  \n",
       "2072                         5  \n",
       "2073                         4  \n",
       "2074                         5  \n",
       "2075                        13  \n",
       "2076                        12  \n",
       "2077                        10  \n",
       "2078                         4  \n",
       "2079                         5  \n",
       "2080                         8  \n",
       "2081                         5  \n",
       "2082                         4  \n",
       "2083                         4  \n",
       "2084                         4  \n",
       "2085                         3  \n",
       "2086                         4  \n",
       "2087                         6  \n",
       "2088                         5  \n",
       "\n",
       "[2089 rows x 4 columns]"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "part_one = ps.sqldf(\"select d.market_name, d.delivery_window_id, count(o.order_id) no_orders_placed, count(distinct s.shopper_id) no_unique_avail_shoppers from delivery d join orders o on d.delivery_window_id = o.delivery_window_id join shopper s on o.delivery_window_id = s.delivery_window_id group by 1,2\")\n",
    "part_one"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2.\tNow, add two columns to the previous table in (1): \n",
    "\n",
    "    a.\tthe number of unique shoppers who abandoned their scheduled delivery window (hint: use the abandoned field)\n",
    "   \n",
    "   b.\tthe number of unique shoppers who fulfilled an order in the delivery window"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to complete this, will need to output 2 different tables:\n",
    "1. delivery_window_id + shopper_id + abadoned = 1  (abandoned unique shoppers)\n",
    "2. delivery_window_id + shopper_id + abadoned = 0 (fulfilled unique shoppers)\n",
    "* Combine these two tables on delivery_window_id\n",
    "* Get count of each unique shopper who was available but abandoned\n",
    "* Get count of each unique shopper who was available but did not abandon (fulfilled)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "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>delivery_window_id</th>\n",
       "      <th>no_unique_abandoned_shoppers</th>\n",
       "      <th>abandoned</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29675515</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29675516</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29675517</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29675518</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>29678563</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>29678564</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>29678566</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>29683435</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>29683437</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>29683509</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>29683511</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>29686242</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>29686245</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>29686264</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>29686265</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>29686266</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>29686267</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>29686268</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>29686269</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>29700187</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>29715355</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>29736827</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>29736829</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>29736830</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>29736831</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>29736832</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>29736833</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>29736834</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29736836</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>29736837</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1790</th>\n",
       "      <td>31558951</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1791</th>\n",
       "      <td>31558953</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1792</th>\n",
       "      <td>31561934</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1793</th>\n",
       "      <td>31561942</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1794</th>\n",
       "      <td>31561945</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1795</th>\n",
       "      <td>31561946</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1796</th>\n",
       "      <td>31561947</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1797</th>\n",
       "      <td>31561948</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1798</th>\n",
       "      <td>31561949</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1799</th>\n",
       "      <td>31561950</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1800</th>\n",
       "      <td>31561951</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1801</th>\n",
       "      <td>31561952</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1802</th>\n",
       "      <td>31566792</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1803</th>\n",
       "      <td>31566794</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1804</th>\n",
       "      <td>31566795</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1805</th>\n",
       "      <td>31566798</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1806</th>\n",
       "      <td>31566799</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1807</th>\n",
       "      <td>31566800</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1808</th>\n",
       "      <td>31566801</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1809</th>\n",
       "      <td>31566863</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1810</th>\n",
       "      <td>31566865</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1811</th>\n",
       "      <td>31566866</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1812</th>\n",
       "      <td>31566867</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1813</th>\n",
       "      <td>31566868</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1814</th>\n",
       "      <td>31566873</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1815</th>\n",
       "      <td>31569605</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1816</th>\n",
       "      <td>31569606</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1817</th>\n",
       "      <td>31569628</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1818</th>\n",
       "      <td>31569629</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1819</th>\n",
       "      <td>31569914</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1820 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      delivery_window_id  no_unique_abandoned_shoppers  abandoned\n",
       "0               29675515                             4          1\n",
       "1               29675516                             5          1\n",
       "2               29675517                             5          1\n",
       "3               29675518                             3          1\n",
       "4               29678563                            17          1\n",
       "5               29678564                            15          1\n",
       "6               29678566                            11          1\n",
       "7               29683435                             1          1\n",
       "8               29683437                             2          1\n",
       "9               29683509                            13          1\n",
       "10              29683511                             8          1\n",
       "11              29686242                             3          1\n",
       "12              29686245                             4          1\n",
       "13              29686264                             3          1\n",
       "14              29686265                             3          1\n",
       "15              29686266                             2          1\n",
       "16              29686267                             2          1\n",
       "17              29686268                             2          1\n",
       "18              29686269                             1          1\n",
       "19              29700187                             2          1\n",
       "20              29715355                             2          1\n",
       "21              29736827                             1          1\n",
       "22              29736829                             2          1\n",
       "23              29736830                             3          1\n",
       "24              29736831                             3          1\n",
       "25              29736832                             2          1\n",
       "26              29736833                             2          1\n",
       "27              29736834                             2          1\n",
       "28              29736836                             3          1\n",
       "29              29736837                             2          1\n",
       "...                  ...                           ...        ...\n",
       "1790            31558951                             1          1\n",
       "1791            31558953                             1          1\n",
       "1792            31561934                             1          1\n",
       "1793            31561942                             5          1\n",
       "1794            31561945                             9          1\n",
       "1795            31561946                             9          1\n",
       "1796            31561947                             7          1\n",
       "1797            31561948                             9          1\n",
       "1798            31561949                             9          1\n",
       "1799            31561950                             6          1\n",
       "1800            31561951                             7          1\n",
       "1801            31561952                            12          1\n",
       "1802            31566792                             1          1\n",
       "1803            31566794                             1          1\n",
       "1804            31566795                             1          1\n",
       "1805            31566798                             2          1\n",
       "1806            31566799                             3          1\n",
       "1807            31566800                             3          1\n",
       "1808            31566801                             3          1\n",
       "1809            31566863                             7          1\n",
       "1810            31566865                             9          1\n",
       "1811            31566866                             9          1\n",
       "1812            31566867                             8          1\n",
       "1813            31566868                            10          1\n",
       "1814            31566873                            10          1\n",
       "1815            31569605                             3          1\n",
       "1816            31569606                             1          1\n",
       "1817            31569628                             1          1\n",
       "1818            31569629                             2          1\n",
       "1819            31569914                             1          1\n",
       "\n",
       "[1820 rows x 3 columns]"
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "abandoned = ps.sqldf(\"select d.delivery_window_id, count(distinct s.shopper_id) no_unique_abandoned_shoppers, s.abandoned from delivery d join orders o on d.delivery_window_id = o.delivery_window_id join shopper s on o.delivery_window_id = s.delivery_window_id where s.abandoned=1 group by 1,3\")\n",
    "abandoned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "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>delivery_window_id</th>\n",
       "      <th>no_unique_fulfilled_shoppers</th>\n",
       "      <th>abandoned</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29675515</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29675516</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29675517</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29675518</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>29678563</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>29678564</td>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>29678566</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>29683435</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>29683437</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>29683509</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>29683511</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>29686242</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>29686245</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>29686264</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>29686265</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>29686266</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>29686267</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>29686268</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>29686269</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>29700187</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>29715345</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>29715355</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>29736825</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>29736827</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>29736829</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>29736830</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>29736831</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>29736832</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29736833</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>29736834</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2055</th>\n",
       "      <td>31561950</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2056</th>\n",
       "      <td>31561951</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2057</th>\n",
       "      <td>31561952</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2058</th>\n",
       "      <td>31566792</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2059</th>\n",
       "      <td>31566794</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2060</th>\n",
       "      <td>31566795</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2061</th>\n",
       "      <td>31566798</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2062</th>\n",
       "      <td>31566799</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2063</th>\n",
       "      <td>31566800</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2064</th>\n",
       "      <td>31566801</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2065</th>\n",
       "      <td>31566863</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2066</th>\n",
       "      <td>31566865</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2067</th>\n",
       "      <td>31566866</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2068</th>\n",
       "      <td>31566867</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2069</th>\n",
       "      <td>31566868</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2070</th>\n",
       "      <td>31566873</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2071</th>\n",
       "      <td>31569598</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2072</th>\n",
       "      <td>31569601</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2073</th>\n",
       "      <td>31569605</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2074</th>\n",
       "      <td>31569606</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2075</th>\n",
       "      <td>31569622</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2076</th>\n",
       "      <td>31569624</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2077</th>\n",
       "      <td>31569625</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2078</th>\n",
       "      <td>31569627</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2079</th>\n",
       "      <td>31569628</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2080</th>\n",
       "      <td>31569629</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2081</th>\n",
       "      <td>31569914</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2082</th>\n",
       "      <td>31582016</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2083</th>\n",
       "      <td>31582039</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2084</th>\n",
       "      <td>31582040</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2085 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      delivery_window_id  no_unique_fulfilled_shoppers  abandoned\n",
       "0               29675515                             5          0\n",
       "1               29675516                             2          0\n",
       "2               29675517                             2          0\n",
       "3               29675518                             3          0\n",
       "4               29678563                            36          0\n",
       "5               29678564                            34          0\n",
       "6               29678566                            29          0\n",
       "7               29683435                             7          0\n",
       "8               29683437                             4          0\n",
       "9               29683509                            27          0\n",
       "10              29683511                            26          0\n",
       "11              29686242                            11          0\n",
       "12              29686245                             6          0\n",
       "13              29686264                             6          0\n",
       "14              29686265                             5          0\n",
       "15              29686266                             6          0\n",
       "16              29686267                             6          0\n",
       "17              29686268                             5          0\n",
       "18              29686269                             4          0\n",
       "19              29700187                             5          0\n",
       "20              29715345                             3          0\n",
       "21              29715355                             1          0\n",
       "22              29736825                             2          0\n",
       "23              29736827                             2          0\n",
       "24              29736829                             2          0\n",
       "25              29736830                             2          0\n",
       "26              29736831                             4          0\n",
       "27              29736832                             3          0\n",
       "28              29736833                             2          0\n",
       "29              29736834                             3          0\n",
       "...                  ...                           ...        ...\n",
       "2055            31561950                            26          0\n",
       "2056            31561951                            29          0\n",
       "2057            31561952                            28          0\n",
       "2058            31566792                             7          0\n",
       "2059            31566794                             6          0\n",
       "2060            31566795                             7          0\n",
       "2061            31566798                             7          0\n",
       "2062            31566799                             7          0\n",
       "2063            31566800                             8          0\n",
       "2064            31566801                            10          0\n",
       "2065            31566863                            14          0\n",
       "2066            31566865                            22          0\n",
       "2067            31566866                            23          0\n",
       "2068            31566867                            22          0\n",
       "2069            31566868                            22          0\n",
       "2070            31566873                            31          0\n",
       "2071            31569598                             4          0\n",
       "2072            31569601                             5          0\n",
       "2073            31569605                             5          0\n",
       "2074            31569606                             4          0\n",
       "2075            31569622                             4          0\n",
       "2076            31569624                             4          0\n",
       "2077            31569625                             4          0\n",
       "2078            31569627                             3          0\n",
       "2079            31569628                             3          0\n",
       "2080            31569629                             4          0\n",
       "2081            31569914                             4          0\n",
       "2082            31582016                             5          0\n",
       "2083            31582039                             2          0\n",
       "2084            31582040                             2          0\n",
       "\n",
       "[2085 rows x 3 columns]"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fulfilled = ps.sqldf(\"select d.delivery_window_id, count(distinct s.shopper_id) no_unique_fulfilled_shoppers, s.abandoned from delivery d join orders o on d.delivery_window_id = o.delivery_window_id join shopper s on o.delivery_window_id = s.delivery_window_id where s.abandoned=0 group by 1,3\")\n",
    "fulfilled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "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>market_name</th>\n",
       "      <th>delivery_window_id</th>\n",
       "      <th>no_orders_placed</th>\n",
       "      <th>no_unique_avail_shoppers</th>\n",
       "      <th>delivery_window_id</th>\n",
       "      <th>no_unique_abandoned_shoppers</th>\n",
       "      <th>abandoned</th>\n",
       "      <th>delivery_window_id</th>\n",
       "      <th>no_unique_fulfilled_shoppers</th>\n",
       "      <th>abandoned</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29675515</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>29675515</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>29675515</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29675516</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>29675516</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>29675516</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29675517</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>29675517</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>29675517</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29675518</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>29675518</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>29675518</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29678563</td>\n",
       "      <td>106</td>\n",
       "      <td>53</td>\n",
       "      <td>29678563</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>29678563</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29678564</td>\n",
       "      <td>98</td>\n",
       "      <td>49</td>\n",
       "      <td>29678564</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>29678564</td>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29678566</td>\n",
       "      <td>80</td>\n",
       "      <td>40</td>\n",
       "      <td>29678566</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>29678566</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29683435</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>29683435</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>29683435</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29683437</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>29683437</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>29683437</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29683509</td>\n",
       "      <td>40</td>\n",
       "      <td>40</td>\n",
       "      <td>29683509</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>29683509</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29683511</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>29683511</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>29683511</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686242</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>29686242</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>29686242</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686245</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>29686245</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>29686245</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686264</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>29686264</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>29686264</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686265</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>29686265</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>29686265</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686266</td>\n",
       "      <td>16</td>\n",
       "      <td>8</td>\n",
       "      <td>29686266</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>29686266</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686267</td>\n",
       "      <td>24</td>\n",
       "      <td>8</td>\n",
       "      <td>29686267</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>29686267</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686268</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>29686268</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>29686268</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686269</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>29686269</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>29686269</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29700187</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>29700187</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>29700187</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29715355</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>29715355</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>29715355</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736827</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>29736827</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>29736827</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736829</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>29736829</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>29736829</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736830</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>29736830</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>29736830</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736831</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>29736831</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>29736831</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736832</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>29736832</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>29736832</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736833</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>29736833</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>29736833</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736834</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>29736834</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>29736834</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736836</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>29736836</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>29736836</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736837</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>29736837</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>29736837</td>\n",
       "      <td>4</td>\n",
       "      <td>0</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>1786</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>31558951</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>31558951</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>31558951</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1787</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>31558953</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>31558953</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>31558953</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1788</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561934</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>31561934</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>31561934</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1789</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561942</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>31561942</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>31561942</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1790</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561945</td>\n",
       "      <td>116</td>\n",
       "      <td>29</td>\n",
       "      <td>31561945</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>31561945</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1791</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561946</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>31561946</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>31561946</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1792</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561947</td>\n",
       "      <td>108</td>\n",
       "      <td>27</td>\n",
       "      <td>31561947</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>31561947</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1793</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561948</td>\n",
       "      <td>58</td>\n",
       "      <td>29</td>\n",
       "      <td>31561948</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>31561948</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1794</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561949</td>\n",
       "      <td>66</td>\n",
       "      <td>33</td>\n",
       "      <td>31561949</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>31561949</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1795</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561950</td>\n",
       "      <td>96</td>\n",
       "      <td>32</td>\n",
       "      <td>31561950</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>31561950</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1796</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561951</td>\n",
       "      <td>72</td>\n",
       "      <td>36</td>\n",
       "      <td>31561951</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>31561951</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1797</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561952</td>\n",
       "      <td>120</td>\n",
       "      <td>40</td>\n",
       "      <td>31561952</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>31561952</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1798</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566792</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>31566792</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>31566792</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1799</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566794</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>31566794</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>31566794</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1800</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566795</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>31566795</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>31566795</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1801</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566798</td>\n",
       "      <td>27</td>\n",
       "      <td>9</td>\n",
       "      <td>31566798</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>31566798</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1802</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566799</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>31566799</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>31566799</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1803</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566800</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>31566800</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>31566800</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1804</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566801</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>31566801</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>31566801</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1805</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566863</td>\n",
       "      <td>21</td>\n",
       "      <td>21</td>\n",
       "      <td>31566863</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>31566863</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1806</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566865</td>\n",
       "      <td>93</td>\n",
       "      <td>31</td>\n",
       "      <td>31566865</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>31566865</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1807</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566866</td>\n",
       "      <td>64</td>\n",
       "      <td>32</td>\n",
       "      <td>31566866</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>31566866</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1808</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566867</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>31566867</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>31566867</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1809</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566868</td>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>31566868</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>31566868</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1810</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566873</td>\n",
       "      <td>41</td>\n",
       "      <td>41</td>\n",
       "      <td>31566873</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>31566873</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1811</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569605</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>31569605</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>31569605</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1812</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569606</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>31569606</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>31569606</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1813</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569628</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>31569628</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>31569628</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1814</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569629</td>\n",
       "      <td>24</td>\n",
       "      <td>6</td>\n",
       "      <td>31569629</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>31569629</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1815</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569914</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>31569914</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>31569914</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1816 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        market_name  delivery_window_id  no_orders_placed  \\\n",
       "0      Philadelphia            29675515                 9   \n",
       "1      Philadelphia            29675516                14   \n",
       "2      Philadelphia            29675517                14   \n",
       "3      Philadelphia            29675518                 6   \n",
       "4       New York NY            29678563               106   \n",
       "5       New York NY            29678564                98   \n",
       "6       New York NY            29678566                80   \n",
       "7       New York NY            29683435                 8   \n",
       "8       New York NY            29683437                 6   \n",
       "9       New York NY            29683509                40   \n",
       "10      New York NY            29683511                34   \n",
       "11    San Francisco            29686242                14   \n",
       "12    San Francisco            29686245                10   \n",
       "13    San Francisco            29686264                 9   \n",
       "14    San Francisco            29686265                 8   \n",
       "15    San Francisco            29686266                16   \n",
       "16    San Francisco            29686267                24   \n",
       "17    San Francisco            29686268                14   \n",
       "18    San Francisco            29686269                 5   \n",
       "19     Philadelphia            29700187                 7   \n",
       "20     Philadelphia            29715355                 3   \n",
       "21     Philadelphia            29736827                 3   \n",
       "22     Philadelphia            29736829                 8   \n",
       "23     Philadelphia            29736830                10   \n",
       "24     Philadelphia            29736831                14   \n",
       "25     Philadelphia            29736832                 5   \n",
       "26     Philadelphia            29736833                 4   \n",
       "27     Philadelphia            29736834                10   \n",
       "28     Philadelphia            29736836                 6   \n",
       "29     Philadelphia            29736837                 6   \n",
       "...             ...                 ...               ...   \n",
       "1786   Philadelphia            31558951                18   \n",
       "1787   Philadelphia            31558953                12   \n",
       "1788    New York NY            31561934                 7   \n",
       "1789    New York NY            31561942                16   \n",
       "1790    New York NY            31561945               116   \n",
       "1791    New York NY            31561946                28   \n",
       "1792    New York NY            31561947               108   \n",
       "1793    New York NY            31561948                58   \n",
       "1794    New York NY            31561949                66   \n",
       "1795    New York NY            31561950                96   \n",
       "1796    New York NY            31561951                72   \n",
       "1797    New York NY            31561952               120   \n",
       "1798    New York NY            31566792                 8   \n",
       "1799    New York NY            31566794                 7   \n",
       "1800    New York NY            31566795                 8   \n",
       "1801    New York NY            31566798                27   \n",
       "1802    New York NY            31566799                10   \n",
       "1803    New York NY            31566800                11   \n",
       "1804    New York NY            31566801                13   \n",
       "1805    New York NY            31566863                21   \n",
       "1806    New York NY            31566865                93   \n",
       "1807    New York NY            31566866                64   \n",
       "1808    New York NY            31566867                30   \n",
       "1809    New York NY            31566868                32   \n",
       "1810    New York NY            31566873                41   \n",
       "1811  San Francisco            31569605                 8   \n",
       "1812  San Francisco            31569606                 5   \n",
       "1813  San Francisco            31569628                 4   \n",
       "1814  San Francisco            31569629                24   \n",
       "1815  San Francisco            31569914                 5   \n",
       "\n",
       "      no_unique_avail_shoppers  delivery_window_id  \\\n",
       "0                            9            29675515   \n",
       "1                            7            29675516   \n",
       "2                            7            29675517   \n",
       "3                            6            29675518   \n",
       "4                           53            29678563   \n",
       "5                           49            29678564   \n",
       "6                           40            29678566   \n",
       "7                            8            29683435   \n",
       "8                            6            29683437   \n",
       "9                           40            29683509   \n",
       "10                          34            29683511   \n",
       "11                          14            29686242   \n",
       "12                          10            29686245   \n",
       "13                           9            29686264   \n",
       "14                           8            29686265   \n",
       "15                           8            29686266   \n",
       "16                           8            29686267   \n",
       "17                           7            29686268   \n",
       "18                           5            29686269   \n",
       "19                           7            29700187   \n",
       "20                           3            29715355   \n",
       "21                           3            29736827   \n",
       "22                           4            29736829   \n",
       "23                           5            29736830   \n",
       "24                           7            29736831   \n",
       "25                           5            29736832   \n",
       "26                           4            29736833   \n",
       "27                           5            29736834   \n",
       "28                           6            29736836   \n",
       "29                           6            29736837   \n",
       "...                        ...                 ...   \n",
       "1786                         6            31558951   \n",
       "1787                         6            31558953   \n",
       "1788                         7            31561934   \n",
       "1789                        16            31561942   \n",
       "1790                        29            31561945   \n",
       "1791                        28            31561946   \n",
       "1792                        27            31561947   \n",
       "1793                        29            31561948   \n",
       "1794                        33            31561949   \n",
       "1795                        32            31561950   \n",
       "1796                        36            31561951   \n",
       "1797                        40            31561952   \n",
       "1798                         8            31566792   \n",
       "1799                         7            31566794   \n",
       "1800                         8            31566795   \n",
       "1801                         9            31566798   \n",
       "1802                        10            31566799   \n",
       "1803                        11            31566800   \n",
       "1804                        13            31566801   \n",
       "1805                        21            31566863   \n",
       "1806                        31            31566865   \n",
       "1807                        32            31566866   \n",
       "1808                        30            31566867   \n",
       "1809                        32            31566868   \n",
       "1810                        41            31566873   \n",
       "1811                         8            31569605   \n",
       "1812                         5            31569606   \n",
       "1813                         4            31569628   \n",
       "1814                         6            31569629   \n",
       "1815                         5            31569914   \n",
       "\n",
       "      no_unique_abandoned_shoppers  abandoned  delivery_window_id  \\\n",
       "0                                4          1            29675515   \n",
       "1                                5          1            29675516   \n",
       "2                                5          1            29675517   \n",
       "3                                3          1            29675518   \n",
       "4                               17          1            29678563   \n",
       "5                               15          1            29678564   \n",
       "6                               11          1            29678566   \n",
       "7                                1          1            29683435   \n",
       "8                                2          1            29683437   \n",
       "9                               13          1            29683509   \n",
       "10                               8          1            29683511   \n",
       "11                               3          1            29686242   \n",
       "12                               4          1            29686245   \n",
       "13                               3          1            29686264   \n",
       "14                               3          1            29686265   \n",
       "15                               2          1            29686266   \n",
       "16                               2          1            29686267   \n",
       "17                               2          1            29686268   \n",
       "18                               1          1            29686269   \n",
       "19                               2          1            29700187   \n",
       "20                               2          1            29715355   \n",
       "21                               1          1            29736827   \n",
       "22                               2          1            29736829   \n",
       "23                               3          1            29736830   \n",
       "24                               3          1            29736831   \n",
       "25                               2          1            29736832   \n",
       "26                               2          1            29736833   \n",
       "27                               2          1            29736834   \n",
       "28                               3          1            29736836   \n",
       "29                               2          1            29736837   \n",
       "...                            ...        ...                 ...   \n",
       "1786                             1          1            31558951   \n",
       "1787                             1          1            31558953   \n",
       "1788                             1          1            31561934   \n",
       "1789                             5          1            31561942   \n",
       "1790                             9          1            31561945   \n",
       "1791                             9          1            31561946   \n",
       "1792                             7          1            31561947   \n",
       "1793                             9          1            31561948   \n",
       "1794                             9          1            31561949   \n",
       "1795                             6          1            31561950   \n",
       "1796                             7          1            31561951   \n",
       "1797                            12          1            31561952   \n",
       "1798                             1          1            31566792   \n",
       "1799                             1          1            31566794   \n",
       "1800                             1          1            31566795   \n",
       "1801                             2          1            31566798   \n",
       "1802                             3          1            31566799   \n",
       "1803                             3          1            31566800   \n",
       "1804                             3          1            31566801   \n",
       "1805                             7          1            31566863   \n",
       "1806                             9          1            31566865   \n",
       "1807                             9          1            31566866   \n",
       "1808                             8          1            31566867   \n",
       "1809                            10          1            31566868   \n",
       "1810                            10          1            31566873   \n",
       "1811                             3          1            31569605   \n",
       "1812                             1          1            31569606   \n",
       "1813                             1          1            31569628   \n",
       "1814                             2          1            31569629   \n",
       "1815                             1          1            31569914   \n",
       "\n",
       "      no_unique_fulfilled_shoppers  abandoned  \n",
       "0                                5          0  \n",
       "1                                2          0  \n",
       "2                                2          0  \n",
       "3                                3          0  \n",
       "4                               36          0  \n",
       "5                               34          0  \n",
       "6                               29          0  \n",
       "7                                7          0  \n",
       "8                                4          0  \n",
       "9                               27          0  \n",
       "10                              26          0  \n",
       "11                              11          0  \n",
       "12                               6          0  \n",
       "13                               6          0  \n",
       "14                               5          0  \n",
       "15                               6          0  \n",
       "16                               6          0  \n",
       "17                               5          0  \n",
       "18                               4          0  \n",
       "19                               5          0  \n",
       "20                               1          0  \n",
       "21                               2          0  \n",
       "22                               2          0  \n",
       "23                               2          0  \n",
       "24                               4          0  \n",
       "25                               3          0  \n",
       "26                               2          0  \n",
       "27                               3          0  \n",
       "28                               3          0  \n",
       "29                               4          0  \n",
       "...                            ...        ...  \n",
       "1786                             5          0  \n",
       "1787                             5          0  \n",
       "1788                             6          0  \n",
       "1789                            11          0  \n",
       "1790                            20          0  \n",
       "1791                            19          0  \n",
       "1792                            20          0  \n",
       "1793                            20          0  \n",
       "1794                            24          0  \n",
       "1795                            26          0  \n",
       "1796                            29          0  \n",
       "1797                            28          0  \n",
       "1798                             7          0  \n",
       "1799                             6          0  \n",
       "1800                             7          0  \n",
       "1801                             7          0  \n",
       "1802                             7          0  \n",
       "1803                             8          0  \n",
       "1804                            10          0  \n",
       "1805                            14          0  \n",
       "1806                            22          0  \n",
       "1807                            23          0  \n",
       "1808                            22          0  \n",
       "1809                            22          0  \n",
       "1810                            31          0  \n",
       "1811                             5          0  \n",
       "1812                             4          0  \n",
       "1813                             3          0  \n",
       "1814                             4          0  \n",
       "1815                             4          0  \n",
       "\n",
       "[1816 rows x 10 columns]"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#join three tables on delivery_window_id\n",
    "final_etl  = ps.sqldf(\"select* from part_one po join abandoned a on po.delivery_window_id = a.delivery_window_id join fulfilled f on a.delivery_window_id=f.delivery_window_id order by 2\")\n",
    "final_etl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "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>market_name</th>\n",
       "      <th>delivery_window_id</th>\n",
       "      <th>no_orders_placed</th>\n",
       "      <th>no_unique_avail_shoppers</th>\n",
       "      <th>no_unique_abandoned_shoppers</th>\n",
       "      <th>no_unique_fulfilled_shoppers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29675515</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29675516</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29675517</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29675518</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29678563</td>\n",
       "      <td>106</td>\n",
       "      <td>53</td>\n",
       "      <td>17</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29678564</td>\n",
       "      <td>98</td>\n",
       "      <td>49</td>\n",
       "      <td>15</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29678566</td>\n",
       "      <td>80</td>\n",
       "      <td>40</td>\n",
       "      <td>11</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29683435</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29683437</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29683509</td>\n",
       "      <td>40</td>\n",
       "      <td>40</td>\n",
       "      <td>13</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>29683511</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>8</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686242</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686245</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686264</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686265</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686266</td>\n",
       "      <td>16</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686267</td>\n",
       "      <td>24</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686268</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>29686269</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29700187</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29715355</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736827</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736829</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736830</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736831</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736832</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736833</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736834</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736836</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>29736837</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>4</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1786</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>31558951</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1787</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>31558953</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1788</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561934</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1789</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561942</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1790</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561945</td>\n",
       "      <td>116</td>\n",
       "      <td>29</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1791</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561946</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>9</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1792</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561947</td>\n",
       "      <td>108</td>\n",
       "      <td>27</td>\n",
       "      <td>7</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1793</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561948</td>\n",
       "      <td>58</td>\n",
       "      <td>29</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1794</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561949</td>\n",
       "      <td>66</td>\n",
       "      <td>33</td>\n",
       "      <td>9</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1795</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561950</td>\n",
       "      <td>96</td>\n",
       "      <td>32</td>\n",
       "      <td>6</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1796</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561951</td>\n",
       "      <td>72</td>\n",
       "      <td>36</td>\n",
       "      <td>7</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1797</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31561952</td>\n",
       "      <td>120</td>\n",
       "      <td>40</td>\n",
       "      <td>12</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1798</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566792</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1799</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566794</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1800</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566795</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1801</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566798</td>\n",
       "      <td>27</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1802</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566799</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1803</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566800</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1804</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566801</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1805</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566863</td>\n",
       "      <td>21</td>\n",
       "      <td>21</td>\n",
       "      <td>7</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1806</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566865</td>\n",
       "      <td>93</td>\n",
       "      <td>31</td>\n",
       "      <td>9</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1807</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566866</td>\n",
       "      <td>64</td>\n",
       "      <td>32</td>\n",
       "      <td>9</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1808</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566867</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>8</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1809</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566868</td>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>10</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1810</th>\n",
       "      <td>New York NY</td>\n",
       "      <td>31566873</td>\n",
       "      <td>41</td>\n",
       "      <td>41</td>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1811</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569605</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1812</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569606</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1813</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569628</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1814</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569629</td>\n",
       "      <td>24</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1815</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>31569914</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1816 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        market_name  delivery_window_id  no_orders_placed  \\\n",
       "0      Philadelphia            29675515                 9   \n",
       "1      Philadelphia            29675516                14   \n",
       "2      Philadelphia            29675517                14   \n",
       "3      Philadelphia            29675518                 6   \n",
       "4       New York NY            29678563               106   \n",
       "5       New York NY            29678564                98   \n",
       "6       New York NY            29678566                80   \n",
       "7       New York NY            29683435                 8   \n",
       "8       New York NY            29683437                 6   \n",
       "9       New York NY            29683509                40   \n",
       "10      New York NY            29683511                34   \n",
       "11    San Francisco            29686242                14   \n",
       "12    San Francisco            29686245                10   \n",
       "13    San Francisco            29686264                 9   \n",
       "14    San Francisco            29686265                 8   \n",
       "15    San Francisco            29686266                16   \n",
       "16    San Francisco            29686267                24   \n",
       "17    San Francisco            29686268                14   \n",
       "18    San Francisco            29686269                 5   \n",
       "19     Philadelphia            29700187                 7   \n",
       "20     Philadelphia            29715355                 3   \n",
       "21     Philadelphia            29736827                 3   \n",
       "22     Philadelphia            29736829                 8   \n",
       "23     Philadelphia            29736830                10   \n",
       "24     Philadelphia            29736831                14   \n",
       "25     Philadelphia            29736832                 5   \n",
       "26     Philadelphia            29736833                 4   \n",
       "27     Philadelphia            29736834                10   \n",
       "28     Philadelphia            29736836                 6   \n",
       "29     Philadelphia            29736837                 6   \n",
       "...             ...                 ...               ...   \n",
       "1786   Philadelphia            31558951                18   \n",
       "1787   Philadelphia            31558953                12   \n",
       "1788    New York NY            31561934                 7   \n",
       "1789    New York NY            31561942                16   \n",
       "1790    New York NY            31561945               116   \n",
       "1791    New York NY            31561946                28   \n",
       "1792    New York NY            31561947               108   \n",
       "1793    New York NY            31561948                58   \n",
       "1794    New York NY            31561949                66   \n",
       "1795    New York NY            31561950                96   \n",
       "1796    New York NY            31561951                72   \n",
       "1797    New York NY            31561952               120   \n",
       "1798    New York NY            31566792                 8   \n",
       "1799    New York NY            31566794                 7   \n",
       "1800    New York NY            31566795                 8   \n",
       "1801    New York NY            31566798                27   \n",
       "1802    New York NY            31566799                10   \n",
       "1803    New York NY            31566800                11   \n",
       "1804    New York NY            31566801                13   \n",
       "1805    New York NY            31566863                21   \n",
       "1806    New York NY            31566865                93   \n",
       "1807    New York NY            31566866                64   \n",
       "1808    New York NY            31566867                30   \n",
       "1809    New York NY            31566868                32   \n",
       "1810    New York NY            31566873                41   \n",
       "1811  San Francisco            31569605                 8   \n",
       "1812  San Francisco            31569606                 5   \n",
       "1813  San Francisco            31569628                 4   \n",
       "1814  San Francisco            31569629                24   \n",
       "1815  San Francisco            31569914                 5   \n",
       "\n",
       "      no_unique_avail_shoppers  no_unique_abandoned_shoppers  \\\n",
       "0                            9                             4   \n",
       "1                            7                             5   \n",
       "2                            7                             5   \n",
       "3                            6                             3   \n",
       "4                           53                            17   \n",
       "5                           49                            15   \n",
       "6                           40                            11   \n",
       "7                            8                             1   \n",
       "8                            6                             2   \n",
       "9                           40                            13   \n",
       "10                          34                             8   \n",
       "11                          14                             3   \n",
       "12                          10                             4   \n",
       "13                           9                             3   \n",
       "14                           8                             3   \n",
       "15                           8                             2   \n",
       "16                           8                             2   \n",
       "17                           7                             2   \n",
       "18                           5                             1   \n",
       "19                           7                             2   \n",
       "20                           3                             2   \n",
       "21                           3                             1   \n",
       "22                           4                             2   \n",
       "23                           5                             3   \n",
       "24                           7                             3   \n",
       "25                           5                             2   \n",
       "26                           4                             2   \n",
       "27                           5                             2   \n",
       "28                           6                             3   \n",
       "29                           6                             2   \n",
       "...                        ...                           ...   \n",
       "1786                         6                             1   \n",
       "1787                         6                             1   \n",
       "1788                         7                             1   \n",
       "1789                        16                             5   \n",
       "1790                        29                             9   \n",
       "1791                        28                             9   \n",
       "1792                        27                             7   \n",
       "1793                        29                             9   \n",
       "1794                        33                             9   \n",
       "1795                        32                             6   \n",
       "1796                        36                             7   \n",
       "1797                        40                            12   \n",
       "1798                         8                             1   \n",
       "1799                         7                             1   \n",
       "1800                         8                             1   \n",
       "1801                         9                             2   \n",
       "1802                        10                             3   \n",
       "1803                        11                             3   \n",
       "1804                        13                             3   \n",
       "1805                        21                             7   \n",
       "1806                        31                             9   \n",
       "1807                        32                             9   \n",
       "1808                        30                             8   \n",
       "1809                        32                            10   \n",
       "1810                        41                            10   \n",
       "1811                         8                             3   \n",
       "1812                         5                             1   \n",
       "1813                         4                             1   \n",
       "1814                         6                             2   \n",
       "1815                         5                             1   \n",
       "\n",
       "      no_unique_fulfilled_shoppers  \n",
       "0                                5  \n",
       "1                                2  \n",
       "2                                2  \n",
       "3                                3  \n",
       "4                               36  \n",
       "5                               34  \n",
       "6                               29  \n",
       "7                                7  \n",
       "8                                4  \n",
       "9                               27  \n",
       "10                              26  \n",
       "11                              11  \n",
       "12                               6  \n",
       "13                               6  \n",
       "14                               5  \n",
       "15                               6  \n",
       "16                               6  \n",
       "17                               5  \n",
       "18                               4  \n",
       "19                               5  \n",
       "20                               1  \n",
       "21                               2  \n",
       "22                               2  \n",
       "23                               2  \n",
       "24                               4  \n",
       "25                               3  \n",
       "26                               2  \n",
       "27                               3  \n",
       "28                               3  \n",
       "29                               4  \n",
       "...                            ...  \n",
       "1786                             5  \n",
       "1787                             5  \n",
       "1788                             6  \n",
       "1789                            11  \n",
       "1790                            20  \n",
       "1791                            19  \n",
       "1792                            20  \n",
       "1793                            20  \n",
       "1794                            24  \n",
       "1795                            26  \n",
       "1796                            29  \n",
       "1797                            28  \n",
       "1798                             7  \n",
       "1799                             6  \n",
       "1800                             7  \n",
       "1801                             7  \n",
       "1802                             7  \n",
       "1803                             8  \n",
       "1804                            10  \n",
       "1805                            14  \n",
       "1806                            22  \n",
       "1807                            23  \n",
       "1808                            22  \n",
       "1809                            22  \n",
       "1810                            31  \n",
       "1811                             5  \n",
       "1812                             4  \n",
       "1813                             3  \n",
       "1814                             4  \n",
       "1815                             4  \n",
       "\n",
       "[1816 rows x 6 columns]"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_etl = ps.sqldf(\"select market_name, delivery_window_id, no_orders_placed, no_unique_avail_shoppers, no_unique_abandoned_shoppers, no_unique_fulfilled_shoppers from final_etl\")\n",
    "final_etl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_etl.to_csv(\"etl.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}