# Rebuilding and reviewing the Georgia water audit dataset

The original workbooks and the supplied cleaned workbook are the data authority for this reproduction. The pipeline does not overwrite their values, infer missing audits, or resolve reported unit questions. It reads cached Excel values with `openpyxl` in read-only mode; it does not recalculate formulas. A workbook with missing formula caches requires a separately reviewed spreadsheet recalculation before use.

## Run

From the repository root, with Python 3 and `openpyxl` installed:

```sh
python scripts/build_data.py
python scripts/build_data.py --source-dir dist/sources --output data/processed/data.json
python -m unittest discover -s tests/data -v
```

The default output is a temporary `waterloss-data.json` and adjacent CSV, outside the repository. `--output` can select the canonical checked-in location. No publication occurs unless `--publish` is explicitly supplied:

```sh
python scripts/build_data.py --output data/processed/data.json --publish
```

Publication writes compact `dist/data.json`, `dist/sources/local-audits-reviewed.csv`, and the full on-demand provenance download `dist/sources/audits-with-provenance.json`. Existing source workbook links remain unchanged. Source-dir paths resolve relative to the current directory; defaults resolve relative to the script, so no old workspace paths are needed. The generated CSV keeps Python's full-precision numeric representation and includes nested provenance as JSON cells.

## Schema and provenance

The existing `local`, `regional`, `state`, `counties`, `peerMedian`, `peerN`, `sources`, `changes`, and `method` keys remain compatible. Every preexisting local field and exact value was compared with the prior published dataset.

| Addition | Purpose |
| --- | --- |
| `stateRows` | All 2,394 records not excluded by the cleaned workbook, including original quality flags, `aggregate_eligible` and source file/sheet/row links. The public version selects chart and comparison fields; the two incomplete Army loss records remain inspectable but do not contribute to statewide paired totals. |
| `allRows` | All 2,400 cleaned records, including the six excluded records; canonical and full provenance download only. |
| `raw_original_row` | Every cell of the cited original row, including blanks, footnotes and additional numeric or text fields; canonical and full download only. |
| `fieldCatalog` | Labels, units, groups and explanations for all retained row fields. Fractions are explicitly distinguished from percent values. |
| `sourceFieldCatalog` | Original sheet headers, one-based column positions and mapped field names; full download only. |
| `sourceManifest` | SHA-256 hash and byte size of all 15 authoritative supplied source attachments. Generated CSV/JSON downloads are not inputs. |
| `provenanceDownload` | Public URL for the full provenance dataset. |
| `method.validation` | Source match results, duplicates, missing audits, reconciliation anomalies, original quality warnings, null-loss rows and separately identified plausibility questions. |

Original source layouts have 24 stable initial columns. Audits through 2020 and audits from 2021 use explicit separate mappings for cost fields, valuation basis, ILI and validity score. Numeric reported apparent/real loss rates are checked too. Unmapped original fields remain accessible in raw rows and original-header catalogs; no extra source column is silently discarded.

## Actual validation results

On the supplied 2016–2025 workbooks the build checked 61,602 mapped numeric original cells across all 2,400 cleaned rows, with zero mismatches. There are 89 local audits and no duplicate utility/year keys. Ringgold's 2017 audit is missing; it is recorded as missing, not imputed as zero. The six cleaned exclusions are retained in `allRows` with their original warnings. Exclusion means not used for statewide comparisons; it does not remove a source record.

Six reconciliation types are checked where the reported/cleaned value exists: loss components, supplied-minus-authorized balance, non-revenue balance, real and apparent per-connection rates, and combined loss valuation. The supplied dataset has no numeric reconciliation failures. Two 2024 Army-system records have null cleaned total losses and original NEGATIVE-loss warnings. The two incomplete supply/loss pairs are now excluded from statewide aggregates in their entirety, including the supply denominator and component losses. Their source rows remain in `stateRows` with `aggregate_eligible=false` and in the full provenance download. They are separately listed in `method.incompleteAggregateRows`, distinct from the six suspect-unit exclusions. In 2024, `n`/`nEligible` is 238, `nUsable` is 240 and `nTotal` is 242; `nIncomplete` is 2. Missing observations are never converted to zero. Every auxiliary aggregate reports numeric coverage through `fieldCoverage`; no-observation totals are null. Source matching does not prove a figure is plausible.

The 365-day audit convention is retained even in leap years. Local, regional, county and state aggregates reconcile with their component records. The legacy 208-utility panel has audit records in all ten years but contains these two incomplete 2024 loss records. It must not be described as a fully numeric ten-year balanced loss panel. Its original endpoint share rounds to 16.7% in 2016 and 21.9% in 2025; those endpoint values are unchanged. A revised balanced numeric panel requires a separately reviewed recalculation, so its narrative is not silently recomputed here. Downloaded CSV numeric values are tested against canonical local records for exact float round trips; all nested provenance is tested too. Ten unittest checks pass, including exclusion of both entire incomplete 2024 supply/loss pairs and unchanged 2025 principal totals.

## Questions retained for review

Catoosa Utility District's 2025 population is 7,800, following 60,000 in 2024. It matches the original source; the cleaned population-change warning and ILI-below-one warning remain visible. Dade's 2018/2019 supply, billed use, losses and connections repeat exactly and retain the possible-copied-audit flag. LaFayette's variable production cost moves from 824.98 USD/MG in 2024 to 192.95 in 2025, alongside other year-to-year shifts. The pipeline preserves these source values and separately labels their plausibility questions; it never silently corrects them.

Loss valuations use reported VPC or CRUC, remain in nominal dollars, and are not directly comparable across valuation bases. The legacy `headroom_MG` field is only a descriptive gap to the latest statewide median, not a guaranteed recoverable quantity or savings estimate. Rates, validity scores and ILI should be interpreted together with density, applicability and source quality flags.

## Annual update procedure

1. Add the new original annual workbook with its year immediately after the two-digit attachment prefix; update the reviewed cleaned workbook with exact source file/sheet/row provenance and explicit quality decisions. Preserve prior original attachments.
2. Review any source-layout changes before extending `mapping(year)`. A changed numeric mapped value fails the build, while a new unmapped column remains preserved in raw provenance. Inspect formulas for cached values.
3. Rebuild canonical data without publishing; inspect `method.validation`, especially source mismatches, null values, duplicate IDs, missing local years and unchanged/new quality flags. Compare the hashes with the approved source attachments.
4. Review row-count/coverage changes and peer composition; update snapshot expectations in tests only after explaining the actual new data. Compare aggregate history, unit questions and source footnotes.
5. Run the tests, then explicitly publish. Review the downloaded CSV and full provenance JSON before committing source and generated changes together. Review the site against the new latest audit year; the pipeline derives latest year automatically, but narrative copy and chart ranges may require a separate update.