scripts/analysis/nyc-capacity-packet.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.
// NYC enrollment, capacity and utilization — analysis packet.
//
// Writes data/analysis/nyc-enrollment-capacity.json, the committed audit trail
// behind docs/design/10_nyc_enrollment_capacity_utilization.md and the page that
// doc plans. Anything that displays these figures reads the packet; it does not
// recompute them.
//
// npx tsx scripts/analysis/nyc-capacity-packet.ts regenerate
// npx tsx scripts/analysis/nyc-capacity-packet.ts --check diff against the
// committed file,
// exit 1 on drift
// npx tsx scripts/analysis/nyc-capacity-packet.ts --as-of=2026-09-09 override the stamp
// npx tsx scripts/analysis/nyc-capacity-packet.ts --refresh re-download the
// cached spreadsheet
//
// No database. This script reads six published files plus the committed parse of
// the SCA's PDFs (data/cities/nyc/reference/parsed/), and runs with no
// DATABASE_URL set. The Blue Book parsing lives in
// scripts/loaders/_bluebook.ts and is reused here rather than restated — the
// per-building roll-up in particular (doc §3.2: a school split across buildings
// gets one row per building, so reading any single row misstates the school).
//
// Four conventions, each of which moves the numbers:
//
// 1. UNIVERSE. School-grain figures cover organizations whose org code resolves
// to exactly one DBN in our school universe — the 2019-20 School Locations
// directory plus every year of the DOE Demographic Snapshot across its three
// publication vintages. That is 99.6-99.7% of organizations carrying an
// enrollment. The six or seven dropped each year are named in
// `universe.by_year[].dropped`, and `universe.sensitivity_all_orgs` repeats
// the headline statistics without the filter: measured, it moves the share
// under 50% by at most 0.1 pp and the median not at all.
//
// 2. QUANTILES. Percentiles use the exclusive definition that Python's
// statistics.quantiles applies by default, which is what produced the
// percentile table in doc §4.1. The inclusive definition used elsewhere in
// this repo can differ from it after rounding at p90, so the two are not
// interchangeable.
//
// 3. BUILDING GRAIN. A building's enrollment and capacity repeat on every
// organization row it hosts, so the figures are deduped by building id.
// `rollUpBuildings` keeps the FIRST row it sees for a building, and in
// 2013-14 and 2016-17 that row is sometimes an annex-style row carrying no
// capacity — 13 and 10 buildings. Rows with complete building figures are
// therefore handed to it first. Where two rows for one building are both
// complete they never disagree; `qa.building_row_consistency` checks that.
//
// 4. IRREGULAR PANEL. Twelve school years, 2013-14 to 2025-26, with one gap:
// 2020-21, which the SCA never published. Seven come from Socrata; the
// other five are read from the SCA's own PDFs (pipeline_nyc/parse/bluebook.py)
// and reach this script through the same gate the loader checks, so the
// packet and the warehouse see one panel. `meta.missing_years` carries the
// gap, so a time-series figure breaks its line at 2020-21.
//
// Direction is NEUTRAL throughout. Utilization is how full a building is, not a
// rating of the school inside it, and target capacity is the SCA's planning
// construct rather than a count of how many children fit.
import fs from "node:fs";
import path from "node:path";
import { fetchSocrataAll, fetchAndCache, readSheet } from "../loaders/_fetch";
import { readRecoveredYears } from "../loaders/_bluebook_recovered";
import {
SNAPSHOT_YEAR,
buildDbnIndex,
groupBySnapshot,
num,
resolveDbn,
rollUpBuildings,
rollUpOrgs,
schoolYearForSnapshot,
utilizationAgreement,
type BlueBookRow,
type BuildingRecord,
type OrgRecord,
} from "../loaders/_bluebook";
const OUT = path.join(process.cwd(), "data", "analysis", "nyc-enrollment-capacity.json");
// ── sources ──────────────────────────────────────────────────────────────────
const BLUEBOOK = "gkd7-3vk7"; // live: enrollment, target capacity, published utilization
const BLUEBOOK_HIST_ORG = "q9xk-w9iv"; // retired 2015: the pre-2014 capacity formula
const DIRECTORY = "wg9x-4ke6"; // 2019-20 School Locations — the DBN seed
const DEMO_2013_2018 = "s52a-8aq6";
const DEMO_2018_2022 = "c7ru-d68s";
const DEMO_XLSX_URL =
"https://infohub.nyced.org/docs/default-source/default-document-library/" +
"demographic-snapshot-2021-22-to-2025-26-public.xlsx";
const socrataUrl = (id: string) => `https://data.cityofnewyork.us/resource/${id}.json`;
const DECOMP_FROM = "2018-19";
const DECOMP_TO = "2025-26";
// The school years strictly between the two endpoints that the panel lacks.
// Only 2020-21 is missing, because the SCA never published it (doc §2), so the
// span can be run segment by segment as well as end to end. `segments` splits
// each consecutive published pair, which is what lets the page say whether the
// endpoint reading hides a reversal instead of merely noting that it might.
const DECOMP_SPAN_UNPUBLISHED = ["2020-21"];
const DISTRICT_YEAR = "2025-26";
const HISTORICAL_YEAR = "2013-14"; // the only year the retired capacity series covers
// ── flags ────────────────────────────────────────────────────────────────────
const argv = process.argv.slice(2);
function flagValue(name: string): string | null {
const prefix = `--${name}=`;
const inline = argv.find((a) => a.startsWith(prefix));
if (inline) return inline.slice(prefix.length);
const i = argv.indexOf(`--${name}`);
if (i >= 0 && i + 1 < argv.length && !argv[i + 1].startsWith("--")) return argv[i + 1];
return null;
}
const CHECK = argv.includes("--check");
// --check re-downloads the cached spreadsheet: a stale cache would hide an
// upstream restatement, which is the one thing --check exists to catch.
const REFRESH = argv.includes("--refresh") || CHECK;
const AS_OF_OVERRIDE = flagValue("as-of") ?? process.env.NYC_PACKET_AS_OF ?? null;
// ── statistics ───────────────────────────────────────────────────────────────
/**
* Round half to even, which is what the published tables in the doc use —
* they came out of Python, whose formatting rounds ties to the even digit,
* while JavaScript's `Math.round` rounds them up.
*
* The difference is confined to exact ties, and exact ties are ordinary here
* because a share is a small ratio of counts: 3 of District 20's 48 schools is
* exactly 6.25%, which reads 6.2 under one convention and 6.3 under the other.
* Half-even is also the unbiased choice — half-up drags every tie in one
* direction — so the packet uses it and the doc's numbers reproduce.
*/
function roundTo(x: number, digits: number): number {
const f = 10 ** digits;
const scaled = x * f;
const low = Math.floor(scaled);
const frac = scaled - low;
const n = frac > 0.5 ? low + 1 : frac < 0.5 ? low : low % 2 === 0 ? low : low + 1;
return n / f;
}
const r1 = (n: number) => roundTo(n, 1);
const r3 = (n: number) => roundTo(n, 3);
const sum = (xs: number[]) => xs.reduce((a, b) => a + b, 0);
/**
* Exclusive quantile — the default of Python's `statistics.quantiles`, and the
* definition behind the published percentile table (doc §4.1). Position is
* p × (n + 1) in 1-based terms, the bracketing index is clamped into [1, n-1],
* and the value is interpolated linearly between the two order statistics.
*/
function quantileExclusive(sorted: readonly number[], p: number): number {
const n = sorted.length;
if (n === 0) return NaN;
if (n === 1) return sorted[0];
const pos = p * (n + 1);
const j = Math.min(Math.max(Math.floor(pos), 1), n - 1);
const frac = pos - j;
return sorted[j - 1] + frac * (sorted[j] - sorted[j - 1]);
}
type Distribution = {
n: number;
p10: number | null;
p25: number | null;
median: number | null;
p75: number | null;
p90: number | null;
share_under_50: number | null;
share_under_33: number | null;
share_over_100: number | null;
sum_over_sum: number | null;
enrollment: number;
capacity: number;
};
/**
* The distribution block every grain reports. `utilizations` are integer
* percents as the source states them (published at building grain, summed then
* divided at school grain); `enrollment`/`capacity` are the totals behind
* `sum_over_sum`, which is a student-weighted figure and not the median.
*
* Thresholds are strict and one-sided, matching the doc: under 50, under 33.3
* (a third), over 100.
*/
function distribution(utilizations: number[], enrollment: number, capacity: number): Distribution {
const s = [...utilizations].sort((a, b) => a - b);
const n = s.length;
const share = (pred: (x: number) => boolean) => (n ? r1((100 * s.filter(pred).length) / n) : null);
return {
n,
p10: n ? r1(quantileExclusive(s, 0.1)) : null,
p25: n ? r1(quantileExclusive(s, 0.25)) : null,
median: n ? r1(quantileExclusive(s, 0.5)) : null,
p75: n ? r1(quantileExclusive(s, 0.75)) : null,
p90: n ? r1(quantileExclusive(s, 0.9)) : null,
share_under_50: share((x) => x < 50),
share_under_33: share((x) => x < 33.3),
share_over_100: share((x) => x > 100),
sum_over_sum: capacity > 0 ? r1((100 * enrollment) / capacity) : null,
enrollment,
capacity,
};
}
// ── shapes the packet is assembled from ──────────────────────────────────────
/** A building with complete figures: enrollment, capacity and a published utilization. */
type CompleteBuilding = { id: string; enrollment: number; capacity: number; utilization: number };
/**
* Buildings with complete figures, from `rollUpBuildings`.
*
* `rollUpBuildings` keeps the first row it encounters for a building id, so the
* rows are ordered complete-first: otherwise an annex-style row carrying no
* capacity wins the id and the building drops out of the grain entirely (13
* buildings in 2013-14, 10 in 2016-17 — measured, see the header).
*/
function completeBuildings(rows: readonly BlueBookRow[]): {
records: Map<string, BuildingRecord>;
complete: CompleteBuilding[];
} {
const isComplete = (r: BlueBookRow) => {
const e = num(r.bldg_enroll);
const c = num(r.target_bldg_cap);
return e !== null && e > 0 && c !== null && c > 0;
};
const ordered = [...rows.filter(isComplete), ...rows.filter((r) => !isComplete(r))];
const records = rollUpBuildings(ordered);
const complete: CompleteBuilding[] = [];
for (const b of records.values()) {
if (b.enrollment === null || b.enrollment <= 0) continue;
if (b.targetCapacity === null || b.targetCapacity <= 0) continue;
if (b.utilizationPct === null) continue;
complete.push({
id: b.buildingId,
enrollment: b.enrollment,
capacity: b.targetCapacity,
utilization: b.utilizationPct,
});
}
complete.sort((a, b) => (a.id < b.id ? -1 : a.id > b.id ? 1 : 0));
return { records, complete };
}
/**
* Buildings hosting more than one organization with students in them.
*
* Counted from the raw rows rather than `BuildingRecord.orgCodes`, which admits
* an organization whose enrollment in the building is zero. The two definitions
* differ by 0.5-0.6 pp; `qa.multi_org_definition` carries both so the choice is
* visible rather than implied.
*/
function multiOrgBuildings(rows: readonly BlueBookRow[], ids: ReadonlySet<string>): number {
const occupants = new Map<string, Set<string>>();
for (const r of rows) {
const b = (r.bldg_id ?? "").trim();
const org = (r.org_id ?? "").trim().toUpperCase();
const e = num(r.org_enroll);
if (!b || !org || e === null || e <= 0 || !ids.has(b)) continue;
const bucket = occupants.get(b);
if (bucket) bucket.add(org);
else occupants.set(b, new Set([org]));
}
let multi = 0;
for (const set of occupants.values()) if (set.size > 1) multi += 1;
return multi;
}
/**
* Does this organization describe how full a building is?
*
* Students and seats are both required, and the students strictly. `rollUpOrgs`
* reports an organization that reported no enrollment against a real capacity as
* 0% utilized, which is an organization that did not report rather than an empty
* school; admitting them adds 20 schools at 0% to 2013-14 and moves the share
* under 50% by a point.
*/
function hasUtilization(o: OrgRecord | undefined): boolean {
return (
!!o &&
o.enrollment !== null &&
o.enrollment > 0 &&
o.targetCapacity !== null &&
o.targetCapacity > 0 &&
o.utilizationPct !== null
);
}
/** Organizations carrying students and seats — the school-grain population before the DBN filter. */
function orgsWithUtilization(orgs: Iterable<OrgRecord>): OrgRecord[] {
return [...orgs].filter(hasUtilization);
}
function schoolDistribution(orgs: readonly OrgRecord[]): Distribution {
return distribution(
orgs.map((o) => o.utilizationPct as number),
sum(orgs.map((o) => o.enrollment as number)),
sum(orgs.map((o) => o.targetCapacity as number)),
);
}
// ── the DBN universe the school grain is filtered to ─────────────────────────
type DemoSocrataRow = { dbn?: string; year?: string; total_enrollment?: string };
type DirectoryRow = { system_code?: string };
type DemoXlsxRow = { DBN?: unknown; Year?: unknown; "Total Enrollment"?: unknown };
async function loadDbnUniverse(): Promise<{
dbns: Set<string>;
parts: { source: string; dbns: number }[];
}> {
const parts: { source: string; dbns: number }[] = [];
const dbns = new Set<string>();
const add = (raw: unknown) => {
const d = String(raw ?? "").trim().toUpperCase();
if (d.length >= 3) dbns.add(d);
};
const directory = await fetchSocrataAll<DirectoryRow>(DIRECTORY);
const before0 = dbns.size;
for (const r of directory) if (r.system_code) add(r.system_code);
parts.push({ source: `socrata:${DIRECTORY}`, dbns: dbns.size - before0 });
for (const id of [DEMO_2013_2018, DEMO_2018_2022]) {
const rows = await fetchSocrataAll<DemoSocrataRow>(id);
const before = dbns.size;
// An enrollment is required, matching the probe: a row without one is not
// evidence that the school reported in that year.
for (const r of rows) if (r.dbn && r.year && num(r.total_enrollment) !== null) add(r.dbn);
parts.push({ source: `socrata:${id}`, dbns: dbns.size - before });
}
const xlsxPath = await fetchAndCache(DEMO_XLSX_URL, REFRESH);
const sheet = readSheet<DemoXlsxRow>(xlsxPath, "School");
const before = dbns.size;
for (const r of sheet) {
if (r.DBN && r.Year && num(r["Total Enrollment"]) !== null) add(r.DBN);
}
parts.push({ source: "infohub:demographic-snapshot-2021-22-to-2025-26", dbns: dbns.size - before });
if (dbns.size < 2000) {
throw new Error(
`DBN universe came back with only ${dbns.size} schools, which is far short of the ~2,236 ` +
`measured in doc §3.1. One of the four inputs returned less than it should — regenerating ` +
`on a short universe would silently restate every school-grain figure in this packet.`,
);
}
return { dbns, parts };
}
// ── diffing, for --check ─────────────────────────────────────────────────────
const isRecord = (v: unknown): v is Record<string, unknown> =>
typeof v === "object" && v !== null && !Array.isArray(v);
function diffPaths(committed: unknown, fresh: unknown, at = "$", out: string[] = []): string[] {
if (out.length >= 40) return out;
if (isRecord(committed) && isRecord(fresh)) {
for (const k of new Set([...Object.keys(committed), ...Object.keys(fresh)])) {
if (!(k in committed)) out.push(`${at}.${k}: absent from the committed file`);
else if (!(k in fresh)) out.push(`${at}.${k}: no longer produced`);
else diffPaths(committed[k], fresh[k], `${at}.${k}`, out);
}
return out;
}
if (Array.isArray(committed) && Array.isArray(fresh)) {
if (committed.length !== fresh.length) {
out.push(`${at}: length ${committed.length} → ${fresh.length}`);
return out;
}
for (let i = 0; i < committed.length; i++) diffPaths(committed[i], fresh[i], `${at}[${i}]`, out);
return out;
}
if (committed !== fresh) {
out.push(`${at}: committed ${JSON.stringify(committed)} → regenerated ${JSON.stringify(fresh)}`);
}
return out;
}
// ── build ────────────────────────────────────────────────────────────────────
async function build() {
// Socrata carries seven years; the other five come from the committed parse
// of the SCA's own PDFs, through the same gate the loader checks. Both must
// see one panel — a year in the warehouse and not in the packet is a figure
// the page and the database disagree about.
const recovered = readRecoveredYears();
const socrataRows = await fetchSocrataAll<BlueBookRow>(BLUEBOOK);
const bb = [...socrataRows, ...recovered.flatMap((r) => r.rows)];
const hist = await fetchSocrataAll<Record<string, string>>(BLUEBOOK_HIST_ORG);
const { dbns, parts } = await loadDbnUniverse();
const dbnIndex = buildDbnIndex(dbns);
const bySnapshot = groupBySnapshot(bb);
// schoolYearForSnapshot throws on an unmapped vintage rather than guessing
// from the posting date; the lag runs three to eighteen months (doc §2).
const snapshots = [...bySnapshot.keys()].sort();
const yearOf = new Map(snapshots.map((s) => [s, schoolYearForSnapshot(s)]));
const years = [...yearOf.values()].sort();
const snapshotOf = new Map([...yearOf].map(([s, y]) => [y, s]));
const maxDataAsOf = bb
.map((r) => (r.data_as_of ?? "").trim())
.filter(Boolean)
.sort()
.at(-1);
const generated_at = AS_OF_OVERRIDE ?? maxDataAsOf ?? null;
// ── by_year ───────────────────────────────────────────────────────────────
const by_year: Record<string, unknown> = {};
const universe_by_year: Record<string, unknown> = {};
const sensitivity_all_orgs: Record<string, unknown> = {};
const utilization_agreement: Record<string, unknown> = {};
const matchedByYear = new Map<string, OrgRecord[]>();
const completeByYear = new Map<string, CompleteBuilding[]>();
let inconsistentBuildingRows = 0;
const multiOrgAlternative: Record<string, number> = {};
for (const year of years) {
const snapshot = snapshotOf.get(year) as string;
const rows = bySnapshot.get(snapshot) as BlueBookRow[];
const orgs = rollUpOrgs(rows);
// School grain. Filter to organizations whose org code resolves to exactly
// one DBN; an ambiguous code is dropped rather than assigned (doc §3.1).
const withUtil = orgsWithUtilization(orgs.values());
const matched: OrgRecord[] = [];
const dropped: { org_code: string; name: string | null; reason: string }[] = [];
for (const o of withUtil) {
const res = resolveDbn(o.orgCode, dbnIndex);
if (res.kind === "resolved") matched.push(o);
else dropped.push({ org_code: o.orgCode, name: o.name, reason: res.kind });
}
dropped.sort((a, b) => (a.org_code < b.org_code ? -1 : 1));
matchedByYear.set(year, matched);
// Building grain.
const { records, complete } = completeBuildings(rows);
completeByYear.set(year, complete);
const ids = new Set(complete.map((b) => b.id));
const multi = multiOrgBuildings(rows, ids);
multiOrgAlternative[year] = [...records.values()].filter(
(b) => ids.has(b.buildingId) && b.orgCodes.length > 1,
).length;
// Do two complete rows for the same building ever disagree? (They do not.)
const seen = new Map<string, string>();
for (const r of rows) {
const id = (r.bldg_id ?? "").trim();
const e = num(r.bldg_enroll);
const c = num(r.target_bldg_cap);
const u = num(r.target_bldg_util);
if (!id || e === null || e <= 0 || c === null || c <= 0) continue;
const key = `${e}|${c}|${u}`;
const prior = seen.get(id);
if (prior === undefined) seen.set(id, key);
else if (prior !== key) inconsistentBuildingRows += 1;
}
by_year[year] = {
snapshot,
school: schoolDistribution(matched),
building: {
...distribution(
complete.map((b) => b.utilization),
sum(complete.map((b) => b.enrollment)),
sum(complete.map((b) => b.capacity)),
),
multi_org_buildings: multi,
multi_org_share: complete.length ? r1((100 * multi) / complete.length) : null,
},
};
const orgsTotal = orgs.size;
universe_by_year[year] = {
organizations: orgsTotal,
with_enrollment_and_capacity: withUtil.length,
matched: matched.length,
match_rate: withUtil.length ? r1((100 * matched.length) / withUtil.length) : null,
dropped,
};
sensitivity_all_orgs[year] = schoolDistribution(withUtil);
utilization_agreement[year] = utilizationAgreement(orgs.values());
}
// ── decomposition ─────────────────────────────────────────────────────────
// Continuing buildings only, so seats added in NEW buildings sit outside it.
// Additive split of the change in Σenrollment / Σcapacity: the enrollment
// effect holds capacity at the first year, the capacity effect is the
// remainder. Building grain, because a building is what gets emptied or
// closed; a school moving between buildings is not a change in seats.
// One span, run on the buildings complete in BOTH of its endpoint years. The
// continuing set is a function of the pair, so a sub-span is not a restriction
// of the whole-span set and the segments below do not have to sum to it.
function splitSpan(from: string, to: string) {
const a = new Map((completeByYear.get(from) ?? []).map((b) => [b.id, b]));
const z = new Map((completeByYear.get(to) ?? []).map((b) => [b.id, b]));
const ids = [...a.keys()].filter((id) => z.has(id)).sort();
const e0 = sum(ids.map((id) => (a.get(id) as CompleteBuilding).enrollment));
const e1 = sum(ids.map((id) => (z.get(id) as CompleteBuilding).enrollment));
const c0 = sum(ids.map((id) => (a.get(id) as CompleteBuilding).capacity));
const c1 = sum(ids.map((id) => (z.get(id) as CompleteBuilding).capacity));
const u0 = (100 * e0) / c0;
const u1 = (100 * e1) / c1;
const counterfactual = (100 * e1) / c0; // later students in earlier seats
return {
from,
to,
ids,
fromSize: a.size,
toSize: z.size,
e0,
e1,
c0,
c1,
u0,
u1,
counterfactual,
};
}
const whole = splitSpan(DECOMP_FROM, DECOMP_TO);
const { ids: continuing, e0, e1, c0, c1, u0, u1, counterfactual } = whole;
const fromB = { size: whole.fromSize };
const toB = { size: whole.toSize };
// The same split on every consecutive published pair across the span, so the
// endpoint reading can be checked rather than trusted.
const spanYears = years.filter((y) => y >= DECOMP_FROM && y <= DECOMP_TO);
const segments = spanYears
.slice(0, -1)
.map((y, i) => splitSpan(y, spanYears[i + 1]))
.map((g) => ({
from: g.from,
to: g.to,
continuing_buildings: g.ids.length,
utilization_from_pct: r1(g.u0),
utilization_to_pct: r1(g.u1),
utilization_change_pp: r1(g.u1 - g.u0),
enrollment_effect_pp: r1(g.counterfactual - g.u0),
capacity_effect_pp: r1(g.u1 - g.counterfactual),
enrollment_change_pct: r1(100 * (g.e1 / g.e0 - 1)),
capacity_change_pct: r1(100 * (g.c1 / g.c0 - 1)),
}));
const decomposition = {
from: DECOMP_FROM,
to: DECOMP_TO,
grain: "building",
continuing_buildings: continuing.length,
buildings_from: fromB.size,
buildings_to: toB.size,
enrollment_from: e0,
enrollment_to: e1,
enrollment_change_pct: r1(100 * (e1 / e0 - 1)),
capacity_from: c0,
capacity_to: c1,
capacity_change_pct: r1(100 * (c1 / c0 - 1)),
utilization_from_pct: r1(u0),
utilization_to_pct: r1(u1),
utilization_change_pp: r1(u1 - u0),
enrollment_effect_pp: r1(counterfactual - u0),
capacity_effect_pp: r1(u1 - counterfactual),
span_years: 7,
span_published_between: spanYears.slice(1, -1),
span_unpublished: DECOMP_SPAN_UNPUBLISHED,
span_note:
`${DECOMP_FROM} and ${DECOMP_TO} are 7 school years apart and the panel carries ` +
`${spanYears.length - 2} of the 6 years in between — every one except ` +
`${DECOMP_SPAN_UNPUBLISHED.join(", ")}, which the SCA never published. Four of those ` +
"intermediate years are not on NYC Open Data and are read from the SCA's own PDFs " +
"(pipeline_nyc/parse/bluebook.py). `segments` is the path between the endpoints, so the " +
"endpoint figures can be read against it.",
segments,
segments_note:
"Each segment is computed on the buildings complete in both of ITS endpoint years, so the " +
"continuing sets differ from segment to segment and the segment effects are not required to " +
"sum to the whole-span effects. The 2019-20 → 2021-22 segment spans the unpublished " +
"2020-21 and is two years wide; every other segment is one.",
note:
"Capacity fell over the period, which raised utilization; the decline is enrollment. " +
"Continuing buildings only, so seats in buildings opened after 2018-19 are outside this.",
rounding_note:
"The two effects are additive before rounding (unrounded: -14.240 and +2.485 against a total " +
"of -11.755). To one decimal they read -14.2 and +2.5, which sum to -11.7 against a printed " +
"total of -11.8; the 0.1 pp is display rounding, not a residual term.",
};
// ── by_district ───────────────────────────────────────────────────────────
// geo_dist is the GEOGRAPHIC district of the building, which is not the
// administrative district for D75, D79 or charters (doc §3.1). These are
// therefore districts of place, not of governance.
const districtOrgs = new Map<string, OrgRecord[]>();
let unassigned = 0;
for (const o of matchedByYear.get(DISTRICT_YEAR) ?? []) {
if (o.geoDistrict === null) {
unassigned += 1;
continue;
}
const key = String(o.geoDistrict);
const bucket = districtOrgs.get(key);
if (bucket) bucket.push(o);
else districtOrgs.set(key, [o]);
}
const districts: Record<string, { schools: number; utilization_pct: number; share_under_50: number }> = {};
for (const key of [...districtOrgs.keys()].sort((a, b) => Number(a) - Number(b))) {
const os = districtOrgs.get(key) as OrgRecord[];
const e = sum(os.map((o) => o.enrollment as number));
const c = sum(os.map((o) => o.targetCapacity as number));
districts[key] = {
schools: os.length,
utilization_pct: r1((100 * e) / c),
share_under_50: r1((100 * os.filter((o) => (o.utilizationPct as number) < 50).length) / os.length),
};
}
const by_district = {
year: DISTRICT_YEAR,
grain: "school",
statistic: "Σenrollment / Σcapacity within the district, and the share of its schools under 50%",
schools_without_a_district: unassigned,
districts,
ranked_by_utilization: Object.keys(districts).sort(
(a, b) => districts[a].utilization_pct - districts[b].utilization_pct,
),
};
// ── two capacity definitions ──────────────────────────────────────────────
// The retired series is the pre-2014 formula on the same schools and year, so
// the comparison isolates the denominator. Its columns are named differently;
// they are mapped onto the live shape so the same roll-up applies.
const histRows: BlueBookRow[] = hist.map((r) => ({
org_id: r.org_id,
organization_name: r.organization_name,
bldg_id: r.bldg_id,
geo_dist: r.geo_dist,
org_enroll: r.enroll,
org_target_cap: r.historical_capacity,
org_target_util: r.historical_utilization,
}));
const historical = rollUpOrgs(histRows);
const target = rollUpOrgs(bySnapshot.get(snapshotOf.get(HISTORICAL_YEAR) as string) as BlueBookRow[]);
const bothBases = [...target.keys()]
.filter((code) => {
const h = historical.get(code);
const t = target.get(code);
return !!h && !!t && h.targetCapacity !== null && t.targetCapacity !== null;
})
.sort();
const ratios = bothBases.map(
(code) =>
(target.get(code) as OrgRecord).targetCapacity! / (historical.get(code) as OrgRecord).targetCapacity!,
);
// The ratio needs a capacity on both bases; the utilization comparison needs
// students as well, on both bases, for the reason in `hasUtilization`.
const bothUtil = bothBases.filter(
(code) => hasUtilization(target.get(code)) && hasUtilization(historical.get(code)),
);
const gaps = bothUtil.map(
(code) =>
(target.get(code) as OrgRecord).utilizationPct! - (historical.get(code) as OrgRecord).utilizationPct!,
);
const sortedRatios = [...ratios].sort((a, b) => a - b);
const sortedGaps = [...gaps].sort((a, b) => a - b);
const capacity_definitions = {
year: HISTORICAL_YEAR,
schools_on_both_bases: bothBases.length,
schools_with_both_utilizations: bothUtil.length,
// Each statistic carries its OWN n. The two universes differ — a school can
// have a capacity on both bases but a utilization on only one (it needs an
// enrollment too). A bare pair of counts beside four unlabelled statistics
// makes it easy to attach a statistic to the wrong universe, so the label
// travels with the number instead.
ratio_target_over_historical: {
n: bothBases.length,
median: r3(quantileExclusive(sortedRatios, 0.5)),
p10: r3(quantileExclusive(sortedRatios, 0.1)),
p90: r3(quantileExclusive(sortedRatios, 0.9)),
},
utilization_gap_pp: {
n: bothUtil.length,
median: r1(quantileExclusive(sortedGaps, 0.5)),
p10: r1(quantileExclusive(sortedGaps, 0.1)),
p90: r1(quantileExclusive(sortedGaps, 0.9)),
},
share_under_50_n: bothUtil.length,
share_under_50_target: r1(
(100 * bothUtil.filter((c) => (target.get(c) as OrgRecord).utilizationPct! < 50).length) /
bothUtil.length,
),
share_under_50_historical: r1(
(100 * bothUtil.filter((c) => (historical.get(c) as OrgRecord).utilizationPct! < 50).length) /
bothUtil.length,
),
note:
"Same schools, same year, two capacity formulas. The capacity RATIO is over the " +
"schools carrying both capacity figures; the utilization gap and both under-50% " +
"shares are over the smaller set that also carries a utilization on each basis, " +
"which needs an enrollment as well. A figure naming no denominator is therefore " +
"ambiguous.",
};
// ── qa ────────────────────────────────────────────────────────────────────
// Check (iv) of the validation spec: does the published utilization equal the
// reconstruction. For a single-building organization the comparison is
// unambiguous; a split-site organization publishes a figure per building row,
// so "matches any of them" is the weaker test and is reported apart.
const singleBuildingWorst = Math.min(
...years.map((y) => {
const t = utilization_agreement[y] as ReturnType<typeof utilizationAgreement>;
return t.singleBuilding.n ? (100 * t.singleBuilding.within1pp) / t.singleBuilding.n : 0;
}),
);
const worstMatchRate = Math.min(
...years.map((y) => (universe_by_year[y] as { match_rate: number }).match_rate),
);
// Additivity is a property of the unrounded effects. Checking the rounded
// fields instead would report the 0.1 pp of display rounding as a defect.
const additivity = Math.abs((counterfactual - u0) + (u1 - counterfactual) - (u1 - u0));
const checks = [
{
check: "iv_published_utilization_reconstructed",
pass: singleBuildingWorst >= 100,
detail: `single-building organizations within 1 pp of the published figure, worst year ${r1(singleBuildingWorst)}%`,
},
{
check: "panel_years_mapped",
pass: years.length === Object.keys(SNAPSHOT_YEAR).length
&& snapshots.length === Object.keys(SNAPSHOT_YEAR).length,
detail: `${snapshots.length} snapshots, ${years.length} school years; an unmapped vintage throws rather than guessing`,
},
{
check: "join_match_rate",
pass: worstMatchRate >= 99.5,
detail: `org code → DBN, worst year ${r1(worstMatchRate)}% of organizations carrying students and seats`,
},
{
check: "building_row_consistency",
pass: inconsistentBuildingRows === 0,
detail: `${inconsistentBuildingRows} buildings where two complete rows disagree on enrollment, capacity or utilization`,
},
{
check: "decomposition_additive",
pass: additivity <= 1e-9,
detail: `unrounded enrollment and capacity effects sum to the total change within ${additivity.toExponential(1)} pp`,
},
];
const qa = {
verdict: checks.every((c) => c.pass) ? "PASS" : "FAIL",
checks,
utilization_agreement,
multi_org_definition: {
reported: "organizations with more than 0 students in the building",
alternative: "organizations with a non-null enrollment in the building, zero included",
alternative_counts: multiOrgAlternative,
},
not_verified: [
"No second publisher states NYC school capacity; doc §5.2 tests it against the room inventory, which is the same publisher.",
"2020-21 cannot be checked because the SCA never published it (doc §2).",
"The five years read from the SCA PDFs are checked against Socrata only by proxy: run on the 2025-26 PDF, the parser reproduces the published 2025-26 extract on every row key and every organization-grain enrollment, target capacity and utilization (doc §2.1). The report prints no zero in the PK-capacity and cluster-room columns, so on about 2% of rows those read absent where Socrata reads 0.",
"There is no public closure list for NYC, so nothing here supports a predictive claim (doc §6).",
],
};
// ── packet ────────────────────────────────────────────────────────────────
const packet = {
meta: {
generated_at,
generated_at_basis: AS_OF_OVERRIDE
? "--as-of / NYC_PACKET_AS_OF override"
: `max data_as_of in ${BLUEBOOK}; a wall clock would churn the committed diff`,
script: "scripts/analysis/nyc-capacity-packet.ts",
years,
missing_years: ["2020-21"],
recovered_years: recovered.map((r) => ({
year: r.schoolYear,
snapshot: r.snapshot,
rows: r.rows.length,
source: "SCA PDF, parsed by pipeline_nyc/parse/bluebook.py under a two-parser gate",
})),
panel_note:
"Twelve school years, 2013-14 to 2025-26, with one gap: 2020-21, which the SCA never " +
"published. Seven come from NYC Open Data; the other five are parsed from the SCA's own " +
"PDFs and carry the same check (iv). A time-series figure plots twelve points and breaks " +
"the line at 2020-21.",
snapshot_year: Object.fromEntries(snapshots.map((s) => [s, yearOf.get(s) as string])),
snapshot_year_note:
"data_as_of identifies the vintage and is not a school year: for the seven Socrata vintages it " +
"is a posting date, three to eighteen months after the count, and for the five PDF years it is " +
"the audited-register date the report states. The school year is measured against the DOE " +
"Demographic Snapshot (doc §2), not read off the date.",
universe_note:
"School grain covers organizations whose org code resolves to exactly one DBN in the 2019-20 " +
"School Locations directory plus every year of the DOE Demographic Snapshot. Building grain " +
"covers every building with an enrollment, a target capacity and a published utilization.",
capacity_note:
"Target capacity is the SCA's planning construct, not a fire-code occupancy limit. It counts " +
"Pre-K rooms, each at up to 18 seats full-day or 36 half-day (capped by the room's area ÷ 35 " +
"sq ft), and enrollment includes 3-K and Pre-K students, so Pre-K is on both sides of the " +
"ratio; the Blue Book's separate PK capacity column restates the Pre-K share and is not " +
"added to it. Charters in private space are absent from the Blue Book, so any citywide " +
"figure is a DOE-buildings figure.",
direction_note:
"Every measure here is NEUTRAL. Utilization describes how full a building is; it does not rank " +
"the school in it.",
quantile_method:
"exclusive (the default of Python's statistics.quantiles), which is what produced the " +
"percentile table in doc §4.1",
sources: [
{
id: BLUEBOOK,
url: socrataUrl(BLUEBOOK),
name: "Enrollment Capacity And Utilization Reports (SCA Blue Book)",
rows: socrataRows.length,
role: "enrollment, target capacity and published utilization at both grains, seven school years",
},
{
id: "sca_bluebook_pdf",
url: "https://www.nycsca.org/Community/Capital-Plan-Reports-Data",
name: "SCA Enrollment, Capacity & Utilization Report (the annual PDF)",
rows: recovered.reduce((n, r) => n + r.rows.length, 0),
role:
"the five school years Socrata does not carry — " +
`${recovered.map((r) => r.schoolYear).join(", ")}. Parsed by ` +
"pipeline_nyc/parse/bluebook.py under a two-parser gate and committed to " +
"data/cities/nyc/reference/parsed/",
},
{
id: BLUEBOOK_HIST_ORG,
url: socrataUrl(BLUEBOOK_HIST_ORG),
name: "Enrollment Capacity And Utilization Reports - Historical by Organization",
rows: hist.length,
role: "the pre-2014 capacity formula, 2013-14 only — the second denominator",
},
...parts.map((p) => ({
id: p.source,
url: p.source.startsWith("socrata:") ? socrataUrl(p.source.slice("socrata:".length)) : DEMO_XLSX_URL,
name:
p.source === `socrata:${DIRECTORY}`
? "2019-20 School Locations"
: "Demographic Snapshot (school level)",
dbns_contributed: p.dbns,
role: "DBN universe the org-code join resolves against",
})),
],
source_dependency_note:
"The InfoHub spreadsheet is the only input without a Socrata copy, and its filename carries " +
"its year range, so it rolls annually. When it 404s this script fails rather than rebuilding a " +
"shorter universe, because a shorter universe restates every school-grain figure quietly.",
qa_verdict: qa.verdict,
},
universe: {
dbns: dbns.size,
org_codes: dbnIndex.size,
ambiguous_org_codes: [...dbnIndex.entries()].filter(([, v]) => v.length > 1).map(([k]) => k).sort(),
by_year: universe_by_year,
sensitivity_all_orgs,
sensitivity_note:
"sensitivity_all_orgs drops the DBN filter and keeps every organization with students and " +
"seats. It is the same population plus the six or seven unmatched organizations a year, and " +
"it is reported so the filter's effect is measurable rather than assumed.",
},
by_year,
decomposition,
by_district,
capacity_definitions,
qa,
};
return packet;
}
// ── main ─────────────────────────────────────────────────────────────────────
async function main() {
const packet = await build();
const serialized = JSON.stringify(packet, null, 1);
if (CHECK) {
if (!fs.existsSync(OUT)) {
console.error(`[check] ${OUT} does not exist; run the script without --check first.`);
process.exit(1);
}
const committed = JSON.parse(fs.readFileSync(OUT, "utf-8")) as unknown;
const drift = diffPaths(committed, JSON.parse(serialized) as unknown);
if (drift.length === 0) {
console.log(`[check] ${path.relative(process.cwd(), OUT)} matches the live sources.`);
return;
}
console.error(`[check] ${drift.length} difference(s) between the committed packet and the live sources:`);
for (const d of drift) console.error(` ${d}`);
console.error(
"\nThe upstream data has been restated, or a convention in this script has changed. Re-read " +
"docs/design/10_nyc_enrollment_capacity_utilization.md before regenerating: the doc quotes " +
"these numbers and would need the same revision.",
);
process.exit(1);
}
fs.mkdirSync(path.dirname(OUT), { recursive: true });
fs.writeFileSync(OUT, serialized);
console.log(`[packet] wrote ${path.relative(process.cwd(), OUT)} (${(serialized.length / 1024).toFixed(0)} KB)`);
console.log(` as of ${packet.meta.generated_at} — ${packet.meta.generated_at_basis}`);
console.log(` QA ${packet.qa.verdict}: ${packet.qa.checks.filter((c) => c.pass).length}/${packet.qa.checks.length} checks`);
for (const c of packet.qa.checks) if (!c.pass) console.log(` FAILED ${c.check}: ${c.detail}`);
console.log(` ${"year".padEnd(9)}${"n".padStart(6)}${"median".padStart(8)}${"<50%".padStart(8)}${"<33%".padStart(8)}${">100%".padStart(8)}${"Σ/Σ".padStart(8)}`);
for (const year of packet.meta.years) {
const s = (packet.by_year as Record<string, { school: Distribution }>)[year].school;
console.log(
` ${year.padEnd(9)}${String(s.n).padStart(6)}${String(s.median).padStart(8)}` +
`${String(s.share_under_50).padStart(8)}${String(s.share_under_33).padStart(8)}` +
`${String(s.share_over_100).padStart(8)}${String(s.sum_over_sum).padStart(8)}`,
);
}
const d = packet.decomposition;
console.log(
` decomposition ${d.from} → ${d.to}: ${d.continuing_buildings} buildings, ` +
`${d.utilization_change_pp} pp = ${d.enrollment_effect_pp} enrollment ${d.capacity_effect_pp >= 0 ? "+" : ""}${d.capacity_effect_pp} capacity`,
);
}
main().catch((e) => {
console.error(e);
process.exit(1);
});