Boolean Variants → Canonical true/false¶
What
Fold boolean columns written every which way — Yes/No, Y/N,
1/0, true/false, TRUE/T/F, mixed case — into a canonical
true/false, with blanks and unrecognised junk left empty.
Synthetic / teaching example
The data here is constructed, not sourced
— sample.csv plants one of each boolean spelling plus a blank and a junk
value. The problem class is real and universal; the rows are not.
Why interesting¶
"Boolean" is the least standardised column type in
practice: every system invents its own truthy/falsy spelling, and a join or
filter on the raw text silently splits Yes from Y from true. Normalising
to one representation is routine — but doing it correctly runs into two numeric
coercion traps worth knowing.
flowchart TD
X["raw cell"] --> G{"LEN(TRIM) = 0?"}
G -->|yes| E["(empty)<br/><small>blank stays blank</small>"]
G -->|no| L["LOWER(TRIM)"]
L --> T{"IN truthy?<br/><small>yes·y·1·true·t</small>"}
T -->|yes| TR["true"]
T -->|no| F{"IN falsy?<br/><small>no·n·0·false·f</small>"}
F -->|yes| FA["false"]
F -->|no| U["(empty)<br/><small>junk dropped</small>"]Problem class documented in. (sources for the problem class — not for the data)
- pandas truthy/falsy parsing
(
true_values/false_values) exists because CSVs encode booleans asY/N,1/0,T/F,yes/no, …
The trick¶
(see inline comments in sample.json)
LOWER(TRIM(...)) then IN(...) against the truthy / falsy spellings. The two
traps, both solved by guarding blanks with ISEMPTY([Active]) first:
- Blank vs
'0'. bxp coerces an empty cell to0and the literal'0'to0, so a blank would wrongly match the'0'in the falsy list and becomefalse. Guarding blanks up front keeps them empty. - The guard itself. A bare
TRIM([Active]) = ''also coerces ("0"→0 ==""→0), so it would wrongly treat a real"0"as blank.ISEMPTYcompares the trimmed length — no coercion — so"0"(length 1) survives to be read asfalse. Compare the two on show all: the0row is where they disagree.
Final result¶
Ten spellings collapse to three states; the 0 is a real
false, the blank stays blank, and the junk unknown is dropped to empty:
Yes / TRUE → true / true
N / 0 → false / false
1 / false → true / false
(blank) / unknown → (empty) / (empty)
Now a WHERE active = 'true' filter catches every truthy spelling at once.
Sample data¶
Run it with bxp-cli --config ./sample.json --template boolean_variants_clean:
{
// Teaching example — synthetic data. Boolean columns arrive in every spelling
// imaginable: Yes/No, Y/N, 1/0, true/false, TRUE/T/F, mixed case, plus blanks
// and junk. This template folds them to a canonical "true"/"false" (blank and
// unrecognised → ""), and shows the two numeric-coercion traps that make a
// naive version wrong.
conversion_templates: {
boolean_variants_clean: {
data_dir: ".",
file_pattern_in: ".csv",
file_pattern_out: ".csvx",
input_schema: {
$account: "[Account]",
// THE TRICK — LOWER+TRIM, then IN() against the truthy / falsy spellings.
//
// Two coercion traps, both fixed by the ISEMPTY() guard:
// * empty cell vs the '0' in the falsy list — bxp coerces ""→0 and
// '0'→0, so a blank would WRONGLY match '0' and become "false";
// * a bare [x] = '' guard would ALSO coerce "0"→0 == ""→0 and wrongly
// treat a real "0" as blank. ISEMPTY([x]) tests the trimmed length
// (no coercion): blank / whitespace → true, "0" → false.
// So: guard blanks with ISEMPTY first, THEN it's safe to keep '0' as falsy.
$active: "IF(ISEMPTY([Active]), '', IF(IN(LOWER(TRIM([Active])), 'yes', 'y', '1', 'true', 't'), 'true', IF(IN(LOWER(TRIM([Active])), 'no', 'n', '0', 'false', 'f'), 'false', '')))",
$verified: "IF(ISEMPTY([Verified]), '', IF(IN(LOWER(TRIM([Verified])), 'yes', 'y', '1', 'true', 't'), 'true', IF(IN(LOWER(TRIM([Verified])), 'no', 'n', '0', 'false', 'f'), 'false', '')))"
},
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: {
account: "$account",
active: "$active",
verified: "$verified"
}
}
}
}