Data Cleaning Techniques That Do Most of the Work

Most cleaning jobs are the same eight techniques applied in the right order. The order is not decoration: normalizing before matching finds duplicates that matching alone would miss entirely.

Leading and trailing spaces, double spaces between words, non-breaking spaces pasted from a web page, and zero width characters from a bad export. Every one of them defeats exact matching while being invisible on screen. This runs first because everything after it compares strings.

When UTF-8 text is read back as Latin-1, Muñoz becomes Muñoz and similar wreckage. The damage is mechanical, so it can be reversed by re-encoding the string and decoding it correctly. Do it before anything that looks at names, because the damaged and undamaged versions of the same name will never match.

Deciding what a column holds before deciding how to fix it. A column of five digit numbers is a postcode in one file and a quantity in another, and the difference determines whether a leading zero should be restored or would be nonsense. Header names plus the shape of the values gets this right most of the time.

Convert every date to ISO 8601, which is year, month, day. It sorts correctly as text, it is unambiguous, and it ends the day-first versus month-first argument.

The trap is 03/04/2026, which is two different days depending on where the file came from. Resolve it at the column level: if any value in the column has a first number above twelve, the whole column is day first. Guessing row by row produces a column with two conventions in it, which is worse than the original.

Strip currency symbols and thousand separators, convert the European decimal comma, and turn accounting parentheses into a minus sign. A column of amounts stored as text does not sum, and a total that silently ignores the text rows is the kind of error that reaches a report.

Map every variant of a category to one canonical value: state names to two letter codes, country variants to one country name, Yes and Y and TRUE to one value. Grouping and filtering both depend on it. See address standardization for the fields that cause the most trouble.

Only now, with the values normalized, does matching work properly. Compare on email exactly, on a normalized name and company key, then on a similarity score using edit distance and shared tokens. Block the comparisons by a short prefix so a large file does not become every row compared to every other row. More on how that works in fuzzy matching.

The last technique is not a transformation. Look at every proposed change before applying it, and keep the list of what was applied. Without it you have a cleaner file and no way to answer a question about it, which is the state most cleaned files are in. A data cleaning tool that shows the list before it applies anything is the short way to both, and the data cleaning best practices post covers the habits around it.