Merge Excel Data on Multiple Key Columns with pandas

Real lookups are rarely on one clean ID. A price depends on the region, the store and the product; a target on the rep and the month; a stock level on the warehouse and the item. In Excel this is a VLOOKUP on a helper column that concatenates the keys, or an INDEX/MATCH with several conditions. In pandas it is a merge on a list of columns — which works at once when the two files agree on how every key is written, and silently fails to match when they do not. This page, part of Merging and Joining Excel DataFrames with Pandas, merges on a composite key and deals with each way it goes wrong.

Prerequisites

pip install pandas openpyxl

sales.xlsx has six sales lines keyed by region, store number and product. prices.xlsx has store-specific prices — but written by another system: the column names are capitalised, store numbers are zero-padded text ("007"), and one region has a trailing space. list_prices.xlsx has one list price per product.

Step 1 — Merge on a list of columns

When the key columns have the same names in both files, pass them as a list to on. When the names differ, left_on and right_on take lists in matching order:

import pandas as pd

sales = pd.read_excel("sales.xlsx")
prices = pd.read_excel("prices.xlsx", dtype={"Store": str})

try:
    sales.merge(prices, left_on=["region", "store", "product"],
                right_on=["Region", "Store", "Product"], how="left")
except ValueError as exc:
    print("ValueError:", str(exc).split(".")[0])

pandas refuses: You are trying to merge on int64 and str columns for key 'store'. That is the helpful case — a type mismatch stops the merge. Two text columns that differ only in formatting, such as "South" and "South ", merge without any error and simply fail to match, which is why the keys need checking before the merge rather than after.

Step 2 — Diagnose the keys that do not match

Compare the keys column by column — values and types. A small helper lists, for each key, the values that appear on one side only:

def key_report(left, right, left_keys, right_keys):
    for lk, rk in zip(left_keys, right_keys):
        lv, rv = set(left[lk].map(repr)), set(right[rk].map(repr))
        print(f"{lk}/{rk}: dtypes {left[lk].dtype}/{right[rk].dtype}; "
              f"left only {sorted(lv - rv)[:4]}; right only {sorted(rv - lv)[:4]}")

key_report(sales, prices, ["region", "store", "product"], ["Region", "Store", "Product"])

The report shows the problems at once: the store column is an integer on one side (7) and zero-padded text on the other ('007'), and the region 'South ' has a trailing space. Using repr makes invisible differences — spaces, types, quotes — visible. Every multi-column merge that matches fewer rows than expected is worth this thirty-second check.

Step 3 — Normalise every key the same way on both sides

Write one function that brings the key columns into a canonical form, and apply it to both frames. Text keys are stripped and case-folded; numeric-looking codes are converted to integers so that 7 and "007" agree:

def normalise_keys(frame, mapping):
    out = frame.rename(columns=mapping).copy()
    for col in ["region", "product"]:
        out[col] = out[col].astype(str).str.strip().str.casefold()
    out["store"] = pd.to_numeric(out["store"], errors="raise").astype(int)
    return out

sales_k = normalise_keys(sales, {})
prices_k = normalise_keys(prices, {"Region": "region", "Store": "store", "Product": "product"})

merged = sales_k.merge(prices_k, on=["region", "store", "product"], how="left", indicator=True)
print(len(sales_k), "sales ->", len(merged), "rows after the merge")
print(merged.to_string(index=False))

The keys now match — and the result has seven rows for six sales. The North–7–Widgets sale appears twice, once at 2.50 and once at 2.55, because the price list has two rows for that combination. Nothing warned about it; a total over units * unit_price would count those 40 units twice. errors="raise" in the store conversion is deliberate, by contrast: a store code that is not a number should stop the job, not become a key that quietly fails to match.

Step 4 — Stop duplicate keys from multiplying rows

validate="many_to_one" checks that the right-hand table is unique on the key and raises if it is not — turning the silent doubling from Step 3 into an error:

try:
    sales_k.merge(prices_k, on=["region", "store", "product"], how="left", validate="many_to_one")
except pd.errors.MergeError as exc:
    print("MergeError:", str(exc).splitlines()[0])

dupes = prices_k[prices_k.duplicated(["region", "store", "product"], keep=False)]
print(dupes.to_string(index=False))

The error names the problem and the second print shows the offending rows, so the price list's owner can decide which price is right. Resolving it is a business decision, not a technical one; here, assume the list is in date order and the last entry for a key is current:

prices_u = prices_k.drop_duplicates(["region", "store", "product"], keep="last")
merged = sales_k.merge(prices_u, on=["region", "store", "product"], how="left",
                       validate="many_to_one", indicator=True)
print(len(merged), "rows;", merged["unit_price"].notna().sum(), "with a store price")

Six rows for six sales, four of them with a store-specific price. Use validate on every lookup merge; it costs almost nothing. Validate a pandas Merge to Avoid Duplicate Rows covers the other validation modes.

Step 5 — Fall back to a less specific key

Two sales still have no store-specific price: East–5–Gizmos and North–7–Gadgets. When the business rule is "use the store price if there is one, otherwise the product's list price", merge the list prices on the shorter key and fill the gaps:

lists = pd.read_excel("list_prices.xlsx")
lists["product"] = lists["product"].str.strip().str.casefold()

priced = merged.drop(columns="_merge").merge(lists, on="product", how="left", validate="many_to_one")
priced["price_used"] = priced["unit_price"].fillna(priced["list_price"])
priced["price_source"] = priced["unit_price"].notna().map({True: "store", False: "list"})
priced["value"] = priced["units"] * priced["price_used"]
print(priced[["region", "store", "product", "units", "price_used", "price_source", "value"]].to_string(index=False))

Every sale now has a price, and the price_source column records which rule supplied it — important when someone questions a figure. The same pattern extends to more levels (store, then region, then product) by chaining fillna from progressively shorter keys, most specific first.

Write the result with its audit columns

Keep the key columns, the price used and its source in the output, so the workbook can be checked by anyone without re-running the code:

priced.to_excel("sales_priced.xlsx", index=False,
                columns=["region", "store", "product", "units", "price_used", "price_source", "value"])
print(pd.read_excel("sales_priced.xlsx")["price_source"].value_counts().to_dict())

Four prices came from the store list and two from the product list — a count worth printing in the job log, because a sudden rise in fallbacks usually means the store price list was not updated this month.

Common pitfalls

• Mixed key types. An integer key never equals a text key, and pandas refuses to merge them; convert both sides to the same type before merging.
• Invisible whitespace and case. Strip and case-fold text keys on both sides.
• Duplicate right-hand keys. Pass validate="many_to_one" so extra rows raise instead of doubling totals.
• Checking the result, not the keys. When matches are missing, compare the key columns directly.
• Fallbacks without a record. Always store which rule supplied a looked-up value.
• Resolving duplicates silently. drop_duplicates(keep="last") is a business rule; log how many duplicate keys were dropped so the price list gets fixed at the source.

Performance notes

Multi-column merges hash the combined key and are as fast as single-column ones for typical report sizes — millions of rows in seconds. Normalising keys adds one vectorised pass per column. If the same large table is merged repeatedly, normalise it once and cache the result rather than cleaning keys on every run.

Conclusion

Merge on several columns with on=[...], or left_on and right_on lists when names differ. When rows fail to match, compare each key column's values and types directly, then normalise every key identically on both sides — strip and case-fold text, convert codes to one type. Guard lookups with validate="many_to_one", fall back to less specific keys with chained merges and fillna, and keep a column that records where each looked-up value came from.

Frequently asked questions

How do I merge on more than one column in pandas? Pass a list: pd.merge(left, right, on="region", "store", "product"). When the column names differ between the files, use left_on=... and right_on=... with the columns in matching order.

Why do rows not match when the values look the same? Usually because of type or formatting differences in one of the key columns: 7 as a number in one file and "007" as text in the other, or "South " with a trailing space. Normalise every key column the same way on both sides before merging.

Why does the merge return more rows than the left table had? The right table has more than one row for some key combination, so each left row matches several right rows. Pass validate="many_to_one" to make pandas raise instead, and remove or resolve the duplicates.

How do I fall back to a less specific key? Merge on the full key first, then fill the rows that did not match from a second merge on fewer columns, for example a product-level list price when no store-specific price exists.

Related

• Up one level: Merging and Joining Excel DataFrames with Pandas — every kind of join.
• Merge Two Excel Files on a Common Column in Python — the single-key case.
• Find Rows in One Excel File Missing from Another — listing the keys with no partner.
• Standardise Category Values with a Mapping Table in pandas — normalising key values that differ in wording.