Synthetic Population

PopulationSim 2023 synthetic enrollment estimates, by district and county

Overview

This script produces the “synthetic” half of the validation schema: PopulationSim’s estimate of how many K-12 students and college students live in each district and county, to compare against the real enrollment numbers from 2_enrollment.qmd.

  • Objective: count synthetic persons by school district and by county, to compare with DOE and NCES. By district: sch_grade (every person with SCHG 1-14, at any age, matching DOE’s totalk12 + preschool and NCES’s K-12 + PK) and age_3_18 (every person aged 3-18, counted independently, a sanity bound since age is controlled in PopulationSim). By county: undergraduate (SCHG 15) and graduate (SCHG 16) at any age, and age_18_24 (population aged 18-24, a sanity bound).
  • What it reads: PopulationSim’s 2023 synthetic person output (local-only, under _data/raw/populationsim/ABM/1_Inputs/ScenarioBaseSample/1_PopulationSim/output/, gitignored). The dorm and group-quarters students are in the same file, so no second file is needed. It also reads the school and college crosswalks from 1_geography.qmd.
  • MAZ join: each person has a maz_id. The MAZ rows in crosswalk_school.csv give each person a school district, and the MAZ rows in crosswalk_college.csv give each person a county.
  • Known gaps: college counts are residence-based and include every enrolled resident, while IPEDS counts by institution location and USHE counts public institutions only. The two sides differ in definition, not only in data.
  • Box Elder: handled last. The MPO/TDM model region only partly covers the county, so its synthetic counts need to be clipped to the model boundary (1_geography.qmd’s model_boundary layer) before comparison. This is still a TODO.
  • Output: written to _data/intermediate/. synthetic_k12_by_district.csv has district persons (sch_grade, age_3_18). synthetic_college_by_county.csv has county persons (undergraduate, graduate, age_18_24). synthetic_by_district_level.csv and synthetic_age_by_district.csv hold the school-level and single-year-age breakdowns that the validation charts use.

Setup

Show code 
import pandas as pd
from pathlib import Path

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

synthetic_dir = (
    paths["raw"] / "populationsim" / "ABM" / "1_Inputs" / "ScenarioBaseSample" / "1_PopulationSim" / "output"
)

1. Load synthetic persons

Show code 
# Every person's household is in the households file, and res_type 3 marks group-quarters (dorm)
# households, so the dorm students are already in this one file.
persons = pd.read_csv(
    synthetic_dir / "Persons_Output4ABMwithStudentsInDorm.csv", usecols=["maz_id", "age", "sch_grade"]
)

2. Classify students

Show code 
# Same rules as PopulationSim's PersonType_Label (2_InputPreperation_Seed2.ipynb, person-type rules):
# SCHG 1-14 is pre-college and SCHG 15 or higher is college. K-12 is pre-college at ages 6-18;
# University student is college at age 16 or older.
# College at any age: SCHG 15 is undergraduate and SCHG 16 (beyond bachelor's) is graduate
persons["undergraduate"] = persons["sch_grade"].eq(15)
persons["graduate"] = persons["sch_grade"].eq(16)
persons["age_18_24"] = persons["age"].between(18, 24)
# Nursery/preschool (SCHG 1) through grade 12 (SCHG 14), at any age. Matches DOE's totalk12 + preschool
# and NCES's K-12 + PK. Age is counted separately below, so this flag has no age condition.
persons["grade_1_14"] = persons["sch_grade"].between(1, 14)
persons["age_3_18"] = persons["age"].between(3, 18)

3. Join persons to district and county

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

maz_district = crosswalk_school[crosswalk_school["Unit"] == "maz"].set_index("ID")["District Name"]
maz_county = crosswalk_college[crosswalk_college["Unit"] == "maz"].set_index("ID")["COUNTYFP"]

persons["maz_id"] = persons["maz_id"].astype(str)
persons["District Name"] = persons["maz_id"].map(maz_district)
persons["COUNTYFP"] = persons["maz_id"].map(maz_county)

print(
    "persons with no district:", persons["District Name"].isna().sum(),
    "| persons with no county:", persons["COUNTYFP"].isna().sum(),
)
persons with no district: 0 | persons with no county: 0

4. Aggregate to district and county

Show code 
# Each column counts persons in that district (summing a boolean counts the True rows)
district_persons = (
    persons.groupby("District Name")
    .agg(
        sch_grade=("grade_1_14", "sum"),
        age_3_18=("age_3_18", "sum"),
    )
    .reset_index()
)
district_persons
District Name sch_grade age_3_18
0 ALPINE 120162 140251
1 BOX ELDER 7743 8971
2 CANYONS 46008 51456
3 DAVIS COUNTY 91690 106713
4 GRANITE 85374 97352
5 JORDAN 85360 92126
6 MURRAY CITY 6495 7237
7 NEBO 46391 52687
8 OGDEN CITY 14278 16778
9 PROVO CITY 15538 21858
10 SALT LAKE CITY 25695 31346
11 WEBER COUNTY 39324 45357
Show code 
college_persons = (
    persons.groupby("COUNTYFP")
    .agg(
        undergraduate=("undergraduate", "sum"),
        graduate=("graduate", "sum"),
        age_18_24=("age_18_24", "sum"),
    )
    .reset_index()
)
college_persons
COUNTYFP undergraduate graduate age_18_24
0 003 1126 178 2705
1 011 16257 3382 35640
2 035 66494 15234 121371
3 049 80873 10522 123869
4 057 13505 2223 26652

By school level

Show code 
# Levels by SCHG code, at any age, matching the grade columns used for real enrollment in 2_enrollment.qmd:
# Pre-K is nursery (1), K is kindergarten (2), and the rest are grade ranges.
level_codes = {
    "Pre-K": [1],
    "K": [2],
    "Elementary": list(range(3, 8)),
    "Middle": list(range(8, 11)),
    "High": list(range(11, 15)),
}

synthetic_by_level = pd.concat(
    [
        persons[persons["sch_grade"].isin(codes)]
        .groupby("District Name")
        .size()
        .rename("sch_grade")
        .reset_index()
        .assign(Level=level)
        for level, codes in level_codes.items()
    ],
    ignore_index=True,
)

# The levels together cover SCHG 1-14, so they must add back up to the district sch_grade totals
level_total = synthetic_by_level.groupby("District Name")["sch_grade"].sum()
assert (level_total == district_persons.set_index("District Name")["sch_grade"]).all(), "levels do not add up"

# Single-year ages, so the validation chart can count each person once when several levels are picked
synthetic_age_by_district = (
    persons[persons["age"].between(3, 18)]
    .groupby(["District Name", "age"])
    .size()
    .rename("persons")
    .reset_index()
)
synthetic_by_level
District Name sch_grade Level
0 ALPINE 9453 Pre-K
1 BOX ELDER 677 Pre-K
2 CANYONS 4574 Pre-K
3 DAVIS COUNTY 6149 Pre-K
4 GRANITE 4686 Pre-K
5 JORDAN 7078 Pre-K
6 MURRAY CITY 417 Pre-K
7 NEBO 3240 Pre-K
8 OGDEN CITY 890 Pre-K
9 PROVO CITY 1232 Pre-K
10 SALT LAKE CITY 1828 Pre-K
11 WEBER COUNTY 2445 Pre-K
12 ALPINE 11920 K
13 BOX ELDER 489 K
14 CANYONS 2506 K
15 DAVIS COUNTY 7083 K
16 GRANITE 6174 K
17 JORDAN 5060 K
18 MURRAY CITY 490 K
19 NEBO 4056 K
20 OGDEN CITY 1165 K
21 PROVO CITY 1076 K
22 SALT LAKE CITY 1712 K
23 WEBER COUNTY 3620 K
24 ALPINE 45180 Elementary
25 BOX ELDER 2421 Elementary
26 CANYONS 17031 Elementary
27 DAVIS COUNTY 36420 Elementary
28 GRANITE 33570 Elementary
29 JORDAN 32434 Elementary
30 MURRAY CITY 2349 Elementary
31 NEBO 17487 Elementary
32 OGDEN CITY 5411 Elementary
33 PROVO CITY 4699 Elementary
34 SALT LAKE CITY 9397 Elementary
35 WEBER COUNTY 12899 Elementary
36 ALPINE 23750 Middle
37 BOX ELDER 1642 Middle
38 CANYONS 9737 Middle
39 DAVIS COUNTY 18776 Middle
40 GRANITE 18607 Middle
41 JORDAN 18463 Middle
42 MURRAY CITY 1338 Middle
43 NEBO 11112 Middle
44 OGDEN CITY 2935 Middle
45 PROVO CITY 3921 Middle
46 SALT LAKE CITY 6055 Middle
47 WEBER COUNTY 9508 Middle
48 ALPINE 29859 High
49 BOX ELDER 2514 High
50 CANYONS 12160 High
51 DAVIS COUNTY 23262 High
52 GRANITE 22337 High
53 JORDAN 22325 High
54 MURRAY CITY 1901 High
55 NEBO 10496 High
56 OGDEN CITY 3877 High
57 PROVO CITY 4610 High
58 SALT LAKE CITY 6703 High
59 WEBER COUNTY 10852 High

5. Box Elder clipping

Show code 
# TODO: using 1_geography.qmd's model_boundary layer, flag which Box Elder MAZs fall inside vs.
#   outside the MPO/TDM model domain, and handle Box Elder separately from the other 4 counties

6. Write output

Show code 
district_persons.to_csv(paths["intermediate"] / "synthetic_k12_by_district.csv", index=False)
college_persons.to_csv(
    paths["intermediate"] / "synthetic_college_by_county.csv", index=False
)
synthetic_by_level.to_csv(paths["intermediate"] / "synthetic_by_district_level.csv", index=False)
synthetic_age_by_district.to_csv(
    paths["intermediate"] / "synthetic_age_by_district.csv", index=False
)