PhiladelphiaMethodologyMethodology recipe

SPREE agrees with our DB on 616/616 cells — Philly's data pipeline is structurally clean

A reproducibility log: the data this analysis touched, the queries it ran, and how a third party would re-run it.

Summary

Reads SPREE 2023-24 CSV, maps the long-format `metric` strings to our metric_keys + subgroups, looks up the matching served DB row, and computes per-cell delta at ±1.5pp tolerance.

Data sources

Database tablephilly_school_year_metrics
Long-format facts. PK (school_ulcs, year, metric_key, subgroup, population_cut). Carries comparison_group_percentile + citywide_percentile (direction-adjusted).
External datasetSDP SPREE 2023-24 CSV
Loader scriptpipeline_philly/verify/reconcile_spree.py

Steps

Map SPREE metric strings to (metric_key, subgroup) via METRIC_PREFIX_MAP + SUBGROUP_SUFFIX_MAP.
For each mapped SPREE cell, look up served value at (ulcs, year, metric_key, subgroup), population_cut='acct'.
Compute delta, count matches within ±1.5pp.

Caveats

SPREE is same-publisher as the SDP source files (NOT an independent oracle). For independent cross-check, see __cross_publisher_pssa.md (Future Ready). Different years' SPREE may have different metric-string conventions; the parser handles current format only.

Reproduce

Fetch sdp_spree_2023-24 via discover.py, ensure 2023-24 SDP PSSA also loaded into DB, then run reconcile_spree.py.

The recipe lives at data/cities/philly/stories/recipes.ts in the repo.