Building Mileage-Band Rate Lookups from PCMILER Exports

You built a mileage-band rate table straight from a mileage-engine export, it validated against a handful of lanes, and in production it prices a measurable share of shipments into the wrong band.

The failure you are hitting

The lookup does not crash. It returns a rate for almost every lane, the linehaul figure looks plausible, and the audit passes it — which is exactly why the error survives to reconciliation. Three symptoms show up, none of them as an exception:

  • Long-haul moves resolve one band too cheap. A 620-mile lane that should sit in the 500–1000 band lands in 250–500 because the export you loaded carried shortest miles (591) while the contract prices practical miles (623). Every affected shipment under-recovers by the band-rate gap, and nothing flags it.
  • Boundary lanes flip between runs. A move that computes to exactly 500 miles lands in the lower band on one build and the upper band on the next, because the digitized table stored bounds as inclusive-inclusive and two adjacent bands both claim 500.
  • A batch of lanes returns a rate that is simply wrong for their distance, because the band rows were loaded in the spreadsheet’s paste order and a binary search over an unsorted array walked into the wrong slot.

This is the load-time counterpart to the runtime lookup described in Mileage-Band Rate Lookups: the bucketing code is correct, but the structure it buckets against was built wrong.

Root cause analysis

The export is not the contract. A PCMILER or Rand McNally export is a distance dataset with its own routing assumptions, and turning it into a band-keyed rate structure fails on three independent conditions that a small sample never exercises:

  1. Routing-type mismatch. Mileage engines emit practical miles, shortest miles, and household-goods miles, and they differ by five to fifteen percent. If the contract prices practical miles and the export you loaded is shortest, every distance is systematically understated and long moves drift into cheaper bands.
  2. Ambiguous boundary semantics. A tariff prints bands as 250–500 and 500–1000 and leaves the reader to infer whether 500 belongs above or below. Digitized as two closed intervals, 500 matches both; digitized as half-open [250, 500) and [500, 1000), it matches exactly one. The ambiguity is resolved at build time or it haunts every boundary lane forever.
  3. Unsorted bands. The lookup uses a binary search for O(log n) speed, and binary search is only correct on a sorted array. A band table assembled in export order — or hand-edited later — violates the precondition silently, returning a neighbouring band’s rate with no error.
Building a validated band lookup from a mileage export Raw export rows enter a four-stage build. Stage one parses the export rows into typed records. Stage two normalizes the mileage type, rejecting the export if its routing type does not match the contract's expected type. Stage three builds sorted half-open bands, sorting by lower bound and asserting each band's exclusive upper equals the next band's inclusive lower. Stage four emits a validated lookup structure with a contiguity guarantee. A rejection branch leaves stage two when the mileage type is wrong, and a gap branch leaves stage three when bands are non-contiguous. Export rows origin · dest miles · type 1 · Parse typed records per lane 2 · Normalize mileage type enforce PRACTICAL type mismatch Reject export 3 · Sorted half-open bands assert contiguity band gap Fail build Validated lookup sorted · contiguous half-open bands ready for bisect
The build refuses to emit a lookup structure until the export's mileage type matches the contract and the bands are proven sorted and contiguous — the two guards that stop a plausible-looking table from mispricing boundary and long-haul lanes.

Reproducible diagnostic

Before touching the band definitions, prove which failure you have. This snippet reads the export and the contract’s expected mileage type, then reports the type match and whether the band bounds are sorted and contiguous:

import csv
from decimal import Decimal

EXPECTED_MILEAGE_TYPE = "PRACTICAL"     # from the contract terms

with open("pcmiler_export.csv") as fh:
    rows = list(csv.DictReader(fh))

types = {r["mileage_type"] for r in rows}
print("export mileage types:", types)
print("type match:", types == {EXPECTED_MILEAGE_TYPE})

# band bounds as digitized, in file order
bands = [(int(r["lower"]), int(r["upper"])) for r in rows if r.get("lower")]
print("file order lowers:", [b[0] for b in bands])
print("is sorted:", bands == sorted(bands))

for (l1, u1), (l2, u2) in zip(sorted(bands), sorted(bands)[1:]):
    if u1 != l2:
        print(f"GAP/OVERLAP: upper {u1} != next lower {l2}")

Read the output as a decision table:

Signal Likely cause Where to fix
export mileage types contains SHORTEST or HG routing-type mismatch enforce mileage type (Step 2)
is sorted is False bands in paste order sort before indexing (Step 3)
a GAP/OVERLAP line prints inclusive-inclusive bounds rebuild as half-open (Step 3)
all clean but boundary lanes still flip closed intervals at call site half-open lookup (Step 4)

A single export can trip more than one row — a shortest-miles table with paste-ordered bands fails Step 2 and Step 3 at once — so fix them in order rather than assuming the first signal is the whole story.

Resolution path

The fix is a four-stage build that parses the export, enforces the mileage type, constructs sorted half-open bands with a contiguity assertion, and exposes a half-open lookup. Pin the dependencies so the build is reproducible:

# requirements.txt
pydantic==2.10.6
structlog==24.4.0

Step 1 — Parse the export into typed rows

Coerce every field at the boundary so a stray string or blank never reaches the band math. Distances are integers, rates are Decimal, and the mileage type is carried through unchanged for the next stage to check.

# build/parse_export.py
import csv
from dataclasses import dataclass
from decimal import Decimal
from typing import List


@dataclass(frozen=True)
class ExportBand:
    lower: int
    upper: int          # as printed; half-open conversion happens in Step 3
    basis: str          # PER_MILE | FLAT
    amount: Decimal
    mileage_type: str


def parse_export(path: str) -> List[ExportBand]:
    out: List[ExportBand] = []
    with open(path, newline="") as fh:
        for row in csv.DictReader(fh):
            out.append(ExportBand(
                lower=int(row["lower"]),
                upper=int(row["upper"]),
                basis=row["basis"].strip().upper(),
                amount=Decimal(row["amount"].strip()),
                mileage_type=row["mileage_type"].strip().upper(),
            ))
    if not out:
        raise ValueError(f"{path} produced no band rows")
    return out

Step 2 — Enforce the mileage type

Refuse to build a table from an export whose routing type does not match the contract. This is the single guard that stops the systematic under-recovery, so it fails loudly rather than warning.

# build/enforce_type.py
from typing import List


def enforce_mileage_type(bands: List["ExportBand"], expected: str) -> None:
    """Reject the export if any row's routing type differs from the contract."""
    found = {b.mileage_type for b in bands}
    if found != {expected}:
        # Practical-priced contracts must not be built on shortest miles.
        raise ValueError(
            f"mileage type mismatch: export carries {sorted(found)}, "
            f"contract expects {expected!r} — re-export with the correct routing"
        )

Step 3 — Build sorted, half-open bands

Sort by lower bound, convert each printed upper into an exclusive bound equal to the next band’s lower, and assert contiguity. The top band’s upper becomes infinity so long hauls always have a home.

# build/build_bands.py
from decimal import Decimal
from typing import List


def build_half_open(bands: List["ExportBand"]) -> List[dict]:
    """Sort and convert printed ranges into contiguous half-open bands."""
    ordered = sorted(bands, key=lambda b: b.lower)
    result: List[dict] = []
    for i, b in enumerate(ordered):
        is_last = i == len(ordered) - 1
        # Exclusive upper = next band's lower; the printed upper is discarded
        # because a printed "500" is ambiguous but the next lower is not.
        upper_excl = float("inf") if is_last else ordered[i + 1].lower
        if not is_last and b.lower >= ordered[i + 1].lower:
            raise ValueError(f"non-increasing band edge at {b.lower}")
        result.append({
            "lower_incl": b.lower,
            "upper_excl": upper_excl,
            "basis": b.basis,
            "amount": b.amount,
        })
    # Contiguity: every upper must meet the next lower exactly.
    for prev, nxt in zip(result, result[1:]):
        if prev["upper_excl"] != nxt["lower_incl"]:
            raise ValueError(
                f"band gap: {prev['upper_excl']} != {nxt['lower_incl']}"
            )
    return result

Step 4 — Expose a half-open lookup

The lookup is a bisect_right over the sorted lower bounds, minus one — the same idiom the runtime uses, now guaranteed correct because Step 3 proved the array sorted and contiguous.

# build/lookup.py
import bisect
from decimal import Decimal, ROUND_HALF_UP
from typing import List, Optional


class BandLookup:
    def __init__(self, bands: List[dict]) -> None:
        self._bands = bands
        self._lowers = [b["lower_incl"] for b in bands]

    def rate_for(self, miles: int) -> Optional[dict]:
        if miles < self._lowers[0]:
            return None                       # below floor -> intrazone minimum
        idx = bisect.bisect_right(self._lowers, miles) - 1
        band = self._bands[idx]
        return band if miles < band["upper_excl"] else None

    def linehaul(self, miles: int) -> Decimal:
        band = self.rate_for(miles)
        if band is None:
            raise ValueError(f"no band covers {miles} miles")
        charge = (band["amount"] if band["basis"] == "FLAT"
                  else band["amount"] * Decimal(miles))
        return charge.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)

Verification

Confirm each failure is closed with assertions that pin the boundary and the mileage type. These belong in the build’s test suite so a re-export with the wrong routing fails CI, not production.

import pytest
from decimal import Decimal


def test_shortest_miles_export_is_rejected():
    bands = [ExportBand(0, 500, "PER_MILE", Decimal("2.6"), "SHORTEST")]
    with pytest.raises(ValueError):
        enforce_mileage_type(bands, "PRACTICAL")


def test_paste_ordered_bands_are_sorted_on_build():
    unsorted = [
        ExportBand(500, 1000, "PER_MILE", Decimal("2.2"), "PRACTICAL"),
        ExportBand(0, 50, "FLAT", Decimal("195"), "PRACTICAL"),
        ExportBand(50, 500, "PER_MILE", Decimal("2.6"), "PRACTICAL"),
    ]
    built = build_half_open(unsorted)
    assert [b["lower_incl"] for b in built] == [0, 50, 500]


def test_boundary_mile_lands_in_upper_band():
    built = build_half_open([
        ExportBand(0, 500, "PER_MILE", Decimal("2.60"), "PRACTICAL"),
        ExportBand(500, 1000, "PER_MILE", Decimal("2.20"), "PRACTICAL"),
    ])
    lookup = BandLookup(built)
    assert lookup.rate_for(500)["amount"] == Decimal("2.20")
    assert lookup.rate_for(499)["amount"] == Decimal("2.60")

In production the proof is telemetry: log the mileage type and band-edge checksum on every build, and alert if either changes without a contract amendment. A shifted checksum on an unchanged contract means someone re-exported with different routing — investigate the export, do not rebuild over it.

Preventive configuration

Encode the guards as configuration so the regression cannot return quietly:

mileage_band_build:
  expected_mileage_type: PRACTICAL     # must match contract terms
  boundary_semantics: half_open        # [lower, upper)
  require_contiguous: true             # fail build on any gap or overlap
  top_band_upper: inf                  # long hauls always resolve
  reject_on_type_mismatch: true        # never silently downgrade routing
  • Type gate in CI. Assert every export row carries the contracted mileage type before the band table is written; a mismatch fails the build.
  • Contiguity assertion at build. Reject any table whose bands do not chain exactly, so a digitization gap surfaces at load rather than as a runtime quarantine.
  • Boundary regression test. Keep an exact-edge fixture for every band so a semantics change fails a test instead of repricing lanes.
  • Shared distance contract. The same practical-miles export feeds both this build and Extracting FTL Zone-Based Pricing from Carrier PDFs, so the routing type is validated once and reused.

FAQ

Practical or shortest miles — which does my contract price on?

Read the contract’s mileage clause; it names the engine and the routing type explicitly, most often PCMILER practical miles for truckload. Never infer it from the export. If the clause is silent, treat that as a contracting gap and confirm with the carrier before building, because the two routings differ enough to shift long hauls a full band.

A move computes to exactly 500 miles — which band gets it?

The upper band. Digitize bands as half-open intervals so [250, 500) excludes 500 and [500, 1000) includes it, then look up with bisect_right minus one. That combination assigns every boundary distance to exactly one band deterministically, so the same lane never flips between runs.

Why does binary search return the wrong band on some lanes?

Binary search assumes a sorted array, and a band table pasted from a tariff PDF is frequently row-shuffled. Sort by lower bound at build time and assert contiguity; an unsorted array makes bisect walk into a neighbouring slot with no error, which is why the build sorts before it indexes.

Should I keep the printed upper bound from the export?

No — discard it and derive the exclusive upper from the next band’s lower. A printed 500 is ambiguous, but the next band’s lower bound of 500 is not, so chaining bounds off the neighbour guarantees contiguity and removes the inclusive-versus-exclusive question entirely.

Up one level: Mileage-Band Rate Lookups · Section: Freight Contract Architecture & Rate Mapping