Before this: Import everything as text first
The idea
An identifier is a label. You never add postcodes or average member IDs, so they should not be numbers: converting deletes leading zeros, and very long IDs can lose digits in floating point.
Flags arrive as Y, yes, TRUE, 1 or "No ". Normalize them (trim, lowercase), map each accepted spelling to true or false, and only then convert to a boolean type. A value outside the list is an exception, not a guess.
A missing flag is not false. If someone never answered a consent question, recording "no" asserts a decision that wasn't made. Quarantine it or keep it missing, as the rules say.
Worked example
Input: opted_in: " Yes ", "0", "maybe", missing
- Visual
Trim → Change case (lowercase) → Require a value (bad_flag) → Map values (yes => true, 0 => false, …, unmapped: quarantine) → Convert type to bool
- SQL (DuckDB)
CASE WHEN lower(trim(opted_in, chars)) IN ('y','yes','true','1') THEN true WHEN lower(trim(opted_in, chars)) IN ('n','no','false','0') THEN false END- pandas
flag = df["opted_in"].str.strip(" \t\r\n").str.lower() flag.isin({"y", "yes", "true", "1"})
Result: " Yes " → true, "0" → false, "maybe" and missing → exceptions with bad_flag.
Common mistakes
- Casting identifiers to numbers.
- Treating missing flags as false.
- Accepting any truthy-looking text without a declared list.
Check yourself
Is CAST(postcode AS INTEGER) a harmless way to remove padding?
No. It deletes leading zeros and changes the identifier; trim the text instead.
A row has no opted_in value. Is it false?
No. Missing is not a decision; the rules send it to exceptions.
Practice it
Keep postcodes as text and parse opted_in to a boolean using only the declared spellings.