Energy Settlement Reconciliation Automation Hub

Normalizing interval meter data with pandas

A raw MDM export arrives with 15-minute reads in mixed units, naive local timestamps that hide a daylight-saving overlap, and a scattering of nulls where the head-end dropped comms — and the settlement engine downstream expects a single hourly, kilowatt-hour, timezone-aware series with every substituted interval flagged. This page is the concrete pandas transformation that closes that gap end to end. It is one implementation within Metering Data Integration, which frames where VEE, unit normalization, and interval alignment sit in the wider ingestion tier; here we write the actual code.

The diagram below traces the specific transformation this page implements, from a raw file read to a hashed, settlement-interval-aligned frame.

Interval meter normalization transformation A raw MDM CSV is read into a dataframe, its naive timestamps localized to a timezone-aware market-clock index, its mixed-unit values converted to a kilowatt-hour basis, resampled and summed to the hourly settlement interval with estimated intervals flagged, and emitted as a clean interval-indexed frame with a content hash. Read CSVnaive 15-min reads Localizetz-aware index Convert unitsto kWh basis Resample + flaghourly · estimates Clean frame+ content hash

Prerequisites

  • Python packages: pandas (2.0+ for the zoneinfo-backed tz handling used here) and the standard-library zoneinfo, decimal, and hashlib modules. No third-party timezone library is required.
  • Data dependency: a raw MDM interval export as CSV with columns meter_id, interval_start (naive local wall-clock), usage_raw, and unit (one of Wh, kWh, MWh). A read may be null where the head-end dropped an interval.
  • Configuration: the market clock (an IANA zone such as America/New_York) and the target settlement interval (1h here). Both are keyed to the trading day upstream and passed in — never hard-coded in the transformation.
  • Permissions: read access to the MDM export drop location. The transformation is compute-only; it emits a frame and a hash and writes nothing back to the meter system.

Implementation

The function below is the complete transformation. It defers the Decimal cast to the unit conversion boundary so the vectorized resample stays in fast native dtypes, localizes with explicit daylight-saving handling so neither the spring gap nor the fall overlap is silently mishandled, and carries an estimate flag through the resample so a substituted interval is never confused with a measured one. Inline comments mark the decisions that most often break naive implementations.

import hashlib
import pandas as pd
from decimal import Decimal
from zoneinfo import ZoneInfo

# Canonical settlement basis is kilowatt-hours. Decimal multipliers keep the
# unit conversion exact; a silent 1000x error here corrupts every downstream charge.
UNIT_TO_KWH = {"Wh": Decimal("0.001"), "kWh": Decimal("1"), "MWh": Decimal("1000")}


def normalize_interval_meter(
    csv_path: str,
    market_tz: str,
    settlement_interval: str = "1h",
) -> pd.DataFrame:
    """
    Parse a raw MDM interval export and return a clean, settlement-interval-aligned,
    timezone-aware frame indexed by interval_start, with an estimate flag per interval.
    """
    # 1. Read the raw export. Parse the timestamp column but keep it naive for now —
    #    localization is an explicit, DST-aware step, not an implicit parse.
    raw = pd.read_csv(csv_path, parse_dates=["interval_start"])

    # 2. Localize to the market clock. nonexistent="shift_forward" moves a
    #    spring-forward wall-clock time that never occurred to the next valid instant;
    #    ambiguous="infer" resolves the fall-back duplicated hour by monotonic order.
    idx = pd.DatetimeIndex(raw["interval_start"]).tz_localize(
        ZoneInfo(market_tz), nonexistent="shift_forward", ambiguous="infer"
    )
    raw = raw.set_index(idx).drop(columns=["interval_start"]).sort_index()

    # 3. Convert every value to the canonical kWh basis using its declared unit.
    #    Cast through Decimal for an exact multiply, then back to float for the
    #    vectorized resample. An unknown unit raises rather than passing through.
    def _to_kwh(row) -> float:
        factor = UNIT_TO_KWH[row["unit"]]                    # KeyError => bad unit
        return float(Decimal(str(row["usage_raw"])) * factor) if pd.notna(row["usage_raw"]) else float("nan")

    raw["usage_kwh"] = raw.apply(_to_kwh, axis=1)

    # 4. Flag estimated intervals BEFORE resampling. A null read is a comms gap that
    #    must be estimated; a present read is measured. The flag rides the resample.
    raw["is_estimated"] = raw["usage_kwh"].isna()

    # 5. Fill the gaps deterministically. Time-weighted interpolation for interior
    #    gaps; the flag already records that these intervals are substituted, not measured.
    raw["usage_kwh"] = raw["usage_kwh"].interpolate(method="time", limit_direction="both")

    # 6. Resample to the settlement interval. Sum energy across the sub-intervals;
    #    an hour is "estimated" if ANY constituent 15-minute read was substituted.
    resampled = pd.DataFrame({
        "usage_kwh": raw["usage_kwh"].resample(settlement_interval).sum(),
        "is_estimated": raw["is_estimated"].resample(settlement_interval).max(),
        "meter_id": raw["meter_id"].resample(settlement_interval).first(),
    })

    # 7. Stamp the estimate flag and a per-interval content hash binding the value,
    #    interval, and meter for tamper-evident replay.
    resampled["estimate_flag"] = resampled["is_estimated"].map(
        {True: "ESTIMATED_INTERP", False: "MEASURED"}
    )
    resampled["row_hash"] = [
        hashlib.sha256(
            f"{mid}|{ts.isoformat()}|{kwh:.6f}".encode()
        ).hexdigest()
        for mid, ts, kwh in zip(
            resampled["meter_id"], resampled.index, resampled["usage_kwh"]
        )
    ]
    resampled.index.name = "interval_start"
    return resampled[["meter_id", "usage_kwh", "estimate_flag", "row_hash"]]

The ordering is load-bearing. Flagging estimates in step 4 before the interpolation in step 5 is what keeps a substituted value distinguishable from a measured one after resampling — the is_estimated boolean is captured while the null still exists, then carried through the resample with a max aggregation so any estimated sub-interval marks the whole settlement interval estimated. Reversing those two steps would erase the distinction the settlement ledger depends on. The DST-aware localization in step 2 is the other pivot: without explicit nonexistent and ambiguous arguments, pandas raises or silently drops the boundary hours, so the resample would sum a 23- or 25-hour day against a 24-hour expectation.

Verification

Confirm the output before it feeds the settlement engine. Three checks catch the failure modes this transformation is built to prevent.

def verify_normalized_frame(df: pd.DataFrame, market_tz: str) -> None:
    """Assert shape, timezone, and hash stability of the normalized frame."""
    # 1. Timezone: the index must be tz-aware in the market clock, never naive.
    assert df.index.tz is not None, "index lost its timezone"
    assert str(df.index.tz) == market_tz, f"wrong tz: {df.index.tz}"

    # 2. Expected shape: columns fixed, no null energy after interpolation.
    assert list(df.columns) == ["meter_id", "usage_kwh", "estimate_flag", "row_hash"]
    assert df["usage_kwh"].notna().all(), "null energy survived normalization"

    # 3. Hash stability: re-hashing the persisted values must reproduce row_hash,
    #    proving no interval was mutated after normalization.
    import hashlib
    for mid, ts, kwh, h in zip(
        df["meter_id"], df.index, df["usage_kwh"], df["row_hash"]
    ):
        recomputed = hashlib.sha256(f"{mid}|{ts.isoformat()}|{kwh:.6f}".encode()).hexdigest()
        assert recomputed == h, f"hash mismatch at {ts}"

Beyond these assertions, reconcile the frame two ways. First, an interval-count check: a standard trading day should yield exactly 24 hourly rows, a spring-forward day 23, and a fall-back day 25 — any other count means the localization mishandled a boundary. Second, a sum-reconciliation diff: the summed usage_kwh should match the meter’s cumulative register movement over the window within tolerance; a gap means intervals were dropped or double-counted in the resample. The estimate share (df["estimate_flag"].eq("ESTIMATED_INTERP").mean()) is the third number to record — it quantifies how much of the day rested on substitution and feeds the estimation-rate alerting in the parent component.

Compliance Note

This transformation must be validated against the applicable state VEE (validation, estimation, and editing) requirements and the market’s settlement tariff. Two obligations bear directly on the code above. First, every estimated interval must be flagged and its substitution method recorded — the estimate_flag and the interpolation choice here are the auditable record that satisfies the editing requirement, letting a true-up prove which intervals were measured versus substituted. Second, the settlement interval and market clock are tariff-fixed inputs, not implementation choices; resampling to the wrong grid or localizing to the wrong zone produces charges that reconcile internally but fail against the ISO. The row_hash provides the tamper-evident lineage that FERC traceability expects, binding each persisted value to its meter and interval so a historical replay reproduces both the number and its provenance. Where the raw file structure itself is in question, validate it against the field-level conventions in ISO/RTO Data Format Standards.

Frequently Asked Questions

Why localize timestamps explicitly instead of parsing them as UTC?

Meter reads are recorded in the market’s prevailing local clock, and settlement is defined against that clock, so the wall-clock time carries meaning that a blind UTC parse would discard. Explicit localization with nonexistent and ambiguous arguments is also the only way to handle the daylight-saving boundaries correctly — the spring-forward hour that never occurred and the fall-back hour that occurs twice. Parsing straight to UTC either raises on those boundaries or silently collapses them, mis-summing the affected day.

How do I keep an estimated interval distinguishable after resampling?

Capture the estimate flag before you fill any gaps, while the null read still exists, then carry that boolean through the resample with a max aggregation so any estimated sub-interval marks the whole settlement interval as estimated. If you interpolate first and flag afterward, the substituted value is indistinguishable from a measured one and the settlement ledger loses the ability to attribute a later reconciliation difference to a specific estimate.

What settlement interval should I resample to?

The settlement interval is fixed by the market’s tariff — typically 5-minute for real-time and hourly for day-ahead — and is passed in as configuration keyed to the trading day, never hard-coded. Resampling sub-interval reads to a coarser settlement grid sums their energy; splitting a coarser read to a finer grid distributes it. Using the wrong interval yields a frame that is internally consistent but fails reconciliation against the ISO’s settlement statement.