Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Concatenate Excel Sheets with Different Columns

Stack monthly exports whose columns drift — align headers, map renamed fields, keep a source column, and report what each file was missing before you combine them.

A monthly export is never quite the same file twice. One month a column is called revenue, the next it is Revenue, then somebody adds currency and drops notes. Stack them naively with pd.concat and you get a frame with revenue and Revenue as separate columns, each two-thirds empty, and totals that are silently wrong. This guide covers aligning the headers first, tracking where each row came from, and reporting the drift instead of absorbing it. It extends Merging and Joining Excel DataFrames.

Header drift across three monthly files, and what concat does with it Three monthly exports have slightly different headers: June uses revenue, July uses Revenue with a capital, August uses total_revenue and adds a currency column. Concatenating them directly produces three separate revenue columns each about a third populated, because concat matches column names as exact strings. Applying a rename map first collapses all three into one canonical revenue column, and currency simply appears with nulls for the months that lacked it. three monthly files June · region, revenue July · region, Revenue August · region, total_revenue, currency concat as-is revenue · Revenue · total_revenue · currency three columns, each two-thirds empty rename map, then concat region · revenue · currency · source_file one revenue column; currency null where absent

Prerequisites

Bash
pip install pandas openpyxl

Three months with realistic drift:

Python
from pathlib import Path
import pandas as pd

Path("monthly").mkdir(exist_ok=True)

pd.DataFrame({"region": ["North", "South"],
              "revenue": [5150.00, 4268.50]}).to_excel(
    "monthly/2026-06.xlsx", index=False)

pd.DataFrame({"Region": ["North", "West"],
              "Revenue": [4980.25, 3511.25]}).to_excel(
    "monthly/2026-07.xlsx", index=False)

pd.DataFrame({"region ": ["North", "South", "East"],
              "total_revenue": ["5,402.75", "3140.75", "2980.10"],
              "currency": ["EUR", "EUR", "EUR"]}).to_excel(
    "monthly/2026-08.xlsx", index=False)

Note the trailing space in August's region — that alone is enough to create a duplicate column.

Step 1 — See the drift before combining

Compare the headers across files first, so you know what you are dealing with:

Python
from pathlib import Path
import pandas as pd

def header_matrix(pattern="monthly/*.xlsx"):
    """Which columns appear in which files?"""
    headers = {}
    for path in sorted(Path().glob(pattern)):
        headers[path.stem] = list(pd.read_excel(path, nrows=0).columns)

    every = sorted({c for cols in headers.values() for c in cols})
    return pd.DataFrame(
        {name: [c in cols for c in every] for name, cols in headers.items()},
        index=every,
    )

matrix = header_matrix()
print(matrix.to_string())

Reading with nrows=0 fetches only the header, which makes this cheap even over dozens of large files. The output makes the problem obvious: Region, region and region each appear in exactly one file.

Step 2 — Normalise the header names

Most drift is cosmetic — case, spacing, punctuation. Handle it mechanically before anything else:

Python
import re

def normalise_header(name):
    """Canonical form for a column name: lowercase, underscore-separated."""
    text = str(name).strip().lower()
    text = re.sub(r"[^\w]+", "_", text)     # spaces and punctuation to _
    return re.sub(r"_+", "_", text).strip("_")

print(normalise_header("region "))        # region
print(normalise_header("Total Revenue"))  # total_revenue

That collapses the accidental variants. The genuine renames — total_revenue really meaning the same thing as revenue — need an explicit map, because no rule can infer them:

Python
RENAMES = {
    "total_revenue": "revenue",
    "sales_value": "revenue",
    "rev": "revenue",
    "area": "region",
    "region_name": "region",
}

def canonical_columns(df, renames=RENAMES):
    """Normalise header spelling, then apply the known rename map."""
    df = df.rename(columns=normalise_header)
    return df.rename(columns={k: v for k, v in renames.items()
                              if k in df.columns})

Keep the map in one place and treat adding to it as a deliberate act. That is what turns "the numbers went wrong in August" into "August introduced a new spelling, add one line".

Step 3 — Combine, tracking the source

Read, canonicalise, tag, concatenate:

Python
from pathlib import Path
import pandas as pd

def combine(pattern="monthly/*.xlsx", renames=RENAMES, required=("region",)):
    """Stack every matching file into one frame, aligned and tagged."""
    frames, seen = [], {}

    for path in sorted(Path().glob(pattern)):
        df = canonical_columns(pd.read_excel(path), renames)

        missing = set(required) - set(df.columns)
        if missing:
            raise ValueError(
                f"{path.name} is missing required column(s): "
                f"{', '.join(sorted(missing))}"
            )

        df["source_file"] = path.name
        seen[path.name] = set(df.columns)
        frames.append(df)

    if not frames:
        raise FileNotFoundError(f"no files matched {pattern}")

    combined = pd.concat(frames, ignore_index=True, sort=False)

    # Report columns that appear in only some files — usually a signal.
    everywhere = set.intersection(*seen.values())
    partial = sorted(set(combined.columns) - everywhere)
    if partial:
        print("columns present in only some files:", partial)
        for name in partial:
            files = [f for f, cols in seen.items() if name in cols]
            print(f"  {name:<16} {len(files)}/{len(seen)} file(s): "
                  f"{', '.join(files)}")

    return combined

combined = combine()
print(combined.head())

The source_file column is worth more than it looks. When a total is wrong, the first question is which file contributed the bad rows, and without a source column that question needs the whole pipeline re-run to answer.

ignore_index=True renumbers the result so the index is unique — without it, three files each starting at zero produce a frame with repeated index values, which then breaks loc lookups in confusing ways. And sort=False keeps the column order from the first frame rather than sorting alphabetically, which reads better in the output.

Step 4 — Reconcile the dtypes

concat aligns names, not types. A column read as float in two files and text in the third becomes object, and every subsequent sum concatenates strings:

One text column turns the whole combined column into object dtype June and July supply revenue as float64. August supplies it as text because the export wrote formatted strings. Concatenating produces an object column, so summing it concatenates strings rather than adding numbers and no error is raised. Coercing each file's column to a numeric type before combining keeps the result float64. June · revenue float64 July · revenue float64 August · revenue object combined revenue → object sum() concatenates strings · no error raised coerce before concat → float64 to_numeric on each file, then combine check combined.dtypes afterwards — an object column where you expect a number is the signal
Python
import pandas as pd

SCHEMA = {
    "region": "string",
    "revenue": "float64",
    "currency": "string",
}

def coerce(df, schema=SCHEMA):
    """Force each known column to its intended dtype before combining."""
    out = df.copy()
    for name, dtype in schema.items():
        if name not in out.columns:
            continue
        if dtype.startswith("float") or dtype.startswith("Int"):
            cleaned = out[name].astype("string").str.replace(
                r"[^\d.\-]", "", regex=True
            )
            out[name] = pd.to_numeric(cleaned, errors="coerce")
            if dtype.startswith("Int"):
                out[name] = out[name].astype(dtype)
        else:
            out[name] = out[name].astype(dtype)
    return out

Slot it into the reader and the combined frame comes out typed:

Python
df = coerce(canonical_columns(pd.read_excel(path)))

Then verify, because a silent object column is exactly the failure this is meant to prevent:

Python
for name, expected in SCHEMA.items():
    if name in combined.columns and str(combined[name].dtype) != expected:
        print(f"WARNING {name}: {combined[name].dtype}, expected {expected}")

The fuller text-to-number treatment is in converting Excel text columns to numbers.

Step 5 — Reconcile the row counts

The row count is the cheapest correctness check there is Three source files contributing forty, thirty-eight and forty-two rows should produce a combined frame of one hundred and twenty. If the combined count is lower, a file failed to read or a filter dropped rows. If it is higher, a file was included twice, which is what happens when a glob pattern matches both the original and a backup copy. Grouping the combined frame by its source column shows immediately which file is responsible. June · 40 rows July · 38 rows August · 42 rows = expect 120 fewer → a file failed to read exactly 120 → nothing lost more → a file was included twice

A combine should conserve rows exactly. Asserting it catches a file that failed to read and one that got read twice:

Python
from pathlib import Path
import pandas as pd

expected = sum(
    len(pd.read_excel(p)) for p in sorted(Path().glob("monthly/*.xlsx"))
)
assert len(combined) == expected, (
    f"combined has {len(combined)} rows, source files have {expected}"
)

print(combined.groupby("source_file").size().to_string())

Where each file also carries a total you can check against, comparing sums per source is stronger still — the reconciliation idea developed in comparing two Excel files for differences.

Common pitfalls and fixes

SymptomCauseFix
Two near-identical columnsCase or whitespace differsNormalise header names first.
A column is mostly emptyIt only exists in some filesReport partial columns; add a rename if it is the same field.
sum() concatenates stringsDtype collision fell back to objectCoerce each file before combining.
Repeated index valuesignore_index not setPass ignore_index=True.
Columns reordered alphabeticallyDefault sort=True behaviourPass sort=False.
Cannot tell which file a bad row came fromNo provenanceAdd a source_file column.
Combine is very slowAppending in a loopBuild a list and concat once.
A file silently contributed nothingRead failed or matched no rowsAssert the row counts reconcile.

Performance and scale notes

pd.concat allocates one new frame and copies each input into it — a single linear pass. Accumulating with repeated concatenation in a loop is quadratic, because every iteration copies everything gathered so far:

Python
import pandas as pd

# Wrong: copies the accumulated frame on every iteration.
combined = pd.DataFrame()
for path in paths:
    combined = pd.concat([combined, pd.read_excel(path)])

# Right: one allocation, one copy per input.
combined = pd.concat([pd.read_excel(p) for p in paths], ignore_index=True)

Three further habits. Read only the columns you need with usecols — after canonicalising you know the target names, so a wide export contributes only its relevant columns. Read the files in parallel, since parsing dominates and each file is independent:

Python
from concurrent.futures import ThreadPoolExecutor
from pathlib import Path
import pandas as pd

def read_one(path):
    df = coerce(canonical_columns(pd.read_excel(path)))
    df["source_file"] = path.name
    return df

paths = sorted(Path().glob("monthly/*.xlsx"))
with ThreadPoolExecutor(max_workers=4) as pool:
    combined = pd.concat(list(pool.map(read_one, paths)), ignore_index=True)

Threads work here because the Excel parsers release the GIL during I/O and much of the parsing; for very large files, processes are better still.

Convert the category-like columns after combining, not before. Making region a category in each frame separately gives each its own categories, and concatenating those falls back to object. Convert once on the result:

Python
combined["region"] = combined["region"].astype("category")

For a corpus too large to hold at once, write each canonicalised frame to Parquet and let a columnar reader handle the union — or convert the sources once, as described in combining multiple Excel files into one, so the expensive parse happens a single time.

Conclusion

Concatenating files with drifting headers is an alignment problem, not a stacking one. Compare the headers first so you know what changed, normalise the cosmetic variation mechanically, and keep an explicit rename map for the genuine renames that no rule can infer. Tag every row with its source file, coerce the dtypes before combining so a text column cannot poison the result, and report the columns that appear in only some files rather than absorbing them silently. Then assert the row counts reconcile — a combine that quietly dropped a file looks exactly like one that worked.

Frequently asked questions

What happens to columns that only appear in some files?pd.concat keeps every column it sees and fills the missing ones with NaN. That is usually what you want, but it means a typo in one file's header silently becomes a new mostly-empty column instead of an error.

How do I stop a renamed column becoming two columns? Apply an explicit rename map before concatenating, so revenue, Revenue and total_revenue all become one canonical name. Relying on concat to align them will not work — it matches on exact strings.

Should I use concat or append?concat. DataFrame.append was removed, and concatenating a list of frames in one call is far faster than repeatedly appending, which copies the whole accumulated frame each time.

How do I know which file a row came from? Add a source column before concatenating, or pass keys to pd.concat to build a hierarchical index. The source column is usually more convenient because it survives a reset_index and writes to Excel cleanly.

What if the same column has different dtypes across files?concat falls back to object dtype, which silently disables arithmetic. Coerce each column to its intended type before combining, and check dtypes on the result.