# MACStats tidy reference tables

This project downloads every Excel source published through the MACStats archive and the February 2026 current MACStats data book, profiles the heterogeneous workbook layouts, and produces cross-year tidy reference tables for trend analysis.

The source inventory was refreshed on August 17, 2026. It includes 18 archived report editions from March 2011 through December 2024 and 51 exhibits in the February 2026 data book. MACPAC provides Excel files for 43 of those current exhibits; Exhibits 8, 9, 18, 19, 20, 49, 50, and 51 are currently PDF-only. They remain in `source_manifest.csv` with `excel_available=false`, while prior Excel editions of those series are included when available.

## Main outputs

- `data/processed/macstats_tidy_long.csv` contains every retained report vintage. Use this when revisions, reprints, and source-edition provenance matter.
- `data/processed/macstats_latest_vintage.csv` keeps the newest published vintage for each otherwise equivalent observation. This is the simplest starting point for trend analysis.
- `data/processed/tables/` contains one all-vintage CSV per canonical series.
- `data/processed/tables_latest/` contains one latest-vintage CSV per canonical series.
- `data/processed/table_catalog.csv` records every retained workbook/worksheet table, extraction range, fingerprint, match method, and observation count.
- `data/processed/series_catalog.csv` lists the 51 current exhibit series plus 16 discontinued historical series.
- `data/processed/series_crosswalk.csv` maps each source-edition table number and title to a stable series identifier. Table numbers are not assumed to be stable across years.
- `data/processed/data_dictionary.csv` defines every field in the tidy observation files.
- `data/raw/source_manifest.csv` is the download inventory with URLs, availability, sizes, and SHA-256 checksums.
- `qa/` contains coverage, exclusions, duplicate-table decisions, and the machine-readable validation summary.

## Run locally

Python 3.10 or newer is recommended.

```bash
python -m venv .venv
source .venv/bin/activate          # Windows: .venv\Scripts\activate
python -m pip install -r requirements.txt
python run_pipeline.py
```

Useful variants:

```bash
python run_pipeline.py --skip-download   # reuse existing downloads
python run_pipeline.py --force-download  # refresh every source file
```

The pipeline stages are:

1. `src/download_sources.py` inventories the two MACPAC pages, downloads the 18 archive ZIPs and 43 current Excel files, and records checksums.
2. `src/profile_workbooks.py` safely extracts the archive ZIPs and profiles every workbook and worksheet.
3. `src/build_tidy.py` detects data blocks, reconstructs multirow headers, extracts chart caches and text-only crosswalks, applies the reviewed series crosswalk, and writes the tidy outputs.
4. `src/validate_outputs.py` checks source counts and hashes, table coverage, crosswalk review status, portability, duplicate facts, header quality, and representative source values.

## Data model and analytic use

Each row in a tidy output is one published observation. Important fields include:

- `series_id` and `canonical_title`: stable cross-report table-series identifiers.
- `report_edition`: the MACStats publication vintage, such as `2024-12` or `2026-02`.
- `period_year`, `period_type`, and `period_label`: the analytic period inferred from the row, header, or title.
- `row_dimension_*` and `column_dimension_*`: reconstructed source dimensions; `column_path` preserves the complete multirow header hierarchy.
- `value_raw`: the source value without scale changes.
- `value_numeric`: the numeric interpretation in the source scale.
- `value_normalized`: percentage points or base units when the source explicitly reports percentages, thousands, millions, or billions.
- `data_source`: source identified from the title and notes when possible, including NHIS, MEPS, ACS, CMS-64, and T-MSIS/CMS-64.
- `is_total_row` and `include_in_reporting`: flags that let analysis retain national totals while avoiding accidental double counting. This flag excludes recognized row totals and subtotals; it does not identify additive columns or make all remaining rows safe to sum.
- `source_workbook`, `source_sheet`, and `source_cell`: exact provenance. For six 2016–2017 workbooks that stored values only in embedded charts, `source_cell` identifies the chart-cache series and point.

The pipeline keeps standard errors in the same schema with `estimate_type=standard_error`. Missing and suppressed values remain explicit in `value_status`; they are not silently converted to zero.

## Validation baseline

The included build passes the following checks:

- 69 published-source records: 18 archive packages and 51 current exhibits
- 61 downloaded Excel/ZIP files with matching SHA-256 checksums
- 499 workbooks and 864 worksheets profiled
- 783 retained tables across 67 canonical and legacy series
- 289,053 all-vintage observations
- 229,084 latest-vintage observations
- 270 observations recovered from embedded chart caches
- 0 unresolved extraction issues
- 0 unreviewed series mappings

## Official sources

- MACStats archive: https://www.macpac.gov/publication/macstats-archive/
- Current compiled MACStats: https://www.macpac.gov/publication/macstats-compiled/


## September 5, 2026 review

Read `MACStats_audit.md` for the independent audit, repairs, website national-total corrections, category reconciliation differences, and limitations. The raw all-vintage observation count is unchanged; latest-vintage identity now distinguishes units and data sources. Always include units and relevant source/definition distinctions when pivoting or comparing observations. Historical source labels and classifications are not universally harmonized.
