NYSED ↔ NYC DOE Chronic-Absenteeism Reconciliation

Date: 2026-06-09. First cross-publisher reconciliation of NYSED Report Card chronic absenteeism (CA) against the NYC DOE end-of-year per-grade CA data in the local Postgres replica (edu_data_local.school_year_metrics). School years compared: 2018-19, 2023-24, 2024-25. Expanded 2026-06-10 to 2020-21, 2021-22, 2022-23 — see §9.

1. Method

NYSED side

• Source zips (downloaded 2026-06-09): SRC2019, SRC2022, SRC2024, SRC2025 from data.nysed.gov. Extracted the .mdb from each, exported tables ACC EM Chronic Absenteeism (grades 1–8) and ACC HS Chronic Absenteeism (grades 9–12) with mdb-export, then deleted the mdbs. CSVs in nyc-edu-data/data/reference/sources/nysed_src/2026-06-09/extracted/ (gitignored).
• YEAR encoding confirmed from SRC2025 ReadMe: "YEAR … 2024 for 2023-24, 2025 for 2024-25". So SRC2019 YEAR=2019 → 2018-19 SY; SRC2024 YEAR=2024 → 2023-24; SRC2025 YEAR=2025 → 2024-25.
• Rows used: SUBGROUP_NAME='All Students', NYC (ENTITY_CD county prefix 31–35), school-level (digits 9–12 ≠ '0000'), district schools only (digits 7–8 ≠ '86'). ABSENT_RATE parsed as a number; s (suppressed) rows skipped.
• Revision check: SRC2024 and SRC2025 YEAR=2024 NYC district-school All-Students rows are byte-identical (EM 1166/1166, HS 505/505) — no inter-release revisions; own-year file precedence is moot for these rows.

Our side (DOE)

• school_year_metrics, metric_key = chronic_absenteeism_rate_grade_1 … _grade_12, subgroup='ALL', not suppressed and value is not null and denominator > 0. Single provenance: data_load 644077af "NYCDOE — End-of-Year Attendance + Chronic Absenteeism (2018-19 to 2024-25)" (one xlsx covering all years uniformly).
• Band aggregate per school-year: N = Σ denominator, absent_est = Σ(value/100 × denominator), rate = 100 × absent_est / N, grades 1–8 (EM) and 9–12 (HS).
• Joined on (derived DBN, school year). District DBNs only (^(0[1-9]|[12]\d|3[0-2])[MXKQR]\d{3}$).

File-format findings (step 2)

• All columns export as quoted text. ABSENT_RATE is one decimal place (48.6) or a bare integer (39); no % signs, no padding. ENROLLMENT/ABSENT_COUNT are bare integers. Suppression marker is the literal s in the value columns. The only whitespace issue is trailing spaces in some ENTITY_NAMEs (84–1,910 rows/file); no whitespace in numeric fields.
• Layout variants as documented in nysed-scout/FORMAT_NOTES.md: SRC2019 has the MIP-era goal columns and no INSTITUTION_ID; SRC2025 drops LEVEL/OVERRIDE.
• Row counts: SRC2019 EM 81,731 / HS 37,848; SRC2022 EM 87,057 / HS 40,900; SRC2024 EM 92,270 / HS 43,440; SRC2025 EM 92,050 / HS 43,410. Each file carries exactly two YEAR values (current + prior).

2. BEDS → DBN derivation

Rule applied: borough letter from county code (31→M, 32→X, 33→K, 34→Q, 35→R); 2-digit district = digits 3–4; DBN number = last 3 digits of ENTITY_CD. Verified structure: digits 5–8 are 0001 for every NYC district-school row in all six panels.

• Digit 9 is not always '0'. 1,658 rows (465 distinct schools across years/tables) have digit 9 = '1' — these are buildings numbered 1xxx (e.g. 331300011430 → 13K430 Brooklyn Tech, 310100011450 → 01M450 East Side Community). No other digit-9 values occur. The last-3-digits rule still produces the correct DBN.
• Zero DBN collisions in any (year, table) panel — no 0xxx/1xxx building-number ambiguity materialized.
• D75: not derivable and moot — NYSED CA tables contain no D75 schools at all (0 rows with ENTITY_CD prefix 3075 in all 8 exports; the FORMAT_NOTES D75 examples exist as institutions but have no CA rows). D79 also absent (0 rows prefix 3079), confirming the scout finding. Headline stats are district schools only, as planned.
• Charters (digits 7–8 = '86') excluded from the join (no deterministic DBN; crosswalk needed).

3. NYC coverage per panel

Success Academy non-reporting (DATA_REP_FLAG = 'N')

SA stopped reporting attendance to NYSED network-wide starting 2023-24:

SA is the bulk of the non-reporting universe (charters only; doesn't affect the district-school reconciliation below).

4. Reconciliation results (district schools, All Students)

diff = our rate − NYSED rate, percentage points. Enrollment ratio = our N / NYSED ENROLLMENT.

Enrollment-weighted aggregates over the joined set:

Exact agreement: at HS, 71% of joined schools have byte-identical denominators in 2023-24/2024-25 (most HS serve only 9–12, so the universes coincide); in 2024-25 HS, 52% of schools match the rate within 0.05pp. At EM, denominators rarely match exactly (ours ~1.7–3.3% larger at the median), consistent with universe-rule differences (NYSED EM counts ungraded age-equivalent students; window/inclusion rules differ).

Join coverage notes

• NYSED-only HS (~52–61/yr) are overwhelmingly transfer/alternative high schools (e.g. 01M458 Forsyth Satellite, 01M515 Lower East Side Prep, 01M650 Cascades) plus a few 6-12 schools (03M610/04M610 Young Women's Leadership). Checked in DB: these DBNs exist in schools but have zero metrics rows — the DOE EOY attendance file simply omits them. This is a coverage gap on our side, not a join failure.
• Ours-only (3–13/panel) are schools whose NYSED row is suppressed (s) or absent that year (e.g. 08X583, 03M291 West End Secondary 2018-19 HS — school too new/small for NYSED accountability reporting).
• NYSED-only EM examples 2023-24/2024-25: 03M610, 05M371 SEED Harlem (state-authorized boarding school, not in DOE file), 07X642 South Bronx Literacy Academy.

Worst |diff| cases (top of each panel; full top-10 in recon_results.json)

• 2018-19 EM: 23K697 Teachers Preparatory HS, NYSED 51.6 vs ours 82.35 (N 62 vs 17) — see hand case 3: partial-grade artifact, not a data conflict.
• 2023-24 EM: 13K266 (38.6 vs 44.8, N 70 vs 87); 05M194 (48.7 vs 54.7, N 117 vs 137) — small schools where universe differences swing the rate.
• 2024-25 EM: 02M347 ASL & English Lower (49.1 vs 38.4, N 55 vs 125); 32K116 (36.2 vs 28.2); nearly all top-10 share the same sign (ours lower).
• 2024-25 HS: 25Q425 John Bowne (42.8 vs 34.1, N 2894 vs 2927, −8.7pp) — largest single-school conflict found; denominators nearly identical, numerator diverges.

5. Hand-verified cases (end-to-end arithmetic)

Case 1 — 01M015 PS 15 Roberto Clemente, 2023-24 EM. NYSED (SRC2024, YEAR=2024): ENROLLMENT 142, ABSENT_COUNT 69, ABSENT_RATE 48.6. Ours (grades 1–5): 35×45.714287% = 16; 29×51.724136% = 15; 30×53.333332% = 16; 33×42.424244% = 14; 23×47.826088% = 11 → 72 absent / 150 = 48.00% vs NYSED 48.6% (diff −0.60pp; N 150 vs 142, ratio 1.056). Reconstructed counts are integers — the DOE rates are exact count ratios.

Case 2 — 13K430 Brooklyn Technical HS, 2024-25 HS. NYSED (SRC2025, YEAR=2025): 5,840 / 979 / 16.8. Ours: g9 1,399×5.861330% = 82; g10 1,410×8.368794% = 118; g11 1,468×14.032698% = 206; g12 1,563×27.063339% = 423 → 829 / 5,840 = 14.20% vs NYSED 16.8%. Denominator identical (5,840 = 5,840); numerator differs by 150 students. DOE's own published school-total CA for 13K430 2024-25 is 14.195% — matches our reconstruction exactly. Same school in 2018-19: 10.2 vs 10.31 (+0.11pp); 2023-24: 14.0 vs 14.21 (+0.21pp). The divergence is new in 2024-25 and numerator-only.

Case 3 — 23K697 Teachers Preparatory HS (6-12 school), 2018-19. NYSED EM: 62 / 32 / 51.6. Ours: only grade 8 is non-suppressed (17×82.352943% = 14) → 82.35% on N=17. Grades 6–7 are missing from the DOE per-grade file, so our EM aggregate covers 17 of ~62 students — the worst-case |diff| of +30.75pp is a partial-grade-coverage artifact, not a publisher conflict. The HS side of the same school reconciles fine: NYSED 217/122/56.2 vs ours 121/212 = 57.08% (+0.88pp).

6. The 2024-25 EM divergence (verified facts vs open cause)

Verified:

1. Five of six panels agree tightly (median |diff| ≤ 0.4pp, weighted aggregate within ±0.5pp, correlation ≥ 0.993). 2024-25 EM does not: median −1.84pp, weighted −1.71pp, only 22% of schools within ±1pp.
2. The divergence is broad-based, not compositional: median diff −1.92pp for elementary-only (gr 1–5) schools, −1.82 for K-8, −1.49 for middle-only (6–8).
3. It is numerator-side, not denominator-side: enrollment ratios are unchanged from 2023-24 (median 1.027 vs 1.033); Brooklyn Tech shows identical N with a 150-student numerator gap; 59 of the 62 EM schools with byte-identical N still differ by >0.5pp (vs 2 of 22 in 2023-24).
4. The two publishers disagree on the direction of the year-over-year change for the same joined schools: NYSED EM CA 30.02% → 30.75% (up +0.7pp); DOE 30.49% → 29.04% (down −1.4pp). DOE's citywide per-grade rates fall in every grade PK–12 in 2024-25 (−0.5 to −4.1pp); a milder echo exists at HS (ours −1.06pp YoY vs NYSED −0.38pp).
5. Not a NYSED revision or vintage issue: SRC2024 vs SRC2025 overlap rows are identical, and our DOE source is a single EOY file covering all years with the same layout.
6. Not a reconstruction artifact: for pure 6–8 schools, our grade-sum reproduces DOE's own published school-total rate with median diff 0.000pp and identical N in both years (199/335 and 197/335 exact; remainder explained by per-grade suppression/ungraded students).

Cause not established from data on hand. Candidate explanations to verify externally (do not assert without sourcing): a DOE 2024-25 definitional change in the EOY file (the OSC audit reportedly claims DOE excludes out-of-school-suspension and medical absences); a NYSED-side change coinciding with CA's removal as an accountability indicator in 2025 (replaced by the Attendance Index; minimum-N dropped 30→20). The data show whatever changed in 2024-25 removed ~5,700 students from the EM numerator (and ~150 at Brooklyn Tech alone) relative to NYSED's count, while denominators track as before.

7. Caveats

• Our side reconstructs counts from published per-grade rates × denominators; this is exact where rates are exact count ratios (hand cases reproduce integers) but limited by per-grade suppression: schools with suppressed grades contribute partial aggregates (see Case 3). No minimum-coverage filter was applied (spec: any non-suppressed grades, N>0).
• Universe definitions differ by construction: NYSED ENROLLMENT = enrolled ≥10 instructional days and present ≥1 day, grades 1–8/9–12 including ungraded age-equivalent students; DOE per-grade denominators = students contributing 10+ days & 1+ present day, graded only. Median our/NYSED N ratio: ~1.02–1.03 EM, ~1.00 HS.
• NYSED suppression (s) removes 29–83 district schools per panel from the join; suppressed rows are excluded, biasing the joined set slightly toward larger schools.
• Transfer/alternative HS are absent from the DOE EOY file (not from NYSED) — HS join covers ~410 of ~470 rate-bearing NYSED district HS rows.
• Charters reconciled not at all here (no deterministic DBN; SA non-reporting from 2023-24 makes NYSED charter coverage incomplete anyway). D75/D79 are absent from NYSED CA tables.
• 2020-21 (remote year), 2021-22 and 2022-23 were not in the original scope; they were added 2026-06-10 — see §9 (including the 2020-21 remote-attendance caveat).

8. Artifacts

• extracted/SRC{2019,2022,2024,2025}_{EM,HS}_CA.csv — raw mdb exports (gitignored dir).
• nysed_nyc_ca_{2018-19,2023-24,2024-25}.csv — NYC school-level All-Students rows incl. charters, with derived DBN and flags.
• joined_pairs.csv — every joined (school, year, band) with both rates/Ns/counts and diff.
• recon_results.json — full per-panel stats incl. worst-10 lists and BEDS exceptions.
• stats.json — machine-readable key numbers.
• ours_grade_aggregates.csv, ours_gradespan.csv, ours_school_names.csv — DB exports.
• inspect_csvs.py, reconcile.py — the pipeline (reads only; DB access was SELECT-only).

9. Expanded panels (2026-06-10)

Six panels added: 2020-21, 2021-22, 2022-23 × EM/HS. Same method as §1–§2, with two mechanical differences: the NYSED side reads the committed NYC-filtered CSVs at data/sources/nysed/SRC{2022,2024}_{EM,HS}_CA_nyc.csv (2020-21 = SRC2022 YEAR=2021; 2021-22 = SRC2022 YEAR=2022; 2022-23 = SRC2024 YEAR=2023 — its prior-year rows), and the DOE side is a fresh school_year_metrics export (verified row-identical to the original run's export; still the single data_load 644077af). The reimplementation was regression-checked: it reproduces all six original panels field-for-field against the committed stats.json before computing anything new.

Not panelable: 2017-18 is NYSED-only — SRC2019 carries YEAR=2018, but the DOE EOY file starts at 2018-19, so there is nothing to join. 2019-20 was never published (COVID waiver). No inter-release revision check is possible for the added years: each is held in exactly one downloaded release (SRC2021/SRC2023 were not retrieved). BEDS→DBN: zero structural exceptions and zero DBN collisions in all six added panels; D75/D79 rows remain entirely absent.

Coverage (same layout as §3)

(Success Academy reported in all three added years — 0 of 20/29/31 EM schools flagged N — consistent with its non-reporting starting only in 2023-24.)

Results (same layout as §4)

Enrollment-weighted aggregates over the joined sets:

The COVID-era HS tail (verified facts)

The EM band reconciles in all three added years on the same footing as 2018-19/2023-24 (medians −0.13/+0.09/+0.13pp; 76–77% within ±1pp). The HS band does not reconcile in the tails: a one-sided population of high schools sits far below NYSED on near-identical denominators.

1. Schools with our rate more than 5pp below NYSED's, per HS panel: 62 (2020-21), 112 (2021-22), 129 (2022-23) of ~414 joined — vs 0 in 2018-19, 0 in 2023-24 and 3 in 2024-25. The median HS school still nearly agrees (−0.26/−0.15/−0.60pp), so this is a tail phenomenon, not a level shift.
2. Numerator-side: HS denominators are byte-identical at 174/298/272 of ~414 schools, and the worst cases have equal N with hugely different counts (e.g. 2022-23 03M291 West End Secondary: NYSED 318/402 = 79.1% vs ours 104/401 = 25.9%). Extreme 2020-21 examples: 02M507 NYSED 99.8% vs DOE 15.3%; 14K685 NYSED 100.0% vs DOE 32.7% — identical N.
3. Publisher-level, not a reconstruction artifact: at tail schools DOE's own published school-total CA matches our per-grade reconstruction exactly (13K430 2021-22: DOE 20.147% = ours; 02M507 2020-21: DOE 15.317% = ours; 25Q425 2022-23: DOE 36.496% = ours).
4. Same-school persistence: 86 schools are in the <−5pp tail in both 2021-22 and 2022-23; only 11 overlap between 2020-21 and 2021-22 (and 10 across all three).
5. The 2022-23 EM tail (20 schools <−5pp) consists entirely of secondary schools that also appear in the HS panel (6-12/MS-HS buildings: 30Q580, 03M415, 10X141, 28Q284…) — the EM band inherits the HS-grade phenomenon at secondary schools; elementary-only schools are unaffected.
6. Brooklyn Tech illustrates entry/exit: diff +0.11 (2018-19), −0.02 (2020-21), −12.65 (2021-22), −9.49 (2022-23), +0.21 (2023-24), −2.60 (2024-25).

Cause not established from data on hand. The shape (bimodal — exact agreement or a massive NYSED-higher gap, school-by-school, HS grades only, 2020-21 through 2022-23) is what a school-level reporting failure into SIRS would look like (the SIRS manual's default-to-absent rule for missing daily-attendance records is the one documented mechanism that inflates NYSED's numerator only — see NARROWING.md), but no document on hand shows that this occurred or which schools it hit. Do not assert a cause.

2020-21 caveat (applies to both 2020-21 panels, whatever the agreement)

2020-21 was the remote/hybrid year: DOE counted remote and blended "interactions" as attendance, and NYSED published 2020-21 as an informational-only year under the federal accountability waiver. The panels happen to agree at the median (EM −0.13pp, weighted −0.30pp), but both 2020-21 panels are flagged caveat in stats.json: the two publishers were measuring attendance under non-comparable instructional conditions, so agreement or disagreement that year is weaker evidence than in any other panel.

EM trend — no precursor to the 2024-25 EM divergence

Median EM diff (ours − NYSED) in time order: +0.39 (2018-19), −0.13 (2020-21), +0.09 (2021-22), +0.13 (2022-23), +0.38 (2023-24), −1.84 (2024-25). The 2024-25 EM divergence appears abruptly with no precursor, and its shape is different in kind: broad-based (474 of 1,115 schools below −2pp; elementary-only schools shift as much as secondary) vs the COVID-era EM panels (51–61 schools below −2pp, deep <−5pp tail concentrated at secondary buildings, medians unmoved).

Hand-verified cases (continuing §5 numbering)

Case 4 — 01M015 PS 15 Roberto Clemente, 2020-21 EM. NYSED (SRC2022, YEAR=2021): ENROLLMENT 146, ABSENT_COUNT 56, ABSENT_RATE 38.4. Ours (grades 1–5): 32×53.125000% = 17; 27×51.851852% = 14; 32×25.000000% = 8; 32×40.625000% = 13; 29×24.137932% = 7 → 59 absent / 152 = 38.82% vs NYSED 38.4% (diff +0.42pp; N 152 vs 146, ratio 1.041). Reconstructed counts are integers.

Case 5 — 13K430 Brooklyn Technical HS, 2021-22 HS (a tail school). NYSED (SRC2022, YEAR=2022): 5,977 / 1,963 / 32.8. Ours: g9 1,590×6.918239% = 110; g10 1,374×13.173217% = 181; g11 1,528×18.848167% = 288; g12 1,484×42.115902% = 625 → 1,204 / 5,976 = 20.15% vs NYSED 32.8% (−12.65pp on a denominator that differs by one student). DOE's own published school-total CA for 13K430 2021-22 is 20.14725% — matches the reconstruction exactly. Same signature as the 2024-25 Brooklyn Tech case (§5 Case 2): identical universe, conflicting numerator.

Case 6 — 25Q425 John Bowne HS, 2022-23 HS. NYSED (SRC2024, YEAR=2023): 3,119 / 1,298 / 41.6. Ours: g9 1,039×33.686237% = 350; g10 823×36.816525% = 303; g11 659×34.294384% = 226; g12 630×43.015873% = 271 → 1,150 / 3,151 = 36.50% vs NYSED 41.6% (−5.10pp). DOE's published school total 36.49635% matches the reconstruction exactly. (The same school is the single worst 2024-25 HS case at −8.7pp.)

Artifacts (expansion)

• docs/qa_reports/nysed/stats.json — the six new panels appended under panels (same schema; 2020-21 panels carry a caveat field), plus expansion_2026_06_10 notes.
• Scratch (gitignored, alongside §8 artifacts): expand/expand_recon.py (pipeline incl. the §4-panel regression test), expand/ours_grade_aggregates_all.csv (fresh DB export), expand/new_panels.json, expand/joined_pairs_expand.csv (all 12 panels' joined rows).