Reorder Columns to Match a Template Layout with pandas

An import template for a finance system expects exactly twelve columns in exactly this order. The monthly files that feed it come from four branches whose sheets have the same information — mostly — in four different orders, with one branch calling the column "Cust No" instead of "Customer ID" and another adding a "Notes" column nobody asked for. Pasting them into the template by hand is how columns get shifted by one and a month of invoices gets posted against the wrong accounts. pandas can enforce the layout: read the expected columns from the template, rename the variants, reorder with reindex, and decide explicitly what to do about anything missing or extra. This page belongs to Cleaning Excel Data with pandas.

Prerequisites

pip install pandas openpyxl

import_template.xlsx is the target: a title in row 1 and a formatted header in row 3 with eight columns. branch_north.xlsx has the same data in a different order, a lower-case invoice date, Cust No instead of Customer ID, no Currency column and an extra Notes column.

Step 1 — Read the required layout from the template

Do not copy the column list into the script — read it from the template, so the code follows the template when it changes:

import pandas as pd
from openpyxl import load_workbook

tpl_ws = load_workbook("import_template.xlsx")["Import"]
HEADER_ROW = 3
template_cols = [c.value for c in tpl_ws[HEADER_ROW] if c.value is not None]
print(template_cols)

branch = pd.read_excel("branch_north.xlsx")
print(list(branch.columns))

The template wants Invoice No, Invoice Date, Customer ID, …, Currency; the branch file has its own order and names. A single source of truth for the layout matters more than it seems: when finance adds a column to the template next quarter, the script picks it up — and the missing-column check below tells you which branch files need updating.

Step 2 — Map header variants to the template names

Normalise case and spacing first, then map known aliases:

ALIASES = {"cust no": "Customer ID", "customer no": "Customer ID", "inv no": "Invoice No",
           "date": "Invoice Date", "ccy": "Currency"}
canon = {c.lower(): c for c in template_cols}

def to_template_name(col):
    key = " ".join(str(col).split()).lower()
    return canon.get(key) or ALIASES.get(key) or col

branch = branch.rename(columns=to_template_name)
print(list(branch.columns))

invoice date matches Invoice Date by case-insensitive comparison; Cust No becomes Customer ID through the alias table; Notes is left as it is, because it has no counterpart. Keeping aliases in one dictionary turns each new branch spelling into a one-line change. The same normalisation idea is covered for all columns in Standardise Excel Column Names with pandas.

Step 3 — Reorder with reindex, and decide about missing columns

reindex(columns=...) returns the columns in the given order. Columns that do not exist are created and filled with NaN — convenient, and dangerous if a required column is silently empty. Check first:

missing = [c for c in template_cols if c not in branch.columns]
extra = [c for c in branch.columns if c not in template_cols]
print("missing:", missing, "| extra:", extra)

DEFAULTS = {"Currency": "EUR"}
unfixable = [c for c in missing if c not in DEFAULTS]
if unfixable:
    raise ValueError(f"branch_north.xlsx lacks required columns: {unfixable}")
aligned = branch.reindex(columns=template_cols)
for col, value in DEFAULTS.items():
    aligned[col] = aligned[col].fillna(value)
print(aligned)

Currency is missing, but the business rule says branch files are always in euros, so it gets a documented default; any other missing column stops the run with a message naming it. Notes is dropped by the reindex because it is not in the template. Whether extras should be dropped, kept at the end, or treated as an error depends on the receiving system; for an import that rejects unknown columns, dropping is right — but log it, so a column that should have mapped (a new spelling of "Customer ID", say) does not vanish unnoticed.

The two kinds of mismatch each need a policy, chosen per column rather than once for the whole file:

Writing the policy down as data — a DEFAULTS dictionary, a list of columns allowed to be missing — keeps it reviewable by the people who own the import format, who rarely read Python but can read a table.

Step 4 — Verify the layout before writing

A final assertion costs one line and catches regressions in the mapping code:

assert list(aligned.columns) == template_cols, "column order does not match the template"
problems = aligned[template_cols].isna().sum()
print(problems[problems > 0].to_dict() or "no empty required values")

The order check guarantees nothing was shifted; the per-column count of missing values shows whether a column that exists is actually filled. Deeper checks — types, value ranges, allowed codes — belong in validation; see Validate Excel Columns Before Import with pandas.

Step 5 — Write the data into the template itself

Writing the aligned frame to a new file with to_excel would lose the template's title and formatted header. Load the template with openpyxl and write the values under its header row instead:

import datetime as dt

book = load_workbook("import_template.xlsx")
sheet = book["Import"]
for r, row in enumerate(aligned.itertuples(index=False), start=HEADER_ROW + 1):
    for c, value in enumerate(row, start=1):
        if isinstance(value, pd.Timestamp):
            value = value.to_pydatetime()
        cell = sheet.cell(r, c, None if pd.isna(value) else value)
        if isinstance(value, dt.datetime):
            cell.number_format = "dd/mm/yyyy"
        elif isinstance(value, float):
            cell.number_format = "#,##0.00"
book.save("import_north_2026-09.xlsx")
check = pd.read_excel("import_north_2026-09.xlsx", header=HEADER_ROW - 1)
print(list(check.columns) == template_cols, len(check), "rows")

The title, the indigo header and the column widths stay exactly as the template's owner made them; the data starts on row 4. Timestamps are converted to Python datetimes and given a date format, and amounts a number format, because openpyxl writes raw values. Reading the result back with header=2 (the zero-based header row) confirms the layout end to end. Filling templates in general is covered in Populate an Excel Template Without Losing Formatting.

Step 6 — Process every branch the same way

Wrap the steps in a function and run it over all incoming files, collecting problems instead of stopping at the first:

def align_to_template(path):
    df = pd.read_excel(path).rename(columns=to_template_name)
    missing = [c for c in template_cols if c not in df.columns and c not in DEFAULTS]
    if missing:
        return None, f"{path}: missing {missing}"
    out = df.reindex(columns=template_cols)
    for col, value in DEFAULTS.items():
        out[col] = out[col].fillna(value)
    dropped = [c for c in df.columns if c not in template_cols]
    return out, f"{path}: ok" + (f", dropped {dropped}" if dropped else "")

pd.DataFrame({"Invoice No": ["S-9"], "Net": [10.0]}).to_excel("branch_south.xlsx", index=False)
results = [align_to_template(p) for p in ("branch_north.xlsx", "branch_south.xlsx")]
for _, message in results:
    print(message)
combined = pd.concat([df for df, _ in results if df is not None], ignore_index=True)
print(combined.shape)

North aligns, with Notes reported as dropped; South is rejected with the list of columns it lacks; the combined import contains only valid files. Sending the messages back to the branches is what eventually makes their files consistent — see Send an Alert When an Excel Report Job Fails.

Common pitfalls

• Relying on reindex to catch missing columns. It fills them with NaN silently; check explicitly.
• Hard-coding the column list. Read it from the template so changes propagate.
• Dropping extras without logging. A misspelled required column looks like an extra; report what was dropped.
• Overwriting the template's formatting with to_excel. Write values into the loaded template instead.
• Position-based pasting. Matching by name is what makes the result independent of each branch's order.

Conclusion

Read the required column order from the template, map header variants to the template's names with a normalised lookup and an alias table, and reorder with reindex(columns=template_cols) — after checking for missing columns, filling only those with documented defaults, and logging any extras that are dropped. Assert the final order, then write the values below the template's own header with openpyxl so its formatting survives, and run the same function over every incoming file, collecting problems rather than stopping.

Frequently asked questions

How do I reorder DataFrame columns to match a list? Use df.reindex(columns=template_cols) or dftemplate_cols. reindex adds any missing columns filled with NaN, while dftemplate_cols raises a KeyError, so check for missing columns first if you use reindex.

How do I handle columns that are named differently in each file? Normalise header text (trim, single spaces, lower case) and map it to the template names through a dictionary of known aliases with df.rename(columns=function) before reordering.

How do I write data into an Excel template without losing its header formatting? Load the template with openpyxl, write the values cell by cell starting below the header row, apply number formats, and save under a new name. to_excel would replace the sheet's layout.

What should happen to extra columns that are not in the template? For strict import formats, drop them with reindex, but log their names so a misspelled required column is noticed. If the receiver accepts extras, append them after the template columns instead.

Related

• Up one level: Cleaning Excel Data with pandas — tidying data before it moves on.
• Merge Excel Files with Different Column Names — combining files whose headers disagree.
• Concatenate Excel Sheets with Different Columns — stacking when layouts differ.
• Fill an Excel Template with Python and openpyxl — template filling in depth.