Python Doc & Data Automation

Clean Column Names and Whitespace in pandas

The supplier file opens fine, frame.head() shows an Order ID column, and the next line raises:

KeyError: 'Order ID'

Printing frame.columns.tolist() shows ['Order ID ', 'Customer\xa0Name', 'Qty', 'Unit Price\n', 'Order ID'] — a trailing space, a non-breaking space, a trailing newline and a duplicate name. The same problem appears in the data: df[df["status"] == "Open"] returns nothing because the values are "Open ", and a group-by produces Open, Open , open and Open\u00a0 as four separate groups.

Root Cause

Headers and values carry characters that are invisible in every viewer. Excel exports non-breaking spaces (U+00A0) where a user pressed Alt+Space or where a formula produced one; copy-paste from web pages brings zero-width spaces (U+200B) and directional marks; CSVs written on Windows and read with the wrong line terminator leave \r at the end of the last field; and files saved as UTF-8 with a byte-order mark prefix the first header with U+FEFF. pandas treats every one of those as part of the string, so "Order ID " and "Order ID" are different keys, and .str.strip() — which only removes ASCII whitespace by default in older pandas versions — does not remove them all. Duplicate headers add a second failure mode: pandas renames repeats to Order ID.1 on read, so the column you select may not be the one you meant.

Minimal Diagnostic

Show each header and value as code points, so invisible characters become visible, and report duplicates after normalisation.

# pip install "pandas>=2.2"
import unicodedata
from collections import Counter
from pathlib import Path
import pandas as pd

SOURCE = Path("in/supplier-orders.csv")
SUSPECT = {"\u00a0": "NBSP", "\u200b": "ZWSP", "\ufeff": "BOM", "\u2007": "FIGSP",
           "\u202f": "NNBSP", "\r": "CR", "\n": "LF", "\t": "TAB"}

def show_hidden(text: str) -> str:
    return "".join(f"<{SUSPECT[c]}>" if c in SUSPECT else c for c in str(text))

def diagnose(path: Path, sample_cols: int = 3) -> None:
    frame = pd.read_csv(path, dtype="string")
    print("headers:")
    for col in frame.columns:
        flags = [name for ch, name in SUSPECT.items() if ch in str(col)]
        print(f"  {show_hidden(col)!r:<34} len={len(str(col))} {'<- ' + ', '.join(flags) if flags else ''}")
    normalised = Counter(str(c).strip().casefold() for c in frame.columns)
    dupes = {k: v for k, v in normalised.items() if v > 1}
    print("duplicate headers after trimming:", dupes or "none")
    for col in list(frame.columns)[:sample_cols]:
        values = frame[col].dropna().astype(str)
        dirty = values[values != values.str.strip()]
        odd = values[values.str.contains("|".join(map(str, SUSPECT)), regex=True, na=False)]
        print(f"  {show_hidden(col)!r}: {len(dirty)} value(s) with outer whitespace, "
              f"{len(odd)} with hidden characters, e.g. {show_hidden(odd.iloc[0]) if len(odd) else '-'}")

if __name__ == "__main__":
    diagnose(SOURCE)

headers:
  '<BOM>Order ID ' len=10 <- BOM
  'Customer<NBSP>Name' len=13 <- NBSP
  'Qty' len=3
  'Unit Price<LF>' len=11 <- LF
  'Order ID' len=8
duplicate headers after trimming: {'order id': 2}

Every symptom is now explicit: a BOM on the first header, a non-breaking space inside the second, a newline on the fourth, and two columns that become identical after trimming.

Invisible characters and where they come from A byte order mark at U+FEFF comes from Excel's UTF-8 CSV export and attaches to the first header, breaking selection by name. A non-breaking space at U+00A0 comes from Excel cells and web copy-paste and makes values look equal while comparing unequal. A zero-width space at U+200B comes from web copy-paste and is invisible everywhere. A carriage return at U+000D comes from Windows line endings read with the wrong terminator and attaches to the last field. Character Code point Comes from Effect BOM U+FEFF Excel UTF-8 CSV breaks first header Non-breaking space U+00A0 Excel, web paste unequal values Zero-width space U+200B web paste invisible mismatch Carriage return U+000D Windows line endings trailing in last field

Fix: One Normaliser for Headers and Values

Normalise Unicode, replace the space-like characters with plain spaces, strip and collapse runs, then apply a stable naming convention to headers. Changed lines carry comments.

# pip install "pandas>=2.2"
import re
import unicodedata
from pathlib import Path
import pandas as pd

SPACE_LIKE = dict.fromkeys(map(ord, "\u00a0\u2007\u202f\u2009\u200a\u2002\u2003"), " ")  # changed
ZERO_WIDTH = dict.fromkeys(map(ord, "\u200b\u200c\u200d\u2060\ufeff"), None)             # changed: removed

def clean_text(value):
    if not isinstance(value, str):
        return value
    text = unicodedata.normalize("NFKC", value)          # changed: fold width and compatibility forms
    text = text.translate(SPACE_LIKE).translate(ZERO_WIDTH)
    text = re.sub(r"\s+", " ", text)                      # changed: collapse runs incl. CR/LF/TAB
    return text.strip()

def snake_case(name: str) -> str:
    text = clean_text(str(name))
    text = re.sub(r"[^\w\s]+", " ", text, flags=re.UNICODE)     # punctuation to spaces
    text = re.sub(r"(?<=[a-z0-9])(?=[A-Z])", " ", text)          # split camelCase
    return "_".join(part.lower() for part in text.split()) or "unnamed"

def clean_frame(frame: pd.DataFrame, rename: dict[str, str] | None = None) -> pd.DataFrame:
    out = frame.copy()
    names, used = [], {}
    for col in out.columns:
        name = snake_case(col)
        if name in used:                                          # changed: disambiguate duplicates
            used[name] += 1
            name = f"{name}_{used[name]}"
        else:
            used[name] = 1
        names.append(name)
    out.columns = names
    for col in out.select_dtypes(include=["object", "string"]).columns:
        out[col] = out[col].map(clean_text)                       # changed: same rules for values
    if rename:
        missing = set(rename) - set(out.columns)
        if missing:
            raise KeyError(f"expected columns not found after cleaning: {sorted(missing)}")  # changed
        out = out.rename(columns=rename)
    return out

if __name__ == "__main__":
    raw = pd.read_csv(Path("in/supplier-orders.csv"), dtype="string", encoding="utf-8-sig")  # changed: BOM
    orders = clean_frame(raw, rename={"order_id": "order_id", "customer_name": "customer"})
    print(orders.columns.tolist())

['order_id', 'customer', 'qty', 'unit_price', 'order_id_2']

encoding="utf-8-sig" removes the byte-order mark at read time, which is cleaner than stripping it afterwards. NFKC normalisation folds full-width characters and ligatures, so Ｏｒｄｅｒ　ＩＤ from a Japanese export becomes Order ID. Numbering duplicates rather than dropping them keeps the data intact and makes the collision visible — order_id_2 in the output is a question for the supplier, not something to silently discard.

Raising on a missing expected column is what turns a header change into an immediate, clear failure instead of a KeyError three steps later in the pipeline.

Normalisation order Files are read with utf-8-sig so the byte order mark is removed. Each string is normalised with NFKC to fold width and compatibility forms. Space-like characters such as non-breaking and figure spaces become ordinary spaces. Zero-width characters are removed. Runs of whitespace including carriage returns and tabs collapse to one space and outer whitespace is stripped. Headers are then converted to snake case and duplicates are numbered. Read utf-8-sig removes the BOM before it reaches the header NFKC normalise full-width and compatibility forms folded Space-like to space U+00A0, U+202F, U+2007 become ordinary spaces Remove zero-width U+200B, U+200D, U+FEFF deleted Collapse and strip runs of whitespace, CR and TAB to one space snake_case + numbering order_id, order_id_2 — collisions stay visible

Variant Fix 1: Keeping the Original Headers for Output

Reports often need the supplier's original headings even though the code works with clean names. Keep a mapping and restore it on export:

# pip install "pandas>=2.2"
import pandas as pd

def clean_with_mapping(frame: pd.DataFrame) -> tuple[pd.DataFrame, dict[str, str]]:
    cleaned = clean_frame(frame)
    mapping = dict(zip(cleaned.columns, [clean_text(str(c)) for c in frame.columns]))
    return cleaned, mapping

def restore_headers(frame: pd.DataFrame, mapping: dict[str, str]) -> pd.DataFrame:
    return frame.rename(columns={k: v for k, v in mapping.items() if k in frame.columns})

Cleaned names are the contract inside the code; the mapping is presentation. That separation also makes column renames upstream a one-line change in the mapping rather than a search across the codebase — the same principle as the field map in list PDF form field names with Python.

Variant Fix 2: Whitespace Inside Values That Should Stay

Collapsing all whitespace is wrong for free-text columns such as addresses or comments, where line breaks carry meaning. Apply different rules per column type:

# pip install "pandas>=2.2"
import re
import pandas as pd

def clean_values(frame: pd.DataFrame, multiline_cols: tuple[str, ...] = ()) -> pd.DataFrame:
    out = frame.copy()
    for col in out.select_dtypes(include=["object", "string"]).columns:
        if col in multiline_cols:
            out[col] = out[col].map(lambda v: re.sub(r"[ \t]+", " ",
                                                     clean_text(v).replace(" \n", "\n")) if isinstance(v, str) else v)
        else:
            out[col] = out[col].map(clean_text)
    return out

For key columns — anything used in a join, lookup or group-by — go further and store a folded key alongside the display value, so "ACME Ltd" and "acme ltd" match without losing the supplier's capitalisation. That pattern is covered in fix merge indicator unexpected left_only rows.

Group-by before and after cleaning Before cleaning, grouping by status returns Open with 412 rows, Open with a trailing space with 88 rows, open in lower case with 31 rows and Open with a non-breaking space with 12 rows, so the real total of 543 is split across four rows of the report. After cleaning and case folding, one Open group holds all 543 rows. Raw values 'Open' 412 'Open ' 88 'open' 31 'Open\xa0' 12 4 groups, total hidden After clean_text + casefold 'open' 543 1 group, total visible

Applying the Same Rules Everywhere

Cleaning that lives in one notebook drifts from cleaning in the scheduled job. Put the normaliser in a small module every job imports, and test it against the characters you have actually seen:

# tests/test_clean.py  — pip install pytest "pandas>=2.2"
import pandas as pd
import pytest

from cleaning import clean_frame, clean_text, snake_case

@pytest.mark.parametrize("raw, expected", [
    ("Order ID ", "order_id"),
    ("\ufeffOrder ID", "order_id"),
    ("Customer\u00a0Name", "customer_name"),
    ("Unit Price\n", "unit_price"),
    ("unitPrice", "unit_price"),
    ("Total (EUR)", "total_eur"),
    ("", "unnamed"),
])
def test_snake_case(raw, expected):
    assert snake_case(raw) == expected

def test_values_and_duplicates():
    frame = pd.DataFrame({"Status ": ["Open ", "open", "Open\u00a0"], "Status": ["a", "b", "c"]})
    cleaned = clean_frame(frame)
    assert list(cleaned.columns) == ["status", "status_2"]
    assert cleaned["status"].str.casefold().nunique() == 1

Every row in the parametrised list is a real header seen in a real file; adding a new one each time a supplier surprises you turns the test suite into documentation of the sources' quirks.

Verification

After cleaning, assert that no header or value still carries hidden characters and that the columns the pipeline needs exist.

# pip install "pandas>=2.2"
import re
import pandas as pd

HIDDEN = re.compile(r"[\u00a0\u200b\u200c\u200d\u2060\ufeff\r\n\t]|^\s|\s$|\s{2,}")

def verify_clean(frame: pd.DataFrame, required: set[str]) -> None:
    bad_headers = [c for c in frame.columns if HIDDEN.search(str(c)) or c != c.strip()]
    assert not bad_headers, f"headers still unclean: {bad_headers}"
    assert not frame.columns.duplicated().any(), f"duplicate headers: {frame.columns[frame.columns.duplicated()].tolist()}"
    missing = required - set(frame.columns)
    assert not missing, f"missing required columns: {sorted(missing)}"
    for col in frame.select_dtypes(include=["object", "string"]).columns:
        values = frame[col].dropna().astype(str)
        offenders = values[values.map(lambda v: bool(HIDDEN.search(v)))]
        assert offenders.empty, f"{col}: {len(offenders)} value(s) with hidden characters, e.g. {offenders.iloc[0]!r}"
    print(f"{len(frame.columns)} columns clean; {len(frame)} rows checked")

Run it immediately after reading every external file. The failure message names the column and shows an offending value, which usually identifies both the supplier and the tool that produced the file.

FAQ

Does str.strip() remove non-breaking spaces? In recent pandas versions Series.str.strip() strips Unicode whitespace, which includes U+00A0 but not zero-width characters. Explicit replacement is predictable across versions.

Should I lower-case column names? Yes for code-facing names — mixed case is a constant source of KeyError. Keep the original for display through a mapping.

How do I handle headers spread over two rows? Read with header=[0, 1] and join the levels, as in fix pivot table MultiIndex columns in Excel.

Will NFKC change my data? It folds compatibility forms: ① becomes 1, full-width digits become ASCII, fi becomes fi. That is desirable for keys and headers; apply it more carefully to free text where the original glyphs matter.

Part of Cleaning Messy CSV Data with pandas.