Load_and_route_optimisation.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Load_and_route_optimisation_f.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/nitishymtpl/innovation-agri-hackathon/blob/MTPL-Challenge5/Load_and_route_optimisation.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "In load and route optimisation problems, many different transportation tools such as trucks, airplanes and ships are available. Different choices of routes and transporation tools will lead to different costs. To minimize cost, we should consider goods consolidation (Occassions when different goods share a journey together.), different transportation costs and delivery time constraints etc. This project uses mathematical programming to model such situation and solves for overall cost minimization solution. The model construction offers options of mathematical programming fremwork using CVXPY. CVXPY is an opensource programming framework originally developed by a Stanford research team.\n",
        "\n",
        "**Problem Statement:** In our simulated case, there are 8 goods, 4 arbitary/dummy cities/countries (Gujarat, Mumbai, Singapore, Malaysia), 16 ports and 4 transportation tools. The 8 goods originate from different cities and have different destinations. Each city/country has 4 ports, the airport, railway station, seaport and warehouse. There are in total 50 direct routes connecting different ports. **Please check model.xlsx<data file>.** Each route has a specific transportation tool, transportation cost, transit time and weekly schedule. Warehouse in each city allows goods to be deposited for a period of time so as to fit certain transportation schedules or wait for other goods to be transported together. All goods might have different order dates and different delivery deadlines. With all these criteria, how can we find out solution routes for all goods that minimize the overall operational cost with optimal distribution of load and route?"
      ],
      "metadata": {
        "id": "kLUFg2ph9DPX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install cplex\n",
        "!pip install docplex\n",
        "!pip install cylp\n",
        "!pip install osmnx\n",
        "!pip install networkx\n",
        "from itertools import product\n",
        "import numpy as np\n",
        "import cvxpy as cp\n",
        "import pandas as pd\n",
        "import json"
      ],
      "metadata": {
        "id": "MkMOUY4EVz9-",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "outputId": "bde1b56e-fb25-408b-db47-cd5b0f587560"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting cplex\n",
            "  Downloading cplex-22.1.0.0-cp37-cp37m-manylinux1_x86_64.whl (43.3 MB)\n",
            "\u001b[K     |████████████████████████████████| 43.3 MB 1.2 MB/s \n",
            "\u001b[?25hInstalling collected packages: cplex\n",
            "Successfully installed cplex-22.1.0.0\n",
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting docplex\n",
            "  Downloading docplex-2.23.222.tar.gz (610 kB)\n",
            "\u001b[K     |████████████████████████████████| 610 kB 9.0 MB/s \n",
            "\u001b[?25hRequirement already satisfied: six in /usr/local/lib/python3.7/dist-packages (from docplex) (1.15.0)\n",
            "Building wheels for collected packages: docplex\n",
            "  Building wheel for docplex (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for docplex: filename=docplex-2.23.222-py3-none-any.whl size=662847 sha256=302f434cc6c38e94763a40e5664782bab2dbb5fbbe79ee7e9ca43d8cad0cdc24\n",
            "  Stored in directory: /root/.cache/pip/wheels/a7/c9/fb/cee5a89f304e77a39c466e625ac2830434b76eb8384999d116\n",
            "Successfully built docplex\n",
            "Installing collected packages: docplex\n",
            "Successfully installed docplex-2.23.222\n",
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting cylp\n",
            "  Downloading cylp-0.91.5-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (9.2 MB)\n",
            "\u001b[K     |████████████████████████████████| 9.2 MB 6.1 MB/s \n",
            "\u001b[?25hRequirement already satisfied: scipy>=0.10.0 in /usr/local/lib/python3.7/dist-packages (from cylp) (1.4.1)\n",
            "Requirement already satisfied: numpy>=1.5.0 in /usr/local/lib/python3.7/dist-packages (from cylp) (1.21.6)\n",
            "Installing collected packages: cylp\n",
            "Successfully installed cylp-0.91.5\n",
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting osmnx\n",
            "  Downloading osmnx-1.1.2-py2.py3-none-any.whl (95 kB)\n",
            "\u001b[K     |████████████████████████████████| 95 kB 3.0 MB/s \n",
            "\u001b[?25hRequirement already satisfied: pandas>=1.3 in /usr/local/lib/python3.7/dist-packages (from osmnx) (1.3.5)\n",
            "Requirement already satisfied: networkx>=2.6 in /usr/local/lib/python3.7/dist-packages (from osmnx) (2.6.3)\n",
            "Collecting pyproj>=3.2\n",
            "  Downloading pyproj-3.2.1-cp37-cp37m-manylinux2010_x86_64.whl (6.3 MB)\n",
            "\u001b[K     |████████████████████████████████| 6.3 MB 16.2 MB/s \n",
            "\u001b[?25hRequirement already satisfied: Shapely<2.0,>=1.7 in /usr/local/lib/python3.7/dist-packages (from osmnx) (1.8.2)\n",
            "Collecting geopandas>=0.10\n",
            "  Downloading geopandas-0.10.2-py2.py3-none-any.whl (1.0 MB)\n",
            "\u001b[K     |████████████████████████████████| 1.0 MB 58.2 MB/s \n",
            "\u001b[?25hCollecting Rtree>=0.9\n",
            "  Downloading Rtree-1.0.0-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.0 MB)\n",
            "\u001b[K     |████████████████████████████████| 1.0 MB 54.2 MB/s \n",
            "\u001b[?25hRequirement already satisfied: numpy>=1.21 in /usr/local/lib/python3.7/dist-packages (from osmnx) (1.21.6)\n",
            "Collecting requests>=2.26\n",
            "  Downloading requests-2.28.1-py3-none-any.whl (62 kB)\n",
            "\u001b[K     |████████████████████████████████| 62 kB 1.4 MB/s \n",
            "\u001b[?25hCollecting matplotlib>=3.4\n",
            "  Downloading matplotlib-3.5.2-cp37-cp37m-manylinux_2_5_x86_64.manylinux1_x86_64.whl (11.2 MB)\n",
            "\u001b[K     |████████████████████████████████| 11.2 MB 42.3 MB/s \n",
            "\u001b[?25hCollecting fiona>=1.8\n",
            "  Downloading Fiona-1.8.21-cp37-cp37m-manylinux2014_x86_64.whl (16.7 MB)\n",
            "\u001b[K     |████████████████████████████████| 16.7 MB 29.5 MB/s \n",
            "\u001b[?25hCollecting cligj>=0.5\n",
            "  Downloading cligj-0.7.2-py3-none-any.whl (7.1 kB)\n",
            "Requirement already satisfied: setuptools in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas>=0.10->osmnx) (57.4.0)\n",
            "Requirement already satisfied: attrs>=17 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas>=0.10->osmnx) (21.4.0)\n",
            "Requirement already satisfied: certifi in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas>=0.10->osmnx) (2022.6.15)\n",
            "Requirement already satisfied: six>=1.7 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas>=0.10->osmnx) (1.15.0)\n",
            "Collecting click-plugins>=1.0\n",
            "  Downloading click_plugins-1.1.1-py2.py3-none-any.whl (7.5 kB)\n",
            "Collecting munch\n",
            "  Downloading munch-2.5.0-py2.py3-none-any.whl (10 kB)\n",
            "Requirement already satisfied: click>=4.0 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas>=0.10->osmnx) (7.1.2)\n",
            "Requirement already satisfied: cycler>=0.10 in /usr/local/lib/python3.7/dist-packages (from matplotlib>=3.4->osmnx) (0.11.0)\n",
            "Requirement already satisfied: python-dateutil>=2.7 in /usr/local/lib/python3.7/dist-packages (from matplotlib>=3.4->osmnx) (2.8.2)\n",
            "Requirement already satisfied: kiwisolver>=1.0.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib>=3.4->osmnx) (1.4.3)\n",
            "Requirement already satisfied: packaging>=20.0 in /usr/local/lib/python3.7/dist-packages (from matplotlib>=3.4->osmnx) (21.3)\n",
            "Requirement already satisfied: pillow>=6.2.0 in /usr/local/lib/python3.7/dist-packages (from matplotlib>=3.4->osmnx) (7.1.2)\n",
            "Collecting fonttools>=4.22.0\n",
            "  Downloading fonttools-4.33.3-py3-none-any.whl (930 kB)\n",
            "\u001b[K     |████████████████████████████████| 930 kB 53.1 MB/s \n",
            "\u001b[?25hRequirement already satisfied: pyparsing>=2.2.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib>=3.4->osmnx) (3.0.9)\n",
            "Requirement already satisfied: typing-extensions in /usr/local/lib/python3.7/dist-packages (from kiwisolver>=1.0.1->matplotlib>=3.4->osmnx) (4.1.1)\n",
            "Requirement already satisfied: pytz>=2017.3 in /usr/local/lib/python3.7/dist-packages (from pandas>=1.3->osmnx) (2022.1)\n",
            "Requirement already satisfied: urllib3<1.27,>=1.21.1 in /usr/local/lib/python3.7/dist-packages (from requests>=2.26->osmnx) (1.24.3)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests>=2.26->osmnx) (2.10)\n",
            "Requirement already satisfied: charset-normalizer<3,>=2 in /usr/local/lib/python3.7/dist-packages (from requests>=2.26->osmnx) (2.0.12)\n",
            "Installing collected packages: munch, cligj, click-plugins, pyproj, fonttools, fiona, Rtree, requests, matplotlib, geopandas, osmnx\n",
            "  Attempting uninstall: requests\n",
            "    Found existing installation: requests 2.23.0\n",
            "    Uninstalling requests-2.23.0:\n",
            "      Successfully uninstalled requests-2.23.0\n",
            "  Attempting uninstall: matplotlib\n",
            "    Found existing installation: matplotlib 3.2.2\n",
            "    Uninstalling matplotlib-3.2.2:\n",
            "      Successfully uninstalled matplotlib-3.2.2\n",
            "\u001b[31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.\n",
            "google-colab 1.0.0 requires requests~=2.23.0, but you have requests 2.28.1 which is incompatible.\n",
            "datascience 0.10.6 requires folium==0.2.1, but you have folium 0.8.3 which is incompatible.\n",
            "albumentations 0.1.12 requires imgaug<0.2.7,>=0.2.5, but you have imgaug 0.2.9 which is incompatible.\u001b[0m\n",
            "Successfully installed Rtree-1.0.0 click-plugins-1.1.1 cligj-0.7.2 fiona-1.8.21 fonttools-4.33.3 geopandas-0.10.2 matplotlib-3.5.2 munch-2.5.0 osmnx-1.1.2 pyproj-3.2.1 requests-2.28.1\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "application/vnd.colab-display-data+json": {
              "pip_warning": {
                "packages": [
                  "matplotlib",
                  "mpl_toolkits"
                ]
              }
            }
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Requirement already satisfied: networkx in /usr/local/lib/python3.7/dist-packages (2.6.3)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Assumptions**:\n",
        "\n",
        "Before model building, some assumptions should be made to simplify the case because real-world delivery problems consist of too many unmeasurable factors that can affect the delivery process and final outcomes. Here are the main assumptions:\n",
        "1. The delivery process is deterministic, no random effect will appear on delivery time and cost etc.\n",
        "2. Goods can be transported in normal container, no special containers (refrigerated, thermostatic etc.) will be needed.\n",
        "3. Container only constraints on the good's volume, and all goods are divisible in terms of volume. (No bin packing problem needed to be considered.)\n",
        "4. The model only evaluates the major carriage routes. The first and last mile between end user and origin/destination shipping point are not considered. (From warehouse to warehouse.)\n",
        "5. There is only one transportation tool available between each two ports. For instance, we can only directly go from one airport to the other airport in different cities by flight, while direct journey by ship or railway or truck is infeasible.\n",
        "6. Overall cost is restricted to the most important 3 parts, transportation cost, warehouse cost and goods tariff.\n",
        "7. The minimum unit for time is day in the model, and there is at most one transit in a route in one day."
      ],
      "metadata": {
        "id": "7ybvq3mq8937"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Dimension \\& Matrixing**:\n",
        "\n",
        "In order to make the criteria logic clearer and the calculation more efficient, we use the concept of matrixing to build the necessary components in the model. In our case, there are totally 4 dimensions:\n",
        "1. Start Port: $i$\n",
        "Indicating the start port of a direct transport route. The dimension length equals the total number of ports in the data.\n",
        "2. End Port: $j$\n",
        "Indicating the end port of a direct transport route. The dimension length equals the total number of ports in the data.\n",
        "3. Time: $\\boldsymbol{t}$\n",
        "Indicating the departure time of a direct transport. The dimension length equals the total number of days between the earliest order date and the latest delivery deadline date of all goods in the data.\n",
        "4. Goods: $k$\n",
        "Indicating the goods to be transported. The dimension length equals the total number of goods in the data.\n",
        "All the variable or parameter matrices to be introduced in the later parts will have one or more of these 4 dimensions."
      ],
      "metadata": {
        "id": "bl8QtfvG1eMK"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Decision Variables**:\n",
        "\n",
        "As mentioned above, we will use the concept of variable matrix, a list of variables deployed in the form of a matrix or multi-dimensional array. In our model, 3 variable matrices will be introduced:\n",
        "1. Decision Variable Matrix: $X$\n",
        "\n",
        "The most important variable matrix in the model. It's a 4 dimensional matrix, each dimension representing start port, end port, time and goods respectively. Each element in the matrix is a binary variable, representing whether a route is taken by a specific goods. For example, element $X_{i, j, t, k}$ represents whether goods $k$ travels from port i to port $j$ at time $t$.\n",
        "2. Container Number Matrix: $Y$\n",
        "\n",
        "A variable matrix used to support the decision variable matrix. It's a 3 dimensional matrix, with each dimension representing start port, end port and time respectively. Each element in the matrix is an integer variable, representing the number of containers needed in a specific route. For example, $Y_{i, j, t}$ represents the number of containers needed to load all the goods travelling simultaneously from port i to port $j$ at time $t$. Such matrix is introduced to make up for the limitation of \"linear operator only\" in mathematical programming, when we need a roundup() method in direct calculation of the container number.\n",
        "3. Route Usage Matrix: $Z$\n",
        "\n",
        "A variable matrix used to support the decision variable matrix. It's a 3 dimensional matrix, with each dimension representing start port, end port and time respectively. Each element in the matrix is a binary variable, representing whether a route is used or not. For instance, $Z_{i, j, t}$ represents whether the route from port $i$ to port $j$ at time $t$ is used or not (no matter which goods). It's introduced with similar purpose to $Y_{i, j, t}$.\n",
        "\n",
        "\n",
        "**Parameters**:\n",
        "\n",
        "Similar to the decision variables, the following parameter arrays or matrices are introduced for the sake of later model building:\n",
        "1. Per Container Cost: $\\quad$ C\n",
        "\n",
        "A 3 dimensional parameter matrix, each dimension representing start port, end port and time. $C_{i, j, t}$ in the matrix represents the overall transportation cost per container from port $i$ to port $j$ at time $t$. This overall cost includes handling cost, bunker/fuel cost, documentation cost, equipment cost and extra cost from model data.xlsx. For infeasible route, the cost element will be set to be big $\\mathrm{M}$ (an extremely large number), making the choice infeasible.\n",
        "2. Route Fixed Cost: $\\quad$ FC\n",
        "\n",
        "A 3 dimensional parameter matrix, each dimension representing start port, end port and time. $F C_{i, j, t}$ in the matrix represents the fixed transportation cost to travel from port $i$ to port $j$ at time $t$, regardless of goods number or volume. For infeasible route, the cost element will be set to be big $M$ as well.\n",
        "3. Warehouse Cost: $\\quad$ wh\n",
        "\n",
        "A one dimension array with dimension start port. $w h_{i}$ represents the warehouse cost per cubic meter per day at port i. Warehouse cost for ports with no warehouse function (like airport, railway station etc.) is set to be big M.\n",
        "4. Transportation Time: $T$\n",
        "\n",
        "A 3 dimensional parameter matrix, each dimension representing start port, end port and time. $T_{i j, t}$ in the matrix represents the overall transportation time from port $i$ to port $j$ at time $t$. This overall time includes custom clearance time, handling time, transit time and extra time from model data.xlsx. For infeasible route, the time element will be set to be big $M$.\n",
        "5. Tax Percentage: tax\n",
        "\n",
        "A one dimension array with dimension goods. tax ${ }_{k}$ represents the tax percentage for goods $k$ imposed by its destination country. If the goods only goes through a domestic transit, the tax percentage for such goods will be set as $0 .$\n",
        "6. Transit Duty: $td$ \n",
        "\n",
        "A two dimensional matrix, each dimension representing start port and end port. $t d_{i, j}$ represents the transit duty (tax imposed on goods passing through a country) percentage for goods to go from port $i$ to port $j$. If port i and port $j$ belong to the same country, transit duty percentage is set to be 0 . For simplicity purpose, transit duty is set to be equal among all goods. (can be extended easily)\n",
        "7. Container Volume: $ctnV$\n",
        "\n",
        "A two dimensional matrix, each dimension representing start port and end port. $c t n V_{i, j}$ represents the volume of container in the route from port $i$ to port $j$.\n",
        "8. Goods Volume: $V$\n",
        "\n",
        "A one dimension array with dimension goods. $V_{k}$ represents the volume of goods $k$.\n",
        "9. Goods Value: val\n",
        "\n",
        "10. Order Date: ord\n",
        "\n",
        "A one dimension array with dimension goods. ord $k$ represents the order date of goods $k$.\n",
        "11. Deadline Date: $d d l$\n",
        "\n",
        "A one dimension array with dimension goods. $d d l_{k}$ represents the deadline delivery date of goods $k$.\n",
        "12. Origin Port: $O P$\n",
        "\n",
        "A one dimension array with dimension goods. $O P_{k}$ represents the port where goods $\\mathrm{k}$ starts from.\n",
        "13. Destination Port: $D P$\n",
        "\n",
        "A one dimension array with dimension goods. $D P_{k}$ represents the port where goods $k$ ends up to be in.\n",
        "The data of all the above parameter matrices will be imported from model data.xIsx with function transform() and set_param().\n"
      ],
      "metadata": {
        "id": "n3L65gDl2FUO"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Mathematical Modelling**:\n",
        "\n",
        "With all the variables and parameters defined above, we can build up the objectives and constraints to form an integer programming model.\n",
        "Objective\n",
        "The objective of the model is to minimize the overall cost, which includes 3 parts, transportation cost, warehouse cost and tax cost. Firstly, the transportation cost includes container cost and route fixed cost. Container cost equals the number of containers used in each route times per container cost while route fixed cost equals the sum of fixed cost of all used routes. Secondly, the warehouse cost equals all goods' sum of volume times days of storage times warehouse fee per cubic meter per day in each warehouse. Finally, the tax cost equals the sum of import tariff and transit duty of all goods. \n",
        "\n",
        "Mathematic formulation are attached below.\n",
        "\n",
        "$\\min _{x, y, z}$ Transportation Cost $+$ Warehouse Cost $+$ Tax Cost\n",
        "\n",
        "Transportation Cost $=\\sum_{t} \\sum_{j} \\sum_{i} Y_{i, j, t} \\times C_{i, j, t}+Z_{i, j, t} \\times F C_{i, j, t}$\n",
        "\n",
        "Warehouse Cost $=\\sum_{i}\\left[\\sum_{k}\\left(\\sum_{j} \\sum_{t} t \\times x_{i, j, t, k}\\right) \\times V_{k}\\right] \\times w h_{i}-\\sum_{j}\\left\\{\\sum_{k}\\left[\\sum_{i} \\sum_{t}\\left(t+T_{i, j, t}\\right) \\times x_{i, j, t, k}\\right] \\times V_{k}\\right\\} \\times w h_{j}$\n",
        "$$\n",
        "\\operatorname{Tax} \\text { Cost }=\\sum_{k} \\operatorname{tax}_{k} * \\operatorname{val}_{k}+\\sum_{i} \\sum_{j}\\left(\\sum_{t} \\sum_{k} x_{i, j, t, k} \\times v a l_{k}\\right) \\times t d_{i, j}\n",
        "$$\n",
        "\n",
        "\n",
        "**Constraints**:\n",
        "\n",
        "1. For each goods $k$, it must be shipped out from its origin to another node and shipped to its destination.\n",
        "$$\n",
        "\\begin{aligned}\n",
        "&\\sum_{t} \\sum_{j} x_{O P_{k}, j, t, k}=1, \\forall k \\\\\n",
        "&\\sum_{t} \\sum_{i} x_{i, D P_{k}, t, k}=1, \\forall k\n",
        "\\end{aligned}\n",
        "$$\n",
        "\n",
        "2. For each goods $k$, it couldn't be shipped out from its destination or shipped to its origin.\n",
        "$$\n",
        "\\begin{aligned}\n",
        "&\\sum_{t} \\sum_{i} x_{i, 0 P_{k}, t, k}=0, \\forall k \\\\\n",
        "&\\sum_{t} \\sum_{j} x_{D P_{k}, j, t, k}=0, \\forall k\n",
        "\\end{aligned}\n",
        "$$\n",
        "\n",
        "3. For each goods $k$ at transition point $j$ (neither origin nor destination), ship-in times must equal ship-out times.\n",
        "$$\n",
        "\\sum_{t} \\sum_{i} x_{i, j, t, k}=\\sum_{t} \\sum_{i} x_{j, i, t, k}, \\forall k, j \\text { s.t. } j \\neq O P_{k}, j \\neq D P_{k}\n",
        "$$\n",
        "\n",
        "4. Each goods k can only be transitioned in or out of a port for at most once.\n",
        "$$\n",
        "\\begin{aligned}\n",
        "&\\sum_{t} \\sum_{j} x_{i, j, t, k} \\leq 1, \\forall k, i \\\\\n",
        "&\\sum_{t} \\sum_{i} x_{i, j, t, k} \\leq 1, \\forall k, j\n",
        "\\end{aligned}\n",
        "$$\n",
        "\n",
        "5. For each goods $k$ at transition port $j$, ship-out time should be after ship-in time. For goods $k$ at its origin port, ship-out time should be after order date. (Or stay time greater than order date, because ship-in time is none)\n",
        "$$\n",
        "\\begin{aligned}\n",
        "&\\sum_{j} \\sum_{t} t \\times x_{i, j, t, k}-\\sum_{j} \\sum_{t}\\left(t \\times x_{j, i, t, k}+T_{j, i, t} \\times x_{j, i, t, k}\\right) \\geq 0, \\forall k, i \\text { s.t. } i \\neq O P_{k}, i \\neq D P_{k} \\\\\n",
        "&\\sum_{j} \\sum_{t} t \\times x_{O P_{k}, j, t, k}-\\sum_{j} \\sum_{t}\\left(t \\times x_{j, O P_{k}, t, k}+T_{j, O P_{k, t}} \\times x_{j, O P_{k}, t, k}\\right) \\geq \\operatorname{ord}_{k}, \\forall k\n",
        "\\end{aligned}\n",
        "$$\n",
        "\n",
        "6. At each route at time $t$, the total volume of containers should be larger than the total volume of goods.\n",
        "$$\n",
        "y_{i, j, t} \\geq \\frac{\\sum_{k} x_{i, j, t, k} \\times V_{k}}{\\operatorname{ctn} V_{i, j}}, \\forall i, j, t\n",
        "$$\n",
        "\n",
        "7. Check whether a route is used at time t. Because $Z_{i, j, t}$ is binary variable, if a route is used, sum of $X_{i, j, t, k}$ for all goods $k$ at $i, j, t$ must be greater than 0 . We can scale it back to $[0,1]$ by multiplying a small number.\n",
        "$$\n",
        "z_{i, j, t} \\geq \\sum_{k} x_{i, j, t, k} \\times 10^{-5}, \\forall i, j, t\n",
        "$$\n",
        "\n",
        "8. For each goods $k$, it should be shipped to its destination port before the deadline delivery date.\n",
        "$$\n",
        "\\sum_{i} \\sum_{t}\\left(t \\times x_{i, D P_{k}, t, k}+T_{i, D P_{k}, t} \\times x_{i, D P_{k}, t, k}\\right) \\leq d d l_{k}, \\forall k\n",
        "$$\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "iTnBk5Lfvsio"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "class SCO:\n",
        "    '''a Model class that solves the supply chain transportation optimization problem.'''\n",
        "\n",
        "    def __init__(self, framework='CVXPY'):\n",
        "        # parameters\n",
        "        self.port_Space = None\n",
        "        self.date_Space = None\n",
        "        self.Goods = None\n",
        "        self.index_Port = None\n",
        "        self.port_Index = None\n",
        "        self.max_Date = None\n",
        "        self.min_Date = None\n",
        "        self.tran_Cost = None\n",
        "        self.tran_Fixed_Cost = None\n",
        "        self.tran_Time = None\n",
        "        self.container_Vol = None\n",
        "        self.wh_Cost = None\n",
        "        self.k_Vol = None\n",
        "        self.k_Value = None\n",
        "        self.k_DDL = None\n",
        "        self.k_Start_Port = None\n",
        "        self.k_End_Port = None\n",
        "        self.k_Start_Time = None\n",
        "        self.tax_Pct = None\n",
        "        self.transit_Duty = None\n",
        "        self.route_num = None\n",
        "        self.available_routes = None\n",
        "        # decision variables\n",
        "        self.var = None\n",
        "        self.x = None\n",
        "        self.var_2 = None\n",
        "        self.y = None\n",
        "        self.var_3 = None\n",
        "        self.z = None\n",
        "        # result & solution\n",
        "        self.xs = None\n",
        "        self.ys = None\n",
        "        self.zs = None\n",
        "        self.wh_Cost_Final = None\n",
        "        self.transport_Cost = None\n",
        "        self.tax_Cost = None\n",
        "        self.solution_ = None\n",
        "        self.arrTime_ = None\n",
        "        self.objective_value = None\n",
        "        # helping variables\n",
        "        self.var_location = None\n",
        "        self.var_2_location = None\n",
        "        self.var_3_location = None\n",
        "\n",
        "\n",
        "\n",
        "    def set_param(self, route, order):\n",
        "        '''set model parameters based on the read-in route and order information.'''\n",
        "\n",
        "        bigM = 100000\n",
        "        route = route[route['Feasibility'] == 1]\n",
        "        route['Warehouse Cost'][route['Warehouse Cost'].isnull()] = bigM\n",
        "        route = route.reset_index()\n",
        "\n",
        "        portSet = set(route['Source']) | set(route['Destination'])\n",
        "\n",
        "        self.port_Space = len(portSet)\n",
        "        self.port_Index = dict(zip(range(len(portSet)), portSet))\n",
        "        self.index_Port = dict(zip(self.port_Index.values(), self.port_Index.keys()))\n",
        "\n",
        "        self.max_Date = np.max(order['Required Delivery Date'])\n",
        "        self.min_Date = np.min(order['Order Date'])\n",
        "        self.date_Space = (self.max_Date - self.min_Date).days\n",
        "        startWeekday = self.min_Date.weekday() + 1\n",
        "        weekday = np.mod((np.arange(self.date_Space) + startWeekday), 7)\n",
        "        weekday[weekday == 0] = 7\n",
        "        weekdayDateList = {i: [] for i in range(1, 8)}\n",
        "        for i in range(len(weekday)):\n",
        "            weekdayDateList[weekday[i]].append(i)\n",
        "        for i in weekdayDateList:\n",
        "            weekdayDateList[i] = json.dumps(weekdayDateList[i])\n",
        "\n",
        "        source = list(route['Source'].replace(self.index_Port))\n",
        "        destination = list(route['Destination'].replace(self.index_Port))\n",
        "        DateList = list(route['Weekday'].replace(weekdayDateList).apply(json.loads))\n",
        "\n",
        "        self.Goods = order.shape[0]\n",
        "        self.tran_Cost = np.ones([self.port_Space, self.port_Space, self.date_Space]) * bigM\n",
        "        self.tran_Fixed_Cost = np.ones([self.port_Space, self.port_Space, self.date_Space]) * bigM\n",
        "        self.tran_Time = np.ones([self.port_Space, self.port_Space, self.date_Space]) * bigM\n",
        "\n",
        "        for i in range(route.shape[0]):\n",
        "            self.tran_Cost[source[i], destination[i], DateList[i]] = route['Cost'][i]\n",
        "            self.tran_Fixed_Cost[source[i], destination[i], DateList[i]] = route['Fixed Freight Cost'][i]\n",
        "            self.tran_Time[source[i], destination[i], DateList[i]] = route['Time'][i]\n",
        "\n",
        "        self.transit_Duty = np.ones([self.port_Space, self.port_Space]) * bigM\n",
        "        self.transit_Duty[source, destination] = route['Transit Duty']\n",
        "\n",
        "        # make the container size of infeasible routes to be small enough, similar to bigM\n",
        "        self.container_Vol = np.ones([self.port_Space, self.port_Space]) * 0.1\n",
        "        self.container_Vol[source, destination] = route['Container Size']\n",
        "        self.container_Vol = self.container_Vol.reshape(self.port_Space, self.port_Space, 1)\n",
        "        self.wh_Cost = route[['Source', 'Warehouse Cost']].drop_duplicates()\n",
        "        self.wh_Cost['index'] = self.wh_Cost['Source'].replace(self.index_Port)\n",
        "        self.wh_Cost = np.array(self.wh_Cost.sort_values(by='index')['Warehouse Cost'])\n",
        "        self.k_Vol = np.array(order['Volume'])\n",
        "        self.k_Value = np.array(order['Order Value'])\n",
        "        self.k_DDL = np.array((order['Required Delivery Date'] - self.min_Date).dt.days)\n",
        "        self.k_Start_Port = np.array(order['Ship From'].replace(self.index_Port))\n",
        "        self.k_End_Port = np.array(order['Ship To'].replace(self.index_Port))\n",
        "        self.k_Start_Time = np.array((order['Order Date'] - self.min_Date).dt.days)\n",
        "        self.tax_Pct = np.array(order['Tax Percentage'])\n",
        "\n",
        "        # add available route indexes\n",
        "        self.route_num = route[['Source', 'Destination']].drop_duplicates().shape[0]\n",
        "        routes = route[['Source', 'Destination']].drop_duplicates().replace(self.index_Port)\n",
        "        self.available_routes = list(zip(routes['Source'], routes['Destination']))\n",
        "        # localization variables of decision variables in the matrix\n",
        "        var_location = product(self.available_routes, range(self.date_Space), range(self.Goods))\n",
        "        var_location = [(i[0][0], i[0][1], i[1], i[2]) for i in var_location]\n",
        "        self.var_location = tuple(zip(*var_location))\n",
        "\n",
        "        var_2_location = product(self.available_routes, range(self.date_Space))\n",
        "        var_2_location = [(i[0][0], i[0][1], i[1]) for i in var_2_location]\n",
        "        self.var_2_location = tuple(zip(*var_2_location))\n",
        "\n",
        "        self.var_3_location = self.var_2_location\n",
        "\n",
        "    def build_model(self):\n",
        "        '''overall function to build up model objective and constraints'''\n",
        "        self.cvxpy_build_model()\n",
        "        \n",
        "\n",
        "    def cvxpy_build_model(self):\n",
        "        '''build up the mathematical programming model's objective and constraints using CVXPY framework.'''\n",
        "\n",
        "        # 4 dimensional binary decision variable matrix\n",
        "        self.var = cp.Variable(self.route_num * self.date_Space * self.Goods, boolean=True, name='x')\n",
        "        self.x = np.zeros((self.port_Space, self.port_Space, self.date_Space, self.Goods)).astype('object')\n",
        "        self.x[self.var_location] = list(self.var)\n",
        "        # 3 dimensional container number matrix\n",
        "        self.var_2 = cp.Variable(self.route_num * self.date_Space, integer=True, name='y')\n",
        "        self.y = np.zeros((self.port_Space, self.port_Space, self.date_Space)).astype('object')\n",
        "        self.y[self.var_2_location] = list(self.var_2)\n",
        "        # 3 dimensional route usage matrix\n",
        "        self.var_3 = cp.Variable(self.route_num * self.date_Space, boolean=True, name='z')\n",
        "        self.z = np.zeros((self.port_Space, self.port_Space, self.date_Space)).astype('object')\n",
        "        self.z[self.var_3_location] = list(self.var_3)\n",
        "        # warehouse related cost\n",
        "        warehouseCost, arrTime, stayTime = self.warehouse_fee(self.x)\n",
        "        ###objective###\n",
        "        transport_Cost = np.sum(self.y * self.tran_Cost) + np.sum(self.z * self.tran_Fixed_Cost)\n",
        "        transit_DutyCost = np.sum(np.sum(np.dot(self.x, self.k_Value), axis=2) * self.transit_Duty)\n",
        "        tax_Cost = np.sum(self.tax_Pct * self.k_Value) + transit_DutyCost\n",
        "        objective = cp.Minimize(transport_Cost + warehouseCost + tax_Cost)\n",
        "        ###constraint###\n",
        "        constraints = []\n",
        "        # 1.Goods must be shipped out from its origin to another node and shipped to its destination.\n",
        "        constraints += [np.sum(self.x[self.k_Start_Port[k], :, :, k]) == 1 for k in range(self.Goods)]\n",
        "        constraints += [np.sum(self.x[:, self.k_End_Port[k], :, k]) == 1 for k in range(self.Goods)]\n",
        "        # 2.For each Goods k, it couldn't be shipped out from its destination or shipped to its origin.\n",
        "        constraints += [np.sum(self.x[:, self.k_Start_Port[k], :, k]) == 0 for k in range(self.Goods)]\n",
        "        constraints += [np.sum(self.x[self.k_End_Port[k], :, :, k]) == 0 for k in range(self.Goods)]\n",
        "        # 3.constraint for transition point\n",
        "        for k in range(self.Goods):\n",
        "            for j in range(self.port_Space):\n",
        "                if (j != self.k_Start_Port[k]) & (j != self.k_End_Port[k]):\n",
        "                    constraints.append(np.sum(self.x[:, j, :, k]) == np.sum(self.x[j, :, :, k]))\n",
        "        # 4.each Goods can only be transitioned in or out of a port for at most once\n",
        "        constraints += [np.sum(self.x[i, :, :, k]) <= 1 for k in range(self.Goods) for i in range(self.port_Space)]\n",
        "        constraints += [np.sum(self.x[:, j, :, k]) <= 1 for k in range(self.Goods) for j in range(self.port_Space)]\n",
        "        # 5.transition-out should be after transition-in\n",
        "        constraints += [stayTime[j, k] >= 0 for j in range(self.port_Space) for k in range(self.Goods)]\n",
        "        # 6.constraint for number of containers used\n",
        "        numCtn = np.dot(self.x, self.k_Vol) / self.container_Vol\n",
        "        constraints += [self.y[i, j, t] - numCtn[i, j, t] >= 0 \\\n",
        "                        for i in range(self.port_Space) for j in range(self.port_Space) for t in\n",
        "                        range(self.date_Space) if not isinstance(self.y[i, j, t] - numCtn[i, j, t] >= 0, bool)]\n",
        "        # 7. constraint to check whether a route is used\n",
        "        constraints += [self.z[i, j, t] >= (np.sum(self.x[i, j, t, :]) * 10e-5) \\\n",
        "                        for i in range(self.port_Space) for j in range(self.port_Space) for t in\n",
        "                        range(self.date_Space) if\n",
        "                        not isinstance(self.z[i, j, t] >= (np.sum(self.x[i, j, t, :]) * 10e-5), bool)]\n",
        "        # 8.time limitation constraint for each Goods\n",
        "        constraints += [np.sum(arrTime[:, self.k_End_Port[k], :, k]) <= self.k_DDL[k] for k in range(self.Goods)\n",
        "                        if not isinstance(np.sum(arrTime[:, self.k_End_Port[k], :, k]) <= self.k_DDL[k], bool)]\n",
        "        model = cp.Problem(objective, constraints)\n",
        "\n",
        "        self.objective = objective\n",
        "        self.constraints = constraints\n",
        "        self.model = model\n",
        "\n",
        "    \n",
        "    def solve_model(self, solver=cp.CBC):\n",
        "        '''\n",
        "        solve the optimization model & cache the optimized objective value, route and arrival time for each Goods.\n",
        "        :param solver: the solver to use to solve the LP problem when framework is CVXPY, has no effect to the model\n",
        "        Default solver is cvxpy.CBC, other open source solvers do not perform that well.\n",
        "        :return: None\n",
        "        '''\n",
        "        try:\n",
        "                self.objective_value = self.model.solve(solver)\n",
        "                self.xs = np.zeros((self.port_Space, self.port_Space, self.date_Space, self.Goods))\n",
        "                self.xs[self.var_location] = self.var.value\n",
        "                self.ys = np.zeros((self.port_Space, self.port_Space, self.date_Space))\n",
        "                self.ys[self.var_2_location] = self.var_2.value\n",
        "                self.zs = np.zeros((self.port_Space, self.port_Space, self.date_Space))\n",
        "                self.zs[self.var_3_location] = self.var_3.value\n",
        "\n",
        "        except:\n",
        "            raise Exception('Model is not solvable, no solution will be provided')\n",
        "\n",
        "        nonzeroX = list(zip(*np.nonzero(self.xs)))\n",
        "        nonzeroX = sorted(nonzeroX, key=lambda x: x[2])\n",
        "        nonzeroX = sorted(nonzeroX, key=lambda x: x[3])\n",
        "        nonzeroX = list(map(lambda x: (self.port_Index[x[0]], self.port_Index[x[1]], \\\n",
        "                                       (self.min_Date + pd.to_timedelta(x[2], unit='days')).date().isoformat(),\n",
        "                                       x[3]), nonzeroX))\n",
        "\n",
        "        self.wh_Cost_Final, arrTime, _ = self.warehouse_fee(self.xs)\n",
        "        self.transport_Cost = np.sum(self.ys * self.tran_Cost) + np.sum(self.zs * self.tran_Fixed_Cost)\n",
        "        self.tax_Cost = np.sum(self.tax_Pct * self.k_Value) + \\\n",
        "                       np.sum(np.sum(np.dot(self.xs, self.k_Value), axis=2) * self.transit_Duty)\n",
        "        self.solution_ = {}\n",
        "        self.arrTime_ = {}\n",
        "        for i in range(self.Goods):\n",
        "            self.solution_['Goods-' + str(i + 1)] = list(filter(lambda x: x[3] == i, nonzeroX))\n",
        "            self.arrTime_['Goods-' + str(i + 1)] = (self.min_Date + pd.to_timedelta \\\n",
        "                (np.sum(arrTime[:, self.k_End_Port[i], :, i]), unit='days')).date().isoformat()\n",
        "\n",
        "    def get_output_(self):\n",
        "        '''After the model is solved, return total cost, final solution and arrival\n",
        "        time for each of the Goods'''\n",
        "\n",
        "        return self.objective_value, self.solution_, self.arrTime_\n",
        "\n",
        "    def warehouse_fee(self, x):\n",
        "        '''return warehouse fee, arrival time and stay time for each port.'''\n",
        "\n",
        "        startTime = np.arange(self.date_Space).reshape(1, 1, self.date_Space, 1) * x\n",
        "        arrTimeMtrx = startTime + self.tran_Time.reshape(self.port_Space, \\\n",
        "                                                        self.port_Space, self.date_Space, 1) * x\n",
        "        arrTime = arrTimeMtrx.copy()\n",
        "        arrTimeMtrx[:, self.k_End_Port.tolist(), :, range(self.Goods)] = 0\n",
        "        stayTime = np.sum(startTime, axis=(1, 2)) - np.sum(arrTimeMtrx, axis=(0, 2))\n",
        "        stayTime[self.k_Start_Port.tolist(), range(self.Goods)] -= self.k_Start_Time\n",
        "        warehouseCost = np.sum(np.sum(stayTime * self.k_Vol, axis=1) * self.wh_Cost)\n",
        "\n",
        "        return warehouseCost, arrTime, stayTime\n",
        "\n",
        "    def txt_solution(self, route, order):\n",
        "        '''transform the cached results to text.'''\n",
        "\n",
        "        travelMode = dict(zip(zip(route['Source'], route['Destination']), route['Travel Mode']))\n",
        "        txt = \"Solution\"\n",
        "        txt += \"\\nNumber of Goods: \" + str(order['Order Number'].count())\n",
        "        txt += \"\\nTotal cost: \" + str(self.transport_Cost + self.wh_Cost_Final + self.tax_Cost)\n",
        "        txt += \"\\nTransportation cost: \" + str(self.transport_Cost)\n",
        "        txt += \"\\nWarehouse cost: \" + str(self.wh_Cost_Final)\n",
        "        txt += \"\\nTax cost: \" + str(self.tax_Cost)\n",
        "\n",
        "        for i in range(order.shape[0]):\n",
        "            txt += \"\\n------------------------------------\"\n",
        "            txt += \"\\nGoods-\" + str(i + 1) + \"  Category: \" + order['Commodity'][i]\n",
        "            txt += \"\\nStart date: \" + pd.to_datetime(order['Order Date']) \\\n",
        "                .iloc[i].date().isoformat()\n",
        "            txt += \"\\nArrival date: \" + str(self.arrTime_['Goods-' + str(i + 1)])\n",
        "            txt += \"\\nRoute:\"\n",
        "            solution = self.solution_['Goods-' + str(i + 1)]\n",
        "            route_txt = ''\n",
        "            a = 1\n",
        "            for j in solution:\n",
        "                route_txt += \"\\n(\" + str(a) + \")Date: \" + j[2]\n",
        "                route_txt += \"  From: \" + j[0]\n",
        "                route_txt += \"  To: \" + j[1]\n",
        "                route_txt += \"  By: \" + travelMode[(j[0], j[1])]\n",
        "                a += 1\n",
        "            txt += route_txt\n",
        "\n",
        "        return txt\n",
        "\n",
        "\n",
        "def transform(filePath):\n",
        "    '''Read in order and route data, transform the data into a form that can\n",
        "    be processed by the operation research model.'''\n",
        "    order = pd.read_excel(filePath, sheet_name='Order Information')\n",
        "    route = pd.read_excel(filePath, sheet_name='Route Information')\n",
        "    order['Tax Percentage'][order['Journey Type'] == 'Domestic'] = 0\n",
        "    route['Cost'] = route[route.columns[7:12]].sum(axis=1)\n",
        "    route['Time'] = np.ceil(route[route.columns[14:18]].sum(axis=1) / 24)\n",
        "    route = route[list(route.columns[0:4]) + ['Fixed Freight Cost', 'Time', \\\n",
        "                                              'Cost', 'Warehouse Cost', 'Travel Mode', 'Transit Duty'] + list(\n",
        "        route.columns[-9:-2])]\n",
        "    route = pd.melt(route, id_vars=route.columns[0:10], value_vars=route.columns[-7:] \\\n",
        "                    , var_name='Weekday', value_name='Feasibility')\n",
        "    route['Weekday'] = route['Weekday'].replace({'Monday': 1, 'Tuesday': 2, 'Wednesday': 3, \\\n",
        "                                                 'Thursday': 4, 'Friday': 5, 'Saturday': 6, 'Sunday': 7})\n",
        "\n",
        "    return order, route"
      ],
      "metadata": {
        "id": "KM9z8xe4WIs1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "\n",
        "# Accessing My Google Drive, Please authorise it from gmail id. Just give the permission to access google drive file.\n",
        "drive.mount('/content/drive', force_remount=True)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bGTh-81EW3SZ",
        "outputId": "a55b399e-230f-44a5-f192-731f248dc856"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "if __name__ == '__main__':\n",
        "    order, route = transform(\"/content/drive/My Drive/Colab Notebooks/data/model.xlsx\") #Please upload the model.xlsx data file on your google drive location: My Drive/Colab Notebooks/data/model.xlsx\n",
        "    m = SCO(framework='CVXPY') # for open source framework\n",
        "    m.set_param(route, order)\n",
        "    m.build_model()\n",
        "    m.solve_model()\n",
        "    txt = m.txt_solution(route, order)\n",
        "    with open(\"/content/drive/My Drive/Colab Notebooks/data/Solution.txt\", \"w\") as text_file:\n",
        "        text_file.write(txt) #Please download the solution.txt file from your google drive file location: My Drive/Colab Notebooks/data/Solution.txt"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BYH6csd8ayin",
        "outputId": "a0a5aad8-c1c1-48cd-c170-3b325a6ba915"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:277: SettingWithCopyWarning: \n",
            "A value is trying to be set on a copy of a slice from a DataFrame\n",
            "\n",
            "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
            "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:57: SettingWithCopyWarning: \n",
            "A value is trying to be set on a copy of a slice from a DataFrame\n",
            "\n",
            "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
            "/usr/local/lib/python3.7/dist-packages/pandas/core/generic.py:8870: SettingWithCopyWarning: \n",
            "A value is trying to be set on a copy of a slice from a DataFrame\n",
            "\n",
            "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
            "  return self._update_inplace(result)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Optimization Result \\& Solution** :\n",
        "\n",
        "With the objective \\& constraints built, the model is now complete! To make users understand the result easier, we process it with the function txt_solution() and save it into a text file Solution.txt. The minimized cost value as well as optimal routes for all goods are presented in it."
      ],
      "metadata": {
        "id": "JkKCgl2v_WOX"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "route variable shows various available domestic and international route and cost associated with it. Please check model.xlsx file [Route information sheet]."
      ],
      "metadata": {
        "id": "aiWhd-lLNV2y"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "route"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "id": "TX24I1IIYoNu",
        "outputId": "00061aa7-20d9-4583-fcc8-19bcc3df9b7a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "     Route Number                   Source          Destination  \\\n",
              "0               1           Singapore Port         Gujarat Port   \n",
              "1               2             Gujarat Port       Singapore Port   \n",
              "2               3           Singapore Port        Malaysia Port   \n",
              "3               4            Malaysia Port       Singapore Port   \n",
              "4               5             Gujarat Port        Malaysia Port   \n",
              "..            ...                      ...                  ...   \n",
              "345            46  Gujarat Railway Station    Gujarat Warehouse   \n",
              "346            47      Singapore Warehouse   Malaysia Warehouse   \n",
              "347            48       Malaysia Warehouse  Singapore Warehouse   \n",
              "348            49        Gujarat Warehouse     Mumbai Warehouse   \n",
              "349            50         Mumbai Warehouse    Gujarat Warehouse   \n",
              "\n",
              "     Container Size  Fixed Freight Cost  Time  Cost  Warehouse Cost  \\\n",
              "0                67                 300   8.0   900             NaN   \n",
              "1                67                 150   8.0   900             NaN   \n",
              "2                34                  50   4.0   650             NaN   \n",
              "3                34                  50   4.0   650             NaN   \n",
              "4                67                 300   7.0   730             NaN   \n",
              "..              ...                 ...   ...   ...             ...   \n",
              "345              34                 100   1.0     0             NaN   \n",
              "346              34                 150   1.0     0            20.0   \n",
              "347              34                 150   1.0     0            10.0   \n",
              "348              34                 125   1.0     0            15.0   \n",
              "349              34                 125   1.0     0             8.0   \n",
              "\n",
              "    Travel Mode  Transit Duty  Weekday  Feasibility  \n",
              "0           Sea         0.002        1            1  \n",
              "1           Sea         0.001        1            1  \n",
              "2           Sea         0.000        1            0  \n",
              "3           Sea         0.001        1            0  \n",
              "4           Sea         0.000        1            0  \n",
              "..          ...           ...      ...          ...  \n",
              "345       Truck         0.000        7            1  \n",
              "346       Truck         0.000        7            1  \n",
              "347       Truck         0.001        7            1  \n",
              "348       Truck         0.000        7            1  \n",
              "349       Truck         0.000        7            1  \n",
              "\n",
              "[350 rows x 12 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-d701ba46-f478-412b-9483-ece2c9bb12c9\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Route Number</th>\n",
              "      <th>Source</th>\n",
              "      <th>Destination</th>\n",
              "      <th>Container Size</th>\n",
              "      <th>Fixed Freight Cost</th>\n",
              "      <th>Time</th>\n",
              "      <th>Cost</th>\n",
              "      <th>Warehouse Cost</th>\n",
              "      <th>Travel Mode</th>\n",
              "      <th>Transit Duty</th>\n",
              "      <th>Weekday</th>\n",
              "      <th>Feasibility</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>Singapore Port</td>\n",
              "      <td>Gujarat Port</td>\n",
              "      <td>67</td>\n",
              "      <td>300</td>\n",
              "      <td>8.0</td>\n",
              "      <td>900</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Sea</td>\n",
              "      <td>0.002</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>Gujarat Port</td>\n",
              "      <td>Singapore Port</td>\n",
              "      <td>67</td>\n",
              "      <td>150</td>\n",
              "      <td>8.0</td>\n",
              "      <td>900</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Sea</td>\n",
              "      <td>0.001</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>Singapore Port</td>\n",
              "      <td>Malaysia Port</td>\n",
              "      <td>34</td>\n",
              "      <td>50</td>\n",
              "      <td>4.0</td>\n",
              "      <td>650</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Sea</td>\n",
              "      <td>0.000</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>Malaysia Port</td>\n",
              "      <td>Singapore Port</td>\n",
              "      <td>34</td>\n",
              "      <td>50</td>\n",
              "      <td>4.0</td>\n",
              "      <td>650</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Sea</td>\n",
              "      <td>0.001</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>Gujarat Port</td>\n",
              "      <td>Malaysia Port</td>\n",
              "      <td>67</td>\n",
              "      <td>300</td>\n",
              "      <td>7.0</td>\n",
              "      <td>730</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Sea</td>\n",
              "      <td>0.000</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>345</th>\n",
              "      <td>46</td>\n",
              "      <td>Gujarat Railway Station</td>\n",
              "      <td>Gujarat Warehouse</td>\n",
              "      <td>34</td>\n",
              "      <td>100</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Truck</td>\n",
              "      <td>0.000</td>\n",
              "      <td>7</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>346</th>\n",
              "      <td>47</td>\n",
              "      <td>Singapore Warehouse</td>\n",
              "      <td>Malaysia Warehouse</td>\n",
              "      <td>34</td>\n",
              "      <td>150</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>20.0</td>\n",
              "      <td>Truck</td>\n",
              "      <td>0.000</td>\n",
              "      <td>7</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>347</th>\n",
              "      <td>48</td>\n",
              "      <td>Malaysia Warehouse</td>\n",
              "      <td>Singapore Warehouse</td>\n",
              "      <td>34</td>\n",
              "      <td>150</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>Truck</td>\n",
              "      <td>0.001</td>\n",
              "      <td>7</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>348</th>\n",
              "      <td>49</td>\n",
              "      <td>Gujarat Warehouse</td>\n",
              "      <td>Mumbai Warehouse</td>\n",
              "      <td>34</td>\n",
              "      <td>125</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>15.0</td>\n",
              "      <td>Truck</td>\n",
              "      <td>0.000</td>\n",
              "      <td>7</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>349</th>\n",
              "      <td>50</td>\n",
              "      <td>Mumbai Warehouse</td>\n",
              "      <td>Gujarat Warehouse</td>\n",
              "      <td>34</td>\n",
              "      <td>125</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>Truck</td>\n",
              "      <td>0.000</td>\n",
              "      <td>7</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>350 rows × 12 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d701ba46-f478-412b-9483-ece2c9bb12c9')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-d701ba46-f478-412b-9483-ece2c9bb12c9 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-d701ba46-f478-412b-9483-ece2c9bb12c9');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "order variable shows quoted order. Please check model.xlsx file [order information sheet]"
      ],
      "metadata": {
        "id": "yyFGApwAN45m"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "order"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 500
        },
        "id": "kPTS_CEBYyao",
        "outputId": "e65880a3-75cd-410c-a390-d270384ae4b9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   Order Number            Ship From              Ship To Commodity  \\\n",
              "0             1  Singapore Warehouse     Mumbai Warehouse     Honey   \n",
              "1             2   Malaysia Warehouse    Gujarat Warehouse       Tea   \n",
              "2             3  Singapore Warehouse    Gujarat Warehouse    Banana   \n",
              "3             4  Singapore Warehouse    Gujarat Warehouse      Seed   \n",
              "4             5     Mumbai Warehouse   Malaysia Warehouse     Mango   \n",
              "5             6    Gujarat Warehouse  Singapore Warehouse     Apple   \n",
              "6             7   Malaysia Warehouse  Singapore Warehouse    Litchi   \n",
              "7             8     Mumbai Warehouse    Gujarat Warehouse    Coffee   \n",
              "\n",
              "   Order Value  Weight (KG)  Volume Shipper Name Shipper Address  \\\n",
              "0        50000        21000      34          YCH     8 Bulim Ave   \n",
              "1        10000        20000      67          YCH     8 Bulim Ave   \n",
              "2        12000        20000      67          YCH     8 Bulim Ave   \n",
              "3       800000           20       7          YCH     8 Bulim Ave   \n",
              "4       700000         5000      50          YCH     8 Bulim Ave   \n",
              "5        30000        25000      67          YCH     8 Bulim Ave   \n",
              "6        10000        10000      34          YCH     8 Bulim Ave   \n",
              "7        10000        20000      67          YCH     8 Bulim Ave   \n",
              "\n",
              "  Shipper Country Consignee Country Order Date Required Delivery Date  \\\n",
              "0       Singapore             India 2022-08-02             2022-08-25   \n",
              "1        Malaysia             India 2022-08-02             2022-08-23   \n",
              "2       Singapore             India 2022-08-03             2022-08-23   \n",
              "3       Singapore             India 2022-08-04             2022-08-24   \n",
              "4           India          Malaysia 2022-08-05             2022-08-22   \n",
              "5           India         Singapore 2022-08-06             2022-08-23   \n",
              "6        Malaysia         Singapore 2022-08-07             2022-08-24   \n",
              "7           India             India 2022-08-08             2022-08-25   \n",
              "\n",
              "    Journey Type  Tax Percentage  \n",
              "0  International            0.03  \n",
              "1  International            0.01  \n",
              "2  International            0.01  \n",
              "3  International            0.10  \n",
              "4  International            0.15  \n",
              "5  International            0.01  \n",
              "6  International            0.01  \n",
              "7       Domestic            0.00  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-a51256e3-7c8a-436c-94b3-30125510fef1\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Order Number</th>\n",
              "      <th>Ship From</th>\n",
              "      <th>Ship To</th>\n",
              "      <th>Commodity</th>\n",
              "      <th>Order Value</th>\n",
              "      <th>Weight (KG)</th>\n",
              "      <th>Volume</th>\n",
              "      <th>Shipper Name</th>\n",
              "      <th>Shipper Address</th>\n",
              "      <th>Shipper Country</th>\n",
              "      <th>Consignee Country</th>\n",
              "      <th>Order Date</th>\n",
              "      <th>Required Delivery Date</th>\n",
              "      <th>Journey Type</th>\n",
              "      <th>Tax Percentage</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>Singapore Warehouse</td>\n",
              "      <td>Mumbai Warehouse</td>\n",
              "      <td>Honey</td>\n",
              "      <td>50000</td>\n",
              "      <td>21000</td>\n",
              "      <td>34</td>\n",
              "      <td>YCH</td>\n",
              "      <td>8 Bulim Ave</td>\n",
              "      <td>Singapore</td>\n",
              "      <td>India</td>\n",
              "      <td>2022-08-02</td>\n",
              "      <td>2022-08-25</td>\n",
              "      <td>International</td>\n",
              "      <td>0.03</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>Malaysia Warehouse</td>\n",
              "      <td>Gujarat Warehouse</td>\n",
              "      <td>Tea</td>\n",
              "      <td>10000</td>\n",
              "      <td>20000</td>\n",
              "      <td>67</td>\n",
              "      <td>YCH</td>\n",
              "      <td>8 Bulim Ave</td>\n",
              "      <td>Malaysia</td>\n",
              "      <td>India</td>\n",
              "      <td>2022-08-02</td>\n",
              "      <td>2022-08-23</td>\n",
              "      <td>International</td>\n",
              "      <td>0.01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>Singapore Warehouse</td>\n",
              "      <td>Gujarat Warehouse</td>\n",
              "      <td>Banana</td>\n",
              "      <td>12000</td>\n",
              "      <td>20000</td>\n",
              "      <td>67</td>\n",
              "      <td>YCH</td>\n",
              "      <td>8 Bulim Ave</td>\n",
              "      <td>Singapore</td>\n",
              "      <td>India</td>\n",
              "      <td>2022-08-03</td>\n",
              "      <td>2022-08-23</td>\n",
              "      <td>International</td>\n",
              "      <td>0.01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>Singapore Warehouse</td>\n",
              "      <td>Gujarat Warehouse</td>\n",
              "      <td>Seed</td>\n",
              "      <td>800000</td>\n",
              "      <td>20</td>\n",
              "      <td>7</td>\n",
              "      <td>YCH</td>\n",
              "      <td>8 Bulim Ave</td>\n",
              "      <td>Singapore</td>\n",
              "      <td>India</td>\n",
              "      <td>2022-08-04</td>\n",
              "      <td>2022-08-24</td>\n",
              "      <td>International</td>\n",
              "      <td>0.10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>Mumbai Warehouse</td>\n",
              "      <td>Malaysia Warehouse</td>\n",
              "      <td>Mango</td>\n",
              "      <td>700000</td>\n",
              "      <td>5000</td>\n",
              "      <td>50</td>\n",
              "      <td>YCH</td>\n",
              "      <td>8 Bulim Ave</td>\n",
              "      <td>India</td>\n",
              "      <td>Malaysia</td>\n",
              "      <td>2022-08-05</td>\n",
              "      <td>2022-08-22</td>\n",
              "      <td>International</td>\n",
              "      <td>0.15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>6</td>\n",
              "      <td>Gujarat Warehouse</td>\n",
              "      <td>Singapore Warehouse</td>\n",
              "      <td>Apple</td>\n",
              "      <td>30000</td>\n",
              "      <td>25000</td>\n",
              "      <td>67</td>\n",
              "      <td>YCH</td>\n",
              "      <td>8 Bulim Ave</td>\n",
              "      <td>India</td>\n",
              "      <td>Singapore</td>\n",
              "      <td>2022-08-06</td>\n",
              "      <td>2022-08-23</td>\n",
              "      <td>International</td>\n",
              "      <td>0.01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>7</td>\n",
              "      <td>Malaysia Warehouse</td>\n",
              "      <td>Singapore Warehouse</td>\n",
              "      <td>Litchi</td>\n",
              "      <td>10000</td>\n",
              "      <td>10000</td>\n",
              "      <td>34</td>\n",
              "      <td>YCH</td>\n",
              "      <td>8 Bulim Ave</td>\n",
              "      <td>Malaysia</td>\n",
              "      <td>Singapore</td>\n",
              "      <td>2022-08-07</td>\n",
              "      <td>2022-08-24</td>\n",
              "      <td>International</td>\n",
              "      <td>0.01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>8</td>\n",
              "      <td>Mumbai Warehouse</td>\n",
              "      <td>Gujarat Warehouse</td>\n",
              "      <td>Coffee</td>\n",
              "      <td>10000</td>\n",
              "      <td>20000</td>\n",
              "      <td>67</td>\n",
              "      <td>YCH</td>\n",
              "      <td>8 Bulim Ave</td>\n",
              "      <td>India</td>\n",
              "      <td>India</td>\n",
              "      <td>2022-08-08</td>\n",
              "      <td>2022-08-25</td>\n",
              "      <td>Domestic</td>\n",
              "      <td>0.00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-a51256e3-7c8a-436c-94b3-30125510fef1')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-a51256e3-7c8a-436c-94b3-30125510fef1 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-a51256e3-7c8a-436c-94b3-30125510fef1');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Resultant solution after optimisation through cvxpy solver. Please check solution.txt file for optimal route, load and cost."
      ],
      "metadata": {
        "id": "NbCKSDAxOE6Y"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "m.get_output_()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3ww0dw5nZcCU",
        "outputId": "b435481c-682e-4722-e88f-fd806ab385ed"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(198484.0,\n",
              " {'Goods-1': [('Singapore Warehouse', 'Malaysia Warehouse', '2022-08-02', 0),\n",
              "   ('Malaysia Warehouse', 'Malaysia Port', '2022-08-05', 0),\n",
              "   ('Malaysia Port', 'Gujarat Port', '2022-08-06', 0),\n",
              "   ('Gujarat Port', 'Gujarat Warehouse', '2022-08-13', 0),\n",
              "   ('Gujarat Warehouse', 'Mumbai Warehouse', '2022-08-14', 0)],\n",
              "  'Goods-2': [('Malaysia Warehouse', 'Malaysia Port', '2022-08-02', 1),\n",
              "   ('Malaysia Port', 'Gujarat Port', '2022-08-03', 1),\n",
              "   ('Gujarat Port', 'Gujarat Warehouse', '2022-08-10', 1)],\n",
              "  'Goods-3': [('Singapore Warehouse', 'Malaysia Warehouse', '2022-08-03', 2),\n",
              "   ('Malaysia Warehouse', 'Malaysia Port', '2022-08-05', 2),\n",
              "   ('Malaysia Port', 'Gujarat Port', '2022-08-06', 2),\n",
              "   ('Gujarat Port', 'Gujarat Warehouse', '2022-08-13', 2)],\n",
              "  'Goods-4': [('Singapore Warehouse', 'Malaysia Warehouse', '2022-08-04', 3),\n",
              "   ('Malaysia Warehouse', 'Malaysia Port', '2022-08-05', 3),\n",
              "   ('Malaysia Port', 'Gujarat Port', '2022-08-06', 3),\n",
              "   ('Gujarat Port', 'Gujarat Warehouse', '2022-08-13', 3)],\n",
              "  'Goods-5': [('Mumbai Warehouse', 'Gujarat Warehouse', '2022-08-06', 4),\n",
              "   ('Gujarat Warehouse', 'Gujarat Port', '2022-08-07', 4),\n",
              "   ('Gujarat Port', 'Singapore Port', '2022-08-08', 4),\n",
              "   ('Singapore Port', 'Singapore Warehouse', '2022-08-16', 4),\n",
              "   ('Singapore Warehouse', 'Malaysia Warehouse', '2022-08-17', 4)],\n",
              "  'Goods-6': [('Gujarat Warehouse', 'Gujarat Port', '2022-08-07', 5),\n",
              "   ('Gujarat Port', 'Singapore Port', '2022-08-08', 5),\n",
              "   ('Singapore Port', 'Singapore Warehouse', '2022-08-16', 5)],\n",
              "  'Goods-7': [('Malaysia Warehouse', 'Singapore Warehouse', '2022-08-07', 6)],\n",
              "  'Goods-8': [('Mumbai Warehouse', 'Gujarat Warehouse', '2022-08-08', 7)]},\n",
              " {'Goods-1': '2022-08-15',\n",
              "  'Goods-2': '2022-08-11',\n",
              "  'Goods-3': '2022-08-14',\n",
              "  'Goods-4': '2022-08-14',\n",
              "  'Goods-5': '2022-08-18',\n",
              "  'Goods-6': '2022-08-17',\n",
              "  'Goods-7': '2022-08-08',\n",
              "  'Goods-8': '2022-08-09'})"
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install geopy\n",
        "import geopy\n",
        "from geopy.geocoders import Nominatim\n",
        "import folium\n",
        "\n",
        "# this is just for demonstration\n",
        "\n",
        "geolocator = Nominatim(timeout=10, user_agent = \"my-application\")\n",
        "location1 = geolocator.geocode('Gujarat Port')\n",
        "location2 = geolocator.geocode('Mumbai Port')\n",
        "print(location1)\n",
        "print((location1.latitude, location1.longitude))\n",
        "print(location2)\n",
        "print((location2.latitude, location2.longitude))\n",
        "\n",
        "\n",
        "p = folium.Map(location=[location1.latitude, location1.longitude],\n",
        "              zoom_start=15)\n",
        "\n",
        "loc = [(location1.latitude, location1.longitude),\n",
        "       (location2.latitude, location2.longitude)]    \n",
        "\n",
        "folium.PolyLine(loc,\n",
        "                color='red',\n",
        "                weight=5,\n",
        "                opacity=0.8).add_to(p)\n",
        "p"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 987
        },
        "id": "_7fEzU0kM5eZ",
        "outputId": "e8e16773-70d8-478f-d198-92ea270c8fb9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Requirement already satisfied: geopy in /usr/local/lib/python3.7/dist-packages (1.17.0)\n",
            "Requirement already satisfied: geographiclib<2,>=1.49 in /usr/local/lib/python3.7/dist-packages (from geopy) (1.52)\n",
            "Port, Salaya, Khambhalia Taluka, Devbhumi Dwaraka District, Gujarat, India\n",
            "(22.318400699999998, 69.60041945843892)\n",
            "Jawaharlal Nehru Port, Sheva, Mumbai Metropolitan Region, Uran, Raigad, Maharashtra, India\n",
            "(18.945030600000003, 72.94942299008869)\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<folium.folium.Map at 0x7f96fa2b4850>"
            ],
            "text/html": [
              "<div style=\"width:100%;\"><div style=\"position:relative;width:100%;height:0;padding-bottom:60%;\"><span style=\"color:#565656\">Make this Notebook Trusted to load map: File -> Trust Notebook</span><iframe srcdoc=\"&lt;!DOCTYPE html&gt;\n",
              "&lt;head&gt;    \n",
              "    &lt;meta http-equiv=&quot;content-type&quot; content=&quot;text/html; charset=UTF-8&quot; /&gt;\n",
              "    &lt;script&gt;L_PREFER_CANVAS=false; L_NO_TOUCH=false; L_DISABLE_3D=false;&lt;/script&gt;\n",
              "    &lt;script src=&quot;https://cdn.jsdelivr.net/npm/leaflet@1.4.0/dist/leaflet.js&quot;&gt;&lt;/script&gt;\n",
              "    &lt;script src=&quot;https://code.jquery.com/jquery-1.12.4.min.js&quot;&gt;&lt;/script&gt;\n",
              "    &lt;script src=&quot;https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js&quot;&gt;&lt;/script&gt;\n",
              "    &lt;script src=&quot;https://cdnjs.cloudflare.com/ajax/libs/Leaflet.awesome-markers/2.0.2/leaflet.awesome-markers.js&quot;&gt;&lt;/script&gt;\n",
              "    &lt;link rel=&quot;stylesheet&quot; href=&quot;https://cdn.jsdelivr.net/npm/leaflet@1.4.0/dist/leaflet.css&quot;/&gt;\n",
              "    &lt;link rel=&quot;stylesheet&quot; href=&quot;https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css&quot;/&gt;\n",
              "    &lt;link rel=&quot;stylesheet&quot; href=&quot;https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap-theme.min.css&quot;/&gt;\n",
              "    &lt;link rel=&quot;stylesheet&quot; href=&quot;https://maxcdn.bootstrapcdn.com/font-awesome/4.6.3/css/font-awesome.min.css&quot;/&gt;\n",
              "    &lt;link rel=&quot;stylesheet&quot; href=&quot;https://cdnjs.cloudflare.com/ajax/libs/Leaflet.awesome-markers/2.0.2/leaflet.awesome-markers.css&quot;/&gt;\n",
              "    &lt;link rel=&quot;stylesheet&quot; href=&quot;https://rawcdn.githack.com/python-visualization/folium/master/folium/templates/leaflet.awesome.rotate.css&quot;/&gt;\n",
              "    &lt;style&gt;html, body {width: 100%;height: 100%;margin: 0;padding: 0;}&lt;/style&gt;\n",
              "    &lt;style&gt;#map {position:absolute;top:0;bottom:0;right:0;left:0;}&lt;/style&gt;\n",
              "    \n",
              "    &lt;meta name=&quot;viewport&quot; content=&quot;width=device-width,\n",
              "        initial-scale=1.0, maximum-scale=1.0, user-scalable=no&quot; /&gt;\n",
              "    &lt;style&gt;#map_198786cdbff3efa34bb5018d99cbb0bd {\n",
              "        position: relative;\n",
              "        width: 100.0%;\n",
              "        height: 100.0%;\n",
              "        left: 0.0%;\n",
              "        top: 0.0%;\n",
              "        }\n",
              "    &lt;/style&gt;\n",
              "&lt;/head&gt;\n",
              "&lt;body&gt;    \n",
              "    \n",
              "    &lt;div class=&quot;folium-map&quot; id=&quot;map_198786cdbff3efa34bb5018d99cbb0bd&quot; &gt;&lt;/div&gt;\n",
              "&lt;/body&gt;\n",
              "&lt;script&gt;    \n",
              "    \n",
              "    \n",
              "        var bounds = null;\n",
              "    \n",
              "\n",
              "    var map_198786cdbff3efa34bb5018d99cbb0bd = L.map(\n",
              "        &#x27;map_198786cdbff3efa34bb5018d99cbb0bd&#x27;, {\n",
              "        center: [22.318400699999998, 69.60041945843892],\n",
              "        zoom: 15,\n",
              "        maxBounds: bounds,\n",
              "        layers: [],\n",
              "        worldCopyJump: false,\n",
              "        crs: L.CRS.EPSG3857,\n",
              "        zoomControl: true,\n",
              "        });\n",
              "\n",
              "\n",
              "    \n",
              "    var tile_layer_af2acadabac5cda5e8edc2e319bff18b = L.tileLayer(\n",
              "        &#x27;https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png&#x27;,\n",
              "        {\n",
              "        &quot;attribution&quot;: null,\n",
              "        &quot;detectRetina&quot;: false,\n",
              "        &quot;maxNativeZoom&quot;: 18,\n",
              "        &quot;maxZoom&quot;: 18,\n",
              "        &quot;minZoom&quot;: 0,\n",
              "        &quot;noWrap&quot;: false,\n",
              "        &quot;opacity&quot;: 1,\n",
              "        &quot;subdomains&quot;: &quot;abc&quot;,\n",
              "        &quot;tms&quot;: false\n",
              "}).addTo(map_198786cdbff3efa34bb5018d99cbb0bd);\n",
              "    \n",
              "                var poly_line_6ee784a787968e46e82a261916156c1b = L.polyline(\n",
              "                    [[22.318400699999998, 69.60041945843892], [18.945030600000003, 72.94942299008869]],\n",
              "                    {\n",
              "  &quot;bubblingMouseEvents&quot;: true,\n",
              "  &quot;color&quot;: &quot;red&quot;,\n",
              "  &quot;dashArray&quot;: null,\n",
              "  &quot;dashOffset&quot;: null,\n",
              "  &quot;fill&quot;: false,\n",
              "  &quot;fillColor&quot;: &quot;red&quot;,\n",
              "  &quot;fillOpacity&quot;: 0.2,\n",
              "  &quot;fillRule&quot;: &quot;evenodd&quot;,\n",
              "  &quot;lineCap&quot;: &quot;round&quot;,\n",
              "  &quot;lineJoin&quot;: &quot;round&quot;,\n",
              "  &quot;noClip&quot;: false,\n",
              "  &quot;opacity&quot;: 0.8,\n",
              "  &quot;smoothFactor&quot;: 1.0,\n",
              "  &quot;stroke&quot;: true,\n",
              "  &quot;weight&quot;: 5\n",
              "}\n",
              "                    )\n",
              "                    .addTo(map_198786cdbff3efa34bb5018d99cbb0bd);\n",
              "            \n",
              "&lt;/script&gt;\" style=\"position:absolute;width:100%;height:100%;left:0;top:0;border:none !important;\" allowfullscreen webkitallowfullscreen mozallowfullscreen></iframe></div></div>"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Please check **solution.txt file** for optimal route and load for concerned order.  Data used for simulation are in model.xlsx file."
      ],
      "metadata": {
        "id": "i79yjVd_r4vZ"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Solution\n",
        "\n",
        "Number of Goods: 8\n",
        "\n",
        "Total cost: 198484.0\n",
        "\n",
        "Transportation cost: 6125.0\n",
        "\n",
        "Warehouse cost: 2755.0\n",
        "\n",
        "Tax cost: 189604.0\n",
        "\n",
        "------------------------------------\n",
        "\n",
        "Goods-1  Category: Honey\n",
        "\n",
        "Start date: 2022-08-02\n",
        "\n",
        "Arrival date: 2022-08-15\n",
        "\n",
        "Route:\n",
        "\n",
        "(1)Date: 2022-08-02  From: Singapore Warehouse  To: Malaysia Warehouse  By: Truck\n",
        "\n",
        "(2)Date: 2022-08-05  From: Malaysia Warehouse  To: Malaysia Port  By: Truck\n",
        "\n",
        "(3)Date: 2022-08-06  From: Malaysia Port  To: Gujarat Port  By: Sea\n",
        "\n",
        "(4)Date: 2022-08-13  From: Gujarat Port  To: Gujarat Warehouse  By: Truck\n",
        "\n",
        "(5)Date: 2022-08-14  From: Gujarat Warehouse  To: Mumbai Warehouse  By: Truck\n",
        "\n",
        "------------------------------------\n",
        "\n",
        "Goods-2  Category: Tea\n",
        "\n",
        "Start date: 2022-08-02\n",
        "\n",
        "Arrival date: 2022-08-11\n",
        "\n",
        "Route:\n",
        "\n",
        "(1)Date: 2022-08-02  From: Malaysia Warehouse  To: Malaysia Port  By: Truck\n",
        "\n",
        "(2)Date: 2022-08-03  From: Malaysia Port  To: Gujarat Port  By: Sea\n",
        "\n",
        "(3)Date: 2022-08-10  From: Gujarat Port  To: Gujarat Warehouse  By: Truck\n",
        "\n",
        "------------------------------------\n",
        "\n",
        "Goods-3  Category: Banana\n",
        "\n",
        "Start date: 2022-08-03\n",
        "\n",
        "Arrival date: 2022-08-14\n",
        "\n",
        "Route:\n",
        "\n",
        "(1)Date: 2022-08-03  From: Singapore Warehouse  To: Malaysia Warehouse  By: Truck\n",
        "\n",
        "(2)Date: 2022-08-05  From: Malaysia Warehouse  To: Malaysia Port  By: Truck\n",
        "\n",
        "(3)Date: 2022-08-06  From: Malaysia Port  To: Gujarat Port  By: Sea\n",
        "\n",
        "(4)Date: 2022-08-13  From: Gujarat Port  To: Gujarat Warehouse  By: Truck\n",
        "\n",
        "------------------------------------\n",
        "\n",
        "Goods-4  Category: Seed\n",
        "\n",
        "Start date: 2022-08-04\n",
        "\n",
        "Arrival date: 2022-08-14\n",
        "\n",
        "Route:\n",
        "\n",
        "(1)Date: 2022-08-04  From: Singapore Warehouse  To: Malaysia Warehouse  By: Truck\n",
        "\n",
        "(2)Date: 2022-08-05  From: Malaysia Warehouse  To: Malaysia Port  By: Truck\n",
        "\n",
        "(3)Date: 2022-08-06  From: Malaysia Port  To: Gujarat Port  By: Sea\n",
        "\n",
        "(4)Date: 2022-08-13  From: Gujarat Port  To: Gujarat Warehouse  By: Truck\n",
        "\n",
        "------------------------------------\n",
        "Goods-5  Category: Mango\n",
        "\n",
        "\n",
        "Start date: 2022-08-05\n",
        "\n",
        "Arrival date: 2022-08-18\n",
        "\n",
        "Route:\n",
        "\n",
        "(1)Date: 2022-08-06  From: Mumbai Warehouse  To: Gujarat Warehouse  By: Truck\n",
        "\n",
        "(2)Date: 2022-08-07  From: Gujarat Warehouse  To: Gujarat Port  By: Truck\n",
        "\n",
        "(3)Date: 2022-08-08  From: Gujarat Port  To: Singapore Port  By: Sea\n",
        "\n",
        "(4)Date: 2022-08-16  From: Singapore Port  To: Singapore Warehouse  By: Truck\n",
        "\n",
        "(5)Date: 2022-08-17  From: Singapore Warehouse  To: Malaysia Warehouse  By: Truck\n",
        "\n",
        "------------------------------------\n",
        "\n",
        "Goods-6  Category: Apple\n",
        "\n",
        "Start date: 2022-08-06\n",
        "\n",
        "Arrival date: 2022-08-17\n",
        "\n",
        "Route:\n",
        "\n",
        "(1)Date: 2022-08-07  From: Gujarat Warehouse  To: Gujarat Port  By: Truck\n",
        "\n",
        "(2)Date: 2022-08-08  From: Gujarat Port  To: Singapore Port  By: Sea\n",
        "\n",
        "(3)Date: 2022-08-16  From: Singapore Port  To: Singapore Warehouse  By: Truck\n",
        "\n",
        "------------------------------------\n",
        "\n",
        "Goods-7  Category: Litchi\n",
        "\n",
        "Start date: 2022-08-07\n",
        "\n",
        "Arrival date: 2022-08-08\n",
        "\n",
        "Route:\n",
        "\n",
        "(1)Date: 2022-08-07  From: Malaysia Warehouse  To: Singapore Warehouse  By: Truck\n",
        "\n",
        "------------------------------------\n",
        "\n",
        "Goods-8  Category: Coffee\n",
        "\n",
        "Start date: 2022-08-08\n",
        "\n",
        "Arrival date: 2022-08-09\n",
        "\n",
        "Route:\n",
        "\n",
        "(1)Date: 2022-08-08  From: Mumbai Warehouse  To: Gujarat Warehouse  By: Truck"
      ],
      "metadata": {
        "id": "znd9LsBjhpjq"
      }
    }
  ]
}