PhiladelphiaDiscipline & climateMethodology recipe

Philly publishes the suspension distribution — and the heavy-tail share is the real story

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

Summary

Derived `oss_pct_chronic` from the SDP suspension distribution columns (`% with 4+ OS Suspensions`, residualized to handle missing 4+ column). Top 8 schools in 2024-25, all ALL subgroup, restricted to non-null and non-suppressed.

Data sources

Database tablephilly_schools
One row per Philly school. PK is ulcs_code (SDP canonical, = id_eos in PSSA, = SRC_School_ID × 10). Carries aun_code + pa_code for PDE-side joins.
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 School Performance (OpenDataPhilly CDN)

Steps

Top OSS chronic share in 2024-25.

SELECT s.name, s.school_type::text, m.value AS pct_4plus
FROM philly_school_year_metrics m
JOIN philly_schools s ON s.ulcs_code = m.school_ulcs
WHERE m.metric_key='oss_pct_chronic' AND m.subgroup='ALL' AND m.year='2024-25'
ORDER BY m.value DESC LIMIT 8;

Caveats

Suspension distributions are public-policy contested. Some districts code differently for the same behavior. SDP's suspension reporting follows PA's standardized definition. Schools with very few students have small-denominator volatility.

Reproduce

Load `sdp_attendance_and_suspensions.ts`, run query.

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