Computing Locational Running Annual Averages in Python

Disinfection byproducts do not breach their limits on a single bad afternoon; they accumulate as a four-quarter average that is judged separately at every sampling point in a distribution system. This page implements that calculation — the Locational Running Annual Average (LRAA) required by the Stage 2 Disinfectants and Disinfection Byproducts Rule — as a deterministic pandas routine, and it belongs to the parent MCL Exceedance Logic Implementation section, where the instantaneous checks live alongside these slower averaging rules. The task is precise: for total trihalomethanes (TTHM, limit 0.080 mg/L) and the sum of five haloacetic acids (HAA5, limit 0.060 mg/L), you must reduce every routine sample to a per-location quarterly mean, roll a four-quarter window forward one quarter at a time, and compare each location’s own average against the limit. The single most common defect in a first attempt is collapsing every monitoring point into one system-wide number, which hides a hot location behind cooler ones and silently under-reports a violation.

Prerequisites & Environment Setup

The routine targets Python 3.10+ and pandas for the grouping and rolling arithmetic. You need a tidy sample table with, at minimum, a monitoring-location identifier, an analyte name, a UTC sample timestamp, and a numeric result in mg/L. Where those samples originate from field instruments rather than a lab LIMS, they should already have passed through the resampling of irregular sensor data to compliance windows so that each reading carries a clean, timezone-aware timestamp before any quarter is assigned. The specific limits and analyte definitions used below are the ones catalogued in the SDWA MCL Reference Mapping, and the number of routine samples each site owes per quarter comes from Monitoring Frequency Scheduling.

python3 -m venv .venv && source .venv/bin/activate
pip install "pandas==2.2.*" "pyarrow==16.*"
# Optional: pydantic for validating the inbound sample schema
pip install "pydantic==2.7.*"

Step-by-Step Implementation

The LRAA for a given location at quarter $q$ is the arithmetic mean of the four most recent quarterly means at that same location:

where $\bar C_i$ is the mean of all routine samples collected at one monitoring location during quarter $i$. Note the two-stage averaging — samples are averaged within a quarter first, then quarters are averaged across the year — so a quarter with ten samples never outvotes a quarter with two. The implementation follows that structure exactly.

Step 1 — Assign a compliance quarter to every sample

Convert timestamps to a pandas Period at quarterly frequency. Periods sort and difference correctly across year boundaries, which matters the moment a window spans December into January.

import pandas as pd

STAGE2_MCL = {"TTHM": 0.080, "HAA5": 0.060}  # mg/L


def assign_quarter(samples: pd.DataFrame, ts_col: str = "sample_ts") -> pd.DataFrame:
    """Attach a quarterly Period derived from a UTC timestamp column."""
    ts = pd.to_datetime(samples[ts_col], utc=True)
    return samples.assign(quarter=ts.dt.to_period("Q"))

Step 2 — Reduce samples to per-location quarterly means

Group by the location identifier, the analyte, and the quarter — never by analyte and quarter alone. The location must stay in the grouping key all the way through; the instant it leaves, the per-location structure is gone and you have computed a system-wide running annual average by accident.

def quarterly_location_means(samples: pd.DataFrame) -> pd.DataFrame:
    """Mean of all routine results per (location, analyte, quarter)."""
    means = (
        samples
        .groupby(["location_id", "analyte", "quarter"], observed=True)["result_mgl"]
        .mean()
        .rename("quarterly_mean")
        .reset_index()
    )
    return means.sort_values(["location_id", "analyte", "quarter"])

Step 3 — Roll a four-quarter window forward per location

A positional rolling(4) is only correct if each location’s quarters are consecutive with no gaps. If a site misses a quarter, a naive rolling window silently averages across a five-calendar-quarter span and reports a number that no regulator would recognize. Guard against that by reindexing each location onto a complete quarterly range, so a skipped quarter becomes an explicit NaN that suppresses the window instead of corrupting it.

def _reindex_full_quarters(group: pd.DataFrame) -> pd.DataFrame:
    """Fill gaps in a single location/analyte series with empty quarters."""
    g = group.set_index("quarter").sort_index()
    full = pd.period_range(g.index.min(), g.index.max(), freq="Q")
    return g.reindex(full).rename_axis("quarter")


def locational_running_annual_average(
    quarterly: pd.DataFrame, min_quarters: int = 4
) -> pd.DataFrame:
    """Rolling 4-quarter mean of quarterly means, computed within each location."""
    rebuilt = (
        quarterly
        .groupby(["location_id", "analyte"], observed=True, group_keys=True)
        .apply(_reindex_full_quarters, include_groups=False)
        .reset_index()
    )
    rebuilt["lraa"] = (
        rebuilt
        .groupby(["location_id", "analyte"], observed=True)["quarterly_mean"]
        .transform(lambda s: s.rolling(window=4, min_periods=min_quarters).mean())
    )
    return rebuilt

The min_periods=4 clause is deliberate: an LRAA is undefined until a location has four consecutive quarters on record, so partial windows resolve to NaN rather than to a smaller, artificially low average during a site’s first year of monitoring. A location that has just entered the sampling plan therefore shows no LRAA until it earns one. The diagram below traces the sliding window across five quarters at a single location, and shows why the average can cross the limit even when no individual quarter looks alarming.

Step 4 — Flag exceedances against the Stage 2 limits

With an LRAA per location per quarter in hand, the comparison is a straight lookup. Attach each analyte’s limit and mark any finite LRAA that exceeds it. A location is in violation on its own; nothing here averages one point against another.

def flag_exceedances(lraa: pd.DataFrame) -> pd.DataFrame:
    """Compare each location's LRAA to its Stage 2 limit."""
    out = lraa.copy()
    out["mcl_mgl"] = out["analyte"].map(STAGE2_MCL)
    out["exceedance"] = out["lraa"].notna() & (out["lraa"] > out["mcl_mgl"])
    return out


def evaluate(samples: pd.DataFrame) -> pd.DataFrame:
    """End-to-end: samples in, per-location LRAA exceedance flags out."""
    quartered = assign_quarter(samples)
    means = quarterly_location_means(quartered)
    lraa = locational_running_annual_average(means)
    return flag_exceedances(lraa)

The frame returned by evaluate is the input the real-time surface consumes: the same exceedance rows drive the alerting described in Python Logic for Detecting MCL Exceedances in Real Time, which handles the single-sample acute limits that resolve far faster than a four-quarter average.

Configuration Reference

The table below fixes the parameters the routine depends on. Treat the limits and sample counts as versioned reference data pulled from the rule, not as inline literals scattered through the code.

Verification & Testing

The decisive test is that a per-location LRAA is never diluted by other locations. Build a two-location frame where one site sits comfortably under the limit and the other runs hot, then assert that the hot site is flagged on its own and that a mistaken system-wide mean would have missed it.

import pandas as pd
from lraa import evaluate, quarterly_location_means, STAGE2_MCL


def _four_quarters(location, analyte, values):
    base = pd.Timestamp("2024-01-15", tz="UTC")
    rows = []
    for i, v in enumerate(values):
        rows.append({
            "location_id": location,
            "analyte": analyte,
            "sample_ts": base + pd.DateOffset(months=3 * i),
            "result_mgl": v,
        })
    return rows


def test_hot_location_flagged_on_its_own():
    rows = (
        _four_quarters("DIST-07", "TTHM", [0.088, 0.091, 0.090, 0.089])  # hot
        + _four_quarters("DIST-02", "TTHM", [0.030, 0.028, 0.031, 0.029])  # cool
    )
    result = evaluate(pd.DataFrame(rows))
    hot = result[(result.location_id == "DIST-07") & result.lraa.notna()]
    assert bool(hot["exceedance"].iloc[-1]) is True
    # A system-wide mean of the two sites would fall under 0.080 and hide it.
    system_wide = result[result.lraa.notna()]["lraa"].mean()
    assert system_wide < STAGE2_MCL["TTHM"]


def test_partial_window_is_undefined():
    rows = _four_quarters("DIST-07", "HAA5", [0.055, 0.058, 0.061])  # only 3
    means = quarterly_location_means(pd.DataFrame(rows).assign(
        quarter=pd.PeriodIndex(pd.to_datetime([r["sample_ts"] for r in rows]), freq="Q")
    ))
    result = evaluate(pd.DataFrame(rows))
    assert result["lraa"].notna().sum() == 0  # no LRAA before four quarters

Acceptance criteria before this routine gates a compliance determination:

• Grouping key includes location_id at every stage; no reduction collapses locations together.
• Quarterly mean is computed before the four-quarter mean (two-stage averaging), not a single mean of all samples in the year.
• An LRAA is emitted only after four consecutive quarters; partial windows resolve to NaN.
• A location with a missing quarter does not average across a five-calendar-quarter span.
• TTHM is compared to 0.080 mg/L and HAA5 to 0.060 mg/L, each on its own LRAA.
• The hot-location test fails if the grouping ever drops location_id.

Troubleshooting & Gotchas

• Every location suddenly reports the same LRAA. The grouping key lost location_id somewhere — often in an intermediate groupby(["analyte", "quarter"]) added for a summary view and then reused. Once locations are pooled you have computed a system-wide running annual average, which is a different regulatory quantity and will under-report a localized breach. Keep the location in the key until the final comparison.
• An LRAA appears one quarter too early or spans a gap. A positional rolling(4) counts rows, not calendar quarters. If a site skipped a quarter, four rows can cover five quarters. Reindexing each location onto a complete period_range before rolling turns the skipped quarter into a NaN that correctly suppresses the window.
• Quarter assignment drifts at year end. Bucketing on raw month arithmetic instead of a pandas Period mishandles the December-to-January boundary, so a January sample lands in the wrong annual window. Always derive the quarter with dt.to_period("Q") on a timezone-aware timestamp.
• HAA5 looks low because a member acid is missing. HAA5 is the sum of five acids; if the lab reports fewer, the summed result is biased low before it ever reaches this routine. Validate the analyte composition upstream and reject incomplete HAA5 records rather than averaging a partial sum.
• A brand-new site shows no violations for a year. That is correct, not a bug: with min_periods=4, a location cannot produce an LRAA until it has four quarters of data, so its first-year exceedances surface only once the window fills.

Related

• MCL Exceedance Logic Implementation — the parent section that houses both the averaging rules and the instantaneous checks
• Python Logic for Detecting MCL Exceedances in Real Time — the single-sample counterpart to this slow four-quarter average
• SDWA MCL Reference Mapping — where the 0.080 and 0.060 mg/L limits and analyte definitions are catalogued
• Resampling Irregular Sensor Data to Compliance Windows — preparing clean, timestamped inputs before quarters are assigned
• Violation Detection & Rule Engine Logic — the broader rule-engine architecture these determinations feed