Normalizing Capitalization and Spelling in a CSV Column

If a column is meant to hold a country, a status or a category, it should hold a small number of distinct values. A CSV exported from several systems holds many more: USA, usa, U.S., United States. They all mean one thing, and every group by, filter and join treats them as five. Fixing this is mechanical for capitalization, tedious for spelling variants and risky for typos, because the tool that makes the typos go away can also make them wrong. This article normalizes two columns of the 200,000 row customer file, shows the mapping-table approach that keeps you in control, and demonstrates a fuzzy match that picked the wrong country. It is part of the cleaning series. The standard-library pieces used are difflib and unicodedata.

Count the spellings first

A cleaning rule is only as good as the list of what exists, so print the distinct values of the column with counts:

import sys, difflib, unicodedata
sys.path.insert(0, "/batches/csv-0001-0200/lab/clean")
import pandas as pd
from clean import *

df = drop_blank_and_footer(read_raw("/batches/csv-0001-0200/lab/data/clean/customers-messy-200k.csv"))[0].apply(lambda c: c.str.strip())
print("country spellings in the file:")
print(df["country"].value_counts().to_string())
print("status spellings:", df["status"].value_counts().to_dict())

# 1. Map every variant to one code, through a table you can read
df["country_code"] = df["country"].map(norm_country)
print("\nafter mapping:", df["country_code"].value_counts(dropna=False).to_dict())
print("unmapped values:", df.loc[df["country_code"].isna(), "country"].unique().tolist())

# 2. A status column: one case, not title case
df["status_n"] = df["status"].str.casefold()
print("status after casefold:", df["status_n"].value_counts().to_dict())

# 3. Typos: exact table first, fuzzy suggestions only as suggestions
known = ["usa", "united states", "u.s.", "united states of america", "italy", "germany", "brazil", "switzerland", "ukraine", "austria", "australia"]
typos = ["Itally", "Germny", "Brasil", "Untied States", "Austrai", "Austraila", "Switserland", "Narnia"]
print("\nfuzzy suggestions (difflib, cutoff 0.8):")
for t in typos:
    cands = difflib.get_close_matches(t.lower(), known, n=3, cutoff=0.8)
    scores = [round(difflib.SequenceMatcher(None, t.lower(), c).ratio(), 2) for c in cands]
    print(f"  {t:15s} -> {list(zip(cands, scores))}")
print("  Austria vs Australia ratio:", round(difflib.SequenceMatcher(None, "austria", "australia").ratio(), 2))

# 4. Unicode: the same visible word, two different strings
composed = "Zürich"; decomposed = "Zürich"
print("\nZürich composed == decomposed:", composed == decomposed, "| lengths", len(composed), len(decomposed),
      "| equal after NFC:", unicodedata.normalize("NFC", composed) == unicodedata.normalize("NFC", decomposed))
print("casefold vs lower: 'Straße'.lower() =", "Straße".lower(), "| 'Straße'.casefold() =", "Straße".casefold(), "| 'STRASSE'.casefold() =", "STRASSE".casefold())

python /batches/csv-0001-0200/lab/clean/t0118.py

country spellings in the file:
country
USA                         20359
usa                         20078
United States               20027
Germany                     19979
ITALY                       19949
U.S.                        19949
Brazil                      19947
United States of America    19843
Italy                       19826
germany                     19821
status spellings: {'active': 33556, 'Active': 33404, 'pending': 33390, 'ACTIVE': 33366, 'inactive': 33109, 'Inactive': 32953}

after mapping: {'US': 100256, 'DE': 39800, 'IT': 39775, 'BR': 19947}
unmapped values: []
status after casefold: {'active': 100326, 'inactive': 66062, 'pending': 33390}

fuzzy suggestions (difflib, cutoff 0.8):
  Itally          -> [('italy', 0.91)]
  Germny          -> [('germany', 0.92)]
  Brasil          -> [('brazil', 0.83)]
  Untied States   -> [('united states', 0.92)]
  Austrai         -> [('australia', 0.88), ('austria', 0.86)]
  Austraila       -> [('australia', 0.89), ('austria', 0.75)]
  Switserland     -> [('switzerland', 0.91)]
  Narnia          -> []
  Austria vs Australia ratio: 0.88

Zürich composed == decomposed: False | lengths 6 7 | equal after NFC: True
casefold vs lower: 'Straße'.lower() = straße | 'Straße'.casefold() = strasse | 'STRASSE'.casefold() = strasse

The country column has ten values for four countries: USA (20,359), usa (20,078), United States (20,027), U.S. (19,949) and United States of America (19,843) for the United States; Germany and germany; ITALY and Italy; and Brazil. The status column has six values for three states, active, Active and ACTIVE (33,556, 33,404 and 33,366), inactive and Inactive, and pending. Counted separately, the United States is five countries with about 20,000 rows each, and a report grouping by country would show twenty countries. Print this list for every categorical column, because it also tells you how big the problem is: ten values is a table; ten thousand is a different project.

A mapping table, not a clever function

For countries, the safest rule is a dictionary from every known spelling to one code, applied after lowercasing and trimming:

COUNTRIES = {"usa": "US", "u.s.": "US", "united states": "US", "united states of america": "US",
             "italy": "IT", "germany": "DE", "brazil": "BR", ...}

df["country"].map(norm_country) applied it, and the result: {'US': 100256, 'DE': 39800, 'IT': 39775, 'BR': 19947} with an empty list of unmapped values. The table is data you can read, review, extend and share with the person who owns the source. The line that matters most is the one that follows it: print the values that did not map. A new spelling will arrive, and a mapping that silently turns it into None or leaves it as it was hides it. Fail the run, or write unmapped rows to a review file, and extend the table.

Use a standard code for the target, ISO 3166-1 alpha-2 for countries, because it is unambiguous, sortable and what most systems expect. The full names are for display.

Case: casefold, not title

For comparison and grouping, make the case uniform. The status column went from six values to three with str.casefold(): active 100,326, inactive 66,062 and pending 33,390. casefold is lower for comparison purposes and it handles more of Unicode:

'Straße'.lower() = straße | 'Straße'.casefold() = strasse | 'STRASSE'.casefold() = strasse

German ß lowercases to itself and case-folds to ss, so only casefold makes Straße and STRASSE equal. Use it for the key you compare on, and keep a separate display value.

Do not reach for title(). It capitalizes every word and produces Mcdonald, Van Der Berg and Iphone, as the splitting article showed. For a controlled vocabulary like a status or a country code you do not need title case at all: you need the canonical value from your table. For free text such as names, the safe rule is to change case only when the value is entirely upper or entirely lower, and to leave mixed case alone, since mixed case is usually a person's intent.

Unicode: the same word as different strings

Two strings can look identical and compare unequal. Zürich can be written with the single code point ü (U+00FC) or with u followed by a combining diaeresis (U+0308):

Zürich composed == decomposed: False | lengths 6 7 | equal after NFC: True

They have lengths 6 and 7 and compare unequal until both are normalized with unicodedata.normalize("NFC", s). Some file systems and some export tools produce the decomposed form; most keyboards produce the composed one. A join that "should" match and does not is often this. Normalize every text key to NFC (or NFKC when you also want to fold compatibility characters), along with trimming and casefolding, as one function applied to the key.

Fuzzy matching: a suggestion, not a fix

Typos are the hard case. Itally, Germny and Untied States are clearly misspellings, and a program can find the nearest known value. Python's standard library has difflib.get_close_matches. Here are its suggestions for eight misspelled countries against a list of known ones, with a similarity cutoff of 0.8:

• Itally to italy (0.91)

• Germny to germany (0.92)

• Brasil to brazil (0.83)

• Untied States to united states (0.92)

• Switserland to switzerland (0.91)

• Austrai to australia (0.88), austria (0.86)

• Austraila to australia (0.89), austria (0.75)

• Narnia to nothing

Most are right. The one that matters is Austrai. The person meant Austria, a transposition of two letters, and the function ranked Australia first, with a higher score than Austria, because the similarity ratio rewards the shared letters and length. If you accept the top suggestion automatically, you have moved an Austrian customer to Australia, with a confidence of 0.88. The plain ratio of the correct words, austria against australia, is also 0.88: the two countries are as similar to each other as a typo is to its target.

So the rule I follow is: exact table first, fuzzy second, and the fuzzy result is a suggestion in a review file, never an automatic replacement, unless the data is low-stakes. For high-stakes columns, accept a suggestion only when it is the single candidate above the cutoff and well clear of the second (for example, a margin of 0.1), and send everything else for review. Narnia matched nothing at 0.8, which is the right answer: an unknown value should stay unknown and be reported, not coerced to the nearest thing.

Writing it so it can be redone

The normalization is a function of the input and the mapping table, so the pipeline can be rerun when the table grows. Keep:

• the original column (country_raw)

• the normalized value (country)

• optionally a country_match column: exact, suggested, unmapped

and count the values of the last one on every run. A jump in unmapped is a new data source or a new typo; a rise in suggested is a reason to look at what the fuzzy step is doing.

How to tell it worked

The number of distinct values should equal the number of real categories, which you know. The sum of the counts per value should equal the number of rows. Spot check three values from each source system. Look at the unmapped and suggested rows. Run the normalization on its own output; it should change nothing. And compare group-by counts before and after: the combined counts for the variants should equal the count of the normalized value (here 20,359 + 20,078 + 20,027 + 19,949 + 19,843 = 100,256).

When a fuzzy match is only a guess

When the values are names of people, companies or places that are close to one another, when a wrong match costs more than a missing one, and when you cannot ask the data owner. Use a mapping table, review the suggestions and decide. For a name field, the matching problem is entity resolution, which is a larger topic than a cleaning step. The next article removes blank rows and stray footer lines, which need to go before any of this runs.