2017_outbound shipping.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies\n",
    "import pandas as pd\n",
    "import random\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "from scipy.stats import sem\n",
    "import os\n",
    "from pathlib import Path\n",
    "import requests\n",
    "import json\n",
    "from scipy.stats import linregress, pearsonr\n",
    "import scipy.stats as st\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#load files\n",
    "outbound_shipments_2017 = os.path.join(\".\", \"resources\", \"outbound_shipments_2017.csv\")\n",
    "period_num = os.path.join(\".\", \"resources\", \"period_numbers_2016to2019.csv\")\n",
    "states = os.path.join(\".\", \"resources\", \"states.csv\")\n",
    "\n",
    "#read data\n",
    "outbound_shipments_2017_df = pd.read_csv(outbound_shipments_2017)\n",
    "period_num_df = pd.read_csv(period_num)\n",
    "states_df = pd.read_csv(states)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Navision Order ID</th>\n",
       "      <th>Navision Created Date</th>\n",
       "      <th>Channel</th>\n",
       "      <th>Channel Order ID</th>\n",
       "      <th>Shipping Agent</th>\n",
       "      <th>Tracking No</th>\n",
       "      <th>Order Status</th>\n",
       "      <th>Status Summary</th>\n",
       "      <th>Shipping City</th>\n",
       "      <th>Shipping State</th>\n",
       "      <th>Shipped Date</th>\n",
       "      <th>Delivered On</th>\n",
       "      <th>Status On</th>\n",
       "      <th>Delivered</th>\n",
       "      <th>period_number</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SYMSP151085</td>\n",
       "      <td>12/20/16 16:43</td>\n",
       "      <td>Amazon.com, LLC</td>\n",
       "      <td>6B94AE4Z</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564517881869</td>\n",
       "      <td>Canceled</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>seattle</td>\n",
       "      <td>WA</td>\n",
       "      <td>1/3/17</td>\n",
       "      <td>1/5/17</td>\n",
       "      <td>1/5/17 10:35</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SYMSP182977</td>\n",
       "      <td>8/4/17 14:32</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>31667297</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564004417458</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>BROADVIEW HEIGHTS</td>\n",
       "      <td>OH</td>\n",
       "      <td>8/4/17</td>\n",
       "      <td>8/7/17</td>\n",
       "      <td>8/7/17 10:08</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SYMSP173756</td>\n",
       "      <td>5/22/17 18:40</td>\n",
       "      <td>Amazon.com, LLC</td>\n",
       "      <td>5KKXU2CG</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>725534178509</td>\n",
       "      <td>Partial Shipment</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>Sumner</td>\n",
       "      <td>WA</td>\n",
       "      <td>5/23/17</td>\n",
       "      <td>5/30/17</td>\n",
       "      <td>5/30/17 8:45</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SYMSP153151</td>\n",
       "      <td>1/4/17 21:08</td>\n",
       "      <td>Dixie Adoptables</td>\n",
       "      <td>10110336</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>674625642641</td>\n",
       "      <td>Partial Shipment</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>LUCEDALE</td>\n",
       "      <td>MS</td>\n",
       "      <td>1/5/17</td>\n",
       "      <td>1/9/17</td>\n",
       "      <td>1/9/17 11:45</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SYMSP173756</td>\n",
       "      <td>5/22/17 18:40</td>\n",
       "      <td>Amazon.com, LLC</td>\n",
       "      <td>5KKXU2CG</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>725534178510</td>\n",
       "      <td>Partial Shipment</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>Sumner</td>\n",
       "      <td>WA</td>\n",
       "      <td>5/23/17</td>\n",
       "      <td>5/30/17</td>\n",
       "      <td>5/30/17 8:45</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61175</th>\n",
       "      <td>SYMSP154474</td>\n",
       "      <td>1/13/17 8:30</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111371</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564992473429</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Lakemoor</td>\n",
       "      <td>IL</td>\n",
       "      <td>1/13/17</td>\n",
       "      <td>1/17/17</td>\n",
       "      <td>1/17/17 14:13</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61176</th>\n",
       "      <td>SYMSP154493</td>\n",
       "      <td>1/13/17 11:00</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111381</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564999393188</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>OH</td>\n",
       "      <td>1/13/17</td>\n",
       "      <td>1/17/17</td>\n",
       "      <td>1/17/17 17:38</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61177</th>\n",
       "      <td>SYMSP154495</td>\n",
       "      <td>1/13/17 11:00</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111383</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564999395779</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Johnston</td>\n",
       "      <td>RI</td>\n",
       "      <td>1/13/17</td>\n",
       "      <td>1/17/17</td>\n",
       "      <td>1/17/17 10:58</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61178</th>\n",
       "      <td>SYMSP154442</td>\n",
       "      <td>1/12/17 22:30</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111349</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>674625645342</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>Wheat Ridge</td>\n",
       "      <td>CO</td>\n",
       "      <td>1/13/17</td>\n",
       "      <td>1/17/17</td>\n",
       "      <td>1/17/17 11:31</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61179</th>\n",
       "      <td>SYMSP154477</td>\n",
       "      <td>1/13/17 8:30</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111374</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564992472415</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Manteca</td>\n",
       "      <td>CA</td>\n",
       "      <td>1/13/17</td>\n",
       "      <td>1/18/17</td>\n",
       "      <td>1/18/17 17:44</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61180 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Navision Order ID Navision Created Date           Channel  \\\n",
       "0           SYMSP151085        12/20/16 16:43   Amazon.com, LLC   \n",
       "1           SYMSP182977          8/4/17 14:32   ROYAL CANIN USA   \n",
       "2           SYMSP173756         5/22/17 18:40   Amazon.com, LLC   \n",
       "3           SYMSP153151          1/4/17 21:08  Dixie Adoptables   \n",
       "4           SYMSP173756         5/22/17 18:40   Amazon.com, LLC   \n",
       "...                 ...                   ...               ...   \n",
       "61175       SYMSP154474          1/13/17 8:30   WisdomPanel.com   \n",
       "61176       SYMSP154493         1/13/17 11:00   WisdomPanel.com   \n",
       "61177       SYMSP154495         1/13/17 11:00   WisdomPanel.com   \n",
       "61178       SYMSP154442         1/12/17 22:30   WisdomPanel.com   \n",
       "61179       SYMSP154477          1/13/17 8:30   WisdomPanel.com   \n",
       "\n",
       "      Channel Order ID Shipping Agent             Tracking No  \\\n",
       "0             6B94AE4Z           USPS  9400111899564517881869   \n",
       "1             31667297           USPS  9400111899564004417458   \n",
       "2             5KKXU2CG          FEDEX            725534178509   \n",
       "3             10110336          FEDEX            674625642641   \n",
       "4             5KKXU2CG          FEDEX            725534178510   \n",
       "...                ...            ...                     ...   \n",
       "61175         10111371           USPS  9400111899564992473429   \n",
       "61176         10111381           USPS  9400111899564999393188   \n",
       "61177         10111383           USPS  9400111899564999395779   \n",
       "61178         10111349          FEDEX            674625645342   \n",
       "61179         10111374           USPS  9400111899564992472415   \n",
       "\n",
       "           Order Status            Status Summary      Shipping City  \\\n",
       "0              Canceled  Delivered, In/At Mailbox            seattle   \n",
       "1                -NONE-  Delivered, In/At Mailbox  BROADVIEW HEIGHTS   \n",
       "2      Partial Shipment                 Delivered             Sumner   \n",
       "3      Partial Shipment                 Delivered           LUCEDALE   \n",
       "4      Partial Shipment                 Delivered             Sumner   \n",
       "...                 ...                       ...                ...   \n",
       "61175           Shipped  Delivered, In/At Mailbox           Lakemoor   \n",
       "61176           Shipped  Delivered, In/At Mailbox           Columbus   \n",
       "61177           Shipped  Delivered, In/At Mailbox           Johnston   \n",
       "61178           Shipped                 Delivered        Wheat Ridge   \n",
       "61179           Shipped  Delivered, In/At Mailbox            Manteca   \n",
       "\n",
       "      Shipping State Shipped Date Delivered On      Status On  Delivered  \\\n",
       "0                 WA       1/3/17       1/5/17   1/5/17 10:35        1.0   \n",
       "1                 OH       8/4/17       8/7/17   8/7/17 10:08        1.0   \n",
       "2                 WA      5/23/17      5/30/17   5/30/17 8:45        1.0   \n",
       "3                 MS       1/5/17       1/9/17   1/9/17 11:45        1.0   \n",
       "4                 WA      5/23/17      5/30/17   5/30/17 8:45        1.0   \n",
       "...              ...          ...          ...            ...        ...   \n",
       "61175             IL      1/13/17      1/17/17  1/17/17 14:13        1.0   \n",
       "61176             OH      1/13/17      1/17/17  1/17/17 17:38        1.0   \n",
       "61177             RI      1/13/17      1/17/17  1/17/17 10:58        1.0   \n",
       "61178             CO      1/13/17      1/17/17  1/17/17 11:31        1.0   \n",
       "61179             CA      1/13/17      1/18/17  1/18/17 17:44        1.0   \n",
       "\n",
       "       period_number country  \n",
       "0                  1      US  \n",
       "1                  8      US  \n",
       "2                  6      US  \n",
       "3                  1      US  \n",
       "4                  6      US  \n",
       "...              ...     ...  \n",
       "61175              1      US  \n",
       "61176              1      US  \n",
       "61177              1      US  \n",
       "61178              1      US  \n",
       "61179              1      US  \n",
       "\n",
       "[61180 rows x 16 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#####2017 DATASET\n",
    "\n",
    "#merge outbound_shipments_2018 and period_num_df\n",
    "combined_2017_outbound_period_df = pd.merge(\n",
    "    outbound_shipments_2017_df,\n",
    "    period_num_df[[\"date\", \"period_number\"]],  \n",
    "    left_on=\"Shipped Date\",\n",
    "    right_on=\"date\",\n",
    "    how=\"left\").drop(columns=\"date\")\n",
    "\n",
    "#combined_outbound_period_df with states_df\n",
    "combined_2017_dataset_df = pd.merge(\n",
    "    combined_2017_outbound_period_df,\n",
    "    states_df[[\"state_abr\", \"country\"]],  \n",
    "    left_on=\"Shipping State\",\n",
    "    right_on=\"state_abr\",\n",
    "    how=\"left\").drop(columns=\"state_abr\")\n",
    "\n",
    "combined_2017_dataset_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Navision Order ID         object\n",
       "Navision Created Date     object\n",
       "Channel                   object\n",
       "Channel Order ID          object\n",
       "Shipping Agent            object\n",
       "Tracking No               object\n",
       "Order Status              object\n",
       "Status Summary            object\n",
       "Shipping City             object\n",
       "Shipping State            object\n",
       "Shipped Date              object\n",
       "Delivered On              object\n",
       "Status On                 object\n",
       "Delivered                float64\n",
       "period_number              int64\n",
       "country                   object\n",
       "dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "combined_2017_dataset_df.dtypes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Navision Order ID</th>\n",
       "      <th>Navision Created Date</th>\n",
       "      <th>Channel</th>\n",
       "      <th>Channel Order ID</th>\n",
       "      <th>Shipping Agent</th>\n",
       "      <th>Tracking No</th>\n",
       "      <th>Order Status</th>\n",
       "      <th>Status Summary</th>\n",
       "      <th>Shipping City</th>\n",
       "      <th>Shipping State</th>\n",
       "      <th>Shipped Date</th>\n",
       "      <th>Delivered On</th>\n",
       "      <th>Status On</th>\n",
       "      <th>Delivered</th>\n",
       "      <th>period_number</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SYMSP151085</td>\n",
       "      <td>12/20/16 16:43</td>\n",
       "      <td>Amazon.com, LLC</td>\n",
       "      <td>6B94AE4Z</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564517881869</td>\n",
       "      <td>Canceled</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>seattle</td>\n",
       "      <td>WA</td>\n",
       "      <td>1/3/17</td>\n",
       "      <td>1/5/17</td>\n",
       "      <td>1/5/17 10:35</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SYMSP182977</td>\n",
       "      <td>8/4/17 14:32</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>31667297</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564004417458</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>BROADVIEW HEIGHTS</td>\n",
       "      <td>OH</td>\n",
       "      <td>8/4/17</td>\n",
       "      <td>8/7/17</td>\n",
       "      <td>8/7/17 10:08</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SYMSP173756</td>\n",
       "      <td>5/22/17 18:40</td>\n",
       "      <td>Amazon.com, LLC</td>\n",
       "      <td>5KKXU2CG</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>725534178509</td>\n",
       "      <td>Partial Shipment</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>Sumner</td>\n",
       "      <td>WA</td>\n",
       "      <td>5/23/17</td>\n",
       "      <td>5/30/17</td>\n",
       "      <td>5/30/17 8:45</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SYMSP153151</td>\n",
       "      <td>1/4/17 21:08</td>\n",
       "      <td>Dixie Adoptables</td>\n",
       "      <td>10110336</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>674625642641</td>\n",
       "      <td>Partial Shipment</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>LUCEDALE</td>\n",
       "      <td>MS</td>\n",
       "      <td>1/5/17</td>\n",
       "      <td>1/9/17</td>\n",
       "      <td>1/9/17 11:45</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SYMSP173756</td>\n",
       "      <td>5/22/17 18:40</td>\n",
       "      <td>Amazon.com, LLC</td>\n",
       "      <td>5KKXU2CG</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>725534178510</td>\n",
       "      <td>Partial Shipment</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>Sumner</td>\n",
       "      <td>WA</td>\n",
       "      <td>5/23/17</td>\n",
       "      <td>5/30/17</td>\n",
       "      <td>5/30/17 8:45</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61175</th>\n",
       "      <td>SYMSP154474</td>\n",
       "      <td>1/13/17 8:30</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111371</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564992473429</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Lakemoor</td>\n",
       "      <td>IL</td>\n",
       "      <td>1/13/17</td>\n",
       "      <td>1/17/17</td>\n",
       "      <td>1/17/17 14:13</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61176</th>\n",
       "      <td>SYMSP154493</td>\n",
       "      <td>1/13/17 11:00</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111381</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564999393188</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>OH</td>\n",
       "      <td>1/13/17</td>\n",
       "      <td>1/17/17</td>\n",
       "      <td>1/17/17 17:38</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61177</th>\n",
       "      <td>SYMSP154495</td>\n",
       "      <td>1/13/17 11:00</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111383</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564999395779</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Johnston</td>\n",
       "      <td>RI</td>\n",
       "      <td>1/13/17</td>\n",
       "      <td>1/17/17</td>\n",
       "      <td>1/17/17 10:58</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61178</th>\n",
       "      <td>SYMSP154442</td>\n",
       "      <td>1/12/17 22:30</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111349</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>674625645342</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>Wheat Ridge</td>\n",
       "      <td>CO</td>\n",
       "      <td>1/13/17</td>\n",
       "      <td>1/17/17</td>\n",
       "      <td>1/17/17 11:31</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61179</th>\n",
       "      <td>SYMSP154477</td>\n",
       "      <td>1/13/17 8:30</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111374</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564992472415</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Manteca</td>\n",
       "      <td>CA</td>\n",
       "      <td>1/13/17</td>\n",
       "      <td>1/18/17</td>\n",
       "      <td>1/18/17 17:44</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61180 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Navision Order ID Navision Created Date           Channel  \\\n",
       "0           SYMSP151085        12/20/16 16:43   Amazon.com, LLC   \n",
       "1           SYMSP182977          8/4/17 14:32   ROYAL CANIN USA   \n",
       "2           SYMSP173756         5/22/17 18:40   Amazon.com, LLC   \n",
       "3           SYMSP153151          1/4/17 21:08  Dixie Adoptables   \n",
       "4           SYMSP173756         5/22/17 18:40   Amazon.com, LLC   \n",
       "...                 ...                   ...               ...   \n",
       "61175       SYMSP154474          1/13/17 8:30   WisdomPanel.com   \n",
       "61176       SYMSP154493         1/13/17 11:00   WisdomPanel.com   \n",
       "61177       SYMSP154495         1/13/17 11:00   WisdomPanel.com   \n",
       "61178       SYMSP154442         1/12/17 22:30   WisdomPanel.com   \n",
       "61179       SYMSP154477          1/13/17 8:30   WisdomPanel.com   \n",
       "\n",
       "      Channel Order ID Shipping Agent             Tracking No  \\\n",
       "0             6B94AE4Z           USPS  9400111899564517881869   \n",
       "1             31667297           USPS  9400111899564004417458   \n",
       "2             5KKXU2CG          FEDEX            725534178509   \n",
       "3             10110336          FEDEX            674625642641   \n",
       "4             5KKXU2CG          FEDEX            725534178510   \n",
       "...                ...            ...                     ...   \n",
       "61175         10111371           USPS  9400111899564992473429   \n",
       "61176         10111381           USPS  9400111899564999393188   \n",
       "61177         10111383           USPS  9400111899564999395779   \n",
       "61178         10111349          FEDEX            674625645342   \n",
       "61179         10111374           USPS  9400111899564992472415   \n",
       "\n",
       "           Order Status            Status Summary      Shipping City  \\\n",
       "0              Canceled  Delivered, In/At Mailbox            seattle   \n",
       "1                -NONE-  Delivered, In/At Mailbox  BROADVIEW HEIGHTS   \n",
       "2      Partial Shipment                 Delivered             Sumner   \n",
       "3      Partial Shipment                 Delivered           LUCEDALE   \n",
       "4      Partial Shipment                 Delivered             Sumner   \n",
       "...                 ...                       ...                ...   \n",
       "61175           Shipped  Delivered, In/At Mailbox           Lakemoor   \n",
       "61176           Shipped  Delivered, In/At Mailbox           Columbus   \n",
       "61177           Shipped  Delivered, In/At Mailbox           Johnston   \n",
       "61178           Shipped                 Delivered        Wheat Ridge   \n",
       "61179           Shipped  Delivered, In/At Mailbox            Manteca   \n",
       "\n",
       "      Shipping State Shipped Date Delivered On      Status On  Delivered  \\\n",
       "0                 WA       1/3/17       1/5/17   1/5/17 10:35        1.0   \n",
       "1                 OH       8/4/17       8/7/17   8/7/17 10:08        1.0   \n",
       "2                 WA      5/23/17      5/30/17   5/30/17 8:45        1.0   \n",
       "3                 MS       1/5/17       1/9/17   1/9/17 11:45        1.0   \n",
       "4                 WA      5/23/17      5/30/17   5/30/17 8:45        1.0   \n",
       "...              ...          ...          ...            ...        ...   \n",
       "61175             IL      1/13/17      1/17/17  1/17/17 14:13        1.0   \n",
       "61176             OH      1/13/17      1/17/17  1/17/17 17:38        1.0   \n",
       "61177             RI      1/13/17      1/17/17  1/17/17 10:58        1.0   \n",
       "61178             CO      1/13/17      1/17/17  1/17/17 11:31        1.0   \n",
       "61179             CA      1/13/17      1/18/17  1/18/17 17:44        1.0   \n",
       "\n",
       "       period_number country  \n",
       "0                  1      US  \n",
       "1                  8      US  \n",
       "2                  6      US  \n",
       "3                  1      US  \n",
       "4                  6      US  \n",
       "...              ...     ...  \n",
       "61175              1      US  \n",
       "61176              1      US  \n",
       "61177              1      US  \n",
       "61178              1      US  \n",
       "61179              1      US  \n",
       "\n",
       "[61180 rows x 16 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_2017_dataset_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/0v/_cwy54bd71x52xsydh3mz7nh0000gn/T/ipykernel_80669/2308186661.py:7: UserWarning: Could not infer format, so each element will be parsed individually, falling back to `dateutil`. To ensure parsing is consistent and as-expected, please specify a format.\n",
      "  combined_2017_dataset_df[\"Shipped Date\"] = pd.to_datetime(combined_2017_dataset_df[\"Shipped Date\"], errors=\"coerce\")\n",
      "/var/folders/0v/_cwy54bd71x52xsydh3mz7nh0000gn/T/ipykernel_80669/2308186661.py:8: UserWarning: Could not infer format, so each element will be parsed individually, falling back to `dateutil`. To ensure parsing is consistent and as-expected, please specify a format.\n",
      "  combined_2017_dataset_df[\"Delivered On\"] = pd.to_datetime(combined_2017_dataset_df[\"Delivered On\"], errors=\"coerce\")\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Navision Order ID</th>\n",
       "      <th>Navision Created Date</th>\n",
       "      <th>Channel</th>\n",
       "      <th>Channel Order ID</th>\n",
       "      <th>Shipping Agent</th>\n",
       "      <th>Tracking No</th>\n",
       "      <th>Order Status</th>\n",
       "      <th>Status Summary</th>\n",
       "      <th>Shipping City</th>\n",
       "      <th>Shipping State</th>\n",
       "      <th>Shipped Date</th>\n",
       "      <th>Delivered On</th>\n",
       "      <th>Status On</th>\n",
       "      <th>Delivered</th>\n",
       "      <th>period_number</th>\n",
       "      <th>country</th>\n",
       "      <th>transit_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SYMSP151085</td>\n",
       "      <td>12/20/16 16:43</td>\n",
       "      <td>Amazon.com, LLC</td>\n",
       "      <td>6B94AE4Z</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564517881869</td>\n",
       "      <td>Canceled</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>seattle</td>\n",
       "      <td>WA</td>\n",
       "      <td>2017-01-03</td>\n",
       "      <td>2017-01-05</td>\n",
       "      <td>1/5/17 10:35</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SYMSP182977</td>\n",
       "      <td>8/4/17 14:32</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>31667297</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564004417458</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>BROADVIEW HEIGHTS</td>\n",
       "      <td>OH</td>\n",
       "      <td>2017-08-04</td>\n",
       "      <td>2017-08-07</td>\n",
       "      <td>8/7/17 10:08</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8</td>\n",
       "      <td>US</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SYMSP173756</td>\n",
       "      <td>5/22/17 18:40</td>\n",
       "      <td>Amazon.com, LLC</td>\n",
       "      <td>5KKXU2CG</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>725534178509</td>\n",
       "      <td>Partial Shipment</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>Sumner</td>\n",
       "      <td>WA</td>\n",
       "      <td>2017-05-23</td>\n",
       "      <td>2017-05-30</td>\n",
       "      <td>5/30/17 8:45</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>US</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SYMSP153151</td>\n",
       "      <td>1/4/17 21:08</td>\n",
       "      <td>Dixie Adoptables</td>\n",
       "      <td>10110336</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>674625642641</td>\n",
       "      <td>Partial Shipment</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>LUCEDALE</td>\n",
       "      <td>MS</td>\n",
       "      <td>2017-01-05</td>\n",
       "      <td>2017-01-09</td>\n",
       "      <td>1/9/17 11:45</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SYMSP173756</td>\n",
       "      <td>5/22/17 18:40</td>\n",
       "      <td>Amazon.com, LLC</td>\n",
       "      <td>5KKXU2CG</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>725534178510</td>\n",
       "      <td>Partial Shipment</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>Sumner</td>\n",
       "      <td>WA</td>\n",
       "      <td>2017-05-23</td>\n",
       "      <td>2017-05-30</td>\n",
       "      <td>5/30/17 8:45</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>US</td>\n",
       "      <td>7.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",
       "      <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>61175</th>\n",
       "      <td>SYMSP154474</td>\n",
       "      <td>1/13/17 8:30</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111371</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564992473429</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Lakemoor</td>\n",
       "      <td>IL</td>\n",
       "      <td>2017-01-13</td>\n",
       "      <td>2017-01-17</td>\n",
       "      <td>1/17/17 14:13</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61176</th>\n",
       "      <td>SYMSP154493</td>\n",
       "      <td>1/13/17 11:00</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111381</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564999393188</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>OH</td>\n",
       "      <td>2017-01-13</td>\n",
       "      <td>2017-01-17</td>\n",
       "      <td>1/17/17 17:38</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61177</th>\n",
       "      <td>SYMSP154495</td>\n",
       "      <td>1/13/17 11:00</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111383</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564999395779</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Johnston</td>\n",
       "      <td>RI</td>\n",
       "      <td>2017-01-13</td>\n",
       "      <td>2017-01-17</td>\n",
       "      <td>1/17/17 10:58</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61178</th>\n",
       "      <td>SYMSP154442</td>\n",
       "      <td>1/12/17 22:30</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111349</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>674625645342</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>Wheat Ridge</td>\n",
       "      <td>CO</td>\n",
       "      <td>2017-01-13</td>\n",
       "      <td>2017-01-17</td>\n",
       "      <td>1/17/17 11:31</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61179</th>\n",
       "      <td>SYMSP154477</td>\n",
       "      <td>1/13/17 8:30</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>10111374</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400111899564992472415</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Manteca</td>\n",
       "      <td>CA</td>\n",
       "      <td>2017-01-13</td>\n",
       "      <td>2017-01-18</td>\n",
       "      <td>1/18/17 17:44</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61180 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Navision Order ID Navision Created Date           Channel  \\\n",
       "0           SYMSP151085        12/20/16 16:43   Amazon.com, LLC   \n",
       "1           SYMSP182977          8/4/17 14:32   ROYAL CANIN USA   \n",
       "2           SYMSP173756         5/22/17 18:40   Amazon.com, LLC   \n",
       "3           SYMSP153151          1/4/17 21:08  Dixie Adoptables   \n",
       "4           SYMSP173756         5/22/17 18:40   Amazon.com, LLC   \n",
       "...                 ...                   ...               ...   \n",
       "61175       SYMSP154474          1/13/17 8:30   WisdomPanel.com   \n",
       "61176       SYMSP154493         1/13/17 11:00   WisdomPanel.com   \n",
       "61177       SYMSP154495         1/13/17 11:00   WisdomPanel.com   \n",
       "61178       SYMSP154442         1/12/17 22:30   WisdomPanel.com   \n",
       "61179       SYMSP154477          1/13/17 8:30   WisdomPanel.com   \n",
       "\n",
       "      Channel Order ID Shipping Agent             Tracking No  \\\n",
       "0             6B94AE4Z           USPS  9400111899564517881869   \n",
       "1             31667297           USPS  9400111899564004417458   \n",
       "2             5KKXU2CG          FEDEX            725534178509   \n",
       "3             10110336          FEDEX            674625642641   \n",
       "4             5KKXU2CG          FEDEX            725534178510   \n",
       "...                ...            ...                     ...   \n",
       "61175         10111371           USPS  9400111899564992473429   \n",
       "61176         10111381           USPS  9400111899564999393188   \n",
       "61177         10111383           USPS  9400111899564999395779   \n",
       "61178         10111349          FEDEX            674625645342   \n",
       "61179         10111374           USPS  9400111899564992472415   \n",
       "\n",
       "           Order Status            Status Summary      Shipping City  \\\n",
       "0              Canceled  Delivered, In/At Mailbox            seattle   \n",
       "1                -NONE-  Delivered, In/At Mailbox  BROADVIEW HEIGHTS   \n",
       "2      Partial Shipment                 Delivered             Sumner   \n",
       "3      Partial Shipment                 Delivered           LUCEDALE   \n",
       "4      Partial Shipment                 Delivered             Sumner   \n",
       "...                 ...                       ...                ...   \n",
       "61175           Shipped  Delivered, In/At Mailbox           Lakemoor   \n",
       "61176           Shipped  Delivered, In/At Mailbox           Columbus   \n",
       "61177           Shipped  Delivered, In/At Mailbox           Johnston   \n",
       "61178           Shipped                 Delivered        Wheat Ridge   \n",
       "61179           Shipped  Delivered, In/At Mailbox            Manteca   \n",
       "\n",
       "      Shipping State Shipped Date Delivered On      Status On  Delivered  \\\n",
       "0                 WA   2017-01-03   2017-01-05   1/5/17 10:35        1.0   \n",
       "1                 OH   2017-08-04   2017-08-07   8/7/17 10:08        1.0   \n",
       "2                 WA   2017-05-23   2017-05-30   5/30/17 8:45        1.0   \n",
       "3                 MS   2017-01-05   2017-01-09   1/9/17 11:45        1.0   \n",
       "4                 WA   2017-05-23   2017-05-30   5/30/17 8:45        1.0   \n",
       "...              ...          ...          ...            ...        ...   \n",
       "61175             IL   2017-01-13   2017-01-17  1/17/17 14:13        1.0   \n",
       "61176             OH   2017-01-13   2017-01-17  1/17/17 17:38        1.0   \n",
       "61177             RI   2017-01-13   2017-01-17  1/17/17 10:58        1.0   \n",
       "61178             CO   2017-01-13   2017-01-17  1/17/17 11:31        1.0   \n",
       "61179             CA   2017-01-13   2017-01-18  1/18/17 17:44        1.0   \n",
       "\n",
       "       period_number country  transit_time  \n",
       "0                  1      US           2.0  \n",
       "1                  8      US           3.0  \n",
       "2                  6      US           7.0  \n",
       "3                  1      US           4.0  \n",
       "4                  6      US           7.0  \n",
       "...              ...     ...           ...  \n",
       "61175              1      US           4.0  \n",
       "61176              1      US           4.0  \n",
       "61177              1      US           4.0  \n",
       "61178              1      US           4.0  \n",
       "61179              1      US           5.0  \n",
       "\n",
       "[61180 rows x 17 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#calcualte transit time for 2017 \n",
    "\n",
    "\n",
    "#####2017 DATASET\n",
    "\n",
    "#update datetime format, errors=\"coerce\" replaces error values with NaT\n",
    "combined_2017_dataset_df[\"Shipped Date\"] = pd.to_datetime(combined_2017_dataset_df[\"Shipped Date\"], errors=\"coerce\")\n",
    "combined_2017_dataset_df[\"Delivered On\"] = pd.to_datetime(combined_2017_dataset_df[\"Delivered On\"], errors=\"coerce\")\n",
    "\n",
    "\n",
    "#create \"transit_time\" column and calculate transit time \n",
    "combined_2017_dataset_df[\"transit_time\"] = combined_2017_dataset_df.apply(\n",
    "    lambda row: (row[\"Delivered On\"] - row[\"Shipped Date\"]).days if pd.notnull(row[\"Delivered On\"]) and pd.notnull(row[\"Shipped Date\"]) else None,\n",
    "    axis=1)\n",
    "\n",
    "#display df\n",
    "combined_2017_dataset_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Shipping Agent</th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>period_number</th>\n",
       "      <th>avg_2017_transit_time</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2.62</td>\n",
       "      <td>3680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>2</td>\n",
       "      <td>2.62</td>\n",
       "      <td>4138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>2.70</td>\n",
       "      <td>3288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>2.56</td>\n",
       "      <td>3029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>5</td>\n",
       "      <td>2.62</td>\n",
       "      <td>3482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>2.72</td>\n",
       "      <td>3335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>7</td>\n",
       "      <td>2.61</td>\n",
       "      <td>2422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>8</td>\n",
       "      <td>2.71</td>\n",
       "      <td>2711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>2.87</td>\n",
       "      <td>4122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>2.77</td>\n",
       "      <td>2824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>2.88</td>\n",
       "      <td>3172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>3.17</td>\n",
       "      <td>6237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>13</td>\n",
       "      <td>3.00</td>\n",
       "      <td>6019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Shipping Agent country  year  period_number  avg_2017_transit_time  count\n",
       "0            USPS      US  2017              1                   2.62   3680\n",
       "1            USPS      US  2017              2                   2.62   4138\n",
       "2            USPS      US  2017              3                   2.70   3288\n",
       "3            USPS      US  2017              4                   2.56   3029\n",
       "4            USPS      US  2017              5                   2.62   3482\n",
       "5            USPS      US  2017              6                   2.72   3335\n",
       "6            USPS      US  2017              7                   2.61   2422\n",
       "7            USPS      US  2017              8                   2.71   2711\n",
       "8            USPS      US  2017              9                   2.87   4122\n",
       "9            USPS      US  2017             10                   2.77   2824\n",
       "10           USPS      US  2017             11                   2.88   3172\n",
       "11           USPS      US  2017             12                   3.17   6237\n",
       "12           USPS      US  2017             13                   3.00   6019"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#display transit times by shipping agent, country, period_number, count \n",
    "\n",
    "\n",
    "#####2017 DATASET\n",
    "\n",
    "#create new column \"year\" and adds  year based on \"Shipped Date\"\n",
    "combined_2017_dataset_df[\"year\"] = combined_2017_dataset_df[\"Shipped Date\"].dt.year\n",
    "\n",
    "# #filter shipping agents: FedEx and USPS\n",
    "# filtered_df = combined_2017_dataset_df[\n",
    "#     (combined_2017_dataset_df[\"Shipping Agent\"].isin([\"USPS\", \"FEDEX\"])) &\n",
    "#     (combined_2017_dataset_df[\"country\"] == \"US\")]\n",
    "\n",
    "#filter shipping agents: USPS and in the \"country\" = \"US\"\n",
    "filtered_2017_df = combined_2017_dataset_df[\n",
    "    (combined_2017_dataset_df[\"Shipping Agent\"].isin([\"USPS\"])) &\n",
    "    (combined_2017_dataset_df[\"country\"] == \"US\")]\n",
    "\n",
    "#group by \"Shipping Agent\", \"country\" ,\"year\", \"period_number\" \n",
    "transit_time_2017_stats_df = filtered_2017_df.groupby([\"Shipping Agent\", \"country\" ,\"year\", \"period_number\"]).agg(\n",
    "    #calculate the avg_transit_time \n",
    "    avg_2017_transit_time=(\"transit_time\", \"mean\"), \n",
    "    #copunt the number of times transit_time calculated \n",
    "    count=(\"transit_time\", \"size\")).round(2).reset_index()\n",
    "\n",
    "\n",
    "transit_time_2017_stats_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export the df to csv\n",
    "transit_time_2017_stats_df.to_csv(\"output/usps_transit_time_2017.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Shipping Agent</th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>period_number</th>\n",
       "      <th>avg_2018_transit_time</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2.80</td>\n",
       "      <td>704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>2</td>\n",
       "      <td>2.50</td>\n",
       "      <td>867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>2.90</td>\n",
       "      <td>828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>2.69</td>\n",
       "      <td>737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>5</td>\n",
       "      <td>2.50</td>\n",
       "      <td>902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>2.73</td>\n",
       "      <td>776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>7</td>\n",
       "      <td>2.74</td>\n",
       "      <td>665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>8</td>\n",
       "      <td>2.73</td>\n",
       "      <td>960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>2.63</td>\n",
       "      <td>839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>2.64</td>\n",
       "      <td>777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>2.53</td>\n",
       "      <td>696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>3.30</td>\n",
       "      <td>1342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2017</td>\n",
       "      <td>13</td>\n",
       "      <td>2.40</td>\n",
       "      <td>1561</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Shipping Agent country  year  period_number  avg_2018_transit_time  count\n",
       "0           FEDEX      US  2017              1                   2.80    704\n",
       "1           FEDEX      US  2017              2                   2.50    867\n",
       "2           FEDEX      US  2017              3                   2.90    828\n",
       "3           FEDEX      US  2017              4                   2.69    737\n",
       "4           FEDEX      US  2017              5                   2.50    902\n",
       "5           FEDEX      US  2017              6                   2.73    776\n",
       "6           FEDEX      US  2017              7                   2.74    665\n",
       "7           FEDEX      US  2017              8                   2.73    960\n",
       "8           FEDEX      US  2017              9                   2.63    839\n",
       "9           FEDEX      US  2017             10                   2.64    777\n",
       "10          FEDEX      US  2017             11                   2.53    696\n",
       "11          FEDEX      US  2017             12                   3.30   1342\n",
       "12          FEDEX      US  2017             13                   2.40   1561"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#display transit times by shipping agent, country, period_number, count \n",
    "\n",
    "\n",
    "#####2018 DATASET\n",
    "\n",
    "#create new column \"year\" and adds  year based on \"Shipped Date\"\n",
    "combined_2017_dataset_df[\"year\"] = combined_2017_dataset_df[\"Shipped Date\"].dt.year\n",
    "\n",
    "# #filter shipping agents: FedEx and USPS\n",
    "# filtered_df = combined_2017_dataset_df[\n",
    "#     (combined_2017_dataset_df[\"Shipping Agent\"].isin([\"USPS\", \"FEDEX\"])) &\n",
    "#     (combined_2017_dataset_df[\"country\"] == \"US\")]\n",
    "\n",
    "#filter shipping agents: USPS and in the \"country\" = \"US\"\n",
    "filtered_2017_df = combined_2017_dataset_df[\n",
    "    (combined_2017_dataset_df[\"Shipping Agent\"].isin([\"FEDEX\"])) &\n",
    "    (combined_2017_dataset_df[\"country\"] == \"US\")]\n",
    "\n",
    "#group by \"Shipping Agent\", \"country\" ,\"year\", \"period_number\" \n",
    "fedex_transit_time_2018_stats = filtered_2017_df.groupby([\"Shipping Agent\", \"country\" ,\"year\", \"period_number\"]).agg(\n",
    "    #calculate the avg_transit_time \n",
    "    avg_2018_transit_time=(\"transit_time\", \"mean\"), \n",
    "    #copunt the number of times transit_time calculated \n",
    "    count=(\"transit_time\", \"size\")).round(2).reset_index()\n",
    "\n",
    "\n",
    "fedex_transit_time_2018_stats"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}