A strike that looks like an emergency

The rows that are not there

Attendance rows exist only for days a school was open. A date with no row is a closure, not an absence, and nothing in the file marks which is which.

import pandas as pd

attendance = pd.read_csv("school-attendance-2024.v1.csv")
roster = pd.read_csv("school-roster-2024.v1.csv")

joined = attendance.merge(roster[["student_id", "school_id"]], on="student_id")
calendar = sorted(attendance["attendance_date"].unique())

days_per_school = joined.groupby("school_id")["attendance_date"].nunique()
print(f"school days in the calendar: {len(calendar)}")
print(days_per_school.sort_values().head())

library(dplyr)

attendance |>
  left_join(roster, by = "student_id") |>
  summarise(days = n_distinct(attendance_date), .by = school_id) |>
  arrange(days)

Twenty-two schools have all sixty days. SCH07 and SCH18 have forty-five.

missing = {
    school: sorted(set(calendar) - set(group["attendance_date"]))
    for school, group in joined.groupby("school_id")
}
for school, dates in missing.items():
    if dates:
        print(f"{school}: {len(dates)} days, {dates[0]} to {dates[-1]}")

# Which dates, not just how many. The pattern is the diagnosis.

Both are missing exactly 11 to 29 March, and both are missing the same fifteen days. That is not two schools with an attendance problem; that is one event.

Consecutive missing days at multiple schools is a closure until proved otherwise. Scattered missing days at one school is a recording problem. The shape tells you which, and it takes one line to look.

What happens if you do not look

The natural way to build an attendance table is to construct the full grid of students by school days and fill in the marks. It is also the way to turn a strike into a crisis.

def attendance_rate(school, missing_counts_as_absent):
    students = roster.loc[roster["school_id"] == school, "student_id"]
    marks = attendance[attendance["student_id"].isin(students)]
    present = marks["present"].isin(["true", "Y"]).sum()
    if missing_counts_as_absent:
        denominator = len(students) * len(calendar)
    else:
        denominator = marks["present"].isin(["true", "Y", "false", "N"]).sum()
    return present / denominator

for school in ("SCH07", "SCH18", "SCH01"):
    observed = attendance_rate(school, False)
    filled = attendance_rate(school, True)
    print(f"{school}: observed {observed:.1%}, grid-filled {filled:.1%}")

# Two denominators: marks made, and student-days in the calendar.

School	On marks made	On a full grid
SCH07	88.2%	66.1%
SCH18	85.2%	63.9%
SCH01	91.3%	91.3%

Twenty-two points, invented. On the grid-filled figures SCH07 and SCH18 are the two worst schools in the district by a wide margin, and a programme reading that table would send a dropout response to two schools whose children attended normally on every day they were asked to.

The unaffected school is unchanged, which is what makes the error so hard to catch: the table looks fine, most of it is fine, and the two wrong rows are the two you act on.

The rule

Build the denominator from days the school was open, not from the calendar.

open_days = joined.groupby("school_id")["attendance_date"].nunique()
enrolled = roster.groupby("school_id")["student_id"].nunique()
expected = (open_days * enrolled).rename("student_days_expected")

actual = joined.groupby("school_id").size().rename("marks_made")
coverage = (actual / expected).rename("mark_coverage")
print(pd.concat([expected, actual, coverage.round(3)], axis=1).head())

# Expected student-days uses each school's own open days.

That gives a second, useful number: mark coverage, the share of expected student-days that carry a mark at all. A school at 100% attendance and 60% mark coverage is not a school with good attendance.

When a closure is the finding

Fifteen school days is a quarter of the term. The closure is more consequential than any attendance figure in this file, and an analysis that correctly excludes those days and then says nothing about them has removed the largest thing that happened.

Attendance, February to April 2024

  Average daily attendance, all schools    88.4%   on marks made
  SCH07                                    88.2%
  SCH18                                    85.2%

  SCH07 and SCH18 were closed for the fifteen school days from 11 to 29
  March, a quarter of the term. Their attendance figures are computed on the
  45 days they were open and are comparable with other schools; their
  instructional time is not.

  Counting the closure days as absences would report these two schools at
  66.1% and 63.9% and rank them worst in the district.

Report the closure as lost instructional days, separately from attendance. The two answer different questions: attendance is about whether children came, and instructional days are about whether school happened.

The general form

This is the third time this platform has met the same defect in a different file.

Course	The absent thing	What it looked like
Routine data and DHIS2	A facility that did not report	A district with falling coverage
WASH analysis	A monitoring round nobody drove	A district with improving functionality
Education	A day the school was closed	Two schools with a dropout emergency

In all three, the absence is not a value and nothing in the file announces it. The habit that catches all three is the same: before computing any rate, construct what the denominator should be from something other than the rows you have, and compare.

What comes next

Attendance describes one term. Whether a child is still in school next year is a different question, and the next unit follows the 2023 cohort through promotion, repetition and dropout to find out.