Trimming Whitespace in Every Field of a CSV Safely

Whitespace is the most boring defect in a CSV and the one that most reliably breaks the next step. A name with a trailing space does not match the same name in the customer table. An email with a leading space gets rejected by the form that tries to send to it. A value of "paid " counts as a different status from "paid". The fix is one line, and the one line has two limits that cost people hours: what counts as whitespace, and what does not. This article trims a messy customer file four ways, shows the character that survives strip(), and times the work on 200,000 rows. It is part of the cleaning series that begins with a repeatable way to clean a messy CSV.

What the padding looks like

Read the file as text, with no NA guessing, and print repr of the values, because a printed table hides everything that matters:

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

df = read_raw("/batches/csv-0001-0200/lab/data/clean/customers-messy.csv")
sample = df.loc[[0, 7, 9], ["name", "email"]]
print("raw values:")
for col in sample:
    print(f"  {col:5s}", [repr(v) for v in sample[col]])

stripped = df.apply(lambda c: c.str.strip())
print("after str.strip():")
for col in ["name", "email"]:
    print(f"  {col:5s}", [repr(v) for v in stripped.loc[[0, 7, 9], col]])

# which characters are left at the ends?
def edge_chars(s):
    out = set()
    for v in s:
        if v:
            for ch in (v[0], v[-1]):
                if not ch.isalnum() and ch not in "@.+-_()$€,':;":
                    out.add(f"U+{ord(ch):04X} {unicodedata.name(ch, '?')}")
    return sorted(out)
print("odd edge characters after strip():", edge_chars(stripped["email"]) + edge_chars(stripped["name"]))

# zero-width and other format characters (category Cf) anywhere in a field
def has_cf(v): return any(unicodedata.category(ch) == "Cf" for ch in v)
cf = df.apply(lambda c: c.map(has_cf))
print("fields containing a format character (category Cf):", int(cf.sum().sum()), "->", {c: int(n) for c, n in cf.sum().items() if n})

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

raw values:
  name  ["'  Ana Silva '", "' Ed Zürich\\xa0'", "'gus riga'"]
  email ["'[email protected]'", "' [email protected] '", "'[email protected]\\u200b'"]
after str.strip():
  name  ["'Ana Silva'", "'Ed Zürich'", "'gus riga'"]
  email ["'[email protected]'", "'[email protected]'", "'[email protected]\\u200b'"]
odd edge characters after strip(): ['U+200B ZERO WIDTH SPACE']
fields containing a format character (category Cf): 1 -> {'email': 1}

The first block shows raw values: ' Ana Silva ' has two spaces in front and one behind, ' Ed Zürich\xa0' has a leading space and a trailing non-breaking space (\xa0, U+00A0), and ' [email protected] ' is padded on both sides. After str.strip() the first two are clean, and the NBSP went with them. Python's str.strip() with no argument removes every character for which str.isspace() is true, and that includes U+00A0 and the other Unicode space characters (the documentation says so). A trim that only knew about the ASCII space would have missed it, which is one reason I do not write .strip(" ").

The character that survives

The email on row 9 was '[email protected]​', and strip() left it alone. U+200B is the ZERO WIDTH SPACE. It is invisible, it often arrives with text copied from a web page or a rich-text editor, and it is not whitespace: its Unicode general category is Cf, a format character. The last two lines of the output find it:

odd edge characters after strip(): ['U+200B ZERO WIDTH SPACE']
fields containing a format character (category Cf): 1 -> {'email': 1}

The check uses unicodedata.category(ch) == "Cf" over every field. The Unicode category list gives the other members of Cf: zero-width joiner and non-joiner, the word joiner, soft hyphen and the byte order mark U+FEFF, the same character that causes the id header problem in the BOM article. Any of them in a key makes two visually identical strings different. The test for whether you have this problem is cheap: compare len(s) with len(s.strip()) and with the visible length. DuckDB shows the same thing:

bash /batches/csv-0001-0200/lab/clean/t0111c.sh

--- csv module, trimming while streaming
1,Ana Silva,[email protected],(415) 555-0132,2025-03-14,"$1,234.50","1,200",USA,active
--- DuckDB
[Ana Silva]
17|17|16
--- qsv
1,Ana Silva,[email protected],(415) 555-0132,2025-03-14,"$1,234.50","1,200",USA,active

SELECT length(email), length(trim(email)), length(replace(trim(email), chr(8203), '')) for row 9 returned 17|17|16: the email is 17 characters long, trim leaves it at 17, and removing code point 8203 (U+200B) makes it 16. The visible address, [email protected], has 16. A match against the customer table on the trimmed value would have failed, and the failure would look like a missing customer.

Four ways to trim

pandas, on a frame read as text. df.apply(lambda c: c.str.strip()) trims every column. Reading with dtype=str and keep_default_na=False is what makes it safe: without them a blank becomes NaN, and the .str accessor on a column with numbers fails or returns NaN.

The csv module, while streaming, which is the cheapest way to clean a file you will not hold in memory. The block in the output above reads each row, strips every cell and writes it out, and the printed first data line, 1,Ana Silva,[email protected],(415) 555-0132,..., has no padding. This version has no pandas and no dependencies; it is the one I use in a script that must run anywhere.

DuckDB, with trim(col), applied in the same query that reads the file. For many columns, SELECT trim(COLUMNS(*)) FROM read_csv(..., all_varchar=true) does them all in one statement (a DuckDB feature, COLUMNS(*), that I did not test here).

qsv, from the shell: qsv apply operations trim name file.csv printed the first record with Ana Silva trimmed. It works on the columns you name.

A cleaner that also removes invisible characters

For a column that will be used as a key, strip() is not enough. This version removes the zero-width characters, collapses any run of whitespace to one space, and strips:

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

INVISIBLE = re.compile(r"[​‌‍⁠]")
def clean_text(s):
    s = s.str.replace(INVISIBLE, "", regex=True)
    s = s.str.replace(r"\s+", " ", regex=True)   # collapse runs of any whitespace, including NBSP
    return s.str.strip()

df = read_raw("/batches/csv-0001-0200/lab/data/clean/customers-messy.csv")
out = df.apply(clean_text)
for col in ["name", "email"]:
    print(f"{col:5s}", [repr(v) for v in out.loc[[0, 7, 9], col]])
print("header names:", list(df.columns))

big = read_raw("/batches/csv-0001-0200/lab/data/clean/customers-messy-200k.csv")
def padded(frame): return int(frame.apply(lambda c: (c != c.str.strip()).sum()).sum())
t = time.perf_counter(); strip_only = big.apply(lambda c: c.str.strip()); t1 = time.perf_counter() - t
t = time.perf_counter(); full = big.apply(clean_text); t2 = time.perf_counter() - t
print(f"200,000 rows x 9 columns: fields with edge whitespace before: {padded(big)}, after strip(): {padded(strip_only)}")
print(f"str.strip() only: {t1:.2f}s | invisible + collapse + strip: {t2:.2f}s")
inner = full.apply(lambda c: c.str.contains(r"\s{2,}", regex=True)).sum().sum()
inner_before = strip_only.apply(lambda c: c.str.contains(r"\s{2,}", regex=True)).sum().sum()
print("fields with a run of 2+ whitespace characters inside, after strip():", int(inner_before), "| after collapsing:", int(inner))

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

name  ["'Ana Silva'", "'Ed Zürich'", "'gus riga'"]
email ["'[email protected]'", "'[email protected]'", "'[email protected]'"]
header names: ['id', 'name', 'email', 'phone', 'signup_date', 'balance', 'units', 'country', 'status']
200,000 rows x 9 columns: fields with edge whitespace before: 32950, after strip(): 0
str.strip() only: 0.03s | invisible + collapse + strip: 0.91s
fields with a run of 2+ whitespace characters inside, after strip(): 0 | after collapsing: 0

On the small file, the three problem rows come out clean: 'Ana Silva', 'Ed Zürich' and 'gus riga' for names, and '[email protected]', '[email protected]' and '[email protected]' for emails, with the zero-width space gone. The header names were already clean, but pad-proof them anyway with df.columns = df.columns.str.strip(); a header of "id " is a common reason a column "is missing".

On the generated file of 200,000 rows and 9 columns, 32,950 fields had leading or trailing whitespace before and 0 after. str.strip() alone took 0.03 s; the version with the regular expressions took 0.91 s, thirty times more, and still under a second for 1.8 million fields. Pay that cost for key columns (email, ids, codes) and use plain strip() for the rest.

What to be careful about

Trim before you compare. The order of operations in a cleaning pipeline matters: trim, then normalize case, then dedupe, then join. Dedupe on an untrimmed email and ' [email protected] ' survives next to '[email protected]', which the deduplication article shows with real counts.

Do not trim what carries meaning. A fixed-width export where padding is the format, a column of code or Markdown where leading spaces are indentation, a password hash, a field you will write back to the system it came from: keep the original. I trim into a new column or a new file and never overwrite the raw data.

Quoted padding is still padding. A value written " Ana Silva " in the CSV has the spaces inside the quotes, and the CSV parser keeps them. They are data until you decide otherwise, and that is why parsing and cleaning are separate steps.

Count what you changed. A trim that touched 32,950 fields in a 200,000 row file is a fact about the source system: someone is padding values on export. Log the number, and if it jumps, find out why.

How to tell it worked

Compare len(s) before and after for a few rows you know were padded. Count fields where s != s.strip(), which should be zero. Search for category Cf characters in key columns. Check that two rows that should match now match: count distinct values of the trimmed email against the count of distinct values of the untrimmed one, and the difference is the number of keys you just merged. And keep the raw file.

When not to trim

When the padding is the data, when the destination system trims for you and you want the raw value to reconcile against, or when the field is free text a human wrote and the spaces are part of how they wrote it. For everything that is a key, a code, a name used for matching or a number, trim, and log how many fields changed. The next steps in the series are normalizing capitalization and spelling and removing duplicate rows.