Cross-Checking Billable Weight Against Actual Weight Logs

This page resolves the three failures that break billable-versus-actual weight reconciliation in production freight audits: carrier weights that will not parse, bulk joins that get OOMKilled against months of scale history, and a static tolerance that floods the queue with false positives while masking real overpayments.

The Failure You Are Hitting

You wrote a step that recomputes each shipment’s billable weight from the contracted dimensional divisor and compares it to the carrier’s billed weight, then cross-checks both against the certified scale log. It passed every fixture. In production it degrades in three observable ways, none of which necessarily raises an exception:

• ValueError: could not convert string to float mid-batch, because one carrier’s EDI 210/214 payload appends a unit suffix (1450 LBS, 658KG, 220#) or trailing whitespace to a field your parser assumed was numeric.
• A MemoryError or a silent OOMKilled pod when a pandas.merge() joins the current invoice batch against 12+ months of unindexed actual_weight logs and the cardinality blows up into a near cross-product.
• The reconciliation runs, but a flat 3% tolerance flags thousands of low-class LTL shipments that are within normal density variance, while a genuine 40 lb reweigh overcharge on a heavy FTL load slips through under the same percentage band.

This stage sits inside Weight & Zone Cross-Validation, the deterministic step within Rule-Based Rate Validation & Accessorial Auditing that recomputes the billable weight every base charge depends on. When this cross-check drops rows, crashes, or mis-flags, every charge check downstream inherits a corrupted baseline.

Root Cause Analysis

These failures are rarely bugs in pandas itself. They trace to four production conditions a single clean fixture never exercises:

1. Unit drift across carriers. The same manifest mixes LB, LBS, KG, KGS, and #. Treating the field as already-numeric throws on the first suffixed value; silently coercing it bills a 658 kg load as 658 lb — a 1,450 lb error.
2. Unbounded join cardinality. Duplicate PRO or BOL numbers on the scale-log side turn a left join into a many-to-many explosion. Row count and memory scale O(N×M) instead of O(N), and the worker dies before it finishes.
3. One tolerance for every freight profile. A hardcoded percentage band ignores that density variance on freight class 50–60 LTL is structurally larger than on a palletized FTL load. The same number is simultaneously too loose for heavy shipments and too tight for light ones.
4. Float contamination at the boundary. A billed_freight_charge or weight that arrives as a JSON float loses precision exactly at the tolerance edge, so a record flips PASS/FAIL non-deterministically between runs.

Use this matrix to map the symptom you see to the layer that produced it before touching any code:

Reproducible Diagnostic

Before changing the join, confirm which failure you have. This snippet quantifies unit drift and join-cardinality blow-up without loading the full history:

import pandas as pd

invoices = pd.read_csv("invoices.csv", dtype={"weight": str}, nrows=50_000)
logs = pd.read_csv("weight_logs.csv", dtype={"pro_number": str})

# 1. Unit drift: any non-numeric characters in the weight field?
dirty = invoices["weight"].str.contains(r"[^\d.\s]", na=False)
print("rows with unit suffix/noise:", int(dirty.sum()))
print(invoices.loc[dirty, "weight"].head(3).tolist())

# 2. Cardinality: is the join key unique on the log side?
dupes = logs["pro_number"].duplicated().sum()
print("duplicate PRO keys in logs:", int(dupes), "-> >0 means the merge can explode")

Read the output as a decision tree: a non-zero suffix count means fix unit normalization first; a non-zero duplicate-key count means the join will inflate and must be de-duplicated and indexed before you scale the input.

Resolution Path

The fix is a four-part reconciliation engine: normalize units to a single base, stream the join with an indexed lookup to bound memory, evaluate a dynamic tolerance, then route the unresolved remainder instead of guessing. Pin dependencies so CI and production agree:

# requirements.txt
pandas==2.2.2
numpy==2.0.1
pydantic==2.10.6

Step 1 — Stream the join and normalize units in one pass

Never materialize a full-history merge. Pre-index the scale log once, then stream the invoice batch in chunks, normalizing KG to lb inline and reclaiming memory between chunks. Indexing the reference log reduces join complexity from O(N×M) to O(N):

import pandas as pd
import numpy as np
import gc
from pathlib import Path
from typing import Generator, Tuple

def chunked_weight_reconciliation(
    invoice_path: Path,
    weight_log_path: Path,
    chunk_size: int = 500_000,
    join_keys: Tuple[str, str] = ("pro_number", "shipment_id"),
) -> Generator[pd.DataFrame, None, None]:
    """Memory-bounded reconciliation of billable vs actual weight logs."""
    # Pre-index logs for O(1) lookups; drop dupes so the join cannot explode.
    weight_index = pd.read_parquet(
        weight_log_path,
        columns=[join_keys[1], "actual_weight_lb", "unit_of_measure"],
    )
    weight_index = (
        weight_index.drop_duplicates(subset=[join_keys[1]]).set_index(join_keys[1])
    )

    for chunk in pd.read_csv(invoice_path, chunksize=chunk_size):
        # Strip unit suffix, then convert KG -> lb so both sides share a base unit.
        chunk["weight"] = chunk["weight"].astype(str).str.extract(r"(\d+\.?\d*)")[0].astype(float)
        chunk["billable_weight_lb"] = np.where(
            chunk["unit"].str.upper().str.startswith("KG"),
            chunk["weight"] * 2.20462,
            chunk["weight"],
        )

        # Join only the column we need, on the indexed key, to keep RSS flat.
        merged = chunk.merge(
            weight_index[["actual_weight_lb"]],
            left_on=join_keys[0],
            right_index=True,
            how="left",
        )

        # Compute discrepancy safely; a missing log is NaN, never a false 0%.
        merged["weight_diff_pct"] = np.where(
            merged["actual_weight_lb"].notna(),
            np.abs(merged["billable_weight_lb"] - merged["actual_weight_lb"])
            / merged["actual_weight_lb"] * 100,
            np.nan,
        )

        yield merged
        del chunk, merged
        gc.collect()

The chunksize stream prevents full-table materialization, the indexed set_index() lookup keeps the join linear, and the explicit gc.collect() reclaims memory between chunks on long-running jobs. The same unit normalization must run wherever weights are first ingested by Automated Invoice Parsing & EDI/XML Ingestion — a value normalized on only one side still mis-compares.

Step 2 — Evaluate a dynamic, freight-class-aware tolerance

A static band is the root of both the false positives and the missed overcharges. Resolve the threshold from freight class, fall back to an absolute pound cap for light loads, and never compare against a non-positive actual weight:

from dataclasses import dataclass, field
from typing import Dict, Optional

@dataclass
class ToleranceConfig:
    default_pct: float = 3.0
    ltl_class_overrides: Dict[int, float] = field(
        default_factory=lambda: {50: 8.0, 55: 7.0, 60: 6.0}
    )
    absolute_cap_lb: float = 50.0
    fallback_action: str = "route_to_manual_audit"

def evaluate_weight_discrepancy(
    billable: float,
    actual: float,
    freight_class: Optional[int],
    config: ToleranceConfig,
) -> Dict[str, object]:
    """Grade a discrepancy against a class-aware tolerance and return a route."""
    if actual <= 0:
        return {"status": "INVALID", "reason": "actual_weight_non_positive",
                "route": config.fallback_action}

    delta = abs(billable - actual)
    pct_diff = (delta / actual) * 100

    threshold_pct = config.ltl_class_overrides.get(freight_class, config.default_pct)

    # Pass within EITHER the percentage OR the absolute cap (or-logic is normal in LTL).
    is_valid = (pct_diff <= threshold_pct) or (delta <= config.absolute_cap_lb)

    if is_valid:
        return {"status": "PASSED", "pct_diff": round(pct_diff, 2), "route": "auto_approve"}
    return {
        "status": "FAILED",
        "pct_diff": round(pct_diff, 2),
        "delta_lb": round(delta, 2),
        "threshold_pct": threshold_pct,
        "route": config.fallback_action,
    }

A FAILED verdict is the cross-check doing its job: it carries forward to Accessorial Charge Scoring, which weighs the variance for dispute, while Threshold Tuning & Alerting reads the pct_diff distribution to decide whether a band is drifting. The cross-check flags; it never adjudicates the dollar amount.

Step 3 — Route the unresolved remainder with an audit trail

When a record has no matching log, or the tolerance fails, it must be routed and recorded, not silently defaulted. Emit a tamper-evident record so any dispute is replayable from the hash:

import logging, hashlib, json
from datetime import datetime, timezone
from logging.handlers import RotatingFileHandler

audit_logger = logging.getLogger("freight_weight_audit")
audit_logger.setLevel(logging.INFO)
handler = RotatingFileHandler("logs/weight_audit.log", maxBytes=50_000_000, backupCount=5)
handler.setFormatter(logging.Formatter("%(message)s"))
audit_logger.addHandler(handler)

def log_reconciliation_event(event_type: str, payload: dict, status: str, route: str) -> None:
    """Emit an immutable, SHA-256-hashed reconciliation record."""
    payload_json = json.dumps(payload, sort_keys=True, default=str).encode("utf-8")
    record = {
        "timestamp": datetime.now(timezone.utc).isoformat(),
        "event_type": event_type,
        "status": status,
        "route": route,
        "payload_hash": hashlib.sha256(payload_json).hexdigest(),
        "metadata": payload,
    }
    audit_logger.info(json.dumps(record, separators=(",", ":")))

The fallback chain runs in order: dynamic tolerance, then a 90-day lane baseline, then carrier-specific exceptions (known scale-calibration offsets), and finally the manual-audit queue with the payload hash for traceability. The structured-JSON design follows the Python logging documentation and ingests directly into Splunk, Datadog, or ELK.

Verification

Confirm each failure is closed rather than hidden. These assertions belong in the integration suite that runs on every new carrier feed:

def test_kg_is_normalized_to_lb():
    cfg = ToleranceConfig()
    # 300 kg billed == 661.4 lb; an actual log of 660 lb must PASS, not flag a 1.4 lb gap.
    out = evaluate_weight_discrepancy(300 * 2.20462, 660.0, 70, cfg)
    assert out["status"] == "PASSED"

def test_non_positive_actual_is_invalid():
    out = evaluate_weight_discrepancy(500.0, 0.0, 50, ToleranceConfig())
    assert out["status"] == "INVALID"

def test_class_band_passes_dense_ltl_but_flags_overcharge():
    cfg = ToleranceConfig()
    assert evaluate_weight_discrepancy(108.0, 100.0, 50, cfg)["status"] == "PASSED"   # 8% allowed
    assert evaluate_weight_discrepancy(140.0, 100.0, 50, cfg)["status"] == "FAILED"   # real overcharge

In production the proof is in telemetry: a healthy run emits a flat memory profile, a stable status_distribution, and zero could not convert exceptions. A sudden spike in INVALID or FAILED means a carrier changed its weight format or a divisor was renegotiated — investigate the feed, do not loosen the band.

Preventive Configuration

Encode the guards as configuration, not tribal knowledge, so the regression cannot return:

weight_reconciliation:
  base_unit: lb                      # everything normalizes to pounds at ingestion
  chunk_size: 500_000                # streaming join; never full materialization
  dedupe_log_key: shipment_id        # collapse duplicate PRO/BOL before the join
  default_tolerance_pct: 3.0
  ltl_class_overrides: {50: 8.0, 55: 7.0, 60: 6.0}
  absolute_cap_lb: 50.0
  on_missing_log: route_to_manual_audit   # never default to a PASS
  memory_limit_mb: 4096
  audit_retention_months: 24         # FMCSA / dispute-defense minimum

• Schema enforcement at ingestion. Validate incoming EDI/CSV with pydantic and reject rows with malformed weight units or missing keys before they reach the join.
• Cardinality gate in CI. Assert merged.height <= invoices.height * 1.05 on a sample; a blow-up means the log key is not unique and de-duplication is missing.
• Memory budget gate. Run the streaming join on a sample under ulimit -v and fail the build if peak RSS exceeds 80% of the worker allocation.
• Golden-dataset drift test. Keep a fixture of known discrepancies and run it nightly so a tolerance change cannot silently raise the false-positive rate.
• Decimal at the money boundary. Coerce billed_freight_charge with Decimal(str(...)) and reject raw JSON floats so a record never flips verdict at the tolerance edge.

FAQ

Why does my reconciliation throw "could not convert string to float" only on some carriers?

Those carriers append a unit token or whitespace to the weight field (1450 LBS, 658KG, 220#). Extract the numeric portion with str.extract(r"(\d+\.?\d*)") and convert KG to lb before any arithmetic, as in Step 1. Normalizing on both the invoice and the log side is what makes the comparison valid.

How do I stop the merge against a year of weight logs from being OOMKilled?

Do not materialize the full history. Pre-index the log on its join key, drop duplicate PRO/BOL rows so the join stays O(N), then stream the invoice batch with read_csv(chunksize=...) and call gc.collect() between chunks. A duplicate key on the log side is the usual cause of the many-to-many explosion.

Why does a flat 3% tolerance flag so many LTL shipments?

Low-class LTL freight (class 50–60) has structurally higher density variance than palletized FTL, so a single percentage band is too tight for it and too loose for heavy loads. Resolve the threshold from freight class and add an absolute pound cap for light shipments (Step 2) so dense LTL passes while a real reweigh overcharge still fails.

What should happen when a shipment has no matching scale log?

Tag it INVALID/route it to manual audit with the payload hash — never default a missing log to a PASS. A missing match is signal: it points to a feed gap or a key-normalization bug, and silently approving it is exactly how an audit under-recovers without anyone noticing.

Related

• Weight & Zone Cross-Validation — the parent stage that recomputes billable weight and zone before any payment is approved.
• Matching Shipment Lanes to Contracted Rate Tables Using Python — the sibling matcher whose resolved lane key selects the rate this cross-check reconciles against.
• Accessorial Charge Scoring — where FAILED weight variances are weighed for dispute.
• Threshold Tuning & Alerting — turns the pct_diff distribution into alert thresholds and tolerance adjustments.
• LTL Rate Sheet Digitization — the class-based rate store whose weight brackets this check must stay in sync with.

Up one level: Weight & Zone Cross-Validation · Section: Rule-Based Rate Validation & Accessorial Auditing