scripts/analysis/philly-absenteeism-descriptives.ts
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.
// Philly chronic-absenteeism descriptives packet — the Philadelphia parallel
// of the NYC packet behind /methodology/analysis/absenteeism
// (data/analysis/absenteeism-descriptives.json). Writes
// data/analysis/philly-absenteeism-descriptives.json; the working-analysis
// page at /philly/methodology/analysis/absenteeism renders ONLY numbers from
// that file.
//
// Metric: SDP publishes "% of students with 90%+ attendance" but no
// school-level chronic-absenteeism rate. The packet's headline series is the
// COMPLEMENT — pct_below_90 = 100 − rate90, "share of students attending
// less than 90% of enrolled days" — a chronic-absenteeism PROXY, close to but
// not identical to the federal/NYC definition (see the metric methodology
// page for the differences).
//
// Universe (rev 2): the citywide, per-grade, distribution, stability, and
// subgroup series are computed from the SOURCE CSV over ALL District-sector
// schools — including schools that have since closed (e.g. Austin Meehan,
// closed after 2021-22), which the served directory excludes. Sector ==
// "District" is ENFORCED, not assumed. Series that need directory metadata
// (school level, demographic shares, ADA pairing) come from the served DB and
// carry a disclosed directory restriction; the universe_reconciliation block
// lists every school-year the DB is missing relative to the source.
//
// Weighted rates use the EXACT published numerators ("# with 90%+"), not the
// 2dp-rounded percentage — which also makes the source-vs-DB crosscheck a
// comparison of two genuinely different computations.
//
// Suppression note (rev 2): from 2021-22 onward SDP suppresses Non-Binary at
// ~45-58 schools/yr and, to prevent back-calculation, ALSO suppresses exactly
// one of Male/Female at each such school — including very large high schools.
// The gender series is therefore restricted to schools reporting BOTH Male
// and Female; the Black-White gap is additionally computed on the
// common-school universe (schools reporting both groups), since the
// all-cells gap mixes race with school composition.
//
// Run: npx tsx scripts/analysis/philly-absenteeism-descriptives.ts
import fs from "node:fs";
import path from "node:path";
import { parse } from "csv-parse/sync";
import { config as loadEnv } from "dotenv";
loadEnv({ path: path.join(process.cwd(), ".env.local") });
import pg from "pg";
const directUrl = (process.env.DIRECT_URL ??
process.env.POSTGRES_URL_NON_POOLING ??
process.env.DATABASE_URL)!;
function makePool() {
const u = new URL(directUrl);
const ssl =
u.searchParams.get("sslmode") === "disable"
? false
: ({ rejectUnauthorized: false } as const);
return new pg.Pool({
user: decodeURIComponent(u.username),
password: decodeURIComponent(u.password),
host: u.hostname,
port: parseInt(u.port || "5432", 10),
database: u.pathname.slice(1),
ssl,
max: 2,
statement_timeout: 120_000,
});
}
const YEARS = ["2020-21", "2021-22", "2022-23", "2023-24", "2024-25"] as const;
type Year = (typeof YEARS)[number];
// 2021-22: the first in-person year in this file AND the Omicron-affected
// series maximum. There is no pre-pandemic anchor in this file; changes
// measured from a series maximum are bounded to be improvements. The page
// must say this.
const BASELINE: Year = "2021-22";
const LATEST: Year = "2024-25";
const OUT = path.join(
process.cwd(),
"data",
"analysis",
"philly-absenteeism-descriptives.json",
);
// ---------------------------------------------------------------------------
// Small stats helpers
// ---------------------------------------------------------------------------
function r4(n: number): number {
return Math.round(n * 10000) / 10000;
}
function quantile(sorted: number[], q: number): number {
if (sorted.length === 0) return NaN;
const pos = (sorted.length - 1) * q;
const lo = Math.floor(pos);
const hi = Math.ceil(pos);
if (lo === hi) return sorted[lo];
return sorted[lo] + (sorted[hi] - sorted[lo]) * (pos - lo);
}
function mean(xs: number[]): number {
return xs.reduce((a, b) => a + b, 0) / xs.length;
}
function sd(xs: number[]): number {
const m = mean(xs);
return Math.sqrt(mean(xs.map((x) => (x - m) ** 2)));
}
function median(xs: number[]): number {
return quantile([...xs].sort((a, b) => a - b), 0.5);
}
function pearson(pairs: [number, number][]): number {
const mx = mean(pairs.map((p) => p[0]));
const my = mean(pairs.map((p) => p[1]));
let sxy = 0;
let sxx = 0;
let syy = 0;
for (const [x, y] of pairs) {
sxy += (x - mx) * (y - my);
sxx += (x - mx) ** 2;
syy += (y - my) ** 2;
}
return sxy / Math.sqrt(sxx * syy);
}
/** Midrank transform for Spearman. */
function midranks(xs: number[]): number[] {
const idx = xs.map((v, i) => [v, i] as const).sort((a, b) => a[0] - b[0]);
const out = new Array<number>(xs.length);
let i = 0;
while (i < idx.length) {
let j = i;
while (j + 1 < idx.length && idx[j + 1][0] === idx[i][0]) j++;
const rank = (i + j) / 2 + 1;
for (let k = i; k <= j; k++) out[idx[k][1]] = rank;
i = j + 1;
}
return out;
}
function spearman(pairs: [number, number][]): number {
const rx = midranks(pairs.map((p) => p[0]));
const ry = midranks(pairs.map((p) => p[1]));
return pearson(rx.map((r, i) => [r, ry[i]] as [number, number]));
}
type SchoolStats = {
weighted: number;
unweighted_mean: number;
p10: number;
p25: number;
p50: number;
p75: number;
p90: number;
sd: number;
n_schools: number;
n_students: number;
};
/** School-level descriptives over cells with exact numerators.
* v = below-90 share per school (for school-level stats);
* weighted = 1 − Σnum90/Σdenom (exact-numerator citywide share). */
function describe(
cells: { v: number; denom: number; num90: number }[],
): SchoolStats {
if (cells.length === 0)
throw new Error("describe() called with zero cells — check the filters");
const vs = cells.map((c) => c.v).sort((a, b) => a - b);
const totN = cells.reduce((a, c) => a + c.denom, 0);
const tot90 = cells.reduce((a, c) => a + c.num90, 0);
return {
weighted: r4(100 * (1 - tot90 / totN)),
unweighted_mean: r4(mean(vs)),
p10: r4(quantile(vs, 0.1)),
p25: r4(quantile(vs, 0.25)),
p50: r4(quantile(vs, 0.5)),
p75: r4(quantile(vs, 0.75)),
p90: r4(quantile(vs, 0.9)),
sd: r4(sd(vs)),
n_schools: cells.length,
n_students: totN,
};
}
function weightedBelow90(cells: { denom: number; num90: number }[]): number {
const totN = cells.reduce((a, c) => a + c.denom, 0);
const tot90 = cells.reduce((a, c) => a + c.num90, 0);
return 100 * (1 - tot90 / totN);
}
// ---------------------------------------------------------------------------
// Source-CSV readers (independent of the DB)
// ---------------------------------------------------------------------------
function latestSourcePath(sourceId: string): string {
const dir = path.join(
process.cwd(),
"data",
"cities",
"philly",
"sources",
sourceId,
);
const dated = fs
.readdirSync(dir)
.filter((n) => /^\d{4}-\d{2}-\d{2}$/.test(n))
.sort()
.reverse();
for (const d of dated) {
const entries = fs
.readdirSync(path.join(dir, d))
.filter((n) => n.endsWith(".csv"));
if (entries.length > 0) return path.join(dir, d, entries[0]);
}
throw new Error(`no fetched CSV for ${sourceId} — run pipeline_philly.discover`);
}
type SchoolCsvRow = {
year: Year;
ulcs: string;
name: string;
category: string;
group: string;
denom: number | null;
num90: number | null;
rate90: number | null;
};
function normYear(sy: string): Year | null {
// "2020-2021" → "2020-21"
const m = /^(\d{4})-(\d{4})$/.exec(sy);
if (!m) return null;
const y = `${m[1]}-${m[2].slice(2)}` as Year;
return (YEARS as readonly string[]).includes(y) ? y : null;
}
/** District-sector rows only — enforced, with a canary on unexpected sectors. */
function readSchoolCsv(): SchoolCsvRow[] {
const p = latestSourcePath("sdp_attendance_90_school");
const rows = parse(fs.readFileSync(p, "utf-8"), {
columns: true,
skip_empty_lines: true,
}) as Record<string, string>[];
const sectors = new Set(rows.map((r) => r["Sector"]));
for (const s of sectors) {
if (s !== "District")
console.warn(`[warn] school CSV contains non-District sector "${s}" — excluded`);
}
return rows
.filter((r) => r["Sector"] === "District")
.map((r) => {
const year = normYear(r["School Year"]);
const denom = parseFloat(r["Total Students (Yearly)"]);
const num90 = parseFloat(r["# with 90%+ Attendance (Yearly)"]);
const rate = parseFloat(r["% with 90%+ Attendance (Yearly)"]);
return {
year: year as Year,
ulcs: r["ULCS Code"],
name: r["School Name"],
category: r["Category"],
group: r["Group"],
denom: Number.isFinite(denom) ? denom : null,
num90: Number.isFinite(num90) ? num90 : null,
rate90: Number.isFinite(rate) ? rate : null,
};
})
.filter((r) => r.year !== null);
}
type MonthlyCsvRow = {
sy: string; // full "2020-2021".."2025-2026" label — includes 2025-26
month: string;
category: string;
group: string;
denom: number | null;
num90: number | null;
rate90: number | null;
};
function readMonthlyCsv(): MonthlyCsvRow[] {
const p = latestSourcePath("sdp_attendance_90_district_monthly");
const rows = parse(fs.readFileSync(p, "utf-8"), {
columns: true,
skip_empty_lines: true,
}) as Record<string, string>[];
return rows
.filter((r) => r["Sector"] === "District")
.map((r) => {
const denom = parseFloat(r["Total Students (This Month)"]);
const num90 = parseFloat(r["# with 90%+ Attendance (This Month)"]);
const rate = parseFloat(r["% with 90%+ Attendance (This Month)"]);
return {
sy: r["School Year"],
month: r["Month"],
category: r["Category"],
group: r["Group"],
denom: Number.isFinite(denom) ? denom : null,
num90: Number.isFinite(num90) ? num90 : null,
rate90: Number.isFinite(rate) ? rate : null,
};
});
}
type Cell = { ulcs: string; name: string; v: number; denom: number; num90: number };
// ---------------------------------------------------------------------------
// Main
// ---------------------------------------------------------------------------
async function main() {
const pool = makePool();
const schoolCsv = readSchoolCsv();
// ---- Source: All-Students cells per year (FULL District universe,
// including since-closed schools; suppressed cells excluded) ----
const allCells = new Map<Year, Cell[]>();
for (const y of YEARS) allCells.set(y, []);
for (const r of schoolCsv) {
if (r.category !== "All Students") continue;
if (r.denom === null || r.num90 === null || r.rate90 === null) continue;
allCells.get(r.year)!.push({
ulcs: r.ulcs,
name: r.name,
v: 100 - r.rate90,
denom: r.denom,
num90: r.num90,
});
}
// 1 — citywide trend (source, full universe, exact numerators)
const citywide_trend = Object.fromEntries(
YEARS.map((y) => [y, describe(allCells.get(y)!)]),
) as Record<Year, SchoolStats>;
// 2 — per-grade citywide (source, full universe)
const GRADE_ORDER = [
"00", "01", "02", "03", "04", "05", "06",
"07", "08", "09", "10", "11", "12",
];
const gradeLabel = (g: string) => (g === "00" ? "K" : String(parseInt(g, 10)));
const per_grade: Record<
string,
Record<Year, { weighted: number; n_schools: number; n_students: number }> & {
chg_baseline_to_latest: number;
}
> = {};
for (const g of GRADE_ORDER) {
const entry: Record<string, unknown> = {};
for (const y of YEARS) {
const cells = schoolCsv.filter(
(r) =>
r.category === "Grade Level" && r.group === g && r.year === y &&
r.denom !== null && r.num90 !== null,
);
const totN = cells.reduce((a, c) => a + (c.denom as number), 0);
const tot90 = cells.reduce((a, c) => a + (c.num90 as number), 0);
entry[y] = {
weighted: totN > 0 ? r4(100 * (1 - tot90 / totN)) : null,
n_schools: cells.length,
n_students: totN,
};
}
const b = (entry[BASELINE] as { weighted: number }).weighted;
const l = (entry[LATEST] as { weighted: number }).weighted;
entry.chg_baseline_to_latest = r4(l - b);
per_grade[gradeLabel(g)] = entry as (typeof per_grade)[string];
}
// ---- DB: All-Students cells (directory-restricted; used for series that
// need directory metadata, and for the crosscheck) ----
const dbRows = (
await pool.query<{
school_ulcs: string;
year: Year;
value: number;
denominator: number;
school_level: string | null;
school_name: string;
}>(
`SELECT m.school_ulcs, m.year, m.value, m.denominator,
s.school_level, s.name AS school_name
FROM philly_school_year_metrics m
JOIN philly_schools s ON s.ulcs_code = m.school_ulcs
WHERE m.metric_key = 'attendance_rate_above_90'
AND m.subgroup = 'ALL' AND NOT m.suppressed
AND m.value IS NOT NULL AND m.denominator IS NOT NULL`,
)
).rows.map((r) => ({
...r,
value: Number(r.value),
denominator: Number(r.denominator),
chronic: 100 - Number(r.value),
}));
const dbByYear = new Map<Year, typeof dbRows>();
for (const y of YEARS) dbByYear.set(y, []);
for (const r of dbRows) dbByYear.get(r.year)?.push(r);
// 2b — grade-profile shape, computed so the page's wording ("lowest in
// grade X", "higher in each successive grade") can be conditioned on the
// data instead of asserted.
const GRADE_LABELS = GRADE_ORDER.map(gradeLabel);
const gradeVals = (y: Year): { g: string; v: number }[] =>
GRADE_LABELS.map((g) => ({ g, v: per_grade[g][y].weighted as number }));
const per_grade_shape = Object.fromEntries(
([BASELINE, LATEST] as Year[]).map((y) => {
const vals = gradeVals(y);
const trough = vals.reduce((a: { g: string; v: number }, b: { g: string; v: number }) => (b.v < a.v ? b : a));
const ups = vals.slice(1).filter((e: { g: string; v: number }, i: number) => e.v > vals[i].v).map((e: { g: string; v: number }) => e.g);
return [y, {
trough_grade: trough.g,
trough_value: r4(trough.v),
grades_higher_than_previous: ups,
k_to_grade1_direction: vals[1].v > vals[0].v ? "up" : vals[1].v < vals[0].v ? "down" : "flat",
monotone_decline_k_to_trough: vals
.slice(1, vals.findIndex((e: { g: string }) => e.g === trough.g) + 1)
.every((e: { v: number }, i: number) => e.v <= vals[i].v),
monotone_rise_9_to_12: (() => {
const i9 = vals.findIndex((e: { g: string }) => e.g === "9");
return vals.slice(i9 + 1).every((e: { v: number }, i: number) => e.v >= vals[i9 + i].v);
})(),
}];
}),
);
const per_grade_change = Object.fromEntries(
GRADE_LABELS.map((g) => [g, r4((per_grade[g][LATEST].weighted as number) - (per_grade[g][BASELINE].weighted as number))]),
);
// 3 — school-level (grade-configuration) trend — DB (needs school_level).
// Directory-restricted: since-closed schools are absent (disclosed).
const LEVELS = [
"Elementary",
"Elementary-Middle",
"Middle",
"Middle-High",
"High",
];
const school_level_trend = Object.fromEntries(
LEVELS.map((lvl) => {
const entry: Record<string, unknown> = {};
for (const y of YEARS) {
const cells = dbByYear
.get(y)!
.filter((r) => r.school_level === lvl);
const totN = cells.reduce((a, c) => a + c.denominator, 0);
entry[y] = {
weighted: r4(
cells.reduce((a, c) => a + c.chronic * c.denominator, 0) / totN,
),
unweighted_mean: r4(mean(cells.map((c) => c.chronic))),
n_schools: cells.length,
};
}
const b = (entry[BASELINE] as { weighted: number }).weighted;
const l = (entry[LATEST] as { weighted: number }).weighted;
entry.chg_baseline_to_latest = r4(l - b);
return [lvl, entry];
}),
);
// 4 — chronic proxy vs ADA — DB pairs (ADA is loaded ALL-only). Pearson AND
// Spearman (the page's "rank schools" language needs a rank statistic).
const adaRows = (
await pool.query<{ school_ulcs: string; year: Year; value: number }>(
`SELECT school_ulcs, year, value
FROM philly_school_year_metrics
WHERE metric_key = 'average_daily_attendance'
AND subgroup = 'ALL' AND NOT suppressed AND value IS NOT NULL`,
)
).rows;
const adaBy = new Map(adaRows.map((r) => [`${r.school_ulcs}|${r.year}`, Number(r.value)]));
// NOTE on weighting: weighted_ada is the mean of school ADA rates weighted
// by each school's attendance-file student count (not by possible
// student-days), so it is an enrollment-weighted average of school ADA,
// not a pooled district ADA. The threshold measure (below-90 share) and
// ADA answer different questions — how many students cross a line vs how
// many days are missed in total — and need not move proportionally.
const ca_vs_ada = Object.fromEntries(
YEARS.map((y) => {
const pairs: [number, number][] = [];
let wCa = 0;
let wAda = 0;
let totN = 0;
for (const r of dbByYear.get(y)!) {
const ada = adaBy.get(`${r.school_ulcs}|${y}`);
if (ada === undefined) continue;
pairs.push([r.chronic, ada]);
wCa += r.chronic * r.denominator;
wAda += ada * r.denominator;
totN += r.denominator;
}
const rr = pearson(pairs);
return [
y,
{
weighted_ca: r4(wCa / totN),
weighted_ada: r4(wAda / totN),
school_level_r: r4(rr),
spearman_r: r4(spearman(pairs)),
r_squared: r4(rr * rr),
n_schools: pairs.length,
},
];
}),
);
// 5 — subgroup series (source, full District universe — same as Fig 1).
//
// Race/ethnicity: per-group levels over all schools reporting that group
// (n_schools varies a lot — White reports at ~half of schools — so the
// packet carries n per cell and the chart must show it).
//
// Gender: restricted to schools reporting BOTH Male and Female, because
// SDP's complementary suppression (see header) removes one of the two at
// ~45-58 schools/yr from 2021-22 on — including large high schools — which
// otherwise corrupts the series and fabricates a M/F crossover.
const GROUP_TO_KEY: Record<string, string> = {
"Black/African American": "BLACK",
"Hispanic/Latino": "HISPANIC",
White: "WHITE",
Asian: "ASIAN",
"Multi Racial/Other": "TWO_OR_MORE_RACES",
};
const raceCells = new Map<string, Map<Year, Cell[]>>();
for (const key of Object.values(GROUP_TO_KEY)) {
raceCells.set(key, new Map(YEARS.map((y) => [y, []])));
}
const genderCells = new Map<Year, Map<string, { m?: Cell; f?: Cell }>>(
YEARS.map((y) => [y, new Map()]),
);
for (const r of schoolCsv) {
if (r.denom === null || r.num90 === null || r.rate90 === null) continue;
const cell: Cell = {
ulcs: r.ulcs, name: r.name, v: 100 - r.rate90,
denom: r.denom, num90: r.num90,
};
if (r.category === "Race/Ethnicity" && GROUP_TO_KEY[r.group]) {
raceCells.get(GROUP_TO_KEY[r.group])!.get(r.year)!.push(cell);
} else if (r.category === "Gender" && (r.group === "Male" || r.group === "Female")) {
const bySchool = genderCells.get(r.year)!;
const e = bySchool.get(r.ulcs) ?? {};
if (r.group === "Male") e.m = cell;
else e.f = cell;
bySchool.set(r.ulcs, e);
}
}
const subgroup_trend = Object.fromEntries(
Object.values(GROUP_TO_KEY).map((key) => [
key,
Object.fromEntries(
YEARS.map((y) => {
const cells = raceCells.get(key)!.get(y)!;
return [
y,
{
weighted: r4(weightedBelow90(cells)),
n_schools: cells.length,
n_students: cells.reduce((a, c) => a + c.denom, 0),
},
];
}),
),
]),
);
// Peak year per race/ethnicity group (so "every group peaked in 2021-22"
// is conditioned on the data).
const subgroup_peak_year = Object.fromEntries(
Object.values(GROUP_TO_KEY).map((key) => {
const inPerson = YEARS.filter((y) => y !== "2020-21").map((y) => ({ y, v: subgroup_trend[key][y].weighted as number }));
const peak = inPerson.reduce((a, b) => (b.v > a.v ? b : a));
return [key, { peak_in_person_year: peak.y, peak_value: peak.v, range_in_person: r4(Math.max(...inPerson.map((e) => e.v)) - Math.min(...inPerson.map((e) => e.v))) }];
}),
);
// Gender, common-school universe only.
const gender_trend_common = Object.fromEntries(
(["MALE", "FEMALE"] as const).map((key) => [
key,
Object.fromEntries(
YEARS.map((y) => {
const both = [...genderCells.get(y)!.values()].filter((e) => e.m && e.f);
const cells = both.map((e) => (key === "MALE" ? e.m! : e.f!));
return [
y,
{
weighted: r4(weightedBelow90(cells)),
n_schools: cells.length,
n_students: cells.reduce((a, c) => a + c.denom, 0),
},
];
}),
),
]),
);
// Gender, FIXED panel: the schools reporting both genders in EVERY
// in-person year (the per-year common set above changes membership from
// year to year — 157/165/165/171 schools — so this sensitivity holds the
// school set constant).
const IN_PERSON_YEARS = YEARS.filter((y) => y !== "2020-21");
const genderPanel = IN_PERSON_YEARS.map((y) =>
new Set([...genderCells.get(y)!.entries()].filter(([, e]) => e.m && e.f).map(([u]) => u)),
).reduce((acc, s) => new Set([...acc].filter((u) => s.has(u))));
const gender_trend_fixed_panel = {
years: IN_PERSON_YEARS,
n_schools: genderPanel.size,
note: "Schools reporting both Male and Female (unsuppressed) in every in-person year; a sensitivity check on the per-year common-school series.",
...Object.fromEntries(
(["MALE", "FEMALE"] as const).map((key) => [
key,
Object.fromEntries(
IN_PERSON_YEARS.map((y) => {
const cells = [...genderCells.get(y)!.entries()]
.filter(([u]) => genderPanel.has(u))
.map(([, e]) => (key === "MALE" ? e.m! : e.f!));
return [y, { weighted: r4(weightedBelow90(cells)), n_schools: cells.length, n_students: cells.reduce((a, c) => a + c.denom, 0) }];
}),
),
]),
),
};
// 5b — Black-White gap on the common-school universe (schools reporting
// BOTH groups unsuppressed). The all-cells gap mixes race with school
// composition: White reports at only ~half of schools, and the schools
// that don't clear the White reporting threshold are the higher-absence
// schools — inflating the apparent gap.
const black_white_common = Object.fromEntries(
YEARS.map((y) => {
const blackBy = new Map(
raceCells.get("BLACK")!.get(y)!.map((c) => [c.ulcs, c]),
);
const pairs = raceCells
.get("WHITE")!
.get(y)!
.filter((c) => blackBy.has(c.ulcs))
.map((w) => ({ w, b: blackBy.get(w.ulcs)! }));
const black = weightedBelow90(pairs.map((p) => p.b));
const white = weightedBelow90(pairs.map((p) => p.w));
// `gap` is the difference between two GROUP-weighted rates over the
// common schools (each group's students pooled across those schools);
// it still reflects which schools each group's students attend. The
// two within-school contrasts below hold the school fixed: the mean
// of each school's own Black−White difference, unweighted and
// weighted by the school's Black+White student count.
const withinDiffs = pairs.map((p) => p.b.v - p.w.v);
const bwWeights = pairs.map((p) => p.b.denom + p.w.denom);
const wsum = bwWeights.reduce((a, b) => a + b, 0);
const wavg = (vs: number[]) => vs.reduce((a, v, i) => a + v * bwWeights[i], 0) / wsum;
return [
y,
{
black: r4(black),
white: r4(white),
gap: r4(black - white),
// Within-school contrasts and the level pairs behind them.
black_mean_school: r4(mean(pairs.map((p) => p.b.v))),
white_mean_school: r4(mean(pairs.map((p) => p.w.v))),
gap_within_school_equal_weight: r4(mean(withinDiffs)),
black_bw_weighted: r4(wavg(pairs.map((p) => p.b.v))),
white_bw_weighted: r4(wavg(pairs.map((p) => p.w.v))),
gap_within_school_bw_weight: r4(wavg(withinDiffs)),
n_schools: pairs.length,
},
];
}),
);
// 5c — Black-White fixed panel: schools reporting both groups in EVERY
// in-person year (the per-year common set changes membership: 113 → 111
// schools), so the trend in the within-school gap is read on one school set.
const bwPanel = IN_PERSON_YEARS.map((y) => {
const blackBy = new Set(raceCells.get("BLACK")!.get(y)!.map((c) => c.ulcs));
return new Set(raceCells.get("WHITE")!.get(y)!.filter((c) => blackBy.has(c.ulcs)).map((c) => c.ulcs));
}).reduce((acc, s2) => new Set([...acc].filter((u) => s2.has(u))));
const black_white_fixed_panel = {
years: IN_PERSON_YEARS,
n_schools: bwPanel.size,
note: "Schools with unsuppressed Black and White cells in every in-person year; weights are the school's Black + White student records.",
...Object.fromEntries(
IN_PERSON_YEARS.map((y) => {
const blackBy = new Map(raceCells.get("BLACK")!.get(y)!.map((c) => [c.ulcs, c]));
const pairs = raceCells.get("WHITE")!.get(y)!.filter((c) => bwPanel.has(c.ulcs)).map((w) => ({ w, b: blackBy.get(w.ulcs)! }));
const diffs = pairs.map((p) => p.b.v - p.w.v);
const wts = pairs.map((p) => p.b.denom + p.w.denom);
const wsum = wts.reduce((a, b) => a + b, 0);
return [y, {
black_mean_school: r4(mean(pairs.map((p) => p.b.v))),
white_mean_school: r4(mean(pairs.map((p) => p.w.v))),
gap_within_school_equal_weight: r4(mean(diffs)),
gap_within_school_bw_weight: r4(diffs.reduce((a, d, i) => a + d * wts[i], 0) / wsum),
n_schools: pairs.length,
}];
}),
),
};
// 6 — demographic correlation (DB; 2024-25 only — the year shares are
// loaded for). Simple bivariate r; school level is a major confounder
// (special-admit HS are whiter, lower-poverty, lower-absence) — the page
// must say so.
const demoRows = (
await pool.query<{ school_ulcs: string; metric_key: string; value: number }>(
`SELECT school_ulcs, metric_key, value
FROM philly_school_year_metrics
WHERE metric_key = ANY($1) AND subgroup = 'ALL' AND year = '2024-25'
AND NOT suppressed AND value IS NOT NULL`,
[[
"pct_econ_disadv", "pct_black", "pct_hispanic", "pct_white",
"pct_english_learner", "pct_special_ed", "enrollment",
]],
)
).rows;
const caLatest = new Map(
dbByYear.get(LATEST)!.map((r) => [r.school_ulcs, r.chronic]),
);
const demographics_correlation = Object.fromEntries(
[
"pct_econ_disadv", "pct_black", "pct_hispanic", "pct_white",
"pct_english_learner", "pct_special_ed", "enrollment",
].map((mk) => {
const pairs: [number, number][] = [];
for (const d of demoRows.filter((r) => r.metric_key === mk)) {
const ca = caLatest.get(d.school_ulcs);
if (ca !== undefined) pairs.push([Number(d.value), ca]);
}
return [mk, { r: r4(pearson(pairs)), n_schools: pairs.length }];
}),
);
// 6b — composition segments (2024-25): the reader-facing version of the
// correlations above. Schools split into thirds by each demographic share
// (equal school counts); weighted below-90 rate per third. Same underlying
// data as the correlations — a presentation, not a new method.
const caDenomLatest = new Map(
dbByYear
.get(LATEST)!
.map((r) => [r.school_ulcs, { chronic: r.chronic, denom: r.denominator }]),
);
// pct_econ_disadv is excluded: 203 of 248 District schools are recorded at
// exactly 100% economically disadvantaged (community-eligibility
// reporting), so the measure cannot distinguish schools. The count is
// carried in econ_disadv_top_coded for the page to state plainly.
const SEGMENT_KEYS = [
"pct_special_ed",
"pct_white",
"pct_english_learner",
] as const;
const econRows = demoRows.filter(
(r) => r.metric_key === "pct_econ_disadv" && caDenomLatest.has(r.school_ulcs),
);
const composition_segments = {
year: LATEST,
note:
"Schools split into thirds (equal school counts) by each 2024-25 demographic share; rate = student-weighted below-90 share within the third.",
econ_disadv_top_coded: {
n_at_100: econRows.filter((r) => Number(r.value) >= 99.95).length,
n_total: econRows.length,
},
characteristics: Object.fromEntries(
SEGMENT_KEYS.map((mk) => {
const rows = demoRows
.filter((r) => r.metric_key === mk)
.map((d) => ({
share: Number(d.value),
cell: caDenomLatest.get(d.school_ulcs),
}))
.filter((x): x is { share: number; cell: { chronic: number; denom: number } } =>
x.cell !== undefined,
)
.sort((a, b) => a.share - b.share);
const thirds = [
rows.slice(0, Math.floor(rows.length / 3)),
rows.slice(Math.floor(rows.length / 3), Math.floor((2 * rows.length) / 3)),
rows.slice(Math.floor((2 * rows.length) / 3)),
];
return [
mk,
thirds.map((t, i) => {
const totN = t.reduce((a, x) => a + x.cell.denom, 0);
return {
third: (["lowest", "middle", "highest"] as const)[i],
share_min: r4(t[0].share),
share_max: r4(t[t.length - 1].share),
n_schools: t.length,
weighted_below_90: r4(
t.reduce((a, x) => a + x.cell.chronic * x.cell.denom, 0) / totN,
),
};
}),
];
}),
),
};
// 7 — distribution (source, full universe)
const BIN = 5;
const edges = Array.from({ length: 100 / BIN + 1 }, (_, i) => i * BIN);
function histo(y: Year) {
const vs = allCells.get(y)!.map((c) => c.v);
const counts = new Array(edges.length - 1).fill(0);
for (const v of vs) {
const i = Math.min(Math.floor(v / BIN), counts.length - 1);
counts[i] += 1;
}
return { counts, n_schools: vs.length, bin_edges: edges };
}
const distribution = {
bins_pp: BIN,
histograms: { [BASELINE]: histo(BASELINE), [LATEST]: histo(LATEST) },
n_above_50_by_year: Object.fromEntries(
YEARS.map((y) => {
const vs = allCells.get(y)!.map((c) => c.v);
const n = vs.filter((v) => v > 50).length;
return [
y,
{ n_above_50: n, n_schools: vs.length, pct: r4((100 * n) / vs.length) },
];
}),
),
};
// 8 — stability (source, full universe; pairs on ULCS — closed schools
// participate in the year-pairs where they exist)
const srcCellBy = new Map<string, Cell>();
for (const y of YEARS)
for (const c of allCells.get(y)!) srcCellBy.set(`${c.ulcs}|${y}`, c);
function pairSeries(y1: Year, y2: Year): [number, number][] {
const out: [number, number][] = [];
for (const c of allCells.get(y1)!) {
const nxt = srcCellBy.get(`${c.ulcs}|${y2}`);
if (nxt) out.push([c.v, nxt.v]);
}
return out;
}
const consecutive_r = Object.fromEntries(
YEARS.slice(0, -1).map((y, i) => {
const y2 = YEARS[i + 1];
const ps = pairSeries(y, y2);
return [`${y}->${y2}`, { pearson_r: r4(pearson(ps)), n_schools: ps.length }];
}),
);
const longArc = pairSeries(BASELINE, LATEST);
const latestPairYears: [Year, Year] = ["2023-24", "2024-25"];
const latestPairs: { prev: number; cur: number; n: number }[] = [];
for (const c of allCells.get(latestPairYears[0])!) {
const nxt = srcCellBy.get(`${c.ulcs}|${latestPairYears[1]}`);
if (nxt) latestPairs.push({ prev: c.v, cur: nxt.v, n: nxt.denom });
}
const changes = latestPairs.map((p) => p.cur - p.prev);
const pct = (f: (c: number) => boolean) =>
r4((100 * changes.filter(f).length) / changes.length);
const SIZE_BINS: [string, (n: number) => boolean][] = [
["<300", (n) => n < 300],
["300-599", (n) => n >= 300 && n < 600],
["600+", (n) => n >= 600],
];
const stability = {
consecutive_r,
long_arc_baseline_to_latest: {
pair: `${BASELINE}->${LATEST}`,
pearson_r: r4(pearson(longArc)),
n_schools: longArc.length,
},
latest_pair_change: {
pair: `${latestPairYears[0]}->${latestPairYears[1]}`,
n_schools: changes.length,
median_change: r4(median(changes)),
mean_change: r4(mean(changes)),
median_abs_change: r4(median(changes.map(Math.abs))),
pct_improved_gt2pp: pct((c) => c < -2),
pct_improved_gt5pp: pct((c) => c < -5),
pct_worsened_gt2pp: pct((c) => c > 2),
pct_worsened_gt5pp: pct((c) => c > 5),
n_moved_gt5: changes.filter((c) => Math.abs(c) > 5).length,
n_moved_gt10: changes.filter((c) => Math.abs(c) > 10).length,
},
volatility_by_size_latest_pair: Object.fromEntries(
SIZE_BINS.map(([label, f]) => {
const cs = latestPairs.filter((p) => f(p.n)).map((p) => p.cur - p.prev);
return [
label,
{
n: cs.length,
sd_change: r4(sd(cs)),
median_abs_change: r4(median(cs.map(Math.abs))),
mean_change: r4(mean(cs)),
},
];
}),
),
scatter_latest_pair: latestPairs.map(
(p) => [r4(p.prev), r4(p.cur)] as [number, number],
),
};
// Named size-contrast ratios (the statistic matters: sd vs median |change|).
const smallBin = stability.volatility_by_size_latest_pair["<300"];
const largeBin = stability.volatility_by_size_latest_pair["600+"];
const stabilityWithRatios = {
...stability,
small_vs_large_ratio: {
sd_change: r4(smallBin.sd_change / largeBin.sd_change),
median_abs_change: r4(smallBin.median_abs_change / largeBin.median_abs_change),
note: "<300 students vs 600+; the standard-deviation ratio and the median-absolute-change ratio differ, so the page names which one it uses.",
},
};
// 9 — district monthly (incl. partial 2025-26), plus a like-for-like
// Sep-Mar aggregate per year (the months every year shares with 2025-26).
const monthly = readMonthlyCsv();
const MONTHS = ["Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar", "Apr", "May", "Jun"];
const SEP_MAR = ["Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar"];
const SEP_NOV = ["Sep", "Oct", "Nov"];
const DEC_FEB = ["Dec", "Jan", "Feb"];
const MONTHLY_SYS = [
"2020-2021", "2021-2022", "2022-2023",
"2023-2024", "2024-2025", "2025-2026",
];
const district_monthly = Object.fromEntries(
MONTHLY_SYS.map((sy) => {
const entry: Record<string, unknown> = {};
for (const m of MONTHS) {
const rows = monthly.filter(
(r) =>
r.sy === sy && r.month === m &&
r.category === "All Students" &&
r.rate90 !== null && r.denom !== null,
);
if (rows.length > 1)
throw new Error(`expected ≤1 All-Students row for ${sy} ${m}, got ${rows.length}`);
if (rows.length === 1)
entry[m] = {
pct_below_90: r4(100 - (rows[0].rate90 as number)),
n_students: rows[0].denom as number,
};
}
const smCells = SEP_MAR.map((m) => entry[m]).filter(Boolean) as {
pct_below_90: number;
n_students: number;
}[];
const smN = smCells.reduce((a, c) => a + c.n_students, 0);
entry.sep_mar_weighted =
smCells.length === SEP_MAR.length
? r4(smCells.reduce((a, c) => a + c.pct_below_90 * c.n_students, 0) / smN)
: null;
// Window averages of monthly shares, weighted by each month's student
// count (a student can contribute in several months; NOT the share of
// unique students below 90% over the window).
const winAvg = (ms: string[]) => {
const cells = ms.map((m) => entry[m]).filter(Boolean) as { pct_below_90: number; n_students: number }[];
if (cells.length !== ms.length) return null;
const n = cells.reduce((a, c) => a + c.n_students, 0);
return r4(cells.reduce((a, c) => a + c.pct_below_90 * c.n_students, 0) / n);
};
entry.sep_nov_weighted = winAvg(SEP_NOV);
entry.dec_feb_weighted = winAvg(DEC_FEB);
const short = normYear(sy) ?? `${sy.slice(0, 4)}-${sy.slice(7)}`;
return [short, entry];
}),
);
// 9b — monthly records: for every month, which year had the lowest and
// highest within-month below-90 share, among ALL years and among the
// fully in-person years (2020-21 excluded — the same exclusion the trend
// statements use). Superlatives on the page are composed from this block,
// never typed.
const MONTHLY_YEARS = Object.keys(district_monthly);
const IN_PERSON_MONTHLY = MONTHLY_YEARS.filter((y) => y !== "2020-21");
const monthRecord = (m: string, years: string[]) => {
const vals = years
.map((y) => ({ y, v: (district_monthly[y] as Record<string, { pct_below_90: number } | undefined>)[m]?.pct_below_90 }))
.filter((e): e is { y: string; v: number } => typeof e.v === "number");
if (vals.length === 0) return null;
const lo = vals.reduce((a, b) => (b.v < a.v ? b : a));
const hi = vals.reduce((a, b) => (b.v > a.v ? b : a));
const sorted = [...vals].sort((a, b) => a.v - b.v);
return {
lowest: lo.y, lowest_value: lo.v, highest: hi.y, highest_value: hi.v,
second_highest_value: sorted.length > 1 ? sorted[sorted.length - 2].v : null,
n_years: vals.length,
};
};
const windowRecord = (key: string, years: string[]) => {
const vals = years
.map((y) => ({ y, v: (district_monthly[y] as Record<string, number | null>)[key] }))
.filter((e): e is { y: string; v: number } => typeof e.v === "number");
if (vals.length === 0) return null;
const lo = vals.reduce((a, b) => (b.v < a.v ? b : a));
const hi = vals.reduce((a, b) => (b.v > a.v ? b : a));
return { lowest: lo.y, lowest_value: lo.v, highest: hi.y, highest_value: hi.v, n_years: vals.length };
};
const monthly_records = {
note: "'all_years' = every year in the monthly file; 'in_person' excludes 2020-21 (mostly virtual), matching the trend-statement exclusion. Lowest = best attendance.",
by_month: Object.fromEntries(
MONTHS.map((m) => [m, { all_years: monthRecord(m, MONTHLY_YEARS), in_person: monthRecord(m, IN_PERSON_MONTHLY) }]),
),
windows: Object.fromEntries(
(["sep_nov_weighted", "dec_feb_weighted", "sep_mar_weighted"] as const).map((k) => [
k, { all_years: windowRecord(k, MONTHLY_YEARS), in_person: windowRecord(k, IN_PERSON_MONTHLY) },
]),
),
};
// 10 — crosschecks + universe reconciliation.
//
// (a) Universe reconciliation: schools in the source (full District
// universe) but not the served DB, per year, with their rates — the
// check that CAN fail, and the disclosure of the directory restriction
// (closed schools are absent from the current master list).
// (b) Cell crosscheck on the common universe: DB value (loaded percentage)
// vs source exact-numerator recomputation, tolerance 0.01 — two
// genuinely different computations of the same cell set.
const universe_reconciliation: Record<
string,
{
source_only: { ulcs: string; name: string; n_students: number; below_90: number }[];
db_only: { ulcs: string; name: string }[];
weighted_full_universe?: number;
weighted_directory_only?: number;
effect_of_including_source_only?: number;
}
> = {};
const crosschecks: {
stat: string;
from_db: number;
from_source: number;
diff: number;
match: boolean;
}[] = [];
for (const y of YEARS) {
const src = allCells.get(y)!;
const db = dbByYear.get(y)!;
const dbSet = new Set(db.map((r) => r.school_ulcs));
const srcSet = new Set(src.map((c) => c.ulcs));
universe_reconciliation[y] = {
source_only: src
.filter((c) => !dbSet.has(c.ulcs))
.map((c) => ({
ulcs: c.ulcs,
name: c.name,
n_students: c.denom,
below_90: r4(c.v),
})),
db_only: db
.filter((r) => !srcSet.has(r.school_ulcs))
.map((r) => ({ ulcs: r.school_ulcs, name: r.school_name })),
};
// Effect of the directory restriction on the district-wide weighted rate:
// the full-universe figure (what the page shows) minus the directory-only
// figure. Sign is whatever the data says — closed schools are not assumed
// to be high-absence.
const dirOnly = src.filter((c) => dbSet.has(c.ulcs));
universe_reconciliation[y].weighted_full_universe = r4(weightedBelow90(src));
universe_reconciliation[y].weighted_directory_only = r4(weightedBelow90(dirOnly));
universe_reconciliation[y].effect_of_including_source_only = r4(weightedBelow90(src) - weightedBelow90(dirOnly));
// Common-universe weighted comparison (exact numerators vs loaded values).
const common = src.filter((c) => dbSet.has(c.ulcs));
const dbCommon = db.filter((r) => srcSet.has(r.school_ulcs));
const srcWeighted = weightedBelow90(common);
const dbTotN = dbCommon.reduce((a, r) => a + r.denominator, 0);
const dbWeighted =
dbCommon.reduce((a, r) => a + r.chronic * r.denominator, 0) / dbTotN;
const push = (stat: string, dbV: number, srcV: number, tol: number) =>
crosschecks.push({
stat,
from_db: r4(dbV),
from_source: r4(srcV),
diff: r4(dbV - srcV),
match: Math.abs(dbV - srcV) <= tol,
});
push(`common_weighted_${y}`, dbWeighted, srcWeighted, 0.01);
push(`common_n_schools_${y}`, dbCommon.length, common.length, 0);
push(
`common_n_students_${y}`,
dbTotN,
common.reduce((a, c) => a + c.denom, 0),
0,
);
}
const nSourceOnly = Object.values(universe_reconciliation).reduce(
(a, u) => a + u.source_only.length,
0,
);
const nDbOnly = Object.values(universe_reconciliation).reduce(
(a, u) => a + u.db_only.length,
0,
);
// 11 — Cross-sector series on the STATE's measure (Future Ready PA
// "Regular Attendance" / PercentPersistentAttendance: students enrolled
// 90+ school days who attended 90%+ of them, PDE).
// YEARS ARE ATTENDANCE YEARS: the state's element is a lagging indicator
// (PDE glossary — "data is from the year prior to the reporting year"), and
// the loaders store it under workbook year − 1. So the 2024-25 workbook
// supplies 2023-24 here, and the latest state observation is 2023-24.
// The cross-publisher check (docs/qa_reports/philly/
// __cross_publisher_attendance.json), run on aligned years, finds the state
// figure a stable ~1.7 points higher than the district's (its 90-day
// enrollment rule drops short-enrollment students) — NOT spliceable under
// the pre-committed threshold, so this block is a SEPARATE series: both
// sectors on the same publisher, never mixed with the SDP-based series
// above. 100 − regular attendance = below-90 share, same proxy semantics.
//
// Weighting: Future Ready publishes no student counts for this measure, and
// enrollment is loaded for every sector only for 2024-25, so "weighted"
// here uses each school's 2024-25 enrollment as a FIXED weight across all
// years (documented assumption); the unweighted school mean and median are
// carried alongside.
// Attendance years 2017-18 and 2018-19 (from the 2018-19 and 2019-20
// workbooks) are pre-pandemic; 2019-20 (from the 2020-21 workbook) was cut
// short by the March 2020 building closures; 2020-21 (from the 2021-22
// workbook) was mostly virtual/hybrid. Both pandemic years are shown but
// excluded from trend statements. Workbook→attendance-year mapping is in
// scripts/loaders/philly/_lib.ts (STATE_METRIC_YEAR_OFFSET).
const SECTOR_YEARS = ["2017-18", "2018-19", "2019-20", "2020-21", "2021-22", "2022-23", "2023-24"] as const;
const STATE_LATEST = "2023-24";
const STATE_PANDEMIC_YEARS = ["2019-20", "2020-21"];
const SECTORS = ["REGULAR", "CHARTER"] as const;
const SECTOR_SUBGROUPS = ["ECON_DISADV", "IEP", "ELL", "BLACK", "WHITE"] as const;
const frRows = (
await pool.query<{
school_ulcs: string;
year: string;
subgroup: string;
value: number;
school_type: string;
school_level: string | null;
}>(
`SELECT m.school_ulcs, m.year, m.subgroup, m.value, s.school_type, s.school_level
FROM philly_school_year_metrics m
JOIN philly_schools s ON s.ulcs_code = m.school_ulcs
WHERE m.metric_key = 'attendance_persistence_rate'
AND NOT m.suppressed AND m.value IS NOT NULL
AND m.year = ANY($1) AND m.subgroup = ANY($2)
AND s.school_type = ANY($3)`,
[SECTOR_YEARS as unknown as string[], ["ALL", ...SECTOR_SUBGROUPS], SECTORS as unknown as string[]],
)
).rows.map((r) => ({ ...r, below90: 100 - Number(r.value) }));
const enrollRows = (
await pool.query<{ school_ulcs: string; value: number }>(
`SELECT school_ulcs, value FROM philly_school_year_metrics
WHERE metric_key = 'enrollment' AND subgroup = 'ALL' AND year = '2024-25'
AND NOT suppressed AND value IS NOT NULL`,
)
).rows;
const enroll2425 = new Map(enrollRows.map((r) => [r.school_ulcs, Number(r.value)]));
function sectorStats(cells: { ulcs: string; v: number }[]) {
if (cells.length === 0) return null;
const vs = cells.map((c) => c.v);
const weighted = cells.filter((c) => enroll2425.has(c.ulcs));
const wN = weighted.reduce((a, c) => a + enroll2425.get(c.ulcs)!, 0);
return {
n_schools: cells.length,
mean_school: r4(mean(vs)),
median_school: r4(median(vs)),
weighted_by_2024_25_enrollment:
weighted.length > 0
? r4(weighted.reduce((a, c) => a + c.v * enroll2425.get(c.ulcs)!, 0) / wN)
: null,
n_schools_with_enrollment_weight: weighted.length,
};
}
const sector_trend = Object.fromEntries(
SECTORS.map((sec) => [
sec,
Object.fromEntries(
SECTOR_YEARS.map((y) => [
y,
sectorStats(
frRows
.filter((r) => r.school_type === sec && r.year === y && r.subgroup === "ALL")
.map((r) => ({ ulcs: r.school_ulcs, v: r.below90 })),
),
]),
),
]),
);
// Fixed panel (sensitivity): schools with an all-students value in EVERY
// state year, so changes in which schools contribute cannot drive the
// pre/post-pandemic comparison. Same fixed 2024-25 weights.
const allYearSchools = new Set(
[...new Set(frRows.filter((r) => r.subgroup === "ALL").map((r) => r.school_ulcs))].filter((u) =>
SECTOR_YEARS.every((y) => frRows.some((r) => r.school_ulcs === u && r.year === y && r.subgroup === "ALL")),
),
);
const sector_fixed_panel = {
note: "Schools with an unsuppressed all-students value in all seven state years; same fixed 2024-25 enrollment weights. A sensitivity check on the changing-coverage series above.",
n_schools: Object.fromEntries(SECTORS.map((sec) => [sec, [...allYearSchools].filter((u) => frRows.some((r) => r.school_ulcs === u && r.school_type === sec)).length])),
trend: Object.fromEntries(
SECTORS.map((sec) => [
sec,
Object.fromEntries(
SECTOR_YEARS.map((y) => [
y,
sectorStats(
frRows
.filter((r) => r.school_type === sec && r.year === y && r.subgroup === "ALL" && allYearSchools.has(r.school_ulcs))
.map((r) => ({ ulcs: r.school_ulcs, v: r.below90 })),
),
]),
),
]),
),
};
// Like-for-like by grade configuration (latest state year), only where
// both sectors have at least 8 schools in the configuration.
const LEVEL_GROUPS: Record<string, string[]> = {
Elementary: ["Elementary"],
"Elementary-Middle": ["Elementary-Middle"],
High: ["High"],
};
const sector_by_level_latest = Object.fromEntries(
Object.entries(LEVEL_GROUPS).map(([label, levels]) => [
label,
Object.fromEntries(
SECTORS.map((sec) => [
sec,
sectorStats(
frRows
.filter(
(r) =>
r.school_type === sec && r.year === STATE_LATEST && r.subgroup === "ALL" &&
r.school_level !== null && levels.includes(r.school_level),
)
.map((r) => ({ ulcs: r.school_ulcs, v: r.below90 })),
),
]),
),
]),
);
// Within-school subgroup gaps (latest state year): at schools reporting
// both the subgroup and all-students figures, gap = subgroup below-90 −
// all-students below-90 (positive = the subgroup is chronically absent more
// often). Because "all students" includes the subgroup, every gap is
// attenuated toward zero, more so for large subgroups.
const sector_subgroup_gaps_latest = Object.fromEntries(
SECTOR_SUBGROUPS.map((sg) => [
sg,
Object.fromEntries(
SECTORS.map((sec) => {
const allBy = new Map(
frRows
.filter((r) => r.school_type === sec && r.year === STATE_LATEST && r.subgroup === "ALL")
.map((r) => [r.school_ulcs, r.below90]),
);
const pairs = frRows
.filter((r) => r.school_type === sec && r.year === STATE_LATEST && r.subgroup === sg)
.filter((r) => allBy.has(r.school_ulcs))
.map((r) => ({ sub: r.below90, all: allBy.get(r.school_ulcs)! }));
if (pairs.length === 0) return [sec, null];
return [
sec,
{
n_schools: pairs.length,
mean_subgroup_below_90: r4(mean(pairs.map((p) => p.sub))),
mean_all_below_90_same_schools: r4(mean(pairs.map((p) => p.all))),
mean_gap: r4(mean(pairs.map((p) => p.sub - p.all))),
median_gap: r4(median(pairs.map((p) => p.sub - p.all))),
},
];
}),
),
]),
);
// The cross-publisher check result, carried for the page to cite.
const xpubPath = path.join(
process.cwd(), "docs", "qa_reports", "philly", "__cross_publisher_attendance.json",
);
const xpub = fs.existsSync(xpubPath)
? (JSON.parse(fs.readFileSync(xpubPath, "utf-8")) as { summary: Record<string, unknown> }).summary
: null;
// Directory facts the page cites about the two sectors (from the 2024-25
// master school list): admission types, Renaissance charters, and which
// schools the like-for-like-by-configuration comparison leaves out.
const dirRows = (
await pool.query<{
school_type: string; admission_type: string | null; is_renaissance: boolean; school_level: string | null; n: number;
}>(
`SELECT school_type::text, admission_type::text, is_renaissance, school_level, COUNT(*)::int AS n
FROM philly_schools WHERE school_type IN ('REGULAR','CHARTER')
GROUP BY 1,2,3,4`,
)
).rows;
const dirFor = (sec: string) => {
const rows = dirRows.filter((r) => r.school_type === sec);
const n = rows.reduce((a, r) => a + r.n, 0);
const sum = (f: (r: (typeof rows)[number]) => boolean) => rows.filter(f).reduce((a, r) => a + r.n, 0);
const servesHs = (lvl: string | null) => !!lvl && /High/.test(lvl);
return {
n_schools: n,
lottery: sum((r) => r.admission_type === "CITYWIDE_LOTTERY"),
catchment: sum((r) => r.admission_type === "CATCHMENT"),
renaissance: sum((r) => r.is_renaissance),
standalone_high: sum((r) => r.school_level === "High"),
serving_high_school_grades: sum((r) => servesHs(r.school_level)),
// Schools that serve high-school grades inside a K-12, 6-12, etc.
// configuration — the ones the stand-alone-high-school comparison omits.
mixed_grade_with_high_school: sum((r) => servesHs(r.school_level) && r.school_level !== "High"),
k8_or_elementary_only: sum((r) => ["Elementary", "Elementary-Middle", "Middle"].includes(r.school_level ?? "")),
// Directory counts vs counts with a state value in the latest year.
standalone_high_with_state_value_latest: new Set(
frRows.filter((r) => r.school_type === sec && r.year === STATE_LATEST && r.subgroup === "ALL" && r.school_level === "High").map((r) => r.school_ulcs),
).size,
};
};
const sector_directory = { REGULAR: dirFor("REGULAR"), CHARTER: dirFor("CHARTER") };
const sector_state_measure = {
measure:
"Per school: 100 − the Future Ready PA Index 'Regular Attendance' figure (PercentPersistentAttendance in the 2021-22+ workbooks), i.e. the share of the school's students enrolled 90 or more school days who attended LESS than 90% of them (PA Dept. of Education). Sector figures are averages of these school rates (fixed 2024-25 enrollment weights, or unweighted mean/median), NOT the share of all students in the sector — the state publishes no student counts for this measure. Separate publisher and enrollment basis from the SDP-based series; do not splice.",
year_semantics:
"Years are ATTENDANCE years. PDE's Future Ready glossary: Regular Attendance is 'a lagging indicator indicating data is from the year prior to the reporting year', so the 2024-25 workbook's value is 2023-24 attendance and is stored under 2023-24 (scripts/loaders/philly/_lib.ts STATE_METRIC_YEAR_OFFSET). The latest state observation is therefore 2023-24, one year behind the district's file. Corrected 2026-09-07 after an external audit.",
latest_year: STATE_LATEST,
universe_note:
"District-run (REGULAR) and charter (CHARTER) schools present in the 2024-25 school directory — the state's rows are joined to schools through that directory's CURRENT state codes, so schools closed before 2024-25 are absent from every year, and so is the earlier history of any school that changed operator or state code (e.g. John B. Stetson and Olney, Renaissance charters until 2022 and district-run since, appear in the 2018-19 workbook under charter codes 133513315-8149 and 126513452-8205 that the current crosswalk cannot join; their pre-2022 years are missing from this series). Sector and school-type classifications are the current ones, carried backward. Historical observations for schools matchable to the current directory, not a year-by-year universe of who ran which school. Contracted/alternative programs (19 schools), cyber charters, and the special-education school are excluded from both sectors. Enrollment rule (PDE glossary): only students enrolled 90 or more school days count, versus the district file's 10-day rule. The state's figure runs a median ~0.8–1.7 points above the district's for the same schools and year; the eligibility difference is a plausible contributor, but its share of that offset has not been measured (the district file has no enrollment-spell detail), and local partial-day coding is another candidate.",
selection_note:
"A charter-vs-district gap is descriptive, not a measure of what charter schools do. Possible mechanisms these aggregate files cannot measure: most charters enroll by citywide lottery (Renaissance charters — former district schools run by charter operators — keep neighborhood catchments); students who leave a charter mid-year may enroll in district-run schools; the state's 90-day rule leaves out any enrollment spell shorter than 90 days, so the most mobile students are only partly represented; and PDE lets each local education agency decide how partial-day absences count. None of these is quantified here, and no Philadelphia study is cited for the attendance of mid-year leavers.",
years: SECTOR_YEARS,
pandemic_years: STATE_PANDEMIC_YEARS,
trend_windows: { pre_pandemic: ["2017-18", "2018-19"], in_person: ["2021-22", "2022-23", "2023-24"] },
year_notes: {
"2017-18": "pre-pandemic; from the 2018-19 workbook (older long format, element 'Percent Regular Attendance')",
"2018-19": "pre-pandemic; from the 2019-20 workbook (older long format). Registered 2026-09-07 — an earlier version of this site wrongly stated PDE never published a 2019-20 datafile",
"2019-20": "PANDEMIC: buildings closed 13 March 2020 and the year finished virtually; from the 2020-21 workbook. Shown hatched, excluded from trend statements",
"2020-21": "PANDEMIC: mostly virtual, hybrid in-person from March 2021 (SDP); attendance recorded under remote rules; from the 2021-22 workbook. Shown hatched, excluded from trend statements",
"2021-22": "first full in-person year; from the 2022-23 workbook. The state's figure tracks the district's for the same schools in every aligned year (cross-publisher check), so the in-person trend window is 2021-22 → 2023-24",
"2023-24": "latest state observation (2024-25 workbook); the district's own file runs a year later, to 2024-25",
},
weighting_note:
"weighted_by_2024_25_enrollment uses each school's 2024-25 enrollment as a fixed weight in every year (the state publishes no student counts for this measure, and enrollment is loaded for all sectors only for 2024-25) — it is an enrollment-weighted average of school rates with FIXED weights, not the share of students in the sector; mean_school/median_school are unweighted and carried alongside so the reader can see all three.",
sector_labels: { REGULAR: "District-run schools", CHARTER: "Charter schools" },
directory: sector_directory,
trend: sector_trend,
fixed_panel: sector_fixed_panel,
by_level_latest: sector_by_level_latest,
subgroup_gaps_latest: sector_subgroup_gaps_latest,
cross_publisher_check: xpub,
};
const packet = {
meta: {
generated: new Date().toISOString(),
database: "served Postgres (DIRECT_URL), philly_* tables",
universe:
`All SDP District-sector schools in the source attendance file with an unsuppressed all-students cell, INCLUDING since-closed schools (${Math.min(...YEARS.map((y) => allCells.get(y)!.length))}-${Math.max(...YEARS.map((y) => allCells.get(y)!.length))} per year). The source file carries District sector only — no charter, cyber-charter, contracted, OR alternative schools. Series needing directory metadata (school level, demographic shares, ADA pairs) are restricted to directory-present schools; universe_reconciliation lists every school-year that restriction drops. n_students figures are sums of school-level student counts (students enrolled 10+ days at the school, per SDP); a student enrolled 10+ days at two schools counts at both, so these are student RECORDS, not distinct students.`,
metric:
"pct_below_90 = 100 − ('% with 90%+ Attendance (Yearly)'); weighted rates computed from the exact published numerators. Chronic-absenteeism PROXY; see /philly/methodology/metrics/chronic-absenteeism",
weighting:
"weighted = student-weighted (Σ(denom−num90)/Σdenom); unweighted = school mean; suppressed cells excluded",
baseline_year: BASELINE,
baseline_caveat:
"2021-22 is the first full in-person year in this file AND the Omicron-affected series maximum; there is no pre-pandemic anchor in the district's school-level file, and changes measured from a series maximum are bounded to be improvements. (Pre-pandemic context exists elsewhere: see external_context and the state series, whose attendance years reach back to 2017-18.)",
covid_year_excluded_from_trends: "2020-21",
covid_year_note:
"2020-21 was mostly virtual, with hybrid in-person instruction from March 2021; SDP used multiple methods to determine presence during virtual instruction (SDP, Student Attendance Patterns in Philadelphia 2017-18 to 2021-22, June 2023). Shown, and excluded from the ANNUAL in-person recovery comparisons (citywide, grade, school-type, subgroup, stability); the monthly records block deliberately reports both all-year and in-person-only comparisons, labeled as such.",
external_context: {
source: "School District of Philadelphia, Office of Research and Evaluation, 'Student Attendance Patterns in Philadelphia, 2017-18 to 2021-22' (June 2023), https://www.philasd.org/research/wp-content/uploads/sites/90/2023/06/Student-Attendance-Patterns-in-Philadelphia-2017-18-to-2021-22-June-2023.pdf",
note: "HAND-TRANSCRIBED from the report's Table A-1 (not recomputed; not covered by the packet QA). District and Alternative schools; 2019-20 runs through March 2020. Cited for pre-pandemic context only.",
table_a1_schools_with_more_than_75pct_at_90plus: { "2017-18": 94, "2018-19": 97, "2019-20": 121, "2020-21": 83, "2021-22": 48 },
table_a1_total_schools: { "2017-18": 240, "2018-19": 241, "2019-20": 241, "2020-21": 241, "2021-22": 241 },
report_statement: "the share of students attending 90%+ of days rose from 2017-18 to 2019-20, then fell by 18 percentage points from 2019-20 to 2021-22 (report text; Figure 1 values are not machine-readable)",
},
gender_series_note:
"Gender restricted to schools reporting BOTH Male and Female: from 2021-22 SDP suppresses one of the two at every school where Non-Binary is suppressed (complementary suppression), including large high schools. The common set changes membership by year; gender_trend_fixed_panel holds it constant as a sensitivity check.",
sources: [
"sdp_attendance_90_school (source CSV — primary)",
"sdp_attendance_90_district_monthly (source CSV)",
"served DB: attendance_rate_above_90 (crosscheck), average_daily_attendance, school directory, pct_* shares (2024-25)",
],
n_crosschecks: crosschecks.length,
n_crosscheck_mismatches: crosschecks.filter((c) => !c.match).length,
crosscheck_tolerances: "weighted rates: 0.01 — the loaded percentages are published to 2 decimals, so each school's rate can differ from its exact-count rate by up to 0.005, and a weighted average of such errors is bounded by 0.005 when the school sets match; 0.01 is that bound plus a small explicit margin. Counts: exact",
max_abs_crosscheck_diff_rate: r4(Math.max(...crosschecks.filter((c) => c.stat.startsWith("common_weighted")).map((c) => Math.abs(c.diff)))),
n_source_only_school_years: nSourceOnly,
n_db_only_school_years: nDbOnly,
corrections_log: "https://findwhat.works/schools/philly/methodology/metrics/chronic-absenteeism#corrections — the authoritative, dated record of every change; the summary below is partial",
revisions:
"v4 (2026-09-08, second–fourth external audits) — sector medians and fixed panels; Black–White level pairs and 97-school panel; monthly records block; grade shape and subgroup peaks; hypothesis wording for the publisher offset; student-count comparisons independent of suppression in the QA; evidence-version identifiers. v3 (2026-09-07, external audit) — state attendance stored under attendance years (lagging indicator), 2019-20 workbook added (attendance year 2018-19), state series 2017-18..2023-24 with pandemic years hatched and no basis-change claim; gender fixed panel; within-school Black-White contrasts; closed-school inclusion effect signed from the data; student-record denominators labeled; SDP 10-day rule and PDE 90-day rule documented. v2 — source-CSV full universe for citywide/grade/distribution/stability/subgroups (closed schools included); exact numerators; Sector=District enforced; gender common-universe; Black-White common-school gap; Sep-Mar monthly aggregate; Spearman for CA-vs-ADA; universe reconciliation replaces the circular crosscheck. v1 — initial.",
},
citywide_trend,
per_grade,
per_grade_shape,
per_grade_change,
school_level_trend,
ca_vs_ada,
subgroup_trend,
subgroup_peak_year,
gender_trend_common,
gender_trend_fixed_panel,
black_white_common,
black_white_fixed_panel,
demographics_correlation,
composition_segments,
distribution,
stability: stabilityWithRatios,
district_monthly,
monthly_records,
crosschecks,
universe_reconciliation,
sector_state_measure,
};
fs.writeFileSync(OUT, JSON.stringify(packet, null, 1) + "\n");
console.log(`[packet] wrote ${path.relative(process.cwd(), OUT)}`);
console.log(
`[packet] crosschecks: ${crosschecks.length}, mismatches: ${crosschecks.filter((c) => !c.match).length}`,
);
console.log(
`[packet] universe: ${nSourceOnly} source-only school-years (closed schools), ${nDbOnly} db-only`,
);
for (const [y, u] of Object.entries(universe_reconciliation)) {
for (const s of u.source_only)
console.log(` [source-only] ${y}: ${s.ulcs} ${s.name} (n=${s.n_students}, below90=${s.below_90})`);
}
await pool.end();
}
main().catch((e) => {
console.error(e);
process.exit(1);
});