Repository navigation
Output Files
This page documents all output files generated by SANDAG PopulationSim, their formats, and how to use them.
output/
└── 2022/
├── synthetic_households.csv # Combined HH+GQ households
├── synthetic_persons.csv # Combined HH+GQ persons
├── mgra15_based_input_2022.csv # MGRA spatial reference for ABM
│
├── final_summary_mgra.csv # MGRA-level household controls
├── final_summary_mgra_PUMA.csv # PUMA-level household controls
├── final_summary_region_1.csv # Regional household controls
│
├── final_summary_mgra_gq_mil.csv # MGRA-level military GQ
├── final_summary_mgra_gq_col.csv # MGRA-level college GQ
├── final_summary_mgra_gq_oth.csv # MGRA-level other GQ
│
├── final_summary_mgra_PUMA_gq_mil.csv # PUMA-level military GQ
├── final_summary_mgra_PUMA_gq_col.csv # PUMA-level college GQ
├── final_summary_mgra_PUMA_gq_oth.csv # PUMA-level other GQ
│
├── final_summary_region_1_gq_mil.csv # Regional military GQ
├── final_summary_region_1_gq_col.csv # Regional college GQ
├── final_summary_region_1_gq_oth.csv # Regional other GQ
│
├── mgra_controls.csv # Household controls used
├── mgra_controls_gq_mil.csv # Military GQ controls by MGRA
├── mgra_controls_gq_col.csv # College GQ controls by MGRA
├── mgra_controls_gq_oth.csv # Other GQ controls by MGRA
├── region_controls.csv # Regional controls used
├── reporting_controls.csv # Report-only benchmark definitions, when configured
├── benchmark_comparisons.csv # Benchmark results, when definitions are configured
│
├── timing_log.csv # Household run timing
├── timing_log_gq_mil.csv # Military GQ run timing
├── timing_log_gq_col.csv # College GQ run timing
└── timing_log_gq_oth.csv # Other GQ run timing
| File Type | Purpose | Primary User | Format |
|---|---|---|---|
synthetic_*.csv |
Synthesis output (combined HH+GQ) | ABM team | CSV, detailed attributes |
mgra15_based_input_*.csv |
MGRA-level summary for ABM | ABM team | CSV, zonal data |
final_summary_*.csv |
Validation control totals | Technical staff | CSV, control vs result |
final_summary_*_gq_*.csv |
GQ validation by type | Technical staff | CSV, GQ control vs result |
*_controls.csv |
Control definitions | QA validation | CSV, metadata |
reporting_controls.csv |
Report-only benchmark definitions; not used for fitting | QA/reporting | CSV |
benchmark_comparisons.csv |
Saved benchmark target/result comparisons | QA, Power BI/data lake consumers | CSV |
timing_log*.csv |
Performance metrics | Technical staff | CSV, timing data |
Purpose: Complete household-level synthesis results (combined regular households and GQ)
Schema:
| Column | Type | Description | Example |
|---|---|---|---|
household_id |
int | Unique household identifier | 1, 2, 3, ... |
mgra |
int | Master Geographic Reference Area | 57, 420, 23142, ... |
integer_weight |
float | Final integer weight after balancing | 1.0, 2.0, 3.0, ... |
SERIALNO |
varchar | PUMS household serial number | 2017000025834 |
NP |
float | Number of persons in household | 1.0, 2.0, 3.0, 4.0, 5.0+ |
HHADJINC |
float | Adjusted household income ($) | 98696.0, 231224.0, ... |
HHT |
float | Household/family type | 3.0, 4.0, ... |
HUPAC |
float | HH presence and age of children | 4.0 (no children), 1-3 (with children) |
VEH |
float | Vehicles available | 0.0, 1.0, 2.0, 3.0+ |
BLD |
float | Building type | 2.0 (single-family), 6.0 (apt), ... |
gq_type |
int | Group quarters type (0=not GQ) | 0 |
workers |
int | Number of workers in household | 0, 1, 2, 3+ |
Row Count: 1,276,883 households (2022)
Example:
household_id,mgra,integer_weight,SERIALNO,NP,HHADJINC,HHT,HUPAC,VEH,BLD,gq_type,workers
1,57,1.0,2017000025834,3.0,98696.0,3.0,4.0,1.0,6.0,0,2
2,57,1.0,2017000093566,1.0,231224.0,4.0,4.0,2.0,2.0,0,1Purpose: Complete person-level synthesis results (combined regular households and GQ)
Schema:
| Column | Type | Description | Example |
|---|---|---|---|
mgra |
int | Master Geographic Reference Area | 57, 420, ... |
integer_weight |
float | Final integer weight | 1.0, 2.0, ... |
household_id |
int | Links to household file | 1, 2, 3, ... |
SERIALNO |
varchar | PUMS household serial number | 2017000025834 |
SPORDER |
float | Person number within household | 1.0, 2.0, 3.0, ... |
AGEP |
float | Age in years | 64.0, 29.0, ... |
SEX |
int | Sex (1=Male, 2=Female) | 1, 2 |
ESR |
float | Employment status recode | 1.0 (employed), 3.0 (unemployed), 6.0 (not in LF) |
COW |
float | Class of worker | 1.0 (private), 2.0 (government), 5.0 (self) |
WKHP |
float | Usual hours worked per week | 15.0, 40.0, ... |
SCHG |
int | School enrollment grade level | 0 (not enrolled), 14, 16, ... |
RAC1P |
int | Recoded detailed race code | 1-9 |
HISP |
int | Hispanic origin | 1 (not), 3, 23, ... |
MIL |
float | Military service | 4.0 (never served), 1.0 (active duty) |
SCHL |
float | Educational attainment | 19.0, 21.0, ... |
OCCP |
float | Occupation code | 2050.0, 4700.0, ... |
WKW |
float | Weeks worked last year | 1.0 (50-52 weeks), ... |
NAICSP |
int | NAICS industry code | 8131, 4481, ... |
NAICS2 |
int | NAICS 2-digit code | 81, 44, ... |
SOCP |
int | SOC occupation code | 212021, 411011, ... |
SOC2 |
float | SOC 2-digit code | 21.0, 41.0, ... |
Row Count: 3,283,519 persons (2022)
Example:
mgra,integer_weight,household_id,SERIALNO,SPORDER,AGEP,SEX,ESR,COW,WKHP,SCHG,RAC1P,HISP,MIL,SCHL,OCCP,WKW,NAICSP,NAICS2,SOCP,SOC2
57,1.0,1,2017000025834,1.0,64.0,2,1.0,2.0,15.0,0,9,23,4.0,21.0,2050.0,1.0,8131,81,212021,21.0
57,1.0,1,2017000025834,2.0,29.0,1,1.0,1.0,40.0,0,9,3,4.0,19.0,4700.0,1.0,4481,44,411011,41.0Notes:
- Group quarters (GQ) households and persons are included in the main files with
gq_typefield indicating type (0=regular, 1=military, 2=college, 3=other) - Total population = regular households + GQ households
- To filter for regular households only:
df[df['gq_type'] == 0] - To filter for GQ only:
df[df['gq_type'] > 0]
Purpose: MGRA-level summary file with demographic totals for ABM Series 15
Note: "mgra15" refers to Series 15 land use data format, not 15 MGRAs
Schema (selected columns):
| Column | Type | Description | Source |
|---|---|---|---|
mgra |
int | Master Geographic Reference Area | Geography |
taz |
int | Traffic Analysis Zone | Land use data |
LUZ |
int | Land Use Zone | Land use data |
pop |
int | Total population | Synthesis + GQ |
hhp |
int | Household population | Excludes group quarters |
hs |
int | Housing supply (units) | Land use data |
hs_sf |
int | Single-family housing supply | Land use data |
hs_mf |
int | Multi-family housing supply | Land use data |
hs_mh |
int | Mobile home housing supply | Land use data |
hh |
int | Total households | Synthesis results |
hh_sf |
int | Single-family households | Synthesis results |
hh_mf |
int | Multi-family households | Synthesis results |
hh_mh |
int | Mobile home households | Synthesis results |
hhs |
float | Average household size | pop / hh |
gq_civ |
int | Civilian group quarters population | GQ synthesis |
gq_mil |
int | Military group quarters population | GQ synthesis |
i1 - i10
|
int | Income categories | Control totals |
emp_total |
int | Total employment | UDM + Economic Team |
emp_gov |
int | Government employment | Economic Team |
emp_mil |
int | Military employment | Economic Team |
emp_ag_min |
int | Agriculture/mining employment | Economic Team |
| ... | ... | (40+ employment categories) | Economic Team |
enrollgradekto8 |
int | K-8 enrollment | Land use data |
enrollgrade9to12 |
int | 9-12 enrollment | Land use data |
majorcollegeenroll_total |
int | College enrollment | Land use data |
zip |
int | ZIP code | Geography |
pseudomsa |
int | Pseudo MSA code | Geography |
district27 |
int | District 27 indicator | Geography |
milestocoast |
float | Distance to coast (miles) | Geography |
acre |
float | Total acres | Geography |
landacre |
float | Land area (acres) | Geography |
truckregiontype |
int | Truck region type | Transportation |
Row Count: 24,321 MGRAs
Example:
mgra,taz,LUZ,pop,hhp,hs,hh,hhs,gq_civ,gq_mil,emp_total,zip,...
21276,4727,215,103,103,30,27,3.815,0,0,6,92065,...
420,2541,6,192,192,71,68,2.824,0,0,42,92105,...
23142,3368,211,0,0,0,0,0.0,0,0,46,92025,...Usage: ABM reads this file for zonal demographic and employment data
Purpose: Regional-level control totals vs. synthesis results with balancing stages
Schema:
| Column | Description |
|---|---|
control_name |
Control variable name (e.g., "Total_HH") |
control_value |
Target from UDM/Economic Team |
region_preliminary_balanced_weight |
After region-level IPF |
region_balanced_weight |
After PUMA-level balancing |
region_integer_weight |
After integerization |
mgra_balanced_weight |
After MGRA sub-balancing |
mgra_integer_weight |
Final result (after MGRA integerization) |
Example:
control_name,control_value,region_preliminary_balanced_weight,region_balanced_weight,region_integer_weight,mgra_balanced_weight,mgra_integer_weight
Total_HH,1160472.0,1160472.0006298593,1160472.0001404851,1160472,1160472.0,1160472
HHSize_1,278544.0,278539.73085250036,278539.6579843013,278538,278538.0,278207
HHSize_2,381757.0,381756.01274555293,381755.8576917914,381761,381761.0,382314Purpose: PUMA-level control totals vs. synthesis results (22 PUMAs)
Schema:
-
geography: "PUMA" -
id: PUMA code (7301-7322) - For each control: -
{control_name}_control: Target value-
{control_name}_result: Synthesis result -
{control_name}_diff: Difference (result - control)
-
Row Count: 22 rows (one per PUMA)
Example: (truncated for readability)
geography,id,Total_HH_control,Total_HH_result,Total_HH_diff,HHSize_1_control,HHSize_1_result,...
PUMA,7301,63802,63802,0,13814,13812,-2,...
PUMA,7302,43077,43077,0,9166,8923,-243,...Purpose: MGRA-level control totals vs. synthesis results
Schema: Same as final_summary_mgra_PUMA.csv but with geography = "mgra" and id = MGRA number
Row Count: ~24,321 MGRAs × 56 controls (with sparse storage)
Use Case: Detailed validation, identifying problematic MGRAs
reporting_controls.csv defines reporting-only benchmarks. These rows are not passed to PopulationSim as fitting controls. During output organization, the run's definitions are archived and benchmark_comparisons.csv is generated when benchmark definitions are available.
The comparisons file has one row per benchmark and geography, and includes a region total for MGRA benchmarks when a region target is not already reported. Its columns are:
| Column | Description |
|---|---|
target |
Benchmark name |
control_field |
Field in the saved geography controls used as the target |
geography |
mgra or region
|
id |
Geography identifier; region uses 1
|
control_value |
Saved benchmark target |
result |
Count calculated from the final synthetic population |
The file is included in the Azure Data Lake export with the other year-level CSV outputs. See Validation & QA for how benchmark-only rows differ from fitting controls.
| File | Size | Rows |
|---|---|---|
| ABM Deliverables | ||
synthetic_households.csv |
~77 MB | 1,276,883 |
synthetic_persons.csv |
~292 MB | 3,283,519 |
mgra15_based_input_2022.csv |
~4.8 MB | 24,321 |
| Validation Summaries | ||
final_summary_mgra.csv |
~5.1 MB | 24,321 |
final_summary_mgra_PUMA.csv |
~20 KB | 22 |
final_summary_region_1.csv |
~5 KB | 54 |
final_summary_mgra_gq_mil.csv |
~100 KB | varies |
final_summary_mgra_gq_col.csv |
~100 KB | varies |
final_summary_mgra_gq_oth.csv |
~100 KB | varies |
| Control Totals | ||
mgra_controls.csv |
~2.6 MB | 24,321 |
mgra_controls_gq_mil.csv |
~50 KB | varies |
mgra_controls_gq_col.csv |
~50 KB | varies |
mgra_controls_gq_oth.csv |
~50 KB | varies |
region_controls.csv |
<1 KB | few |
| Performance Metrics | ||
timing_log.csv |
<1 KB | ~30 |
timing_log_gq_mil.csv |
<1 KB | ~11 |
timing_log_gq_col.csv |
<1 KB | ~11 |
timing_log_gq_oth.csv |
<1 KB | ~11 |
| Total per year | ~382 MB | varies |
Purpose: Regional-level control definitions (high-level)
Size: <1 KB
Purpose: Group quarters validation by type (exact match expected)
Files:
-
final_summary_mgra_gq_mil.csv- Military GQ -
final_summary_mgra_gq_col.csv- College GQ -
final_summary_mgra_gq_oth.csv- Other GQ
Schema (each file):
| Column | Description |
|---|---|
id |
MGRA number |
geography |
"mgra" |
Total_GQ_control |
GQ target for this type |
Total_GQ_result |
GQ result for this type |
Total_GQ_diff |
Difference (should be 0) |
Example (final_summary_mgra_gq_mil.csv):
id,geography,Total_GQ_control,Total_GQ_result,Total_GQ_diff
1234,mgra,150,150,0
5678,mgra,85,85,0Note: Each file only includes MGRAs that have that specific GQ type.
Purpose: Performance timing data for each processing step
Schema:
| Column | Description |
|---|---|
process_name |
Name of the process (e.g., "mp_seed_balancing") |
model_name |
Sub-model name (e.g., "input_pre_processor") |
seconds |
Elapsed time in seconds |
minutes |
Elapsed time in minutes |
notes |
Additional information |
Example:
process_name,model_name,seconds,minutes,notes
mp_seed_balancing,input_pre_processor,2.2,0.0,
mp_seed_balancing,setup_data_structures,1.7,0.0,
mp_seed_balancing,initial_seed_balancing,54.6,0.9,
mp_seed_balancing,meta_control_factoring,0.1,0.0,| File | Typical Size | Rows |
|---|---|---|
final_households.csv |
150-200 MB | 1.2-1.5M |
final_persons.csv |
400-500 MB | 3.5-4.0M |
abm/households.csv |
80-100 MB | 1.2-1.5M |
abm/persons.csv |
200-250 MB | 3.5-4.0M |
abm/mgra_based_input_YYYY.csv |
1-2 MB | 23,000 |
summary_region.csv |
<1 KB | 56 |
summary_puma.csv |
50-100 KB | 1,232 |
summary_mgra.csv |
100-150 MB | 1,288,000 |
| Total per year | ~1.0-1.2 GB |
1. File Existence:
# Verify all expected files exist
Test-Path output/2022/synthetic_households.csv
Test-Path output/2022/synthetic_persons.csv
Test-Path output/2022/mgra15_based_input_2022.csv
Test-Path output/2022/final_summary_region_1.csv
Test-Path output/2022/final_summary_mgra.csv
Test-Path output/2022/timing_log.csv2. Row Counts:
import pandas as pd
# Households (includes regular HH + GQ)
hh = pd.read_csv('output/2022/synthetic_households.csv')
print(f"Total households: {len(hh):,}") # Should be ~1,276,883
print(f" Regular HH: {len(hh[hh['gq_type']==0]):,}") # Should be ~1,160,472
print(f" GQ: {len(hh[hh['gq_type']>0]):,}") # Should be ~116,411
# Persons should be 2.5-3x households
persons = pd.read_csv('output/2022/synthetic_persons.csv')
print(f"Total persons: {len(persons):,}") # Should be ~3,283,519
print(f"Average HH size: {len(persons) / len(hh):.2f}") # Should be ~2.5-2.73. Household-Person Linkage:
# All persons should link to valid households
hh_ids = set(hh['household_id'])
person_hh_ids = set(persons['household_id'])
orphans = person_hh_ids - hh_ids
assert len(orphans) == 0, f"Found {len(orphans)} orphan persons!"4. Geographic Coverage:
# All MGRAs should have at least some presence
mgra_hh = set(hh['mgra'])
mgra_coverage = mgra_hh
# Load MGRA list
mgra_input = pd.read_csv('output/2022/mgra15_based_input_2022.csv')
total_mgras = len(mgra_input)
print(f"MGRAs with population: {len(mgra_hh)}")
print(f"Total MGRAs covered: {len(mgra_coverage)} / {total_mgras}")
print(f"Empty MGRAs: {total_mgras - len(mgra_coverage)}")
# Check GQ distribution
gq_counts = hh[hh['gq_type'] > 0].groupby('gq_type').size()
print(f"\nGQ distribution:")
print(f" Military (gq_type=1): {gq_counts.get(1, 0):,}")
print(f" College (gq_type=2): {gq_counts.get(2, 0):,}")
print(f" Other (gq_type=3): {gq_counts.get(3, 0):,}")5. Control Total Validation:
# Check regional totals from summary file
summary_region = pd.read_csv('output/2022/final_summary_region_1.csv')
# Total households should match exactly
total_hh_row = summary_region[summary_region['control_name'] == 'Total_HH']
control = total_hh_row['control_value'].iloc[0]
result = total_hh_row['mgra_integer_weight'].iloc[0]
diff = result - control
pct_diff = (diff / control) * 100
print(f"Total_HH control: {control:,.0f}")
print(f"Total_HH result: {result:,.0f}")
print(f"Difference: {diff:,.0f} ({pct_diff:.3f}%)")
assert abs(pct_diff) < 0.01, f"Total HH off by {pct_diff:.3f}%!"6. NULL Value Check:
# Critical columns should have no NULLs
critical_cols_hh = ['household_id', 'mgra', 'NP', 'HHADJINC', 'workers']
critical_cols_person = ['household_id', 'mgra', 'AGEP', 'SEX']
print("Household NULLs:")
print(hh[critical_cols_hh].isnull().sum())
print("\nPerson NULLs:")
print(persons[critical_cols_person].isnull().sum())import pandas as pd
# Read main synthesis outputs
hh = pd.read_csv('output/2022/synthetic_households_2022.csv')
persons = pd.read_csv('output/2022/synthetic_persons_2022.csv')
# Read GQ outputs
hh_gq = pd.read_csv('output/2022/synthetic_households_gq.csv')
persons_gq = pd.read_csv('output/2022/synthetic_persons_gq.csv')
# Read MGRA summary
mgra_summary = pd.read_csv('output/2022/mgra15_based_input_2022.csv')
# Basic statistics
print(f"Total households: {len(hh):,}")
print(f"Total persons: {len(persons):,}")
print(f"GQ households: {len(hh_gq):,}")
print(f"GQ persons: {len(persons_gq):,}")
print(f"Average household size: {len(persons) / len(hh):.2f}")
print(f"Average income: ${hh['HHADJINC'].mean():,.0f}")
# Merge households and persons
hh_persons = persons.merge(hh, on='household_id', suffixes=('_person', '_hh'))
print(f"Merged records: {len(hh_persons):,}")library(tidyverse)
# Read households and persons
hh <- read_csv('output/2022/synthetic_households_2022.csv')
persons <- read_csv('output/2022/synthetic_persons_2022.csv')
mgra_summary <- read_csv('output/2022/mgra15_based_input_2022.csv')
# Summary statistics
hh %>% summarise(
count = n(),
mean_income = mean(HHADJINC, na.rm = TRUE),
mean_size = mean(NP, na.rm = TRUE),
mean_workers = mean(workers, na.rm = TRUE)
)
# Persons by age group
per9. File Retention
**Recommended retention policy:**
| File Type | Retention | Reason |
|-----------|-----------|--------|
| `synthetic_*_2022.csv` | Permanent | Primary synthesis outputs, ABM inputs |
| `synthetic_*_gq.csv` | Permanent | Group quarters population |
| `mgra15_based_input_*.csv` | Permanent | Critical ABM input file |
| `summary_*.csv` | 2 years | Validation and QA audits |
| `*_controls.csv` | 2 years | Control definitions for reproducibility |
| `timing_log.csv` | 1 year | Performance monitoring |
| Database tables | Permanent | Production data with versioning |
**Disk space planning:**
- Per year: ~738 MB
- All 7 years: ~5.2 GB
- With database: +3-n-Sim\output\2022\synthetic_households_2022.csv'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
TABLOCK
);
-- Bulk load persons
BULK INSERT [staging].[synthetic_persons_2022]
FROM 'C:\Population-Sim\output\2022\synthetic_persons_2022.csv'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
TABLOCK
);
-- Verify counts
SELECT 'Households' AS type, COUNT(*) AS count
FROM [staging].[synthetic_households_2022]
UNION ALL
SELECT 'Persons', COUNT(*)
FROM [staging].[synthetic_persons_2022];# Income distribution by MGRA
income_by_mgra = hh.groupby('mgra')['HHADJINC'].agg(['mean', 'median', 'count'])
income_by_mgra = income_by_mgra[income_by_mgra['count'] >= 10] # Filter low-count MGRAs
print(income_by_mgra.describe())
# Workers distribution
workers_dist = hh['workers'].value_counts().sort_index()
print("\nWorkers per household:")
print(workers_dist)
# Age distribution
age_dist = persons['AGEP'].value_counts(bins=[0, 18, 25, 35, 50, 65, 100])
print("\nAge distribution:")
print(age_dist)
# Employment status
employment = persons['ESR'].value_counts()
print("\nEmployment status:")
print(employment)
# ESR: 1=employed at work, 2=employed not at work, 3=unemployed,
# 4=armed forces at work, 5=armed forces not at work, 6=not in labor forceRecommended retention policy:
| File Type | Retention | Reason |
|---|---|---|
synthetic_*.csv |
Permanent | Primary synthesis outputs, ABM inputs |
mgra15_based_input_*.csv |
Permanent | Critical ABM input file |
final_summary_*.csv |
2 years | Validation and QA audits |
*_controls.csv |
2 years | Control definitions for reproducibility |
timing_log*.csv |
1 year | Performance monitoring |
| Database tables | Permanent | Production data with versioning |
Disk space planning:
- Per year: ~382 MB
- All 7 years: ~2.7 GB
- With database: +3 GB
Next: Validation & QA - Learn how to validate these outputs