Session 11 lab: Are deliveries on time?¶

# Operations data setup (synthetic). Run this cell first.
# Downloads the course's synthetic shipment data from GitHub and checks it is the exact version this course uses.
import hashlib
import io
import os
import urllib.request

import pandas as pd

OPERATIONS_URL = "https://raw.githubusercontent.com/Aayush181509/AnalyticsLecture/main/data/synthetic/operations-v1/"
OPERATIONS_SHA256 = {"shipments.csv": "7b5c0860beb2a16ba4f84a3dd0b5bdf9d88e968b720c6541f1dcb7d688c75687"}
OPERATIONS_DIR = os.environ.get("COURSE_OPERATIONS_DIR", "")
CUTOFF = pd.Timestamp("2025-12-31")


def load_shipments():
    """Load operations-v1 shipments: one row per shipment, known at the end of 2025-12-31."""
    if OPERATIONS_DIR:
        with open(os.path.join(OPERATIONS_DIR, "shipments.csv"), "rb") as f:
            data = f.read()
    else:
        data = urllib.request.urlopen(OPERATIONS_URL + "shipments.csv").read()
    if hashlib.sha256(data).hexdigest() != OPERATIONS_SHA256["shipments.csv"]:
        print("WARNING: this file differs from the course version. Ask your facilitator.")
    return pd.read_csv(io.BytesIO(data), parse_dates=["dispatch_date", "promised_date", "delivered_date"])


print("Operations setup complete.")

Operations setup complete.

import matplotlib.pyplot as plt
import numpy as np

pd.set_option("display.float_format", "{:,.2f}".format)
plt.rcParams.update({
    "figure.figsize": (8, 4),
    "axes.spines.top": False,
    "axes.spines.right": False,
    "axes.grid": True,
    "axes.axisbelow": True,
    "grid.color": "#e6e5e1",
    "axes.prop_cycle": plt.cycler(color=["#2a78d6", "#eb6834", "#1baf7a"]),
    "axes.titleweight": "bold",
    "axes.titlelocation": "left",
})
print("Plot style ready.")

Plot style ready.

shipments = load_shipments()
print("Rows:", len(shipments))
print("shipment_id unique:", shipments["shipment_id"].is_unique, "| order_id unique:", shipments["order_id"].is_unique)
print()
print(shipments["status"].value_counts().to_string())
shipments[["shipment_id", "order_id", "carrier", "dispatch_date", "promised_date", "delivered_date", "status"]].head()

Rows: 5893
shipment_id unique: True | order_id unique: False

status
delivered     5603
in_transit     250
canceled        40

# 1. Run the cell. Does every 'delivered' row have a delivered_date? Does any other status have one?
# 2. Change check_column to "promised_date". Which status has blank promise dates?
# 3. Read the date checks. Which counts must be zero, and which are allowed to be above zero?
check_column = "delivered_date"

print(f"Blank {check_column} by status:")
display(pd.crosstab(shipments["status"], shipments[check_column].isna().map({True: "blank", False: "known"})))

date_checks = pd.Series({
    "delivered before dispatch": (shipments["delivered_date"] < shipments["dispatch_date"]).sum(),
    "promised before dispatch": (shipments["promised_date"] < shipments["dispatch_date"]).sum(),
    "dispatched after cutoff": (shipments["dispatch_date"] > CUTOFF).sum(),
    "delivered after cutoff": (shipments["delivered_date"] > CUTOFF).sum(),
    "promised after cutoff": (shipments["promised_date"] > CUTOFF).sum(),
}, name="rows")
date_checks

Blank delivered_date by status:

delivered before dispatch     0
promised before dispatch      0
dispatched after cutoff       0
delivered after cutoff        0
promised after cutoff        99
Name: rows, dtype: int64

def add_service_columns(df, cutoff=CUTOFF):
    """Label each shipment's population at the cutoff and add on-time, pending and lead-time columns."""
    out = df.copy()
    out["population"] = np.select(
        [out["status"] == "canceled", out["promised_date"].isna(), out["promised_date"] > cutoff],
        ["canceled", "unknown promise", "not yet due"],
        default="due",
    )
    delivered = out["delivered_date"].notna()
    out["on_time"] = delivered & (out["delivered_date"] <= out["promised_date"])
    out["pending"] = ~delivered & (out["status"] != "canceled")
    overdue = (out["population"] == "due") & out["pending"]
    out["days_past_due"] = (cutoff - out["promised_date"]).dt.days.clip(lower=0).where(overdue)
    out["lead_days"] = (out["delivered_date"] - out["dispatch_date"]).dt.days
    out["promised_month"] = out["promised_date"].dt.to_period("M")
    return out


s = add_service_columns(shipments)
due = s[s["population"] == "due"]
print("Service columns added.")

Service columns added.

# 1. Run the cell. Do the four groups add up to all shipments? Do due + not yet due + unknown promise add up to eligible?
# 2. Change show to "not yet due". How many of them were already delivered? Why are they still excluded?
# 3. Change show to "unknown promise". What status do they have? Write one sentence a manager would read about them.
show = "due"

groups = s.pivot_table(index="population", columns="status", values="shipment_id", aggfunc="count", fill_value=0, margins=True, margins_name="All")
display(groups)

eligible = s[s["population"] != "canceled"]
print("All groups add up to all shipments:", groups.loc["All", "All"] == len(s))
print("Eligible:", len(eligible), "| due + not yet due + unknown promise:",
      (s["population"].isin(["due", "not yet due", "unknown promise"])).sum())
print()
print(f"First rows of '{show}':")
s.loc[s["population"] == show, ["shipment_id", "carrier", "dispatch_date", "promised_date", "delivered_date", "status"]].head()

All groups add up to all shipments: True
Eligible: 5853 | due + not yet due + unknown promise: 5853

First rows of 'due':

on_time = due["on_time"].sum()
pending_due = due["pending"].sum()
on_time_rate = on_time / len(due)

print(f"Due shipments:                   {len(due)}")
print(f"  delivered on time:             {on_time}")
print(f"  delivered late:                {(due['delivered_date'].notna() & ~due['on_time']).sum()}")
print(f"  still pending (overdue/today): {pending_due}")
print(f"On-time rate: {on_time} / {len(due)} = {on_time_rate:.1%}")
print()
delivered_only = due[due["delivered_date"].notna()]
print(f"Delivered-only rate (wrong):     {delivered_only['on_time'].mean():.1%} from {len(delivered_only)} shipments")

Due shipments:                   5742
  delivered on time:             4700
  delivered late:                885
  still pending (overdue/today): 157
On-time rate: 4700 / 5742 = 81.9%

Delivered-only rate (wrong):     84.2% from 5585 shipments

# 1. Run the cell. Which month is worst? In which months do the two lines differ, and why only there?
# 2. Change group_col to "dispatch_month". Does the picture change? Which grouping answers "how did we do on promises due in December"?
# 3. Find the month with the most due shipments. Is it also the worst month?
group_col = "promised_month"

data = s.assign(dispatch_month=s["dispatch_date"].dt.to_period("M"))
due_m = data[data["population"] == "due"]
monthly = due_m.groupby(group_col).agg(due=("on_time", "size"), on_time=("on_time", "sum"), pending=("pending", "sum"))
monthly["on_time_rate"] = monthly["on_time"] / monthly["due"]
delivered_m = due_m[due_m["delivered_date"].notna()].groupby(group_col)["on_time"].mean()
monthly["delivered_only_rate"] = delivered_m

ax = plt.gca()
x = monthly.index.to_timestamp()
ax.plot(x, monthly["on_time_rate"] * 100, marker="o", label="Correct: all due shipments")
ax.plot(x, monthly["delivered_only_rate"] * 100, marker="o", linestyle="--", label="Wrong: delivered only")
ax.set_title(f"On-time rate by {group_col.replace('_', ' ')}")
ax.set_ylabel("On-time rate (%)")
ax.legend(loc="lower left")
plt.show()
monthly.assign(on_time_rate=monthly["on_time_rate"].map("{:.1%}".format),
               delivered_only_rate=monthly["delivered_only_rate"].map("{:.1%}".format))

def service_table(df, by):
    """On-time rate as a ratio of counts for each group, with the due count visible."""
    due_rows = df[df["population"] == "due"]
    t = due_rows.groupby(by).agg(due=("on_time", "size"), on_time=("on_time", "sum"), pending=("pending", "sum"))
    t["on_time_rate"] = t["on_time"] / t["due"].where(t["due"] > 0)
    return t


def show_rates(t):
    """Format the rate column for reading, with n/a for empty groups."""
    return t.assign(on_time_rate=t["on_time_rate"].map(lambda r: "n/a" if pd.isna(r) else f"{r:.1%}"))

# 1. Run the cell. Which carrier has the lowest on-time rate? How many due shipments is that rate based on?
# 2. Change group_by to "warehouse". Which warehouse looks worst?
# 3. Change group_by to "service". Is Express more or less reliable than Standard?
group_by = "carrier"

t = service_table(s, group_by)
print(f"Overall (ratio of counts):      {t['on_time'].sum() / t['due'].sum():.1%}")
print(f"Mean of the group percentages:  {t['on_time_rate'].mean():.1%}   <- not the overall rate")
show_rates(t)

Overall (ratio of counts):      81.9%
Mean of the group percentages:  82.0%   <- not the overall rate

# 1. Run the cell. Within each carrier, how different are the three warehouses?
# 2. Which warehouse sends the largest share of its shipments with the weakest carrier?
# 3. Write two sentences: is the worst warehouse's problem its own process or its carrier mix? Use the mix-adjusted rates.
rows, cols = "warehouse", "carrier"

wc = service_table(s, [rows, cols])
rates = wc["on_time_rate"].unstack(cols)
volume = wc["due"].unstack(cols)
share = volume.div(volume.sum(axis=1), axis=0)

half = 1.96 * np.sqrt(wc["on_time_rate"] * (1 - wc["on_time_rate"]) / wc["due"])
wc_display = wc[["due", "on_time_rate"]].assign(low=(wc["on_time_rate"] - half).map("{:.1%}".format),
                                               high=(wc["on_time_rate"] + half).map("{:.1%}".format))

company_mix = volume.sum() / volume.sum().sum()
overall = service_table(s, rows)
print("On-time rate:"); display(rates.map("{:.1%}".format))
print("Share of each row's due shipments:"); display(share.map("{:.0%}".format))
print("Actual rate vs rate with the company's overall mix:")
display(pd.DataFrame({"actual": overall["on_time_rate"], "company mix": (rates * company_mix).sum(axis=1)}).map("{:.1%}".format))
print("Every cell, with due count and approximate 95% interval:")
show_rates(wc_display)

On-time rate:

Share of each row's due shipments:

Actual rate vs rate with the company's overall mix:

Every cell, with due count and approximate 95% interval:

# 1. Run the cell. For each service, how far apart are the mean and the median? Why?
# 2. Change by to ["service", "carrier"]. Which carrier has the longest 90th percentile for Standard?
# 3. Change service_to_plot to "Express". Draw the promise line at 2 days instead of 5 (edit promise_days).
by = "service"
service_to_plot = "Standard"
promise_days = 5

completed = s[s["delivered_date"].notna()]
lead = completed.groupby(by)["lead_days"].agg(
    completed="count", mean="mean", median="median", p90=lambda x: x.quantile(0.9), max="max")
display(lead)

part = completed[completed["service"] == service_to_plot]
carriers = sorted(part["carrier"].unique())
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(11, 3.8))
ax1.hist(part["lead_days"], bins=np.arange(0, part["lead_days"].max() + 2) - 0.5)
ax1.axvline(part["lead_days"].median(), color="black", linestyle="--", label="Median")
ax1.axvline(part["lead_days"].mean(), color="#eb6834", linewidth=2, label="Mean")
ax1.set_title(f"{service_to_plot}: lead time, completed shipments")
ax1.set_xlabel("Lead time (calendar days)")
ax1.set_ylabel("Shipments")
ax1.legend()
ax2.boxplot([part.loc[part["carrier"] == c, "lead_days"] for c in carriers],
            flierprops={"marker": ".", "markersize": 4, "alpha": 0.5})
ax2.set_xticks(range(1, len(carriers) + 1), carriers)
ax2.axhline(promise_days, color="#eb6834", linestyle="--", label=f"Promise: {promise_days} days")
ax2.set_title(f"{service_to_plot}: by carrier")
ax2.set_ylabel("Lead time (calendar days)")
ax2.legend()
fig.tight_layout()
plt.show()

# 1. Run the cell. Which carrier owns most of the overdue backlog? How old is its oldest shipment?
# 2. Change n_oldest to 20. Are the oldest shipments spread across carriers and warehouses, or concentrated?
# 3. Change backlog_by to "warehouse". Does the backlog follow the carrier or the warehouse?
n_oldest = 10
backlog_by = "carrier"

backlog = s[(s["population"] == "due") & s["pending"]]
summary = backlog.groupby(backlog_by)["days_past_due"].agg(
    pending_due="count", median_days_past_due="median", max_days_past_due="max")
summary["due_today"] = backlog[backlog["days_past_due"] == 0].groupby(backlog_by).size()
display(summary.fillna({"due_today": 0}))

backlog.sort_values(["days_past_due", "shipment_id"], ascending=[False, True]).head(n_oldest)[
    ["shipment_id", "warehouse", "carrier", "service", "promised_date", "days_past_due"]
].astype({"days_past_due": int})

# 1. Run the cell. How many due orders have more than one due shipment? How many of those are "half on time"?
# 2. Compare the number of due shipments with the number of due orders. Which one did the carriers actually deliver?
# 3. Change show_mixed to False to see split orders that were fully on time or fully late.
show_mixed = True

per_order = due.groupby("order_id").agg(shipments=("shipment_id", "count"), on_time=("on_time", "sum"))
split = per_order[per_order["shipments"] > 1]
mixed = split[(split["on_time"] > 0) & (split["on_time"] < split["shipments"])]

print(f"Due shipments: {len(due)} | due orders: {len(per_order)}")
print(f"Split orders (2+ due shipments): {len(split)} | of which partly on time, partly late: {len(mixed)}")
chosen = mixed.index if show_mixed else split.drop(mixed.index).index
due[due["order_id"].isin(chosen[:3])][["order_id", "shipment_id", "carrier", "promised_date", "delivered_date", "on_time"]]

Due shipments: 5742 | due orders: 5071
Split orders (2+ due shipments): 671 | of which partly on time, partly late: 154

# 1. Run the cell. Do the intervals of the weakest carrier and the others overlap?
# 2. Change months to [12]. With only December's due shipments, how much wider are the intervals?
# 3. Change group_by to "warehouse" (with all months). Which warehouse gaps survive the intervals?
group_by = "carrier"
months = list(range(1, 13))

subset = s[s["promised_date"].dt.month.isin(months)]
t = service_table(subset, group_by)
half = 1.96 * np.sqrt(t["on_time_rate"] * (1 - t["on_time_rate"]) / t["due"])

ax = plt.gca()
ax.errorbar(t["on_time_rate"] * 100, range(len(t)), xerr=half * 100, fmt="o", capsize=5)
ax.set_yticks(range(len(t)), [f"{g} ({n} due)" for g, n in zip(t.index, t["due"])])
ax.set_title(f"On-time rate by {group_by}, approximate 95% intervals")
ax.set_xlabel("On-time rate (%)")
plt.show()
t.assign(low=(t["on_time_rate"] - half).map("{:.1%}".format), high=(t["on_time_rate"] + half).map("{:.1%}".format),
         on_time_rate=t["on_time_rate"].map("{:.1%}".format))

# Colleague A: "Only delivered shipments have a delivery date, so I filter to those first."
data = shipments[shipments["status"] == "delivered"].copy()
data = data[data["promised_date"].notna() & (data["promised_date"] <= CUTOFF)]
data["on_time"] = data["delivered_date"] <= data["promised_date"]
dashboard_rate = data["on_time"].mean()

print(f"Dashboard A on-time rate: {dashboard_rate:.1%}  from {len(data)} shipments")
print(f"Correct rate (G3):        {on_time_rate:.1%}  from {len(due)} shipments")
print(f"Shipments missing from the dashboard: {len(due) - len(data)}")

Dashboard A on-time rate: 84.2%  from 5585 shipments
Correct rate (G3):        81.9%  from 5742 shipments
Shipments missing from the dashboard: 157

# Colleague B: "Customers care about orders, so I count distinct orders."
due_orders = due["order_id"].nunique()
on_time_orders = due.loc[due["on_time"], "order_id"].nunique()
dashboard_rate = on_time_orders / due_orders

print(f"Dashboard B on-time rate: {dashboard_rate:.1%}  ({on_time_orders} / {due_orders} orders)")
print(f"Correct rate (G3):        {on_time_rate:.1%}  ({on_time} / {len(due)} shipments)")

Dashboard B on-time rate: 83.1%  (4212 / 5071 orders)
Correct rate (G3):        81.9%  (4700 / 5742 shipments)

# Colleague C: "Blank dates break my formulas, so I fill them with the cutoff."
filled = shipments.copy()
filled["delivered_date"] = filled["delivered_date"].fillna(CUTOFF)
filled_s = add_service_columns(filled)
filled_due = filled_s[filled_s["population"] == "due"]

def snapshot_measures(table):
    """The measures a service dashboard shows, formatted for a before/after comparison."""
    due_rows = table[table["population"] == "due"]
    has_date = table["delivered_date"].notna()
    return {
        "shipments with a delivery date": f"{has_date.sum()}",
        "on-time due shipments": f"{due_rows['on_time'].sum()}",
        "on-time rate": f"{due_rows['on_time'].mean():.2%}",
        "mean lead time (days)": f"{table.loc[has_date, 'lead_days'].mean():.2f}",
        "due shipments still pending": f"{due_rows['pending'].sum()}",
    }


pd.DataFrame({"correct (G2–G3)": snapshot_measures(s), "dashboard C (filled)": snapshot_measures(filled_s)})

# 1. Run the cell. Which group combines a low rate, a large due volume and an old backlog?
# 2. Change group_by to "warehouse", then to ["warehouse", "carrier"]. Does your choice change?
# 3. Write your recommendation (see the template below the output).
group_by = "carrier"

t = service_table(s, group_by)
completed_std = s[s["delivered_date"].notna() & (s["service"] == "Standard")]
backlog = s[(s["population"] == "due") & s["pending"]]
evidence = pd.DataFrame({
    "due": t["due"],
    "share": t["due"] / t["due"].sum(),
    "on time": t["on_time_rate"],
    "Dec": service_table(s[s["promised_date"].dt.month == 12], group_by)["on_time_rate"],
    "pending": t["pending"],
    "median late": backlog.groupby(group_by)["days_past_due"].median(),
    "max late": backlog.groupby(group_by)["days_past_due"].max(),
    "p90 lead": completed_std.groupby(group_by)["lead_days"].quantile(0.9),
})
print("share = share of all due shipments; Dec = December on-time rate; pending = due shipments still pending;")
print("median/max late = days past due of pending shipments; p90 lead = Standard 90th percentile lead time, completed (days)")
formats = {"share": "{:.0%}", "on time": "{:.1%}", "Dec": "{:.1%}",
           "median late": "{:.1f}", "max late": "{:.0f}", "p90 lead": "{:.0f}"}
evidence.assign(**{col: evidence[col].map(lambda v, f=f: "n/a" if pd.isna(v) else f.format(v)) for col, f in formats.items()})

share = share of all due shipments; Dec = December on-time rate; pending = due shipments still pending;
median/max late = days past due of pending shipments; p90 lead = Standard 90th percentile lead time, completed (days)