Python Doc & Data Automation

Fix Unnamed Columns in pandas read_excel

The frame loads, and none of the columns have usable names:

>>> pd.read_excel("data/monthly_report.xlsx").columns
Index(['Monthly Sales Report', 'Unnamed: 1', 'Unnamed: 2', 'Unnamed: 3'], dtype='object')

Or half of them are named and half are not:

Index(['Region', 'Unnamed: 1', 'Q1', 'Unnamed: 3', 'Q2', 'Unnamed: 5'], dtype='object')

Unnamed: N is pandas telling you that cell N of the row it used as the header was blank. It is never a parsing failure — it is a disagreement about which row the header is.

Root Cause

read_excel takes row 0 of the sheet as the header. Human-facing workbooks rarely put it there. A title banner occupies row 1, a blank spacer row follows, the real column names sit in row 3, and the data starts in row 4. pandas dutifully uses the banner: one populated cell becomes a column name and every other cell, being blank, becomes Unnamed: N.

The half-named variant has a different cause: merged cells. A two-row header where Q1 spans two columns stores the text in the top-left cell of the merge and leaves its neighbour empty, so the neighbour arrives as Unnamed. Empty spacer columns used for visual separation produce the same artefact.

Why pandas picks the wrong header row A worksheet has a title banner in row one, a blank spacer in row two, the real column names in row three and data from row four. pandas reads row one as the header by default, so the single populated title cell becomes the first column name and the blank cells beside it become Unnamed 1, Unnamed 2 and Unnamed 3. Passing header equals two, the zero based index of the real header row, fixes it. The worksheet Monthly Sales Report 1 2 Region Units Value Share 3 EMEA 1204 98,400 41% 4 default: header=0 'Monthly Sales Report', 'Unnamed: 1', 'Unnamed: 2' header=2 'Region', 'Units', 'Value', 'Share' header= takes a zero-based row index, so the third row is header=2

Minimal Diagnostic

Read the top of the sheet with no header at all and look at it. Nothing else answers the question faster.

# pip install "pandas>=2.2,<3" "openpyxl>=3.1,<4"
from pathlib import Path
import pandas as pd

XLSX = Path("data/monthly_report.xlsx")

def peek(xlsx_path: Path, sheet=0, rows: int = 8) -> pd.DataFrame:
    """Show the first rows raw, with no header interpretation at all."""
    if not xlsx_path.exists():
        raise FileNotFoundError(f"No such workbook: {xlsx_path}")
    try:
        return pd.read_excel(xlsx_path, sheet_name=sheet, header=None, nrows=rows)
    except Exception as exc:
        raise RuntimeError(f"Could not read {xlsx_path}: {exc}") from exc

def guess_header_row(raw: pd.DataFrame, min_filled: float = 0.6) -> int:
    """First row where most cells are non-empty text — the likely header."""
    for index, row in raw.iterrows():
        filled = row.notna().mean()
        textual = row.dropna().map(lambda v: isinstance(v, str)).mean() if row.notna().any() else 0
        if filled >= min_filled and textual >= 0.8:
            return int(index)
    raise ValueError("No plausible header row found in the sampled rows")

if __name__ == "__main__":
    raw = peek(XLSX)
    print(raw)
    print("likely header row (0-based):", guess_header_row(raw))

                      0      1       2      3
0  Monthly Sales Report    NaN     NaN    NaN
1                   NaN    NaN     NaN    NaN
2                Region  Units   Value  Share
3                  EMEA   1204   98400   0.41
likely header row (0-based): 2

The heuristic is "mostly filled and mostly text", which distinguishes a header from both a banner (one cell) and a data row (mixed types). Use it to find the row during exploration, then hard-code the number in production so a layout change fails loudly instead of guessing differently.

The Fix

# pip install "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd

def read_report(xlsx_path: Path, sheet=0, header_row: int = 2) -> pd.DataFrame:
    """Read with the real header row, then drop spacer columns and blank rows."""
    frame = pd.read_excel(xlsx_path, sheet_name=sheet, header=header_row)

    # Spacer columns arrive as 'Unnamed: N' and are entirely empty
    unnamed = [c for c in frame.columns if str(c).startswith("Unnamed:")]
    empty_unnamed = [c for c in unnamed if frame[c].isna().all()]
    frame = frame.drop(columns=empty_unnamed)

    frame = frame.dropna(how="all")                       # blank separator rows
    frame.columns = [str(c).strip() for c in frame.columns]
    return frame.reset_index(drop=True)

if __name__ == "__main__":
    df = read_report(Path("data/monthly_report.xlsx"))
    print(df.columns.tolist())
    print(df.head())

Dropping only the Unnamed columns that are entirely empty is the important restraint. An Unnamed column that contains data is a real column whose header cell was blank — deleting it silently loses a column of values, which is far worse than an ugly name. Rename those instead.

skiprows is the alternative spelling of the same fix and is better when the rows above the header are irregular: pd.read_excel(path, skiprows=2) discards them and then treats the next row as the header.

Variant Fix 1: Two-Row Merged Headers

A header split across two rows — a group name above, a metric below — reads as a MultiIndex when both rows are passed. Flattening it produces usable names:

# pip install "pandas>=2.2,<3"
import pandas as pd

def flatten_header(xlsx_path: str, sheet=0, header_rows=(2, 3)) -> pd.DataFrame:
    """Read a two-row header and join the levels into single column names."""
    frame = pd.read_excel(xlsx_path, sheet_name=sheet, header=list(header_rows))
    flat = []
    for upper, lower in frame.columns:
        upper = "" if str(upper).startswith("Unnamed:") else str(upper).strip()
        lower = "" if str(lower).startswith("Unnamed:") else str(lower).strip()
        flat.append("_".join(part for part in (upper, lower) if part) or "column")
    frame.columns = flat
    return frame

if __name__ == "__main__":
    print(flatten_header("data/quarterly.xlsx").columns.tolist())
    # ['Region', 'Q1_units', 'Q1_value', 'Q2_units', 'Q2_value']

Merged group cells leave the second and later columns of the merge blank in the upper row, so Q1 appears once and its neighbour is Unnamed. Forward-filling the upper level before joining restores the grouping:

# pip install "pandas>=2.2,<3"
upper = pd.Series([c[0] for c in frame.columns]).replace(r"^Unnamed.*", pd.NA, regex=True).ffill()

Variant Fix 2: Rename Rather Than Drop

When an Unnamed column holds data, give it a name derived from its content or position:

# pip install "pandas>=2.2,<3"
import pandas as pd

def name_unnamed(frame: pd.DataFrame) -> pd.DataFrame:
    """Replace Unnamed headers with positional names, keeping the data."""
    renamed = {}
    for position, column in enumerate(frame.columns):
        if str(column).startswith("Unnamed:"):
            if frame[column].isna().all():
                continue                        # handled by the drop step instead
            renamed[column] = f"column_{position}"
    return frame.rename(columns=renamed)

Then log the rename. A column that needed a synthetic name is a signal that the sheet layout drifted, and the log entry is what turns a silent shift into a ticket — the general discipline described in scheduling and logging automation jobs.

Variant Fix 3: Different Layout per Sheet

Multi-sheet workbooks rarely agree on where the header sits. Detect per sheet rather than passing one number for all:

# pip install "pandas>=2.2,<3" "openpyxl>=3.1,<4"
from pathlib import Path
import pandas as pd

def read_all_sheets(xlsx_path: Path) -> dict[str, pd.DataFrame]:
    """Read every sheet, finding each one's own header row."""
    frames = {}
    book = pd.ExcelFile(xlsx_path)
    for name in book.sheet_names:
        raw = pd.read_excel(book, sheet_name=name, header=None, nrows=10)
        try:
            header_row = guess_header_row(raw)
        except ValueError:
            print(f"skipping {name}: no header row found")
            continue
        frames[name] = pd.read_excel(book, sheet_name=name, header=header_row)
    return frames

Combining those frames afterwards is where mismatched column names bite; the alignment rules are in fix pandas concat columns misaligned, and the wider workflow in merging multiple spreadsheets.

What to do with each Unnamed column Starting from an Unnamed column, the first test asks whether the column is entirely empty. If it is, it is a spacer and can be dropped. If it is not, the column holds data and must be renamed rather than dropped. A second branch asks whether the header spans two rows, in which case a list of header rows is passed and the levels are joined, with the upper level forward filled across merged cells. Unnamed: N blank header cell Column all empty? YES Spacer — drop it visual separator, no data NO Real column — rename it dropping it loses values

Verification

Assert the schema after reading. A header-row change upstream should fail the job, not reshape the data silently.

# pip install "pandas>=2.2,<3"
import pandas as pd

EXPECTED = ["Region", "Units", "Value", "Share"]

def assert_schema(frame: pd.DataFrame, expected: list[str], allow_extra: bool = False) -> None:
    """Fail on unnamed leftovers, missing columns or an unexpected order."""
    unnamed = [c for c in frame.columns if str(c).startswith("Unnamed:")]
    assert not unnamed, f"unnamed column(s) survived: {unnamed}"

    missing = [c for c in expected if c not in frame.columns]
    assert not missing, f"missing column(s): {missing} — header row may have moved"

    if not allow_extra:
        extra = [c for c in frame.columns if c not in expected]
        assert not extra, f"unexpected column(s): {extra}"

    assert len(frame) > 0, "no data rows below the header — header row may be too low"
    print(f"schema OK: {len(frame):,} row(s), columns {list(frame.columns)}")

if __name__ == "__main__":
    assert_schema(read_report(Path("data/monthly_report.xlsx")), EXPECTED)

The empty-frame assertion catches the opposite mistake to the one this page opens with: a header value one row too low consumes the first data row as the header, producing sensible-looking column names made of the first record's values. Checking that data survives is what distinguishes the two.

The opposite mistake: a header row one too low With header set to two the column names are Region, Units and Value and all four data rows survive. With header set to three the first data row EMEA, 1204 and 98400 is consumed as the column names, leaving three rows and headings that look like data. The row count is the tell: a correct header choice never reduces the data by exactly one row. header=2 — correct header=3 — one too low Region | Units | Value EMEA | 1204 | 98400 AMER | 980 | 74210 4 data rows kept EMEA | 1204 | 98400 AMER | 980 | 74210 APAC | 655 | 51002 3 rows — one silently became the header

FAQ

Should I use header= or skiprows=?header=N when the rows above are junk you want gone and the header is at a known index. skiprows=N when those rows are irregular, or when you also want to drop rows below the header. They can be combined, in which case header is counted after the skip.

Why does Unnamed: 0 appear on a CSV I exported from pandas? That is a different problem — the DataFrame index was written as an unnamed first column. Export with index=False; the details are in fix pandas to_csv extra index column.

Can pandas find the header row automatically? No, and that is deliberate — a wrong guess is worse than a required argument. The heuristic above is fine for exploration; pin the number for production.

My header row is correct but names have trailing spaces. Excel headers frequently carry trailing whitespace and non-breaking spaces. Normalise with frame.columns = frame.columns.str.replace(" ", " ").str.strip() immediately after reading, before any lookup by name.

How do I keep merged group headers as a MultiIndex? Pass header=[2, 3] and simply do not flatten. A MultiIndex is genuinely useful for frame["Q1"] style access, at the cost of more awkward merges and exports later.

Part of Reading Excel Files with Python.