Normalize Mixed Units — Solution & ExplanationProblems / Normalize Mixed Units / EditorialApproach

Map every row to kilograms with a CASE expression on weight_unit: kg passes through, lb is multiplied by 0.453592, g is divided by 1000.

SUM the converted values per warehouse.

ROUND(..., 2) the total — not the per-row values.

Order by warehouse.

Key InsightUnit normalization must happen before aggregation — summing raw weight_value across mixed units is meaningless. The second trap is rounding order: round-then-sum accumulates per-row rounding error, while sum-then-round keeps full precision until the very end. Boston in this dataset is the proof: its shipments sum to 21.696984 kg, which rounds to 21.70, but rounding each shipment first yields 21.69. Always round as late as possible.Hints

A CASE expression on weight_unit picks the conversion per row: 'kg' stays as-is, 'lb' multiplies by 0.453592, 'g' divides by 1000.

Convert first, SUM second, and round only the final total. Rounding each shipment before summing accumulates rounding error — one warehouse in this dataset gives a different answer if you round too early.

In PySpark, build the converted column with chained F.when(...).when(...).when(...), then groupBy('warehouse') and F.round(F.sum(...), 2).

Solve Normalize Mixed Units yourself →