Before this: Trim the right characters, and only those
The idea
Free-text categories drift: Email, e-mail, "email " and E-Mail all mean one channel. Reports need one value per meaning, so you map spellings to a controlled vocabulary.
The mapping should be data, not ad-hoc code: a dictionary of approved left → right pairs that someone reviewed. Include identity pairs (email → email) so every accepted value is listed in one place.
Normalize before mapping (trim, case) so the dictionary stays short. Anything still unmapped is quarantined with a reason. Guessing that "web-chat" means chat may be right today and wrong tomorrow, and nobody can review a guess.
Worked example
Input: channel: "E-mail ", "Web Chat", "fax", missing
- Visual
Trim → Change case → Blank to missing → Require a value (missing_channel) → Map values with the dictionary, unmapped: quarantine (unmapped_channel)
- SQL (DuckDB)
JOIN (VALUES ('e-mail','email'), ('web chat','chat'), ...) AS dict(raw, canon) ON NULLIF(lower(trim(channel, chars)), '') = dict.raw- pandas
channel.map(lambda v: DICTIONARY.get(v) if isinstance(v, str) else None)
Result: "E-mail " → email, "Web Chat" → chat, "fax" → unmapped_channel, missing → missing_channel.
Common mistakes
- Mapping before normalizing case and spacing.
- Keeping unmapped values instead of quarantining them.
- Burying the mapping in nested IF statements nobody can review.
Check yourself
Why include email → email in the dictionary?
So the dictionary lists every accepted value; anything not listed is unmapped by definition.
pandas: what's wrong with DICTIONARY.get(v, v)?
It keeps unmapped values unchanged, silently accepting spellings nobody approved.
Practice it
Map every ticket channel with the approved dictionary and quarantine the rest with the right reason.