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

Merge on Keys That Differ in Case and Spacing with pandas

A merge that should match every row matches half of them because one file says "SKU-001 " and the other "sku-001", or an ID has lost its leading zeros, or an invisible non-breaking space hides in a cell. Build a normalised join key on both sides, merge on it while keeping the original values, detect keys that collapse into collisions, and report what still does not match.
Joins, lookups & comparisonsData cleaning

The orders file and the product master both have a product code. orders.merge(products, on="SKU") should match every order, and matches about half. Look at the unmatched rows and the codes look identical — until you notice that one side has "SKU-001 " with a trailing space, the other "sku-001" in lower case; one export dropped the leading zeros from numeric IDs; and one cell copied from a web page contains a non-breaking space that looks exactly like a normal one. merge compares keys exactly, character for character, so every one of these is a non-match. The fix is a normalised join key on both sides, used for matching only, with the original values kept for display. This page belongs to Merging and Joining Excel DataFrames.

Prerequisites

Bash
pip install pandas openpyxl

merge_inputs.xlsx has an Orders sheet, a Products master and a Customers master. The order SKUs carry every common defect — trailing space, lower case, a non-breaking space, a zero-width space at the end, a space instead of a hyphen — plus one genuinely unknown code. The customer numbers sometimes lost their leading zeros. And the product master has a trap of its own: Sku-006 and SKU-006 are two rows that differ only in case.

Step 1 — See how much an exact merge misses

Python
import pandas as pd

book = pd.read_excel("merge_inputs.xlsx", sheet_name=None, dtype=str)
orders, products, customers = book["Orders"], book["Products"], book["Customers"]
exact = orders.merge(products, on="SKU", how="left", indicator=True)
print(exact[["OrderID", "SKU", "Product", "_merge"]].assign(SKU=exact["SKU"].map(repr)).to_string(index=False))
print(exact["_merge"].value_counts().to_dict())

Reading everything as text (dtype=str) prevents pandas from turning 00412 into the number 412 before you get a chance to decide. indicator=True adds a _merge column saying whether each row matched. Printing the keys with repr reveals what the eye cannot: 'SKU\xa0003' contains a non-breaking space and 'SKU-004​' a zero-width space. None of the seven orders matched — not even SKU-001, whose trailing space is enough to make it different.

Step 2 — Build a normalised key

Python
import re
import unicodedata

def norm_key(value):
    s = unicodedata.normalize("NFKC", str(value))
    s = s.replace("​", "").replace("", "")       # zero-width space, BOM
    s = re.sub(r"[\s\-_]+", "", s)                           # spaces (incl. NBSP after NFKC), hyphens, underscores
    return s.casefold()

for raw in ["SKU-001 ", "sku-002", "SKU 003", "SKU-004​", "SKU 005"]:
    print(repr(raw), "->", norm_key(raw))

Each step removes one class of difference. NFKC normalisation turns lookalike characters — the non-breaking space, full-width letters pasted from some systems — into their plain forms. The zero-width characters are removed explicitly because no normalisation touches them. Spaces, hyphens and underscores are dropped entirely, so SKU 005, SKU-005 and SKU_005 agree. casefold is a stronger lower that also handles characters such as the German ß. The rule must be the same function on both sides — that is what makes the keys comparable.

Five spellings normalised to one key format On the left five order SKUs with a trailing space, lower case, a non-breaking space, a zero-width space and a space instead of a hyphen. On the right their normalised keys sku001 to sku005, all in the same format. raw 'SKU-001 ' 'sku-002' 'SKU\xa0003' 'SKU-004\u200b' 'SKU 005' key sku001 sku002 sku003 sku004 sku005 raw SKU (repr) join key norm_key()

Step 3 — Check for collisions before merging

Normalising can make two different master records identical. In the product master, Sku-006 (an old record) and SKU-006 both become sku006. Merging on that key would duplicate the matching order. Find such collisions first:

Python
products["Key"] = products["SKU"].map(norm_key)
collisions = products[products["Key"].duplicated(keep=False)].sort_values("Key")
print(collisions.to_string(index=False))

The master has two rows for sku006, and they disagree on the product name. That is a data-quality problem for the owner of the master, not something a script should guess at. Resolve it — here by keeping the current record — and enforce uniqueness on every later run with validate:

Python
products_clean = products[products["SKU"] != "Sku-006"]
orders["Key"] = orders["SKU"].map(norm_key)
joined = orders.merge(products_clean[["Key", "Product"]], on="Key", how="left",
                      validate="many_to_one", indicator=True)
print(joined[["OrderID", "SKU", "Product", "_merge"]].to_string(index=False))

validate="many_to_one" raises a MergeError if the right-hand keys are not unique, so a new collision in next month's master stops the job instead of silently doubling orders. Now six of the seven orders match; the original SKU column is untouched for display. More on merge validation in Validate a Merge to Avoid Duplicate Rows.

What normalising can break Normalising matches order keys that differed in case and spacing. But it can also make two different master rows identical — Sku-006 and SKU-006 both become sku006 — which would duplicate matching orders unless the master is de-duplicated and the merge validated. Fixed by normalising trailing spaces case differences NBSP and zero-width spaces space vs hyphen Created by normalising Sku-006 and SKU-006 collide one order would match twice caught by validate="many_to_one"

Step 4 — Numeric IDs that lost their leading zeros

Customer numbers are a different kind of key: 412, 00412 and 0412 should match, but 412 and 4120 must not. Pad every ID to the master's width:

Python
width = customers["CustomerNo"].str.len().max()
customers["Key"] = customers["CustomerNo"].str.strip().str.zfill(width)
orders["CustKey"] = orders["CustomerNo"].str.strip().str.zfill(width)
with_customers = joined.merge(customers[["Key", "Customer"]].rename(columns={"Key": "CustKey"}),
                              left_on=orders["CustKey"], right_on="CustKey", how="left")
print(with_customers[["OrderID", "CustomerNo", "Customer"]].to_string(index=False))

zfill pads with zeros on the left to the master's five digits, so 412 becomes 00412 and matches Müller GmbH. Order 7's customer 2001 pads to 02001 and matches Oakridge Farms. Use this only for keys that are genuinely numeric with lost padding; applying it to alphanumeric codes would invent matches. Leading zeros on the way in are covered in Keep Leading Zeros When Reading Excel with pandas.

Step 5 — Report what still does not match

Whatever remains unmatched after normalisation is a real difference, and the people who own the data need to see it:

Python
unmatched = joined[joined["_merge"] == "left_only"][["OrderID", "SKU", "Qty"]]
print(unmatched.to_string(index=False))
summary = joined["_merge"].map({"both": "matched", "left_only": "unmatched"}).value_counts()
print(summary.to_dict())

Order 7 references SKU-099, which is not in the master in any spelling — a new product that has not been set up, or a typo. Listing it, rather than dropping it, keeps the totals honest; the full pattern for finding rows missing from another file is in Find Rows in One Excel File Missing from Another.

Orders matched to the product master An exact merge matched none of the seven orders. After normalising keys and resolving the duplicate master record, six of seven matched; the one remaining order references a product code that does not exist. exact merge 0 of 7 normalised keys 6 of 7 still unmatched 1 (SKU-099)

Step 6 — Keep the rule in one reusable function

Every report that joins on these codes needs the same rule, and a rule copied into five scripts drifts. Wrap normalisation, the collision check, the merge and the report into one function:

Python
def normalised_merge(left, right, on, key_func=norm_key, how="left"):
    left = left.assign(_key=left[on].map(key_func))
    right = right.assign(_key=right[on].map(key_func))
    dupes = right[right["_key"].duplicated(keep=False)]
    if not dupes.empty:
        raise ValueError(f"lookup keys collide after normalising: {sorted(dupes[on])}")
    merged = left.merge(right.drop(columns=on), on="_key", how=how,
                        validate="many_to_one", indicator=True)
    unmatched = merged.loc[merged["_merge"] == "left_only", on].tolist()
    return merged.drop(columns=["_key", "_merge"]), unmatched

try:
    normalised_merge(orders[["OrderID", "SKU", "Qty"]], products[["SKU", "Product"]], on="SKU")
except ValueError as exc:
    print("ValueError:", exc)
result, missing = normalised_merge(orders[["OrderID", "SKU", "Qty"]], products_clean[["SKU", "Product"]], on="SKU")
print(result.to_string(index=False))
print("unmatched:", missing)

Against the raw master the function refuses to run and names the colliding codes; against the cleaned master it returns the joined orders and the list of unmatched codes in one call. Importing it from a shared module means a change to the rule — say, a new separator character that turns up in one supplier's files — applies to every report at once. The helper raises on collisions instead of picking one silently, because the right answer depends on business knowledge the script does not have.

Performance notes

Series.map with a Python function runs once per value, which is fine for tens of thousands of keys and slow for millions. For large tables, normalise the unique keys only and map back — uniq = s.unique(); lookup = dict(zip(uniq, map(norm_key, uniq))); s.map(lookup) — because codes usually repeat heavily, or use vectorised string methods (str.normalize("NFKC"), str.replace(r"[\s\-_]+", "", regex=True), str.casefold(), plus a str.replace for the zero-width characters), which perform the same steps column-wise. The merge itself is a hash join and costs the same as any other merge.

Common pitfalls

  • Normalising only one side. Both sides must go through the same function.
  • Overwriting the original key. Keep the raw value for display and audit; join on a separate column.
  • Ignoring collisions. Normalisation can merge distinct master records; check and validate.
  • Reading IDs as numbers. Leading zeros vanish before you can normalise; read keys with dtype=str.
  • Over-normalising. Removing every non-alphanumeric character can make A-12 and A1-2 equal; remove only the separators that are noise in your data.

Conclusion

Exact merges fail on keys that differ in case, spacing, invisible characters, separators or lost leading zeros. Read keys as text, build one normalisation function — NFKC, remove zero-width characters, drop separators, casefold, zfill for numeric IDs — and apply it to both sides into a separate key column. Check the master for keys that collide after normalising, merge with validate="many_to_one" and indicator=True, and report the rows that still do not match.

Frequently asked questions

Why does my pandas merge miss rows that look identical? merge compares keys character by character. Trailing spaces, different case, non-breaking or zero-width spaces, or different separators make keys unequal even when they look the same. Print them with repr() to see the hidden characters.

How do I merge case-insensitively in pandas? Create a key column on both DataFrames with the same normalisation — strip the text and casefold it — and merge on that column instead of the original.

How do I match IDs when one file lost the leading zeros? Read both columns as text and pad them to the same width with str.zfill(width), using the width of the master's IDs, then merge on the padded keys.

How do I stop a normalised merge from duplicating rows? Check the lookup table for keys that become duplicates after normalisation, resolve them, and merge with validate="many_to_one" so any future duplicate raises an error.

More on joins, lookups & comparisons

More on joins, lookups & comparisons →