Session 15 lecture: Capstone studio: combining evidence¶

# Course data setup. Run this cell first; you do not need to understand it yet.
# It downloads the Superstore sample from Tableau's website, checks it is the
# exact version this course was written for, and prepares the order-lines table.
import hashlib
import os
import urllib.request

import matplotlib.pyplot as plt
import pandas as pd

SOURCE_URL = "https://public.tableau.com/app/sample-data/sample_-_superstore.xls"
EXPECTED_SHA256 = "5045ed4fec49cae2cc0dfc5ab0d204ba09efa767fe00c996822499e8cc42fbbd"
SOURCE_FILE = os.environ.get("COURSE_XLS_PATH", "sample_-_superstore.xls")

try:
    import xlrd  # noqa: F401  (needed to read .xls files)
except ImportError:
    import subprocess
    import sys

    subprocess.check_call([sys.executable, "-m", "pip", "install", "-q", "xlrd"])

if not os.path.exists(SOURCE_FILE):
    urllib.request.urlretrieve(SOURCE_URL, SOURCE_FILE)

with open(SOURCE_FILE, "rb") as f:
    digest = hashlib.sha256(f.read()).hexdigest()
if digest != EXPECTED_SHA256:
    print("WARNING: Tableau has changed this sample file since the course was written.")
    print("Your numbers will differ from the lesson. Ask your facilitator for the course copy.")


def load_orders():
    """Return one row per order line, orders placed 2023-01-01 to 2025-12-31."""
    raw = pd.read_excel(SOURCE_FILE, sheet_name="Orders")
    returns = pd.read_excel(SOURCE_FILE, sheet_name="Returns")

    # Orders with more than one Customer ID (a known data issue), found before filtering.
    ids_per_order = raw.groupby("Order ID")["Customer ID"].nunique()
    conflict_orders = set(ids_per_order[ids_per_order > 1].index)

    in_scope = (raw["Order Date"] >= "2023-01-01") & (raw["Order Date"] < "2026-01-01")
    df = raw.loc[in_scope].copy()

    df = df.rename(columns={
        "Row ID": "row_id", "Order ID": "order_id", "Order Date": "order_date",
        "Ship Date": "ship_date", "Ship Mode": "ship_mode", "Customer ID": "customer_id",
        "Segment": "segment", "Country/Region": "country_region", "Region": "region",
        "Category": "category", "Sub-Category": "sub_category", "Product ID": "product_id",
        "Product Name": "product_name", "Sales": "sales", "Quantity": "quantity",
        "Discount": "discount", "Profit": "profit",
    })
    df["returned_order_flag"] = df["order_id"].isin(set(returns["Order ID"])).astype(int)
    df["customer_order_conflict"] = df["order_id"].isin(conflict_orders).astype(int)
    df["customer_analysis_eligible"] = 1 - df["customer_order_conflict"]
    df["order_to_ship_days"] = (df["ship_date"] - df["order_date"]).dt.days
    df["order_month"] = df["order_date"].dt.to_period("M").dt.to_timestamp()

    columns = [
        "row_id", "order_id", "order_date", "ship_date", "ship_mode", "customer_id",
        "segment", "country_region", "region", "category", "sub_category", "product_id",
        "product_name", "sales", "quantity", "discount", "profit", "returned_order_flag",
        "customer_analysis_eligible", "customer_order_conflict", "order_to_ship_days",
        "order_month",
    ]
    return df[columns].sort_values("row_id").reset_index(drop=True)


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("Setup complete.")

Setup complete.

# Marketing data setup (synthetic). Run this cell first.
# Downloads the course's synthetic marketing 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

MARKETING_URL = "https://raw.githubusercontent.com/Aayush181509/AnalyticsLecture/main/data/synthetic/marketing-v1/"
MARKETING_SHA256 = {
    "marketing_daily.csv": "1cbfc8e1c91ae72d18364b2d13bdf6db513c4aec28c339ef8b91f87a60dc0a07",
    "ab_test.csv": "41a3662997111b66d7ab1eb899f4b32e26bd64c66284ce881db6b8a7300ad47f",
}
MARKETING_DIR = os.environ.get("COURSE_MARKETING_DIR", "")


def load_marketing(name):
    """Load one marketing-v1 file: 'marketing_daily.csv' or 'ab_test.csv'."""
    if MARKETING_DIR:
        with open(os.path.join(MARKETING_DIR, name), "rb") as f:
            data = f.read()
    else:
        data = urllib.request.urlopen(MARKETING_URL + name).read()
    if hashlib.sha256(data).hexdigest() != MARKETING_SHA256[name]:
        print("WARNING: this file differs from the course version. Ask your facilitator.")
    return pd.read_csv(io.BytesIO(data), parse_dates=["date"])


print("Marketing setup complete.")

Marketing setup complete.

# Finance data setup (synthetic). Run this cell first.
# Downloads the course's synthetic finance 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

FINANCE_URL = "https://raw.githubusercontent.com/Aayush181509/AnalyticsLecture/main/data/synthetic/finance-v1/"
FINANCE_SHA256 = {
    "finance_actuals.csv": "3aeb2773990f86c21a1536b8854d7290794315e8bc872420c2e5df77151ac7fd",
    "finance_budget.csv": "238dbec8e7e80a61068c12c48280ff4f9de91198879ee295366774f530efad60",
}
FINANCE_DIR = os.environ.get("COURSE_FINANCE_DIR", "")


def load_finance(name):
    """Load one finance-v1 file: 'finance_actuals.csv' or 'finance_budget.csv'."""
    if FINANCE_DIR:
        with open(os.path.join(FINANCE_DIR, name), "rb") as f:
            data = f.read()
    else:
        data = urllib.request.urlopen(FINANCE_URL + name).read()
    if hashlib.sha256(data).hexdigest() != FINANCE_SHA256[name]:
        print("WARNING: this file differs from the course version. Ask your facilitator.")
    return pd.read_csv(io.BytesIO(data), parse_dates=["month"], keep_default_na=False,
                       na_values={"amount": [""], "budget": [""]})


print("Finance setup complete.")

Finance setup complete.

# 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.

# HR data setup (synthetic). Run this cell first.
# Downloads the course's synthetic workforce 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

HR_URL = "https://raw.githubusercontent.com/Aayush181509/AnalyticsLecture/main/data/synthetic/hr-v1/"
HR_SHA256 = {
    "employees.csv": "7d59a66675d53b7d6de8cce6a4d3369ddd6268e51913e86f30e9560c019342a9",
    "department_history.csv": "12e6524a7838e9d5426c2bef116769ad850d5eaf11e10958a4e4ae13d21dca36",
    "recruitment.csv": "3a6c2046fd5cbb4a07d435f50e3d10e5df160314decd1e651718a5d0bd7fbc05",
}
HR_DATES = {
    "employees.csv": ["hire_date", "exit_date"],
    "department_history.csv": ["effective_from", "effective_to"],
    "recruitment.csv": ["opened_date", "offer_accepted_date", "hire_date"],
}
HR_DIR = os.environ.get("COURSE_HR_DIR", "")
CUTOFF = pd.Timestamp("2025-12-31")


def load_hr(name):
    """Load one hr-v1 file: 'employees.csv', 'department_history.csv' or 'recruitment.csv'."""
    if HR_DIR:
        with open(os.path.join(HR_DIR, name), "rb") as f:
            data = f.read()
    else:
        data = urllib.request.urlopen(HR_URL + name).read()
    if hashlib.sha256(data).hexdigest() != HR_SHA256[name]:
        print("WARNING: this file differs from the course version. Ask your facilitator.")
    return pd.read_csv(io.BytesIO(data), parse_dates=HR_DATES[name])


print("HR setup complete.")

HR setup complete.

import datetime
import platform

import numpy as np

ONE_DAY = datetime.timedelta(days=1)
MONTHS = pd.date_range("2025-01-01", "2025-12-01", freq="MS")   # the common calendar: 2025 only

orders = load_orders()
marketing = load_marketing("marketing_daily.csv")
actuals = load_finance("finance_actuals.csv")
budget = load_finance("finance_budget.csv")
shipments = load_shipments()
employees = load_hr("employees.csv")
history = load_hr("department_history.csv")

pd.DataFrame({
    "rows": [len(orders), len(marketing), len(actuals), len(budget), len(shipments), len(employees), len(history)],
}, index=["Superstore order lines", "Marketing campaign-days", "Finance postings", "Finance budget cells",
          "Shipments", "Employees", "Department intervals"])

def retail_monthly(orders):
    """Superstore, orders placed in 2025: Sales (sum of order lines) and distinct orders per month. Session 7."""
    r = orders[orders["order_date"].dt.year == 2025]
    return r.groupby("order_month").agg(sales=("sales", "sum"), orders=("order_id", "nunique")).reindex(MONTHS)


def marketing_monthly(daily):
    """marketing-v1: spend, attributed revenue and conversions per month (sums; rates come later). Session 12."""
    month = daily["date"].dt.to_period("M").dt.to_timestamp()
    m = daily.groupby(month).agg(spend=("spend", "sum"), attributed_revenue=("attributed_revenue", "sum"),
                                 conversions=("conversions", "sum"))
    return m.reindex(MONTHS)


def finance_cells(actuals, budget):
    """finance-v1: aggregate postings to (month, department, account) FIRST, then outer-join the budget. Session 10."""
    keys = ["month", "department", "account"]
    a = actuals.groupby(keys, as_index=False)["amount"].sum().rename(columns={"amount": "actual"})
    return a.merge(budget[keys + ["budget"]], on=keys, how="outer").fillna({"actual": 0.0})


def finance_monthly(cells):
    """Comparable scope only (known budget): actual, budget and variance per month."""
    known = cells[cells["budget"].notna()]
    m = known.groupby("month")[["actual", "budget"]].sum()
    m["variance"] = m["actual"] - m["budget"]
    return m.reindex(MONTHS)


def service_population(shipments):
    """operations-v1: label each shipment at the cutoff. Session 11."""
    s = shipments.copy()
    s["population"] = np.select(
        [s["status"] == "canceled", s["promised_date"].isna(), s["promised_date"] > CUTOFF],
        ["canceled", "unknown promise", "not yet due"], default="due")
    s["on_time"] = s["delivered_date"].notna() & (s["delivered_date"] <= s["promised_date"])
    return s


def operations_monthly(s):
    """On-time rate by promised month: on-time due shipments / ALL due shipments (pending ones count as failures)."""
    due = s[s["population"] == "due"]
    month = due["promised_date"].dt.to_period("M").dt.to_timestamp()
    m = due.groupby(month).agg(due=("on_time", "size"), on_time=("on_time", "sum"))
    m["on_time_rate"] = m["on_time"] / m["due"].where(m["due"] > 0)
    return m.reindex(MONTHS)


def active_on(table, day, start="effective_from", end="effective_to"):
    """Rows whose interval [start, end) contains `day`. Session 13."""
    day = pd.Timestamp(day)
    return int(((table[start] <= day) & (table[end].isna() | (table[end] > day))).sum())


def hr_department_monthly(history, department):
    """hr-v1: opening, exits and closing headcount of one department per month (exits = leaving the company)."""
    h = history.sort_values(["employee_id", "effective_from"]).copy()
    h["last_interval"] = ~h.duplicated("employee_id", keep="last")
    d = h[h["department"] == department]
    rows = []
    for m in MONTHS:
        end = m + pd.offsets.MonthEnd(0)
        rows.append({"opening": active_on(d, m - ONE_DAY),
                     "exits": int((d["last_interval"] & d["effective_to"].between(m, end)).sum()),
                     "closing": active_on(d, end)})
    return pd.DataFrame(rows, index=MONTHS)


retail_m = retail_monthly(orders)
marketing_m = marketing_monthly(marketing)
cells = finance_cells(actuals, budget)
finance_m = finance_monthly(cells)
s = service_population(shipments)
operations_m = operations_monthly(s)
hr_ops_m = hr_department_monthly(history, "Operations")
print("Five monthly tables built, one per dataset, each with", len(MONTHS), "months.")

Five monthly tables built, one per dataset, each with 12 months.

operations_m.assign(on_time_rate=operations_m["on_time_rate"].map("{:.1%}".format))

# Values for the register, each from its own dataset.
retail_total = retail_m["sales"].sum()
retail_q4_share = retail_m.loc["2025-10-01":, "sales"].sum() / retail_total

campaigns = marketing.groupby("campaign_id")[["spend", "attributed_revenue"]].sum()
campaigns["roas"] = campaigns["attributed_revenue"] / campaigns["spend"].where(campaigns["spend"] > 0)
roas_all = campaigns["attributed_revenue"].sum() / campaigns["spend"].sum()
below_one = campaigns[campaigns["roas"] < 1]

software = cells[(cells["department"] == "Technology") & (cells["account"] == "Software subscriptions")
                 & cells["budget"].notna()]
software_var_pct = (software["actual"].sum() - software["budget"].sum()) / software["budget"].sum()

dec = operations_m.loc["2025-12-01"]
jan_oct = operations_m.loc[:"2025-10-01"]
jan_oct_rate = jan_oct["on_time"].sum() / jan_oct["due"].sum()

ops_avg_headcount = ((hr_ops_m["opening"] + hr_ops_m["closing"]) / 2).mean()
ops_turnover = hr_ops_m["exits"].sum() / ops_avg_headcount

register = pd.DataFrame([
    {"claim": "Store sales are concentrated in the last quarter (context)",
     "source": "Superstore (Tableau sample)", "metric": "Oct-Dec Sales / 2025 Sales",
     "grain": "Order line, by order month", "period": "Orders placed in 2025",
     "value": f"{retail_q4_share:.0%} of {retail_total:,.0f} source units",
     "check": "Monthly sums equal the annual total",
     "limitation": "Fictitious sample store; a different context from the other four"},
    {"claim": "On-time delivery fell sharply in December",
     "source": "operations-v1 shipments", "metric": "On-time due / all due shipments",
     "grain": "Shipment, by promised month", "period": "Dec vs Jan-Oct 2025, cutoff 31 Dec",
     "value": f"{dec['on_time_rate']:.1%} of {int(dec['due'])} vs {jan_oct_rate:.1%}",
     "check": "Populations add back to all shipments",
     "limitation": "Descriptive; does not show why it fell"},
    {"claim": "The Operations team had high turnover in 2025",
     "source": "hr-v1 department history", "metric": "Exits / mean monthly average headcount",
     "grain": "Department interval, monthly", "period": "2025, cutoff 31 Dec",
     "value": f"{ops_turnover:.0%} ({hr_ops_m['exits'].sum()} exits)",
     "check": "Department exits add up to company exits",
     "limitation": "Cannot show causes or anyone's performance"},
    {"claim": "Some campaigns return less attributed revenue than they cost",
     "source": "marketing-v1 daily", "metric": "Attributed revenue / spend (ratio of sums)",
     "grain": "Campaign-day, summed by campaign", "period": "2025",
     "value": f"{len(below_one)} of {len(campaigns)} campaigns below 1; all: {roas_all:.2f}",
     "check": "Ratio of sums, not mean of daily ROAS",
     "limitation": "Synthetic attribution, not incremental effect"},
    {"claim": "Technology software spending runs well over budget",
     "source": "finance-v1 actuals + budget", "metric": "(Actual - budget) / budget, known budget",
     "grain": "Month x department x account cell", "period": "2025",
     "value": f"{software_var_pct:+.1%}",
     "check": "Aggregated before joining; budget not multiplied",
     "limitation": "Expense view only; no revenue or benefit side"},
], index=pd.Index(["E1 retail", "E2 operations", "E3 HR", "E4 marketing", "E5 finance"], name="id"))
register

print(f"Ratio of sums (correct):      {roas_all:.2f}")
print(f"Mean of the campaign ROAS:    {campaigns['roas'].mean():.2f}   <- gives a tiny-budget campaign equal weight")
campaigns.sort_values("roas", ascending=False).round(2)

Ratio of sums (correct):      4.12
Mean of the campaign ROAS:    8.43   <- gives a tiny-budget campaign equal weight

retail_2025 = orders[orders["order_date"].dt.year == 2025]
mk = marketing.assign(order_month=marketing["date"].dt.to_period("M").dt.to_timestamp())

bad = retail_2025.merge(mk[["order_month", "campaign_id", "spend"]], on="order_month")   # a false join

print(f"Order lines before the join:  {len(retail_2025):,}")
print(f"Rows after the join:          {len(bad):,}")
print(f"Sales before the join:        {retail_2025['sales'].sum():,.0f}")
print(f"'Sales' after the join:       {bad['sales'].sum():,.0f}")
print(f"Order IDs found in both Superstore and operations: "
      f"{len(set(orders['order_id']) & set(shipments['order_id']))}")

Order lines before the join:  2,634
Rows after the join:          562,051
Sales before the join:        613,934
'Sales' after the join:       131,112,132
Order IDs found in both Superstore and operations: 0

pd.DataFrame({
    "2025 total": [retail_m["sales"].sum(), marketing_m["attributed_revenue"].sum()],
    "units": ["source monetary units", "currency units"],
    "what it measures": ["Sales on order lines of a fictitious store",
                         "Purchase value allocated to campaigns, synthetic"],
}, index=["Superstore Sales", "Marketing attributed revenue"])

side_by_side = pd.concat({
    "Retail Sales (source units)": retail_m["sales"],
    "Marketing spend (currency units)": marketing_m["spend"],
    "Operations on-time rate": operations_m["on_time_rate"],
    "HR Operations headcount (closing)": hr_ops_m["closing"],
}, axis=1)
side_by_side.index = side_by_side.index.strftime("%Y-%m")

assert len(side_by_side) == 12 and side_by_side.index.is_unique, "expected exactly one row per month"
assert side_by_side.notna().all().all(), "a month is missing in one of the domains"
side_by_side.assign(**{"Operations on-time rate": side_by_side["Operations on-time rate"].map("{:.1%}".format)})

fig, axes = plt.subplots(2, 2, figsize=(11, 6.5), sharex=True)
panels = [
    ("Retail Sales (source units)", "Superstore: Sales by order month", "Source monetary units", 1),
    ("Marketing spend (currency units)", "Marketing: media spend", "Currency units", 1),
    ("Operations on-time rate", "Operations: on-time rate by promised month", "% of due shipments", 100),
    ("HR Operations headcount (closing)", "HR: Operations team headcount", "Employees at month end", 1),
]
x = np.arange(12)
for ax, (col, title, ylabel, scale) in zip(axes.flat, panels):
    ax.plot(x, side_by_side[col] * scale, marker="o", markersize=4, color="#2a78d6")
    ax.set_title(title, fontsize=10)
    ax.set_ylabel(ylabel)
    ax.set_ylim(0, None)
for ax in axes[1]:
    ax.set_xticks(x, [m[5:] for m in side_by_side.index])
    ax.set_xlabel("Month of 2025")
fig.suptitle("Four separate fictional contexts on one calendar: compare shapes, not causes",
             fontweight="bold", x=0.01, ha="left")
fig.text(0.01, -0.01, "Each panel comes from a different dataset with its own units. No rows are linked across panels.",
         fontsize=8, color="#555555")
fig.tight_layout()
plt.show()

side_by_side.corr().round(2)

scores = pd.DataFrame({
    "size":     [5, 4, 3, 2],
    "evidence": [4, 3, 2, 5],
    "cost":     [3, 3, 5, 4],
    "speed":    [4, 2, 5, 3],
    "urgency":  [5, 4, 2, 2],
}, index=["A: carrier and peak capacity", "B: Operations retention", "C: marketing budget shift", "D: software contracts"])

reasons = {
    "A: carrier and peak capacity": "E2: large December drop on many shipments; clean due-population contract",
    "B: Operations retention": "E3: high turnover, but causes unknown and effect slow",
    "C: marketing budget shift": "E4: cheap and fast, but attribution is not incremental",
    "D: software contracts": "E5: well-measured overspend, but a small share of spend",
}

weights = pd.Series({"size": 0.30, "evidence": 0.25, "cost": 0.15, "speed": 0.15, "urgency": 0.15})
assert abs(weights.sum() - 1) < 1e-9, "weights must add up to 1"

result = scores.assign(weighted=scores @ weights, reason=pd.Series(reasons)).sort_values("weighted", ascending=False)
result

def rerank(scores, weights, criterion, factor):
    """Multiply one criterion's weight by `factor`, renormalise to 1, and return the winner and its margin."""
    w = weights.copy()
    w[criterion] *= factor
    w = w / w.sum()
    total = (scores @ w).sort_values(ascending=False)
    return {"criterion": criterion, "weight now": round(w[criterion], 2), "winner": total.index[0],
            "margin over 2nd": round(total.iloc[0] - total.iloc[1], 2)}


one_at_a_time = pd.DataFrame([rerank(scores, weights, c, f) for c in weights.index for f in (0.5, 2.0)])
one_at_a_time

rng = np.random.default_rng(seed=15)
random_weights = rng.dirichlet(np.ones(len(weights)), size=2000)    # 2,000 weight sets, each adding to 1
totals = scores.to_numpy() @ random_weights.T                       # one column of totals per weight set
winners = pd.Series(scores.index[totals.argmax(axis=0)])
share_first = winners.value_counts(normalize=True).reindex(scores.index, fill_value=0)

ax = plt.gca()
ax.barh(share_first.index, share_first * 100, color=["#eb6834"] + ["#b8b6b0"] * 3)
ax.invert_yaxis()
ax.set_title("How often each option ranks first under 2,000 random weight sets")
ax.set_xlabel("% of weight sets in which the option ranks first")
ax.set_ylabel("Option")
plt.show()
share_first.map("{:.0%}".format).to_frame("ranked first")

def source_sha256(local_dir, url, name):
    """SHA-256 of a source file, read the same way as its setup cell reads it."""
    if local_dir:
        with open(os.path.join(local_dir, name), "rb") as f:
            return hashlib.sha256(f.read()).hexdigest()
    return hashlib.sha256(urllib.request.urlopen(url + name).read()).hexdigest()


def fingerprint(df):
    """First 12 characters of the SHA-256 of the table's CSV text: changes if any value changes."""
    return hashlib.sha256(df.to_csv(index=False).encode()).hexdigest()[:12]


versions = pd.DataFrame([
    ("Superstore", "sample_-_superstore.xls", EXPECTED_SHA256[:12], digest == EXPECTED_SHA256, len(orders), fingerprint(orders)),
    ("marketing-v1", "marketing_daily.csv", MARKETING_SHA256["marketing_daily.csv"][:12],
     source_sha256(MARKETING_DIR, MARKETING_URL, "marketing_daily.csv") == MARKETING_SHA256["marketing_daily.csv"],
     len(marketing), fingerprint(marketing)),
    ("finance-v1", "finance_actuals.csv", FINANCE_SHA256["finance_actuals.csv"][:12],
     source_sha256(FINANCE_DIR, FINANCE_URL, "finance_actuals.csv") == FINANCE_SHA256["finance_actuals.csv"],
     len(actuals), fingerprint(actuals)),
    ("finance-v1", "finance_budget.csv", FINANCE_SHA256["finance_budget.csv"][:12],
     source_sha256(FINANCE_DIR, FINANCE_URL, "finance_budget.csv") == FINANCE_SHA256["finance_budget.csv"],
     len(budget), fingerprint(budget)),
    ("operations-v1", "shipments.csv", OPERATIONS_SHA256["shipments.csv"][:12],
     source_sha256(OPERATIONS_DIR, OPERATIONS_URL, "shipments.csv") == OPERATIONS_SHA256["shipments.csv"],
     len(shipments), fingerprint(shipments)),
    ("hr-v1", "department_history.csv", HR_SHA256["department_history.csv"][:12],
     source_sha256(HR_DIR, HR_URL, "department_history.csv") == HR_SHA256["department_history.csv"],
     len(history), fingerprint(history)),
], columns=["dataset", "file", "sha256 (start)", "matches course version", "rows", "table fingerprint"])
versions

checks = {}

checks["Retail: monthly Sales add up to the 2025 total"] = np.isclose(
    retail_m["sales"].sum(), orders.loc[orders["order_date"].dt.year == 2025, "sales"].sum())
checks["Marketing: monthly spend adds up to the daily total"] = np.isclose(
    marketing_m["spend"].sum(), marketing["spend"].sum())
checks["Finance: aggregated cells keep every posting's amount"] = np.isclose(
    cells["actual"].sum(), actuals["amount"].sum())
checks["Finance: budget not multiplied by the join"] = np.isclose(
    cells["budget"].sum(), budget["budget"].sum())
populations = s["population"].value_counts()
checks["Operations: populations add back to all shipments"] = populations.sum() == len(shipments)
checks["Operations: monthly due counts add up to all due"] = operations_m["due"].sum() == populations["due"]
h = history.sort_values(["employee_id", "effective_from"])
dept_exits = (~h.duplicated("employee_id", keep="last") & h["effective_to"].between("2025-01-01", CUTOFF)).sum()
checks["HR: department exits add up to company exits"] = dept_exits == employees["exit_date"].between(
    "2025-01-01", CUTOFF).sum()

for name, ok in checks.items():
    assert ok, f"FAILED: {name}"
pd.Series({k: "PASS" for k in checks}).to_frame("result")

print("Python ", platform.python_version())
print("pandas ", pd.__version__)
print("numpy  ", np.__version__)
print("Random seed used for the weight test: 15")

Python  3.12.13
pandas  2.2.3
numpy   2.5.4
Random seed used for the weight test: 15

top = result.index[0]
print(
    f"Recommendation: fund option {top}.\n\n"
    f"Evidence: in the operations data, the on-time rate for shipments promised in December 2025 was "
    f"{dec['on_time_rate']:.0%} of {int(dec['due'])} due shipments, against {jan_oct_rate:.0%} for January to October "
    f"(register E2). In the HR data, the Operations team's 2025 turnover was {ops_turnover:.0%} of its average headcount "
    f"(E3). In the marketing data, {len(below_one)} of {len(campaigns)} campaigns returned less attributed revenue than "
    f"they cost (E4), and in the finance data Technology software ran {software_var_pct:+.0%} against budget (E5).\n\n"
    f"Trade-off: with the stated weights, option {top[0]} scores highest; it ranks first in {share_first[top]:.0%} of 2,000 random "
    f"weight sets.\n\n"
    f"Limits: the five datasets are separate fictional contexts, so no finding explains another. All findings are "
    f"descriptive; none shows a cause. Next evidence: delivery results by carrier for the next peak, and a review of "
    f"why December failed before committing the full budget."
)

Recommendation: fund option A: carrier and peak capacity.

Evidence: in the operations data, the on-time rate for shipments promised in December 2025 was 55% of 790 due shipments, against 88% for January to October (register E2). In the HR data, the Operations team's 2025 turnover was 54% of its average headcount (E3). In the marketing data, 2 of 7 campaigns returned less attributed revenue than they cost (E4), and in the finance data Technology software ran +17% against budget (E5).

Trade-off: with the stated weights, option A scores highest; it ranks first in 78% of 2,000 random weight sets.

Limits: the five datasets are separate fictional contexts, so no finding explains another. All findings are descriptive; none shows a cause. Next evidence: delivery results by carrier for the next peak, and a review of why December failed before committing the full budget.