UseCases/Vehicle_Routing_Delivery/Vehicle_Routing_Delivery_STO_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cd19cbef-86bc-4f7a-ab62-e8b238dcd356",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Vehicle Routing and Delivery using STO(Script Table Operator)\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6948a41e-a152-4f6a-bf99-7f0addd929a9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "This demo walks through a vehicle routing problem, we have a vehicle moving from point A to point B. Between these two points, there are several routes. The goal is to find the best of these routes. However, in real world scenario, we often would have not just one vehicle, but a fleet of vehicles and set locations.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "The Problem Statement is to find optimal (lowest cost) routes for a fleet of trucks to deliver packages to their destinations.\n",
    "The Questions that need to be answered are:\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>What combination of packages should be put on which truck? </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Is it better to fully load a truck or divide over more? </li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>How to plan the routes?</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> In this demo we model this problem using a Mixed Integer Linear Programming (MILP) framework which has a linear objective function, linear constraints and a mixture of non-negative and binary decision variables. The problem is formulated in Python using pulp library and cbc solver was used to solve it. We use the Script operator from teradataml to use STO, the parameters are set according to requirements. The output of the STO is used for further analysis such as solution validation and network diagrams. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0750eef3-f561-4fe7-8f30-25079f3974ea",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to the Vantage.</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2139108-3df7-4230-bc69-84c3472b60dc",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the section, we import the required libraries and set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bff1277d-b5a1-41cf-81e8-b963bb2d4aae",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install pulp==2.4.0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69f71d13-5265-4e29-9d89-46aec3b4a99a",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>The above statements may need to be uncommented if you run the notebooks on a platform other than ClearScape Analytics Experience that does not have the libraries installed. If you uncomment those installs, be sure to restart the kernel after executing those lines to bring the installed libraries into memory. The simplest way to restart the Kernel is by typing zero zero: <b> 0 0</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14cec5b2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:05:20.016950Z",
     "start_time": "2023-07-20T13:05:14.218169Z"
    }
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import getpass\n",
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "import numpy as np\n",
    "# from pulp import *\n",
    "import teradatasql\n",
    "from teradataml.context.context import *\n",
    "from teradataml.table_operators.sandbox_container_util import setup_sandbox_env, cleanup_sandbox_env\n",
    "from collections import OrderedDict,defaultdict\n",
    "from teradatasqlalchemy import (VARCHAR, INTEGER, FLOAT)\n",
    "from teradataml.table_operators.Script import *\n",
    "from teradataml import *\n",
    "from teradataml.options.display import display\n",
    "import networkx as nx\n",
    "import matplotlib.pyplot as plt\n",
    "warnings.filterwarnings(\"ignore\", category=DeprecationWarning)\n",
    "# display.print_sqlmr_query = True\n",
    "display.max_rows=5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e947e1c3-019a-4c0d-b6c4-c7edf715ce02",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, then use down arrow to go to next cell. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f5f86c2-c479-447d-b92c-ab5090569272",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25ffb024-73d7-444d-934f-369b9548ac7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Vehicle_Routing_Delivery_STO_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10110794",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:05:44.137588Z",
     "start_time": "2023-07-20T13:05:44.134412Z"
    }
   },
   "outputs": [],
   "source": [
    "warnings.filterwarnings(\"ignore\", category=DeprecationWarning)\n",
    "display.print_sqlmr_query = True"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4916255d-c599-4f8a-968f-63d26edcd131",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo we are generating synthetic data. The data generated contains data for Trucks and Packages.</p>   \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "240fea84",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the first step we randomly generate data of 10 trucks with volume capacity ranging from 5.7m${^3}$ to 9.8m${^3}$ (typical delivery truck size) .We are using the random function to get the volume and weight for each truck.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "625b1283",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:05:47.526475Z",
     "start_time": "2023-07-20T13:05:47.520469Z"
    }
   },
   "outputs": [],
   "source": [
    "n = 10 # n trucks\n",
    "np.random.seed(1)\n",
    "\n",
    "# truck max volumes in cm3 - converted from m^3\n",
    "truck_vol = np.random.randint(5.7*1000000,9.8*1000000, n) \n",
    "\n",
    "truck_ids = np.arange(1, n+1)\n",
    "\n",
    "# truck weight capacities in lbs\n",
    "max_wt_lbs = np.random.randint(1980, 2640, n) \n",
    "\n",
    "stack = np.stack([truck_ids, truck_vol, max_wt_lbs], axis=1)\n",
    "df = pd.DataFrame.from_records(stack)\n",
    "df.columns = ['truck_id', 'max_vol_cm3', 'max_wt_lbs']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d876818f-fe87-496f-93fb-2056fc710514",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We store this generated data for Trucks in a table in Vantage.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0600b46a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:06:09.755106Z",
     "start_time": "2023-07-20T13:05:48.140063Z"
    }
   },
   "outputs": [],
   "source": [
    "copy_to_sql(df, table_name='TRUCKS', if_exists = 'replace') # push to Vantage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "978490d9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:06:10.208997Z",
     "start_time": "2023-07-20T13:06:09.758343Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = '''sel cast(truck_id as int) as truck_id,cast(max_vol_cm3 as int) as max_vol_cm3 ,cast(max_wt_lbs as int) \n",
    "as max_wt_lbs from trucks;'''\n",
    "\n",
    "truck_df = pd.read_sql_query(query, eng)\n",
    "truck_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77c6d998-3b12-4198-b2ba-9dc224ef5db7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the next step we generate data for packages.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>First we create different zones where the packages are to be delivered.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12445eae",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:06:10.218880Z",
     "start_time": "2023-07-20T13:06:10.211155Z"
    }
   },
   "outputs": [],
   "source": [
    "# Number of packages to be delivered in each zone\n",
    "zones = np.concatenate([np.repeat('A', 15), np.repeat('B', 20), \n",
    "                        np.repeat('C', 25), np.repeat('D', 20),\n",
    "                        np.repeat('E', 10), np.repeat('F', 15), \n",
    "                        np.repeat('G', 20), np.repeat('H', 25),\n",
    "                        np.repeat('J', 20), np.repeat('T', 10)])\n",
    "np.random.shuffle(zones)\n",
    "zones"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6662abe4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Then we create packages with different volumes using the random function and then assign the zones created above to each of these packages.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "128cbc88",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:06:10.228424Z",
     "start_time": "2023-07-20T13:06:10.222760Z"
    }
   },
   "outputs": [],
   "source": [
    "np.random.seed(1)\n",
    "p = len(zones) # p packages\n",
    "pkg_id = np.arange(1, p+1)\n",
    "pkg_zone = zones\n",
    "\n",
    "# Typical 2kg pkg max dims are 90x60x60 = 324000.\n",
    "# Assume enough space for all pkgs, hence 200k mean\n",
    "pkg_vol = np.random.normal(200000, 25000, p).astype(int)\n",
    "\n",
    "pkg_stack = np.stack([pkg_id, pkg_zone, pkg_vol], axis=1)\n",
    "pdf = pd.DataFrame.from_records(pkg_stack)\n",
    "pdf.columns = ['pkg_id', 'pkg_zone', 'pkg_vol_cm3']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf85bca5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:06:10.234985Z",
     "start_time": "2023-07-20T13:06:10.230709Z"
    }
   },
   "outputs": [],
   "source": [
    "pdf['pkg_id'] = pdf['pkg_id'].astype(int)\n",
    "pdf['pkg_vol_cm3'] = pdf['pkg_vol_cm3'].astype(int)\n",
    "pdf['pkg_zone'] = pdf['pkg_zone'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33622db8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:06:10.246828Z",
     "start_time": "2023-07-20T13:06:10.237042Z"
    }
   },
   "outputs": [],
   "source": [
    "pkg_df = pdf.copy()\n",
    "pkg_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a28dc9e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:06:29.731922Z",
     "start_time": "2023-07-20T13:06:10.248941Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "copy_to_sql(pkg_df, table_name= 'PACKAGES', if_exists = 'replace')\n",
    "pkg_df =pd.read_sql_query('SEL * FROM PACKAGES;', eng)\n",
    "pkg_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb904114-39ce-4a49-85f6-3233eb32f3b7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the next step we will create links between the Zone and assign a cost associated with the transport from one zone to other.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create a table for storing this data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5e1a982",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:06:33.242325Z",
     "start_time": "2023-07-20T13:06:29.734271Z"
    }
   },
   "outputs": [],
   "source": [
    "query = '''CREATE Multiset TABLE zone_links (\n",
    "link_id INTEGER,\n",
    "node_i VARCHAR(5),\n",
    "node_j VARCHAR(5),\n",
    "cost INTEGER\n",
    ");'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "\n",
    "except:\n",
    "    db_drop_table('zone_links')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae6918cf",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:06:33.746231Z",
     "start_time": "2023-07-20T13:06:33.244963Z"
    }
   },
   "outputs": [],
   "source": [
    "query = '''\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (1,'O','A',2);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (2,'O','B',5);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (3,'O','C',4);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (4,'A','B',2);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (5,'A','D',7);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (6,'B','C',1);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (7,'B','D',4);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (8,'B','E',3);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (9,'C','E',4);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (10,'D','E',1);\n",
    "\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (12,'E','F',1);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (13,'E','G',2);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (14,'E','H',3);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (15,'E','J',4);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (16,'D','F',5);\n",
    "\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (17,'F','T',4);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (18,'H','T',5);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (19,'D','T',5);\n",
    "INSERT INTO zone_links (link_id, node_i, node_j, cost) VALUES (20,'E','T',7);'''\n",
    "\n",
    "execute_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "268cc8da-e5ca-42c1-a6bf-685cb213696c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:06:34.322723Z",
     "start_time": "2023-07-20T13:06:33.752439Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "z = pd.read_sql_query(\"SEL * FROM zone_links order by link_id\", eng)\n",
    "z"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5ef0db7-1952-410a-a5c5-974dc1d3ec18",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the next step we will concatenate all the 3 tables to send to the STO.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55895cba-6ce5-496f-a74f-6604feeba6db",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:06:34.355438Z",
     "start_time": "2023-07-20T13:06:34.326073Z"
    }
   },
   "outputs": [],
   "source": [
    "#STO can only be given one table as input, so concatenating all tables\n",
    "df_final = pd.concat([truck_df,pkg_df, z],axis=1,sort=False)\n",
    "df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f3f206b-a35a-4a11-8fd1-9715b188219a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:06:52.754490Z",
     "start_time": "2023-07-20T13:06:34.358352Z"
    }
   },
   "outputs": [],
   "source": [
    "copy_to_sql(df_final, table_name='actual_table', index = False, if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66da66ca-5a06-41a9-9261-309c14321ee9",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Create the best route for trucks using Mixed Integer Linear Programming (MLIP) in python.<b style = 'font-size:14px;font-family:Arial;color:#00233C'>(MILP_continuous.py).</b></p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The  python file take table as input which includes information about packages , truck and the network. These values are used to set up a mixed integer linear programming (MILP) problem in pulp library. The objective function and constraints are defined in pulp as well. The problem is solved and the result is returned as a table.<p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Below is the explanation for the python code which will be executed using STO. In the python file we first create a binary matrix of the packages and Zones</p>    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29219fcc-b03c-43b5-bb9e-de76ebe4028a",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Create matrix of packages and zones</b></p>\n",
    "\n",
    "```python \n",
    "#Delivery binary matrix of package and zones\n",
    "\n",
    "D = pkg_df.copy()\n",
    "\n",
    "D = pd.get_dummies(data=D, columns=['pkg_id', 'pkg_zone']) #.astype(int) # .set_index('pkg_id')\n",
    "\n",
    "```  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd169c3b-69df-40c4-892b-61c56ba1c818",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We create the zone links and nodes to connect the zones</p>    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a99c37bf-461c-4f65-b277-72a49a0459e0",
   "metadata": {},
   "source": [
    "```python \n",
    "#Delivery binary matrix of package and zones\n",
    "z2 = pd.DataFrame()\n",
    "# create reverse links for two-way traversing and append\n",
    "z2['node_i'] = z['node_j'] \n",
    "z2['node_j'] = z['node_i']\n",
    "z2['cost'] = z['cost']\n",
    "z2['link_id'] = np.arange(len(z)+1, len(z)+len(z)+1)\n",
    "\n",
    "zone_links = z.append(z2).reset_index(drop=True)\n",
    "zone_links.sort_values(by='link_id')\n",
    "\n",
    "#Nodes that require deliveries\n",
    "nodes = list(np.unique(zone_links.node_i.tolist() + zone_links.node_j.tolist()))\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d082b44-0b03-4c22-b263-6ab8687f37e2",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Assign packages to Trucks and create data for each truck.</b></p>\n",
    "\n",
    "```python\n",
    "# packages and trucks\n",
    "t_ids = truck_df['truck_id'].tolist()\n",
    "p_ids = pkg_df['pkg_id'].tolist()\n",
    "\n",
    "pkg_vols = dict(zip(pkg_df['pkg_id'].tolist(), pkg_df['pkg_vol_cm3'].tolist()))\n",
    "truck_vols = dict(zip(truck_df['truck_id'].tolist(), truck_df['max_vol_cm3'].tolist()))\n",
    "pt_assign = [(p,t) for p in p_ids for t in t_ids]\n",
    "\n",
    "# Create data for each truck\n",
    "for t in t_ids:\n",
    "    exec(f'zone_links_t{t} = zone_links.copy()')\n",
    "    exec(f\"zone_links_t{t}['node_i'] = zone_links['node_i']+'{t}'\")\n",
    "    exec(f\"zone_links_t{t}['node_j'] = zone_links['node_j']+'{t}'\")\n",
    "    \n",
    "    exec(f\"node_i{t} = zone_links_t{t}['node_i'].tolist()\")\n",
    "    exec(f\"node_j{t} = zone_links_t{t}['node_j'].tolist()\")\n",
    "    exec(f\"arcs_t{t} = list(zip(node_i{t}, node_j{t}))\")\n",
    "    \n",
    "    exec(f\"arcData_t{t} = dict(zip(arcs_t{t}, (zone_links_t{t}.loc[i,['cost']].tolist() for i in zone_links_t{t}.index)))\")\n",
    "    exec(f\"costs_t{t} = splitDict(arcData_t{t})[0]\")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca6107d0-959c-42a4-ba7e-5a6c7be9d5aa",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Create the LpVariable using the package and the truck assignment data and then create the LpProblem variable.</b></p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3df95fc6-d4ba-4218-a542-d5c55a953f7c",
   "metadata": {},
   "source": [
    "```python\n",
    "# pkg and truck assignment variable\n",
    "pt_assign_var = LpVariable.dicts(\"Assign pkg truck\", pt_assign, cat=LpBinary, lowBound=0, upBound=1)\n",
    "    \n",
    "for t in t_ids:\n",
    "    # assignment var pkg-truck\n",
    "    exec(f\"P{t} = [pt_assign_var[(i, {t})] for i in p_ids]\") \n",
    "    # Flow at Origin - Production nodes - sum of all pkgs assigned to a truck\n",
    "    exec(f\"B{t} = lpSum(P{t})\")\n",
    "    # Creates the selector/binary variables on each segment for each truck\n",
    "    exec(f\"selector_vars_t{t} = LpVariable.dicts(\\\"Route_select_t{t}\\\", arcs_t{t}, cat=LpBinary, lowBound=0)\")\n",
    "    # Z vars \n",
    "    exec(f\"Z_var_t{t} = LpVariable.dicts(\\\"Z_{t}\\\", arcs_t{t}, cat=LpInteger, lowBound=0)\")\n",
    "    # Consumption variables \n",
    "    for n in nodes:\n",
    "        exec(f\"F{n}{t} = lpSum((D['pkg_zone_{n}'] * P{t}))*-1\") # Diz * Pij \n",
    "        \n",
    "# Create total demand at nodes variable \n",
    "F = defaultdict(str)\n",
    "for n in nodes:\n",
    "    for t in t_ids:\n",
    "        exec(f\"a = F{n}{t}\")\n",
    "        F[n, t] += a\n",
    "        \n",
    "# Creates the 'prob' variable to contain the problem data \n",
    "prob = LpProblem(\"Min Cost Flow Problem\", LpMinimize)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a80fb4b3-c7ee-497b-971a-cba5a38c0d23",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Each package is than assigned to a truck based on the volume of the package and the volume of the truck.</b></p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7df71bbc-15d4-4a64-a149-f6dc0fed6dfc",
   "metadata": {},
   "source": [
    "```python\n",
    "# Each package needs to be assigned to only one truck of all\n",
    "for i in p_ids:\n",
    "    prob += lpSum([pt_assign_var[(i,t)] for t in t_ids]) == 1\n",
    "\n",
    "M = 100\n",
    "# print(prob.variables())\n",
    "\n",
    "for t in t_ids:\n",
    "    # Volumes of all pkgs on a truck <= Vol of truck\n",
    "    prob += lpSum([pt_assign_var[(i,t)] * pkg_vols[i] for i in p_ids]) <= truck_vols[t] \n",
    "```    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07f35da0-c339-4f0f-b3f8-c15525a8dbe7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>The objective function of the LpProblem is created and is solved using the Pulp's choice of solver.</b></p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f78b644-7b41-48d8-836f-bf2fcadc1f47",
   "metadata": {},
   "source": [
    "```python\n",
    "# Creates the objective function\n",
    "\n",
    "# Flows and costs of all trucks\n",
    "prob += lpSum([flows_dict[a] * costs_dict[a] for a in arcs_list]), \"Total Cost of Transport\"\n",
    "\n",
    "# The problem is solved using PuLP's choice of Solver\n",
    "LpSolverDefault.msg = 0\n",
    "prob.solve()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6450e34-99e1-4bd7-87f5-f630fcce8a6f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Finally, the output which is the variable and the cost assigned to the variable are passed as output of the script which is returned to the STO function as a table</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4fe4bf7c-228c-4067-9817-f3b75c67d813",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Using Script Command to get the forecasted values back to Vantage.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3963392-c829-4e89-88e6-1ebd448c108f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The SCRIPT COMMAND requires the below elements \n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>ON clause: </b> The SCRIPT function can have only one ON clause (single input). The ON clause can be specified with no options or with: HASH BY, PARTITION BY, PARTITION BY ANY, an optional ORDER BY or LOCAL ORDER BY clause</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>SCRIPT_COMMAND: </b>\n",
    "The script to be executed. The SCRIPT_COMMAND is a required keyword.\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>runtime_literal_command: </b>The parameters to SCRIPT_COMMAND can be an executable name followed by the script name and other inputs, or any valid LINUX command.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'><b>RETURNS: </b>\n",
    "    The names and types of the output columns returned by the script. <b>* </b>Specifies that all columns of the input table should be returned by the SCRIPT function.</li>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>First we will create a dataset which can be passed to the Script function.</p>    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0440c190-0841-4d83-966a-288fdc7b6f19",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_table_df2 = DataFrame.from_query('''SELECT 1 as v_id, cast(case when truck_id IS NULL then 0 else truck_id end as Int) as truck_id,\n",
    "case when max_vol_cm3 IS NULL then 0 else max_vol_cm3 end as max_vol_cm3, \n",
    "case when max_wt_lbs IS NULL then 0 else max_wt_lbs end as max_wt_lbs,pkg_id,pkg_zone,pkg_vol_cm3,\n",
    "cast(case when link_id IS NULL then 0 else link_id end as int) as link_id ,\n",
    "case when node_i IS NULL then 'None' else node_i end as node_i ,\n",
    "case when node_j IS NULL then 'None' else node_j end as node_j ,\n",
    "case when cost IS NULL then 0 else cost end as cost FROM actual_table;''')\n",
    "final_table_df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e558f954-3f01-45da-b653-093d63584d4b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>First we set the Database for execution to the user database: demo_user in this case</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d6c86d7-2609-4524-8864-e29e88b0366d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "database_name = 'demo_user'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a81b72b5-949c-43f6-ae46-16cfa3b6dbe2",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Set the search path to the database where the file is installed</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "473f0ecd-1d7b-4af6-8647-cad179f9b49c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "execute_sql(f\"SET SESSION SEARCHUIFDBPATH = {database_name};\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b7b7b27-9f9c-4088-808c-f96640130773",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Install the python file for sto execution.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48166bea-b645-42cd-af90-0f283d6db2ab",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Create the STO object by passing the dataframe with required data, the python file, the delimiter, the return variables along with the partition and order columns.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9d0c831-5214-479f-9f1f-a33988db85e9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sto = Script(data = final_table_df2, script_name='MILP_continuous.py',\n",
    "             files_local_path= r\".\",\n",
    "             script_command= f'tdpython3 ./{database_name}/MILP_continuous.py', \n",
    "                         delimiter = \"\\t\", nulls_first = False,\n",
    "                         returns={\"Variable\":VARCHAR(50), \"Value1\": VARCHAR(50)}, # }, #,  \"Variable\":VARCHAR(50)\n",
    "                         data_order_column = \"pkg_id\", #charset='latin',\n",
    "                        data_partition_column=\"v_id\",\n",
    "                         is_local_order = False, sort_ascending=False)\n",
    "\n",
    "try:\n",
    "    sto.install_file(file_identifier='MILP_continuous', file_name='MILP_continuous.py', is_binary=False)\n",
    "except:\n",
    "    sto.remove_file(file_identifier='MILP_continuous', force_remove=False)\n",
    "    sto.install_file(file_identifier='MILP_continuous', file_name='MILP_continuous.py', is_binary=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25946cc8-d070-4b83-959e-60de0bae327c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Execute the sto command to get the output in dataframe.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "338e17f4-46b4-444f-87cf-e2dbe8319a96",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "DF = sto.execute_script()\n",
    "DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b426de1-5b17-433f-ba3a-ac144e05eeb9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = DF.to_pandas()\n",
    "df = df[~df.Value1.str.contains('None')]\n",
    "df['Value1'] = df['Value1'].astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9911b6a-0b26-41c6-86a8-cf7022d8b827",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Validation of package deliveries.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca8a406f-b3c7-4eb5-992f-8c0a9980e9a4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>After the STO script has executed and returned the table, we do additional verifications and validations on the solution. We verify whether:\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>All packages are delivered</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The packages are delivered to their correct destination</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The truck takes only one out of all possible route at each node</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>The total cost of solution is computed manually and matched</li>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fe2e968-2bcb-4cd6-a1e1-f9c1424f2272",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Assignment validation :-  Check if all packages were assigned to trucks </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7171571-207d-40f0-8a4b-12cc1b43ed74",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:35.391150Z",
     "start_time": "2023-07-20T13:07:35.366280Z"
    }
   },
   "outputs": [],
   "source": [
    "assignments = []\n",
    "df_assignments =df[df['Variable'].str.contains('Assign')& df['Value1']>0]\n",
    "#print(df_assignments.head())\n",
    "#print(re.findall('\\d+',df_assignments['Variable'].str))\n",
    "df_assignments[['Pkg','Truck']] = df_assignments.Variable.astype('str').str.extractall('(\\d+)').unstack().astype(int)\n",
    "df_assignments\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22223ede-f645-4c75-9a06-031c7b7f33f3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:35.401888Z",
     "start_time": "2023-07-20T13:07:35.393046Z"
    }
   },
   "outputs": [],
   "source": [
    "for t in np.unique(df_assignments['Truck']):\n",
    "    exec(f\"Ps_on_T{t} = df_assignments[df_assignments.Truck=={t}].Pkg.to_list()\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac5bdbd9-5548-4b74-b846-eb858a5b1671",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:35.411827Z",
     "start_time": "2023-07-20T13:07:35.404063Z"
    }
   },
   "outputs": [],
   "source": [
    "df_assignments[['Truck', 'Pkg']].groupby('Truck').count() "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e4ac4d7-ec81-4b13-a49d-245d4dace165",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Flows Validation :- Check if the trucks take the shortest route </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b807a33-17ee-4f60-9a3b-c6785637c5a5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:35.426311Z",
     "start_time": "2023-07-20T13:07:35.414225Z"
    }
   },
   "outputs": [],
   "source": [
    "df_flows =df[df['Variable'].str.contains('Z')& df['Value1']!=0]\n",
    "df_flows.reset_index(drop=True,inplace = True)\n",
    "df_flows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e0fb842-4174-41e7-98dd-09b36ba00a43",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:35.440553Z",
     "start_time": "2023-07-20T13:07:35.428648Z"
    }
   },
   "outputs": [],
   "source": [
    "df_edges =df[df['Variable'].str.contains('select')& df['Value1']!=0]\n",
    "df_edges.reset_index(drop=True,inplace = True)\n",
    "df_edges"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91b47d65-af0e-4a31-bb74-9d433acd0a22",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Total Cost :- Calculate the total cost manually and validate </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6763f911-2b5d-49cf-86ec-2ad4b34b6731",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:35.462107Z",
     "start_time": "2023-07-20T13:07:35.446208Z"
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "#Create z2 from z with node directions inverted\n",
    "\n",
    "z2 = pd.DataFrame()\n",
    "# create reverse links and append\n",
    "z2['node_i'] = z['node_j'] \n",
    "z2['node_j'] = z['node_i']\n",
    "z2['cost'] = z['cost']\n",
    "z2['link_id'] = np.arange(len(z)+1, len(z)+len(z)+1)\n",
    "zone_links = pd.concat([z,z2],axis=0,sort=False)\n",
    "#zone_links\n",
    "\n",
    "\n",
    "total_cost = 0\n",
    "df_flows['key1'] = df_flows.Variable.str.split(\"'\").str[1].str[0]\n",
    "\n",
    "df_flows['key2'] = df_flows.Variable.str.split(\"'\").str[3].str[0]\n",
    "df_flows['cost'] = 0\n",
    "\n",
    "merged_df = df_flows.merge(zone_links, left_on=['key1', 'key2'], right_on=['node_i', 'node_j'], how='left')\n",
    "merged_df\n",
    "df_flows['cost'] = merged_df['cost_y']\n",
    "df_flows\n",
    "\n",
    "\n",
    "df_flows['total_cost'] = df_flows['cost'].multiply(df_flows['Value1'])\n",
    "print('The total cost is', df_flows['total_cost'].sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "878a24cb-fbf1-465e-bbd5-b49f198e7c51",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Delivery and Demand validation :- Check if all packages tagged were delivered to the actual location.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4bce9472-9cf2-4e57-b22b-0cb922936a6d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:35.469090Z",
     "start_time": "2023-07-20T13:07:35.464257Z"
    }
   },
   "outputs": [],
   "source": [
    "nodes = list(np.unique(zone_links.node_i.tolist() + zone_links.node_j.tolist()))\n",
    "nodes.remove(\"O\")\n",
    "nodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a08e15f1-214a-42f3-b4bc-53cf72ee09ac",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:35.483549Z",
     "start_time": "2023-07-20T13:07:35.471988Z"
    }
   },
   "outputs": [],
   "source": [
    "# Pkg ids belonging to given nodes as per original tagging\n",
    "\n",
    "D = pkg_df.copy()\n",
    "D = pd.get_dummies(D[['pkg_id', 'pkg_zone']]).set_index('pkg_id').astype(int)\n",
    "demands = dict(list(zip(sorted(nodes), D.sum())))\n",
    "for n in nodes:\n",
    "    exec(f\"{n}_ids = list(D[D['pkg_zone_{n}']==1].index)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6f87614-2260-42d5-ba77-d05aab9b8e2b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:35.500607Z",
     "start_time": "2023-07-20T13:07:35.485670Z"
    }
   },
   "outputs": [],
   "source": [
    "# Check if all pkgs orignially tagged for each node were actually delivered at the node\n",
    "\n",
    "# Dict that contains list of all package ids delivered to a node\n",
    "pkgs_delivered = defaultdict(list)\n",
    "t_ids = truck_df['truck_id'].to_list()\n",
    "for n in nodes:\n",
    "    # original tagging\n",
    "    original_tag = set(D[D[f'pkg_zone_{n}']==1][f'pkg_zone_{n}'].index)\n",
    "    for t in t_ids:\n",
    "        # packages on truck\n",
    "        exec(f\"pkgs_on_truck = set(Ps_on_T{t})\")\n",
    "        # append pkg ids delivered to a node by a truck and was supposed to be\n",
    "        pkgs_delivered[n] += list(original_tag.intersection(pkgs_on_truck))\n",
    "        \n",
    "    exec(f\"print(n, sorted(pkgs_delivered[n]) == {n}_ids)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "503f2a26-86ce-4f58-b682-448db53da374",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:35.525780Z",
     "start_time": "2023-07-20T13:07:35.503665Z"
    }
   },
   "outputs": [],
   "source": [
    "df_flows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4d5c429-d793-4a67-ad7c-8b3368874ef2",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Demand check :- Check if the Demand and the Net flow is the same</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3abe950-466f-4070-879d-32ccfd1aada0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:35.549688Z",
     "start_time": "2023-07-20T13:07:35.528445Z"
    }
   },
   "outputs": [],
   "source": [
    "# Demand meeting check\n",
    "\n",
    "\n",
    "df_flows\n",
    "for n in nodes:\n",
    "    \n",
    "    \n",
    "    inflow = df_flows[df_flows['key2'] ==n]['Value1'].sum().astype(int)\n",
    "    outflow = df_flows[df_flows['key1'] ==n]['Value1'].sum().astype(int)\n",
    "    assert(demands[n] <= inflow-outflow)\n",
    "    print(f'{n}:', 'Demand:', demands[n], f'\\n{n}: Net flow:', inflow-outflow)\n",
    "    assert(demands[n] <= inflow-outflow)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "340d4a0f-12b3-43d5-8b36-ce3a2dbc5951",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Visualization of the routes.</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> We create a python function to visualize the routes using the network diagram</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41c2e57e-d679-4714-8785-ca96a8358c12",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:35.573108Z",
     "start_time": "2023-07-20T13:07:35.553427Z"
    }
   },
   "outputs": [],
   "source": [
    "df_flows_new =df[df['Variable'].str.contains('Z')]\n",
    "df_flows_new.reset_index(drop=True,inplace = True)\n",
    "def graph_viz(t):\n",
    "    # Create an empty directed graph\n",
    "    G = nx.DiGraph()\n",
    "    \n",
    "   \n",
    "    \n",
    "    temp_nodes = [node + f'{t}' for node in nodes]\n",
    "    exec(f\"graph_nodes_t{t} = temp_nodes\") #['A1', 'B1', 'C1', 'D1', 'E1', 'O1', 'T1']\n",
    "    #exec(f\"graph_nodes_t{t} = ['A1', 'B1', 'C1', 'D1', 'E1', 'O1', 'T1']\")\n",
    "\n",
    "    exec(f\"G.add_nodes_from(graph_nodes_t{t})\")\n",
    "\n",
    "    # Define the edge labels as a dictionary\n",
    "    edgeLabels = defaultdict(tuple)\n",
    "    for i in range(df_flows_new.shape[0]):\n",
    "        \n",
    "        if(int(df_flows_new.loc[i,'Variable'].split('_')[1]) == t):\n",
    "            \n",
    "        \n",
    "            #print(df_flows_new.loc[i,'Variable'].split(\"'\")[1],df_flows_new.loc[i,'Variable'].split(\"'\")[3],df_flows_new.loc[i,'Value1'])\n",
    "            edgeLabels[(df_flows_new.loc[i,'Variable'].split(\"'\")[1],df_flows_new.loc[i,'Variable'].split(\"'\")[3])] = df_flows_new.loc[i,'Value1']\n",
    "#     for v in prob.variables():\n",
    "#         if f'Z_{t}' in v.name:\n",
    "#             edgeLabels[(v.name.split(\"'\")[1], v.name.split(\"'\")[3])] = v.varValue\n",
    "#         else:\n",
    "#             pass\n",
    "\n",
    "\n",
    "    # Create a set to store the filtered pairs\n",
    "    filtered_pairs = set()\n",
    "    \n",
    "\n",
    "    # If a nonzero pair exists, the reverse-order pair with zero flow needs to be removed \n",
    "    # to avoid overwriting the nonzero flow in edge labels\n",
    "    for pair, value in edgeLabels.items():\n",
    "        reverse_pair = (pair[1], pair[0]) \n",
    "        reverse_value = edgeLabels.get(reverse_pair)\n",
    "        #filtered_pairs.add(pair)\n",
    "        \n",
    "\n",
    "        if (value != 0.0 and reverse_value == 0.0) or (value == 0.0 and reverse_value==0.0):\n",
    "            #print('hello ji')\n",
    "            filtered_pairs.add(pair)\n",
    "            \n",
    "        else:\n",
    "            pass\n",
    "\n",
    "\n",
    "    # update edge labels\n",
    "    edge_labels = {key: edgeLabels[key] for key in list(filtered_pairs)}\n",
    "#     print(edge_labels)\n",
    "\n",
    "    # Add edges to the graph and assign the weights using the edge_labels dictionary\n",
    "    G.add_edges_from(edge_labels.keys())\n",
    "\n",
    "    # Set node color\n",
    "    node_color = 'lightgray'\n",
    "\n",
    "    # Set node size\n",
    "    node_size = 1000\n",
    "\n",
    "    # Define the position for better visualization\n",
    "    seed_value = 4\n",
    "    \n",
    "    pos = {f'O{t}': np.array([-0.64307049,  0.67121434]),\n",
    "     f'G{t}': np.array([ 1., -0.16012511]),\n",
    "     f'A{t}': np.array([-0.90599189,  0.15068216]),\n",
    "     f'H{t}': np.array([ 0.50721255, -0.65769381]),\n",
    "     f'E{t}': np.array([ 0.27184027, -0.07098749]),\n",
    "     f'T{t}': np.array([ 0.05455484, -0.50582441]),\n",
    "     f'J{t}': np.array([0.82960681, 0.39761469]),\n",
    "     f'C{t}': np.array([-0.06881305,  0.60493769]),\n",
    "     f'D{t}': np.array([-0.52304613, -0.20663306]),\n",
    "     f'F{t}': np.array([-0.24173344, -0.51682124]),\n",
    "     f'B{t}': np.array([-0.28055945,  0.29363624])}\n",
    "\n",
    "#     pos = nx.spring_layout(G, seed=seed_value)\n",
    "\n",
    "    # Increase plot size\n",
    "    plt.figure(figsize=(10, 6))  # Adjust the width and height as desired\n",
    "\n",
    "    # Iterate over edges and set edge color for nonzero weights\n",
    "    edge_colors = []\n",
    "\n",
    "    for pair in list(filtered_pairs):\n",
    "        if edge_labels[pair] == 0.0:\n",
    "            edge_colors.append('gray')\n",
    "        else:\n",
    "            edge_colors.append('orange') # Highlight nonzero edges\n",
    "\n",
    "    # Draw the edges with customized colors and edge labels\n",
    "    nx.draw_networkx_edges(G, pos, edgelist=list(filtered_pairs), edge_color=edge_colors)\n",
    "    nx.draw_networkx_edge_labels(G, pos, edge_labels=edge_labels, font_size=12)\n",
    "\n",
    "    # Draw the nodes with customized color and size\n",
    "    nx.draw_networkx_nodes(G, pos, node_color=node_color, node_size=node_size)\n",
    "\n",
    "    # Draw the node labels\n",
    "    nx.draw_networkx_labels(G, pos)\n",
    "\n",
    "    # Plot adjustments\n",
    "    plt.axis('off')\n",
    "    plt.title(label=f\"Delivery Flow of Truck {t}\",\n",
    "              loc=\"left\",\n",
    "              fontstyle='italic',\n",
    "              fontweight=15,\n",
    "              fontsize=15)\n",
    "\n",
    "    # Display the graph\n",
    "    plt.show()\n",
    "#     print(f\"graph_nodes_t{t}\")\n",
    "#     print(G.nodes)\n",
    "    G.clear()\n",
    "#     print(G.nodes)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a85caf9-eb07-42e2-87d5-62926f057a76",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> The below table shows the total number of packages each truck will be delivering to different nodes along the path highlighted in the visual.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a71e70d4-778d-4415-90bf-640622d18985",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_assignments[['Truck', 'Pkg']].groupby('Truck').count() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cce9e07a-f31a-4e72-82a1-40bb294daa59",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-07-20T13:07:39.396199Z",
     "start_time": "2023-07-20T13:07:35.575651Z"
    }
   },
   "outputs": [],
   "source": [
    "for t in t_ids:\n",
    "    graph_viz(t) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd0961ac-1420-4f32-9885-64dca9e7ce99",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above visualization shows the nodes and paths for all the 10 trucks. The bubbles show all the zones available and the edges shows the paths that can be traversed for the delivery of the packages. Based on the assignment of packages and the zones for each truck to be delivered the best route is assigned. The table above the visualization is for the total number of packages each truck is carrying. The orange colored lines show the best route of each truck and the number of packages the truck is carrying while travelling on each path. The path starts at the left most node of the orange line. The truck is carrying the number of packages mentioned on the first line. The number of packages dropped at the first node will be the subtraction of packages mentioned on the 1st and 2nd path, the number of packages dropped on the 2nd node will be subtraction of packages mentioned on 2nd and 3rd path and so on till all packages are dropped by the truck. Similarly, the paths and packages for all trucks are highlighted in orange.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "816344f7-8fb5-4127-9917-523f51694c42",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Conclusion.</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45566135-7c5d-45ae-b5ac-1ef5536baf2f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Thus, using the Mixed Integer Linear Programming(MLIP) function and calling that using the Script Table Operator(STO) we find the optimal route for the Trucks and the total cost involved in the package deliveries. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71b16b6e-ef45-4e0f-a220-b4f70642e86d",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Cleanup</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "405d37e6-559e-4360-bd09-3e4a8a19970b",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['TRUCKS', 'PACKAGES','zone_links', 'actual_table']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    db_drop_table(table_name=table)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81852779-6d20-4e6e-a514-d015bc7b66a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e221a99-f2e4-4755-b748-7bbc693f3323",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.10"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "165px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}