--- title: "Dropout risk from attendance patterns" subtitle: "School attendance, 2024" author: "Cassion · data-analysis.cassion.dev" format: html: toc: true code-fold: false jupyter: python3 --- ## What this produces A per-student dropout risk ranking built from consecutive-absence patterns, in time to act before the term ends. Attendance decays over roughly three weeks before a student stops coming, and that decay is what makes early warning possible at all. Three things have to be right first, and each of them changes the ranking: the boolean coding, the join, and the difference between a closure and an absence. Every dataset on this platform is synthetic. No real student is represented. ## Setup ```{python} import pandas as pd import numpy as np BASE = "https://data-analysis.cassion.dev/datasets/files/" attendance = pd.read_csv(BASE + "school-attendance-2024.v1.csv", dtype={"student_id": "string", "present": "string"}) roster = pd.read_csv(BASE + "school-roster-2024.v1.csv", dtype={"student_id": "string", "school_id": "string"}) attendance["attendance_date"] = pd.to_datetime(attendance["attendance_date"]) print(f"attendance rows: {len(attendance):,}") print(f"roster rows : {len(roster):,}") ``` ## The boolean that is not a boolean One school recorded attendance with `Y` and `N` instead of `true` and `false`. A boolean cast turns those into missing values silently — and it is not a random 30% of the file, it is one school. ```{python} print(attendance["present"].value_counts(dropna=False)) ``` ```{python} naive = attendance["present"] == "true" PRESENT = { "true": True, "TRUE": True, "Y": True, "y": True, "yes": True, "false": False, "FALSE": False, "N": False, "n": False, "no": False, } attendance["present_clean"] = ( attendance["present"].str.strip().map(PRESENT) ) print(f"unparsed after mapping: {int(attendance['present_clean'].isna().sum())} " "(genuinely blank — never marked either way)") print(f"naive cast attendance rate : {naive.mean():.3f}") print(f"correct attendance rate : {attendance['present_clean'].mean():.3f}") ``` The overall difference looks small. Look at the affected school on its own: ```{python} roster_unique = roster.drop_duplicates("student_id") joined_check = attendance.merge( roster_unique[["student_id", "school_id"]], on="student_id", how="left" ) by_school = joined_check.assign(naive=joined_check["present"] == "true").groupby("school_id").agg( naive_rate=("naive", "mean"), correct_rate=("present_clean", "mean"), ) by_school["difference"] = by_school["correct_rate"] - by_school["naive_rate"] by_school.sort_values("difference", ascending=False).head(3).round(3) ``` One school appears to have 60% attendance instead of 86%. On a dropout ranking that school supplies most of the top of the list, and every intervention goes to the wrong students. ## The join that fans out Two students appear on the roster twice, after a transfer that was never de-registered. A straight join multiplies their attendance rows and double-counts them. ```{python} duplicated = roster[roster.duplicated("student_id", keep=False)] duplicated.sort_values("student_id") ``` ```{python} before = len(attendance) naive_join = attendance.merge(roster, on="student_id", how="left") print(f"rows before join: {before:,}") print(f"rows after naive join: {len(naive_join):,} (+{len(naive_join) - before})") ``` ```{python} # Resolve the duplicate deliberately rather than dropping arbitrarily: keep the # row with a grade recorded, which is the post-transfer registration. roster_resolved = ( roster.sort_values("grade", na_position="last") .drop_duplicates("student_id", keep="first") ) daily = attendance.merge(roster_resolved, on="student_id", how="left", validate="many_to_one") assert len(daily) == before, "join changed the row count" print(f"rows after resolved join: {len(daily):,}") ``` `validate="many_to_one"` is what turns this from a silent 3,000-row inflation into an error at the point it happens. ## A missing row is a closure, not an absence **Attendance rows exist only for days a school was open.** A date with no row is a closure, and nothing in the file marks which is which. ```{python} school_days = daily.groupby("school_id")["attendance_date"].nunique().sort_values() all_days = daily["attendance_date"].nunique() print(f"distinct school days in the file: {all_days}") school_days.head(4) ``` ```{python} calendar = sorted(daily["attendance_date"].unique()) closures = {} for school in school_days[school_days < all_days].index: open_days = set(daily.loc[daily["school_id"] == school, "attendance_date"]) closures[school] = sorted(set(calendar) - open_days) missing = closures[school] print(f"{school}: {len(missing)} days closed, " f"{pd.Timestamp(missing[0]).date()} to {pd.Timestamp(missing[-1]).date()}") closed_schools = list(closures) ``` Fifteen consecutive school days in March. That is a strike. ## What the closure does if you fill it The damage happens the moment you build a student-by-date matrix — the natural shape for a run-length feature — because reindexing to the full calendar manufactures rows that were never recorded, and the obvious fill value is "absent". ```{python} matrix = ( daily.set_index(["student_id", "attendance_date"])["present_clean"] .unstack() .reindex(columns=calendar) ) filled_rate = matrix.fillna(False).mean(axis=1) # closure counted as absence recorded_rate = daily.groupby("student_id")["present_clean"].mean() comparison = pd.DataFrame({ "closure filled as absent": filled_rate, "recorded days only": recorded_rate, }).join(roster_resolved.set_index("student_id")[["school_id"]]) ( comparison.groupby("school_id")[ ["closure filled as absent", "recorded days only"] ] .mean() .assign(gap=lambda d: d["recorded days only"] - d["closure filled as absent"]) .sort_values("gap", ascending=False) .head(4) .round(3) ) ``` Twenty-two points, at two schools, out of nowhere. Now watch what that does to a watchlist: ```{python} THRESHOLD = 0.70 in_closed = comparison["school_id"].isin(closed_schools) for label, rate in [ ("closure filled as absent", filled_rate), ("recorded days only", recorded_rate), ]: flagged = rate < THRESHOLD share = comparison.loc[flagged, "school_id"].isin(closed_schools).mean() print(f"{label:26} {int(flagged.sum()):>4} students flagged, " f"{share:.1%} of them from the two closed schools") print(f"\nthose two schools are {in_closed.mean():.1%} of the roster") ``` A hundred and thirty students instead of eighty-five, and nearly half the list drawn from schools holding a tenth of the roster. The fix is not a clever adjustment — it is **not reindexing in the first place**. Compute every feature on the days the student's school actually recorded. ## Build the risk features on recorded days only The signal is not total absence — it is a *recent run* of it. A student who missed a fortnight in February and came back is not the same as one who has missed the last fortnight. ```{python} # The 271 rows never marked either way are dropped here rather than earlier: they # are missing marks on days the school was open, which is a different thing from # a closure and should not inflate an absence run. daily = ( daily.dropna(subset=["present_clean"]) .sort_values(["student_id", "attendance_date"]) ) term_end = daily["attendance_date"].max() def student_features(group): # astype(bool) because the mapped column is a nullable boolean, and numpy # will not index with an object array. present = group["present_clean"].astype(bool).to_numpy() dates = group["attendance_date"].to_numpy() # Trailing run of absences, in school days the student's own school opened. run = 0 for value in present[::-1]: if value: break run += 1 last_present = dates[present].max() if present.any() else pd.NaT return pd.Series({ "days_recorded": len(group), "attendance_rate": present.mean(), "trailing_absences": run, "last_present": last_present, # School days missed since last attending. Closure days are absent from # this count because they were never recorded, which is the whole point. "school_days_missed": int((dates > last_present).sum()) if present.any() else len(group), }) features = daily.groupby("student_id").apply(student_features, include_groups=False) features = features.join( roster_resolved.set_index("student_id")[["school_id", "grade", "feeding_programme"]] ) features.head() ``` ```{python} DISENGAGED_DAYS = 15 # school days, not calendar days features["at_risk"] = features["school_days_missed"] > DISENGAGED_DAYS print(f"flagged: {int(features['at_risk'].sum())} of {len(features)} " f"({features['at_risk'].mean():.1%})") flagged_share = features.loc[features["at_risk"], "school_id"].isin(closed_schools).mean() print(f"of those, {flagged_share:.1%} are at the two closed schools " f"(which hold {features['school_id'].isin(closed_schools).mean():.1%} of the roster)") ``` The closure schools are still somewhat over-represented, and that is worth saying plainly rather than adjusting away: a three-week closure is a plausible trigger for genuine disengagement. Counting closure days as absences invents dropouts; counting them as nothing leaves a real signal that a head teacher should be told about. ## The list a head teacher can use ```{python} watchlist = ( features[features["at_risk"]] .sort_values(["school_days_missed", "attendance_rate"], ascending=[False, True]) .loc[:, ["school_id", "grade", "attendance_rate", "trailing_absences", "school_days_missed"]] .round(3) ) watchlist.head(15) ``` Sorted by how long the student has been gone, then by how they were attending before. That ordering matters: a student who was at 95% and stopped three weeks ago is a different case from one who was at 40% all term. ## What to report The list, the threshold used, the closure adjustment and which schools it applied to. And the caveat that matters most: this ranks *risk*, not dropout. A student on this list may have transferred, be ill, or be temporarily helping at home — the output is a conversation to have, not a status to record.