Cleaning text: whitespace, case, Unicode, spelling

Text columns hold the same value written many ways: Pune, Pune with a trailing space, PUNE, pune, and a Pune with an invisible character inside. To pandas each spelling is a different value, so each becomes its own group in a groupby, a missed match in a join and an extra bar in a chart.

Cleaning text means deciding what counts as the same and applying that rule the same way every time. Strip and collapse whitespace, remove case differences with casefold(), make Unicode comparable with normalisation, and map the spellings that are still different, such as Bombay and Mumbai, to one value with a mapping table that someone has reviewed. Clean into a new column, keep the original, and count distinct values before and after each step: the counts tell you whether a step did anything.

Whitespace, invisible characters and case

import pandas as pd

NBSP, ZWSP = "\u00a0", "\u200b"  # a no-break space and a zero-width space, both common in pasted text
city = pd.Series(["Pune", "Pune ", " Pune", f"Pune{NBSP}", f"Pu{ZWSP}ne", "pune", "PUNE", "Pune\t"])

print("raw:", city.nunique(), "distinct values")
stripped = city.str.strip()
print("after strip():", stripped.nunique())
folded = stripped.str.casefold()
print("after casefold():", folded.nunique())
print(folded.value_counts().to_string())

# The survivor looks like "pune" but is one character longer.
odd = folded[folded.str.len() != 4].iloc[0]
print(repr(odd), [f"U+{ord(c):04X}" for c in odd])
print("after removing U+200B:", folded.str.replace(ZWSP, "", regex=False).nunique())

# casefold() removes more case differences than lower().
print("Straße".lower(), "Straße".casefold(), "STRASSE".casefold())

raw: 8 distinct values
after strip(): 4
after casefold(): 2
pune     7
pu​ne    1
'pu\u200bne' ['U+0070', 'U+0075', 'U+200B', 'U+006E', 'U+0065']
after removing U+200B: 1
straße strasse strasse

Eight raw values become 4 after str.strip(), which removes spaces, tabs and the no-break space at both ends, and 2 after str.casefold(). The last survivor looks exactly like pune but is five characters long: it holds U+200B, a zero-width space, which can arrive with text copied from web pages and documents. Neither strip() nor casefold() touches it, because it is not whitespace to Python. Remove it explicitly, and only it, as the last count shows.

casefold() exists for caseless matching and removes more case differences than lower(): "Straße".lower() stays straße, while casefold() gives strasse, the same as "STRASSE".casefold(). Scripts without case, such as Devanagari, pass through unchanged.

The same regular expression, two answers

Collapsing runs of whitespace inside a value is a job for a regular expression, s.str.replace(r"\s+", " ", regex=True). In pandas 3 its result depends on where the text is stored:

import pandas as pd

NBSP = "\u00a0"
values = [f"New{NBSP}Delhi", "New  Delhi", "New\tDelhi"]

arrow = pd.Series(values)  # pandas 3 with PyArrow installed: the str dtype keeps its text in PyArrow
plain = pd.Series(values, dtype=pd.StringDtype("python", na_value=float("nan")))
print("storage:", arrow.dtype.storage, "and", plain.dtype.storage)

# The same regular expression, two answers: PyArrow's \s means ASCII whitespace only.
for s in (arrow, plain):
    print(s.dtype.storage, [repr(v) for v in s.str.replace(r"\s+", " ", regex=True)])

# Two ways that give the same answer in both: name the no-break space, or normalise with NFKC first.
for s in (arrow, plain):
    named = s.str.replace(f"[\\s{NBSP}]+", " ", regex=True)
    nfkc = s.str.normalize("NFKC").str.replace(r"\s+", " ", regex=True)
    print(f"{s.dtype.storage}: named class {named.nunique()} distinct value, NFKC first {nfkc.nunique()} distinct value")

storage: pyarrow and python
pyarrow ["'New\\xa0Delhi'", "'New Delhi'", "'New Delhi'"]
python ["'New Delhi'", "'New Delhi'", "'New Delhi'"]
pyarrow: named class 1 distinct value, NFKC first 1 distinct value
python: named class 1 distinct value, NFKC first 1 distinct value

pandas 3 keeps the str dtype’s text in PyArrow when PyArrow is installed and in Python objects otherwise, and s.dtype.storage tells you which. With PyArrow storage, pandas hands this pattern to PyArrow, whose regular expressions use Google’s RE2 library, where \s means ASCII whitespace only. Python’s re module also matches the no-break space. So the first value keeps its no-break space with PyArrow storage and loses it with Python storage: the same code gives two answers on two computers, depending on whether PyArrow happens to be installed.

Two fixes give the same answer everywhere. Name the character in the pattern, "[\\s\u00a0]+" as a Python string, or normalise with NFKC first, which turns a no-break space into an ordinary space (next section). The same difference applies to \d and \w: in RE2 they match ASCII digits and letters only, so a pattern meant for Devanagari digits needs an explicit character range.

Unicode normalisation: NFC and NFKC

Unicode can encode some visible letters in more than one way. é is either one code point, U+00E9, or e followed by the combining accent U+0301. The Devanagari letter क़ (QA) is either U+0958 or KA followed by the nukta sign, U+0915 U+093C. The two forms look identical and compare unequal:

import unicodedata

import pandas as pd

# Two pairs that look identical on screen, built from different code points.
words = {
    "Cafe, e-acute as one code point": "Caf\u00e9",
    "Cafe, e + combining accent": "Cafe\u0301",
    "Devanagari QA as one letter": "\u0958",
    "Devanagari KA + NUKTA": "\u0915\u093c",
}
for label, text in words.items():
    print(f"{label:<32} {text}  {[f'U+{ord(c):04X}' for c in text]}")

s = pd.Series(list(words.values()))
print("equal as typed:", s[0] == s[1], s[2] == s[3], "| distinct:", s.nunique())
nfc = s.str.normalize("NFC")
print("equal after NFC:", nfc[0] == nfc[1], nfc[2] == nfc[3], "| distinct:", nfc.nunique())
print("QA after NFC:", [f"U+{ord(c):04X}" for c in nfc[2]])

# NFKC also folds compatibility characters: useful for search keys, too strong for stored text.
for text in ["\u00a0", "\ufb01le", "m\u00b2", "\uff11\uff12"]:
    print(f"{text!r:>9} -> {unicodedata.normalize('NFKC', text)!r}")

Cafe, e-acute as one code point  Café  ['U+0043', 'U+0061', 'U+0066', 'U+00E9']
Cafe, e + combining accent       Café  ['U+0043', 'U+0061', 'U+0066', 'U+0065', 'U+0301']
Devanagari QA as one letter      क़  ['U+0958']
Devanagari KA + NUKTA            क़  ['U+0915', 'U+093C']
equal as typed: False False | distinct: 4
equal after NFC: True True | distinct: 2
QA after NFC: ['U+0915', 'U+093C']
   '\xa0' -> ' '
    'file' -> 'file'
     'm²' -> 'm2'
     '１２' -> '12'

str.normalize("NFC") turns canonically equivalent sequences into one form, so the four values become two and both pairs compare equal. NFC usually composes, but not always: for QA it returns the decomposed pair, because U+0958 is on Unicode’s list of composition exclusions, so NFC never produces it. What matters is that both spellings end up identical.

NFKC goes further and folds compatibility characters as well: the no-break space becomes a space, the ligature fi becomes fi, m² becomes m2 and full-width digits become ordinary ones. That is what you want in a comparison key and not in the stored text, where m² should stay m². A safe default: NFC for the data you keep, NFKC in a key column that you only compare.

Leave the zero-width joiner and non-joiner (U+200D and U+200C) alone. In Devanagari and other Indian scripts they change how letters join, for example asking for a half form instead of a conjunct, as the Unicode Standard describes, so removing them changes the word. Remove only characters you have decided carry no meaning, such as U+200B and the byte-order mark U+FEFF.

Spelling variants: a reviewed mapping table

After normalisation, real spelling differences remain: old and new city names, abbreviations, typos. Map them with a table, not with a chain of replace() calls. A table is data that others can read, check and extend, and it lists every decision once:

import pandas as pd

students = pd.read_csv("admissions.csv")
city_map = pd.read_csv("city_map.csv")  # reviewed: one row per spelling, with the official name
assert city_map["key"].is_unique

# The comparison key: normalised, without zero-width spaces, trimmed, case-folded, single spaces.
key = (
    students["home_city"]
    .str.normalize("NFKC")
    .str.replace("\u200b", "", regex=False)
    .str.strip()
    .str.casefold()
    .str.replace(r"\s+", " ", regex=True)
)
students["city"] = key.map(city_map.set_index("key")["city"])

print("raw spellings:", students["home_city"].nunique(), "| keys:", key.nunique(), "| cities:", students["city"].nunique())
print(students["city"].value_counts().to_string())

# Anything the table does not know is listed, never guessed.
unknown = students.loc[students["city"].isna(), "home_city"].value_counts()
print("unknown:", unknown.to_dict())

raw spellings: 16 | keys: 14 | cities: 7
city
Bengaluru    5
Mumbai       4
Chennai      3
Kolkata      2
Gurugram     2
Pune         2
Mangaluru    1
unknown: {'Banglore': 1}

key,city
bengaluru,Bengaluru
bangalore,Bengaluru
b'lore,Bengaluru
mumbai,Mumbai
bombay,Mumbai
chennai,Chennai
madras,Chennai
kolkata,Kolkata
calcutta,Kolkata
gurugram,Gurugram
gurgaon,Gurugram
pune,Pune
mangaluru,Mangaluru
mangalore,Mangaluru

student,home_city
S01,Bengaluru
S02,Bangalore
S03,Mumbai
S04,B'lore
S05,Chennai
S06,Bombay
S07,Kolkata
S08,BANGALORE
S09,Madras
S10,Gurugram
S11,mumbai 
S12,Calcutta
S13,Pune
S14,Gurgaon
S15,Bengaluru
S16,Mangalore
S17,Banglore
S18,Pune
S19,Chennai
S20,Bombay

The key column, normalised, without zero-width spaces, stripped, case-folded and with single spaces, already merges Bangalore with BANGALORE and Mumbai with mumbai : 16 raw spellings become 14 keys. The table then maps the keys to 7 official names, with Bombay going to Mumbai, Madras to Chennai, Gurgaon to Gurugram and B’lore to Bengaluru. The assert on its key column guards against a table that maps one spelling twice.

One student wrote Banglore, which the table does not know. It stays missing in the city column and is listed, never guessed: the next section shows why.

Finding near-duplicate spellings with difflib

difflib.get_close_matches from Python’s standard library suggests the known spellings closest to an unknown one, with a similarity score from 0 to 1 that SequenceMatcher.ratio() computes:

import difflib

import pandas as pd

known = pd.read_csv("city_map.csv")["key"].tolist()

# Suggestions for a spelling the table does not know, with their similarity scores.
for unknown in ["banglore", "gurgoan", "trivandrum"]:
    found = difflib.get_close_matches(unknown, known, n=3, cutoff=0.6)
    scores = [round(difflib.SequenceMatcher(None, unknown, k).ratio(), 2) for k in found]
    print(f"{unknown}: {list(zip(found, scores))}")

banglore: [('bangalore', 0.94), ('mangalore', 0.82), ("b'lore", 0.71)]
gurgoan: [('gurgaon', 0.86), ('gurugram', 0.67)]
trivandrum: []

For banglore, the best suggestion is bangalore at 0.94, a typo of Bengaluru, but the second is mangalore at 0.82, a different city. A rule that took any suggestion above a cut-off of 0.6 would send students to the wrong city. gurgoan finds gurgaon first, and trivandrum finds nothing at all, because no spelling in the table is close to it.

So treat suggestions as candidates for a person to approve. The workflow that scales: list unknown values with their counts, show each one’s suggestions, let someone who knows the data choose, add the choice to the mapping table, and run the clean again. The table grows with every review, and the clean stays repeatable.

Exercise

import unicodedata

REMOVE = ("\u200b", "\ufeff")  # zero-width space and byte-order mark


def district_key(text):
    """Return the comparison key: NFKC, invisible marks removed, stripped, case-folded, single spaces, no end '.' or ','."""
    # Replace this line with your code.
    return text.strip().lower()


def standardise(values, official):
    """Return (official names with None for unknown cells, sorted distinct unknown raw cells)."""
    # Replace these lines with your code.
    return [official.get(v) for v in values], []

from districts import district_key, standardise

COMPOSED = "\u095a\u093e\u095b\u093f\u092f\u093e\u092c\u093e\u0926"  # Ghaziabad with precomposed nukta letters
DECOMPOSED = "\u0917\u093c\u093e\u091c\u093c\u093f\u092f\u093e\u092c\u093e\u0926"  # the same, letter + nukta

OFFICIAL = {
    "pune": "Pune",
    "poona": "Pune",
    "new delhi": "New Delhi",
    "bengaluru urban": "Bengaluru Urban",
    "bangalore urban": "Bengaluru Urban",
}


def test_case_spaces_and_punctuation():
    """trims, case-folds, collapses spaces and drops a final full stop or comma"""
    assert district_key("PUNE") == "pune"
    assert district_key("  Pune. ") == "pune"
    assert district_key("Pune,") == "pune"
    assert district_key("New   Delhi") == "new delhi"
    assert district_key("New\tDelhi") == "new delhi"


def test_invisible_characters():
    """handles no-break spaces, zero-width spaces and a byte-order mark"""
    assert district_key("New\u00a0Delhi") == "new delhi"
    assert district_key("Pu\u200bne") == "pune"
    assert district_key("\ufeffPune") == "pune"


def test_compatibility_characters():
    """folds full-width letters with NFKC"""
    assert district_key("\uff30\uff55\uff4e\uff45") == "pune"


def test_devanagari_forms():
    """gives the same key for precomposed and decomposed nukta letters"""
    assert COMPOSED != DECOMPOSED
    assert district_key(COMPOSED) == district_key(DECOMPOSED)


def test_keeps_other_characters():
    """removes nothing but the listed invisible marks"""
    assert district_key("Bengaluru Urban") == "bengaluru urban"
    joined = "\u0915\u094d\u200d\u0937"  # KA, VIRAMA, ZERO WIDTH JOINER, SSA: the joiner asks for a half form
    assert district_key(joined) == joined


def test_standardise():
    """maps keys to official names and reports unknown raw values, sorted"""
    values = ["Poona", " PUNE ", "Bangalore Urban", "New\u00a0Delhi", "Banglore Urban", None, "Gurgaon", "Banglore Urban"]
    names, unknown = standardise(values, OFFICIAL)
    assert names == ["Pune", "Pune", "Bengaluru Urban", "New Delhi", None, None, None, None]
    assert unknown == ["Banglore Urban", "Gurgaon"]


def test_standardise_devanagari():
    """finds the official name whichever way the nukta letters were typed"""
    official = {district_key(COMPOSED): "Ghaziabad"}
    names, unknown = standardise([DECOMPOSED, COMPOSED], official)
    assert names == ["Ghaziabad", "Ghaziabad"]
    assert unknown == []

Interview questions

Why does Unicode normalisation matter when you group or join text columns?

Because two strings can look identical and still differ in their code points, and pandas compares code points. An accented letter can be one precomposed character or a base letter followed by a combining accent; a Devanagari letter with a nukta can be one code point or two. Text from different keyboards, systems or copy-and-paste sources mixes the forms, so groupby splits one city into two groups and a join misses rows that look like matches. Applying str.normalize("NFC") to both sides makes canonically equivalent strings identical, without changing what they say. NFKC also folds compatibility characters, such as the no-break space or full-width digits, which helps in a comparison key but changes the text itself, so keep it out of stored values. Normalise before stripping, case-folding and mapping, and count distinct values before and after to see what it changed.

Key takeaways

• Clean text into a key column and keep the original. Count distinct values after every step.
• strip() removes spaces, tabs and the no-break space at both ends; the zero-width space U+200B survives everything but an explicit replace.
• casefold() is for caseless matching and removes more than lower().
• In pandas 3 with PyArrow, regular expressions run in RE2, where \s, \d and \w are ASCII only. Name special characters in the pattern or normalise with NFKC first, and check s.dtype.storage.
• NFC makes look-alike encodings identical for stored text; NFKC also folds compatibility characters, for keys only. Never strip the zero-width joiner or non-joiner from Indian scripts.
• Map spelling variants with a reviewed table kept as a file; list unknown values instead of guessing.
• difflib gives candidates with scores, not answers: Bangalore and Mangalore are one letter apart.