Cleaning Messy CSV Data: Headers, Duplicates, Whitespace, and Codepage Damage

Dirty CSV cleaning fails in one specific way: the cleaner runs in the wrong order and quietly merges two problems into one that looks fixed. The order below is the whole page; the tools are interchangeable.

The order that does not corrupt anything

1. Fix encoding before anything else — a codepage problem looks like a data-quality problem and every check after it reports garbage. See encoding and the BOM.
2. Confirm the shape — delimiter, column counts, row count. Never open a file you have not shaped.
3. Normalise headers, not values.
4. Trim values, deliberately and per column.
5. Type-cast with a declared schema, never inference.
6. Dedupe by key, after casting ("1" and "01" and 1 are three rows that mean one thing).
7. Quarantine rejects into a file you can read, rather than dropping them.

Steps 5 before 6 is the one people get wrong: deduplicating before casting keeps "1" and "1.0" as separate records, and you find out when the count is still wrong.

1. Shape first

head -c 512 f.csv | hexdump -C | head -4      # BOM? UTF-16? delimiter?
awk -F, 'NR==1{print "header cols:", NF; exit}' f.csv
awk -F, '{print NF}' f.csv | sort | uniq -c    # every distinct column count

Two distinct column counts means the file is two files. Fix the producer; a cleaner that pads ragged rows has just invented data — the same objection applies to converters that pad silently, which is why the converter reports the count instead of repairing it.

2. Headers: normalise names, keep meaning

Common damage: First Name, first_name , FIRSTNAME, 客户名, a stray \ufeff, an empty final column from a trailing comma. Map once, loudly:

import re, unicodedata
def clean(h: str) -> str:
    h = h.lstrip('\ufeff').strip().lower()
    h = re.sub(r'[\s\-.]+', '_', h)
    h = re.sub(r'_+', '_', h).strip('_')
    return h or 'unnamed'

Then assert the result is unique. Two source headers (Total, Total ) collapse to one key and the earlier column's data is gone with no error — the exact failure mode documented in patterns and gotchas. A rename table you can diff in review beats clever fuzzy matching; fuzzy header matching is how a pipeline silently starts reading the wrong column after someone adds Total (USD).

3. Trim where it is safe, and not everywhere

Trimming is not free: a field of " " and a field of "" are different facts, and RFC 4180 says whitespace inside a quoted field is part of the field. Rules that survive review:

• Trim leading/trailing whitespace on text columns destined for a database.
• Do not trim inside quoted fields if the producer quoted them to mean something — the case where a parser that trims unconditionally has already rewritten your data.
• Normalise internal whitespace (\s+ → single space) for search keys only, never for the stored value.
• Never strip non-breaking spaces (U+00A0) by accident: they arrive from web exports and are invisible in every viewer, which is why WHERE name = 'Acme Corp' misses. Print with repr()/JSON.stringify before deciding a value is clean.

4. Cast with a declared schema

SPEC = {'zip': str, 'phone': str, 'id': int, 'amount': Decimal, 'signed_up': 'date:ISO8601'}

Inference is the bug, not the convenience: 0123456789, NA, 1-5, 1e5, and 9007199254740993 are all values a real file contains and all values an inferencer destroys. Decimal for money is not pedantry — float accumulation on 100k rows produces a cent error that takes a day to trace.

5. Dedupe by a key you can defend

seen, out, dupes = set(), [], []
for r in rows:
    k = (r['id'], r['signed_up'])          # the key, not the whole row
    (dupes if k in seen else out).append(r)
    seen.add(k)

Whole-row dedupe misses the common case: the same record exported twice with a different exported_at. Row-hash dedupe misses 1 vs 1.0. Name the key, or you are counting instead of cleaning. Keep the duplicates in a file — a count of why they duplicate is the report the data owner actually needs.

6. Quarantine, do not delete

try:
    row = cast(row)
except CastError as e:
    rejects.append({**row, '_reject': str(e)})

Three reasons this is not optional: a dropped row makes your output count unexplainable; the reject reason is the bug report for the producer; and "we lost 4,000 rows" is a much smaller conversation than "we loaded 4,000 zeros".

Command-line tools for the 90 % case

mlr is the one that fits a pipeline: it streams, holds nothing in memory, and every transformation is a reviewable one-liner. pandas is the one that fits analysis — and it is also the one that will re-type your columns unless you pass dtype, which is the same bug with a dataframe attached.

Then keep the recipe. A cleaning script that runs twice must produce the same output; if it does not, the step that differs is the one with inference in it. For loading the result, see CSV to SQL; for files that will not fit in memory, large CSV files.