Spatial Epi Public Health GIS

Aggregating Water Quality Violations to Service Areas

This guide, part of Hazard Proximity & Drinking Water Metrics, turns a regulatory compliance database into a system-level exposure variable — and is explicit about the several things that variable is measuring at once.

Problem Context & Constraints

A drinking water compliance database records violations, not concentrations. That distinction drives every design decision here.

Violation categories differ fundamentally. A health-based violation means a contaminant exceeded its enforceable limit. A monitoring and reporting violation means the system failed to sample or to file paperwork. A public notification violation means it failed to tell customers. Summing them produces a count in which a nitrate exceedance and a late report weigh equally, which is indefensible as an exposure.

Counts measure detection as well as occurrence. A system that monitors diligently discovers and records exceedances that a lax system never finds. Across systems, violation counts therefore partly rank compliance behaviour rather than water quality — and, perversely, a system with many monitoring violations may have fewer recorded health-based ones precisely because it did not look.

Duration matters more than count. One exceedance lasting three years and three exceedances lasting a month each produce different exposures and can produce the same count. Compliance records carry begin and end dates; using them turns a count into person-time.

The constraint that makes the variable usable is to build it from health-based violations with duration weighting, and to carry the monitoring-violation count separately as a covariate describing detection effort rather than folding it into the exposure.

Separating Exposure From Detection Effort Three violation categories from a compliance database. Health-based violations, where a contaminant exceeded its limit, feed the exposure variable weighted by duration. Monitoring and reporting violations, where the system failed to sample or file, feed a separate covariate describing detection effort, because they indicate how hard the system looked rather than what was in the water. Public notification violations feed neither directly but inform a governance measure. A note warns that summing all three produces a count in which a nitrate exceedance weighs the same as a late report. One database, three categories, two different variables Health-based a limit was exceeded Monitoring and reporting the system did not sample Public notification customers were not told EXPOSURE duration-weighted, by contaminant COVARIATE detection effort, not water quality GOVERNANCE reported separately A total violation count puts a nitrate exceedance and a late report on the same scale and then ranks systems by a number that means neither one thing nor the other

Prerequisites

  • python 3.11
  • pandas 2.2.2, numpy 1.26.4
  • A compliance database with system identifier, violation category, contaminant code, and begin and end dates
  • The system assignment from the sibling service area join, including the purchase chain
  • A declared exposure window per subject or per study
  • System service population, for population weighting where a summary across systems is needed

Step-by-Step Solution

Classify violations, compute overlap between each violation period and the subject’s exposure window, and summarise as duration-weighted exposure by contaminant group with a separate detection-effort covariate.

# System-level drinking water exposure from compliance records.
# Pinned: pandas==2.2.2, numpy==1.26.4
import logging

import numpy as np
import pandas as pd

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

HEALTH_BASED = {"MCL", "MRDL", "TT"}          # exceedance and treatment-technique
MONITORING = {"MR", "MON"}                     # failure to sample or report
OPEN_END_CAP_DAYS = 365                        # an unresolved violation is capped


def classify(violations: pd.DataFrame, type_col: str = "violation_type") -> pd.DataFrame:
    """Split the compliance table into exposure, detection and governance sets."""
    v = violations.copy()
    v["category"] = np.select(
        [v[type_col].isin(HEALTH_BASED), v[type_col].isin(MONITORING)],
        ["health", "monitoring"], default="other")
    counts = v["category"].value_counts().to_dict()
    log.info("violations classified: %s", counts)
    unmapped = sorted(set(v.loc[v["category"] == "other", type_col].unique()))
    if unmapped:
        log.warning("unmapped violation types treated as 'other': %s", unmapped[:12])
    return v


def window_overlap_days(v: pd.DataFrame, win_start: pd.Timestamp,
                        win_end: pd.Timestamp) -> pd.Series:
    """Days of each violation that fall inside the exposure window."""
    begin = pd.to_datetime(v["begin_date"])
    end = pd.to_datetime(v["end_date"])
    # Unresolved violations have no end date; cap rather than run to today.
    end = end.fillna(begin + pd.to_timedelta(OPEN_END_CAP_DAYS, unit="D"))
    lo = np.maximum(begin.to_numpy("datetime64[ns]"), np.datetime64(win_start))
    hi = np.minimum(end.to_numpy("datetime64[ns]"), np.datetime64(win_end))
    days = (hi - lo) / np.timedelta64(1, "D")
    return pd.Series(np.clip(days, 0, None), index=v.index)


def system_exposure(violations: pd.DataFrame, pwsid_chain: list[str],
                    win_start: pd.Timestamp, win_end: pd.Timestamp,
                    contaminant_groups: dict) -> dict:
    """Duration-weighted exposure for one subject's system chain."""
    v = violations[violations["pwsid"].isin(pwsid_chain)].copy()
    if v.empty:
        return {"days_health_total": 0.0, "n_monitoring": 0, "chain": pwsid_chain}
    v["overlap_days"] = window_overlap_days(v, win_start, win_end)
    v = v[v["overlap_days"] > 0]

    out = {"chain": pwsid_chain}
    health = v[v["category"] == "health"]
    window_days = max((win_end - win_start).days, 1)
    out["days_health_total"] = float(health["overlap_days"].sum())
    out["frac_window_in_violation"] = min(out["days_health_total"] / window_days, 1.0)

    for group, codes in contaminant_groups.items():
        sel = health[health["contaminant_code"].isin(codes)]
        out[f"days_{group}"] = float(sel["overlap_days"].sum())

    # Detection effort travels as its own variable, never inside the exposure.
    out["n_monitoring"] = int((v["category"] == "monitoring").sum())
    out["monitoring_days"] = float(v.loc[v["category"] == "monitoring",
                                         "overlap_days"].sum())
    return out

Capping unresolved violations rather than running them to the present date is a small decision with a large effect. Compliance databases contain violations opened decades ago with no recorded return to compliance, most of which were resolved without the resolution being filed. Letting them run to today assigns a household twenty years of continuous exceedance from one data-entry omission.

Summing across the purchase chain means a household inherits both its retailer’s distribution violations and its wholesaler’s treatment violations, which together describe the water at the tap. Using only one of the two is the most common error in this analysis and it systematically misses whichever category the omitted system is responsible for.

Only the Overlap Counts, and an Open Violation Is Capped A timeline with a subject's exposure window marked as a band. Three violation periods are drawn against it. The first begins before the window and ends inside it, so only the portion inside the window contributes. The second falls entirely within the window and contributes in full. The third has no recorded end date and is capped at a stated number of days after its start rather than running to the present, which prevents a single unfiled resolution from assigning years of continuous exceedance. An unfiled return to compliance is not twenty years of exceedance exposure window partial overlap counts fully inside, counts in full no end date: capped at the stated limit Report the cap; it bounds the maximum exposure any single record can contribute

Validation & Edge Cases

Unmapped violation type codes. Vocabularies differ between states and change over time. Codes not in the classification map must be logged and reviewed, not silently bucketed as “other”, because a state-specific health-based code landing in “other” removes real exposure.

Contaminant groups that mix mechanisms. Grouping nitrate with disinfection byproducts produces a variable with no coherent health interpretation. Group by plausible mechanism and outcome, and report groups separately.

Systems that merged or changed identifier. A violation filed under a retired identifier will not join. Apply the identifier crosswalk before aggregating, and count matches made through it.

Concurrent violations of the same contaminant. A system can carry two overlapping violation records for the same contaminant, filed by different rule provisions. Summing their overlap days double-counts the exposure and can push the fraction of the window in violation above one. Merge overlapping intervals within each contaminant group before summing, and count how many records were merged, because a high merge rate signals a state whose reporting conventions differ from the national schema.

Windows shorter than the monitoring cycle. Many contaminants are sampled quarterly or annually. An exposure window of three months can therefore contain no sample at all, so a violation-free result for that window carries almost no information. Compare the window length against the monitoring frequency for each contaminant group, and where the window is shorter, report the exposure as undetermined rather than as zero.

Bottled water and point-of-use treatment. A household under a boil-water notice may have drunk none of the affected supply. The compliance record describes what the system delivered, not what was consumed, and in prolonged, well-publicised events the gap between the two is large. Where a notice was issued, record it as a separate flag so an analysis can test whether the association weakens in exactly the periods when consumption most likely fell.

Very small systems. A system serving 40 people can have an extreme violation rate from a single event. Where summarising across systems, weight by service population; where assigning to individuals, do not, because each household experiences its own system regardless of its size.

Violation-free is not contaminant-free. A system with no health-based violations may have concentrations just under the limit throughout. Where measured concentration data exist in the compliance database, prefer them; violations are a coarse threshold on what was measured.

INFO water.violations violations classified: {'health': 48120, 'monitoring': 211944, 'other': 30518}
WARNING water.violations unmapped violation types treated as 'other': ['02', '5A', '63', 'PN-ROUTINE']

The interval-merging rule in that list is easiest to understand as two overlapping records collapsing into one period of exposure:

Merge Overlapping Violations Before Summing Days Two violation records for the same contaminant at the same system, filed under different rule provisions, whose periods overlap for several months. Summing their overlap with the exposure window counts the shared months twice and can push the fraction of the window in violation above one. Merging the intervals first produces one continuous period, and summing that gives the correct number of days in violation. Two records, one exposure — count the days once as filed record 1: nitrate, rule A record 2: nitrate, rule B summed: 520 days merged one nitrate exposure period correct: 420 days 2021 2022 2023 merge within each contaminant group, then sum, and count how many records were merged

Compliance Notes

Record the compliance database identifier and extract date, the violation-type classification map with the source of each mapping, the open-violation cap, the contaminant grouping definitions, the identifier crosswalk and how many records used it, the exposure window definition, and whether the purchase chain was included.

Describe the exposure variable precisely in any output: duration of health-based violations within the exposure window for the assigned system chain, by contaminant group. That phrase is longer than “water quality” and it is what the variable is. Report the detection-effort covariate alongside it, and state that a violation count conflates occurrence with monitoring behaviour — a caveat that belongs in the result, not in a methods appendix, because the direction of the resulting bias is not obvious to a general reader.