Fix "Unnamed: 0" Columns When Reading Excel with pandas

You read a workbook and the DataFrame arrives with columns you never created:

['Unnamed: 0', 'region', 'sales']

or, from a sheet someone built by hand, a scattering of them between the real headings:

['id', 'name', 'Unnamed: 2', 'amount', 'Unnamed: 4', 'Unnamed: 5', 'Unnamed: 6']

Nothing is broken. pandas.read_excel takes the header row literally, and whenever a header cell is empty it has to call the column something, so it uses Unnamed: plus the column's position. The fix depends on why the header cell is empty, and there are four common reasons. This guide, part of Troubleshooting Common Python Excel Errors, shows how to tell them apart and repair each one without losing data.

Prerequisites

pip install pandas openpyxl

The examples build their own small workbooks, so every block can be run as written:

import pandas as pd
from openpyxl import Workbook

df = pd.DataFrame({"region": ["North", "South"], "sales": [410, 388]})

Cause 1 — the file was written with its index

This is the most common source, and the one in the diagram above. DataFrame.to_excel() writes the index by default. An unnamed RangeIndex goes into column A with nothing in A1, so reading the file back produces an extra column:

df.to_excel("report.xlsx")                     # index=True is the default
print(pd.read_excel("report.xlsx").columns.tolist())
# ['Unnamed: 0', 'region', 'sales']

There are two fixes, and which one you want depends on whether the index meant anything. If it was just row numbers, stop writing it:

df.to_excel("report.xlsx", index=False)
print(pd.read_excel("report.xlsx").columns.tolist())
# ['region', 'sales']

If the index carried real labels — customer IDs, dates, a region key — keep writing it, and tell read_excel that column A is the index:

labelled = df.set_index("region")
labelled.to_excel("labelled.xlsx")
back = pd.read_excel("labelled.xlsx", index_col=0)
print(back.index.name, back.columns.tolist())
# region ['sales']

Naming the index before writing (df.index.name = "key") has a useful side effect: A1 is no longer empty, so even a reader who forgets index_col gets a column called key rather than Unnamed: 0. The same thing happens with CSV — to_csv also writes the index by default, and read_csv produces the same Unnamed: 0 — so the habit of passing index=False pays off in both formats. Write a Pandas DataFrame to Excel Without the Index covers the writing side in more detail.

Cause 2 — a blank header above real data

Hand-built sheets often have a column whose heading nobody typed: a notes column, a flag, a second amount column squeezed in next to the first. pandas cannot guess the name, so it numbers it:

wb = Workbook()
ws = wb.active
ws.append(["id", "name", None, "amount"])
ws.append([1, "Ada", "rush", 30])
ws.append([2, "Ben", None, 45])
wb.save("orders.xlsx")

orders = pd.read_excel("orders.xlsx")
print(orders.columns.tolist())
# ['id', 'name', 'Unnamed: 2', 'amount']

Column C holds data, so dropping it would lose the "rush" flag. Rename it instead. Because the generated name depends on the column's position, rename by position rather than by the Unnamed: 2 string — if someone inserts a column to the left next month, the number changes and a name-based rename silently stops matching:

cols = list(orders.columns)
cols[2] = "flag"
orders.columns = cols
print(orders.columns.tolist())
# ['id', 'name', 'flag', 'amount']

When the layout is fixed and agreed, the cleanest option is to ignore the file's header row altogether and supply your own names. header=0 says row 1 is a header (so it is skipped), and names= replaces it:

orders = pd.read_excel("orders.xlsx", header=0, names=["id", "name", "flag", "amount"])
print(orders.columns.tolist())

names= must list exactly as many names as the columns being read. If the sheet may grow extra columns on the right, combine it with usecols="A:D" so the count stays fixed.

Cause 3 — empty columns that Excel thinks are in use

The trailing Unnamed: 4, Unnamed: 5, Unnamed: 6 in the second example at the top are a different animal. Those columns are empty, but something in them — a fill colour, a border, a single space somebody typed and deleted the rest of — made Excel record them as part of the sheet's used range. openpyxl reports that range faithfully and pandas reads every column in it.

wb = Workbook()
ws = wb.active
ws.append(["id", "amount"])
ws.append([1, 30])
ws.append([2, 45])
from openpyxl.styles import PatternFill
ws["D3"].fill = PatternFill("solid", fgColor="FFFF00")   # formatting only
ws["E2"] = " "                                             # a stray space
wb.save("wide.xlsx")

wide = pd.read_excel("wide.xlsx")
print(wide.columns.tolist())
# ['id', 'amount', 'Unnamed: 2', 'Unnamed: 3', 'Unnamed: 4']

The quickest fix, when you know which columns matter, is not to read the others at all. usecols accepts a letter range, a list of names, or a function that receives each header and returns whether to keep it:

print(pd.read_excel("wide.xlsx", usecols="A:B").columns.tolist())
keep = pd.read_excel("wide.xlsx", usecols=lambda c: not str(c).startswith("Unnamed"))
print(keep.columns.tolist())
# ['id', 'amount']

The callable version is convenient but has the same danger as dropping by name: it also discards a real column with a blank heading. When the sheet can contain both kinds, drop only the columns that are genuinely empty. That needs one extra step, because dropna(axis=1, how="all") does not treat the stray space as missing:

print(wide.dropna(axis=1, how="all").columns.tolist())
# ['id', 'amount', 'Unnamed: 4']   <- the column with " " survives

text = wide.select_dtypes(include=["object", "string"]).columns
wide[text] = wide[text].apply(lambda s: s.str.strip().replace("", pd.NA))
print(wide.dropna(axis=1, how="all").columns.tolist())
# ['id', 'amount']

If the same workbook comes back every week, it is also worth fixing at the source: select the stray columns in Excel, clear them (Home → Clear → Clear All), and save. The used range shrinks and the phantom columns disappear for every reader, not just your script. Fix openpyxl max_row Counting Empty Rows explains the same used-range effect for rows.

Cause 4 — gaps in a multi-row header

Reports with grouped headings — "Sales" spanning Q1 and Q2, "Region" sitting above a blank — read with header=[0, 1] produce a MultiIndex, and every blank in the second header row gets a generated name with a _level_1 suffix:

wb = Workbook()
ws = wb.active
ws.append(["Region", None, "Sales", None])
ws.append([None, "Code", "Q1", "Q2"])
ws.append(["North", "N1", 410, 380])
ws.merge_cells("A1:B1")
ws.merge_cells("C1:D1")
wb.save("grouped.xlsx")

grouped = pd.read_excel("grouped.xlsx", header=[0, 1])
print(grouped.columns.tolist())
# [('Region', 'Unnamed: 0_level_1'), ('Region', 'Code'), ('Sales', 'Q1'), ('Sales', 'Q2')]

Flatten the two levels into one string per column and drop the generated part as you go:

def flat(col):
    parts = [str(p) for p in col if not str(p).startswith("Unnamed")]
    return " ".join(parts)

grouped.columns = [flat(c) for c in grouped.columns]
print(grouped.columns.tolist())
# ['Region', 'Region Code', 'Sales Q1', 'Sales Q2']

The full technique, including headers that span three rows, is in Read Excel with Multi-Row Headers in pandas.

Make the fix permanent in a reading function

When a script reads the same kind of file repeatedly, wrap the rules in one function so every caller gets the same, checked result. This one keeps real data, removes the phantoms, and fails loudly if a required column is missing:

REQUIRED = {"id", "amount"}

def read_clean(path, **kwargs):
    frame = pd.read_excel(path, **kwargs)
    text = frame.select_dtypes(include=["object", "string"]).columns
    frame[text] = frame[text].apply(lambda s: s.str.strip().replace("", pd.NA))
    frame = frame.dropna(axis=1, how="all")
    leftover = [c for c in frame.columns if str(c).startswith("Unnamed")]
    if leftover:
        raise ValueError(f"unnamed columns hold data: {leftover} — name them in the sheet or pass names=")
    missing = REQUIRED - set(frame.columns)
    if missing:
        raise ValueError(f"missing columns: {sorted(missing)}")
    return frame

print(read_clean("wide.xlsx").columns.tolist())
# ['id', 'amount']

Raising on leftover unnamed columns is deliberate. A column of data under a blank heading is a question for the person who owns the sheet, and a clear error message gets it asked, where a silent drop would have removed the data from every report downstream.

Common pitfalls

• Renaming by the generated name. rename(columns={"Unnamed: 2": "flag"}) works until a column is inserted to its left. Rename by position, or better, get the heading typed into the sheet.
• Treating index_col=0 as a universal fix. It turns column A into the index whatever it holds. On a sheet where column A is real data with a heading, that moves the data out of the columns.
• Forgetting index=False on the next write. Reading with index_col=0, modifying, and writing with the default index=True round-trips correctly — but reading without index_col and writing with the index adds a second unnamed column each cycle.
• Stray whitespace. A single space keeps a column alive through dropna. Strip first.
• CSV exports of the same data. to_csv has the same default; read_csv produces the same Unnamed: 0. The fixes are identical.

Performance notes

None of these fixes costs anything noticeable on normal sheets. The one worth thinking about on large files is usecols: it is applied while parsing, so columns you exclude are never converted into Python objects, which saves memory as well as a clean-up step. Dropping columns after reading means pandas first built them in full. For a used range that stretches to column XFD because of a formatted whole row, the difference is large — see Reduce Memory When Reading Excel with pandas.

Conclusion

Unnamed: is pandas telling you a header cell was empty. A saved index in column A wants index_col=0 or index=False; a blank heading over real data wants a name, given by position or with names=; formatted empty columns want usecols or an all-empty drop after stripping text; and _level_1 names come from gaps in a multi-row header and want flattening. Put the rules in one reading function that refuses to drop columns holding data, and the phantom columns stop reaching your reports.

Frequently asked questions

Why does pandas add an "Unnamed: 0" column when I read my own file back? The file was written with to_excel() and the default index=True, so column A holds the old index under an empty header cell. Read it back with index_col=0, or write it with index=False in the first place.

Is it safe to drop every column whose name starts with "Unnamed"? Only after checking what is in them. A blank header above real data is common in hand-built sheets, and dropping by name throws that data away. Drop columns that are completely empty, and rename the ones that hold values.

Why does dropna(axis=1, how="all") leave some Unnamed columns behind? Those columns contain something that is not NaN — usually a cell holding a single space or an empty string typed by someone. Strip strings and turn empty text into NaN first, then drop the all-empty columns.

What does "Unnamed: 0_level_1" mean? It comes from a multi-row header read with header=0, 1. The second header row had a blank cell under a merged or filled first-row label, so pandas invented a name for that level. Rename the column tuples or flatten the header after reading.

Related

• Up one level: Troubleshooting Common Python Excel Errors — the other errors that interrupt reading and writing.
• Skip Rows and Set the Header When Reading Excel with pandas — when the header is not on row 1 at all.
• Read Specific Columns From Excel With Pandas — usecols in all its forms.
• Standardise Excel Column Names with pandas — the next step once every column has a name.