edfinr

An R package for downloading and analyzing education finance data from multiple sources.

Installation

You can install edfinr from CRAN with:

install.packages("edfinr")

You can install the development version of edfinr from GitHub with:

pak::pkg_install("bellwetherorg/edfinr") 

Data Sources

This package provides access to education finance data from:

• NCES CCD F-33 Data
• NCES CCD Directory Data via the Urban Institute’s educationdata package
• Census Bureau SAIPE Estimates
• American Community Survey 5-Year Estimates via tidycensus package
• U.S Bureau of Labor Statistics Consumer Price Index for All Urban Consumers (CPI-U)
• NCES EDGE Comparable Wage Index for Teachers (CWIFT)
• U.S. Census Bureau Gazetteer Files

Data Processing Methods

• Methodology based on process used by edbuildr, which is detailed on a methodology page and in their workshop documentation.
• The EdFund Data Dictionary informs our handling of F-33 data.
• Revenue adjustments for payments to other school systems follows the approach used by Kristen Blagg, Emily Gutierrez, and Fanny Terrones in Funding Flows: Which Students Receive a Greater Share of School Funding?.
• Inflation adjustments use an average of second half CPI-U of one year and first half CPI-U of the following year to align with the academic calendar.

Data Processing Detail

Full data processing methods and scripts are available on GitHub via bellwetherorg/edfinr_data_cleaning.

NCES F-33 Survey Data

Data source: NCES Common Core of Data text files of F-33 data from 2011-12 through 2022-23.

Raw variables selected:

• Basic information: state, leaid, name, yrdata, V33
• Revenue data: totalrev, tlocrev, tstrev, tfedrev
• Expenditure data: c11, u11, v91, v92, c24, l12, m12, d11, q11
• Current expenditure data: ce1, ce2, and ce3
• Detailed expenditure data: z32, z34, v93, v95, v02, k14, e13, z33, v10, e17, v11, v12, e07, v13, v14, e08, v15, v16, e09, v17, v18, v40, v21, v22, v45, v23, v24, v90, v37, v38, e11, v29, v30, v60, v32, v65, ae1, ae2, ae3, ae4, ae5, ae6, ae7, ae8
• Capital outlay, debt, and fund-balance data: tcapout, f12, g15, k09, k10, k11, i86, _19h, _21f, _31f, _41f, _61v, _66v, w01, w31, w61

Adjustments:

• Rename variables
• Convert district names to title case
• Ensure enrollment is a numeric variable
• Replace -1 and -2 codes with NA values

CCD Directory Data

Data source: NCES CCD Directory data obtained via the educationdata package.

Raw variables selected:

• Core district identifiers and location: state, ncesid, county, dist_name, state_leaid
• Institutional details: lea_type, lea_type_id, urbanicity, congressional_dist

Adjustments:

• Rename variables to more intuitive names

SAIPE Poverty Estimates

Data source: Census Bureau SAIPE Estimates

Raw variables selected:

• Basic geographic and demographic fields: State Postal Code, State FIPS Code, District ID, Name
• Population estimates: Estimated Total Population, Estimated Population 5-17, and the estimated number of relevant children 5 to 17 years old in poverty

Adjustments:

• Convert population fields to numeric
• Construct a combined NCES district identifier by concatenating state FIPS and District ID

ACS 5-Year Estimates

Data source: American Community Survey 5-Year Estimates accessed via the tidycensus package

Raw variables selected:

• Economic indicators: Median household income (B19013_001), mean household income (aggregate income / households), median property value (B25077_001), and the Gini index of income inequality (B19083_001)
• Household and labor characteristics: owner-occupied housing share (B25003), SNAP receipt (B22003), and the unemployment rate (B23025)
• Educational attainment: Total population 25 years or older (B15003_001) and subsets of that population holding bachelor’s degrees (B15003_022), master’s degrees (B15003_023), professional degrees (B15003_024), and doctoral degrees (B15003_025).
• Data are pulled for different geographic breakdowns (unified, elementary, and secondary school districts)

Adjustments:

• Reshape data from long to wide format
• Rename “GEOID” to a standard ncesid and ensure proper formatting of district identifiers
• Convert estimates to numeric as needed

CPI

Data source: U.S. Bureau of Labor Statistics, specifically the Consumer Price Index for All Urban Consumers (CPI-U)

Raw variables selected:

• CPI time series data (specific variable names as provided in the raw file)

Adjustments:

• Calculate an averaged CPI value using the second half of one year and the first half of the following year to align with the academic calendar, with the 2011-12 school year as the baseline year
• Clean and reformat CPI data for consistency across processing scripts

Census Gazetteer Files

Data source: U.S. Census Bureau Gazetteer Files, school district vintages.

Raw variables selected:

• District identifier (GEOID) and land area (ALAND_SQMI, land only, excludes water).

Adjustments:

• Rename GEOID to ncesid and ALAND_SQMI to land_area_sq_mi.
• Each Gazetteer vintage is joined to the edfinr fiscal year covering the same school year.
• s_per_sq_mi is derived as enroll / land_area_sq_mi; it is NA, never Inf, where land area is zero or unavailable.
• LEAs without a Census boundary (charters, ESAs, state-operated agencies) have no Gazetteer match and are NA for both fields.
• Vermont’s FY2016-FY2021 Act 46 district consolidation leaves match rates around 7-12% for those years, versus 97%+ in other years and states; restrict Vermont trend analyses to FY2012-FY2015 and FY2022 onward.

Joining Data

• The joining process is implemented in the 08_edfinr_join_and_exclude.R script (CWIFT is prepared in 07_cwift_clean.R and joined there).
• Data from the F-33 survey, CCD Directory, ACS (unified, elementary, and secondary), SAIPE, CWIFT, and Census Gazetteer sources are merged using left joins on shared district identifiers (ncesid) and fiscal year.
• The procedure ensures that each district record is enriched with revenue, expenditure, demographic, and economic data.

Revenue Adjustments

Additional transformations are applied after the join: - State revenue for capital outlay and debt service (C11) is subtracted from state revenues; the subtracted amount ships as rev_state_cap_debt in both datasets (zero-filled, not NA, for non-reporting districts), unadjusted state revenue is preserved in rev_state_unadj/rev_state_unadj_pp, and c11_spike_flag marks district-years where this adjustment produces an anomalous spike - Property sales (U11) are subtracted from local revenues - For Texas LEAs in 2012-13 and earlier, payments to state governments (L12) are subtracted from local revenues - Payments to other school systems (V91, V92, and Q11) are proportionally subtracted from local, state, and federal revenues

Exclusions

• Districts with enrollment of zero or below are removed.
• Districts with total revenue of zero or below are removed.
• Districts with an invalid LEA type (i.e. where lea_type_id is not one of 1, 2, 3, or 7) are excluded. Since 0.2.0 the screen tolerates single-vintage miscodes (a district-year is excluded only if the following directory vintage agrees), and Massachusetts regional districts miscoded as service agencies are retained for FY2012-FY2016; see the “Data Sources and Methodology” vignette.
• Districts with invalid LEA/school level type (i.e. where schlev is not one of “01”, “02”, or “03”, except for specified CA exceptions) are excluded.
• Districts where total revenue per-pupil is greater than $70,000 in 2011-12 dollars are excluded.
• Districts where total revenue per pupil is less than $500 in 2011-12 dollars are excluded.
• Connecticut LEAs consisting of semi-private high schools are removed (NCES IDs “0905371”, “0905372”, and “0905373”).

Data Notes and Cautions

Users should note the following when working with the edfinr datasets:

• Some variables were originally coded with -1 to indicate missing values; these have been replaced with NA during processing.
• During data processing, we identified a sharp rise in the number of California districts appearing only from 2019 onward in the data. This reflects the fact that many charter schools became separate LEAs in those years. Beginning in 2018–19, a wave of California charter schools switched to independent CALPADS/CBEDS reporting and thus were assigned their own NCES LEA IDs for the first time. Once in the NCES LEA universe, those new charter‐LEAs automatically show up in the F-33 finance survey (with blanks or flags if they report no finance data), and Census’s SAIPE and ACS school‐district products (which mirror NCES LEA boundaries).
• The joined dataset represents a synthesis of data from multiple sources; discrepancies in source data formats may lead to minor variations.
• Inflation and adjustment factors (e.g., CPI adjustments) are based on averages and may not perfectly reflect local cost variations.
• Capital outlay is excluded from exp_cur_total, is lumpy year to year (use multi-year averages), and NA means an item was not reported, not zero. Debt and fund-balance stocks (debt_*, fund_bal_*) are point-in-time levels and are never CPI-adjusted.
• CWIFT is a relative labor-cost index (not a price deflator); FY2012-2014 are unavailable, FY2020 is interpolated, and FY2023 is carried forward from FY2022.
• land_area_sq_mi and s_per_sq_mi are NA by design for LEAs without a Census boundary (charters, ESAs, state-operated agencies), and s_per_sq_mi is NA (never Inf) wherever land area is zero or unavailable. Vermont has a coverage gap from FY2016-FY2021. Use log scales when plotting s_per_sq_mi.
• The hosted datasets are distributed as gzip-compressed Parquet files.
• Caution is advised when comparing data across fiscal years due to potential differences in data collection and processing methods.

Authors

• Alex Spurrier (alex.spurrier@bellwether.org) - Lead developer and package maintainer
• Krista Kaput - Core development and feature implementation
• Michael Chrzan - Data processing functions and testing