2018_outbound_shipping.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "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_2018 = os.path.join(\".\", \"resources\", \"outbound_shipments_2018.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_2018_df = pd.read_csv(outbound_shipments_2018)\n",
    "period_num_df = pd.read_csv(period_num)\n",
    "states_df = pd.read_csv(states)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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</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>SYMSP211039</td>\n",
       "      <td>1/1/18 0:00</td>\n",
       "      <td>ROYAL</td>\n",
       "      <td>32107548</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>9.74468E+14</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HOLBROOK</td>\n",
       "      <td>NY</td>\n",
       "      <td>1/2/18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SYMSP211039</td>\n",
       "      <td>1/1/18 21:05</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>32107548</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>9.74468E+14</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HOLBROOK</td>\n",
       "      <td>NY</td>\n",
       "      <td>1/2/18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SYMSP211132</td>\n",
       "      <td>1/2/18 17:15</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>32109579</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>9.74468E+14</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NEW BRAUNFELS</td>\n",
       "      <td>TX</td>\n",
       "      <td>1/2/18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SYMSP211122</td>\n",
       "      <td>1/2/18 16:12</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>32109423</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>9.74468E+14</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Frisco</td>\n",
       "      <td>CO</td>\n",
       "      <td>1/2/18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SYMSP211112</td>\n",
       "      <td>1/2/18 14:09</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>32108343</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>9.74468E+14</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DAHLONEGA</td>\n",
       "      <td>GA</td>\n",
       "      <td>1/2/18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>67595</th>\n",
       "      <td>SYMSP276872</td>\n",
       "      <td>12/28/18 17:14</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>35136117</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>4.41128E+11</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>CHALFONT</td>\n",
       "      <td>PA</td>\n",
       "      <td>12/28/18</td>\n",
       "      <td>12/31/18</td>\n",
       "      <td>12/31/18 0:00</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67596</th>\n",
       "      <td>SYMSP276873</td>\n",
       "      <td>12/28/18 17:14</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>35136337</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>4.41128E+11</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>COCOA</td>\n",
       "      <td>FL</td>\n",
       "      <td>12/28/18</td>\n",
       "      <td>1/2/19</td>\n",
       "      <td>1/2/19 0:00</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67597</th>\n",
       "      <td>SYMSP276874</td>\n",
       "      <td>12/28/18 17:14</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>14082922</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>4.41128E+11</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>IRVINE</td>\n",
       "      <td>CA</td>\n",
       "      <td>12/28/18</td>\n",
       "      <td>1/4/19</td>\n",
       "      <td>1/4/19 0:00</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67598</th>\n",
       "      <td>SYMSP276871</td>\n",
       "      <td>12/28/18 17:14</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>35134300</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>4.41128E+11</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>ASBURY PARK</td>\n",
       "      <td>NJ</td>\n",
       "      <td>12/28/18</td>\n",
       "      <td>1/2/19</td>\n",
       "      <td>1/2/19 0:00</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67599</th>\n",
       "      <td>SYMSP276871</td>\n",
       "      <td>12/28/18 17:14</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>35134300</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>4.41128E+11</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>ASBURY PARK</td>\n",
       "      <td>NJ</td>\n",
       "      <td>12/28/18</td>\n",
       "      <td>1/2/19</td>\n",
       "      <td>1/2/19 0:00</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67600 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Navision Order ID Navision Created Date          Channel  \\\n",
       "0           SYMSP211039           1/1/18 0:00            ROYAL   \n",
       "1           SYMSP211039          1/1/18 21:05  ROYAL CANIN USA   \n",
       "2           SYMSP211132          1/2/18 17:15  ROYAL CANIN USA   \n",
       "3           SYMSP211122          1/2/18 16:12  ROYAL CANIN USA   \n",
       "4           SYMSP211112          1/2/18 14:09  ROYAL CANIN USA   \n",
       "...                 ...                   ...              ...   \n",
       "67595       SYMSP276872        12/28/18 17:14  ROYAL CANIN USA   \n",
       "67596       SYMSP276873        12/28/18 17:14  ROYAL CANIN USA   \n",
       "67597       SYMSP276874        12/28/18 17:14  ROYAL CANIN USA   \n",
       "67598       SYMSP276871        12/28/18 17:14  ROYAL CANIN USA   \n",
       "67599       SYMSP276871        12/28/18 17:14  ROYAL CANIN USA   \n",
       "\n",
       "      Channel Order ID Shipping Agent  Tracking No Order Status  \\\n",
       "0             32107548          FEDEX  9.74468E+14       -NONE-   \n",
       "1             32107548          FEDEX  9.74468E+14       -NONE-   \n",
       "2             32109579          FEDEX  9.74468E+14       -NONE-   \n",
       "3             32109423          FEDEX  9.74468E+14       -NONE-   \n",
       "4             32108343          FEDEX  9.74468E+14       -NONE-   \n",
       "...                ...            ...          ...          ...   \n",
       "67595         35136117          FEDEX  4.41128E+11      Shipped   \n",
       "67596         35136337          FEDEX  4.41128E+11      Shipped   \n",
       "67597         14082922          FEDEX  4.41128E+11      Shipped   \n",
       "67598         35134300          FEDEX  4.41128E+11      Shipped   \n",
       "67599         35134300          FEDEX  4.41128E+11      Shipped   \n",
       "\n",
       "      Status Summary  Shipping City Shipping State Shipped Date Delivered On  \\\n",
       "0                NaN       HOLBROOK             NY       1/2/18          NaN   \n",
       "1                NaN       HOLBROOK             NY       1/2/18          NaN   \n",
       "2                NaN  NEW BRAUNFELS             TX       1/2/18          NaN   \n",
       "3                NaN         Frisco             CO       1/2/18          NaN   \n",
       "4                NaN      DAHLONEGA             GA       1/2/18          NaN   \n",
       "...              ...            ...            ...          ...          ...   \n",
       "67595      Delivered       CHALFONT             PA     12/28/18     12/31/18   \n",
       "67596      Delivered          COCOA             FL     12/28/18       1/2/19   \n",
       "67597      Delivered         IRVINE             CA     12/28/18       1/4/19   \n",
       "67598      Delivered    ASBURY PARK             NJ     12/28/18       1/2/19   \n",
       "67599      Delivered    ASBURY PARK             NJ     12/28/18       1/2/19   \n",
       "\n",
       "              Status  Delivered  period_number country  \n",
       "0                NaN          0              1      US  \n",
       "1                NaN          0              1      US  \n",
       "2                NaN          0              1      US  \n",
       "3                NaN          0              1      US  \n",
       "4                NaN          0              1      US  \n",
       "...              ...        ...            ...     ...  \n",
       "67595  12/31/18 0:00          1             13      US  \n",
       "67596    1/2/19 0:00          1             13      US  \n",
       "67597    1/4/19 0:00          1             13      US  \n",
       "67598    1/2/19 0:00          1             13      US  \n",
       "67599    1/2/19 0:00          1             13      US  \n",
       "\n",
       "[67600 rows x 16 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#####2018 DATASET\n",
    "\n",
    " #merge outbound_shipments_2018 and period_num_df\n",
    "combined_2018_outbound_period_df = pd.merge(\n",
    "    outbound_shipments_2018_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_2018_outbound_period_df\n",
    "\n",
    "#combined_outbound_period_df with states_df\n",
    "combined_2018_dataset_df = pd.merge(\n",
    "    combined_2018_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_2018_dataset_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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                   object\n",
       "Delivered                 int64\n",
       "period_number             int64\n",
       "country                  object\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "combined_2018_dataset_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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</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>SYMSP211039</td>\n",
       "      <td>1/1/18 0:00</td>\n",
       "      <td>ROYAL</td>\n",
       "      <td>32107548</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>9.74468E+14</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HOLBROOK</td>\n",
       "      <td>NY</td>\n",
       "      <td>1/2/18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SYMSP211039</td>\n",
       "      <td>1/1/18 21:05</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>32107548</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>9.74468E+14</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HOLBROOK</td>\n",
       "      <td>NY</td>\n",
       "      <td>1/2/18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SYMSP211132</td>\n",
       "      <td>1/2/18 17:15</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>32109579</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>9.74468E+14</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NEW BRAUNFELS</td>\n",
       "      <td>TX</td>\n",
       "      <td>1/2/18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SYMSP211122</td>\n",
       "      <td>1/2/18 16:12</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>32109423</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>9.74468E+14</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Frisco</td>\n",
       "      <td>CO</td>\n",
       "      <td>1/2/18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SYMSP211112</td>\n",
       "      <td>1/2/18 14:09</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>32108343</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>9.74468E+14</td>\n",
       "      <td>-NONE-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DAHLONEGA</td>\n",
       "      <td>GA</td>\n",
       "      <td>1/2/18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>67595</th>\n",
       "      <td>SYMSP276872</td>\n",
       "      <td>12/28/18 17:14</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>35136117</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>4.41128E+11</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>CHALFONT</td>\n",
       "      <td>PA</td>\n",
       "      <td>12/28/18</td>\n",
       "      <td>12/31/18</td>\n",
       "      <td>12/31/18 0:00</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67596</th>\n",
       "      <td>SYMSP276873</td>\n",
       "      <td>12/28/18 17:14</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>35136337</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>4.41128E+11</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>COCOA</td>\n",
       "      <td>FL</td>\n",
       "      <td>12/28/18</td>\n",
       "      <td>1/2/19</td>\n",
       "      <td>1/2/19 0:00</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67597</th>\n",
       "      <td>SYMSP276874</td>\n",
       "      <td>12/28/18 17:14</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>14082922</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>4.41128E+11</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>IRVINE</td>\n",
       "      <td>CA</td>\n",
       "      <td>12/28/18</td>\n",
       "      <td>1/4/19</td>\n",
       "      <td>1/4/19 0:00</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67598</th>\n",
       "      <td>SYMSP276871</td>\n",
       "      <td>12/28/18 17:14</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>35134300</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>4.41128E+11</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>ASBURY PARK</td>\n",
       "      <td>NJ</td>\n",
       "      <td>12/28/18</td>\n",
       "      <td>1/2/19</td>\n",
       "      <td>1/2/19 0:00</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67599</th>\n",
       "      <td>SYMSP276871</td>\n",
       "      <td>12/28/18 17:14</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>35134300</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>4.41128E+11</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>ASBURY PARK</td>\n",
       "      <td>NJ</td>\n",
       "      <td>12/28/18</td>\n",
       "      <td>1/2/19</td>\n",
       "      <td>1/2/19 0:00</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67600 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Navision Order ID Navision Created Date          Channel  \\\n",
       "0           SYMSP211039           1/1/18 0:00            ROYAL   \n",
       "1           SYMSP211039          1/1/18 21:05  ROYAL CANIN USA   \n",
       "2           SYMSP211132          1/2/18 17:15  ROYAL CANIN USA   \n",
       "3           SYMSP211122          1/2/18 16:12  ROYAL CANIN USA   \n",
       "4           SYMSP211112          1/2/18 14:09  ROYAL CANIN USA   \n",
       "...                 ...                   ...              ...   \n",
       "67595       SYMSP276872        12/28/18 17:14  ROYAL CANIN USA   \n",
       "67596       SYMSP276873        12/28/18 17:14  ROYAL CANIN USA   \n",
       "67597       SYMSP276874        12/28/18 17:14  ROYAL CANIN USA   \n",
       "67598       SYMSP276871        12/28/18 17:14  ROYAL CANIN USA   \n",
       "67599       SYMSP276871        12/28/18 17:14  ROYAL CANIN USA   \n",
       "\n",
       "      Channel Order ID Shipping Agent  Tracking No Order Status  \\\n",
       "0             32107548          FEDEX  9.74468E+14       -NONE-   \n",
       "1             32107548          FEDEX  9.74468E+14       -NONE-   \n",
       "2             32109579          FEDEX  9.74468E+14       -NONE-   \n",
       "3             32109423          FEDEX  9.74468E+14       -NONE-   \n",
       "4             32108343          FEDEX  9.74468E+14       -NONE-   \n",
       "...                ...            ...          ...          ...   \n",
       "67595         35136117          FEDEX  4.41128E+11      Shipped   \n",
       "67596         35136337          FEDEX  4.41128E+11      Shipped   \n",
       "67597         14082922          FEDEX  4.41128E+11      Shipped   \n",
       "67598         35134300          FEDEX  4.41128E+11      Shipped   \n",
       "67599         35134300          FEDEX  4.41128E+11      Shipped   \n",
       "\n",
       "      Status Summary  Shipping City Shipping State Shipped Date Delivered On  \\\n",
       "0                NaN       HOLBROOK             NY       1/2/18          NaN   \n",
       "1                NaN       HOLBROOK             NY       1/2/18          NaN   \n",
       "2                NaN  NEW BRAUNFELS             TX       1/2/18          NaN   \n",
       "3                NaN         Frisco             CO       1/2/18          NaN   \n",
       "4                NaN      DAHLONEGA             GA       1/2/18          NaN   \n",
       "...              ...            ...            ...          ...          ...   \n",
       "67595      Delivered       CHALFONT             PA     12/28/18     12/31/18   \n",
       "67596      Delivered          COCOA             FL     12/28/18       1/2/19   \n",
       "67597      Delivered         IRVINE             CA     12/28/18       1/4/19   \n",
       "67598      Delivered    ASBURY PARK             NJ     12/28/18       1/2/19   \n",
       "67599      Delivered    ASBURY PARK             NJ     12/28/18       1/2/19   \n",
       "\n",
       "              Status  Delivered  period_number country  \n",
       "0                NaN          0              1      US  \n",
       "1                NaN          0              1      US  \n",
       "2                NaN          0              1      US  \n",
       "3                NaN          0              1      US  \n",
       "4                NaN          0              1      US  \n",
       "...              ...        ...            ...     ...  \n",
       "67595  12/31/18 0:00          1             13      US  \n",
       "67596    1/2/19 0:00          1             13      US  \n",
       "67597    1/4/19 0:00          1             13      US  \n",
       "67598    1/2/19 0:00          1             13      US  \n",
       "67599    1/2/19 0:00          1             13      US  \n",
       "\n",
       "[67600 rows x 16 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_2018_dataset_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/0v/_cwy54bd71x52xsydh3mz7nh0000gn/T/ipykernel_81028/2466471808.py:6: 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_2018_dataset_df[\"Delivered On\"] = pd.to_datetime(combined_2018_dataset_df[\"Delivered On\"], errors=\"coerce\")\n",
      "/var/folders/0v/_cwy54bd71x52xsydh3mz7nh0000gn/T/ipykernel_81028/2466471808.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_2018_dataset_df[\"Shipped Date\"] = pd.to_datetime(combined_2018_dataset_df[\"Shipped Date\"], 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</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>15274</th>\n",
       "      <td>SYMSP226139</td>\n",
       "      <td>3/19/18 14:13</td>\n",
       "      <td>Amazon.com, LLC</td>\n",
       "      <td>4FDFXJZF</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>4.05187E+11</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>Kansas City</td>\n",
       "      <td>KS</td>\n",
       "      <td>2018-03-20</td>\n",
       "      <td>2018-03-22</td>\n",
       "      <td>3/22/18 6:53</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>US</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34379</th>\n",
       "      <td>SYMSP245090</td>\n",
       "      <td>7/16/18 4:26</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>5.04257E+11</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400110200793806100069</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Mount Pleasant</td>\n",
       "      <td>SC</td>\n",
       "      <td>2018-07-16</td>\n",
       "      <td>2018-07-18</td>\n",
       "      <td>7/18/18 0:00</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>US</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28948</th>\n",
       "      <td>SYMSP239987</td>\n",
       "      <td>6/11/18 2:28</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>4.84189E+11</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9405510200828762956153</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, In/At Mailbox</td>\n",
       "      <td>Scottsdale</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2018-06-11</td>\n",
       "      <td>2018-06-13</td>\n",
       "      <td>6/13/18 16:16</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>US</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26908</th>\n",
       "      <td>SYMSP237700</td>\n",
       "      <td>5/29/18 17:13</td>\n",
       "      <td>ROYAL CANIN USA</td>\n",
       "      <td>32546300</td>\n",
       "      <td>FEDEX</td>\n",
       "      <td>7.81183E+11</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>PRIOR LAKE</td>\n",
       "      <td>MN</td>\n",
       "      <td>2018-05-30</td>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>6/4/18 8:35</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>US</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24964</th>\n",
       "      <td>SYMSP235955</td>\n",
       "      <td>5/16/18 20:24</td>\n",
       "      <td>WisdomPanel.com</td>\n",
       "      <td>4.70716E+11</td>\n",
       "      <td>USPS</td>\n",
       "      <td>9400110200828742613134</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered, To Mail Room</td>\n",
       "      <td>Austin</td>\n",
       "      <td>TX</td>\n",
       "      <td>2018-05-16</td>\n",
       "      <td>2018-05-21</td>\n",
       "      <td>5/21/18 15:45</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>US</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Navision Order ID Navision Created Date          Channel  \\\n",
       "15274       SYMSP226139         3/19/18 14:13  Amazon.com, LLC   \n",
       "34379       SYMSP245090          7/16/18 4:26  WisdomPanel.com   \n",
       "28948       SYMSP239987          6/11/18 2:28  WisdomPanel.com   \n",
       "26908       SYMSP237700         5/29/18 17:13  ROYAL CANIN USA   \n",
       "24964       SYMSP235955         5/16/18 20:24  WisdomPanel.com   \n",
       "\n",
       "      Channel Order ID Shipping Agent             Tracking No Order Status  \\\n",
       "15274         4FDFXJZF          FEDEX             4.05187E+11      Shipped   \n",
       "34379      5.04257E+11           USPS  9400110200793806100069      Shipped   \n",
       "28948      4.84189E+11           USPS  9405510200828762956153      Shipped   \n",
       "26908         32546300          FEDEX             7.81183E+11      Shipped   \n",
       "24964      4.70716E+11           USPS  9400110200828742613134      Shipped   \n",
       "\n",
       "                 Status Summary   Shipping City Shipping State Shipped Date  \\\n",
       "15274                 Delivered     Kansas City             KS   2018-03-20   \n",
       "34379  Delivered, In/At Mailbox  Mount Pleasant             SC   2018-07-16   \n",
       "28948  Delivered, In/At Mailbox      Scottsdale             AZ   2018-06-11   \n",
       "26908                 Delivered      PRIOR LAKE             MN   2018-05-30   \n",
       "24964   Delivered, To Mail Room          Austin             TX   2018-05-16   \n",
       "\n",
       "      Delivered On         Status  Delivered  period_number country  \\\n",
       "15274   2018-03-22   3/22/18 6:53          1              3      US   \n",
       "34379   2018-07-18   7/18/18 0:00          1              8      US   \n",
       "28948   2018-06-13  6/13/18 16:16          1              6      US   \n",
       "26908   2018-06-04    6/4/18 8:35          1              6      US   \n",
       "24964   2018-05-21  5/21/18 15:45          1              5      US   \n",
       "\n",
       "       transit_time  \n",
       "15274           2.0  \n",
       "34379           2.0  \n",
       "28948           2.0  \n",
       "26908           5.0  \n",
       "24964           5.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#####2018 DATASET\n",
    "\n",
    "#update datetime format with a specified format, errors=\"coerce\" replaces error values with NaT\n",
    "# combined_2018_dataset_df[\"Delivered On\"] = pd.to_datetime(combined_2018_dataset_df[\"Delivered On\"], format=\"%m/%d/%Y\", errors=\"coerce\")\n",
    "# combined_2018_dataset_df[\"Shipped Date\"] = pd.to_datetime(combined_2018_dataset_df[\"Shipped Date\"], format=\"%m/%d/%Y\", errors=\"coerce\")\n",
    "combined_2018_dataset_df[\"Delivered On\"] = pd.to_datetime(combined_2018_dataset_df[\"Delivered On\"], errors=\"coerce\")\n",
    "combined_2018_dataset_df[\"Shipped Date\"] = pd.to_datetime(combined_2018_dataset_df[\"Shipped Date\"], errors=\"coerce\")\n",
    "\n",
    "#create \"transit_time\" column and calculate \n",
    "combined_2018_dataset_df[\"transit_time\"] = combined_2018_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_2018_dataset_df.sample(5)\n"
   ]
  },
  {
   "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>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>3.07</td>\n",
       "      <td>4683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>3.06</td>\n",
       "      <td>4800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "      <td>2.96</td>\n",
       "      <td>3575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>2.97</td>\n",
       "      <td>3441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>5</td>\n",
       "      <td>2.79</td>\n",
       "      <td>4180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>2.56</td>\n",
       "      <td>4109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>7</td>\n",
       "      <td>2.53</td>\n",
       "      <td>3162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>8</td>\n",
       "      <td>2.34</td>\n",
       "      <td>3187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>9</td>\n",
       "      <td>2.39</td>\n",
       "      <td>3719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>10</td>\n",
       "      <td>2.49</td>\n",
       "      <td>3669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>2.96</td>\n",
       "      <td>4166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>3.95</td>\n",
       "      <td>6429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>USPS</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>13</td>\n",
       "      <td>3.26</td>\n",
       "      <td>5545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Shipping Agent country  year  period_number  avg_2018_transit_time  count\n",
       "0            USPS      US  2018              1                   3.07   4683\n",
       "1            USPS      US  2018              2                   3.06   4800\n",
       "2            USPS      US  2018              3                   2.96   3575\n",
       "3            USPS      US  2018              4                   2.97   3441\n",
       "4            USPS      US  2018              5                   2.79   4180\n",
       "5            USPS      US  2018              6                   2.56   4109\n",
       "6            USPS      US  2018              7                   2.53   3162\n",
       "7            USPS      US  2018              8                   2.34   3187\n",
       "8            USPS      US  2018              9                   2.39   3719\n",
       "9            USPS      US  2018             10                   2.49   3669\n",
       "10           USPS      US  2018             11                   2.96   4166\n",
       "11           USPS      US  2018             12                   3.95   6429\n",
       "12           USPS      US  2018             13                   3.26   5545"
      ]
     },
     "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_2018_dataset_df[\"year\"] = combined_2018_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_2018_df = combined_2018_dataset_df[\n",
    "    (combined_2018_dataset_df[\"Shipping Agent\"].isin([\"USPS\"])) &\n",
    "    (combined_2018_dataset_df[\"country\"] == \"US\")]\n",
    "\n",
    "#group by \"Shipping Agent\", \"country\" ,\"year\", \"period_number\" \n",
    "transit_time_2018_stats_df = filtered_2018_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",
    "transit_time_2018_stats_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export the df to csv\n",
    "transit_time_2018_stats_df.to_csv(\"output/usps_transit_time_2018.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>3.04</td>\n",
       "      <td>656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>3.16</td>\n",
       "      <td>1076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "      <td>3.15</td>\n",
       "      <td>864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>2.76</td>\n",
       "      <td>782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>5</td>\n",
       "      <td>3.01</td>\n",
       "      <td>721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>3.38</td>\n",
       "      <td>602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>7</td>\n",
       "      <td>2.87</td>\n",
       "      <td>625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>8</td>\n",
       "      <td>2.65</td>\n",
       "      <td>549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>9</td>\n",
       "      <td>3.44</td>\n",
       "      <td>525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>10</td>\n",
       "      <td>2.74</td>\n",
       "      <td>348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>3.75</td>\n",
       "      <td>1714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>3.73</td>\n",
       "      <td>1559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>FEDEX</td>\n",
       "      <td>US</td>\n",
       "      <td>2018</td>\n",
       "      <td>13</td>\n",
       "      <td>3.40</td>\n",
       "      <td>1485</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  2018              1                   3.04    656\n",
       "1           FEDEX      US  2018              2                   3.16   1076\n",
       "2           FEDEX      US  2018              3                   3.15    864\n",
       "3           FEDEX      US  2018              4                   2.76    782\n",
       "4           FEDEX      US  2018              5                   3.01    721\n",
       "5           FEDEX      US  2018              6                   3.38    602\n",
       "6           FEDEX      US  2018              7                   2.87    625\n",
       "7           FEDEX      US  2018              8                   2.65    549\n",
       "8           FEDEX      US  2018              9                   3.44    525\n",
       "9           FEDEX      US  2018             10                   2.74    348\n",
       "10          FEDEX      US  2018             11                   3.75   1714\n",
       "11          FEDEX      US  2018             12                   3.73   1559\n",
       "12          FEDEX      US  2018             13                   3.40   1485"
      ]
     },
     "execution_count": 10,
     "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_2018_dataset_df[\"year\"] = combined_2018_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_2018_df = combined_2018_dataset_df[\n",
    "    (combined_2018_dataset_df[\"Shipping Agent\"].isin([\"FEDEX\"])) &\n",
    "    (combined_2018_dataset_df[\"country\"] == \"US\")]\n",
    "\n",
    "#group by \"Shipping Agent\", \"country\" ,\"year\", \"period_number\" \n",
    "fedex_transit_time_2018_stats = filtered_2018_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
}