Python Doc & Data Automation

Fix pandas concat Columns Misaligned

Twelve monthly workbooks, one combined frame, and twice as many columns as expected:

>>> combined = pd.concat(frames, ignore_index=True)
>>> combined.columns.tolist()
['Region', 'region', 'Units', 'units ', 'Value', 'Total Value', 'Share']
>>> combined["Region"].isna().sum()
8400        # every row from the files that spelled it 'region'

Nothing has been lost — the values are all present — but they are spread across near-duplicate columns, so every aggregate is wrong and every filter misses most of the data.

Root Cause

pd.concat aligns on exact column labels. "Region", "region" and "Region " are three different labels, so three columns appear and each row populates only the one its source file used. The same applies to a genuine rename between periods: "Value" in January and "Total Value" in February are unrelated as far as alignment is concerned.

The default join="outer" keeps every column from every frame and fills the gaps with NaN. That is the correct behaviour for a general-purpose concatenation and the wrong outcome for a set of files that are supposed to share a schema — which is why the fix is to normalise the schemas before concatenating, not to change how concat joins.

Alignment is by exact label On the left, frame one has columns Region and Units while frame two has region and units with a trailing space. Concatenating them produces four columns, each only half populated, with NaN in the other half. On the right, both frames have their headers normalised to lower case and stripped, so concatenation produces two fully populated columns. Without normalising After normalising Region Units region units Region Units region units EMEA 1204 NaN NaN NaN NaN AMER 980 region units region units region units EMEA 1204 AMER 980 concat never merges near-duplicate labels — normalise the headers before combining Case, trailing spaces and non-breaking spaces are the three usual offenders

Minimal Diagnostic

Compare the schemas across the files before combining anything.

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

FOLDER = Path("data/monthly")

def schema_report(folder: Path, pattern: str = "*.xlsx") -> pd.DataFrame:
    """List which columns appear in which files, so drift is visible at a glance."""
    files = sorted(folder.glob(pattern))
    if not files:
        raise FileNotFoundError(f"No files matching {pattern} in {folder}")

    per_file = {}
    counts: Counter[str] = Counter()
    for path in files:
        try:
            columns = list(pd.read_excel(path, nrows=0).columns)
        except Exception as exc:
            raise RuntimeError(f"Could not read {path.name}: {exc}") from exc
        per_file[path.name] = columns
        counts.update(columns)

    rows = []
    for column, seen in counts.most_common():
        rows.append({
            "column": repr(column),          # repr exposes trailing spaces
            "files_with_it": seen,
            "of_total": len(files),
            "missing_from": [n for n, cols in per_file.items() if column not in cols][:3],
        })
    return pd.DataFrame(rows)

if __name__ == "__main__":
    print(schema_report(FOLDER).to_string(index=False))

     column  files_with_it  of_total        missing_from
   'Region'              9        12  [mar.xlsx, apr.xlsx, may.xlsx]
   'region'              3        12  [jan.xlsx, feb.xlsx, jun.xlsx]
    'Units'             12        12  []
   'units '              0        12  []

repr on the column name is what makes a trailing space visible; printed plainly, 'units ' and 'units' look identical and the report is useless.

The Fix: Normalise, Then Concatenate

Give every frame the same header vocabulary before combining, and record which file each row came from.

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

CANONICAL = {
    "region": "region",
    "area": "region",              # known historical alias
    "units": "units",
    "qty": "units",
    "value": "value",
    "total value": "value",
    "share": "share",
}

def normalise_columns(frame: pd.DataFrame) -> pd.DataFrame:
    """Lower-case, strip, collapse whitespace, then map known aliases."""
    cleaned = (
        pd.Index(frame.columns)
        .astype(str)
        .str.replace(" ", " ", regex=False)   # non-breaking space from Excel
        .str.strip()
        .str.lower()
        .str.replace(r"\s+", " ", regex=True)
    )
    frame = frame.copy()
    frame.columns = [CANONICAL.get(name, name) for name in cleaned]
    return frame

def combine(folder: Path, pattern: str = "*.xlsx") -> pd.DataFrame:
    """Read and concatenate a folder of workbooks with aligned schemas."""
    frames = []
    for path in sorted(folder.glob(pattern)):
        frame = normalise_columns(pd.read_excel(path))
        frame["source_file"] = path.name        # provenance for every row
        frames.append(frame)
    if not frames:
        raise FileNotFoundError(f"No files matching {pattern} in {folder}")
    return pd.concat(frames, ignore_index=True)

if __name__ == "__main__":
    combined = combine(Path("data/monthly"))
    print(combined.columns.tolist())
    print(f"{len(combined):,} row(s) from {combined['source_file'].nunique()} file(s)")

The alias map is the part that must be maintained deliberately. An unmapped new spelling still creates its own column, which is exactly right: it should be visible and require a decision, not be guessed at by fuzzy matching that might merge two genuinely different measures.

source_file costs one column and makes every later question answerable — which file introduced the outlier, which period is missing, which workbook had the alias.

Variant Fix 1: Enforce the Schema with an Inner Join

When only the shared columns matter, join="inner" keeps the intersection and drops the rest:

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

combined = pd.concat(frames, join="inner", ignore_index=True)

Use it with care. Silently dropping a column present in eleven of twelve files loses real data, so pair it with a report of what was discarded:

# pip install "pandas>=2.2,<3"
all_columns = set().union(*(set(f.columns) for f in frames))
shared = set.intersection(*(set(f.columns) for f in frames))
dropped = sorted(all_columns - shared)
if dropped:
    print(f"inner join dropped: {dropped}")

Variant Fix 2: Reindex Each Frame to a Fixed Schema

The strictest version — declare the schema once and force every frame into it. Missing columns appear as NaN, extra columns raise.

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

SCHEMA = ["region", "units", "value", "share"]

def conform(frame: pd.DataFrame, schema: list[str], source: str) -> pd.DataFrame:
    """Force a frame into the declared schema, reporting anything unexpected."""
    extra = [c for c in frame.columns if c not in schema and c != "source_file"]
    if extra:
        raise ValueError(f"{source}: unexpected column(s) {extra} — update the schema or the alias map")
    missing = [c for c in schema if c not in frame.columns]
    if missing:
        print(f"warning: {source} is missing {missing}")
    return frame.reindex(columns=schema)      # order fixed, gaps filled with NaN

if __name__ == "__main__":
    conformed = [conform(normalise_columns(f), SCHEMA, name) for name, f in loaded.items()]
    combined = pd.concat(conformed, ignore_index=True)

Raising on unexpected columns turns schema drift into a build failure rather than a silent widening. The declarative version of this idea — types and ranges as well as names — is validating document data with schemas.

Variant Fix 3: Misalignment Caused by the Index, Not the Columns

ignore_index=True matters more than it looks. Without it, concatenating frames that each start at index 0 produces repeated index values, and any later join, reindex or loc behaves unpredictably:

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

bad = pd.concat(frames)                      # index: 0,1,2,0,1,2,0,1,2 …
good = pd.concat(frames, ignore_index=True)  # index: 0,1,2,3,4,5,6,7,8 …
print(bad.index.is_unique, good.index.is_unique)

If a header row was itself misread, the columns will be wrong before concat ever runs — that failure is diagnosed in fix unnamed columns in pandas read_excel.

How strict should the merge be Three strategies compared. Normalising names keeps every column and merges only the spellings that map to the same canonical name, which suits exploratory work. An inner join keeps only columns present in every file and drops the rest, which suits reporting on a stable core. Reindexing to a declared schema fixes the column set and order and raises on anything unexpected, which suits scheduled pipelines. Pick the strictness the job needs Normalise names keeps every column merges known aliases new names still appear for exploration join="inner" intersection only drops the rest quietly log what was dropped for a stable core reindex to schema fixed set and order raises on the unexpected gaps become NaN for scheduled jobs All three assume normalised header text first — case and whitespace defeat every one of them

Verification

Assert on the combined frame: no near-duplicate labels, no column that is mostly empty, and a row count that adds up.

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

def assert_aligned(combined: pd.DataFrame, sources: dict[str, int],
                   max_null_pct: float = 20.0) -> None:
    """Fail on duplicate-ish columns, sparse columns, or a lost row."""
    normalised = [str(c).strip().lower() for c in combined.columns]
    duplicates = {c for c in normalised if normalised.count(c) > 1}
    assert not duplicates, f"near-duplicate column(s) survived: {sorted(duplicates)}"

    expected_rows = sum(sources.values())
    assert len(combined) == expected_rows, (
        f"row count {len(combined):,} != sum of sources {expected_rows:,}"
    )

    sparse = {
        column: round(combined[column].isna().mean() * 100, 1)
        for column in combined.columns
        if combined[column].isna().mean() * 100 > max_null_pct
    }
    assert not sparse, f"column(s) mostly empty — alignment likely failed: {sparse}"
    print(f"{len(combined):,} row(s), {len(combined.columns)} column(s), all aligned")

if __name__ == "__main__":
    assert_aligned(combined, {"jan.xlsx": 700, "feb.xlsx": 680})

The sparse-column check is the one that catches partial misalignment — the case where nine files agree, three do not, and the resulting column is 25% empty. A row-count check alone passes that scenario happily.

Null share is the fingerprint of misalignment Null percentage per column after concatenating twelve files. Units and value are zero percent null because every file spelled them the same way. Region is twenty five percent null and region lower case is seventy five percent null, together accounting for one hundred percent, which is the signature of one column split across two spellings. Any column above about twenty percent null deserves investigation. Null share per column after concat 20% warning line units 0% Region 25% region 75% Two columns whose null shares sum to 100% are one column with two spellings

FAQ

Should I use concat or merge here?concat stacks rows from files with the same shape; merge joins columns on a key. Twelve monthly extracts of the same report are a concat. Combining a customer list with an order list is a merge — and its own overlapping-column trap is covered in fix pandas merge overlapping columns.

Is fuzzy matching of column names a good idea? No. value and net value are similar strings and different measures. An explicit alias map fails loudly on a new name, which is what you want; fuzzy matching fails silently and plausibly.

Why do dtypes change after concatenating? Combining an int64 column with one containing NaN promotes to float64, and combining category with object produces object. Set dtypes after the concat, or use nullable Int64 throughout.

How do I keep the original file order? Sort the file list explicitly — sorted(folder.glob(...)) is lexicographic, so jan, feb, mar sorts as apr, aug, dec. Sort by a parsed date from the filename when order matters.

Can I concatenate without loading every file at once? Yes: write each normalised frame to a Parquet dataset directory and read the directory back as one frame, or append to a single CSV. The streaming techniques in handling large CSV files with chunking apply directly.

Catching Drift at the Source

The alias map fixes today's spellings; it does nothing about the next one. A short weekly report closes that gap: read only the header row of each new file, compare the set against the canonical schema, and print anything unrecognised.

It costs milliseconds per file and it surfaces a rename the week it happens rather than the month a total looks wrong. Where the files come from a system you can influence, that report is also the evidence needed to ask for the header to be stabilised, which is the only fix that removes the problem rather than absorbing it.

Part of Merging Multiple Spreadsheets.