Repository consolidating multiple Pennsylvania education datasets.

Each dataset is pulled from a PA Department of Education (or PDE-affiliated) source, cleaned into a tidy long-format table, and written to public/ as a parquet file. Most tables share au_number (PDE's Administrative Unit Number, identifying the school district/charter/IU) and school_number (4-digit, zero-padded) as join keys, plus a school_year column (the first year of the school year, e.g. 2024 for SY 2024-2025).

Repo layout

• etl/download_*.R -- downloads a dataset's raw source files into data/source/<dataset>/ (gitignored; run these before the matching build script).
• etl/build_*.R -- reads the raw source files and writes the cleaned parquet table(s) to public/.
• public/ -- the cleaned, tidy output tables described below.

Tables

schools.parquet

A school lookup/location table: one row per public/charter and private/nonpublic school PDE's EdNA registry has ever recorded, open or closed, regardless of whether it shows up in any of the other, enrollment-driven tables below. This is the one table here that isn't a year-by-year series -- it's a point-in-time snapshot, since EdNA itself isn't published as yearly extracts and re-geocoding thousands of schools for a synthetic history isn't worth the cost.

Name, type, address, county, district, and open/closed status all come from EdNA, PDE's authoritative name/address registry. district for a private school is only as granular as EdNA gets -- the containing Intermediate Unit, not a specific public school district. A handful of private-entity categories that aren't schools at all (Act 48 continuing-ed providers, one "Miscellaneous" row) are excluded.

Coordinates prefer EdNA's own Latitude/Longitude first, falling back to demographics.parquet's source file for public/charter schools or to geocoding (US Census batch geocoder, then ArcGIS for whatever Census can't place) for private schools. EdNA and School Fast Facts disagree on a surprising number of public schools -- of ~2,900 with a coordinate in both, only 17% are within 100m of each other -- so every school's chosen coordinate is checked for plausibility (does it fall within a 5km-buffered version of its own ZIP code's Census ZCTA boundary?); anything that fails gets (re-)geocoded from its own address and rechecked. coord_plausible is FALSE for the small number that still don't check out after that, and NA where there's no coordinate, or its ZIP has no ZCTA to check against.

• Source: EdNA (Educational Names & Addresses) -- Public Schools Extract and Private and Nonpublic Entities Extract, both Open + Closed statuses, all categories
• ETL: download_schools.R, build_schools.R
• Columns: au_number, school_number, school_name, school_type, school_address, city, county, zip, district, status (Open/Closed), status_date, latitude, longitude, coord_source (edna / state_demographics / census_geocoder / arcgis_geocoder), coord_plausible

demographics.parquet

Percent of a school's enrollment in each demographic category (economically disadvantaged, race/ethnicity, English learner, special education, foster care, homeless, military-connected, gender, gifted), plus a title_i_school flag. One row per school per school year.

• Source: "School Fast Facts" files, FutureReadyPA Data Files
• ETL: download_demographics.R, build_demographics.R
• Columns: school_year, au_number, school_number, disadvantaged, american_indian, asian, native_hawaiian_pacific_islander, black, hispanic, white, two_or_more_races, english_learner, special_education, foster_care, homeless, military_connected, female, male, gifted, title_i_school

enrollment.parquet

Combined public and private/nonpublic school enrollment by grade, SY 2014-2015 through the most recent year. Public schools cover all types (district, charter, cyber charter, CTC, IU); private/nonpublic school rows are reported at the LEA level only (school_number is always "0000").

• Source: PA.gov enrollment data page
• ETL: download_enrollment.R, build_enrollment.R
• Columns: school_year, au_number, school_number, grade, enrollment

enrollment_el.parquet

English Learner (EL, called LEP in older years) student counts by school.

• Source: PA.gov enrollment data page ("EL Student Counts by LEA and School" files)
• ETL: download_enrollment_el.R, build_enrollment_el.R
• Columns: school_year, au_number, school_number, enrollment

Financials (AFR data)

Six tables built from PDE's Annual Financial Report (AFR) data, all scoped to school districts and charter schools only (career/tech centers and intermediate units are excluded).

• Source: Summary of Annual Financial Report Data
• ETL: download_financials.R, build_financials.R

Table	Description	Columns
financials_revenue_detailed.parquet	Full account-level revenue detail (accounts 6000-9999: local/state/federal/other), one row per district per year per account	school_year, au_number, account, amount
financials_expenditures_detailed.parquet	Full account-level expenditure detail, one row per district per year per account	school_year, au_number, account, amount
financials_general_fund_balance.parquet	Committed/assigned/unassigned general fund balance components (accounts 0830/0840/0850) -- no other fund balance components or a total are published	school_year, au_number, acct_0830, acct_0840, acct_0850
financials_revenue_summary.parquet	Revenue broken down by source (local/state/federal/other) at a coarser, summary level than the detailed table	school_year, au_number, total_revenue, total_local, local_taxes, local_other, total_state, total_federal, total_other, pct_local, pct_state, pct_federal, pct_other
financials_expenditures_summary.parquet	Expenditures broken down by function at a coarser, summary level than the detailed table	school_year, au_number, total_expenditures, current_expenditures, actual_instruction_expense, acct_1000...acct_2900
financials_paid_media_advertising.parquet	Paid media advertising & sponsorship-of-public-events expenditures. Single year only (SY 2024-2025, unaudited), published as a standalone "Miscellaneous" file rather than part of the yearly detailed/summary series above	school_year, au_number, paid_media_advertising

Performance (Future Ready PA Index)

Three tables of PA Future Ready Index school performance measures, one per measure category/pillar, covering SY 2017-2018 through the most recent year. Only each measure's own reported value is kept -- companion columns reporting a statewide average, an ESSA goal, or progress toward a goal are dropped. A null value means the source didn't publish a usable number for that row (including because a subgroup was too small to report -- PA suppresses subgroup values below roughly N=20 students at a school).

Metric names are only shared across the SY 2020-2021 -> SY 2021-2022 format change where the two eras clearly describe the same measure, just reworded; metrics whose definition may have changed keep distinct, era-specific slugs (suffixed _legacy for the pre-2021-2022 ones) instead of guessing at equivalence -- see the comments in build_performance.R for details.

• Source: "Future Ready Performance Data" files, FutureReadyPA Data Files
• ETL: download_performance.R, build_performance.R
• Columns (all three tables): school_year, au_number, school_number, metric, subgroup, value

Table	Category
performance_state_assessment.parquet	State assessment proficiency/advanced rates, test participation, and PVAAS academic growth, by subject (ELA, Math/Algebra 1, Science/Biology)
performance_on_track.parquet	Attendance, chronic absenteeism, English language proficiency, and grade 3/7 reading & math checkpoints
performance_college_career.parquet	Graduation rates, AP/IB/dual-enrollment participation, CTE program completion, industry credentials, and post-graduation outcomes