Null Variants → Empty¶

What

Fold every "no value" spelling — NULL, NA, N/A, n/a, None, "-" — into a single genuine empty cell, while leaving real values untouched.

Synthetic / teaching example

The data here is constructed, not sourced — sample.csv is hand-written rows planting the common null markers across a few columns. The problem class is real and universal; the rows are not.

Why interesting¶

When data passes through several systems, "missing" gets written a dozen incompatible ways. Imported verbatim, the literal text "N/A" is not empty: it inflates COUNTs, breaks joins on the column, sorts as a real value, and clutters dropdowns. Normalising all of them to one empty cell up front is the first step of almost every cleanup.

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

pandas na_values exists precisely because CSVs encode missing values as NA, N/A, null, None, -, … and each must be mapped to a true blank.

The trick¶

See inline comments in sample.json:

IF(IN(TRIM([Email]), 'NULL', 'NA', 'N/A', 'n/a', 'None', 'none', '-'), '', TRIM([Email]))

IN(value, …) is a function in bxp — IN(val, a, b, …) — testing the value against a list of markers (not SQL value IN (…) infix).
TRIM first, so " NA " with stray spaces still matches.
Match → ''; otherwise keep the trimmed value. The same guard drops onto every dirty column.

Final result¶

Bob's N/A email and NULL note, Carol's - phone and None note, Dave's NA/n/a all become truly empty — while follow up next week and the real emails survive:

Bob,,555-9999,
Carol,carol@example.com,,
Dave,,,follow up next week

Now an email IS NULL filter or a COUNT(phone) means what it says.

Sample data¶

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

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

{
  // Teaching example — synthetic data. Different systems write "no value" a
  // dozen different ways: NULL, NA, N/A, n/a, None, "-", empty. Imported as-is,
  // the literal text "N/A" pollutes counts, breaks joins, and shows up in
  // dropdowns. This template folds every null-variant to a single genuine
  // empty cell, leaving real values untouched.
  conversion_templates: {
    null_variants_clean: {
      data_dir:          ".",
      file_pattern_in:   ".csv",
      file_pattern_out:  ".csvx",

      input_schema: {
        $name: "[Name]",

        // THE TRICK — IN(value, ...) tests membership against a list of null
        // markers (IN is a *function* here: IN(val, a, b, …), not SQL infix).
        // TRIM first so " NA " also matches; if it's a marker → "", else keep
        // the trimmed value. Apply the same guard to every dirty column.
        $email: "IF(IN(TRIM([Email]), 'NULL', 'NA', 'N/A', 'n/a', 'None', 'none', '-'), '', TRIM([Email]))",
        $phone: "IF(IN(TRIM([Phone]), 'NULL', 'NA', 'N/A', 'n/a', 'None', 'none', '-'), '', TRIM([Phone]))",
        $notes: "IF(IN(TRIM([Notes]), 'NULL', 'NA', 'N/A', 'n/a', 'None', 'none', '-'), '', TRIM([Notes]))"
      },

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

      output_schema: {
        name:  "$name",
        email: "$email",
        phone: "$phone",
        notes: "$notes"
      }
    }
  }
}

Name,Email,Phone,Notes
Alice,alice@example.com,555-1234,VIP customer
Bob,N/A,555-9999,NULL
Carol,carol@example.com,-,None
Dave,NA,n/a,follow up next week
Eve,,555-0000,NA

name,email,phone,notes
Alice,alice@example.com,555-1234,VIP customer
Bob,,555-9999,
Carol,carol@example.com,,
Dave,,,follow up next week
Eve,,555-0000,