Bonus studio 4: from a model result to a one-page decision memo

Prepared before the session (under 3 minutes). From the repository root, save this as memo_inputs_b04.py (outside the content folders is fine). Do not run it until the second block, because its output is what the framing block asks you to commit to first. Run it with uv run --project python python memo_inputs_b04.py; it takes well under a minute and writes outputs/bonus04_val.csv, one row per validation departure with f_base, f_model, bookings_final, capacity and full.

# memo_inputs_b04.py - the "model result" you will write a decision memo about (synthetic airline data)
import os
import sqlite3
import numpy as np
import pandas as pd
from sklearn.ensemble import HistGradientBoostingRegressor

C_FALSE, C_MISS = 60, 120   # HYPOTHETICAL cost units per flight: closed L but it did not fill / kept L open and it filled
con = sqlite3.connect("python/artifacts/airline_sample.sqlite")
df = pd.read_sql("""
    SELECT f.date, f.flight_no, f.route, f.capacity, f.weekday, f.holiday, f.bookings_final,
           b.cumulative_bookings AS bk14
    FROM flight_day f JOIN booking_curve b ON b.date = f.date AND b.flight_no = f.flight_no
    WHERE b.days_to_departure = 14""", con)
df["month"] = pd.to_datetime(df["date"]).dt.month
tr = df[df.date <= "2023-12-17"].copy()                      # labels known by the first forecast origin (2023-12-18)
va = df[(df.date >= "2024-01-01") & (df.date <= "2024-06-30")].copy()

ratio = (tr.bookings_final / tr.bk14.clip(lower=1)).groupby([tr.route, tr.weekday]).median().rename("ratio")
va = va.join(ratio, on=["route", "weekday"])
va["f_base"] = va.bk14 * va.ratio                            # pickup baseline

codes = {r: i for i, r in enumerate(sorted(tr.route.unique()))}
X = ["bk14", "capacity", "weekday", "holiday", "month", "route_c"]
tr["route_c"], va["route_c"] = tr.route.map(codes), va.route.map(codes)
va["f_model"] = HistGradientBoostingRegressor(random_state=0).fit(tr[X], tr.bookings_final).predict(va[X])

va["full"] = va.bookings_final >= va.capacity               # the flight filled
for name in ["f_base", "f_model"]:
    err = va[name] - va.bookings_final
    close = va[name] >= va.capacity                          # rule: close L when forecast reaches capacity
    miss, false = (~close & va.full).sum(), (close & ~va.full).sum()
    print(f"{name:8s} MAE {err.abs().mean():.2f}  bias {err.mean():+.2f}  WAPE {err.abs().sum() / va.bookings_final.sum():.4f}"
          f"  missed {miss}  false {false}  cost {C_FALSE * false + C_MISS * miss}")
print("validation flights:", len(va), " filled:", int(va.full.sum()))
os.makedirs("outputs", exist_ok=True)
va.to_csv("outputs/bonus04_val.csv", index=False)

Both forecasts are for the same 3,458 validation departures (2024-01-01 to 2024-06-30). The model was fitted on departures up to 2023-12-17 only. The cost units are hypothetical; the director and her opinions are simulated. You need no project files from Weeks 1-3 to do this studio.