Before you start
You will be able to
- Identify the whitespace, case, punctuation and Unicode differences that make equal-looking text values unequal.
- Normalise text values by trimming, case normalisation and Unicode normalisation, in a sensible order.
- Build and maintain a mapping table that consolidates variants into a controlled vocabulary.
- Use simple regular expressions to check, extract and split text, and explain their limits.
- Calculate how category consolidation changes group counts and averages.
Review first
- Incorrect data types and inconsistent dates — You need data types, locales and the habit of converting with an explicit rule and then validating.
- Duplicate records — Near duplicates and edit distance reappear here, applied to category values.
- Datasets: rows, columns, records and variables — Nominal variables and the data dictionary define what a category column should contain.
Fictional case A fictional coffee business founded by Ana Lim. It starts as a catering and barista-training service, opens its own cafés and an online shop for beans, and plans a canned cold brew.
The idea
Fictional case
Lantern’s marketing team wants to split next quarter’s promotion budget across Lantern’s five delivery regions, North, South, East, West and Central, in proportion to orders. Lantern’s analyst, Jun Park, counts orders by region in the online-shop export and gets more than five groups. Customers type their region in the app, staff key some orders by hand, and the export has kept every version: capitals and lower case, stray spaces, abbreviations such as “N.” and typing errors such as “Nrth”. The product names have the same problem.
People read “North”, “north ” and “N.” as the same region. Software compares text exactly, character by character, so it sees three different values. Every operation that relies on equal values is affected: counting and grouping split one category into several, filters miss rows, and joins to another table fail. This lesson turns the many ways of writing a value into one agreed value, and keeps a record of how.
Whitespace you can’t see
Whitespace is any character that shows as blank space: the ordinary space, the tab, line breaks and several special spaces. It causes trouble in three places. Leading and trailing spaces (“North␣”) are invisible on screen but make the value unequal to “North”. Doubled spaces inside a value (“House␣␣Blend”) make two product names differ. And special spaces look identical to an ordinary space but are different characters.
The most common special space is the non-breaking space (Unicode U+00A0), which word processors and web pages use to keep two words on the same line. It arrives when text is copied from a web page or a document, and several locales use spaces like it as thousands separators. Because it isn’t the ordinary space, some cleaning functions leave it alone. Microsoft’s documentation says Excel’s TRIM removes only the ordinary 7-bit ASCII space (character 32) and does not, by itself, remove the non-breaking space (character 160). In this lesson’s tables, ␣ marks an ordinary space and ⍽ a non-breaking one, so that you can see them.
A quick way to find invisible characters is to compare lengths: “North” has 5 characters, and if a value that looks like “North” has 6, something is hidden in it.
Case and punctuation
Case normalisation converts text to a single case, so that “NORTH”, “north” and “North” compare equal. For matching, convert everything to lower case (or, in Python, use casefold(), which goes further: its documentation notes that the German “ß” becomes “ss”, which lower() leaves unchanged). For display, keep a properly capitalised version: the controlled value “North” rather than “north”. Punctuation needs a decision of its own: “N.” and “N” are the same abbreviation, but removing every full stop would also change values in which a full stop matters, such as decimal numbers inside product descriptions.
Characters that look the same but aren’t
Computers store text as a sequence of code points: the numbers that the Unicode standard assigns to characters, written like U+0065 for “e”. Some characters can be stored in more than one way. The letter “é” can be one code point, U+00E9, or two: “e” followed by a combining acute accent, U+0301. Both display as “é”, but a program comparing code points sees “Café” (4 code points) and “Café” (5 code points) as different strings. Which form you get depends on the keyboard, the operating system and the software that produced the text.
Unicode defines rules that put equivalent text into one standard form. Unicode normalisation converts text to one of the forms set out in Unicode Standard Annex #15. NFC composes characters wherever possible, so “e” plus the combining accent becomes the single code point U+00E9; NFD decomposes them. Normalising every value to NFC before comparing makes the two “Café”s equal.
Variants, typos and one agreed list
After whitespace, case and Unicode are dealt with, what remains are genuine variants: abbreviations (“N.”), typing errors (“Nrth”, “Wset”) and alternative names (“Centre” for “Central”). These need a person’s judgement once, and then a rule that applies it every time. The rule lives in a mapping table: a two-column table (with notes) that lists each variant and the value it should become. The values it maps to come from a controlled vocabulary: the agreed list of allowed values for the column, usually set in the data dictionary. For Lantern’s regions the vocabulary is the five names North, South, East, West and Central.
Mapping many variants onto the values of a controlled vocabulary is category consolidation. It looks like tidying labels, but it changes numbers: every count, share and average by category is calculated from different rows after consolidation, and rankings can reverse. The worked examples measure how much.
In depth
An order of operations
The order matters, because each step makes the next one simpler and safer.
- Keep the raw value. Write cleaned values to a new column, so every change can be traced and undone.
- Normalise Unicode (NFC). Do it first, so later steps compare like with like.
- Fix whitespace. Replace non-breaking and other special spaces with ordinary ones, remove leading and trailing spaces, and collapse runs of spaces inside the value to one.
- Normalise case for the matching key, usually lower case.
- Standardise punctuation where it carries no meaning, for example removing the full stop in “N.”.
- Map variants to the controlled vocabulary with the mapping table.
- Validate. Every cleaned value should be in the vocabulary. Count and list the ones that aren’t, and flag them rather than forcing them into a category.
Doing it in this order keeps the mapping table short. Map before trimming and lower-casing, and the table needs separate rows for “North”, “north”, “North␣”, “NORTH” and every other combination; miss one and it slips through. After steps 2 to 5, all of those are already “north”, and the table needs only the genuine variants.
The tools differ in what “trim” means, so check yours. Excel’s TRIM removes leading and trailing ordinary spaces and also collapses doubled spaces between words to one, but not non-breaking spaces; the usual fix is to replace character 160 with an ordinary space first, as in =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). PostgreSQL’s trim removes only the space character by default. Python’s str.strip() and pandas’s str.strip() remove whitespace of any kind, including non-breaking spaces and newlines, from both ends, but leave doubled spaces inside the value.
Unicode normalisation in a little more depth
Unicode Standard Annex #15 defines four normalisation forms. Two rest on canonical equivalence, which the annex describes as a fundamental equivalence between characters or sequences of characters: sequences that represent the same abstract character and should look and behave the same. NFD (canonical decomposition) splits characters into base letters and combining marks; NFC (canonical decomposition followed by canonical composition) puts them back together wherever a single code point exists. NFC is the usual choice for storing and comparing text, and it is the default of PostgreSQL’s normalize function.
The other two forms, NFKD and NFKC, also apply compatibility mappings, which fold characters that differ only in presentation: the ligature “fi” becomes the two letters “fi”, full-width letters such as “Ａ” become ordinary ones, and a non-breaking space becomes an ordinary space. That is useful for building a matching key and risky for stored text, because it throws information away. Neither kind of normalisation removes accents: “Café” and “Cafe” stay different. Stripping accents is a separate, deliberate decision, and a lossy one, since it can merge different words and misspell names.
Mapping tables and controlled vocabularies
Keep the mapping as data, not as a string of find-and-replace operations or a formula buried in one cell. A small table with one row per variant can be read, reviewed and changed by people who don’t write code.
| raw_value (after steps 2–5) | clean_value | note | added |
|---|---|---|---|
| north | North | Spelling in the vocabulary | v1, 2026-04-02 |
| n | North | Abbreviation (from “N.”) | v1, 2026-04-02 |
| nrth | North | Typing error, confirmed by delivery addresses | v1, 2026-04-02 |
| wset | West | Typing error | v1, 2026-04-02 |
| centre | Central | Alternative name, confirmed by the operations team | v2, 2026-05-03 |
Three habits make a mapping table trustworthy. Review: someone who knows the business approves each new row, because only they can say whether “Northern” is a spelling of North or a different delivery zone. Version: the table changes as new variants appear in each month’s export, so date or number each version and record which version cleaned which file. Flag, don’t force: a value with no row in the table is reported, not pushed into the nearest category or an “Other” bucket, where it would quietly distort the counts.
Fuzzy matching helps build the table but shouldn’t replace it. An edit distance of 1 between “Nrth” and “North” is a good reason to suggest a mapping; a person then confirms it. Some vocabularies are set by outside standards rather than by the business. ISO 3166, for example, defines internationally recognised codes for countries, so “GB” is the two-letter ISO code for the United Kingdom. Harborview Hotel’s data dictionary instead uses full English country names, so its analysts map codes such as GB and US, abbreviations such as USA and alternative names to the names in that list.
Splitting and combining fields
A column sometimes holds several variables at once. Wickham (2014) lists “multiple variables are stored in one column” among the five most common problems with messy datasets, and tidy data gives each variable its own column. Lantern’s product names include the pack size (“Decaf Colombia 1kg”), and its export also has a separate size_g column. Extracting the size from the name and comparing it with size_g is a cross-field check that catches rows where the two disagree.
People’s names are the classic trap. Splitting “Ana Lim” at the space gives a given name and a family name, but the same rule fails for many real names: in many East Asian naming conventions the family name comes first (Lim Wei Ling’s family name is Lim), family names can contain spaces (“de la Cruz”), and some people use a single name. Split names only when an analysis needs the parts, keep the full name as it was given, and never treat the split as more reliable than the original. Combining goes the other way, and is usually safer: a matching key such as the trimmed, lower-cased email address, or a single timestamp built from separate date and time columns.
Regular expressions
A regular expression (regex) is a pattern that describes a set of strings. Spreadsheets, SQL databases and pandas all support them for finding, checking, extracting and replacing text. A handful of pieces covers most cleaning work.
| Piece | Matches | Example |
|---|---|---|
\d | Any digit | \d\d matches “25” |
\s | Any whitespace character | a\sb matches “a b” |
+ | One or more of the previous piece | \d+ matches “250” or “1” |
? | Zero or one of the previous piece (optional) | \s? matches a space or nothing |
{5} | Exactly five of the previous piece | \d{5} matches “02134” |
^ and $ | The start and the end of the value | ^\d{5}$ must be five digits and nothing else |
[^@] | Any character except the ones listed | [^@\s]+ is a run with no @ and no space |
( ) and | | A group to extract; alternatives | (g|kg) matches “g” or “kg” |
Three patterns show what they are good for. Replacing \s+ with a single space collapses every run of whitespace. The pattern ^\d{5}$ is a format check: it accepts “02134” and rejects “2134”, “02134-1234” and “␣02134”. And (\d+)\s?(g|kg) extracts a pack size from a product name, returning “250” and “g” from “House Blend 250g” and “1” and “kg” from “Decaf Colombia 1kg”. A first attempt such as (\d+)\s?g would have missed the kilogram packs entirely, and “250G” in capitals would defeat both unless the match ignores case. Test every pattern on the real values, including the odd ones, before trusting it.
Patterns describe the shape of text, not its meaning. Email addresses show the limit. A pattern such as ^[^@\s]+@[^@\s]+\.[^@\s]+$ (something, an @, something, a dot, something) catches obvious errors such as a missing @ or a space, but it accepts “maya@example..com”, which no mail server would deliver to, and rejects some addresses that the formal email syntax allows. The HTML Standard, which defines how browsers check email fields, deliberately departs from that formal syntax (RFC 5322), calling it too strict before the @, too vague after it and too lax in other ways to be practical. A pattern can tell you that a value looks like an email address; only a message that arrives proves it is one.
How consolidation changes the numbers
When variants are merged, each consolidated category’s count is the sum of its variants’ counts, and its mean is the weighted average of the variants’ means (formula below). Before consolidation, an analysis that filters on the exact value “North” sees only the rows spelled that way. If the variants were a random sample of North’s orders, North’s average would merely be estimated from fewer rows. They rarely are: variants come from particular sources (staff keying phone orders, one version of the app, one marketplace), and those sources differ in what customers order. The correctly spelled rows are then a biased subset, and both counts and averages, and the rankings built on them, can change after consolidation.
Keeping the raw values
Never overwrite the original text. Keep region_raw and region side by side, keep the mapping table and its versions, and keep the code or recipe that applies them. Then anyone can see what each value was, why it changed and which version of the rules changed it, and a mistake in the mapping can be corrected by fixing one row and rerunning, rather than by hunting for edits made by hand. In pandas the whole recipe can be one readable chain: s.str.normalize('NFC').str.replace(r'\s+', ' ', regex=True).str.strip().str.casefold().map(mapping), where the replacement turns every run of whitespace, non-breaking spaces included, into one ordinary space, and map turns any value missing from the mapping into a missing value that you can count and flag.
Formulas
Mean of a consolidated category
- Each variant that maps to the category (for example “North”, “north”, “N.”)
- Number of rows with variant vrows
- Mean of the variable among rows with variant vunits of the variable
A weighted mean: variants with more rows count for more. The simple average of the variant means is wrong unless every variant has the same number of rows.
Vocabulary coverage
- Share of rows the cleaning rules have placed in an allowed categoryproportion (0–1)
Report it after every run: a drop means new variants have appeared and the mapping table needs review.
Worked examples
Fictional figures
Twelve region values from Lantern’s export (␣ is an ordinary space, ⍽ a non-breaking space). How many distinct values are there after each cleaning step, and how many orders does each region really have?
| Row | Raw region | Row | Raw region |
|---|---|---|---|
| 1 | North | 7 | N. |
| 2 | north | 8 | Nrth |
| 3 | North␣ | 9 | Central |
| 4 | ␣South | 10 | central⍽ |
| 5 | SOUTH | 11 | West |
| 6 | South | 12 | Wset |
- Count the raw values: all are different strings. A filter for exactly “North” finds only row 1.
- Trim whitespace, including the non-breaking space in row 10. “North␣” becomes “North” and “␣South” becomes “South”, leaving distinct values. (Excel’s TRIM alone would leave row 10 unchanged.)
- Lower-case everything for matching: “North” and “north” merge, as do “SOUTH” and “South”, and “Central” and “central”. That leaves distinct values: north, south, n., nrth, central, west, wset.
- Map the variants with the reviewed mapping table: n. and nrth to North, wset to West, and each remaining value to its capitalised form. That leaves distinct values, all in the vocabulary.
- Count by region: North has rows 1, 2, 3, 7 and 8, so ; South ; Central ; West . Check: , so no row was lost or double-counted.
Answer. Distinct values fall from 12 to 10 (trim), 7 (case) and 4 (mapping). North has 5 orders, South 3, Central 2 and West 2; an exact filter for “North” would have found 1.
What it means. Each step removed one kind of difference, so the mapping table only had to handle three genuine variants (n., nrth and wset). The totals check at the end matters: consolidation should move rows between labels, never create or lose them.
Fictional figures
Lantern’s marketing team compares the North and South regions using orders whose region is spelled exactly “North” or “South”. An analyst consolidates the variants. The variant counts and mean order values are below. Which region has more orders, and which has the higher mean order value, before and after consolidation?
| Raw value | Maps to | Orders | Mean order value |
|---|---|---|---|
| North | North | 40 | $30.00 |
| north | North | 12 | $24.00 |
| N. | North | 18 | $22.00 |
| Nrth | North | 7 | $26.00 |
| South | South | 60 | $27.00 |
| south␣ | South | 15 | $28.00 |
- Before consolidation, only the exact spellings count. North: 40 orders at $30.00. South: 60 orders at $27.00. South looks bigger; North looks more valuable.
- Confirm the mapping before using it. “N.” and “Nrth” are suggested by their similarity to “North”, but someone at Lantern should confirm them, for example by checking the delivery addresses on a sample of those orders. Assume they have been confirmed.
- Consolidated counts: North ; South .
- Consolidated means, weighting each variant by its orders. North: . South: .
- Compare. North now has more orders (77 against 75) and the lower mean ($26.83 against $27.20). Both rankings have reversed.
Answer. Before: South has more orders (60 against 40) and North the higher mean ($30.00 against $27.00). After: North has more orders (77 against 75) and South the higher mean ($27.20 against $26.83).
What it means. North’s misspelt variants were mostly lower-value orders, plausibly phone orders keyed by staff, so the exactly spelled rows were a biased subset. A campaign aimed at “high-value North” would have rested on a spelling artefact. Note too that the simple average of North’s four variant means, , is wrong: the weights matter.
Fictional figures
Harborview Hotel’s marketing manager has $10,000 for advertising in its three largest source countries, split in proportion to bookings. A pivot table of the country column for 200 recent bookings shows the values below. A colleague proposes taking the three largest rows of the pivot table. What split does the data really support? Harborview’s data dictionary says the column should hold full English country names.
| Raw value | Bookings | Maps to |
|---|---|---|
| United Kingdom | 34 | United Kingdom |
| UK | 21 | United Kingdom |
| GB | 18 | United Kingdom |
| England | 9 | United Kingdom |
| Germany | 31 | Germany |
| DE | 14 | Germany |
| Deutschland | 6 | Germany |
| USA | 30 | United States |
| United States | 25 | United States |
| US | 12 | United States |
- The colleague’s proposal: the three largest rows are United Kingdom (34), Germany (31) and USA (30), 95 bookings in all. Splitting $10,000 in proportion gives , and .
- Build the mapping to the controlled vocabulary of full English names. UK and GB (the ISO code) become United Kingdom; England is part of the United Kingdom, so for a country-of-residence column it maps there too, a judgement worth noting in the mapping table; DE and Deutschland become Germany; USA and US become United States.
- Consolidated counts: United Kingdom ; Germany ; United States . Check: .
- Shares and budget: United Kingdom , $4,100; United States , $3,350; Germany , $2,550.
Answer. Split the budget $4,100 United Kingdom, $3,350 United States and $2,550 Germany. The raw pivot table would have given Germany $3,263.16 and the United States only $3,157.89, reversing their order.
What it means. The United States bookings were spread across three spellings, so no single row of the pivot table showed how large that market was. Consolidating before counting took about $700 away from Germany and gave about $500 more to the United Kingdom and about $200 more to the United States. The chart below shows how far each country’s largest single spelling understates its bookings.
Explore
Common mistakes
“Trimming removes every kind of space.”
It depends on the tool. Excel’s TRIM ignores the non-breaking space, and PostgreSQL’s
trimremoves only ordinary spaces by default, while Python’sstrip()removes both but leaves doubled spaces inside the value. After trimming, check the lengths of values that should be equal.“Find and replace is a quick way to fix variants.”
Replacing text inside values changes more than you intend: replacing “N.” with “North” also turns “N.E. depot” into “NorthE. depot”, and replacing “Cent” with “Central” turns every correct “Central” into “Centralral”. Map whole values through a mapping table, after normalising them, so each rule applies only to the value it names.
“Every value should end up in a category.”
Forcing an unknown value into the nearest category or an “Other” bucket hides it. Flag unmapped values, count them, and add a mapping row only after someone has confirmed what the value means.
“If two values look identical, they are identical.”
A trailing space, a non-breaking space or a different Unicode form for “é” is invisible on screen. Compare lengths, normalise to NFC and replace special spaces before concluding that a join or a filter has failed for some other reason.
“A regular expression can check that an email address is real.”
A pattern checks shape only. Simple patterns accept addresses no server will deliver to and reject some the formal syntax allows. Use a pattern to catch obvious typing errors, and a confirmation message to prove an address works.
“Consolidating categories only tidies the labels.”
It changes which rows each category contains, so counts, shares, averages and rankings can all move, as the North and South example showed. Compare the key results before and after consolidation and report the difference.
Guided practice
Work through these with hints when you need them. Every hint and every worked solution is there to be used.
Loading four questions…
On your own
No hints this time. Answer, check, and read the feedback; the solution appears once you have answered or if you ask for it. Your first answers count toward mastery.
Loading six questions…
In practice
Regional marketing at Lantern. The promotion budget is allocated by region, so the region column is a financial input. Jun Park, Lantern’s analyst, cleans it with a fixed recipe (NFC, whitespace, lower case, mapping table, validation), reports the vocabulary coverage of every monthly export, and sends new unmapped values to the operations team, who know which towns belong to which delivery region. When coverage dropped one month, the cause was a new app version that offered “Centre” instead of “Central”: one reviewed row in the mapping table fixed it for every future export.
Programme reports at Northfield University. Northfield’s student records spell programme names in several ways: abbreviations such as “Bus. Admin.”, an acronym such as “CS”, capitals, trailing spaces and the occasional typing error. The registry’s official programme list is the controlled vocabulary. Before planning analysts compare grade point averages across programmes, they map every variant to the official name and check that the number of students per programme matches the registry’s own headcount. A programme whose students are split across four spellings would otherwise appear smaller than it is, and its average would be computed from a biased subset.
Product data. Stray spaces and inconsistent capitals in product names split one product’s sales across several rows of every report, and make stock counts disagree with sales. Retailers therefore keep a product master list, with one row per product and its official name, and match every order line to it by a cleaned key rather than by the name as typed. Regular expressions earn their place here as checks: extracting the pack size from each product name and comparing it with the recorded size flags rows where one of them is wrong.
Guest origins at Harborview. The hotel’s country column feeds advertising budgets and reports to the regional tourism office. Because different booking channels write countries differently, the hotel maps every incoming value to its controlled list as bookings arrive, keeps the raw value, and reviews the mapping table each quarter. The applied example showed what skipping this step costs: a budget split that put the hotel’s second-largest market third.
Knowledge check
Six questions, one attempt each, no hints. Score 80% or more to complete the lesson; you can take it again at any time.
Loading six questions…
Summary
- Software compares text character by character, so case, whitespace, punctuation and Unicode form make equal-looking values unequal and split categories.
- Clean in order: keep the raw value, normalise Unicode (NFC), fix whitespace including non-breaking spaces, normalise case, standardise punctuation, map, validate.
- Tools differ: Excel’s TRIM ignores the non-breaking space, and PostgreSQL’s trim removes only ordinary spaces by default.
- Keep the mapping in a reviewed, versioned mapping table that maps variants to a controlled vocabulary; flag unmapped values instead of forcing them.
- Regular expressions check, extract and replace text by its shape; test them on real values and remember that shape is not meaning.
- Consolidation adds variant counts and takes a weighted mean of variant means; it can reverse rankings, so compare key results before and after.
Key formulas
Terms introduced
- Whitespace
- Characters that display as blank space, including the ordinary space, tabs, line breaks and special spaces such as the non-breaking space.
- Non-breaking space
- A space character (Unicode U+00A0) that looks like an ordinary space but is a different character, often left behind by web pages and documents, and not removed by some trim functions.
- Code point
- The number the Unicode standard assigns to a character, written in the form U+00E9.
- Case normalisation
- Converting text to a single case, usually lower case, so that values differing only in capitals compare as equal.
- Unicode normalisation
- Converting text to one of the standard forms defined in Unicode Standard Annex #15 (NFC, NFD, NFKC, NFKD), so that equivalent characters are stored the same way.
- Mapping table
- A table that lists each variant of a value and the value it should become, kept as reviewed and versioned data rather than as one-off edits.
- Controlled vocabulary
- The agreed list of allowed values for a column, usually set in the data dictionary or by an outside standard.
- Category consolidation
- Merging the variants of a categorical value into the values of a controlled vocabulary, which changes the counts and statistics of each category.
- Regular expression
- A pattern that describes a set of text strings, used to find, check, extract or replace text.
Sources
- Unicode Consortium. Unicode Standard Annex #15: Unicode Normalization Forms. unicode.org/reports/tr15
- Microsoft Support. TRIM function (Excel). support.microsoft.com/en-us/office/trim-function-410388fa-c5…
- Python Software Foundation. Built-in Types: str.casefold(). The Python Standard Library documentation. docs.python.org/3/library/stdtypes.html
- WHATWG. HTML Living Standard, section on the email input type (definition of a valid email address). html.spec.whatwg.org/dev/input.html
- Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10). jstatsoft.org/article/view/v059i10
- International Organization for Standardization. ISO 3166: Country codes. iso.org/iso-3166-country-codes.html