Core Architecture Cost Mapping Systems

Building a Unit-Conversion Matrix in Python

This page walks a food-tech developer through a runnable, vectorized unit-conversion matrix that turns a DataFrame of mixed-unit quantities into canonical base-unit quantities. It is the implementation companion to unit conversion and canonicalization; read that for the dimension model and the quarantine contract, then follow the numbered steps here to stand up code you can run against today’s ingest.

The build has one non-obvious requirement: it must convert tens of thousands of lines nightly without a Python row loop, yet still refuse — row by row — to bridge a volume to a mass without a density. The trick is to do the refusal as a vectorized mask, not an exception.

Prerequisites and Data Contract

  • Python 3.11+, pandas 2.x.
  • An input frame with columns line_id, raw_quantity (numeric), raw_unit (a clean token), and ingredient_id.
  • A densities table mapping ingredient_id to grams-per-milliliter, as reviewed data.

Output is the same frame plus canonical_quantity, canonical_unit, and unit_status (CONVERTED or QUARANTINE). Money is absent here, but quantities stay Decimal-exact so later cost multiplication does not inherit float drift.

Vectorized unit-conversion matrix The input quantity frame joins a unit registry to attach a dimension and a base-unit factor, then left-joins the densities table for volume rows. A single vectorized computation produces the canonical quantity. A boolean mask marks rows with an unknown unit or a missing density as quarantine while the rest are marked converted. Input frame raw qty + unit Join registry dim + factor Join density volume rows Compute + mask canonical / quarantine

Step-by-Step Implementation

Step 1 — Define the unit registry as a frame

Express the registry as a DataFrame so it joins to the data instead of being looked up per row.

from decimal import Decimal

import pandas as pd

unit_registry = pd.DataFrame(
    [
        ("g",   "mass",   Decimal("1")),
        ("kg",  "mass",   Decimal("1000")),
        ("oz",  "mass",   Decimal("28.349523125")),
        ("lb",  "mass",   Decimal("453.59237")),
        ("ml",  "volume", Decimal("1")),
        ("l",   "volume", Decimal("1000")),
        ("each", "count", Decimal("1")),
    ],
    columns=["raw_unit", "dimension", "to_base"],
)

Step 2 — Join registry and densities

A left join on the registry surfaces unknown units as NaN dimension; a left join on densities attaches a density only where one exists.

import pandas as pd


def attach_factors(df: pd.DataFrame, registry: pd.DataFrame,
                   densities: pd.DataFrame) -> pd.DataFrame:
    merged = df.merge(registry, on="raw_unit", how="left")
    merged = merged.merge(densities, on="ingredient_id", how="left")  # adds g_per_ml
    return merged

Step 3 — Convert in one vectorized pass with Decimal

Compute the base quantity, then multiply volume rows by density. Keep the arithmetic in Decimal by mapping, not by using float columns.

from decimal import Decimal

import pandas as pd


def convert(merged: pd.DataFrame) -> pd.DataFrame:
    merged = merged.copy()
    merged["base_qty"] = merged.apply(
        lambda r: Decimal(str(r["raw_quantity"])) * r["to_base"]
        if pd.notna(r["to_base"]) else None,
        axis=1,
    )
    is_volume = merged["dimension"] == "volume"
    merged["canonical_quantity"] = merged.apply(
        lambda r: (r["base_qty"] * Decimal(str(r["g_per_ml"])))
        if (is_volume.loc[r.name] and pd.notna(r["g_per_ml"]) and r["base_qty"] is not None)
        else r["base_qty"],
        axis=1,
    )
    merged["canonical_unit"] = merged["dimension"].map(
        {"mass": "g", "volume": "g", "count": "each"}
    )
    return merged

Step 4 — Mask unconvertible rows to quarantine

A row is quarantined when its unit is unknown (no dimension) or it is a volume with no density. This is the vectorized refusal.

import pandas as pd


def apply_status(merged: pd.DataFrame) -> pd.DataFrame:
    unknown_unit = merged["dimension"].isna()
    volume_no_density = (merged["dimension"] == "volume") & merged["g_per_ml"].isna()
    quarantine = unknown_unit | volume_no_density

    merged["unit_status"] = "CONVERTED"
    merged.loc[quarantine, "unit_status"] = "QUARANTINE"
    merged.loc[quarantine, ["canonical_quantity", "canonical_unit"]] = None
    return merged[["line_id", "canonical_quantity", "canonical_unit", "unit_status"]]

Verification and Validation

  • Round-trip a known unit. Convert 1 lb and assert canonical_quantity == Decimal("453.59237") exactly — a float factor would produce 453.5923700000... with drift.
  • Density bridge. Convert 1000 ml of an ingredient with density 0.915 (olive oil) and confirm 915 grams, not 1000.
  • Quarantine fires. Feed a row with raw_unit="dram" (unknown) and a volume row with no density; both must show unit_status == "QUARANTINE" and null canonical columns, and the batch must complete.
  • Idempotency. Run the pipeline twice on the same input; the output frame must be identical.

Gotchas and Edge Cases

  • Float sneaking in via the density column. Databases return density as float; wrap it in Decimal(str(...)) before multiplying, or the exact round-trip test fails.
  • each divided into a weight. A count row has no mass. Never let a downstream step divide a per-gram cost into an each quantity; keep the count dimension distinct and require an explicit piece-weight to bridge.
  • NaN propagation. An unknown unit yields a NaN dimension; if you compute before masking, NaN can quietly become part of a sum. Mask first, aggregate only CONVERTED rows.
  • Ambiguous oz. Weight ounces and fluid ounces share a token in raw data. Disambiguate upstream in alias resolution so the registry only ever sees a clean, dimension-specific token.

For library specifics, see the official pandas documentation and Python decimal documentation.