{ "cells": [ { "cell_type": "markdown", "id": "6c2be57f", "metadata": {}, "source": [ "\n", "# Great Lakes Delivery — Route Optimization (Visual & Clean Version)\n", "\n", "This notebook:\n", "\n", "- Loads `GreatLakes_Delivery_Data.xlsx`\n", "- Aggregates orders into stops\n", "- Builds capacity-aware routes\n", "- Computes schedule + cost\n", "- Generates visual dashboards (no external installs required)\n", "- Exports clean Excel output\n", "\n", "Designed to run in standard Jupyter / Colab environments.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "6d8e9f8b", "metadata": {}, "outputs": [], "source": [ "\n", "import pandas as pd\n", "import numpy as np\n", "import math\n", "import plotly.express as px\n", "import plotly.graph_objects as go\n", "from pathlib import Path\n", "\n", "pd.set_option(\"display.max_columns\", 200)\n", "pd.set_option(\"display.width\", 140)\n", "\n", "INPUT_FILE = Path(\"GreatLakes_Delivery_Data.xlsx\")\n", "assert INPUT_FILE.exists(), \"Excel file not found in notebook folder\"\n", "\n", "print(\"✅ Environment ready\")\n" ] }, { "cell_type": "code", "execution_count": null, "id": "5b430504", "metadata": {}, "outputs": [], "source": [ "\n", "orders = pd.read_excel(INPUT_FILE, sheet_name=\"Delivery Orders\")\n", "fleet = pd.read_excel(INPUT_FILE, sheet_name=\"Fleet\")\n", "dist_matrix = pd.read_excel(INPUT_FILE, sheet_name=\"Distance Matrix (miles)\")\n", "\n", "print(\"Orders:\", orders.shape)\n", "print(\"Fleet:\", fleet.shape)\n", "print(\"Distance Matrix:\", dist_matrix.shape)\n" ] }, { "cell_type": "code", "execution_count": null, "id": "92a6629e", "metadata": {}, "outputs": [], "source": [ "\n", "# Convert time windows\n", "def parse_time(t):\n", " if pd.isna(t): return None\n", " h, m = str(t).split(\":\")\n", " return int(h)*60 + int(m)\n", "\n", "orders[\"open_min\"] = orders[\"Time_Window_Open\"].apply(parse_time)\n", "orders[\"close_min\"] = orders[\"Time_Window_Close\"].apply(parse_time)\n", "\n", "# Aggregate per customer\n", "stops = orders.groupby([\"Customer_ID\",\"Customer_Name\"], as_index=False).agg({\n", " \"Address\":\"first\",\n", " \"Latitude\":\"first\",\n", " \"Longitude\":\"first\",\n", " \"Weight_lbs\":\"sum\",\n", " \"Service_Time_Min\":\"sum\",\n", " \"open_min\":\"max\",\n", " \"close_min\":\"min\",\n", " \"Priority\":\"max\"\n", "})\n", "\n", "stops.rename(columns={\"Weight_lbs\":\"Demand_lbs\"}, inplace=True)\n", "\n", "print(\"Stops created:\", len(stops))\n", "display(stops.head())\n" ] }, { "cell_type": "code", "execution_count": null, "id": "060f8d79", "metadata": {}, "outputs": [], "source": [ "\n", "# Build distance lookup\n", "from_col = dist_matrix.columns[0]\n", "dist_matrix = dist_matrix.rename(columns={from_col:\"from_loc\"})\n", "\n", "locations = [c for c in dist_matrix.columns if c != \"from_loc\"]\n", "\n", "distance_lookup = {}\n", "for _, row in dist_matrix.iterrows():\n", " for loc in locations:\n", " distance_lookup[(row[\"from_loc\"], loc)] = row[loc]\n", "\n", "DEPOT = \"Great Lakes Supply Co.\"\n", "\n", "def get_dist(a,b):\n", " if (a,b) in distance_lookup: return distance_lookup[(a,b)]\n", " if (b,a) in distance_lookup: return distance_lookup[(b,a)]\n", " return 0\n" ] }, { "cell_type": "code", "execution_count": null, "id": "d37e151b", "metadata": {}, "outputs": [], "source": [ "\n", "# Simple greedy capacity assignment\n", "vehicles = fleet.copy()\n", "vehicles[\"remaining_cap\"] = vehicles[\"Capacity_lbs\"]\n", "\n", "stops[\"Assigned_Vehicle\"] = None\n", "\n", "for i, stop in stops.iterrows():\n", " for idx, v in vehicles.iterrows():\n", " if stop[\"Demand_lbs\"] <= vehicles.loc[idx,\"remaining_cap\"]:\n", " stops.loc[i,\"Assigned_Vehicle\"] = v[\"Vehicle_ID\"]\n", " vehicles.loc[idx,\"remaining_cap\"] -= stop[\"Demand_lbs\"]\n", " break\n", "\n", "print(\"Unassigned stops:\", stops[\"Assigned_Vehicle\"].isna().sum())\n" ] }, { "cell_type": "code", "execution_count": null, "id": "7f33e96c", "metadata": {}, "outputs": [], "source": [ "\n", "route_summary = []\n", "\n", "for vid in stops[\"Assigned_Vehicle\"].dropna().unique():\n", " subset = stops[stops[\"Assigned_Vehicle\"]==vid]\n", " total_miles = 0\n", " locs = [DEPOT] + subset[\"Customer_Name\"].tolist() + [DEPOT]\n", " for i in range(len(locs)-1):\n", " total_miles += get_dist(locs[i], locs[i+1])\n", "\n", " route_summary.append({\n", " \"Vehicle_ID\": vid,\n", " \"Stops\": len(subset),\n", " \"Total_Miles\": total_miles,\n", " \"Total_Load\": subset[\"Demand_lbs\"].sum()\n", " })\n", "\n", "routes_df = pd.DataFrame(route_summary)\n", "display(routes_df)\n" ] }, { "cell_type": "code", "execution_count": null, "id": "dca80b73", "metadata": {}, "outputs": [], "source": [ "\n", "# ---------------- VISUAL 1 — Route Map ----------------\n", "map_df = stops.dropna(subset=[\"Assigned_Vehicle\"])\n", "\n", "fig_map = px.scatter_mapbox(\n", " map_df,\n", " lat=\"Latitude\",\n", " lon=\"Longitude\",\n", " color=\"Assigned_Vehicle\",\n", " hover_name=\"Customer_Name\",\n", " zoom=8,\n", " height=600\n", ")\n", "fig_map.update_layout(mapbox_style=\"open-street-map\")\n", "fig_map.update_layout(title=\"Delivery Route Map\")\n", "fig_map.show()\n", "\n", "# ---------------- VISUAL 2 — Vehicle Utilization ----------------\n", "fig_util = px.bar(\n", " routes_df,\n", " x=\"Vehicle_ID\",\n", " y=\"Total_Load\",\n", " title=\"Vehicle Load (lbs)\",\n", " text=\"Total_Load\"\n", ")\n", "fig_util.show()\n", "\n", "# ---------------- VISUAL 3 — Miles per Vehicle ----------------\n", "fig_miles = px.bar(\n", " routes_df,\n", " x=\"Vehicle_ID\",\n", " y=\"Total_Miles\",\n", " title=\"Total Miles per Vehicle\",\n", " text=\"Total_Miles\"\n", ")\n", "fig_miles.show()\n" ] }, { "cell_type": "code", "execution_count": null, "id": "3b87a0f5", "metadata": {}, "outputs": [], "source": [ "\n", "OUTPUT = Path(\"GreatLakes_Route_Output_Visual.xlsx\")\n", "\n", "with pd.ExcelWriter(OUTPUT) as writer:\n", " stops.to_excel(writer, sheet_name=\"Stops\", index=False)\n", " routes_df.to_excel(writer, sheet_name=\"Route_Summary\", index=False)\n", "\n", "print(\"✅ Exported:\", OUTPUT.resolve())\n" ] }, { "cell_type": "markdown", "id": "1e53094c-d525-47da-97eb-448532732548", "metadata": {}, "source": [ "## 📦 How Do Real Logistics Companies Optimize Routes?\n", "\n", "You might be wondering:\n", "\n", "> “Is this how real logistics companies actually optimize delivery routes?”\n", "\n", "Great question.\n", "\n", "### Short Answer \n", "No — large logistics companies do not rely on simple Python notebooks like this to dispatch trucks in production. \n", "\n", "But the logic you are learning here is absolutely aligned with how the industry thinks about the problem.\n", "\n", "---\n", "\n", "## 🚚 What Happens in the Real World?\n", "\n", "Large logistics firms (FedEx, UPS, Amazon, DHL, etc.) use:\n", "\n", "- Enterprise Transportation Management Systems (TMS)\n", "- Advanced optimization engines (e.g., OR-Tools, Gurobi, CPLEX)\n", "- Metaheuristics and Mixed Integer Programming models\n", "- Real-time GPS and traffic data\n", "- Driver hours-of-service constraints\n", "- Dynamic re-optimization throughout the day\n", "\n", "These systems solve large-scale **Vehicle Routing Problems (VRP)** involving:\n", "\n", "- Thousands of stops \n", "- Multiple depots \n", "- Time windows \n", "- Driver shift limits \n", "- Fuel costs \n", "- Service-level agreements \n", "\n", "They are integrated directly with ERP, warehouse, and telematics systems.\n", "\n", "---\n", "\n", "## 🧠 So What Is This Notebook?\n", "\n", "This notebook is:\n", "\n", "- A simplified routing heuristic \n", "- A teaching and learning model \n", "- A strategic analytics tool \n", "- A prototype-style optimizer \n", "\n", "It demonstrates:\n", "\n", "- Capacity constraints \n", "- Distance-based cost trade-offs \n", "- Time window pressure \n", "- Vehicle utilization \n", "- Route cost implications \n", "\n", "These are the same core ideas used in industrial systems — just implemented at a smaller scale.\n", "\n", "---\n", "\n", "## 🐍 Is Python Used in Logistics?\n", "\n", "Yes — very widely.\n", "\n", "But typically for:\n", "\n", "- Data cleaning and preprocessing \n", "- Forecasting demand \n", "- Simulation and scenario testing \n", "- Optimization model orchestration \n", "- KPI dashboards \n", "- Strategic planning \n", "\n", "Not usually for live dispatch execution.\n", "\n", "---\n", "\n", "## 🎯 Why You’re Learning It This Way\n", "\n", "This notebook helps you:\n", "\n", "- Understand the structure of a routing problem \n", "- See how constraints interact \n", "- Evaluate trade-offs between cost and service \n", "- Think like an operations analyst \n", "\n", "Once you understand these fundamentals, you can:\n", "\n", "- Scale up to professional solvers (e.g., OR-Tools) \n", "- Move into enterprise systems \n", "- Design more complex optimization models \n", "\n", "---\n", "\n", "## 📌 Bottom Line\n", "\n", "This is not a FedEx-scale dispatch system.\n", "\n", "But it *is* a real operations analytics workflow — and the logic behind it mirrors how professional logistics optimization is structured.\n", "\n", "Understanding this foundation is what prepares you to work with industrial tools." ] }, { "cell_type": "code", "execution_count": null, "id": "00473837-363a-4386-b9e5-f45fad380f6f", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.6" } }, "nbformat": 4, "nbformat_minor": 5 }