GrowingPains_EDA_DataPrep.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<i>Tamer Abousoud</i>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><center><span style='font-family: \"DejaVu Sans\"; font-size:48px; color:#FF7133'>Growing Pains Case</span></center>\n",
    "<center><span style='font-family: \"DejaVu Sans\"; font-size:28px'><i>EDA and Data Preparation</i></span></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Standard libraries\n",
    "from __future__ import print_function\n",
    "import os\n",
    "from itertools import product, permutations, combinations\n",
    "from collections import defaultdict\n",
    "# 3rd party libraries\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from ipywidgets import interact, interactive, fixed, interact_manual\n",
    "import ipywidgets as widgets\n",
    "from ipyleaflet import Map, Marker, MarkerCluster, AwesomeIcon"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style='color:red'><b><i>NOTE:</i></b></span> The problem states that Northeast Home Goods (NHG) has <b>123</b> stores but the spreadsheets provided show <b><i>122</i></b> locations plus the Distribution Center (DC) operated by Massachusetts Area Distribution (MAD). The 123<sup>rd</sup> location is assumed to be the DC itself."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make sure the .csv files are all located in the current directory (not necessary but helpful):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['distances.csv', 'orders.csv', 'locations.csv']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dir = './data/'\n",
    "csv_files = [i for i in os.listdir(data_dir) if i.endswith('.csv')]; csv_files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br> Create dataframes of the store locations, orders and distances:"
   ]
  },
  {
   "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>ZIP</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>ZIPID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01060</td>\n",
       "      <td>-72.631389</td>\n",
       "      <td>42.318611</td>\n",
       "      <td>Northampton</td>\n",
       "      <td>MA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01101</td>\n",
       "      <td>-72.578056</td>\n",
       "      <td>42.106111</td>\n",
       "      <td>Springfield</td>\n",
       "      <td>MA</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01420</td>\n",
       "      <td>-71.802222</td>\n",
       "      <td>42.583611</td>\n",
       "      <td>Fitchburg</td>\n",
       "      <td>MA</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01510</td>\n",
       "      <td>-71.682778</td>\n",
       "      <td>42.416667</td>\n",
       "      <td>Clinton</td>\n",
       "      <td>MA</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01570</td>\n",
       "      <td>-71.885556</td>\n",
       "      <td>42.048056</td>\n",
       "      <td>Webster</td>\n",
       "      <td>MA</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>01581</td>\n",
       "      <td>-71.615833</td>\n",
       "      <td>42.269444</td>\n",
       "      <td>Westborough</td>\n",
       "      <td>MA</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>01606</td>\n",
       "      <td>-71.819167</td>\n",
       "      <td>42.292500</td>\n",
       "      <td>Worcester</td>\n",
       "      <td>MA</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>01701</td>\n",
       "      <td>-71.415833</td>\n",
       "      <td>42.276944</td>\n",
       "      <td>Framingham</td>\n",
       "      <td>MA</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>01730</td>\n",
       "      <td>-71.276667</td>\n",
       "      <td>42.491389</td>\n",
       "      <td>Bedford</td>\n",
       "      <td>MA</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>01752</td>\n",
       "      <td>-71.540556</td>\n",
       "      <td>42.346111</td>\n",
       "      <td>Marlborough</td>\n",
       "      <td>MA</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ZIP          X          Y         CITY STATE  ZIPID\n",
       "0  01060 -72.631389  42.318611  Northampton    MA      1\n",
       "1  01101 -72.578056  42.106111  Springfield    MA      2\n",
       "2  01420 -71.802222  42.583611    Fitchburg    MA      3\n",
       "3  01510 -71.682778  42.416667      Clinton    MA      4\n",
       "4  01570 -71.885556  42.048056      Webster    MA      5\n",
       "5  01581 -71.615833  42.269444  Westborough    MA      6\n",
       "6  01606 -71.819167  42.292500    Worcester    MA      7\n",
       "7  01701 -71.415833  42.276944   Framingham    MA      8\n",
       "8  01730 -71.276667  42.491389      Bedford    MA      9\n",
       "9  01752 -71.540556  42.346111  Marlborough    MA     10"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Store locations\n",
    "store_locs = pd.read_csv('data/locations.csv', nrows=123)  # last few rows in .csv are junk\n",
    "# Change zipcodes to strings and pad with leading zeros\n",
    "store_locs['ZIP'] = store_locs['ZIP'].apply(lambda x: str(int(x)).zfill(5)) \n",
    "store_locs.head(10)"
   ]
  },
  {
   "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>ORDERID</th>\n",
       "      <th>FROMZIP</th>\n",
       "      <th>TOZIP</th>\n",
       "      <th>CUBE</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>ST required?</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>01887</td>\n",
       "      <td>01887</td>\n",
       "      <td>(Depot)</td>\n",
       "      <td>(Depot)</td>\n",
       "      <td>(Depot)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>01887</td>\n",
       "      <td>01801</td>\n",
       "      <td>333</td>\n",
       "      <td>Wed</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>01887</td>\n",
       "      <td>01821</td>\n",
       "      <td>556</td>\n",
       "      <td>Fri</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>01887</td>\n",
       "      <td>01843</td>\n",
       "      <td>903</td>\n",
       "      <td>Fri</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>01887</td>\n",
       "      <td>01845</td>\n",
       "      <td>111</td>\n",
       "      <td>Tue</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>01887</td>\n",
       "      <td>01867</td>\n",
       "      <td>206</td>\n",
       "      <td>Thu</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>01887</td>\n",
       "      <td>01876</td>\n",
       "      <td>2699</td>\n",
       "      <td>Wed</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>01887</td>\n",
       "      <td>01887</td>\n",
       "      <td>491</td>\n",
       "      <td>Tue</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>01887</td>\n",
       "      <td>01910</td>\n",
       "      <td>100</td>\n",
       "      <td>Tue</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>01887</td>\n",
       "      <td>02139</td>\n",
       "      <td>182</td>\n",
       "      <td>Tue</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ORDERID FROMZIP  TOZIP     CUBE DayOfWeek ST required?\n",
       "0        0   01887  01887  (Depot)   (Depot)      (Depot)\n",
       "1        1   01887  01801      333       Wed           no\n",
       "2        2   01887  01821      556       Fri           no\n",
       "3        3   01887  01843      903       Fri           no\n",
       "4        4   01887  01845      111       Tue           no\n",
       "5        5   01887  01867      206       Thu           no\n",
       "6        6   01887  01876     2699       Wed           no\n",
       "7        7   01887  01887      491       Tue           no\n",
       "8        8   01887  01910      100       Tue           no\n",
       "9        9   01887  02139      182       Tue           no"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Weekly orders\n",
    "orders = pd.read_csv('data/orders.csv')\n",
    "# Change zipcodes to strings and pad with leading zeros\n",
    "orders['FROMZIP'] = orders['FROMZIP'].apply(lambda x: str(int(x)).zfill(5))\n",
    "orders['TOZIP'] = orders['TOZIP'].apply(lambda x: str(int(x)).zfill(5))\n",
    "\n",
    "orders.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Matrix Dimensions:(123, 123)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>...</th>\n",
       "      <th>114</th>\n",
       "      <th>115</th>\n",
       "      <th>116</th>\n",
       "      <th>117</th>\n",
       "      <th>118</th>\n",
       "      <th>119</th>\n",
       "      <th>120</th>\n",
       "      <th>121</th>\n",
       "      <th>122</th>\n",
       "      <th>123</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>68</td>\n",
       "      <td>82</td>\n",
       "      <td>70</td>\n",
       "      <td>74</td>\n",
       "      <td>69</td>\n",
       "      <td>84</td>\n",
       "      <td>104</td>\n",
       "      <td>83</td>\n",
       "      <td>...</td>\n",
       "      <td>116</td>\n",
       "      <td>112</td>\n",
       "      <td>116</td>\n",
       "      <td>126</td>\n",
       "      <td>110</td>\n",
       "      <td>113</td>\n",
       "      <td>123</td>\n",
       "      <td>123</td>\n",
       "      <td>121</td>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>81</td>\n",
       "      <td>68</td>\n",
       "      <td>55</td>\n",
       "      <td>60</td>\n",
       "      <td>55</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>69</td>\n",
       "      <td>...</td>\n",
       "      <td>97</td>\n",
       "      <td>94</td>\n",
       "      <td>97</td>\n",
       "      <td>108</td>\n",
       "      <td>91</td>\n",
       "      <td>94</td>\n",
       "      <td>104</td>\n",
       "      <td>105</td>\n",
       "      <td>103</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>68</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>45</td>\n",
       "      <td>41</td>\n",
       "      <td>25</td>\n",
       "      <td>43</td>\n",
       "      <td>34</td>\n",
       "      <td>36</td>\n",
       "      <td>...</td>\n",
       "      <td>161</td>\n",
       "      <td>157</td>\n",
       "      <td>161</td>\n",
       "      <td>171</td>\n",
       "      <td>156</td>\n",
       "      <td>157</td>\n",
       "      <td>167</td>\n",
       "      <td>168</td>\n",
       "      <td>166</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>82</td>\n",
       "      <td>68</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>18</td>\n",
       "      <td>11</td>\n",
       "      <td>20</td>\n",
       "      <td>35</td>\n",
       "      <td>13</td>\n",
       "      <td>...</td>\n",
       "      <td>148</td>\n",
       "      <td>144</td>\n",
       "      <td>148</td>\n",
       "      <td>158</td>\n",
       "      <td>144</td>\n",
       "      <td>145</td>\n",
       "      <td>155</td>\n",
       "      <td>155</td>\n",
       "      <td>153</td>\n",
       "      <td>154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70</td>\n",
       "      <td>55</td>\n",
       "      <td>45</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>26</td>\n",
       "      <td>20</td>\n",
       "      <td>38</td>\n",
       "      <td>58</td>\n",
       "      <td>35</td>\n",
       "      <td>...</td>\n",
       "      <td>138</td>\n",
       "      <td>133</td>\n",
       "      <td>133</td>\n",
       "      <td>143</td>\n",
       "      <td>133</td>\n",
       "      <td>135</td>\n",
       "      <td>139</td>\n",
       "      <td>140</td>\n",
       "      <td>143</td>\n",
       "      <td>143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>74</td>\n",
       "      <td>60</td>\n",
       "      <td>41</td>\n",
       "      <td>18</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>33</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>140</td>\n",
       "      <td>136</td>\n",
       "      <td>140</td>\n",
       "      <td>151</td>\n",
       "      <td>136</td>\n",
       "      <td>137</td>\n",
       "      <td>147</td>\n",
       "      <td>147</td>\n",
       "      <td>145</td>\n",
       "      <td>146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>69</td>\n",
       "      <td>55</td>\n",
       "      <td>25</td>\n",
       "      <td>11</td>\n",
       "      <td>20</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>50</td>\n",
       "      <td>19</td>\n",
       "      <td>...</td>\n",
       "      <td>136</td>\n",
       "      <td>132</td>\n",
       "      <td>136</td>\n",
       "      <td>147</td>\n",
       "      <td>132</td>\n",
       "      <td>133</td>\n",
       "      <td>143</td>\n",
       "      <td>143</td>\n",
       "      <td>141</td>\n",
       "      <td>142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>84</td>\n",
       "      <td>70</td>\n",
       "      <td>43</td>\n",
       "      <td>20</td>\n",
       "      <td>38</td>\n",
       "      <td>12</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>151</td>\n",
       "      <td>147</td>\n",
       "      <td>151</td>\n",
       "      <td>161</td>\n",
       "      <td>147</td>\n",
       "      <td>148</td>\n",
       "      <td>158</td>\n",
       "      <td>158</td>\n",
       "      <td>156</td>\n",
       "      <td>157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>104</td>\n",
       "      <td>90</td>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "      <td>58</td>\n",
       "      <td>33</td>\n",
       "      <td>50</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>...</td>\n",
       "      <td>170</td>\n",
       "      <td>166</td>\n",
       "      <td>170</td>\n",
       "      <td>180</td>\n",
       "      <td>166</td>\n",
       "      <td>167</td>\n",
       "      <td>177</td>\n",
       "      <td>177</td>\n",
       "      <td>175</td>\n",
       "      <td>176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>83</td>\n",
       "      <td>69</td>\n",
       "      <td>36</td>\n",
       "      <td>13</td>\n",
       "      <td>35</td>\n",
       "      <td>10</td>\n",
       "      <td>19</td>\n",
       "      <td>6</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>150</td>\n",
       "      <td>146</td>\n",
       "      <td>150</td>\n",
       "      <td>160</td>\n",
       "      <td>145</td>\n",
       "      <td>147</td>\n",
       "      <td>157</td>\n",
       "      <td>157</td>\n",
       "      <td>155</td>\n",
       "      <td>155</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 123 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      1   2   3   4   5   6   7   8    9  10  ...  114  115  116  117  118  \\\n",
       "id                                            ...                            \n",
       "1     0  19  68  82  70  74  69  84  104  83  ...  116  112  116  126  110   \n",
       "2    19   0  81  68  55  60  55  70   90  69  ...   97   94   97  108   91   \n",
       "3    68  81   0  17  45  41  25  43   34  36  ...  161  157  161  171  156   \n",
       "4    82  68  17   0  32  18  11  20   35  13  ...  148  144  148  158  144   \n",
       "5    70  55  45  32   0  26  20  38   58  35  ...  138  133  133  143  133   \n",
       "6    74  60  41  18  26   0  15  12   33  10  ...  140  136  140  151  136   \n",
       "7    69  55  25  11  20  15   0  25   50  19  ...  136  132  136  147  132   \n",
       "8    84  70  43  20  38  12  25   0   22   6  ...  151  147  151  161  147   \n",
       "9   104  90  34  35  58  33  50  22    0  24  ...  170  166  170  180  166   \n",
       "10   83  69  36  13  35  10  19   6   24   0  ...  150  146  150  160  145   \n",
       "\n",
       "    119  120  121  122  123  \n",
       "id                           \n",
       "1   113  123  123  121  122  \n",
       "2    94  104  105  103  103  \n",
       "3   157  167  168  166  166  \n",
       "4   145  155  155  153  154  \n",
       "5   135  139  140  143  143  \n",
       "6   137  147  147  145  146  \n",
       "7   133  143  143  141  142  \n",
       "8   148  158  158  156  157  \n",
       "9   167  177  177  175  176  \n",
       "10  147  157  157  155  155  \n",
       "\n",
       "[10 rows x 123 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Distances\n",
    "distances = pd.read_csv('data/distances.csv', header=1, index_col=1)\n",
    "distances.drop('Zip', axis=1, inplace=True)  # 'Zip' col is redundant\n",
    "print(f'\\nMatrix Dimensions:{distances.shape}\\n')\n",
    "distances.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br> The distribution center in Wilmington should be the center of the locations. This is important to keep in mind when developing the solution to the routing problem since the DC will be the starting and ending node for all the vehicles. The ID for the DC in the dataframe above is 20. This can only be determined by reading the problem statement and cross-referencing the zip code from the `orders` dataframe (which gives 01887 as the \"depot\" or DC zip) with the id's in `store_locs`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ORDERID               0\n",
       "FROMZIP           01887\n",
       "TOZIP             01887\n",
       "CUBE            (Depot)\n",
       "DayOfWeek       (Depot)\n",
       "ST required?    (Depot)\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.loc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "tags": []
   },
   "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>ZIP</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>ZIPID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>01887</td>\n",
       "      <td>-71.175556</td>\n",
       "      <td>42.545556</td>\n",
       "      <td>Wilmington</td>\n",
       "      <td>MA</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      ZIP          X          Y        CITY STATE  ZIPID\n",
       "19  01887 -71.175556  42.545556  Wilmington    MA     20"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "store_locs.loc[store_locs['ZIP'] == '01887']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br> The locations of the DC and stores can be used as map coordinates for plotting. The following `dict` holds the coordinates with the `ZIPID` as the key."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The X, Y coordinates need to be flipped for plotting\n",
    "store_loc_coords = store_locs.loc[:, ['ZIPID', 'Y', 'X']].set_index('ZIPID').T.to_dict('list')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br> A distribution map can be created to show the locations of the DC and NHG stores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "860bfb3e505e4afa8608242ee083fa7a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Map(center=[42.5455555555556, -71.1755555555556], controls=(ZoomControl(options=['position', 'zoom_in_text', '…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# location and icon of DC (depot)\n",
    "dc_loc = tuple(store_loc_coords.get(20))\n",
    "dc_icon = AwesomeIcon(name='home', marker_color='red')\n",
    "\n",
    "# initialize map centered at DC\n",
    "distribution_map = Map(center=dc_loc, zoom=10)\n",
    "\n",
    "# define the DC and stores markers\n",
    "dc_marker = Marker(location=dc_loc, title='Depot-DC', icon=dc_icon, draggable=False)\n",
    "store_map_locs = [tuple(store_loc_coords.get(ID)) for ID in store_loc_coords.keys() if ID != 20]\n",
    "store_marker_locs = [Marker(location=crd, draggable=False) for crd in store_map_locs]\n",
    "store_markers = MarkerCluster(markers=store_marker_locs)\n",
    "\n",
    "# add the markers to the map\n",
    "distribution_map.add_layer(dc_marker)\n",
    "distribution_map.add_layer(store_markers)\n",
    "\n",
    "display(distribution_map)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "distribution_map.save('DistrMap1.html', title='Distribution Map 1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br> <h3><u>Creating the Daily Orders</u></h3>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A truck leaves the DC to make multiple stops and returns to the DC after distributing all the goods. There are no other pickups or deliveries along the way. For the purposes of this project, each day will be treated separately so the routes will be optimized over each day instead of over the whole week. This makes setting up the problem easier to set up for the optimization solver."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All daily orders originate from the DC. The following steps create the daily order book, dropping the origin zip (which is the same for all orders - the DC) and replacing the `TOZIP` column with the zip ID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "daily_orders = orders.drop(labels=0).copy(deep=True)  # drop 1st row depot --> depot\n",
    "daily_orders.drop(columns='ST required?', inplace=True)  # drop last column, not used\n",
    "daily_orders.CUBE = pd.to_numeric(daily_orders.CUBE)  # make sure volumes are numeric\n",
    "# Use the zip indices from `store_locs`\n",
    "daily_orders = pd.merge(left=daily_orders, right=store_locs.loc[:, ['ZIP', 'ZIPID']], \n",
    "                        how='inner', left_on='TOZIP', right_on='ZIP')\n",
    "# Drop all zip codes\n",
    "daily_orders.drop(columns=['FROMZIP', 'TOZIP', 'ZIP'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Total weekly orders:261\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ORDERID</th>\n",
       "      <th>CUBE</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>ZIPID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>333</td>\n",
       "      <td>Wed</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>37</td>\n",
       "      <td>128</td>\n",
       "      <td>Wed</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>126</td>\n",
       "      <td>195</td>\n",
       "      <td>Fri</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>556</td>\n",
       "      <td>Fri</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21</td>\n",
       "      <td>1396</td>\n",
       "      <td>Thu</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>78</td>\n",
       "      <td>146</td>\n",
       "      <td>Tue</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>108</td>\n",
       "      <td>261</td>\n",
       "      <td>Tue</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>251</td>\n",
       "      <td>146</td>\n",
       "      <td>Mon</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3</td>\n",
       "      <td>903</td>\n",
       "      <td>Fri</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4</td>\n",
       "      <td>111</td>\n",
       "      <td>Tue</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>22</td>\n",
       "      <td>286</td>\n",
       "      <td>Tue</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>5</td>\n",
       "      <td>206</td>\n",
       "      <td>Thu</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ORDERID  CUBE DayOfWeek  ZIPID\n",
       "0         1   333       Wed     12\n",
       "1        37   128       Wed     12\n",
       "2       126   195       Fri     12\n",
       "3         2   556       Fri     13\n",
       "4        21  1396       Thu     13\n",
       "5        78   146       Tue     13\n",
       "6       108   261       Tue     13\n",
       "7       251   146       Mon     13\n",
       "8         3   903       Fri     14\n",
       "9         4   111       Tue     15\n",
       "10       22   286       Tue     15\n",
       "11        5   206       Thu     17"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(f'\\nTotal weekly orders:{len(daily_orders)}\\n')\n",
    "daily_orders.head(12)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br> Check the volumes and number of the daily orders:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DayOfWeek\n",
       "Wed    15192\n",
       "Fri    13468\n",
       "Thu    15009\n",
       "Tue    11537\n",
       "Mon    10223\n",
       "Name: CUBE, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_total_vols = daily_orders['CUBE'].groupby(daily_orders.DayOfWeek, sort=False).sum()\n",
    "daily_total_vols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DayOfWeek\n",
       "Wed    50\n",
       "Fri    47\n",
       "Thu    63\n",
       "Tue    58\n",
       "Mon    43\n",
       "Name: CUBE, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_total_dels = daily_orders['CUBE'].groupby(daily_orders.DayOfWeek, sort=False).count()\n",
    "daily_total_dels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br> The next step is to break out the orders by day and aggregate orders for each location. The process is:\n",
    "- For each day, find the total demand (i.e. required deliveries) per location.\n",
    "- Create a `dict` object to store the location demand for every weekday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "def LocDemandByDay(df=daily_orders):\n",
    "    '''\n",
    "    Creates a dictionary of total demand per location for each day\n",
    "    ---\n",
    "    returns: dict of form { 'day':{ ZIPID:total_demand } }\n",
    "    '''\n",
    "    days = list(daily_orders.DayOfWeek.unique())\n",
    "    \n",
    "    daily_demand = defaultdict(dict)\n",
    "    \n",
    "    for day in days:\n",
    "        # filter df by day, aggregate by locations\n",
    "        demand = daily_orders.loc[daily_orders.DayOfWeek == day]\\\n",
    "                  .groupby('ZIPID').sum().drop(columns='ORDERID')\n",
    "        # convert df to dict and add to \n",
    "        daily_demand[day] = demand.to_dict().get('CUBE')\n",
    "        \n",
    "    return daily_demand"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "defaultdict(dict,\n",
       "            {'Wed': {1: 151,\n",
       "              2: 140,\n",
       "              7: 159,\n",
       "              8: 137,\n",
       "              10: 164,\n",
       "              12: 461,\n",
       "              16: 159,\n",
       "              18: 2800,\n",
       "              26: 328,\n",
       "              27: 120,\n",
       "              28: 280,\n",
       "              29: 420,\n",
       "              35: 132,\n",
       "              39: 239,\n",
       "              42: 157,\n",
       "              44: 176,\n",
       "              45: 2077,\n",
       "              54: 151,\n",
       "              55: 366,\n",
       "              56: 241,\n",
       "              57: 2202,\n",
       "              58: 212,\n",
       "              63: 246,\n",
       "              73: 280,\n",
       "              74: 140,\n",
       "              80: 286,\n",
       "              84: 94,\n",
       "              85: 210,\n",
       "              88: 149,\n",
       "              90: 246,\n",
       "              92: 236,\n",
       "              95: 304,\n",
       "              105: 130,\n",
       "              106: 283,\n",
       "              107: 396,\n",
       "              116: 376,\n",
       "              120: 235,\n",
       "              122: 197,\n",
       "              123: 112},\n",
       "             'Fri': {3: 444,\n",
       "              4: 390,\n",
       "              5: 336,\n",
       "              6: 102,\n",
       "              8: 106,\n",
       "              11: 119,\n",
       "              12: 195,\n",
       "              13: 556,\n",
       "              14: 903,\n",
       "              17: 132,\n",
       "              23: 181,\n",
       "              27: 121,\n",
       "              28: 140,\n",
       "              29: 221,\n",
       "              31: 202,\n",
       "              32: 187,\n",
       "              36: 109,\n",
       "              41: 161,\n",
       "              46: 131,\n",
       "              47: 182,\n",
       "              51: 3281,\n",
       "              70: 177,\n",
       "              73: 154,\n",
       "              75: 163,\n",
       "              78: 186,\n",
       "              79: 423,\n",
       "              82: 78,\n",
       "              87: 122,\n",
       "              90: 592,\n",
       "              92: 255,\n",
       "              96: 507,\n",
       "              97: 164,\n",
       "              107: 247,\n",
       "              108: 368,\n",
       "              109: 261,\n",
       "              110: 147,\n",
       "              111: 143,\n",
       "              117: 126,\n",
       "              118: 172,\n",
       "              120: 88,\n",
       "              121: 896},\n",
       "             'Thu': {8: 137,\n",
       "              10: 180,\n",
       "              13: 1396,\n",
       "              17: 206,\n",
       "              19: 163,\n",
       "              21: 250,\n",
       "              25: 121,\n",
       "              26: 272,\n",
       "              27: 281,\n",
       "              29: 461,\n",
       "              30: 480,\n",
       "              31: 148,\n",
       "              34: 142,\n",
       "              36: 172,\n",
       "              37: 604,\n",
       "              38: 424,\n",
       "              40: 146,\n",
       "              43: 478,\n",
       "              46: 2614,\n",
       "              51: 582,\n",
       "              52: 131,\n",
       "              53: 345,\n",
       "              59: 319,\n",
       "              60: 94,\n",
       "              61: 197,\n",
       "              62: 257,\n",
       "              64: 305,\n",
       "              65: 193,\n",
       "              69: 175,\n",
       "              70: 398,\n",
       "              74: 168,\n",
       "              87: 102,\n",
       "              88: 127,\n",
       "              96: 474,\n",
       "              97: 479,\n",
       "              100: 225,\n",
       "              103: 103,\n",
       "              104: 112,\n",
       "              107: 138,\n",
       "              110: 417,\n",
       "              114: 195,\n",
       "              115: 348,\n",
       "              116: 152,\n",
       "              118: 125,\n",
       "              123: 173},\n",
       "             'Tue': {9: 226,\n",
       "              13: 407,\n",
       "              15: 397,\n",
       "              20: 491,\n",
       "              22: 100,\n",
       "              24: 197,\n",
       "              26: 226,\n",
       "              28: 335,\n",
       "              29: 135,\n",
       "              30: 665,\n",
       "              36: 284,\n",
       "              37: 432,\n",
       "              38: 286,\n",
       "              43: 504,\n",
       "              49: 143,\n",
       "              50: 409,\n",
       "              51: 114,\n",
       "              64: 189,\n",
       "              66: 237,\n",
       "              68: 243,\n",
       "              71: 140,\n",
       "              73: 180,\n",
       "              74: 374,\n",
       "              76: 131,\n",
       "              81: 256,\n",
       "              83: 257,\n",
       "              88: 496,\n",
       "              89: 164,\n",
       "              91: 105,\n",
       "              92: 263,\n",
       "              93: 111,\n",
       "              96: 448,\n",
       "              97: 199,\n",
       "              98: 81,\n",
       "              101: 313,\n",
       "              102: 572,\n",
       "              107: 210,\n",
       "              113: 394,\n",
       "              116: 110,\n",
       "              121: 425,\n",
       "              123: 288},\n",
       "             'Mon': {6: 111,\n",
       "              10: 124,\n",
       "              13: 146,\n",
       "              26: 285,\n",
       "              27: 246,\n",
       "              28: 106,\n",
       "              32: 1796,\n",
       "              33: 187,\n",
       "              35: 165,\n",
       "              42: 163,\n",
       "              48: 161,\n",
       "              51: 279,\n",
       "              67: 244,\n",
       "              72: 325,\n",
       "              73: 1479,\n",
       "              76: 184,\n",
       "              77: 159,\n",
       "              84: 157,\n",
       "              85: 170,\n",
       "              86: 110,\n",
       "              87: 127,\n",
       "              90: 242,\n",
       "              92: 124,\n",
       "              93: 538,\n",
       "              94: 102,\n",
       "              95: 220,\n",
       "              96: 755,\n",
       "              97: 293,\n",
       "              99: 213,\n",
       "              101: 402,\n",
       "              106: 167,\n",
       "              107: 153,\n",
       "              112: 127,\n",
       "              119: 163}})"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demands = LocDemandByDay()\n",
    "demands"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br> <h3><u>Create Daily Time Matrices</u></h3>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This type of problem is know as a <i>capacitated vehicle routing problem with time windows</i> or <b>CVRPTW</b> problem. Because of the time windows and the time constraints imposed in the case, the matrix of distances has to be converted to a matrix of travel times between locations. The steps are:\n",
    "- Create an overall time matrix (in minutes) from the given distance matrix.\n",
    "- Extract a time matrix for each day from the overall time matrix.\n",
    "\n",
    "The average vehicle speed is is assumed to be <b>40 mph</b> as given in the case study."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Vehicle speed in miles per minute\n",
    "veh_speed = np.round(40/60, 3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>...</th>\n",
       "      <th>114</th>\n",
       "      <th>115</th>\n",
       "      <th>116</th>\n",
       "      <th>117</th>\n",
       "      <th>118</th>\n",
       "      <th>119</th>\n",
       "      <th>120</th>\n",
       "      <th>121</th>\n",
       "      <th>122</th>\n",
       "      <th>123</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>102</td>\n",
       "      <td>123</td>\n",
       "      <td>105</td>\n",
       "      <td>111</td>\n",
       "      <td>103</td>\n",
       "      <td>126</td>\n",
       "      <td>156</td>\n",
       "      <td>124</td>\n",
       "      <td>...</td>\n",
       "      <td>174</td>\n",
       "      <td>168</td>\n",
       "      <td>174</td>\n",
       "      <td>189</td>\n",
       "      <td>165</td>\n",
       "      <td>169</td>\n",
       "      <td>184</td>\n",
       "      <td>184</td>\n",
       "      <td>181</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>121</td>\n",
       "      <td>102</td>\n",
       "      <td>82</td>\n",
       "      <td>90</td>\n",
       "      <td>82</td>\n",
       "      <td>105</td>\n",
       "      <td>135</td>\n",
       "      <td>103</td>\n",
       "      <td>...</td>\n",
       "      <td>145</td>\n",
       "      <td>141</td>\n",
       "      <td>145</td>\n",
       "      <td>162</td>\n",
       "      <td>136</td>\n",
       "      <td>141</td>\n",
       "      <td>156</td>\n",
       "      <td>157</td>\n",
       "      <td>154</td>\n",
       "      <td>154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>102</td>\n",
       "      <td>121</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>67</td>\n",
       "      <td>61</td>\n",
       "      <td>37</td>\n",
       "      <td>64</td>\n",
       "      <td>51</td>\n",
       "      <td>54</td>\n",
       "      <td>...</td>\n",
       "      <td>241</td>\n",
       "      <td>235</td>\n",
       "      <td>241</td>\n",
       "      <td>256</td>\n",
       "      <td>234</td>\n",
       "      <td>235</td>\n",
       "      <td>250</td>\n",
       "      <td>252</td>\n",
       "      <td>249</td>\n",
       "      <td>249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>123</td>\n",
       "      <td>102</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>48</td>\n",
       "      <td>27</td>\n",
       "      <td>16</td>\n",
       "      <td>30</td>\n",
       "      <td>52</td>\n",
       "      <td>19</td>\n",
       "      <td>...</td>\n",
       "      <td>222</td>\n",
       "      <td>216</td>\n",
       "      <td>222</td>\n",
       "      <td>237</td>\n",
       "      <td>216</td>\n",
       "      <td>217</td>\n",
       "      <td>232</td>\n",
       "      <td>232</td>\n",
       "      <td>229</td>\n",
       "      <td>231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>105</td>\n",
       "      <td>82</td>\n",
       "      <td>67</td>\n",
       "      <td>48</td>\n",
       "      <td>0</td>\n",
       "      <td>39</td>\n",
       "      <td>30</td>\n",
       "      <td>57</td>\n",
       "      <td>87</td>\n",
       "      <td>52</td>\n",
       "      <td>...</td>\n",
       "      <td>207</td>\n",
       "      <td>199</td>\n",
       "      <td>199</td>\n",
       "      <td>214</td>\n",
       "      <td>199</td>\n",
       "      <td>202</td>\n",
       "      <td>208</td>\n",
       "      <td>210</td>\n",
       "      <td>214</td>\n",
       "      <td>214</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>169</td>\n",
       "      <td>141</td>\n",
       "      <td>235</td>\n",
       "      <td>217</td>\n",
       "      <td>202</td>\n",
       "      <td>205</td>\n",
       "      <td>199</td>\n",
       "      <td>222</td>\n",
       "      <td>250</td>\n",
       "      <td>220</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>28</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>25</td>\n",
       "      <td>24</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>184</td>\n",
       "      <td>156</td>\n",
       "      <td>250</td>\n",
       "      <td>232</td>\n",
       "      <td>208</td>\n",
       "      <td>220</td>\n",
       "      <td>214</td>\n",
       "      <td>237</td>\n",
       "      <td>265</td>\n",
       "      <td>235</td>\n",
       "      <td>...</td>\n",
       "      <td>15</td>\n",
       "      <td>16</td>\n",
       "      <td>15</td>\n",
       "      <td>4</td>\n",
       "      <td>34</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>184</td>\n",
       "      <td>157</td>\n",
       "      <td>252</td>\n",
       "      <td>232</td>\n",
       "      <td>210</td>\n",
       "      <td>220</td>\n",
       "      <td>214</td>\n",
       "      <td>237</td>\n",
       "      <td>265</td>\n",
       "      <td>235</td>\n",
       "      <td>...</td>\n",
       "      <td>24</td>\n",
       "      <td>16</td>\n",
       "      <td>15</td>\n",
       "      <td>4</td>\n",
       "      <td>36</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>181</td>\n",
       "      <td>154</td>\n",
       "      <td>249</td>\n",
       "      <td>229</td>\n",
       "      <td>214</td>\n",
       "      <td>217</td>\n",
       "      <td>211</td>\n",
       "      <td>234</td>\n",
       "      <td>262</td>\n",
       "      <td>232</td>\n",
       "      <td>...</td>\n",
       "      <td>12</td>\n",
       "      <td>19</td>\n",
       "      <td>22</td>\n",
       "      <td>13</td>\n",
       "      <td>22</td>\n",
       "      <td>24</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>183</td>\n",
       "      <td>154</td>\n",
       "      <td>249</td>\n",
       "      <td>231</td>\n",
       "      <td>214</td>\n",
       "      <td>219</td>\n",
       "      <td>213</td>\n",
       "      <td>235</td>\n",
       "      <td>264</td>\n",
       "      <td>232</td>\n",
       "      <td>...</td>\n",
       "      <td>13</td>\n",
       "      <td>19</td>\n",
       "      <td>22</td>\n",
       "      <td>15</td>\n",
       "      <td>22</td>\n",
       "      <td>25</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>123 rows × 123 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     1    2    3    4    5    6    7    8    9    10   ...  114  115  116  \\\n",
       "id                                                     ...                  \n",
       "1      0   28  102  123  105  111  103  126  156  124  ...  174  168  174   \n",
       "2     28    0  121  102   82   90   82  105  135  103  ...  145  141  145   \n",
       "3    102  121    0   25   67   61   37   64   51   54  ...  241  235  241   \n",
       "4    123  102   25    0   48   27   16   30   52   19  ...  222  216  222   \n",
       "5    105   82   67   48    0   39   30   57   87   52  ...  207  199  199   \n",
       "..   ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...   \n",
       "119  169  141  235  217  202  205  199  222  250  220  ...    9   10   13   \n",
       "120  184  156  250  232  208  220  214  237  265  235  ...   15   16   15   \n",
       "121  184  157  252  232  210  220  214  237  265  235  ...   24   16   15   \n",
       "122  181  154  249  229  214  217  211  234  262  232  ...   12   19   22   \n",
       "123  183  154  249  231  214  219  213  235  264  232  ...   13   19   22   \n",
       "\n",
       "     117  118  119  120  121  122  123  \n",
       "id                                      \n",
       "1    189  165  169  184  184  181  183  \n",
       "2    162  136  141  156  157  154  154  \n",
       "3    256  234  235  250  252  249  249  \n",
       "4    237  216  217  232  232  229  231  \n",
       "5    214  199  202  208  210  214  214  \n",
       "..   ...  ...  ...  ...  ...  ...  ...  \n",
       "119   28   13    0   24   25   24   25  \n",
       "120    4   34   24    0    1   10   10  \n",
       "121    4   36   25    1    0   12   12  \n",
       "122   13   22   24   10   12    0    3  \n",
       "123   15   22   25   10   12    3    0  \n",
       "\n",
       "[123 rows x 123 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "travel_times = distances.applymap(lambda x: int(np.round(x/veh_speed, 0)))\n",
    "travel_times.columns = travel_times.columns.astype(int, copy=False)\n",
    "travel_times"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br> Double-check that travel times make sense. Take the maximum travel time and check the distance on the map."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>ZIP</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>CITY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>ZIPID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>05401</td>\n",
       "      <td>-73.220556</td>\n",
       "      <td>44.485000</td>\n",
       "      <td>Burlington</td>\n",
       "      <td>VT</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>06877</td>\n",
       "      <td>-73.498056</td>\n",
       "      <td>41.281389</td>\n",
       "      <td>Ridgefield</td>\n",
       "      <td>CT</td>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ZIP          X          Y        CITY STATE  ZIPID\n",
       "71   05401 -73.220556  44.485000  Burlington    VT     72\n",
       "117  06877 -73.498056  41.281389  Ridgefield    CT    118"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "max_travel = tuple(np.where(travel_times == travel_times.max().max())[0])\n",
    "store_locs.loc[(max_travel[0]+1, max_travel[1]+1), :]  # note df is indexed from 1 not 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The largest distance is between the Burlington, VT and Ridgefield, CT stores which seems logical as shown by the map. Note that the distance matrix is based on road miles, not the straight distance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b3f14dc6690b4ee0828a39b91831933a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Map(center=[42.5455555555556, -71.1755555555556], controls=(ZoomControl(options=['position', 'zoom_in_text', '…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# initialize another map centered at DC\n",
    "max_distance_map = Map(center=dc_loc, zoom=6)\n",
    "\n",
    "# define the DC and stores markers\n",
    "store1 = Marker(location=tuple(store_loc_coords.get(72)), draggable=False)\n",
    "store2 = Marker(location=tuple(store_loc_coords.get(118)), draggable=False)\n",
    "\n",
    "# add the markers to the map\n",
    "max_distance_map.add_layer(store1)\n",
    "max_distance_map.add_layer(store2)\n",
    "\n",
    "display(max_distance_map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# max_distance_map.save('MaxDistMap1.html', title='Distance Map 1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br> Next, extract the daily time matrices using the `demands` dict that was just created. Creating the daily matrices can get tricky because:\n",
    "- There are different indexes for the same location. The ZIPID's are labeled from 1 to 123 while the Numpy array will be zero-indexed and truncated for each array. The solver uses yet another indexing scheme. It is important to make sure everything is consistent.\n",
    "- The depot (ZIPID 20) is not included for each day in the demand schedule <i>except</i> Tuesday, so it has be added for each day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def makeTimeMtrxForDay(day, df=travel_times, schedule=demands):\n",
    "    '''\n",
    "    Create a time matrix for a given day based on the overall travel times\n",
    "    ---\n",
    "    day: day for which to create the matrix\n",
    "    df: the df with the overall travel times to extract\n",
    "    schedule: the schedule of deliveries (demands) for the day \n",
    "    '''\n",
    "    from itertools import product\n",
    "    \n",
    "    locations = list(schedule.get(day).keys())  # get the delivery locations\n",
    "    #  - Make sure to include the depot if it is not in the locations\n",
    "    #  - It is better to have the depot at the beginning so it has index 0\n",
    "    #    in the new matrix. This makes things easier later.\n",
    "    if 20 not in locations:\n",
    "        locations.insert(0, 20)\n",
    "    if locations[0] != 20:\n",
    "        locations.remove(20)\n",
    "        locations.insert(0, 20)\n",
    "         \n",
    "    # Initialize a matrix for the values\n",
    "    num_locs = len(locations)  # no.of locations to service\n",
    "    time_matrix = np.zeros((num_locs, num_locs), dtype=int)\n",
    "    \n",
    "    # List of indices to use for inserting travel times\n",
    "    indices = list(product(locations, locations))\n",
    "    # Indices to map to in new array\n",
    "    new_indices = list(product(range(num_locs), range(num_locs)))\n",
    "    \n",
    "    # Insert travel times into matrix\n",
    "    for (i,j), (u,v) in zip(new_indices, indices):\n",
    "        time_matrix[i, j] = travel_times.loc[u, v]\n",
    "    \n",
    "    return time_matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "def makeTimeMatrices(df=travel_times, schedule=demands):\n",
    "    '''\n",
    "    Create time matrices for all days in the schedule\n",
    "    '''\n",
    "    time_mtrx_by_day = dict()\n",
    "    \n",
    "    for day in list(schedule.keys()):\n",
    "        time_mtrx_by_day[day] = makeTimeMtrxForDay(day)\n",
    "        \n",
    "    return time_mtrx_by_day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br> Create the daily time matrices and store in a dictionary:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Wed': array([[  0, 169, 148, ..., 280, 279, 279],\n",
       "        [169,   0,  28, ..., 184, 181, 183],\n",
       "        [148,  28,   0, ..., 156, 154, 154],\n",
       "        ...,\n",
       "        [280, 184, 156, ...,   0,  10,  10],\n",
       "        [279, 181, 154, ...,  10,   0,   3],\n",
       "        [279, 183, 154, ...,  10,   3,   0]]),\n",
       " 'Fri': array([[  0,  69,  58, ..., 264, 280, 282],\n",
       "        [ 69,   0,  25, ..., 234, 250, 252],\n",
       "        [ 58,  25,   0, ..., 216, 232, 232],\n",
       "        ...,\n",
       "        [264, 234, 216, ...,   0,  34,  36],\n",
       "        [280, 250, 232, ...,  34,   0,   1],\n",
       "        [282, 252, 232, ...,  36,   1,   0]]),\n",
       " 'Thu': array([[  0,  46,  64, ..., 270, 264, 279],\n",
       "        [ 46,   0,   9, ..., 226, 220, 235],\n",
       "        [ 64,   9,   0, ..., 225, 217, 232],\n",
       "        ...,\n",
       "        [270, 226, 225, ...,   0,  24,  22],\n",
       "        [264, 220, 217, ...,  24,   0,  22],\n",
       "        [279, 235, 232, ...,  22,  22,   0]]),\n",
       " 'Tue': array([[  0,  21,  12, ..., 270, 282, 279],\n",
       "        [ 21,   0,   9, ..., 255, 265, 264],\n",
       "        [ 12,   9,   0, ..., 262, 274, 271],\n",
       "        ...,\n",
       "        [270, 255, 262, ...,   0,  15,  22],\n",
       "        [282, 265, 274, ...,  15,   0,  12],\n",
       "        [279, 264, 271, ...,  22,  12,   0]]),\n",
       " 'Mon': array([[  0,  73,  64, ..., 219, 249, 265],\n",
       "        [ 73,   0,  15, ..., 159, 189, 205],\n",
       "        [ 64,  15,   0, ..., 172, 202, 220],\n",
       "        ...,\n",
       "        [219, 159, 172, ...,   0,  42,  58],\n",
       "        [249, 189, 202, ...,  42,   0,  21],\n",
       "        [265, 205, 220, ...,  58,  21,   0]])}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_matrices = makeTimeMatrices()\n",
    "time_matrices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br> <h3><u>Save Files</u></h3>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save the prepared objects for use in the solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "gp_data = [time_matrices, \n",
    "           demands, \n",
    "           travel_times, \n",
    "           daily_orders, \n",
    "           daily_total_dels, \n",
    "           daily_total_vols]\n",
    "\n",
    "with open('pickle/gp_data.pickle', 'wb') as f:\n",
    "    pickle.dump(gp_data, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "8e352899cd42346537935e3ff01ccb0e7161997dae885d9db9bd98bc6ae73d2b"
  },
  "kernelspec": {
   "display_name": "Python 3.8.10 64-bit ('dpln_env': conda)",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}