Cleaning and Normalizing Text Data: Case, Spaces and Unicode

Why case causes data problems

Data collected from people is inconsistent. One customer types New York, another new york, a third NEW YORK. A computer sees three different values, so counts, groups and joins are wrong: a pivot table shows three entries for one city, and a lookup finds nothing because the case differs. Fixing case is one of the first steps in cleaning any table of text.

The aim is not to make the text pretty. It is to make equal things equal. For that reason, data teams distinguish two copies of a value: the original, which is kept for display and for audit, and a normalized key, which is used for comparison and matching. Changing the original in place makes mistakes irreversible.

Lowercase, casefold and uppercase

The usual choice for a normalized key is lowercase. It is the habit in most tools: LOWER in SQL and spreadsheets, str.lower() in Python, and the lower() function of pandas. For international text, casefold is better, because it handles letters such as the German ß, which folds to ss, so that Straße and STRASSE become equal. Python offers str.casefold(), and pandas offers str.casefold() for whole columns.

Uppercase is used less often as a key, but some systems, such as older databases and codes in logistics, store everything in capitals. Whatever you choose, apply it to both sides of a comparison. A common bug is to lowercase one column and forget the other.

Trim, collapse and normalize spaces

Case is only one problem. Leading and trailing spaces make John and John look different. Double spaces inside text, tabs, and non-breaking spaces, which look like normal spaces but have a different code, cause the same trouble. A good routine trims the ends, collapses runs of whitespace into one space and replaces special spaces with normal ones.

Do these steps before you change case, or in the same pass. In a spreadsheet, TRIM removes extra spaces but not the non-breaking space, which needs SUBSTITUTE with the character code 160. In Python, ' '.join(s.split()) collapses whitespace in one line. The converter on this page does not trim, so clean spaces separately.

Unicode normalization

The same visible letter can be stored in different ways. The letter é can be one character, or the letter e followed by a combining accent. The two look identical but compare as different. The Unicode standard defines normalization forms, NFC and NFD, and the compatibility forms NFKC and NFKD, to bring text to one standard representation.

For matching, NFC or NFKC is the usual choice, applied before case folding. Python has unicodedata.normalize(), JavaScript has String.prototype.normalize(), and databases usually offer a function or a collation setting. Without normalization, a user whose keyboard produces combined characters may fail to match an entry that was typed with composed ones.

Deduplication and joins

After cleaning, the keys can be used to find duplicates: group by the normalized key and count. Review the duplicates before you delete them, because two records that differ only in case are usually the same entity, but not always. A person named Mark and a brand named MARK in one list are different things.

For joins between tables, apply the same normalization to both sides, and keep the original columns. If you have a large table, create the normalized key as a stored column or an index, so that the join does not need to recompute it for every row.

When not to change case

Do not lowercase values where case carries meaning. Passwords and tokens are case sensitive, base64 strings and many IDs use both cases, and file paths on Linux differ by case. Code identifiers in case-sensitive languages and chemical symbols such as Co and CO, for cobalt and carbon monoxide, mean different things. Changing them destroys information.

Names are another case. Changing a name to Title Case breaks McDonald and van der Berg. For names and addresses, store the original and only normalize a copy for matching. And for text that someone will read, keep the original capitals unless you have a clear reason to change them.

A simple recipe and the converter

A reliable recipe for matching text is: normalize Unicode, trim and collapse spaces, case fold, and then compare. Keep the original column next to the cleaned one. Write the steps in code or in a recorded tool such as Power Query so that you can repeat them on new data.

For quick jobs, the converter helps. Paste a column of values, one per line, choose lowercase or Title Case, and paste the result back. It is also a convenient way to see what a conversion does before you commit to it in a script. Check names and acronyms, and keep the original copy.

Documenting the rules

Write down the cleaning rules you apply, in the order you apply them, and store them with the data. A short note such as normalize Unicode, trim, collapse spaces, casefold makes the work repeatable and lets a colleague understand why two records were merged. When the source data is updated, you can run the same steps again instead of rebuilding them from memory, and you can explain any surprising match or mismatch later. Good documentation is cheap when you write it at the time and costly when you try to reconstruct it afterwards.