Unit 01.03: Build the analysis table on purpose

The analysis table is a deliberate object: one row per unit, one column per planned variable, and nothing else.

Build it on purpose, not by accident

Most statistical mistakes are visible in the table the test ran on — the wrong grain, an extra column that invited an unplanned comparison, a group variable left as text so its reference level is alphabetical.

So construct it explicitly. Select the planned columns. Set the grain. Convert grouping variables to Categorical with the category order you intend, because that order decides which group becomes the reference in any model you fit later.

Leaving extra columns in is not neutral. Each one is another comparison available when the planned one disappoints, and that is the mechanism behind most irreproducible findings.

This block reduces a raw extract to its analysis table.

import pandas as pd

raw = pd.DataFrame({
    "employee_id": range(1, 9),
    "site":        ["pilot", "pilot", "comparison", "comparison",
                    "pilot", "comparison", "pilot", "comparison"],
    "wellbeing":   [62, 71, 58, 55, 68, 60, 74, 57],
    "department":  ["ops", "eng", "ops", "sales", "eng", "eng", "ops", "sales"],
    "extracted_at": pd.Timestamp("2026-07-29"),
})

analysis = (raw[["employee_id", "site", "wellbeing"]]
            .assign(site=lambda d: pd.Categorical(
                d["site"], categories=["comparison", "pilot"])))

print("Unit of analysis: one employee")
print(f"Rows: {len(analysis)}   Columns: {analysis.shape[1]}")
print("Dropped from raw:", sorted(set(raw.columns) - set(analysis.columns)))
print()
print(analysis.groupby("site", observed=True)["wellbeing"]
      .agg(["count", "mean"]).round(2))
print()
print("`department` was not in the plan, so it is out. Leaving it in makes a")
print("second comparison available the moment the first one disappoints.")
print("Setting the category order fixes which group is the reference later.")

Eight rows and 3 columns survive; department and extracted_at are dropped. The grouped summary shows four employees per site with means of 57.50 and 68.75. Dropping department is not a claim that it is irrelevant — it says department was not in the plan, so any analysis of it is a secondary analysis and must be labelled as one.

The mistake this prevents

The mistake is analysing whatever DataFrame the import produced. It usually has the wrong grain, and it always has more columns than the plan named.

Takeaway

Construct the analysis table as its own step: select the planned columns, set the grain, and make grouping variables Categorical with deliberate category order. Anything outside the plan is a secondary analysis and says so.

Mark complete