Pandas vs Polars for settlement batch processing

The nightly settlement batch starts missing its 04:00 close window because a single-threaded pandas job is materializing a full quarter of five-minute interval data into RAM, spilling to swap, and taking ninety minutes to net what should net in twelve. Swapping the engine looks like the fix — but a naive port can quietly change how money rounds, and a settlement statement that reconciles to the wrong cent is worse than a slow one. This guide is a decision framework: when Polars earns its place in a settlement pipeline, when pandas is still the right call, and how to run the two side by side without breaking the audit trail. It sits under Pandas for Trade Data Processing inside the broader Trade Ingestion & Matching Workflows framework, and assumes the typed, quarantine-checked frame that upstream parsing already produced.

The diagram below frames the choice as a routing decision rather than a religion: the same validated interval feed enters, and a handful of batch characteristics decide which engine runs the heavy netting before both converge on the identical Decimal-precise ledger contract.

Prerequisites #

• Python 3.11+ with pandas>=2.1 (PyArrow-backed dtypes available) and polars>=1.0. Install PyArrow explicitly (pyarrow>=15) so both engines can hand Arrow buffers to each other without a serialization copy.
• A representative batch sample — one full settlement day of interval data at production width, so throughput and peak-memory numbers reflect your real schema rather than a toy frame.
• A memory ceiling for the batch host. Know the container’s RAM limit; the engine choice below turns on whether the working set fits in it, and Polars’ streaming path exists precisely for when it does not.
• The ledger column contract — the fixed output schema (settlement_interval, node_id, imbalance_mwh, charge_amount, run_id) the downstream persistence layer expects, identical regardless of which engine computes it.

Implementation #

Start from the decision itself, because it drives everything else. The two engines are not interchangeable line-for-line: pandas evaluates eagerly and keeps the whole frame resident, while Polars builds a lazy query plan it can optimize and stream. For settlement work the axes that matter are memory footprint on wide interval files, execution model, raw throughput on the nightly join-and-aggregate, how each handles Decimal money, ecosystem depth, and the cost of migrating existing code.

Read the table as a routing rule, not a scoreboard. A batch that fits comfortably in RAM and leans on a connector only pandas has should stay on pandas — the rewrite buys nothing. A batch that no longer fits, or whose nightly window is blown by single-threaded aggregation, is exactly what Polars’ streaming lazy engine was built for.

The same netting job in each engine #

The canonical settlement batch is: load a day of interval imbalance, join a loss-factor reference, aggregate to a per-node charge, and hand it to the ledger. Here it is in pandas, eager and explicit, with money kept in Decimal.

from decimal import Decimal, getcontext
import pandas as pd

getcontext().prec = 28  # ample headroom for MWh * $/MWh money math

def net_batch_pandas(interval_path: str, loss_path: str) -> pd.DataFrame:
    # Eager read — the whole day materializes in memory up front
    intervals = pd.read_parquet(interval_path)          # settlement_interval, node_id, imbalance_mwh
    loss = pd.read_parquet(loss_path)                    # node_id, loss_factor

    # Keep money/quantity as Decimal, never float, before any arithmetic
    intervals["imbalance_mwh"] = intervals["imbalance_mwh"].map(Decimal)
    loss["loss_factor"] = loss["loss_factor"].map(Decimal)

    merged = intervals.merge(loss, on="node_id", how="left")
    if merged["loss_factor"].isna().any():
        raise ValueError("Unmapped node_id in loss reference — halt before netting")

    # Grossed-up imbalance per interval, then sum to a per-node charge
    merged["grossed_mwh"] = merged["imbalance_mwh"] * merged["loss_factor"]
    charge = (
        merged.groupby("node_id")["grossed_mwh"]
        .apply(lambda s: sum(s, Decimal("0")))          # exact Decimal reduction
        .reset_index(name="charge_amount")
    )
    return charge

The groupby(...).apply(lambda s: sum(s, Decimal("0"))) is the tell: pandas cannot vectorize a Decimal sum, so it falls back to per-group Python iteration. That is correct to the cent but slow, and on a quarter of intervals it is a large part of why the window is blown.

Now the same job in Polars, lazy and streaming, using the native Decimal dtype so the reduction stays vectorized and exact.

import polars as pl

def net_batch_polars(interval_path: str, loss_path: str) -> pl.DataFrame:
    # Lazy scans — no data is read until .collect(); the optimizer plans first
    intervals = pl.scan_parquet(interval_path).with_columns(
        pl.col("imbalance_mwh").cast(pl.Decimal(scale=6))   # exact, not Float64
    )
    loss = pl.scan_parquet(loss_path).with_columns(
        pl.col("loss_factor").cast(pl.Decimal(scale=9))
    )

    plan = (
        intervals.join(loss, on="node_id", how="left")
        # Fail loud on an unmapped node before it silently zeroes a charge
        .filter(pl.col("loss_factor").is_null())
        .select(pl.len().alias("unmapped"))
    )
    if plan.collect(streaming=True).item() > 0:
        raise ValueError("Unmapped node_id in loss reference — halt before netting")

    charge = (
        intervals.join(loss, on="node_id", how="left")
        .with_columns((pl.col("imbalance_mwh") * pl.col("loss_factor")).alias("grossed_mwh"))
        .group_by("node_id")
        .agg(pl.col("grossed_mwh").sum().alias("charge_amount"))
    )
    # streaming=True processes the plan in chunks — bounded memory on huge files
    return charge.collect(streaming=True)

The Polars version never holds the full grossed-up frame at once: collect(streaming=True) executes the optimized plan in bounded batches, and pl.Decimal keeps the multiply-and-sum exact without dropping to per-row Python. That combination — exact money and a streaming plan — is the specific reason to reach for Polars on the heavy nightly batch. The grossing-up arithmetic here is the same operation formalized in Mapping transmission loss factors to settlement nodes; only the engine changes.

Running both without forking the pipeline #

You do not have to migrate wholesale. Because both engines speak Arrow, a pragmatic pattern is to route by batch size at the boundary and normalize back to a single frame type before persistence, so the rest of the Async Batch Processing Pipelines stack sees one contract.

def net_batch(interval_path: str, loss_path: str, row_estimate: int, mem_budget_rows: int) -> pd.DataFrame:
    if row_estimate > mem_budget_rows:
        # Large batch: Polars streams it, then hand back a pandas frame at the seam
        pl_result = net_batch_polars(interval_path, loss_path)
        return pl_result.to_pandas(use_pyarrow_extension_array=True)
    # Small batch: pandas is fine and keeps the connector-rich path
    return net_batch_pandas(interval_path, loss_path)

Keep the seam thin and typed. The one rule that cannot bend is that money crosses the seam as Decimal — never as Float64 — so the engine boundary is invisible to the ledger and to any later reconciliation against the Schema Validation Frameworks contract.

Verification #

Prove the two engines agree before you trust either in production. The invariant is that for the same input, the reconciled per-node charge is identical to the cent regardless of engine:

$$\sum_{n} \text{charge}^{,\text{pandas}}{n} = \sum{n} \text{charge}^{,\text{polars}}_{n}$$

def verify_engine_parity(pandas_charge: pd.DataFrame, polars_charge: pd.DataFrame) -> None:
    a = pandas_charge.set_index("node_id")["charge_amount"].map(Decimal).sort_index()
    b = polars_charge.to_pandas().set_index("node_id")["charge_amount"].map(Decimal).sort_index()
    assert a.index.equals(b.index), "Node set differs between engines"
    # Exact Decimal equality per node — no floating tolerance permitted for money
    mismatches = {n: (a[n], b[n]) for n in a.index if a[n] != b[n]}
    assert not mismatches, f"Engine charge mismatch on {len(mismatches)} nodes: {mismatches}"

Confirm three things before promoting an engine swap: verify_engine_parity passes on a full production day; peak resident memory (measured with resource.getrusage or a container metric) drops below the host ceiling on the Polars path; and the wall-clock batch time clears the close window with margin. Only when parity holds exactly should the routing threshold move toward Polars — a faster batch that shifts a charge by a cent is a failed migration, not a win.

Compliance Note #

FERC and NERC settlement standards require that reported figures be reproducible and that the calculation lineage be auditable, and neither standard cares which dataframe engine you used — only that the numbers are exact and traceable. Two controls make an engine choice defensible. First, money must never touch binary float: use per-cell Decimal in pandas or the pl.Decimal dtype in Polars, because a Float64 sum over a quarter of intervals accumulates representation error that will fail a penny-level reconciliation. Second, record the engine, engine version, and query plan in the run’s audit record keyed by run_id and settlement date, so a resettlement can replay the exact path — the same lineage discipline the Trade Ingestion & Matching Workflows framework applies to every transformation. When both controls hold, the engine becomes an implementation detail the auditor never has to worry about.

Frequently Asked Questions #

Is Polars always faster than pandas for settlement work? #

No. Polars wins decisively when the batch is large relative to RAM or when the nightly aggregation is single-threaded-bound in pandas — its multi-threaded, streaming lazy engine is built for exactly that. But for batches that fit comfortably in memory, or jobs dominated by a connector or ISO tool that only pandas has, the speedup is marginal and does not justify the rewrite. Choose by batch size, memory ceiling, and query complexity, not by reputation.

Does switching engines change how settlement money rounds? #

It can if you are careless. Both engines default numeric columns to binary float, which is unsafe for money. Keep quantities and charges as Decimal per cell in pandas and as the native pl.Decimal(precision, scale) dtype in Polars, and set the scale to match your tariff’s rounding rules. Verify penny-exact parity between the two engines on a full production day before promoting a swap; if any node differs by a cent, the migration is not done.

Can I migrate incrementally instead of all at once? #

Yes, and it is usually the right call. Because both engines share Arrow buffers, you can route large batches to a Polars streaming path and leave small or connector-bound batches on pandas, then convert back to a single frame type at a thin seam before persistence. The one hard rule is that money crosses the seam as Decimal, so the ledger contract stays identical no matter which engine produced the numbers.

Related #

• Parsing CSV vs XML trade feeds with pandas
• DST gap and overlap in pandas timestamp normalization
• Pandas for Trade Data Processing