Skip to content

Output Files

JiaXu1024 edited this page Nov 13, 2026 · 1 revision

Output Files Specification

This page documents all output files generated by SANDAG PopulationSim, their formats, and how to use them.

Output Directory Structure

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 Types Overview

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

1. Synthesis Output Files

1.1 synthetic_households.csv

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,1

1.2 synthetic_persons.csv

Purpose: 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.0

Notes:

  • Group quarters (GQ) households and persons are included in the main files with gq_type field 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]

2. MGRA Summary File

2.1 mgra15_based_input_{year}.csv

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

3. Validation Summary Files

4.1 summary_region_1.csv

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,382314

4.2 summary_mgra_PUMA.csv

Purpose: 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,...

4.3 final_summary_mgra.csv

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

4. Additional Files

Reporting benchmarks

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.

5. File Sizes (2022 Example)

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

5.3 GQ Summary Files (by Type)

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,0

Note: Each file only includes MGRAs that have that specific GQ type.

5.4 timing_log.csv

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

6. Data Quality Checks

Essential Validations

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.csv

2. 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.7

3. 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())

7. Using Output Files

Loading in Python

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):,}")

Loading in R

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];

Analyzing Results

# 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 force

8. File Retention

Recommended 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

Clone this wiki locally