Enrollment

Real K-12 (Utah DOE + NCES) and college (USHE + IPEDS) enrollment, by district and county

html`<style>
  .cell-output-display svg text { font-size: 15px !important; }
  .cell-output-display table { font-size: 16px; }
  /* Tooltip box is white, so its text must stay dark in dark mode */
  [aria-label="tip"] text { fill: #374151 !important; }
</style>`

Overview

This script produces the “real enrollment” half of the validation schema — the numbers PopulationSim’s synthetic population (3_synthetic.qmd) gets compared against.

• Objective: get district-level K-12 enrollment and county-level college enrollment from real, non-synthetic sources, joined through the school and college crosswalks built in 1_geography.qmd.
• What it pulls: Utah DOE enrollment (public schools only, from the BY2023 UGRC snapshot copied from TDM-INP-K-12-Enrollment; its SchoolYear field is 2025), NCES enrollment by district (public via CCD); NCES private-school enrollment (Private School Universe Survey), reported in section 3 but not in the district validation table; USHE enrollment by institution and site (the WFRC data request, not a home-ZIP file, so its geography is the institution’s site); and IPEDS by county (fall 2023 campus-based enrollment, institutional control, and full-time/part-time detail).
• Data vintages: Utah DOE 2024-25 (the BY2023 UGRC snapshot, SchoolYear 2025), NCES CCD and PSS 2023-24, USHE Academic Year 2025 Term 2, IPEDS fall 2023, and PopulationSim 2023. Every chart and table names the vintage next to each source.
• Known gaps:
  • Charter schools are counted as public schools in the district totals. Whether Utah DOE credits their enrollment to the resident district has not been checked against DOE’s published totals.
  • NCES private-school enrollment is by school location, not residence.
  • USHE is Academic Year 2025 and covers public institutions only. IPEDS is fall 2023. Neither matches the ~2023 vintage used everywhere else, and no 2023-24 USHE extract is on file.
• Output: CSVs in _data/intermediate/. The validation pages read the district and county totals (real_enrollment_by_district.csv, real_enrollment_by_county.csv) and the level breakdowns (real_by_district_level.csv, real_college_by_county_level.csv). university_modality_compare.csv feeds the section 8 chart. ipeds_fulltime_parttime.csv is written but not charted yet. _outputs/ is created here, but nothing is written to it.

Setup

import pandas as pd
import geopandas as gpd
import requests
import zipfile
from pathlib import Path

paths = {
    "raw": Path("_data/raw/"),
    "intermediate": Path("_data/intermediate/"),
    "output": Path("_outputs/"),
}

constants = {
    "data_year": 2023  # NCES/IPEDS/PSS vintage -- bump this one number in ~4 years
}
year_code = f"{str(constants['data_year'])[-2:]}{str(constants['data_year'] + 1)[-2:]}"

school_crosswalk = pd.read_csv(
    paths["intermediate"] / "crosswalk_school.csv", dtype={"ID": str, "COUNTYFP": str}
)
college_crosswalk = pd.read_csv(
    paths["intermediate"] / "crosswalk_college.csv", dtype={"ID": str, "COUNTYFP": str}
)

1. K-12 — Utah DOE enrollment by district

# Public schools only, from the BY2023 UGRC snapshot (SchoolYear 2025) built in 1_geography.qmd
ugrc_public_schools = gpd.read_file(
    paths["intermediate"] / "ugrc_school_by2023.gpkg",
    sql="""
        SELECT * FROM ugrc_school_by2023
        WHERE privateschool = 'False'
          AND onlineschool IS NOT 'Y'
    """,
)

# Join school -> district via the school crosswalk (point-in-polygon, one row per school) rather than
# matching the school's own `leaname` text against the district boundary's `name` text.
# Only the public school IDs joined below are checked. The BY2023 snapshot lists one private school
# (Realms of Inquiry, 1175, zero students) twice, with points in two districts.
doe_crosswalk = school_crosswalk[
    (school_crosswalk["Unit"] == "school")
    & (school_crosswalk["Source"] == "UGRC")
    & school_crosswalk["ID"].isin(ugrc_public_schools["schoolid"].astype(str))
]
assert (doe_crosswalk.groupby("ID")["District Name"].nunique() <= 1).all(), (
    "UGRC school ID maps to two districts"
)
doe_crosswalk = doe_crosswalk.drop_duplicates(subset=["ID"])

# The UGRC layer repeats some school IDs. Exact copies (Minersville, 201, is listed twice with the same
# count) are dropped so they are not double-counted. Separate records that share an ID (Grouse Creek,
# 218, an elementary and a secondary with different counts) are both kept and summed.
doe_grade_cols = ["preschool", "k"] + [f"grade{i}" for i in range(1, 13)]
ugrc_unique = (
    ugrc_public_schools[["schoolid", "schoolname", "totalk12", *doe_grade_cols]]
    .drop_duplicates()
    .assign(doe_students=lambda df: df["totalk12"] + df["preschool"].fillna(0))
)

# Left join, so schools with no crosswalk match are kept visible instead of silently dropped
doe_schools = (
    ugrc_unique.assign(schoolid=lambda df: df["schoolid"].astype("Int64").astype(str))
    .merge(doe_crosswalk[["ID", "District Name"]], how="left", left_on="schoolid", right_on="ID")
)
assert len(doe_schools) == len(ugrc_unique), "school join changed the row count"

unmatched_doe_schools = doe_schools[doe_schools["District Name"].isna()]
print(
    f"DOE schools with no district match: {len(unmatched_doe_schools)} "
    f"({unmatched_doe_schools['doe_students'].sum():,.0f} students, excluded from district totals)"
)

# groupby drops the unmatched rows (NaN district), so they are excluded from the totals below
doe_enrollment_by_district = (
    doe_schools.groupby("District Name")["doe_students"]
    .sum()
    .rename("doe_enrollment")
    .reset_index()
)
doe_enrollment_by_district

DOE schools with no district match: 352 (161,345 students, excluded from district totals)

	District Name	doe_enrollment
0	ALPINE	101530
1	BOX ELDER	12996
2	CANYONS	36017
3	DAVIS COUNTY	77784
4	GRANITE	68449
5	JORDAN	68925
6	MURRAY CITY	5686
7	NEBO	39435
8	OGDEN CITY	13649
9	PROVO CITY	15919
10	SALT LAKE CITY	22729
11	WEBER COUNTY	35527

# Public DOE schools that did not get a district from the school crosswalk
unmatched_doe_schools[["schoolid", "totalk12"]]

	schoolid	totalk12
11	186168	734
12	186436	1894
13	289	199
14	290	616
19	186443	438
...	...	...
1051	70583	0
1054	185804	197
1056	186347	210
1068	777	5
1070	186836	161

352 rows × 2 columns

# Online school enrollment (reported separately; not included in the district totals above)
online_enrollment = gpd.read_file(
    paths["intermediate"] / "ugrc_school_by2023.gpkg",
    sql="""
        SELECT * FROM ugrc_school_by2023
        WHERE privateschool = 'False'
          AND onlineschool = 'Y'
    """,
)["totalk12"].sum()
online_enrollment

np.int64(19357)

2. K-12 — NCES enrollment by district

# Matches constants["data_year"] -- the vintage pulled in 1_geography.qmd
nces_public_school = gpd.read_file(paths["raw"] / "geometry" / "nces_public_school.gpkg")

nces_crosswalk = school_crosswalk[school_crosswalk["Source"] == "NCES public"]
assert (nces_crosswalk.groupby("ID")["District Name"].nunique() <= 1).all(), (
    "NCES public ID maps to two districts"
)
nces_crosswalk = nces_crosswalk.drop_duplicates(subset=["ID"])

# DOE totalk12 is K-12 only: it equals the sum of k and grades 1-12 on every public, non-online row except
# two in the BY2023 snapshot (see the level check in section 6), and it excludes preschool. NCES TOTAL
# includes PreK, plus other codes (UG, AE, G13), so NCES is summed over KG-G12 to match the DOE definition. NCES codes a grade the school doesn't offer as -2; those
# cells count as zero students.
nces_k12_grades = ["KG"] + [f"G{i:02d}" for i in range(1, 13)]
assert (nces_public_school[nces_k12_grades] >= -2).all().all(), "NCES K-12 grade has an unexpected code"
nces_public_school["K12"] = nces_public_school[nces_k12_grades].clip(lower=0).fillna(0).sum(axis=1)

# PK (preschool) uses the same -2 "not offered" rule as the grades, so negative codes count as zero
assert (nces_public_school["PK"] >= -2).all(), "NCES PK has an unexpected code"
nces_public_school["PK_students"] = nces_public_school["PK"].clip(lower=0).fillna(0)
nces_public_school["nces_students"] = nces_public_school["K12"] + nces_public_school["PK_students"]

nces_enrollment_by_district = (
    nces_public_school[["NCESSCH", "nces_students"]]
    .drop_duplicates()
    .assign(NCESSCH=lambda df: df["NCESSCH"].astype(str))
    .merge(
        nces_crosswalk[["ID", "District Name"]],
        left_on="NCESSCH",
        right_on="ID",
    )
    .groupby("District Name")["nces_students"]
    .sum()
    .rename("nces_enrollment")
    .reset_index()
)

nces_enrollment_by_district

	District Name	nces_enrollment
0	ALPINE	100770
1	BOX ELDER	13029
2	CANYONS	37612
3	DAVIS COUNTY	82003
4	GRANITE	69389
5	JORDAN	71141
6	MURRAY CITY	7465
7	NEBO	48097
8	OGDEN CITY	14375
9	PROVO CITY	15984
10	SALT LAKE CITY	23563
11	WEBER COUNTY	35716

3. K-12 — NCES private-school enrollment by district

# Private School Universe Survey (PSS)
pss_dir = paths["raw"] / "pss"
pss_dir.mkdir(parents=True, exist_ok=True)
pss_csv_path = pss_dir / f"pss{year_code}_pu.csv"

if not pss_csv_path.exists():
    zip_path = pss_dir / f"pss{year_code}_pu_csv.zip"
    zip_path.write_bytes(
        requests.get(f"https://nces.ed.gov/surveys/pss/zip/pss{year_code}_pu_csv.zip").content
    )
    with zipfile.ZipFile(zip_path) as z:
        z.extractall(pss_dir)

# NUMSTUDS = total enrollment (verified 2026-10-01 against NCES's own SIZE bucket breaks for every
# UT row checked). PPIN is a string -- some IDs are alpha-prefixed (e.g. "A2392167"), don't cast to
# numeric.
pss = pd.read_csv(
    pss_csv_path, usecols=["PPIN", "PSTABB", "PINST", "NUMSTUDS"], dtype={"PPIN": str}
).query("PSTABB == 'UT'")

crosswalk_private = school_crosswalk[school_crosswalk["Source"] == "NCES private"]
assert (crosswalk_private.groupby("ID")["District Name"].nunique() <= 1).all(), (
    "NCES private ID maps to two districts"
)
crosswalk_private = crosswalk_private.drop_duplicates(subset=["ID"])

nces_private_enrollment_by_district = (
    pss[["PPIN", "PINST", "NUMSTUDS"]]
    .merge(
        crosswalk_private[["ID", "District Name"]],
        left_on="PPIN",
        right_on="ID",
    )
    .groupby("District Name")["NUMSTUDS"]
    .sum()
    .rename("nces_private_enrollment")
    .reset_index()
)
nces_private_enrollment_by_district

	District Name	nces_private_enrollment
0	ALPINE	539
1	BOX ELDER	94
2	CANYONS	1364
3	DAVIS COUNTY	1093
4	GRANITE	1649
5	JORDAN	532
6	MURRAY CITY	121
7	NEBO	646
8	OGDEN CITY	532
9	PROVO CITY	1316
10	SALT LAKE CITY	3272
11	WEBER COUNTY	218

4. Colleges — USHE enrollment by county

# NOTE: the only USHE data WFRC has on file is Academic Year 2025 (Term 2 / Spring) and Academic Year 2020 (Term 2)
# USHE covers Utah's PUBLIC institutions only. Private institutions (e.g., BYU, Westminster, Ensign)
# are not in USHE and only appear in the IPEDS county totals, so county comparisons are not like-for-like.
# Both full-time and part-time students are kept, matching the WF-TDM college repo (6-CollegeControlTotals.qmd).
ushe = pd.read_excel(paths["raw"] / "ushe" / "WFRC Data Request_Final.xlsx")

ushe_2025_fall = ushe.query("`Academic Year` == 2025 and Term == 2")

# Scoped to WFRC region institutions/sites only (constants["counties"] in 1_geography.qmd) --
# USHE has no surrogate ID, so (Institution, Site) is the natural key; UGRC's unique ID is `xid`.
# A few sites have no UGRC point of their own and are reassigned to a same-county campus instead --
# "USU - Ogden Center" goes to Weber State's Main Campus xid, NOT USU's own (Cache County) Main
# Campus, since the reassignment target has to match the real site's county, not its institution.
ushe_to_ugrc_xid = {
    ("Salt Lake Community College", "Correctional Facility--West Valley City"): 52,
    ("Salt Lake Community College", "Main Campus"): 4,
    ("Salt Lake Community College", "SLCC - Airport Center"): 4,
    ("Salt Lake Community College", "SLCC - Jordan Campus"): 51,
    ("Salt Lake Community College", "SLCC - Larry H. Miller Campus"): 50,
    ("Salt Lake Community College", "SLCC - South City Center"): 46,
    ("Salt Lake Community College", "SLCC - West Point Center"): 78,
    ("Salt Lake Community College", "SLCC - West Valley City"): 52,
    ("Salt Lake Community College", "SLCC-UU Juniper building at Herriman Campus"): 4,
    ("University Of Utah", "Main Campus"): 7,
    ("University Of Utah", "SLCC-UU Juniper building at Herriman Campus"): 7,
    ("University Of Utah", "UU - Sandy Center"): 7,
    ("Utah State University", "USU - Brigham Center"): 14,
    ("Utah State University", "USU - Ogden Center"): 1,
    ("Utah State University", "USU - Orem Center"): 28,
    ("Utah State University", "USU - Salt Lake Center"): 32,
    ("Utah State University", "USU - Tremonton Center"): 31,
    ("Utah Valley University", "Main Campus"): 5,
    ("Weber State University", "Main Campus"): 1,
    ("Weber State University", "Technical College Campus--Davis"): 56,
    ("Weber State University", "Technical College Campus--Ogden-Weber"): 69,
    ("Weber State University", "University Center--SLCC"): 4,
    ("Weber State University", "WSU - Davis Center"): 37,
    ("Weber State University", "WSU - Farmington Station"): 42,
}

# Everything not in the dict is out-of-region or a non-geographic site (online, other-site, etc.)
ushe_in_region = ushe_2025_fall[
    ushe_2025_fall.set_index(["Institution", "Site"]).index.isin(ushe_to_ugrc_xid)
].assign(xid=lambda df: df.set_index(["Institution", "Site"]).index.map(ushe_to_ugrc_xid))

ugrc_colleges = gpd.read_file(paths["raw"] / "geometry" / "ugrc_college.gpkg")

ushe_enrollment_by_county = (
    ushe_in_region.merge(ugrc_colleges[["xid", "county_fips"]], on="xid")
    .groupby("county_fips")["Headcount"]
    .sum()
    .rename("ushe_enrollment")
    .reset_index()
)
ushe_enrollment_by_county

	county_fips	ushe_enrollment
0	003	366
1	011	2347
2	035	51556
3	049	21489
4	057	10577

5. Colleges — NCES/IPEDS enrollment by county

# IPEDS Fall Enrollment distance-education component (EF{year}A_DIST). In-person enrollment is
# students in no distance-ed courses (EFDENON) plus students in some distance-ed / hybrid courses
# (EFDESOM); exclusively online students are excluded because they generate no campus trips.
# Same source and method as the WF-TDM college enrollment repo (5-CollegeEnrollmentIPEDS.qmd).
ipeds_dir = paths["raw"] / "ipeds"
ipeds_dir.mkdir(parents=True, exist_ok=True)
zip_path = ipeds_dir / f"EF{constants['data_year']}A_DIST.zip"

if not zip_path.exists():
    # The datacenter/data path serves 2023; the tablefiles/zipfiles path used in the WF-TDM repo returns 404
    response = requests.get(
        f"https://nces.ed.gov/ipeds/datacenter/data/EF{constants['data_year']}A_DIST.zip"
    )
    response.raise_for_status()
    zip_path.write_bytes(response.content)

# pandas reads the single CSV inside the zip directly, so nothing is extracted to disk
ipeds_dist = pd.read_csv(zip_path)

# IPEDS institutional control (public, private not-for-profit, private for-profit) from the IPEDS
# directory file (HD). Downloaded if missing, and the code labels are checked against its dictionary.
hd_year = constants["data_year"]
hd_zip = ipeds_dir / f"HD{hd_year}.zip"
hd_dict_zip = ipeds_dir / f"HD{hd_year}_Dict.zip"

for url, path in [
    (f"https://nces.ed.gov/ipeds/datacenter/data/HD{hd_year}.zip", hd_zip),
    (f"https://nces.ed.gov/ipeds/datacenter/data/HD{hd_year}_Dict.zip", hd_dict_zip),
]:
    if not path.exists():
        response = requests.get(url)
        response.raise_for_status()
        path.write_bytes(response.content)

with zipfile.ZipFile(hd_dict_zip) as z:
    hd_dict_name = next(n for n in z.namelist() if n.lower().endswith(".xlsx"))
    hd_frequencies = pd.read_excel(z.open(hd_dict_name), sheet_name="Frequencies")
control_rows = hd_frequencies[hd_frequencies["VarName"] == "CONTROL"]
# The dictionary stores codes as text, so convert them to the integers used in the directory file
control_labels = {int(code): label for code, label in zip(control_rows["CodeValue"], control_rows["ValueLabel"])}
assert control_labels.get(1) == "Public", "IPEDS CONTROL code 1 is no longer Public"
assert control_labels.get(2) == "Private not-for-profit", "IPEDS CONTROL code 2 changed"
assert control_labels.get(3) == "Private for-profit", "IPEDS CONTROL code 3 changed"

with zipfile.ZipFile(hd_zip) as z:
    hd_directory = pd.read_csv(z.open(z.namelist()[0]), usecols=["UNITID", "CONTROL"], low_memory=False)
college_control = (
    hd_directory.assign(Control=lambda df: df["CONTROL"].map(control_labels).fillna("Not available"))
    .loc[:, ["UNITID", "Control"]]
)

# Every institution in the study counties that IPEDS covers, using campus-based enrollment: in-person
# plus hybrid (EFDENON + EFDESOM). Exclusively online students (EFDEEXC) are left out, since they don't
# attend a campus in the county. EFDELEV 2 is undergraduate and 12 is graduate (the subtotals add up).
ipeds_levels = {2: "Undergraduate", 12: "Graduate"}
nces_college_locations = gpd.read_file(
    paths["raw"] / "geometry" / "nces_college.gpkg", ignore_geometry=True
)
in_study_counties = nces_college_locations["county_fips"].isin(
    ["003", "011", "035", "049", "057"]
)
college_county = (
    nces_college_locations.loc[in_study_counties, ["UNITID", "county_fips"]]
    .dropna(subset=["UNITID"])
    .assign(UNITID=lambda df: df["UNITID"].astype(int))
    .rename(columns={"county_fips": "COUNTYFP"})
)

campus = ipeds_dist[ipeds_dist["EFDELEV"].isin(ipeds_levels)].copy()
campus[["EFDENON", "EFDESOM"]] = campus[["EFDENON", "EFDESOM"]].apply(pd.to_numeric, errors="coerce").fillna(0)
campus["Level"] = campus["EFDELEV"].map(ipeds_levels)
campus["campus_based"] = campus["EFDENON"] + campus["EFDESOM"]
campus = (
    campus[["UNITID", "Level", "campus_based"]]
    .merge(college_county, on="UNITID")
    .merge(college_control, on="UNITID", how="left")
    .assign(Control=lambda df: df["Control"].fillna("Not available"))
)

# Weber State's Davis campus is counted in Davis County, using its share of USHE headcount among
# the two WSU campuses (Main campus and WSU - Davis Center). The Davis Center is not a separate IPEDS record.
wsu_sites = ushe_2025_fall[
    (ushe_2025_fall["Institution"] == "Weber State University")
    & ushe_2025_fall["Site"].isin(["Main Campus", "WSU - Davis Center"])
].groupby("Site")["Headcount"].sum()
wsu_davis_share = wsu_sites["WSU - Davis Center"] / wsu_sites.sum()

wsu = campus["UNITID"].eq(230782)
wsu_davis = campus[wsu].assign(COUNTYFP="011", campus_based=lambda df: df["campus_based"] * wsu_davis_share)
campus.loc[wsu, "campus_based"] = campus.loc[wsu, "campus_based"] * (1 - wsu_davis_share)
campus = pd.concat([campus, wsu_davis], ignore_index=True)

ipeds_by_county_level = (
    campus.groupby(["COUNTYFP", "Level", "Control"])["campus_based"]
    .sum()
    .round()
    .rename("nces_enrollment")
    .reset_index()
)
ipeds_by_county_level.to_csv(paths["intermediate"] / "real_college_by_county_level.csv", index=False)

ipeds_enrollment_by_county = (
    ipeds_by_county_level.groupby("COUNTYFP")["nces_enrollment"].sum().reset_index()
)
ipeds_by_county_level

	COUNTYFP	Level	Control	nces_enrollment
0	011	Graduate	Public	115.0
1	011	Undergraduate	Private for-profit	422.0
2	011	Undergraduate	Public	8051.0
3	035	Graduate	Private for-profit	74.0
4	035	Graduate	Private not-for-profit	310.0
5	035	Graduate	Public	7869.0
6	035	Undergraduate	Private for-profit	5096.0
7	035	Undergraduate	Private not-for-profit	2561.0
8	035	Undergraduate	Public	46861.0
9	049	Graduate	Private for-profit	1071.0
10	049	Graduate	Private not-for-profit	2767.0
11	049	Graduate	Public	706.0
12	049	Undergraduate	Private for-profit	1642.0
13	049	Undergraduate	Private not-for-profit	31508.0
14	049	Undergraduate	Public	41405.0
15	057	Graduate	Public	574.0
16	057	Undergraduate	Private for-profit	58.0
17	057	Undergraduate	Public	23925.0

IPEDS full-time and part-time (main file)

# IPEDS Fall Enrollment main file (enrollment by level and full- or part-time status) and its data
# dictionary. Downloaded if missing, the same way as the distance-education file above.
ef_year = constants["data_year"]
ef_main_zip = ipeds_dir / f"EF{ef_year}A.zip"
ef_dict_zip = ipeds_dir / f"EF{ef_year}A_Dict.zip"

for url, path in [
    (f"https://nces.ed.gov/ipeds/datacenter/data/EF{ef_year}A.zip", ef_main_zip),
    (f"https://nces.ed.gov/ipeds/datacenter/data/EF{ef_year}A_Dict.zip", ef_dict_zip),
]:
    if not path.exists():
        response = requests.get(url)
        response.raise_for_status()
        path.write_bytes(response.content)

# The codes used below, checked against the IPEDS dictionary, so a change in IPEDS fails here
ef_codes = {
    1: "All students total",
    2: "All students, Undergraduate total",
    12: "All students, Graduate",
    21: "Full-time students total",
    22: "Full-time students, Undergraduate total",
    32: "Full-time students, Graduate",
    41: "Part-time students total",
    42: "Part-time students, Undergraduate total",
    52: "Part-time students, Graduate",
}
with zipfile.ZipFile(ef_dict_zip) as z:
    dict_name = next(n for n in z.namelist() if n.lower().endswith(".xlsx"))
    frequencies = pd.read_excel(z.open(dict_name), sheet_name="Frequencies")
dict_labels = frequencies[frequencies["varname"] == "EFALEVEL"].set_index("codevalue")["valuelabel"]
for code, label in ef_codes.items():
    assert dict_labels.get(code) == label, f"EFALEVEL {code} is '{dict_labels.get(code)}', expected '{label}'"

with zipfile.ZipFile(ef_main_zip) as z:
    ef_main = pd.read_csv(z.open(z.namelist()[0]), usecols=["UNITID", "EFALEVEL", "EFTOTLT"], low_memory=False)

ef_main["EFTOTLT"] = pd.to_numeric(ef_main["EFTOTLT"], errors="coerce")
ef_wide = ef_main.pivot_table(index="UNITID", columns="EFALEVEL", values="EFTOTLT", aggfunc="sum")

# Subtotals must add up for every institution that reports all the codes (they do, as of the 2023 file)
ef_complete = ef_wide[list(ef_codes)].dropna()
assert (ef_complete[1] == ef_complete[21] + ef_complete[41]).all(), "total is not full-time + part-time"
assert (ef_complete[21] == ef_complete[22] + ef_complete[32]).all(), "full-time is not undergrad + grad"
assert (ef_complete[41] == ef_complete[42] + ef_complete[52]).all(), "part-time is not undergrad + grad"
assert (ef_complete[12] == ef_complete[32] + ef_complete[52]).all(), "grad is not full-time + part-time"

ipeds_fulltime_parttime = ef_wide[list(ef_codes)].rename(columns=ef_codes).rename_axis("UNITID").reset_index()
ipeds_fulltime_parttime.to_csv(paths["intermediate"] / "ipeds_fulltime_parttime.csv", index=False)
print(f"institutions with full-time and part-time detail: {len(ef_complete):,} of {len(ef_wide):,}")

institutions with full-time and part-time detail: 1,583 of 5,913

6. Assemble enrollment tables

School Enrollment by School Districts

# Canonical district list -- every district the school crosswalk covers (school points and MAZ rows),
# regardless of whether a given source has a school in it (left-merging onto this keeps a real data
# gap visible as NaN instead of silently dropping the row via an inner join)
district_list = school_crosswalk[["District Name"]].dropna().drop_duplicates()

# Public enrollment only. Private school enrollment (NCES PSS) stays in section 3 and is not part of
# the district table used for validation.
school_enrollment = (
    district_list.merge(doe_enrollment_by_district, on="District Name", how="left")
    .merge(nces_enrollment_by_district, on="District Name", how="left")
)

# Sanity check -- every district should have both DOE and NCES public enrollment; NaN here means
# an actual join problem (e.g. a school missing from the crosswalk), not an expected gap
school_enrollment[school_enrollment[["doe_enrollment", "nces_enrollment"]].isna().any(axis=1)]

	District Name	doe_enrollment	nces_enrollment

K-12 and preschool enrollment by school district, public schools only: Utah DOE 2024-25 (BY2023 UGRC snapshot, SchoolYear 2025, K-12 plus preschool) and NCES public (CCD 2023-24, K-12 plus PK). Blank cells are gaps in the source, not zeros.

districts_table = FileAttachment("_data/intermediate/real_enrollment_by_district.csv").csv({typed: true}).then(rows => rows.map(d => ({
  "District Name": d["District Name"],
  "Utah DOE 2024-25": d.doe_enrollment,
  "NCES CCD 2023-24": d.nces_enrollment
})))

Inputs.table(districts_table, {rows: 25})

School Enrollment by Level

# Enrollment by school level, summed from the grade columns each level covers. A school's own label
# can't split its students by level, since one school can serve several levels.
# Each level maps to its DOE columns and its NCES columns.
school_levels = {
    "Pre-K": (["preschool"], ["PK"]),
    "K": (["k"], ["KG"]),
    "Elementary": ([f"grade{i}" for i in range(1, 6)], [f"G{i:02d}" for i in range(1, 6)]),
    "Middle": ([f"grade{i}" for i in range(6, 9)], [f"G{i:02d}" for i in range(6, 9)]),
    "High": ([f"grade{i}" for i in range(9, 13)], [f"G{i:02d}" for i in range(9, 13)]),
}

doe_by_level = (
    doe_schools.assign(
        **{level: doe_schools[doe_cols].fillna(0).sum(axis=1) for level, (doe_cols, _) in school_levels.items()}
    )
    .groupby("District Name")[list(school_levels)]
    .sum()
)

# NCES negative codes (-2, grade not offered) count as zero, same as the K-12 totals above
nces_levels = nces_public_school[["NCESSCH"]].assign(
    **{
        level: nces_public_school[nces_cols].clip(lower=0).fillna(0).sum(axis=1)
        for level, (_, nces_cols) in school_levels.items()
    }
)
nces_by_level = (
    nces_levels.drop_duplicates()
    .assign(NCESSCH=lambda df: df["NCESSCH"].astype(str))
    .merge(nces_crosswalk[["ID", "District Name"]], left_on="NCESSCH", right_on="ID")
    .groupby("District Name")[list(school_levels)]
    .sum()
)

real_by_level = (
    doe_by_level.reset_index()
    .melt(id_vars="District Name", var_name="Level", value_name="doe_enrollment")
    .merge(
        nces_by_level.reset_index().melt(
            id_vars="District Name", var_name="Level", value_name="nces_enrollment"
        ),
        on=["District Name", "Level"],
        how="outer",
    )
)

# The levels must add back up to the district totals computed above
doe_gap = (
    real_by_level.groupby("District Name")["doe_enrollment"].sum()
    - doe_enrollment_by_district.set_index("District Name")["doe_enrollment"]
)
nces_gap = (
    real_by_level.groupby("District Name")["nces_enrollment"].sum()
    - nces_enrollment_by_district.set_index("District Name")["nces_enrollment"]
)
# In the BY2023 snapshot, Mount Jordan Middle (Canyons) and Alice C. Harris Intermediate (Box Elder)
# each report totalk12 one less than their grade sum, so those two districts are off by one student.
assert doe_gap.abs().max() < 1.5, "DOE levels do not add up to district totals"
assert nces_gap.abs().max() < 0.5, "NCES levels do not add up to district totals"

real_by_level.to_csv(paths["intermediate"] / "real_by_district_level.csv", index=False)
real_by_level

	District Name	Level	doe_enrollment	nces_enrollment
0	ALPINE	Elementary	37951	38176
1	ALPINE	High	31379	30454
2	ALPINE	K	7107	7054
3	ALPINE	Middle	23032	22590
4	ALPINE	Pre-K	2061	2496
5	BOX ELDER	Elementary	4841	4875
6	BOX ELDER	High	4008	3981
7	BOX ELDER	K	943	873
8	BOX ELDER	Middle	2948	2954
9	BOX ELDER	Pre-K	257	346
10	CANYONS	Elementary	12822	13898
11	CANYONS	High	12239	12092
12	CANYONS	K	2187	2353
13	CANYONS	Middle	8324	8775
14	CANYONS	Pre-K	446	494
15	DAVIS COUNTY	Elementary	27581	29562
16	DAVIS COUNTY	High	25655	26476
17	DAVIS COUNTY	K	4794	5141
18	DAVIS COUNTY	Middle	18070	19051
19	DAVIS COUNTY	Pre-K	1684	1773
20	GRANITE	Elementary	24381	25048
21	GRANITE	High	22020	22322
22	GRANITE	K	4490	4365
23	GRANITE	Middle	15415	15623
24	GRANITE	Pre-K	2143	2031
25	JORDAN	Elementary	24145	25247
26	JORDAN	High	22845	23537
27	JORDAN	K	4100	4210
28	JORDAN	Middle	16055	16390
29	JORDAN	Pre-K	1780	1757
30	MURRAY CITY	Elementary	2058	2528
31	MURRAY CITY	High	1822	2631
32	MURRAY CITY	K	381	417
33	MURRAY CITY	Middle	1278	1750
34	MURRAY CITY	Pre-K	147	139
35	NEBO	Elementary	14282	18548
36	NEBO	High	12397	14036
37	NEBO	K	2808	3426
38	NEBO	Middle	9059	11135
39	NEBO	Pre-K	889	952
40	OGDEN CITY	Elementary	5157	5202
41	OGDEN CITY	High	4291	4964
42	OGDEN CITY	K	1053	1048
43	OGDEN CITY	Middle	2862	2854
44	OGDEN CITY	Pre-K	286	307
45	PROVO CITY	Elementary	5889	5846
46	PROVO CITY	High	5038	4959
47	PROVO CITY	K	1048	1178
48	PROVO CITY	Middle	3510	3578
49	PROVO CITY	Pre-K	434	423
50	SALT LAKE CITY	Elementary	8490	8462
51	SALT LAKE CITY	High	6824	7785
52	SALT LAKE CITY	K	1536	1510
53	SALT LAKE CITY	Middle	5199	5159
54	SALT LAKE CITY	Pre-K	680	647
55	WEBER COUNTY	Elementary	13112	13459
56	WEBER COUNTY	High	11223	10943
57	WEBER COUNTY	K	2304	2315
58	WEBER COUNTY	Middle	8586	8684
59	WEBER COUNTY	Pre-K	302	315

College Enrollment by Counties

county_list = college_crosswalk[["COUNTYFP"]].dropna().drop_duplicates()

college_enrollment = county_list.merge(
    ushe_enrollment_by_county.rename(columns={"county_fips": "COUNTYFP"}), on="COUNTYFP", how="left"
).merge(ipeds_enrollment_by_county, on="COUNTYFP", how="left")

# Same sanity check, county side
college_enrollment[college_enrollment[["ushe_enrollment", "nces_enrollment"]].isna().any(axis=1)]

	COUNTYFP	ushe_enrollment	nces_enrollment
3	003	366	NaN

College enrollment by county: USHE (Academic Year 2025, Term 2, full-time and part-time) and IPEDS in-person (fall 2023). USHE covers public institutions only; private institutions such as BYU appear only in IPEDS. The IPEDS column covers the seven target colleges, with Weber State’s Davis campus split out by USHE share. The sources also differ in year and method, so compare them with care.

counties_table = FileAttachment("_data/intermediate/real_enrollment_by_county.csv").csv({typed: true}).then(rows => rows.map(d => ({
  County: ({3: "Box Elder", 11: "Davis", 35: "Salt Lake", 49: "Utah", 57: "Weber"})[d.COUNTYFP],
  "USHE (AY 2025)": d.ushe_enrollment,
  "IPEDS in-person (fall 2023)": d.nces_enrollment
})))

Inputs.table(counties_table)

paths["output"].mkdir(parents=True, exist_ok=True)
school_enrollment.to_csv(paths["intermediate"] / "real_enrollment_by_district.csv", index=False)
college_enrollment.to_csv(paths["intermediate"] / "real_enrollment_by_county.csv", index=False)

7. Compare the sources

The two charts below read the CSVs written in section 6 directly, so they update on every render. Hover over a point or bar to see its values.

districts = FileAttachment("_data/intermediate/real_enrollment_by_district.csv").csv({typed: true})

max_district = d3.max(districts, d => Math.max(d.doe_enrollment ?? 0, d.nces_enrollment ?? 0))

Plot.plot({
  width: 720,
  height: 520,
  grid: true,
  marginLeft: 90,
  marginBottom: 70,
  marginTop: 20,
  marginRight: 20,
  style: {background: "transparent", color: "inherit"},
  x: {label: "Utah DOE enrollment, 2024-25 (UGRC school counts)"},
  y: {label: "NCES CCD enrollment, 2023-24"},
  marks: [
    Plot.line([[0, 0], [max_district, max_district]], {stroke: "#999", strokeDasharray: "4 4"}),
    Plot.dot(districts, {
      x: "doe_enrollment",
      y: "nces_enrollment",
      fill: "#1f78b4",
      r: 4,
      title: d => d["District Name"],
      tip: {
        fill: "white",
        stroke: "#374151",
        format: {
          x: d => `DOE 2024-25: ${d.toLocaleString()}`,
          y: d => `NCES 2023-24: ${d.toLocaleString()}`
        }
      }
    })
  ]
})

(a)

(b)

(c)

Figure 1: Public K-12 and preschool enrollment by district: Utah DOE 2024-25 (UGRC school counts) against NCES CCD 2023-24. Points on the dashed line agree exactly; points far from it are districts where the two sources disagree most.

college_long = counties_table.flatMap(d => [
  {county: d.County, source: "USHE (AY 2025)", headcount: d["USHE (AY 2025)"]},
  {county: d.County, source: "IPEDS in-person (fall 2023)", headcount: d["IPEDS in-person (fall 2023)"]}
])

Plot.plot({
  width: 720,
  height: 420,
  grid: true,
  marginLeft: 90,
  marginBottom: 70,
  marginTop: 20,
  marginRight: 20,
  style: {background: "transparent", color: "inherit"},
  fx: {label: null},
  x: {axis: null},
  y: {label: "Headcount", grid: true},
  color: {legend: true, label: "Source"},
  marks: [
    Plot.barY(college_long, {
      fx: "county",
      x: "source",
      y: "headcount",
      fill: "source",
      title: d => `${d.county}, ${d.source}`,
      tip: {
        fill: "white",
        stroke: "#374151",
        format: {
          fx: false,
          x: false,
          fill: false,
          y: d => `Headcount: ${d.toLocaleString()}`
        }
      }
    })
  ]
})

(a)

(b)

Figure 2: College headcount by county from USHE (Academic Year 2025, full-time and part-time, public institutions only) and IPEDS in-person (fall 2023, seven target colleges as in the WF-TDM college repo). Sources and years differ, so read the pattern across counties rather than the gap between bars.

8. Universities: USHE vs IPEDS by modality

university_names = {
    230764: "University of Utah",
    230746: "Salt Lake Community College",
    230782: "Weber State University",
    230737: "Utah Valley University",
    230038: "Brigham Young University",
    230418: "Ensign College",
    230807: "Westminster University",
}
ushe_names = {
    "University Of Utah": "University of Utah",
    "Salt Lake Community College": "Salt Lake Community College",
    "Weber State University": "Weber State University",
    "Utah Valley University": "Utah Valley University",
}

# IPEDS, fall 2023 (same definitions as section 5)
ipeds_long = (
    ipeds_dist[ipeds_dist["EFDELEV"] == 1]
    .loc[lambda df: df["UNITID"].isin(list(university_names))]
    .assign(
        Institution=lambda df: df["UNITID"].map(university_names),
        **{
            "In-person only": lambda df: df["EFDENON"].fillna(0),
            "Hybrid (some DE)": lambda df: df["EFDESOM"].fillna(0),
            "Online only": lambda df: df["EFDEEXC"].fillna(0),
        },
    )
    .melt(
        id_vars=["Institution"],
        value_vars=["In-person only", "Hybrid (some DE)", "Online only"],
        var_name="Segment",
        value_name="Students",
    )
    .assign(Source="IPEDS")
)

# USHE, Academic Year 2025 Term 2 (full- and part-time). The "taught online" rows are the hybrid
# portion; USHE has no online-only category.
ushe_long = (
    ushe_2025_fall[ushe_2025_fall["Institution"].isin(list(ushe_names))]
    .assign(
        Institution=lambda df: df["Institution"].map(ushe_names),
        Segment=lambda df: df["Site"].eq("This portion of the course is taught online").map(
            {True: "Hybrid (some DE)", False: "In-person only"}
        ),
    )
    .groupby(["Institution", "Segment"], as_index=False)["Headcount"]
    .sum()
    .rename(columns={"Headcount": "Students"})
    .assign(Source="USHE")
)

modality_compare = pd.concat([ipeds_long, ushe_long], ignore_index=True)[
    ["Institution", "Source", "Segment", "Students"]
]
modality_compare.to_csv(paths["intermediate"] / "university_modality_compare.csv", index=False)
modality_compare

	Institution	Source	Segment	Students
0	Brigham Young University	IPEDS	In-person only	22188.0
1	Ensign College	IPEDS	In-person only	764.0
2	Utah Valley University	IPEDS	In-person only	23719.0
3	Salt Lake Community College	IPEDS	In-person only	15865.0
4	University of Utah	IPEDS	In-person only	17616.0
5	Weber State University	IPEDS	In-person only	17277.0
6	Westminster University	IPEDS	In-person only	998.0
7	Brigham Young University	IPEDS	Hybrid (some DE)	12087.0
8	Ensign College	IPEDS	Hybrid (some DE)	910.0
9	Utah Valley University	IPEDS	Hybrid (some DE)	14634.0
10	Salt Lake Community College	IPEDS	Hybrid (some DE)	5831.0
11	University of Utah	IPEDS	Hybrid (some DE)	15418.0
12	Weber State University	IPEDS	Hybrid (some DE)	8498.0
13	Westminster University	IPEDS	Hybrid (some DE)	199.0
14	Brigham Young University	IPEDS	Online only	799.0
15	Ensign College	IPEDS	Online only	4299.0
16	Utah Valley University	IPEDS	Online only	6298.0
17	Salt Lake Community College	IPEDS	Online only	5068.0
18	University of Utah	IPEDS	Online only	2226.0
19	Weber State University	IPEDS	Online only	4761.0
20	Westminster University	IPEDS	Online only	17.0
21	Salt Lake Community College	USHE	In-person only	23802.0
22	University of Utah	USHE	In-person only	44633.0
23	Utah Valley University	USHE	Hybrid (some DE)	18046.0
24	Utah Valley University	USHE	In-person only	23211.0
25	Weber State University	USHE	Hybrid (some DE)	11386.0
26	Weber State University	USHE	In-person only	13056.0

uni_modality_compare = FileAttachment("_data/intermediate/university_modality_compare.csv").csv({typed: true})

Plot.plot({
  width: 760,
  height: 110 * new Set(uni_modality_compare.map(d => d.Institution)).size + 60,
  marginLeft: 200,
  marginRight: 30,
  marginBottom: 50,
  grid: true,
  style: {background: "transparent", color: "inherit"},
  fy: {label: null},
  x: {label: "Students", grid: true},
  y: {label: null},
  color: {legend: true, domain: ["In-person only", "Hybrid (some DE)", "Online only"], range: ["#1f78b4", "#f59e0b", "#9ca3af"]},
  marks: [
    Plot.barX(uni_modality_compare, {
      fy: "Institution",
      y: "Source",
      x: "Students",
      fill: "Segment",
      title: d => `${d.Institution}, ${d.Source}, ${d.Segment}: ${d.Students.toLocaleString()}`,
      tip: {fill: "white", stroke: "#374151"}
    })
  ]
})

Figure 3: USHE vs IPEDS enrollment by modality. IPEDS is fall 2023 and includes private institutions. USHE is Academic Year 2025 Term 2, full- and part-time, public institutions only; its hybrid bar is the ‘taught online’ portion of courses.

IPEDS and USHE are not the same definition. USHE counts a student once per campus site they use, so its totals can run higher than the institution’s headcount.