Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Merge Excel Files with Different Column Names in pandas

Two systems export the same customers under different headers — "Customer No" and "cust_id", "Email" and "email_address" — with keys formatted differently. Map both files to one schema, normalise the keys, merge on several columns and combine the overlapping fields with a clear rule for which source wins.
Joins, lookups & comparisonsMany sheets & many filespandas

A CRM export and an ERP export describe the same customers. The CRM calls the key "Customer No" and writes it as 00123; the ERP calls it cust_id and stores 123. The country is "DE" in one file and "de" in the other, and both files carry an email address under different headers. merge cannot guess that these columns belong together, and when it is forced to — with left_on and right_on — the result has two of everything. This guide, part of Merging and Joining Excel DataFrames, maps both files to one schema, fixes the key formats, merges on two columns and resolves the fields both files provide.

Two sets of headers mapped onto one schema The CRM columns Customer No, Country, Name and Email and the ERP columns cust_id, country_code, customer_name, email_address and balance are each renamed to the shared names customer_id, country, name, email and balance before the files are merged. Shared schema customer_id, country, name, email CRM export Customer No, Country, Name, Email ERP export cust_id, country_code, customer_name, email_address

Prerequisites

Bash
pip install pandas openpyxl

The two exports:

Python
import pandas as pd

pd.DataFrame({
    "Customer No": ["00123", "00456", "00789"],
    "Country": ["DE", "FR", "UK"],
    "Name": ["Acme GmbH", "Birch SA", "Cedar Ltd"],
    "Email": ["a@x.de", None, "c@x.uk"],
}).to_excel("crm.xlsx", index=False)
pd.DataFrame({
    "cust_id": [123, 456, 999],
    "country_code": ["de", "FR", "US"],
    "customer_name": ["ACME GMBH", "Birch S.A.", "Dune Inc"],
    "email_address": ["acct@x.de", "b@x.fr", None],
    "balance": [1200, 300, 50],
}).to_excel("erp.xlsx", index=False)

Step 1 — Read keys as text

Read both files with the key columns as strings. Without that, pandas infers types: the CRM's "00123" text cells come back as the integer 123 — the leading zeros are simply gone — and if one file stays text while the other is numeric, merge refuses with ValueError: You are trying to merge on str and int64 columns:

Python
crm = pd.read_excel("crm.xlsx", dtype={"Customer No": str})
erp = pd.read_excel("erp.xlsx", dtype={"cust_id": str})
print(crm["Customer No"].tolist(), erp["cust_id"].tolist())
# ['00123', '00456', '00789'] ['123', '456', '999']

Reading as text keeps the values exactly as the sheet shows them, so you decide how to normalise them rather than letting type inference decide. It also protects IDs that only look numeric, such as account codes with a letter in some rows, from being split into numbers and text within one column.

Step 2 — Map both files to one schema

left_on and right_on handle a single differently named key, but here four fields differ. Rename each file to a shared set of names instead, with one mapping per source kept in one place:

Python
SCHEMA = {
    "crm": {"Customer No": "customer_id", "Country": "country", "Name": "name", "Email": "email"},
    "erp": {"cust_id": "customer_id", "country_code": "country", "customer_name": "name",
            "email_address": "email", "balance": "balance"},
}

def prepare(frame, source):
    mapping = SCHEMA[source]
    missing = set(mapping) - set(frame.columns)
    if missing:
        raise ValueError(f"{source}: missing columns {sorted(missing)}")
    return frame.rename(columns=mapping)[list(mapping.values())]

Selecting only the mapped columns drops anything unexpected, and the check turns a renamed column in next month's export into a clear error. Cleaning messy headers before mapping — spaces, case, symbols — is covered in Standardise Excel Column Names with pandas.

Step 3 — Normalise the key values

Matching names is half the job; the values must match too. Strip spaces, remove leading zeros from the IDs and upper-case the country codes, the same way on both sides:

Python
def normalise_keys(frame):
    out = frame.copy()
    out["customer_id"] = out["customer_id"].str.strip().str.lstrip("0")
    out["country"] = out["country"].str.strip().str.upper()
    return out

a = normalise_keys(prepare(crm, "crm"))
b = normalise_keys(prepare(erp, "erp"))
print(a["customer_id"].tolist(), b["customer_id"].tolist())
# ['123', '456', '789'] ['123', '456', '999']

Removing zeros is the right choice when the numeric value is the identity. If 0123 and 123 can be different customers, pad both sides to a fixed width with str.zfill(5) instead. Either way, apply one function to both files, so the rule cannot drift between them.

Normalising the composite key on both sides The CRM keys 00123 with DE and 00456 with FR and the ERP keys 123 with de and 456 with FR differ in leading zeros and case. After stripping zeros and upper-casing the country, both sides read 123 DE and 456 FR and can be matched. A B C 1 source customer_id country 2 CRM 00123 DE 3 CRM 00456 FR 4 ERP 123 de 5 ERP 456 FR A B C 1 source customer_id country 2 CRM 123 DE 3 CRM 456 FR 4 ERP 123 DE 5 ERP 456 FR before after normalise_keys

Step 4 — Merge on the composite key

The same customer number can exist in two countries, so the key is the pair. Pass both columns to on, keep all customers from both files with an outer join, and label the overlapping fields with suffixes:

Python
merged = a.merge(b, on=["customer_id", "country"], how="outer",
                 suffixes=("_crm", "_erp"), validate="one_to_one", indicator=True)
print(merged[["customer_id", "country", "name_crm", "name_erp", "_merge"]].values.tolist())

validate="one_to_one" fails if either file lists a customer twice, and indicator=True shows that 789 exists only in the CRM and 999 only in the ERP. Those are exactly the records someone should look at, and they are covered in more depth in Find Rows in One Excel File Missing from Another. Why validation matters on every merge is explained in Validate a pandas Merge to Avoid Duplicate Rows.

Step 5 — Combine the fields both files provide

Both files have a name and an email. Decide, per field, which source is authoritative, fill its gaps from the other with combine_first, and report real disagreements:

Python
PREFER = {"name": "erp", "email": "crm"}
for field, source in PREFER.items():
    other = "erp" if source == "crm" else "crm"
    merged[field] = merged[f"{field}_{source}"].combine_first(merged[f"{field}_{other}"])

conflict = (merged["email_crm"].notna() & merged["email_erp"].notna()
            & (merged["email_crm"] != merged["email_erp"]))
print(merged.loc[conflict, ["customer_id", "email_crm", "email_erp"]].values.tolist())
# [['123', 'a@x.de', 'acct@x.de']]
result = merged[["customer_id", "country", "name", "email", "balance", "_merge"]]
print(result)

The ERP is the legal record for names, so its "ACME GMBH" wins; the CRM is maintained by the sales team, so its email wins where it has one, and Birch's missing CRM email is filled from the ERP. Customer 123 has two different emails — not necessarily an error, since one may be an accounts address, but worth a line in a review sheet rather than a silent choice.

Deciding the value of a field both files provide When only one file has a value, use it. When both have the same value, use either. When both have different values, take the preferred source's value and list the row in a conflict report. When neither has a value, leave the field empty. Which files have a value for this field? only one use it combine_first fills gaps both, equal use either no action both, different preferred source + conflict report neither leave empty flag if required

Step 6 — More than two sources

With a third export — a web shop, say — chaining merges multiplies the suffixes: email_crm, email_erp, email_shop. A tidier pattern is to stack the prepared sources with a source column and pick each field's value by priority:

Python
stacked = pd.concat([a.assign(source="crm"), b.assign(source="erp")], ignore_index=True)
rank = {"crm": 0, "erp": 1}                     # lower rank wins
best = (stacked.assign(rank=stacked["source"].map(rank))
               .sort_values("rank")
               .groupby(["customer_id", "country"], as_index=False)
               .first())
print(best[["customer_id", "email"]].values.tolist())
# [['123', 'a@x.de'], ['456', 'b@x.fr'], ['789', 'c@x.uk'], ['999', nan]]

groupby().first() takes the first non-missing value per column, so after sorting by rank each field comes from the highest-priority source that has it. Adding a source means one more mapping in SCHEMA and one entry in rank. For per-field priorities, as in Step 5, run the same pattern once per field with its own ranking.

Step 7 — Write the combined file with its review sheets

Write the merged customers alongside the conflicts and the one-sided records, so the people who own each system can act on them:

Python
with pd.ExcelWriter("customers_combined.xlsx") as writer:
    result.drop(columns="_merge").to_excel(writer, sheet_name="Customers", index=False)
    merged.loc[conflict, ["customer_id", "country", "email_crm", "email_erp"]].to_excel(
        writer, sheet_name="Email conflicts", index=False)
    merged.loc[merged["_merge"] != "both", ["customer_id", "country", "_merge"]].to_excel(
        writer, sheet_name="Only in one system", index=False)
print(pd.ExcelFile("customers_combined.xlsx").sheet_names)

Remember that the key columns were read as text: if the output must show 00123 again, pad with str.zfill before writing, and the cells will stay text in Excel.

Common pitfalls

  • Merging before normalising. Keys that differ only by zeros, spaces or case never match, and the merge reports them as one-sided rather than failing.
  • Stripping zeros from a zero. "0".lstrip("0") is an empty string, which then matches nothing, or matches every other empty key. If an ID of 0 is possible, use str.lstrip("0").replace("", "0") or convert to integers after validating that every key is numeric.
  • Positional renaming. Assigning df.columns = [...] breaks as soon as a column is added. Rename by name with a mapping.
  • Unlabelled overlaps. Without suffixes, pandas adds _x and _y, and nobody remembers which file was which. Name the sources.

Conclusion

Merging files that name the same fields differently is a mapping problem before it is a merge problem. Read keys as text, rename each file to one shared schema, normalise key values with one function applied to both sides, and merge on the full composite key with validation and an indicator. Resolve fields both files provide with an explicit source priority, and hand the conflicts and one-sided records back to the people who own the data.

Frequently asked questions

How do I merge two DataFrames when the key columns have different names? Either pass left_on and right_on with the two names, or rename one or both frames to a shared name first. Renaming is clearer when several columns differ, because the merged frame then has one name per field.

Why does pandas refuse to merge on str and int64 columns? One file stores the key as text, the other as a number, and pandas will not guess how to compare them. Read both keys as text with dtype=str and normalise them the same way before merging.

How do I keep leading zeros in an ID column from Excel? Pass dtype={"column": str} to read_excel. Without it, pandas converts text such as "00123" to the number 123 and the zeros are gone.

How do I combine two columns that hold the same field from different files? Merge with suffixes so both survive, then use combine_first on the preferred column to fill its gaps from the other. Report rows where both have values that disagree.

More on joins, lookups & comparisons

More on joins, lookups & comparisons →