pipeline_philly/verify/cross_publisher_attendance.py
Served verbatim from the project repository. Internal working document conventions apply: documents may reference file paths, branch names, and findings-ledger anchors from the repo.
"""(iii-b) Independent-publisher cross-check for attendance (the chronic-
absenteeism proxy).
SDP publishes "% with 90%+ attendance" per school (sdp_attendance_90_school).
PDE publishes "Regular Attendance" (PercentPersistentAttendance in the newer
workbooks) — students enrolled 90+ days who attended 90%+ of them — per
school via the Future Ready PA Index (futurereadypa_performance_*).
Both publishers describe the same 90% threshold, so at the school level they
should agree closely IF they describe the same school year and enrollment
basis.
YEAR ALIGNMENT. PDE's Future Ready glossary calls Regular Attendance "a
lagging indicator indicating data is from the year prior to the reporting
year". The loaders therefore store the state's attendance element under the
OBSERVATION year (workbook year − 1; scripts/loaders/philly/_lib.ts
STATE_METRIC_YEAR_OFFSET), and this script compares the two publishers for
the same served year. An earlier version of this check (before 2026-09-07)
compared the state's workbook year to the district's attendance year — i.e.
different school years — and reported ~11-point gaps in "2020-21/2021-22"
plus a supposed change in the state's rules at 2022-23. That was an artifact
of the misalignment, flagged by an external methodology audit. The
report-year-matched comparison is retained below as a diagnostic so the
artifact is reproducible.
This check decides whether the two can be spliced into one cross-sector
series (the SDP file has no charters; Future Ready covers every public
school). Both sides are read from the served DB (each is base-case verified
against its own source file by absenteeism_full_qa.py).
Output: docs/qa_reports/philly/__cross_publisher_attendance.{md,json}
Run: python pipeline_philly/verify/cross_publisher_attendance.py
"""
from __future__ import annotations
import json
import pathlib
import statistics
import sys
sys.path.insert(0, str(pathlib.Path(__file__).resolve().parents[2]))
from pipeline_philly.verify import common # noqa: E402
from pipeline_philly.verify.connectors import served_db # noqa: E402
SDP_KEY = "attendance_rate_above_90"
FRPI_KEY = "attendance_persistence_rate"
# Pre-committed verdict thresholds (written 2026-09-06, before the numbers in
# this script were seen; unchanged by the 2026-09-07 year-alignment fix).
# "Spliceable" = the two publishers can be treated as the same number:
# median |Δ| ≤ 0.5 and ≥ 80% of schools within ±1.0.
SPLICE_MEDIAN_ABS_MAX = 0.5
SPLICE_WITHIN_1_MIN_PCT = 80.0
OUTLIER_ABS = 10.0
def pct_within(diffs: list[float], tol: float) -> float:
return round(100.0 * sum(1 for d in diffs if abs(d) <= tol) / len(diffs), 1)
def pearson(xs: list[float], ys: list[float]) -> float | None:
mx, my = statistics.mean(xs), statistics.mean(ys)
sxy = sum((x - mx) * (y - my) for x, y in zip(xs, ys))
sxx = sum((x - mx) ** 2 for x in xs)
syy = sum((y - my) ** 2 for y in ys)
return round(sxy / (sxx * syy) ** 0.5, 4) if sxx and syy else None
def shift_year(y: str, k: int) -> str:
start = int(y[:4]) + k
return f"{start}-{str((start + 1) % 100).zfill(2)}"
def compare(sdp: dict, frpi: dict, sdp_year: str, frpi_year: str) -> tuple[dict | None, list[dict]]:
"""Pair every district-run school with a value in both, return summary + pairs."""
pairs: list[dict] = []
for (ulcs, year), s in sdp.items():
if year != sdp_year or s["value"] is None or s["suppressed"]:
continue
f = frpi.get((ulcs, frpi_year))
if not f or f["value"] is None or f["suppressed"]:
continue
pairs.append({
"school_ulcs": ulcs,
"school_type": s["school_type"],
"sdp_pct_90plus": round(s["value"], 2),
"frpi_regular_attendance": round(f["value"], 2),
"delta_frpi_minus_sdp": round(f["value"] - s["value"], 2),
"sdp_n": s["n"],
})
if len(pairs) < 10:
return None, pairs
ds = [p["delta_frpi_minus_sdp"] for p in pairs]
xs = [p["sdp_pct_90plus"] for p in pairs]
ys = [p["frpi_regular_attendance"] for p in pairs]
return {
"sdp_year": sdp_year,
"frpi_year_served": frpi_year,
"frpi_workbook": shift_year(frpi_year, 1),
"n_pairs": len(pairs),
"median_delta": round(statistics.median(ds), 3),
"mean_delta": round(statistics.mean(ds), 3),
"stdev_delta": round(statistics.pstdev(ds), 3),
"min_delta": round(min(ds), 3),
"max_delta": round(max(ds), 3),
"pct_within_1": pct_within(ds, 1.0),
"pct_within_2": pct_within(ds, 2.0),
"pct_within_5": pct_within(ds, 5.0),
"n_abs_gt_10": sum(1 for d in ds if abs(d) > OUTLIER_ABS),
"pearson_r": pearson(xs, ys),
"sdp_mean_pct_90plus": round(statistics.mean(xs), 2),
"frpi_mean_regular": round(statistics.mean(ys), 2),
}, pairs
def main() -> None:
common.ensure_qa_dir()
sdp = served_db.fetch(SDP_KEY, subgroup="ALL", population_cut="n/a")
frpi = served_db.fetch(FRPI_KEY, subgroup="ALL", population_cut="n/a")
sdp_years = sorted({y for (_, y) in sdp})
frpi_years = sorted({y for (_, y) in frpi})
common_years = sorted(set(sdp_years) & set(frpi_years))
if not common_years:
raise SystemExit("no overlapping years — are both metrics loaded?")
year = common_years[-1] # latest year both publishers describe
summary, pairs = compare(sdp, frpi, year, year)
if summary is None:
raise SystemExit(f"too few pairs for {year}")
summary["publishers"] = {
"sdp": "School District of Philadelphia, '% with 90%+ Attendance (Yearly)' (sdp_attendance_90_school); students enrolled 10+ days at the school",
"frpi": "PA Dept. of Education, Future Ready PA Index 'Regular Attendance' / PercentPersistentAttendance (futurereadypa_performance_<year+1>; lagging indicator, stored under the attendance year); students enrolled 90+ school days",
}
summary["delta_definition"] = "frpi − sdp, in percent of students (positive = the state shows MORE students at 90%+, i.e. FEWER below 90%)"
summary["year_alignment"] = (
f"Both sides describe {year} attendance. The state's value comes from its {shift_year(year, 1)} workbook "
"(PDE glossary: 'a lagging indicator indicating data is from the year prior to the reporting year')."
)
summary["splice_thresholds"] = {
"median_abs_delta_max": SPLICE_MEDIAN_ABS_MAX,
"pct_within_1_min": SPLICE_WITHIN_1_MIN_PCT,
}
spliceable = (
abs(summary["median_delta"]) <= SPLICE_MEDIAN_ABS_MAX
and summary["pct_within_1"] >= SPLICE_WITHIN_1_MIN_PCT
)
summary["verdict"] = "SPLICEABLE" if spliceable else "NOT_SPLICEABLE"
# Per-year sweep, correctly aligned (same served year on both sides).
per_year: dict[str, dict] = {}
all_pairs_by_year: dict[str, list[dict]] = {}
for y in common_years:
s, ps = compare(sdp, frpi, y, y)
if s:
per_year[y] = {k: s[k] for k in ("n_pairs", "median_delta", "mean_delta", "pct_within_1", "pct_within_5", "n_abs_gt_10", "pearson_r", "sdp_mean_pct_90plus", "frpi_mean_regular", "frpi_workbook")}
all_pairs_by_year[y] = ps
# Diagnostic: the REPORT-YEAR-matched comparison the earlier version of
# this check ran (district year Y vs the state's workbook labeled Y, i.e.
# the state's Y−1 attendance). Kept so the earlier, wrong finding is
# reproducible and visibly explained by year misalignment.
misaligned: dict[str, dict] = {}
for y in sdp_years:
frpi_served = shift_year(y, -1) # workbook Y holds attendance for Y−1
if frpi_served not in frpi_years:
continue
s, _ = compare(sdp, frpi, y, frpi_served)
if s:
misaligned[y] = {"district_year": y, "state_workbook": y, "state_attendance_year": frpi_served,
**{k: s[k] for k in ("n_pairs", "median_delta", "pct_within_1", "pct_within_5", "n_abs_gt_10", "pearson_r")}}
med_lo = min(v["median_delta"] for v in per_year.values()) if per_year else None
med_hi = max(v["median_delta"] for v in per_year.values()) if per_year else None
summary["verdict_note"] = (
"The two publishers' 90%-attendance measures can be treated as one number."
if spliceable
else (
f"Not interchangeable, but closely related: for {summary['n_pairs']} district-run schools in {year}, the state's "
f"figure runs a median {summary['median_delta']:+.2f} points {'above' if summary['median_delta'] > 0 else 'below'} the district's "
f"(fewer students below 90%), {summary['pct_within_1']}% of schools agree within ±1, {summary['pct_within_5']}% within ±5, "
f"{summary['n_abs_gt_10']} differ by more than {OUTLIER_ABS:g}, r = {summary['pearson_r']}. The per-school difference ranges from "
f"{summary['min_delta']:+.2f} to {summary['max_delta']:+.2f}, so the offset is a central tendency, not a one-directional gap at every school. "
"The pre-committed splice threshold (median within 0.5 and 80% of schools within ±1) is not met, so cross-sector "
"(charter vs district) comparisons use the state's measure on BOTH sides and never mix publishers in one series. "
"POSSIBLE EXPLANATION (untested): the publishers' eligibility rules differ — PDE counts only students enrolled 90+ "
"school days (PDE Future Ready glossary), the district counts students enrolled 10+ days at the school (SDP, Student "
"Attendance Patterns in Philadelphia 2017-18 to 2021-22, June 2023). If short-enrollment records have lower attendance, "
"that would push the state's rate up; but neither the excluded share nor the excluded records' attendance is published, "
"so how much of the offset it explains is unknown. Local attendance coding, attribution of transfers, revisions, and "
"rounding are other candidates. "
+ (f"PER-YEAR SWEEP (same attendance year on both sides): the median offset is {med_lo:+.2f} to {med_hi:+.2f} across the "
"overlapping years (" + ", ".join(f"{y} {v['median_delta']:+.2f}" for y, v in per_year.items()) + "), with r ≥ "
f"{min(v['pearson_r'] for v in per_year.values())} in every year; no year stands out as a basis change. "
if per_year else "")
+ "CORRECTION (2026-09-07): an earlier version of this check compared the state's workbook year to the "
"district's attendance year and reported ~11-point gaps in 2020-21/2021-22 and a 'basis change at 2022-23'. "
"That was a year misalignment (the state's attendance element lags one year); see report_year_matched_diagnostic. "
"SCOPE: this check covers district-run schools only, in the years both publishers describe (2020-21 onward); it does "
"not test charter schools, the two pre-pandemic years, or whether local attendance coding is identical across sectors."
)
)
summary["per_year"] = per_year
summary["report_year_matched_diagnostic"] = misaligned
# By school level (latest year)
by_level: dict[str, list[float]] = {}
for p in pairs:
by_level.setdefault(p["school_type"] or "?", []).append(p["delta_frpi_minus_sdp"])
per_type = {k: {"n": len(v), "median_delta": round(statistics.median(v), 2), "pct_within_2": pct_within(v, 2.0)} for k, v in by_level.items()}
pairs.sort(key=lambda p: abs(p["delta_frpi_minus_sdp"]), reverse=True)
outliers = [p for p in pairs if abs(p["delta_frpi_minus_sdp"]) > OUTLIER_ABS]
out: list[str] = []
out.append("# Cross-publisher (iii-b) — attendance / chronic-absenteeism proxy\n")
out.append("SDP '% with 90%+ attendance' vs Future Ready PA 'Regular Attendance' (PercentPersistentAttendance)\n")
out.append(f"(students attending 90%+ of days), same district-run schools, {year} attendance on both sides, all students.\n")
out.append("Both are base-case verified against their own source files (absenteeism_full_qa.py); this compares\n")
out.append("the two publishers to each other. Δ = Future Ready − SDP.\n")
out.append(f"\n**Year alignment:** {summary['year_alignment']}\n")
out.append(f"\n## Verdict: **{summary['verdict']}**\n\n{summary['verdict_note']}\n")
out.append(f"\n## Summary ({year})\n```json\n{json.dumps({k: v for k, v in summary.items() if k not in ('per_year', 'report_year_matched_diagnostic', 'verdict_note')}, indent=2)}\n```\n")
out.append("\n## Per year, correctly aligned (same attendance year on both sides)\n")
out.append("| attendance year | state workbook | schools | median Δ | within ±1 | within ±5 | |Δ| > 10 | r |\n|---|---|---|---|---|---|---|---|\n")
for y, v in per_year.items():
out.append(f"| {y} | {v['frpi_workbook']} | {v['n_pairs']} | {v['median_delta']:+.2f} | {v['pct_within_1']}% | {v['pct_within_5']}% | {v['n_abs_gt_10']} | {v['pearson_r']} |\n")
out.append("\n## Diagnostic: report-year-matched (the earlier, MISALIGNED comparison)\n")
out.append("District attendance year Y vs the state's workbook labeled Y, whose attendance element describes Y−1.\n")
out.append("This reproduces the ~11-point gaps the earlier version of this check attributed to a basis change.\n\n")
out.append("| district year | state workbook | state attendance year | schools | median Δ | within ±1 | within ±5 | |Δ| > 10 | r |\n|---|---|---|---|---|---|---|---|---|\n")
for y, v in misaligned.items():
out.append(f"| {v['district_year']} | {v['state_workbook']} | {v['state_attendance_year']} | {v['n_pairs']} | {v['median_delta']:+.2f} | {v['pct_within_1']}% | {v['pct_within_5']}% | {v['n_abs_gt_10']} | {v['pearson_r']} |\n")
out.append("\n## By school type (served directory)\n```json\n" f"{json.dumps(per_type, indent=2)}\n```\n")
out.append(f"\n## Schools differing by more than {OUTLIER_ABS:g} points ({len(outliers)})\n")
for p in outliers:
out.append(f"- ULCS {p['school_ulcs']}: SDP={p['sdp_pct_90plus']:.2f} FRPI={p['frpi_regular_attendance']:.2f} Δ={p['delta_frpi_minus_sdp']:+.2f}\n")
out.append("\n## Largest 20 absolute deltas\n")
for p in pairs[:20]:
out.append(f"- ULCS {p['school_ulcs']}: SDP={p['sdp_pct_90plus']:.2f} FRPI={p['frpi_regular_attendance']:.2f} Δ={p['delta_frpi_minus_sdp']:+.2f}\n")
(common.QA_REPORTS_DIR / "__cross_publisher_attendance.md").write_text("".join(out))
(common.QA_REPORTS_DIR / "__cross_publisher_attendance.json").write_text(
json.dumps({"summary": summary, "by_type": per_type, "outliers_gt_10": outliers, "pairs_top20": pairs[:20]}, indent=2),
)
print(f"year={year} pairs={len(pairs)} verdict={summary['verdict']} median={summary['median_delta']} "
f"within1={summary['pct_within_1']}% within5={summary['pct_within_5']}% gt10={summary['n_abs_gt_10']} r={summary['pearson_r']}")
if __name__ == "__main__":
main()