cassionData Analysis

Back to the lessonLesson 6 of 8Making the pieces line up

The rows that were never written

The same deck as the downloads, rendered as a page. Start the slideshow to present it full screen — arrow keys or a click advance one slide, Escape leaves.

Slides · PDFSlides · PowerPoint

  1. Slide 1 / 27

    What this lesson covers

    • A missing row has no value to be missing
    • Build the grid
    • Where all 1,755 went
    • Absent is not absent
    • A complete grid does not prove everyone reported
    • Period keys that sort
    • Aligning a daily register to a monthly aggregate
    • What comes next
    Speaker notes
    A missing row has no value to be missing. Build the grid the data should have filled, find where 1,755 attendance rows went, and learn why a complete grid still does not prove everyone reported.

  2. Slide 2 / 27

    A missing row has no value to be missing

    • Every check in the cleaning course looked at values: blanks, sentinels, implausible numbers.
    Speaker notes
    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.

  3. Slide 3 / 27

    Build the grid — In Python

    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):,}")

  4. Slide 4 / 27

    Build the grid — In R

    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)))

  5. Slide 5 / 27

    Build the grid — In R

    complete <- attendance |>
  tidyr::complete(student_id, attendance_date)
    Speaker notes
    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() 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.

  6. Slide 6 / 27

    Where all 1,755 went — In Python

    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)

  7. Slide 7 / 27

    Where all 1,755 went — In R

    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)

  8. Slide 8 / 27

    Where all 1,755 went

    school_idmissing rowsdays
    SCH0791515
    SCH1884015
    Speaker notes
    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.

  9. Slide 9 / 27

    Absent is not absent — In Python

    naive = complete["present"].fillna(False)
print("attendance rate treating gaps as absences:", naive.mean())
    Speaker notes
    Now the decision, and it is the whole reason the lesson exists.

  10. Slide 10 / 27

    Absent is not absent — In R

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

  11. Slide 11 / 27

    Absent is not absent — In Python

    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")
    Speaker notes
    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.

  12. Slide 12 / 27

    Absent is not absent — In R

    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")

  13. Slide 13 / 27

    Absent is not absent

    • Build the grid per school, not per district — The universe of periods is a property of the reporting unit, and assuming…
    Speaker notes
    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.

  14. Slide 14 / 27

    Absent is not absent

    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.

  15. Slide 15 / 27

    A complete grid does not prove everyone reported — In Python

    expected = (
    vax["facility_id"].nunique()
    * vax["period"].nunique()
    * vax["antigen"].nunique()
)
print(expected, "expected rows;", len(vax), "actual")
    Speaker notes
    The vaccination extract is the counter-example, and it is important because it looks like the good case.

  16. Slide 16 / 27

    A complete grid does not prove everyone reported — In R

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

  17. Slide 17 / 27

    A complete grid does not prove everyone reported — In Python

    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()
    Speaker notes
    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.

  18. Slide 18 / 27

    A complete grid does not prove everyone reported — In R

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

  19. Slide 19 / 27

    Period keys that sort — In Python

    vax["period"] = pd.to_datetime(vax["period"])
vax["month"] = vax["period"].dt.strftime("%Y-%m")     # 2024-08, sorts correctly
    Speaker notes
    A small mechanical point that causes disproportionate trouble.

  20. Slide 20 / 27

    Period keys that sort — In R

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

  21. Slide 21 / 27

    Period keys that sort

    • The month must carry its year — A twelve-month grid keyed on month alone silently merges August 2023 and August 2024…
    Speaker notes
    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.

  22. Slide 22 / 27

    Aligning a daily register to a monthly aggregate — In Python

    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()
)
    Speaker notes
    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.

  23. Slide 23 / 27

    Aligning a daily register to a monthly aggregate — In R

    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))

  24. Slide 24 / 27

    Aligning a daily register to a monthly aggregate — In Python

    last = monthly["month"].max()
print(f"excluding partial period {last}")
monthly = monthly[monthly["month"] < last]
    Speaker notes
    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.

  25. Slide 25 / 27

    Aligning a daily register to a monthly aggregate — In R

    monthly <- monthly |> filter(month < max(month))
    Speaker notes
    Drop it or mark it, but never plot it silently next to complete months.

  26. Slide 26 / 27

    What comes next

    • You now have a register aggregated to the same grain as the monthly return.
    Speaker notes
    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.

  27. Slide 27 / 27

    Where this goes next

    Read the full lesson, with runnable code Back to the lesson