Spatial Epi Public Health GIS

Reconciling Case Counts Across Geographic Levels

This guide, part of Spatial Data Quality Monitoring, implements the single most effective quality gate in a spatial surveillance pipeline: the requirement that every way of counting the same cases gives the same answer.

Problem Context & Constraints

A spatial pipeline counts cases several ways: raw records ingested, records successfully geocoded, cases assigned to tracts, cases assigned to counties, cases in the published state total. Each of those should be derivable from the one before by an explicit rule. When they disagree, something between them lost, duplicated or misassigned records — and in spatial work the something is almost always a join.

The join failures that reconciliation catches are exactly the ones nothing else does. A point-in-polygon join against a boundary layer with a sliver gap drops the cases that fall in the gap. A join against a boundary layer with an overlap assigns those cases to two areas. A tract identifier with a leading zero stripped by a spreadsheet no longer matches its county. A partition that failed to load is simply absent. None raise an error; all produce a total that is slightly wrong.

Two constraints make reconciliation work in practice. Zero tolerance — counts either reconcile or they do not, and a tolerance converts the strongest check into a weak one, because the errors it catches are frequently small. And an exclusions ledger — records are legitimately dropped at several stages (out-of-jurisdiction residents, duplicate reports, unassignable addresses), and unless every such drop is recorded with its reason, the counts never reconcile and the check gets disabled.

Every Record Accounted For, From Ingestion to Publication A waterfall chart beginning with records ingested. Successive bars subtract ledgered exclusions: duplicate reports, residents of other jurisdictions, and addresses that could not be assigned to any area. What remains must equal the sum of tract counts, which must equal the sum of county counts, which must equal the published state total. A final bar labelled unexplained difference must be zero, and when it is not, the chart marks the level at which the disagreement first appears, which names the join that failed. The unexplained difference must be exactly zero ingested 48 900 duplicates −612 out of state −418 unassignable −1 104 tract sum county sum state total 46 766 at every level unexplained 0 When the last bar is not zero, the first level where totals disagree names the failed join

Prerequisites

  • python 3.11
  • pandas 2.2.2, geopandas 0.14.4
  • A nesting relationship between geographic levels — every tract belongs to exactly one county in the vintage used
  • An exclusions ledger written by every stage that drops records, with a reason code
  • Stable record keys that survive every stage

Step-by-Step Solution

Build the waterfall from the ledger, compute the count at every geographic level, check each level against the next and against the ledgered expectation, and locate the first disagreement.

# Zero-tolerance count reconciliation with an exclusions ledger.
# Pinned: pandas==2.2.2, geopandas==0.14.4
import logging

import pandas as pd

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("dq.reconcile")

REASONS = {"duplicate", "out_of_jurisdiction", "unassignable", "test_record",
           "withdrawn_by_reporter"}


class ExclusionLedger:
    """Every stage that drops a record must say so, with a declared reason."""

    def __init__(self):
        self.rows: list[dict] = []

    def exclude(self, record_keys, stage: str, reason: str) -> None:
        if reason not in REASONS:
            raise ValueError(f"undeclared exclusion reason: {reason!r}")
        keys = list(record_keys)
        self.rows.extend({"record_key": k, "stage": stage, "reason": reason} for k in keys)
        log.info("excluded %d records at stage=%s reason=%s", len(keys), stage, reason)

    def frame(self) -> pd.DataFrame:
        df = pd.DataFrame(self.rows, columns=["record_key", "stage", "reason"])
        dup = df["record_key"].duplicated(keep=False)
        if dup.any():
            # A record excluded twice would be subtracted twice.
            raise AssertionError(f"{int(dup.sum())} records excluded more than once")
        return df


def reconcile(n_ingested: int, ledger: ExclusionLedger,
              levels: dict[str, pd.DataFrame], nesting: dict[str, tuple[str, str]],
              value: str = "cases") -> dict:
    """Check every level against the ledgered expectation and against its parent.

    levels:  {"tract": df, "county": df, "state": df}, each with an id and value
    nesting: {"tract": ("county", "county_geoid"), "county": ("state", "state_fips")}
    """
    ex = ledger.frame()
    expected = n_ingested - len(ex)
    by_reason = ex["reason"].value_counts().to_dict()
    report = {"ingested": n_ingested, "excluded": len(ex), "by_reason": by_reason,
              "expected": expected, "levels": {}, "first_disagreement": None}

    for name, df in levels.items():
        total = int(df[value].sum())
        report["levels"][name] = total
        gap = total - expected
        if gap != 0 and report["first_disagreement"] is None:
            report["first_disagreement"] = {"level": name, "vs": "ledger", "gap": gap}
        log.info("level %-8s total=%d expected=%d gap=%+d", name, total, expected, gap)

    for child, (parent, parent_key) in nesting.items():
        rolled = levels[child].groupby(parent_key)[value].sum()
        pdf = levels[parent].set_index(parent_key)[value]
        diff = rolled.reindex(pdf.index.union(rolled.index)).fillna(0) - \
               pdf.reindex(pdf.index.union(rolled.index)).fillna(0)
        bad = diff[diff != 0]
        if len(bad):
            log.error("%s does not roll up to %s: %d %s units disagree (net %+d)",
                      child, parent, len(bad), parent, int(bad.sum()))
            if report["first_disagreement"] is None:
                report["first_disagreement"] = {"level": child, "vs": parent,
                                                "units": int(len(bad)),
                                                "net": int(bad.sum())}

    report["reconciled"] = report["first_disagreement"] is None
    (log.info if report["reconciled"] else log.error)(
        "reconciliation %s: %s", "PASSED" if report["reconciled"] else "FAILED",
        report["first_disagreement"])
    return report

The ledger’s refusal of undeclared reasons and double exclusions is what keeps the arithmetic honest. An exclusion with a free-text reason becomes a place where records disappear without explanation; a record excluded at two stages is subtracted twice and makes the counts appear to reconcile when they do not.

Locating the first disagreement is what turns the check from an alarm into a diagnosis. A tract-to-county roll-up that fails in three counties with a net loss of 41 cases says, with high specificity, that 41 cases fell into tracts whose county identifier did not match — a stripped leading zero, a boundary vintage mismatch, or a tract that moved counties between vintages.

Reading a Reconciliation Failure Four failure signatures mapped to their usual causes. Tract sum below the ledgered expectation points to cases falling into a gap in the tract layer. Tract sum above expectation points to cases assigned to two overlapping tracts. Tract roll-up disagreeing with county totals in a handful of counties points to identifier formatting or a vintage mismatch in the nesting table. A whole county missing points to a partition that failed to load. The signature of the disagreement narrows the search before any record is inspected. The shape of the disagreement names the join that failed signature usual cause tract sum below expected sliver gaps in the tract layer tract sum above expected overlapping tracts, double assignment roll-up fails in a few counties identifier formatting or vintage mismatch one county entirely absent a partition that failed to load Each is found in minutes from the report, rather than in days from the records

Validation & Edge Cases

Tracts that cross county lines. In some vintages a small number of tracts genuinely straddle a county boundary, which breaks strict nesting. Use the published tract-to-county relationship for the vintage, not a spatial join, and treat any tract without a single parent as a declared exception.

Weighted allocations. Where cases are allocated to areas fractionally, as in areal interpolation, the fine-level sums are not integers and floating-point rounding produces tiny non-zero gaps. Reconcile the totals with a tolerance of a fraction of one case, and reconcile the integer record counts exactly.

Suppressed publication levels. A published table with small cells suppressed does not sum to the total by design. Reconcile before suppression, never after.

Late-arriving records. A record that arrived after the run started but before the level counts were computed appears in one level and not another. Snapshot the input at the start of the run and reconcile against the snapshot.

A reconciliation that passes for the wrong reason. Two errors of opposite sign — a gap dropping forty cases and an overlap double-counting forty — cancel at the total and pass. The roll-up comparison at each nesting level catches most of these because the errors rarely fall in the same parent unit, but where it matters, reconcile record keys rather than counts: every surviving record key must appear in exactly one area at each level, which no pair of offsetting errors can satisfy.

Stage restarts that duplicate ledger rows. A stage that failed halfway and restarted can write its exclusions twice. The ledger’s double-exclusion guard will stop the run, which is correct, but the fix belongs in the stage: write the stage’s ledger rows atomically with its output so a restart replaces them rather than appending.

Ledger written but not persisted. An exclusion recorded in memory and lost when a stage restarts leaves a permanent unexplained gap. Persist the ledger with the stage output, atomically.

INFO dq.reconcile level tract    total=46725 expected=46766 gap=-41
ERROR dq.reconcile tract does not roll up to county: 3 county units disagree (net -41)
ERROR dq.reconcile reconciliation FAILED: {'level': 'tract', 'vs': 'ledger', 'gap': -41}

The offsetting-errors case in that list is why record-level reconciliation exists alongside the count-level check:

Two Errors That Cancel at the Total Two join errors in the same run. A sliver gap drops forty cases in one county, and an overlap between two tracts assigns forty other cases twice in another county. At the state total the two cancel and the count reconciles exactly. The county roll-up exposes both because they fall in different counties. Record-level reconciliation, which requires every surviving record key to appear in exactly one area per level, exposes both regardless of where they fall. A total that reconciles can still hide two opposite errors County A: sliver gap −40 cases County B: overlap +40 cases State total reconciles: 0 gap County roll-up check catches both, because they differ by county Record-level check catches both wherever they fall every surviving record key must appear in exactly one area at each level

Compliance Notes

Record the full waterfall for every run: ingested count, exclusions by stage and reason, expected total, the count at every level, every roll-up comparison, and the first disagreement where one exists. That record is the evidence that a published total accounts for every record the agency received, which is a question auditors and oversight bodies do ask.

The exclusions ledger lists individual record keys with reasons such as out-of-jurisdiction or withdrawn, which makes it a protected artifact in its own right. Keep it inside the protected environment under the case file’s retention rules, and surface only its aggregate counts in the scorecard and provenance. The aggregate counts by reason are themselves informative — a sudden rise in out-of-jurisdiction exclusions often means a geocoding problem at a state border, not a change in who is reporting.