One table, assembled on purpose — Joining and Reshaping Programme Data — Data Analysis

The table everything else is computed from

By the end of an assembly you want exactly one table: one row per unit of analysis, one column per measure, and nothing else. Every chart, every summary and every figure in the report comes off that table.

The discipline matters because the alternative is what usually happens — a notebook of eleven data frames named df, df2, merged, merged_final and merged_final_v2, where the table a figure was computed from is whichever one was in memory at the time. Nobody can reproduce that, including the person who wrote it.

Decide the unit of analysis before you write a line

The unit of analysis is the thing your report makes statements about. Write it down first, because it determines every join that follows.

# unit of analysis: one school x month
# rows: 24 schools x 3 months = 72

# unit of analysis: one school x month
# rows: 24 schools x 3 months = 72

If you cannot state it in one line, the analysis is not ready to be assembled. And if two figures in your report have different units — one per school, one per child — they belong to two tables, not one.

Build the spine first

The spine is the complete set of units you must report on, built from the authority, not from the data. Every measure is then attached to it.

schools = roster["school_id"].drop_duplicates()
months = pd.Series(["2024-02", "2024-03", "2024-04"], name="month")

analysis = (
    pd.MultiIndex.from_product([schools, months], names=["school_id", "month"])
    .to_frame(index=False)
)
print(len(analysis), "rows in the spine")

analysis <- tidyr::expand_grid(
  school_id = unique(roster$school_id),
  month = c("2024-02", "2024-03", "2024-04")
)

Spine first is the single most useful habit in this lesson. A school-month with no data now exists as a row with missing measures, which is visible, instead of not existing, which is not. It is the same argument as the period grid, applied to the whole assembly.

Attach one measure at a time, and assert at every seam

def attach(base, addition, on, name):
    before = len(base)
    out = base.merge(addition, on=on, how="left", validate="one_to_one")
    if len(out) != before:
        raise ValueError(f"{name}: row count changed {before} -> {len(out)}")
    filled = out[addition.columns.difference(on)].notna().any(axis=1).mean()
    print(f"{name}: attached, {filled:.1%} of spine rows matched")
    return out


analysis = attach(analysis, attendance_by_school_month, ["school_id", "month"], "attendance")
analysis = attach(analysis, enrolment_by_school, ["school_id"], "enrolment")
analysis = attach(analysis, feeding_by_school, ["school_id"], "feeding programme")

attach_measure <- function(base, addition, by, name) {
  before <- nrow(base)
  out <- dplyr::left_join(base, addition, by = by, relationship = "one-to-one")
  if (nrow(out) != before) {
    stop(sprintf("%s: row count changed %d -> %d", name, before, nrow(out)))
  }
  message(sprintf("%s: attached", name))
  out
}

analysis <- analysis |>
  attach_measure(attendance_by_school_month, c("school_id", "month"), "attendance") |>
  attach_measure(enrolment_by_school, "school_id", "enrolment")

Two properties worth having. The row count cannot change, so a fan-out is impossible by construction. And the match rate is printed for every attachment — an enrolment table that matched 100% of the spine and an attendance table that matched 92% tell you immediately where the holes are, before any figure is computed.

attach() looks like ceremony until the first time it fires. It will.

Carry provenance

Add columns that say where things came from and what they rest on.

analysis["source_register"] = "school-attendance-2024.v1.csv"
analysis["denominator_source"] = "roster 2024, enrolled students"
analysis["marks_definition"] = "true/false only; Y/N excluded"
analysis["extracted_on"] = "2026-07-27"

analysis <- analysis |>
  mutate(
    source_register    = "school-attendance-2024.v1.csv",
    denominator_source = "roster 2024, enrolled students",
    marks_definition   = "true/false only; Y/N excluded",
    extracted_on       = "2026-07-27"
  )

It looks redundant when every row has the same value. It stops being redundant the moment two extracts are concatenated, a second district arrives, or someone opens the CSV a year later with no idea which version of the register it came from. Constant columns are cheap; unlabelled numbers are not.

Assert what must be true of the finished table

assert analysis.duplicated(subset=["school_id", "month"]).sum() == 0
assert analysis["attendance_rate"].between(0, 1).all()
assert (analysis["days_present"] <= analysis["days_marked"]).all()
assert len(analysis) == 24 * 3

print(f"analysis table: {len(analysis)} rows, "
      f"{analysis['attendance_rate'].isna().sum()} without an attendance rate")

stopifnot(
  !any(duplicated(analysis[c("school_id", "month")])),
  all(dplyr::between(analysis$attendance_rate, 0, 1), na.rm = TRUE),
  all(analysis$days_present <= analysis$days_marked, na.rm = TRUE),
  nrow(analysis) == 24 * 3
)

Four assertions, and each corresponds to a lesson in this course: the grain is unique, the rate is a rate, the numerator cannot exceed its denominator, and the spine is complete. Run them at the end of assembly, every time.

What does not belong in the table

Intermediate columns. present_x, _merge, key_clean and the six columns you needed for one calculation. Drop them; they are how a table becomes unreadable.
Rows below your reporting threshold. If you would suppress a cell of four cases in a published table, do not carry it into a file that gets emailed.
Anything person-level. An analysis table at school-month grain has no business carrying a student identifier, and the day it does is the day it cannot be shared.

Save it with its grain in the name

analysis.to_csv("outputs/tables/attendance_by_school_month.csv", index=False)

readr::write_csv(analysis, here::here("outputs", "tables", "attendance_by_school_month.csv"))

And save the assembly script beside it, because the table is the output and the script is the method. If regenerating the table takes more than one command, regenerating it in three months will not happen.

scripts/
  01_read_and_validate.R      contract and validation   (cleaning course)
  02_clean.R                  rules and cleaning log    (cleaning course)
  03_assemble.R               spine, joins, assertions  (this course)
  04_report.R                 tables and figures

Four scripts, run in order, from raw files to final table. That is the layout Reproducible Analysis Workflows builds out later in the programme; adopting it now costs nothing and saves the reconstruction.

Where this course leaves you

You can take several files that describe the same programme and produce one table you would defend column by column — with the joins proved, the grain stated, the denominator sourced, the calendar complete, and the difference from what was reported upward written down rather than argued about.

The last course in this module, Data Quality Assessment, takes the last of those and makes it a routine: verification factors across a sample of facilities, the five quality dimensions, and a report that names the corrective action, the owner and the deadline — which is what turns a gap you found into a gap that closes.