The rows that were never written

A missing row has no value to be missing

Every check in the cleaning course looked at values: blanks, sentinels, implausible numbers. All of them share an assumption — that the row is there.

The failure this lesson deals with is different. A school that was closed has no attendance rows. A facility that did not report has no row in the extract. A month with no distribution has nothing at all. There is no blank cell to count, no NA to detect, and every summary you compute is over the periods that happened to be recorded.

The fix is always the same shape: construct the grid of periods that should exist, join the data onto it, and let the holes appear as rows.

Build the grid

students = roster["student_id"].drop_duplicates()
school_days = attendance["attendance_date"].drop_duplicates()

grid = pd.MultiIndex.from_product(
    [students, school_days], names=["student_id", "attendance_date"]
).to_frame(index=False)

complete = grid.merge(attendance, on=["student_id", "attendance_date"], how="left")

print(f"grid {len(grid):,}, actual {len(attendance):,}, "
      f"missing {len(grid) - len(attendance):,}")

grid <- tidyr::expand_grid(
  student_id = unique(roster$student_id),
  attendance_date = unique(attendance$attendance_date)
)

complete <- grid |> left_join(attendance, by = c("student_id", "attendance_date"))

cat(sprintf("grid %d, actual %d, missing %d\n",
            nrow(grid), nrow(attendance), nrow(grid) - nrow(attendance)))

1,200 students by 60 school days is a grid of 72,000. The file holds 70,245. 1,755 rows were never written.

dplyr has a shorter route when the frame is already the data:

complete <- attendance |>
  tidyr::complete(student_id, attendance_date)

complete() fills the cross-product of the values it finds, which is right when every combination should exist and wrong when a student joined mid-term — it will not invent days for a student the file never mentions before March. Build the grid explicitly when the universe is defined outside the data, which is the usual case.

Where all 1,755 went

missing = complete[complete["present"].isna()]
by_school = (
    missing.merge(roster[["student_id", "school_id"]], on="student_id")
    .groupby("school_id")
    .agg(missing_rows=("present", "size"),
         days=("attendance_date", "nunique"))
)
print(by_school)

complete |>
  filter(is.na(present)) |>
  left_join(select(roster, student_id, school_id), by = "student_id") |>
  summarise(missing_rows = n(), days = n_distinct(attendance_date), .by = school_id)

school_id	missing rows	days
SCH07	915	15
SCH18	840	15

Two schools, fifteen days each, and 61 × 15 + 56 × 15 = 1,755. The grid accounts for every single missing row, and it does so in a form that names the cause: two schools were shut for the same fifteen days in March.

That is the payoff. Before the grid, the missing rows were invisible. After it, they are two schools and a date range — a strike, a flood, an exam period, something a colleague can confirm in one phone call.

Absent is not absent

Now the decision, and it is the whole reason the lesson exists.

naive = complete["present"].fillna(False)
print("attendance rate treating gaps as absences:", naive.mean())

complete |> summarise(rate = mean(coalesce(present, FALSE)))

Filling the gaps with “absent” makes SCH07 and SCH18 look like a dropout emergency, because a quarter of their term is now recorded as every child missing every day. The correct handling is the opposite: those days were not school days for those schools, and they belong in neither numerator nor denominator.

open_days = attendance.merge(roster[["student_id", "school_id"]], on="student_id")
school_calendar = open_days[["school_id", "attendance_date"]].drop_duplicates()

grid = (
    roster[["student_id", "school_id"]]
    .merge(school_calendar, on="school_id")
)
print(len(grid), "student-days the schools were actually open")

school_calendar <- attendance |>
  left_join(select(roster, student_id, school_id), by = "student_id") |>
  distinct(school_id, attendance_date)

grid <- roster |>
  select(student_id, school_id) |>
  left_join(school_calendar, by = "school_id", relationship = "many-to-many")

Build the grid per school, not per district. The universe of periods is a property of the reporting unit, and assuming a single shared calendar is how a closure becomes an absence.

Deciding whether a gap is a zero, a missing value or a period that should not exist is not a technical question. It is the analysis, and it belongs in the log with its reason.

A complete grid does not prove everyone reported

The vaccination extract is the counter-example, and it is important because it looks like the good case.

expected = (
    vax["facility_id"].nunique()
    * vax["period"].nunique()
    * vax["antigen"].nunique()
)
print(expected, "expected rows;", len(vax), "actual")

c(expected = n_distinct(vax$facility_id) * n_distinct(vax$period) * n_distinct(vax$antigen),
  actual = nrow(vax))

38 × 12 × 6 = 2,736, and the file has 2,736 rows. The grid is perfect. And 642 of those rows carry report_submitted of false and zero doses.

So the grid check passes and the data is still full of holes — they are just holes with a row around them. Two separate checks, and you need both: is every expected row present, and does every present row contain a report.

coverage = (
    vax[vax["report_submitted"]]
    .groupby(["period", "antigen"])
    .agg(doses=("doses_administered", "sum"), target=("target_population", "sum"))
)
reporting = vax.groupby(["period", "antigen"])["report_submitted"].mean()

vax |>
  summarise(
    reporting_rate = mean(report_submitted),
    doses  = sum(doses_administered[report_submitted]),
    target = sum(target_population[report_submitted]),
    .by = c(period, antigen)
  )

Period keys that sort

A small mechanical point that causes disproportionate trouble.

vax["period"] = pd.to_datetime(vax["period"])
vax["month"] = vax["period"].dt.strftime("%Y-%m")     # 2024-08, sorts correctly

vax <- vax |> mutate(month = format(period, "%Y-%m"))

Use ISO period keys — 2024-08, 2024-Q3, 2024-W32 — everywhere a period is a key. Aug, August and 08/2024 all sort wrongly, and 08/2024 is ambiguous between two conventions that are both in daily use in this sector.

The month must carry its year. A twelve-month grid keyed on month alone silently merges August 2023 and August 2024 the first time two years of data are in the same folder.

Aligning a daily register to a monthly aggregate

The last shape in this lesson, and the one the next lesson builds on. To compare a line-level register with a monthly return, aggregate the register to the aggregate’s grain — never the other way.

monthly = (
    attendance.assign(month=attendance["attendance_date"].dt.strftime("%Y-%m"))
    .merge(roster[["student_id", "school_id"]], on="student_id")
    .groupby(["school_id", "month"])
    .agg(marks=("present", "size"),
         present=("present", "sum"))
    .reset_index()
)

monthly <- attendance |>
  mutate(month = format(attendance_date, "%Y-%m")) |>
  left_join(select(roster, student_id, school_id), by = "student_id") |>
  summarise(marks = n(), present = sum(present %in% TRUE), .by = c(school_id, month))

Two things to notice, because both are choices. The month a record belongs to comes from the event date, not from when the form was submitted — a late submission still belongs to the month it describes. And the last month in the file is very often partial, so a trend chart that includes it always appears to be falling.

last = monthly["month"].max()
print(f"excluding partial period {last}")
monthly = monthly[monthly["month"] < last]

monthly <- monthly |> filter(month < max(month))

Drop it or mark it, but never plot it silently next to complete months.

What comes next

You now have a register aggregated to the same grain as the monthly return. The next lesson puts the two side by side, works out why they disagree, and turns the difference into a table you can publish rather than an argument you have to win.