Merging and Joining Excel DataFrames with Pandas

When data lives in separate workbooks — sales in one, inventory in another — pandas.merge() joins them on a shared key the way a SQL join does. The hard parts are the keys: mismatched dtypes produce empty joins, duplicate keys multiply rows, overlapping column names collide, and outer joins inject NaN. This guide covers the patterns that survive a scheduled run. Every block is runnable and shares one namespace, so paste them in order.

This is one stage of Advanced Data Transformation and Cleaning. Merging is most reliable when it runs on data that has already been sanitized, so treat Cleaning Excel Data with Pandas as the step before this one — headers, whitespace, and types fixed first, then the join.

Create sample workbooks

These examples need two source files, so create both up front — a sales table and an inventory lookup keyed on sku. The sales data is deliberately messy (a trailing space, mixed casing, a repeated sku) so the normalization steps below have something real to fix:

import pandas as pd

sales = pd.DataFrame({
    "sku": ["a-100", "b-200 ", "C-300", "a-100"],
    "region": ["North", "South", "West", "East"],
    "units": [10, 5, 8, 3],
})
sales.to_excel("sales.xlsx", index=False)

inventory = pd.DataFrame({
    "sku": ["A-100", "B-200", "C-300"],
    "warehouse": ["W1", "W2", "W3"],
    "stock_level": [200, 150, 90],
})
inventory.to_excel("inventory.xlsx", index=False)

The merge pipeline

A reliable merge follows the same order every time. Skip a step and the failure shows up downstream — usually as a row count that is quietly wrong rather than an exception:

1. Load each workbook into its own DataFrame.
2. Normalize keys — strip whitespace, unify casing, match dtypes.
3. Choose the join — inner, left, right, or outer.
4. Validate cardinality with the validate argument.
5. Audit row counts and unmatched keys.
6. Export the result.

The two steps developers skip most often are normalization and validation — and those are exactly the two that fail silently. The patterns below each anchor on one part of this pipeline.

Pattern 1: Left join to enrich a master table

A left join keeps every row of the primary table and pulls matching attributes from the lookup. Normalize the key on both sides first, or rows that look equal ("a-100" vs "A-100") silently fail to match:

df_sales = pd.read_excel("sales.xlsx", engine="openpyxl", dtype={"sku": str})
df_inventory = pd.read_excel("inventory.xlsx", engine="openpyxl", dtype={"sku": str})

# Normalize the join key on both sides
df_sales["sku"] = df_sales["sku"].str.strip().str.upper()
df_inventory["sku"] = df_inventory["sku"].str.strip().str.upper()

merged = pd.merge(
    df_sales,
    df_inventory[["sku", "warehouse", "stock_level"]],
    on="sku",
    how="left",
    validate="m:1",   # many sales rows to one inventory row
)
print(merged)

validate="m:1" raises MergeError if the inventory side is not unique on sku, catching a duplicate-key bug before it doubles your rows. Reading the key with dtype={"sku": str} also matters: without it, a lookup keyed on numeric-looking codes like "00420" loses its leading zeros and stops matching. For the focused two-file recipe, see Merge Two Excel Files on Common Column Python.

Pattern 2: Join on multiple keys and mismatched names

Real reports rarely join on a single tidy column. Two situations come up constantly: the key is a combination of columns (a region plus a period, say), and the two files name the same key differently.

For a composite key, pass a list to on. Every listed column must match for a row to join:

q1 = pd.DataFrame({
    "region": ["North", "South", "North"],
    "period": ["2026-Q1", "2026-Q1", "2026-Q2"],
    "revenue": [1200, 900, 1500],
})
targets = pd.DataFrame({
    "region": ["North", "South", "North"],
    "period": ["2026-Q1", "2026-Q1", "2026-Q2"],
    "target": [1000, 1000, 1400],
})

by_period = pd.merge(q1, targets, on=["region", "period"], how="left")
by_period["variance"] = by_period["revenue"] - by_period["target"]
print(by_period)

When the columns hold the same values but carry different names, don't rename blindly — use left_on and right_on so the join is explicit, then drop the redundant duplicate key it leaves behind:

orders = pd.DataFrame({"Client_ID": [1, 2, 3], "amount": [100, 200, 300]})
accounts = pd.DataFrame({"Acct_No": [1, 2, 3], "tier": ["gold", "silver", "gold"]})

joined = pd.merge(
    orders, accounts,
    left_on="Client_ID", right_on="Acct_No",
    how="left",
)
joined = joined.drop(columns="Acct_No")   # keep one canonical key
print(joined)

Pattern 3: Resolve overlapping column suffixes

When both frames carry a non-key column of the same name, merge keeps both and disambiguates them with the _x / _y suffixes — one of the most confusing merge outputs to debug because nothing errors. Name the suffixes to say where each column came from, then decide which one is authoritative:

current = pd.DataFrame({"sku": ["A-100", "B-200"], "price": [9.5, 4.0]})
catalog = pd.DataFrame({"sku": ["A-100", "B-200"], "price": [9.5, 4.25]})

priced = pd.merge(
    current, catalog,
    on="sku", how="left",
    suffixes=("_live", "_catalog"),
)
# Flag rows where the two sources disagree, then keep the catalog price
priced["price_mismatch"] = priced["price_live"] != priced["price_catalog"]
priced["price"] = priced["price_catalog"]
print(priced[["sku", "price_live", "price_catalog", "price_mismatch", "price"]])

Explicit suffixes turn a silent overwrite risk into an auditable comparison — you can see exactly which rows diverged before collapsing them to one value.

Pattern 4: Reconcile divergent schemas

Sometimes the goal isn't a side-by-side join but stacking rows from two systems that describe the same entity with different column names. Rename each to a canonical schema, then combine with concat:

df_a = pd.DataFrame({"Client_ID": [1, 2], "Amount_USD": [100, 200]})
df_b = pd.DataFrame({"Acct_No": [3, 4], "Total_Value": [300, 400]})

column_mapping = {
    "Client_ID": "customer_id", "Acct_No": "customer_id",
    "Amount_USD": "amount", "Total_Value": "amount",
}
df_a = df_a.rename(columns={k: v for k, v in column_mapping.items() if k in df_a.columns})
df_b = df_b.rename(columns={k: v for k, v in column_mapping.items() if k in df_b.columns})

common = list(set(df_a.columns) & set(df_b.columns))
unified = pd.concat([df_a[common], df_b[common]], ignore_index=True)
print(unified)

Reach for concat when the frames share meaning and you want more rows; reach for merge when they share a key and you want more columns.

Pattern 5: Track matches with the indicator

For audits you often need to know which rows matched. indicator=True adds a _merge column tagging each row as both, left_only, or right_only:

result = pd.merge(
    df_sales, df_inventory,
    on="sku", how="left", indicator=True,
)
result["match_status"] = result["_merge"].map({
    "both": "matched",
    "left_only": "unmatched_primary",
    "right_only": "orphaned_secondary",
})
print(result[["sku", "region", "warehouse", "match_status"]])

The left_only rows are the ones worth surfacing in a report — sales for a sku that has no inventory record almost always signals a data-entry gap upstream. An outer join plus this indicator gives you both the matched output and the exception list in a single pass.

Common errors and fixes

Dtype mismatch on the join key

Symptom: a zero-row or all-NaN merge despite values that look identical. Cause: one side stores the key as text, the other as a number. Fix — cast both to the same type:

left = pd.DataFrame({"order_id": ["1", "2", "3"], "qty": [4, 5, 6]})
right = pd.DataFrame({"order_id": [1, 2, 3], "price": [9.0, 8.0, 7.0]})
left["order_id"] = pd.to_numeric(left["order_id"], errors="coerce").astype("Int64")
right["order_id"] = right["order_id"].astype("Int64")
print(pd.merge(left, right, on="order_id", how="inner"))

Duplicate keys multiply rows

Symptom: the output is larger than either input. Cause: duplicate keys on both sides produce a many-to-many join. Fix — deduplicate or aggregate the lookup, or pass validate= to fail fast:

dupes = pd.DataFrame({"key_col": ["x", "x", "y"], "revenue": [10, 20, 30]})
deduped = dupes.drop_duplicates(subset=["key_col"], keep="first")
aggregated = dupes.groupby("key_col", as_index=False).agg({"revenue": "sum"})
print(aggregated)

Aggregating is usually the safer choice: drop_duplicates throws away data, while groupby folds the duplicates into a defensible total. When a lookup legitimately has several rows per key, aggregate it to one row before joining.

NaN from outer/right joins

Symptom: numeric columns gain NaN for non-matching rows, breaking later math. Fix — fill the gaps deliberately after the merge:

numeric_cols = merged.select_dtypes(include="number").columns
merged[numeric_cols] = merged[numeric_cols].fillna(0)
print(merged.isna().sum().sum())

For a full treatment of post-merge gaps — including when 0 is the wrong fill and a forward-fill or a flagged sentinel is what you want — see Handling Missing Data in Excel Reports.

Export the consolidated result

Once the join is clean, write it back to a workbook. A merged, enriched table is exactly the shape you want to feed into a summary — see Creating Pivot Tables from Excel Data for the next step:

merged.to_excel("merged_report.xlsx", index=False, engine="openpyxl")
print("Wrote merged_report.xlsx")

Check the join before you trust it

A merge that runs without error can still be wrong in two directions: rows multiplied by a duplicate key, or rows silently unmatched. Both are one line to detect and invisible otherwise:

import pandas as pd

orders = pd.read_excel("orders.xlsx", sheet_name="Orders")
customers = pd.read_excel("customers.xlsx")

before = len(orders)
enriched = orders.merge(
    customers, on="Customer_ID", how="left", validate="m:1", indicator=True
)

matched = int((enriched["_merge"] == "both").sum())
print(f"rows {before} -> {len(enriched)}, matched {matched}/{before}")

unmatched = enriched.loc[enriched["_merge"] == "left_only", "Customer_ID"].unique()
if len(unmatched):
    print("unmatched keys:", list(unmatched)[:10])
enriched = enriched.drop(columns="_merge")

validate="m:1" turns a row explosion into an immediate MergeError — a duplicate on the reference side would otherwise multiply revenue silently. indicator=True adds the _merge column that shows which side each row came from, which is how a low match rate becomes visible instead of becoming a column of NaN that a later fillna(0) quietly absorbs.

Normalise the key on both sides

Most unmatched rows are a formatting difference rather than genuinely absent data. Building the join key deliberately, on both frames, removes the whole category:

def join_key(series):
    return (
        series.astype("string")
        .str.strip()
        .str.replace(r"\s+", " ", regex=True)
        .str.upper()
    )

orders["_key"] = join_key(orders["Customer_ID"])
customers["_key"] = join_key(customers["Customer_ID"])

joined = orders.merge(customers.drop(columns="Customer_ID"), on="_key", how="left")
joined = joined.drop(columns="_key")

Identifiers read as numbers are the other half of the problem: an account code of 00412 becomes 412 on one side and stays text on the other, and nothing matches. Reading identifier columns as strings on both sides — before any merge — is the fix, and it is worth asserting rather than hoping.

Suffixes, and columns that arrive twice

When both frames carry a column of the same name, pandas appends _x and _y — which is rarely what anyone wants in a delivered report:

merged = orders.merge(
    customers, on="Customer_ID", how="left", suffixes=("", "_customer")
)
print([c for c in merged.columns if c.endswith("_customer")])

An empty first suffix keeps the left frame's names unchanged and marks only the incoming duplicates, which makes the result readable and the intent obvious. Better still is to select the columns you want from the reference frame before merging — a join that brings in three columns is easier to reason about than one that brings in thirty and needs half of them dropped afterwards.

Choosing the join type deliberately

Four join types answer four different questions, and picking the wrong one is how rows quietly appear or vanish:

left is the default choice for a reporting pipeline because it guarantees the row count cannot grow beyond the transactions you started with, provided the reference side's key is unique. inner is the one to watch: it silently drops transactions whose reference row is missing, which turns a data-quality problem into a smaller, wrong total.

import pandas as pd

enriched = orders.merge(customers, on="Customer_ID", how="left", validate="m:1")
dropped_by_inner = len(orders) - len(orders.merge(customers, on="Customer_ID", how="inner"))
if dropped_by_inner:
    print(f"an inner join would have dropped {dropped_by_inner} order(s)")

Comparing the two counts before choosing is a cheap way to make the decision explicit — and the number it prints is exactly the figure to put in a data-quality note when it is not zero.

Concatenating is not joining

Stacking frames with pd.concat and joining them with merge solve different problems, and mixing them up produces either duplicated columns or duplicated rows. Concatenate when the frames have the same columns and represent more rows of the same thing — twelve monthly exports, say. Merge when the frames describe the same entities from different angles and share a key.

monthly = pd.concat([jan, feb, mar], ignore_index=True)      # more rows, same columns
enriched = monthly.merge(products, on="SKU", how="left")     # more columns, same rows

ignore_index=True matters on the concatenation: without it the result carries repeated index values from each source frame, which breaks loc lookups and produces confusing duplicates in any later groupby that uses the index.

Log the row count on both sides

Every merge should record what went in and what came out. A one-line log entry — rows before, rows after, match rate — makes a row explosion or a silent drop visible in the run log rather than in a total three stages later. It is the cheapest diagnostic in a reporting pipeline, and the first thing worth adding to a join that has ever surprised anyone.

Select before you join

Bringing three columns from a reference table produces a result you can read; bringing thirty produces one that needs half of them dropped. Selecting the columns you want before the merge keeps the output narrow and the intent obvious.

Fail where the cause is

The most useful place for a check is as close as possible to the thing that can go wrong: the sheet name at the read, the column list before the transform, the row count before the write, the file size before delivery. Each of those turns a confusing downstream error into a message naming the actual problem. Checks placed late still catch the failure, but they describe a symptom — and a symptom three stages from its cause is what makes a simple mistake take an afternoon.

Key takeaways

• A reliable merge rests on three decisions made before pd.merge() is called: normalize the join key on both sides, choose the join type that matches the business logic (most lookups are left), and pass validate= to catch cardinality problems early.
• Match dtypes on the key — a text "1" never joins to a numeric 1, and reading codes with dtype=str preserves leading zeros.
• Use a list in on= for composite keys, and left_on/right_on when the two files name the key differently.
• Name your suffixes to make overlapping columns auditable instead of silently overwritten.
• Deduplicate or aggregate a non-unique lookup before joining, and fill post-merge NaN deliberately rather than letting it propagate.

Frequently asked questions

Why does my merge return zero rows when the keys look identical? The keys probably differ in dtype or formatting — one side stores them as text, the other as numbers, or one has trailing whitespace or different casing. Cast both to the same type and normalize with .str.strip().str.upper() before merging.

What does validate="m:1" actually check? It asserts the relationship is many-to-one — many left rows to one unique right key — and raises MergeError if the right side has duplicate keys. This catches a duplicate-key bug before it silently doubles your rows.

Why is my merged output larger than either input? Duplicate keys on both sides produce a many-to-many join, emitting every combination. Deduplicate with drop_duplicates(subset=key) or aggregate the lookup with groupby before merging.

How do I merge on columns that have different names in each file? Use left_on and right_on instead of on — for example pd.merge(a, b, left_on="Client_ID", right_on="Acct_No"). Drop the now-redundant right key afterward, or rename one side to a shared name first so a single on= works.

How do I tell which rows matched and which didn't? Pass indicator=True to add a _merge column tagging each row as both, left_only, or right_only. You can map those tags to friendlier audit labels afterward.

My outer join introduced NaN that breaks later math — how do I fix it? Fill the gaps deliberately after the merge, for example merged[numeric_cols] = merged[numeric_cols].fillna(0) over the numeric columns. Decide the fill rather than letting NaN propagate silently.

Related

• Up to the parent: Advanced Data Transformation and Cleaning — the full pipeline this merge stage sits inside.
• Do this first: Cleaning Excel Data with Pandas — normalize headers and types so keys actually match.
• The focused recipe: Merge Two Excel Files on Common Column Python.
• Next step: Creating Pivot Tables from Excel Data — summarize the merged table.
• Clean up the joins: Handling Missing Data in Excel Reports — resolve the NaN an outer join introduces.