Standardise Excel Column Names with pandas

Headers in real workbooks are written for people, not code. " Order ID" has a leading space, "Customer↵Name" contains a line break, "Amount (€)" appears twice, one column has no header at all and the monthly columns are real dates. Every script that reads such a sheet ends up full of df["Customer\nName"], and every new export from a different system breaks it again. Cleaning the labels once, right after reading, fixes that. This guide, part of Cleaning Excel Data with pandas, builds a header cleaner, maps synonyms from different sources onto one schema and checks that the columns you need are present.

Prerequisites

pip install pandas openpyxl

A sample sheet with the typical problems:

import datetime as dt

import pandas as pd
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append([" Order ID", "Customer\nName", "Amount (€)", "Amount (€)", None,
           "Ship-Date", dt.datetime(2026, 1, 1), dt.datetime(2026, 2, 1), "Café"])
ws.append([1, "Acme", 10, 11, None, "2026-01-02", 5, 6, "yes"])
wb.save("headers.xlsx")

df = pd.read_excel("headers.xlsx")
print(list(df.columns))

pandas has already changed two things. The second "Amount (€)" is now "Amount (€).1", because column labels must be unique, and the empty header became "Unnamed: 4". The two month columns are datetime objects, not strings — anything that assumes str labels, such as df.columns.str.lower(), will produce NaN for them.

Step 1 — A header cleaning function

Convert every label to text, fold accents to plain letters, spell out the symbols that carry meaning, and collapse everything else into single underscores:

import re
import unicodedata

SYMBOLS = {"€": " eur ", "$": " usd ", "£": " gbp ", "%": " pct ", "#": " no ", "&": " and "}

def clean_header(label) -> str:
    if isinstance(label, (dt.date, dt.datetime, pd.Timestamp)):
        return f"m_{label:%Y_%m}"
    text = str(label)
    text = re.sub(r"\.(\d+)$", lambda m: f"_{int(m.group(1)) + 1}", text)   # pandas' .1 suffix
    for symbol, word in SYMBOLS.items():
        text = text.replace(symbol, word)
    text = unicodedata.normalize("NFKD", text).encode("ascii", "ignore").decode()
    text = re.sub(r"[^0-9A-Za-z]+", "_", text).strip("_").lower()
    if not text or text[0].isdigit():
        text = f"c_{text}"
    return text

print([clean_header(c) for c in df.columns])

Each rule solves a specific problem. Spelling out € as eur keeps the currency in the name — dropping symbols outright would turn "Amount (€)" and "Amount ($)" into the same amount. Turning pandas' .1 suffix into _2 makes duplicates read naturally. NFKD normalisation splits "é" into "e" plus an accent mark, and the ASCII encode drops the mark. Names that would start with a digit get a prefix, so they remain valid Python identifiers and work with df.itertuples().

Step 2 — Guarantee uniqueness after cleaning

Cleaning can create new collisions: "Amount €" and "amount (EUR)" both become amount_eur. pandas allows duplicate labels, but df["amount_eur"] then returns a DataFrame instead of a Series and most code breaks later in a confusing way. Number the repeats deterministically:

def unique(names):
    seen = {}
    out = []
    for name in names:
        seen[name] = seen.get(name, 0) + 1
        out.append(name if seen[name] == 1 else f"{name}_{seen[name]}")
    return out

df.columns = unique([clean_header(c) for c in df.columns])
print(list(df.columns))
# ['order_id', 'customer_name', 'amount_eur', 'amount_eur_2', 'unnamed_4',
#  'ship_date', 'm_2026_01', 'm_2026_02', 'cafe']

Step 3 — Drop the columns that are not really there

An unnamed_* column that is entirely empty is a spacer or leftover formatting, not data. Drop it — but only if it is empty, because an unnamed column with values usually means the header is in a different row:

empty_unnamed = [c for c in df.columns if c.startswith("unnamed_") and df[c].isna().all()]
df = df.drop(columns=empty_unnamed)
print(empty_unnamed)              # ['unnamed_4']

If many columns come out as unnamed_* and contain values, the table does not start on the first row; detect the real header first, as shown in Find the Header Row in an Excel Sheet with a Shifting Layout.

Step 4 — Map synonyms to one canonical schema

The same field arrives under different names from different systems: "Order ID", "Order No", "Ord #". After cleaning, map each known alias to the name your code uses. Keep the aliases in one table so adding a new source is a one-line change:

ALIASES = {
    "order_id": ["order_id", "order_no", "order_number", "ord_no"],
    "customer": ["customer_name", "customer", "client", "account_name"],
    "amount": ["amount_eur", "amount", "net_amount", "value_eur"],
    "ship_date": ["ship_date", "shipped_on", "dispatch_date"],
}
rename = {alias: canon for canon, aliases in ALIASES.items() for alias in aliases}
df = df.rename(columns=rename)
print(list(df.columns))
# ['order_id', 'customer', 'amount', 'amount_eur_2', 'ship_date', 'm_2026_01', 'm_2026_02', 'cafe']

rename ignores labels that are not in the dictionary, so unknown columns pass through with their cleaned names. That is deliberate: a new column should be noticed and added to the table, not dropped silently.

Step 5 — Check the required columns

After mapping, confirm that every column the rest of the pipeline needs is present, and report both what is missing and what arrived unrecognised — the unrecognised list usually tells you what the missing column is now called:

REQUIRED = {"order_id", "customer", "amount", "ship_date"}
known = set(ALIASES) | {c for c in df.columns if c.startswith("m_")}

missing = REQUIRED - set(df.columns)
unrecognised = [c for c in df.columns if c not in known]
if missing:
    raise ValueError(f"missing columns: {sorted(missing)}; unrecognised: {unrecognised}")
print("unrecognised:", unrecognised)      # unrecognised: ['amount_eur_2', 'cafe']

Failing early with both lists turns "KeyError: 'amount'" deep inside a calculation into a message the person who owns the export can act on. Wider checks on types and values are covered in Validating Excel Data with Python.

Step 6 — Restore friendly labels on output

Clean names are for code. The report you write should use labels people recognise. Keep one dictionary from canonical names to display labels and apply it at the very end:

DISPLAY = {"order_id": "Order ID", "customer": "Customer", "amount": "Amount (€)", "ship_date": "Ship date"}
out = df[["order_id", "customer", "amount", "ship_date"]].rename(columns=DISPLAY)
out.to_excel("orders_clean.xlsx", index=False)
print(list(pd.read_excel("orders_clean.xlsx").columns))
# ['Order ID', 'Customer', 'Amount (€)', 'Ship date']

Because the display labels live in one place, changing the wording of a report header never touches the calculation code.

Step 7 — Keep a record of what was renamed

When a figure in the report looks wrong, the first question is which source column it came from. Build the rename log in the same pass, from the raw labels to the final names, and save it with the output:

raw = pd.read_excel("headers.xlsx", nrows=0).columns
steps = unique([clean_header(c) for c in raw])
log = pd.DataFrame({
    "source_header": [str(c).replace("\n", " ") for c in raw],
    "cleaned": steps,
    "canonical": [rename.get(c, "") for c in steps],
})
log.to_csv("column_map.csv", index=False)
print(log.loc[log["canonical"] != "", ["source_header", "canonical"]].values.tolist())

Reading with nrows=0 fetches only the header row, so the log costs almost nothing even on large files. Comparing today's log with last month's is also the quickest way to spot that a supplier has renamed or added a column before it causes a problem downstream.

Common pitfalls

• Using names= to rename. read_excel(names=[...]) assigns labels by position. When the source inserts a column, every label after it is wrong and nothing fails.
• Calling .str methods on the columns. Date and number headers are not strings; df.columns.str.strip() turns them into NaN. Convert with a function that handles every type, as in Step 1.
• Stripping symbols that carry meaning. Dropping % from "Margin %" or the currency from "Amount (€)" can merge distinct columns. Spell them out.
• Case-sensitive aliases. Map aliases after cleaning, never before; the cleaner already lower-cases and normalises, so the alias table only needs one spelling of each name.
• Cleaning inside every script. Put the cleaner and alias table in one shared module so every job agrees on the names.

Conclusion

Read the sheet as it is, then give every column a predictable name with one cleaning function: text for every label, accents folded, meaningful symbols spelled out, underscores for everything else and numbered duplicates. Drop empty unnamed columns, map aliases from each source onto one canonical schema, fail early when required columns are missing, and restore friendly labels only when writing the report.

Frequently asked questions

Why does pandas add .1 to some column names? The sheet has two columns with the same header. pandas keeps both and renames the second one to "Name.1", the third to "Name.2", and so on, so that every column label is unique.

Where do "Unnamed: 4" columns come from? A column whose header cell is empty. pandas invents the name from the column position. It often means an empty spacer column, a stray value far to the right or a header that sits in a different row.

Should I rename columns before or after reading? After. Read the sheet as it is, clean the labels with a function, and map them to your canonical names. The names= argument of read_excel replaces headers by position, which silently mislabels data if the source adds or reorders a column.

How do I get the original header text back when writing the report? Keep the dictionary from canonical names to display labels and rename with it just before writing. Your code uses stable names; readers see friendly ones.

Related

• Up one level: Cleaning Excel Data with pandas — the full cleaning toolkit.
• Strip Whitespace and Normalise Text Columns with pandas — the same ideas applied to cell values.
• Replace Values in an Excel Column with pandas — mapping tables for values rather than headers.
• Concatenate Excel Sheets with Different Columns — combining sources once their names agree.