Fix Duplicate Column Names When Reading Excel with pandas

A sheet has two columns headed "Sales", and pandas quietly renames the second one:

['Region', 'Sales', 'Sales.1', 'Sales ', 'sales', 'Unnamed: 5', 'Region.1']

Nothing fails yet. The trouble comes later: code that selects df["Sales"] gets only the first column, a lookup table built from the sheet loses rows, or a DataFrame assembled with duplicate names makes assignments fail with ValueError: Cannot set a DataFrame with multiple columns to the single column Total. This page shows how duplicates arise when reading Excel, how to find all of them including near-duplicates, and how to give every column a name that says what it is. It is part of Troubleshooting Common Python Excel Errors.

Prerequisites

pip install pandas openpyxl

Two files. sales_by_year.xlsx has a two-row header: years in merged cells on the first row, and "Sales" and "Units" repeated under each year on the second, plus a region code column also headed "Region". typed_by_hand.xlsx is the kind of sheet people maintain by hand: "Sales", "Sales " with a trailing space and "sales" in lower case, and a column with no header at all.

Step 1 — See what pandas did with the headers

Read the sheet with the second row as the header and look at the column names:

import pandas as pd

df = pd.read_excel("sales_by_year.xlsx", header=1)
print(df.columns.tolist())
print(df.head(2).to_string())

pandas never returns duplicate names from read_excel; it appends .1, .2 to repeats. So Sales is 2025 and Sales.1 is 2026, which nobody reading the code will guess, and Region.1 is actually a region code. The names are unique but meaningless.

The dangerous version is when duplicate names are created after reading, for example by assigning a header row yourself, which does not deduplicate:

raw = pd.read_excel("sales_by_year.xlsx", header=None)
manual = raw.iloc[2:].copy()
manual.columns = raw.iloc[1].tolist()
print(manual.columns.tolist())
print("df['Sales'] is a", type(manual["Sales"]).__name__)
try:
    manual["Total"] = manual["Sales"] * 2
except ValueError as e:
    print("ValueError:", e)
records = manual.to_dict("records")
print(records[0])

Selecting a duplicated name returns a DataFrame instead of a Series, so arithmetic and assignment fail. Worse, to_dict("records") only warns that "columns are not unique" and keeps the last value per name: the first record says Region: 'N', the code, having silently dropped North, and its Sales is the 2026 figure with 2025 gone.

Step 2 — Find duplicates and near-duplicates

Exact duplicates are easy to spot; the ones that cause most confusion differ only in case or whitespace, so pandas treats them as different columns. Normalise the raw headers and group them:

from openpyxl import load_workbook

def header_report(path, row=1):
    ws = load_workbook(path, read_only=True).active
    headers = next(ws.iter_rows(min_row=row, max_row=row, values_only=True))
    report = pd.DataFrame({"position": range(len(headers)), "raw": headers})
    report["key"] = [" ".join(str(h).split()).casefold() if h is not None and str(h).strip() else "<blank>"
                     for h in headers]
    return report[report.duplicated("key", keep=False) | (report["key"] == "<blank>")]

print(header_report("typed_by_hand.xlsx").to_string(index=False))
print(pd.read_excel("typed_by_hand.xlsx").columns.tolist())

pandas read that sheet without complaint and produced Sales, Sales and sales as three separate columns, plus Unnamed: 4 for the blank header. The report shows they collide once whitespace and case are ignored, and that position 4 has no header at all.

Step 3 — Build names from a two-row header

When the duplicates are real, different columns that share a label under a group heading, the group heading is the missing part of the name. The years sit in merged cells, and a merged range stores its value only in its first cell, so take the merged ranges from openpyxl and spread each year across the columns it covers. Columns outside a merged range get no year:

def two_row_names(path):
    ws = load_workbook(path).active
    top = [c.value for c in ws[1]]
    for rng in ws.merged_cells.ranges:
        if rng.min_row == 1:
            for col in range(rng.min_col, rng.max_col + 1):
                top[col - 1] = ws.cell(row=1, column=rng.min_col).value
    bottom = [c.value for c in ws[2]]
    return [f"{b} {t}" if t not in (None, "") else b for t, b in zip(top, bottom)]

names = two_row_names("sales_by_year.xlsx")
two = pd.read_excel("sales_by_year.xlsx", header=None, skiprows=2, names=names)
print(names)
print(two.columns.tolist())

The sales and units columns now say which year they belong to. The two region columns are still both called Region, because no heading distinguishes them, and passing duplicate names to read_excel gets the same treatment as a duplicate header: pandas renames the second to Region.1 without comment. That column needs a decision, which is what Step 4 is about; here it is a region code, so name it that:

two = two.rename(columns={"Region.1": "Region code"})
print(two.columns.tolist())

Forward-filling the top row with pandas instead, a common recipe, would have labelled the region code column Region 2026, because an empty cell after a merged range looks exactly like the empty cells inside one. Read Excel with Multi-Row Headers in pandas covers deeper hierarchies.

Step 4 — Rename by position when the header cannot be trusted

Sheets typed by hand need a decision about what each column is, and the decision belongs in the code, not in pandas' .1 suffixes. Map positions to names explicitly and check the raw headers are the ones you expect, so a column inserted next month stops the script instead of shifting every name:

EXPECTED = {0: ("region", "Region"), 1: ("sales", "Sales Q1"), 2: ("sales", "Sales Q2"),
            3: ("sales", "Sales Q3"), 4: ("<blank>", "Flag"), 5: ("notes", "Notes")}

raw_headers = pd.read_excel("typed_by_hand.xlsx", header=None, nrows=1).iloc[0].tolist()
keys = [" ".join(str(h).split()).casefold() if pd.notna(h) else "<blank>" for h in raw_headers]
mismatch = {i: k for i, k in enumerate(keys) if EXPECTED.get(i, (None,))[0] != k}
assert not mismatch and len(keys) == len(EXPECTED), f"unexpected headers: {mismatch}"

clean = pd.read_excel("typed_by_hand.xlsx", header=0)
clean.columns = [EXPECTED[i][1] for i in range(len(EXPECTED))]
print(clean.to_string(index=False))

The names in EXPECTED cannot be derived from the sheet; they come from asking whoever maintains it what each column means. Here the three "Sales" columns turned out to be three quarters typed by different people, and the unlabelled column a review flag. Writing that knowledge into the mapping is the real fix: the next person reading the code sees Sales Q2 instead of guessing what Sales with a trailing space was supposed to be. Keep the expected raw headers next to the new names, as here, so the check fails the day someone relabels a column.

The third branch is the only one where deleting a column is right, and only after checking: two columns called "Sales" that hold the same numbers are a copy-paste leftover, but two that differ in one row are two different things with a typo in the header. df["Sales"].equals(df["Sales.1"]) answers it before anything is dropped.

Step 5 — Guard the rest of the pipeline

Whatever the source, assert uniqueness at the boundary where data enters your code; it is one line, and it turns every later symptom into a clear message at the right place:

def require_unique_columns(frame, source):
    dupes = frame.columns[frame.columns.duplicated()].tolist()
    if dupes:
        raise ValueError(f"{source}: duplicate column names {dupes}")
    return frame

require_unique_columns(two, "sales_by_year.xlsx")
try:
    require_unique_columns(manual, "manual header")
except ValueError as e:
    print(e)

Common pitfalls

• Trusting .1 suffixes. They depend on column order; a new column shifts which one is .1.
• Setting df.columns from a header row. No deduplication happens, and duplicate names break selection and assignment.
• to_dict with duplicates. It warns and keeps one value per name; data disappears.
• Invisible differences. Trailing spaces and case make near-duplicates look distinct; normalise before comparing.
• Blank headers. Unnamed: n names are positional too; give them real names. See Fix "Unnamed: 0" Columns When Reading Excel with pandas.

Conclusion

pandas' read_excel makes repeated headers unique with .1, .2 suffixes, which hides the problem rather than solving it, and manual header assignment creates true duplicates that break selection, assignment and to_dict. Report duplicates and near-duplicates from the raw header row, build meaningful names from multi-row headers or an explicit position mapping checked against the expected headers, and assert column uniqueness where data enters the pipeline.

Frequently asked questions

Why does pandas add .1 to my column names? The sheet has two columns with the same header. read_excel keeps both and appends .1, .2 to the repeats so every name is unique.

How do I read an Excel sheet with a two-row header? Pass header=0, 1 to read_excel to get a MultiIndex, forward-fill the top level across merged cells, and join the two levels into one name per column.

How do I find columns whose names differ only by spaces or case? Normalise each header by collapsing whitespace and applying casefold, then look for duplicates in the normalised names.

Why does df"Sales" return a DataFrame instead of a Series? The DataFrame has more than one column named Sales. Rename the columns so each name is unique.

Related

• Up one level: Troubleshooting Common Python Excel Errors — the other common errors.
• Fix "Unnamed: 0" Columns When Reading Excel with pandas — the blank-header case.
• Read Excel with Multi-Row Headers in pandas — deeper header hierarchies.
• List Sheet Names and Column Headers Without Loading Data — checking headers cheaply.
• Profile an Excel Dataset with pandas — a first look at a new file.