This is work in progress. Here are the latest updates and current areas of focus:
- ✅ Geographic data shapefiles and metadata for counties, school districts, and their intersection
- ✅ Census SAIPE data - county and school district levels
- ✅ Census GIS code lookups - CBSAFP, CLASSFP, CSAFP, FUNCSTAT, LSAD, METDIVFP, MTFCC, STATEFP, STATEUSPS
- ✅ Census Annual Survey of School System Finances - Unit Tables
- ✅ NCES CWIFT data
- ✅ NYSED Student/Educator Staff data
- ✅ Crosswalk for NYSED to CENSUS/NCES district data
- 🔜 Further NYSED data
Information about public financing for New York’s K-12 schools is spread across multiple data sources, including the U.S. Census Bureau, the U.S. Department of Education, and the New York State Education Department (NYSED). This data model brings together these disparate data sources to get a more holistic view of public financing for K-12 schools in New York.
School year 2019-2020 data was retrieved from the sources listed below along with appropriate data dictionaries and technical documentation. For each data set, data elements were categorized and analyzed in three main areas: geospatial boundaries, finances (revenues and expenditures), and demographics. Grouped data elements were consolidated following dimensional modeling standards and best practices (Kimball & Ross, 2013).
School district boundary information is updated annually via the School District Review Program. The resultant GIS data is available via the Census Bureau's TIGER/LINE Shapefiles or via the NCES Education Demographic and Geographic Estimates (EDGE) Program website.
School districts don't fit into standard geographic hierarchies at all. They can cross state lines, counties, cities, towns, villages, and even a single Census block may have multiple school districts within its boundaries. NCES provides a set of relationship files to help determine how school districts overlap with other entities.
In the census_gis_counties table provided in this data set, the Statewide (GEOID=36000) INTPTLAT and INTPTLON come from the Census bureau's State Area Measurements and Internal Point Coordinates. However, the area calculations were done by summing the respective columns (ALAND and AWATER) to provide data that is consistent with the 2020 SDRP GIS files.
The CWIFT is designed to identify geographic variation in wages for college-educated workers outside of the education field after controlling for job-related and demographic characteristics.
An example adapted from the documentation (p.16):
To normalize dollar amounts and make them comparable, divide the dollar amounts by the district-level CWIFT, which are already normalized to the national average wage.
The 2015 CWIFT for Los Angeles Unified School District 1.129.
In 2013-14, Los Angeles Unified spent $6,137 total current expenditures on salary per pupil.
When normalized, this equals $5,436 ($6,137 / 1.129).
In the same year, Palm Beach County School District (in Florida) spent $5,433 total current expenditures on salary per pupil.
Normalized to reflect the lower cost of hiring in this area, they are the equivalent of $5,677 ($5,433 / 0.957).In other words, even though Los Angeles Unified School District spent more than Palm Beach County School District in nominal terms, once the two dollar figures were adjusted for the difference in purchasing power between the two districts, Palm Beach County School District effectively spent $241 more per pupil than did Los Angeles Unified School District.
- ACS Data
- ACS Documentation To download the ACS Data tables, I used data.census.gov.
- Geography: School Districts (Unified) > New York > All = https://data.census.gov/all?g=040XX00US36$9700000
- Surveys: American Community Survey > 5-Year Estimates > Data Profiles = https://data.census.gov/all?g=040XX00US36$9700000&d=ACS+5-Year+Estimates+Data+Profiles
NYSED data is available at https://data.nysed.gov/downloads.php but be warned that most files are in Access database format.
Data about students and educators are reported by total public school (aggregated data for all districts and charter schools), county (aggregated data for all districts and charter schools in the county), Needs-to-Resource-Capacity (N/RC) group, district, and public schools.
I have removed CSO_NAME and PHONE fields from this data set because they are unnecessarily invasive to privacy and are not relevant to data analysis.
Use the included xwalk_nysed_lea table to match NYSED data with the CENSUS or NCES data.