Implementing Tiered Block Rates with Pandas

Tiered block rates look trivial on a rate card and turn subtle the moment you vectorize them. A block schedule prices consumption marginally — each tier’s volume is charged at that tier’s own rate — which is the model detailed in Step-Rate vs Block-Rate Structure Design. The failure this page resolves is the one that hides inside a fast Pandas pipeline: you clip consumption into tiers with NumPy, multiply by a rate vector, and ship a batch of bills that reconcile a few cents off per account because the arithmetic ran in IEEE-754 float. At municipal scale that fractional-cent drift is real revenue variance an auditor will flag, and a schedule whose rate codes did not all resolve will have silently billed some accounts at zero. The remedy is a vectorized slice that stays fast, a hard boundary between the float-speed clipping step and the decimal.Decimal money step, explicit quarantine for unmatched rate codes, and a reconciliation assertion that refuses to post a batch whose tiered charges do not tie to an independently derived control total.

Prerequisites

This targets a reproducible Python 3.11+ stack; assume the following imports and data shape and nothing else.

import pandas as pd
import numpy as np
from decimal import Decimal, ROUND_HALF_UP

Data assumptions: meter_reads is a DataFrame with one row per account for the cycle, carrying account_id, customer_class, service_tier, a jurisdiction_code derived from the service address, and a net_consumption already normalized to billable units (rollovers and reversals resolved upstream). A separate rate_lookup frame binds each (customer_class, service_tier) to a rate_schedule_id. The block schedule itself — ordered tier bounds and per-tier rates — is authoritative configuration, not a code literal; write access to it is governed by Security Boundaries & Role-Based Access. Any read that arrives with a null or unresolved schedule is out of scope here and belongs to Fallback Routing for Missing Rate Data; this page assumes the mapping either resolves or is quarantined.

Annotated Implementation

Two operations, kept deliberately separate. First a deterministic merge that routes any unmatched (customer_class, service_tier) pair to a quarantine frame instead of letting a left join leave NaN rates to be silently treated as zero. Missing rate codes are the primary way an automated cycle over-bills or under-bills without raising anything.

def map_rate_schedules(
    meter_reads: pd.DataFrame, rate_lookup: pd.DataFrame
) -> tuple[pd.DataFrame, pd.DataFrame]:
    """Merge reads to rate codes, diverting unmatched pairs to quarantine."""
    merged = meter_reads.merge(
        rate_lookup,
        on=["customer_class", "service_tier"],
        how="left",
        indicator=True,          # exposes which side each row matched on
    )
    valid = merged[merged["_merge"] == "both"].drop(columns="_merge").copy()

    # Unmatched rows are a controlled state, not a defect to bill at zero.
    quarantine = merged[merged["_merge"] == "left_only"].drop(columns="_merge").copy()
    quarantine["quarantine_reason"] = "MISSING_RATE_CODE"

    # A monotonic upper-bound column is the invariant every slice below relies on.
    assert valid["tier_upper_bound"].is_monotonic_increasing, "tier bounds must ascend"
    return valid, quarantine

The core charge engine. The clipping is vectorized in float for speed, then every currency multiplication crosses into Decimal before a single cent is accumulated — mixing float and Decimal raises TypeError, which is exactly the guardrail you want, because a silent float multiply is the drift you are trying to eliminate. Jurisdictional fees and assistance credits are applied after volumetric pricing, never before: a discount that shrank a tier volume would push usage across the wrong boundary and misprice the whole bill.

Vectorized decimal-safe block-rate slicing for one account A worked pipeline for an account consuming 8200 units. A horizontal bar is clipped against ascending tier bounds at 0, 1000, 3000, 6000 and an uncapped top, subtracting each floor so the bar splits into tier volumes of 1000, 2000, 3000 and 2200 units. The float clip step produces those volumes; then each volume crosses into Decimal and is multiplied by its own marginal rate to give 4.50, 12.40, 24.30 and 23.10. The four products sum to a tiered charge of 64.30. Overlays are applied last: fixed fees are added to form the gross charge, an assistance credit is subtracted and floored at zero, and the result is quantized once to cents with ROUND_HALF_UP to yield the final bill amount. net_consumption = 8200 units np.clip(x, lower, upper) − lower · float, fast 1000 2000 3000 2200 0 1000 3000 6000 ∞ top tier uncapped cross into Decimal — Decimal(str(vol)) × rate Tier 1 1000 × 0.0045 = 4.50 Tier 2 2000 × 0.0062 = 12.40 Tier 3 3000 × 0.0081 = 24.30 Tier 4 2200 × 0.0105 = 23.10 Σ tier products (Decimal) tiered_charge = 64.30 overlays applied last, to the priced charge + fixed fees → gross_charge − assistance credit floored ≥ 0 quantize 0.01 ROUND_HALF_UP final_bill _amount quantize once, at the charge boundary — never per tier a discount applied before slicing would push usage across the wrong boundary

Figure: One account priced end to end. The clip step runs in fast float to split 8200 units into tier volumes; each volume then crosses into Decimal for its marginal multiply, the products sum to the tiered charge, and fixed fees, the floored assistance credit, and a single ROUND_HALF_UP quantize are applied last to yield the final bill.

# Block schedule loaded from governed config; shown inline for clarity.
LOWER_BOUNDS = np.array([0, 1000, 3000, 6000])          # unit floors per tier
UPPER_BOUNDS = np.array([1000, 3000, 6000, np.inf])     # inf = uncapped top tier
MARGINAL_RATES = [Decimal("0.0045"), Decimal("0.0062"),
                  Decimal("0.0081"), Decimal("0.0105")]  # $/unit per tier
ZERO, CENTS = Decimal("0"), Decimal("0.01")

def calculate_block_charges(valid: pd.DataFrame) -> pd.DataFrame:
    """Marginal, decimal-safe block-rate charges over a DataFrame."""
    consumption = valid["net_consumption"].to_numpy(dtype=float)

    # Vectorized slice: clip each account's usage into every tier's window,
    # then subtract the floor so each column holds the volume *in* that tier.
    tier_volumes = np.clip(consumption[:, None], LOWER_BOUNDS, UPPER_BOUNDS) - LOWER_BOUNDS
    tier_volumes = np.maximum(tier_volumes, 0)   # never a negative tier volume

    # Cross into Decimal for the money math; quantize once, at the end.
    tiered = []
    for row in tier_volumes:
        total = ZERO
        for vol, rate in zip(row, MARGINAL_RATES):
            total += Decimal(str(vol)) * rate    # Decimal(str(...)) avoids float repr drift
        tiered.append(total)

    valid = valid.copy()
    valid["tiered_charge"] = pd.Series(tiered, index=valid.index)

    # Overlays apply to the priced bill, not to consumption.
    fixed_fees = valid.get("fixed_fees", pd.Series(ZERO, index=valid.index))
    credits = valid.get("program_credits", pd.Series(ZERO, index=valid.index))
    valid["gross_charge"] = valid["tiered_charge"] + fixed_fees
    # Cap a credit at the gross charge — a bill never goes negative here.
    valid["net_charge"] = (valid["gross_charge"] - credits).apply(
        lambda x: x if x > ZERO else ZERO
    )
    valid["final_bill_amount"] = valid["net_charge"].apply(
        lambda x: x.quantize(CENTS, rounding=ROUND_HALF_UP)
    )
    return valid

Quantizing exactly once, at the charge boundary, is the decisive move: rounding per tier would seed the very fractional-cent variance reconciliation cannot later close. The order of overlays matters too — franchise fees, stormwater assessments, and taxes resolve from jurisdiction_code and layer onto the priced volumetric charge, mirroring Surcharge & Fee Application Logic, while credits from Assistance Program Eligibility Taxonomy apply last and are floored so a percentage discount can never drive a net charge below zero.

Edge Cases and Billing Gotchas

  • Exact threshold hits. An account landing on precisely 3000 units must fill tiers 1 and 2 and leave tier 3 at zero volume — not spill a unit into the next band. Because np.clip(x, lower, upper) - lower yields zero for any tier whose floor is at or above x, boundary values allocate exactly with no double counting. Verify this against boundary fixtures rather than trusting it by eye.
  • Uncapped top tier. The final UPPER_BOUNDS entry must be np.inf. A finite ceiling silently caps the highest-usage accounts and under-bills them; inf lets the clip carry all remaining volume into the top band.
  • Negative net consumption. A meter reversal can produce a negative net_consumption. np.maximum(tier_volumes, 0) prevents a negative delta from generating a spurious credit, but a negative read is a defect, not a discount — flag it for manual review upstream rather than pricing it, consistent with the corruption-vs-gap distinction in Fallback Routing for Missing Rate Data.
  • Prorated and short cycles. When cycle lengths vary, tier bounds authored as monthly volumes over-charge a long cycle and under-charge a short one. Normalize consumption to a daily average, price against per-day tier bounds, then scale by actual cycle days — never apply a monthly tier table to a 40-day read.

Verification Snippet

Correctness is confirmed by tying the sum of tiered charges to an independently derived control total before anything posts. Compute each account’s expected charge the slow, obvious way and assert the vectorized engine agrees to the cent.

def control_total_charge(usage: Decimal) -> Decimal:
    """Reference marginal calculation, one account, no vectorization."""
    total, lower = ZERO, Decimal("0")
    for lo, hi, rate in zip(LOWER_BOUNDS, UPPER_BOUNDS, MARGINAL_RATES):
        ceiling = Decimal(str(hi)) if np.isfinite(hi) else usage
        slice_vol = max(Decimal("0"), min(usage, ceiling) - Decimal(str(lo)))
        total += slice_vol * rate
    return total.quantize(CENTS, rounding=ROUND_HALF_UP)

fixture = pd.DataFrame({
    "account_id": ["A1", "A2", "A3"],
    "net_consumption": [500, 3000, 8200],   # sub-tier, exact edge, top tier
})
priced = calculate_block_charges(fixture)

for _, r in priced.iterrows():
    expected = control_total_charge(Decimal(str(r["net_consumption"])))
    assert r["tiered_charge"].quantize(CENTS) == expected, r["account_id"]

# Batch-level tie-out: the posted total must equal the reference total.
batch = priced["tiered_charge"].map(lambda x: x.quantize(CENTS)).sum()
reference = sum(control_total_charge(Decimal(str(u))) for u in fixture["net_consumption"])
assert batch == reference, "batch does not reconcile to control total"

Run the same tie-out against a corpus of historical bills whose totals are already known; a variance beyond a single cent per account signals a float leak or a mis-authored tier bound and blocks the batch. Every applied schedule, quarantine diversion, and reconciliation result should also emit a metadata-only audit record — account IDs, amounts, and timestamps, never PII — per Data Governance & Privacy Compliance, so an auditor can reconstruct exactly how each charge was priced.

Frequently Asked Questions

Why not just keep everything in float and round at the end?
Because IEEE-754 float cannot represent most rate values exactly, so errors accumulate across tiers and accounts before you round, and the batch total drifts from any independently computed control total. Rounding once at the end hides but does not remove the drift. Clipping in float is fine for speed, but every currency multiplication must cross into decimal.Decimal, and the result is quantized a single time with ROUND_HALF_UP.
What happens to an account whose rate code does not resolve?
It is diverted to the quarantine frame with a MISSING_RATE_CODE reason and is not priced. A left join would leave a NaN rate that arithmetic then treats as zero, silently posting a zero bill. Quarantined accounts are handed to the fallback routing layer, which produces a defensible provisional estimate and tags the account for true-up rather than shipping a wrong charge.
Why apply assistance credits and jurisdictional fees after tier slicing instead of before?
Because a discount or fee applied to consumption would change which tiers the usage fills and misprice the whole bill. Tier slicing must run on true metered volume; fees and credits then decorate the priced charge. Credits are also floored at the gross charge so a percentage discount can never drive a net bill below zero — any excess is carried forward or written off, not billed as a negative.
Does an account landing exactly on a tier boundary get double-counted?
No. The slice expression np.clip(x, lower, upper) - lower yields zero volume for any tier whose floor sits at or above the consumption value, so an account at exactly 3000 units fills the lower tiers and leaves the next band empty. It is still worth asserting against explicit boundary fixtures, because a mis-ordered or non-monotonic bound table would break the invariant.
How do I keep this fast on millions of rows without losing precision?
Keep the clip step vectorized in float over the whole column, which is where the speed lives, and only cross into Decimal for the per-tier multiply and the final quantize. For very large batches, process in chunks so the Decimal loop stays cache-friendly, and use Decimal(str(value)) rather than Decimal(float) so no binary-float representation error leaks into the money math.

Up one level: Step-Rate vs Block-Rate Structure Design · Parent guide: Municipal Utility Billing Architecture & Rate Taxonomy · Return to the utilitybilling.org home.