Before this: Identifiers are text; flags are booleans
The idea
Flags arrive as Y, yes, TRUE, 1, N, no, false, 0 and blank. Normalize (trim, lowercase), then map an approved list to true and false.
Anything else, including blank, is not an answer. Keep it as an exception or as unknown according to the rules; never default it to false.
Store the result as a boolean type that allows missing (SQL BOOLEAN, pandas boolean).
Worked example
Input: opted_in: ' Yes', '0', 'maybe', ''
- Visual
Add steps → Trim, Change case → lowercase, Map values → yes/y/true/1 → true, no/n/false/0 → false, Convert type → bool
- SQL (DuckDB)
CASE lower(trim(opted_in)) WHEN 'yes' THEN true WHEN 'y' THEN true WHEN 'true' THEN true WHEN '1' THEN true WHEN 'no' THEN false WHEN 'n' THEN false WHEN 'false' THEN false WHEN '0' THEN false END
- pandas
df["opted_in"].str.strip().str.lower().map(FLAGS).astype("boolean")
Result: true, false, exception (maybe), exception or unknown (blank)
Common mistakes
- Defaulting blanks to false.
- Unlisted spellings accepted by accident.
- Losing leading zeros on ids in the same table.
Check yourself
Why not treat blank as false?
A missing answer is not a no; treating it as false invents consent decisions.
Practice it
Keep identifiers as text and parse yes/no flags.