Forecasting studio 5: repair an invalid backtest and explain horizon and segment errors

Prepare before the session (2 minutes). Save the following as forecasting-project/review/flawed_w05.py. It is scaffolding with deliberate flaws, so do not reuse it.

# flawed_w05.py - a rolling-origin backtest for the D-14 forecast (contains deliberate flaws; do not reuse)
import sqlite3
import pandas as pd
from sklearn.ensemble import HistGradientBoostingRegressor
from sklearn.metrics import mean_absolute_error

con = sqlite3.connect("python/artifacts/airline_sample.sqlite")
df = pd.read_sql("""
    SELECT f.date, f.flight_no, f.route, f.weekday, f.holiday, f.capacity, f.competitor_price,
           f.bookings_final, b.cumulative_bookings AS bk_d14
    FROM flight_day f
    JOIN booking_curve b ON b.date = f.date AND b.flight_no = f.flight_no AND b.days_to_departure = 14
""", con, parse_dates=["date"]).sort_values(["flight_no", "date"])

g = df.groupby("flight_no")["bookings_final"]
df["lag_7"] = g.shift(7)
df["roll_28"] = g.transform(lambda s: s.rolling(28, min_periods=7).mean())
df["snaive_364"] = g.shift(364)
feats = ["weekday", "holiday", "capacity", "competitor_price", "bk_d14", "lag_7", "roll_28"]

rows = []
for origin in pd.date_range("2023-07-01", "2024-06-01", freq="MS"):
    train = df[df["date"] < origin]
    test = df[(df["date"] >= origin) & (df["date"] < origin + pd.DateOffset(months=1))]
    m = HistGradientBoostingRegressor(random_state=0).fit(train[feats], train["bookings_final"])
    rows.append({"origin": origin.date(),
                 "mae_model": mean_absolute_error(test["bookings_final"], m.predict(test[feats])),
                 "mae_snaive": mean_absolute_error(test["bookings_final"], test["snaive_364"])})

res = pd.DataFrame(rows)
print(res.round(1).to_string(index=False))
print("mean MAE model", round(res["mae_model"].mean(), 1), "| seasonal naive", round(res["mae_snaive"].mean(), 1))

Run it from the repository root with uv run --project python python forecasting-project/review/flawed_w05.py. It takes well under a minute on a laptop. You also need your Saturday files: configs/backtest_w05.yaml, outputs/backtest_w05.csv, the availability-check code and the contract. The colleague and the RM lead are simulated.