Units-in-Cell → Number + Unit¶

What

Split a measurement column that glues a number to its unit — 5.0 kg, 250 g, 1.5 L, 12 pcs — into a clean numeric amount and a separate unit column.

Synthetic / teaching example

The data here is constructed, not sourced — sample.csv is hand-written rows covering a few unit shapes plus a blank. The problem class is real; the rows are not.

Why interesting¶

Quantities routinely ship as "<number> <unit>" in one cell (ingredient lists, lab readings, product specs). You can't SUM weights or convert units while the text kg is fused to the number — every arithmetic op throws. Separating the value from the unit is the prerequisite for any calculation.

Problem class documented in. (sources for the problem class — not for the data)

Tidy-data guidance (e.g. Wickham, Tidy Data) calls a value-plus-unit-in-one-cell a classic "one column, two variables" problem to be split before analysis.

The trick¶

See inline comments in sample.json:

amount: IF(ISEMPTY([Measure]), '', SPLIT_PART([Measure], ' ', 1) * 1)
unit:   SPLIT_PART([Measure], ' ', 2)

SPLIT_PART(…, ' ', 1) takes the number, * 1 makes it a real numeric.
SPLIT_PART(…, ' ', 2) takes the unit token.
ISEMPTY([Measure]) keeps a blank cell empty rather than coercing it to 0. It is a length test on the trimmed string, so — unlike a bare [Measure] = '' — it does not itself swallow a real "0".

Final result¶

One fused column becomes two clean ones:

5.0 kg  →  5    kg
1.5 L   →  1.5  L
12 pcs  →  12   pcs
(blank) →  (empty) (empty)

amount is now summable and unit group-able — ready for a GROUP BY unit or a unit-conversion step.

Sample data¶

Run it with bxp-cli --config ./sample.json --template units_in_cell_split:

sample.json (config)sample.csvsample.csvx (result)

{
  // Teaching example — synthetic data. A measurement column glues the number to
  // its unit in one cell: "5.0 kg", "250 g", "1.5 L". You can't sum or convert
  // that until the number and the unit live in separate columns. SPLIT_PART on
  // the space does both halves.
  conversion_templates: {
    units_in_cell_split: {
      data_dir:          ".",
      file_pattern_in:   ".csv",
      file_pattern_out:  ".csvx",

      input_schema: {
        $product: "[Product]",

        // THE TRICK — split on the space:
        //   SPLIT_PART("5.0 kg", ' ', 1) → "5.0"  (×1 → clean number 5)
        //   SPLIT_PART("5.0 kg", ' ', 2) → "kg"
        // ISEMPTY guards a blank cell so it stays empty instead of 0.
        $amount: "IF(ISEMPTY([Measure]), '', SPLIT_PART([Measure], ' ', 1) * 1)",
        $unit:   "SPLIT_PART([Measure], ' ', 2)"
      },

      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],

      output_schema: {
        product: "$product",
        amount:  "$amount",
        unit:    "$unit"
      }
    }
  }
}

Product,Measure
All-purpose flour,5.0 kg
Caster sugar,250 g
Whole milk,1.5 L
Free-range eggs,12 pcs
Tap water,

product,amount,unit
All-purpose flour,5,kg
Caster sugar,250,g
Whole milk,1.5,L
Free-range eggs,12,pcs
Tap water,,