Python Doc & Data Automation

Fix Merge Indicator Unexpected left_only Rows

A comparison merge reports hundreds of records as left_only and the same number as right_only, and opening both files shows the keys are plainly there on both sides: customer 00417 in the CRM export, customer 00417 in the billing file.

>>> merged = crm.merge(billing, on="customer_id", how="outer", indicator=True)
>>> merged["_merge"].value_counts()
_merge
both          3112
left_only      804
right_only     791

Sometimes the merge refuses outright instead: ValueError: You are trying to merge on object and int64 columns for key 'customer_id'. If you wish to proceed you should use pd.concat.

Root Cause

merge matches keys by exact equality of the stored values, not by how they look when printed. Several differences are invisible in a spreadsheet view but make keys unequal: one side read the column as integers (417) and the other as strings ("00417"); trailing spaces or non-breaking spaces from Excel ("00417 "); case ("ab-12" vs "AB-12"); floats created by a column containing blanks (417.0); zero-width or byte-order-mark characters at the start of a CSV's first field; and look-alike characters such as an en dash in place of a hyphen. Each produces a pair of rows — one left_only, one right_only — for what is really one matched record. The near-equal left and right counts in the output above are the fingerprint: the same keys are failing on both sides.

Minimal Diagnostic

Pair up the unmatched keys by a loosely normalised form and report why each pair failed. The categories tell you exactly which normalisation step is missing.

# pip install "pandas>=2.2"
import unicodedata
import pandas as pd

KEY = "customer_id"

def loose(value) -> str:
    text = unicodedata.normalize("NFKC", str(value))
    text = "".join(ch for ch in text if unicodedata.category(ch) not in ("Cf", "Zs") or ch == " ")
    text = text.strip().upper()
    if text.endswith(".0"):
        text = text[:-2]
    return text.lstrip("0") or "0"

def explain_unmatched(left: pd.DataFrame, right: pd.DataFrame, key: str = KEY) -> pd.Series:
    print("dtypes:", left[key].dtype, "vs", right[key].dtype)
    merged = left[[key]].merge(right[[key]], on=key, how="outer", indicator=True)
    lo = merged.loc[merged["_merge"] == "left_only", key]
    ro = merged.loc[merged["_merge"] == "right_only", key]
    right_by_loose = {loose(v): v for v in ro}
    reasons = []
    for v in lo:
        w = right_by_loose.get(loose(v))
        if w is None:
            reasons.append("genuinely missing on right")
        elif type(v) is not type(w):
            reasons.append(f"type {type(v).__name__} vs {type(w).__name__}")
        elif str(v).strip() != str(v) or str(w).strip() != str(w):
            reasons.append("leading/trailing whitespace")
        elif str(v).upper() == str(w).upper():
            reasons.append("case")
        elif str(v).lstrip("0") == str(w).lstrip("0"):
            reasons.append("leading zeros")
        else:
            reasons.append("hidden or look-alike characters")
    return pd.Series(reasons, dtype="string").value_counts()

if __name__ == "__main__":
    crm = pd.read_csv("in/crm.csv")
    billing = pd.read_excel("in/billing.xlsx")
    print(explain_unmatched(crm, billing).to_string())

dtypes: int64 vs object
type int vs str                  612
leading/trailing whitespace      171
genuinely missing on right        21

612 keys fail only because pandas inferred integers from the CSV, and 171 because the Excel export padded values with spaces. Only 21 are real differences.

Why left_only rows did not match Of 804 rows marked left_only, 612 failed because the CSV key was read as integers while the Excel key was text, 171 failed because of trailing spaces in the Excel export, and only 21 were customers genuinely missing from the billing file. 804 left_only rows, CRM CSV versus billing workbook int vs str dtype 612 rows Trailing whitespace 171 rows Genuinely missing 21 rows Only the last bar is a real difference; the rest are artefacts of reading and exporting

Fix: Read Keys as Text and Normalise Both Sides the Same Way

Prevent type inference at read time, then pass both key columns through one normalisation function before merging. Changed lines carry comments.

# pip install "pandas>=2.2" openpyxl
import unicodedata
from pathlib import Path
import pandas as pd

KEY = "customer_id"
ZERO_WIDTH = {"\N{ZERO WIDTH SPACE}", "\N{ZERO WIDTH NO-BREAK SPACE}", "\N{ZERO WIDTH JOINER}"}
DASHES = {"\N{EN DASH}": "-", "\N{EM DASH}": "-", "\N{NON-BREAKING HYPHEN}": "-"}

def normalise_key(series: pd.Series, pad_to: int | None = 5) -> pd.Series:
    def clean(value):
        if pd.isna(value):
            return pd.NA
        text = unicodedata.normalize("NFKC", str(value))              # changed: fold width variants
        text = "".join(DASHES.get(ch, ch) for ch in text if ch not in ZERO_WIDTH)  # changed
        text = text.replace("\N{NO-BREAK SPACE}", " ").strip().upper()  # changed: spaces and case
        if text.endswith(".0") and text[:-2].isdigit():
            text = text[:-2]                                           # changed: 417.0 from float columns
        if pad_to and text.isdigit():
            text = text.zfill(pad_to)                                  # changed: one canonical zero form
        return text
    return series.map(clean).astype("string")

def load_both(crm_path: Path, billing_path: Path) -> tuple[pd.DataFrame, pd.DataFrame]:
    try:
        crm = pd.read_csv(crm_path, dtype={KEY: "string"}, encoding="utf-8-sig")  # changed: text + BOM
        billing = pd.read_excel(billing_path, dtype={KEY: "string"})              # changed: text
    except (OSError, ValueError) as exc:
        raise SystemExit(f"cannot read inputs: {exc}")
    crm[KEY] = normalise_key(crm[KEY])
    billing[KEY] = normalise_key(billing[KEY])
    return crm, billing

if __name__ == "__main__":
    crm, billing = load_both(Path("in/crm.csv"), Path("in/billing.xlsx"))
    merged = crm.merge(billing, on=KEY, how="outer", indicator=True, validate="one_to_one")
    print(merged["_merge"].value_counts().to_string())

_merge
both          3903
left_only       21
right_only       8

Zero-padding to a fixed width is a choice, not a law. It is right when the business key is defined as five digits and some systems drop the zeros; it is wrong when 417 and 00417 are genuinely different customers. Confirm with the system owner before choosing between padding and stripping — the important part is that both sides use the same canonical form. encoding="utf-8-sig" strips the byte-order mark that Excel-saved CSVs place before the first header, which otherwise silently prefixes the column name with an invisible character and breaks the dtype mapping too.

One normalisation function for both sides Six steps applied to each key value on both sides. Read the column as string dtype. Apply NFKC Unicode normalisation. Remove zero width characters and map look-alike dashes to hyphens. Replace non-breaking spaces, strip whitespace and upper-case. Remove a trailing .0 left by float columns. Zero-pad numeric keys to the canonical width. The merge then compares canonical strings. dtype string no inference NFKC fold width variants Invisible chars zero width, dashes Space and case strip, upper Float suffix 417.0 to 417 Zero pad canonical width

Variant Fix 1: Composite Keys

Merges on several columns — customer_id plus invoice_date — fail when any one component differs in type or format. Dates are the usual culprit: one side has a timestamp at midnight, the other a date with a time, or text:

# pip install "pandas>=2.2"
import pandas as pd

def normalise_composite(frame: pd.DataFrame) -> pd.DataFrame:
    out = frame.copy()
    out["customer_id"] = normalise_key(out["customer_id"])
    out["invoice_date"] = (pd.to_datetime(out["invoice_date"], errors="coerce", dayfirst=True)
                             .dt.tz_localize(None)                    # drop timezone if present
                             .dt.normalize())                         # midnight, no time part
    failed = out["invoice_date"].isna().sum()
    if failed:
        print(f"warning: {failed} date(s) could not be parsed and will not match")
    return out

dt.tz_localize(None) only applies to timezone-aware columns; for naive ones it is a no-op in recent pandas, but wrap it in a check if you support older versions. Mixed day-first and month-first dates are their own problem, handled in normalize inconsistent date formats in CSV.

Variant Fix 2: Many right_only Rows from Duplicates

When one side has duplicate keys, merges multiply rows and counts stop meaning what you expect; after validate="one_to_one" is added, pandas raises MergeError instead. Find the duplicates and decide which record represents the key:

# pip install "pandas>=2.2"
import pandas as pd

def resolve_duplicates(frame: pd.DataFrame, key: str, prefer: str) -> tuple[pd.DataFrame, pd.DataFrame]:
    """Keep the row with the latest `prefer` value per key; return (kept, dropped)."""
    ordered = frame.sort_values([key, prefer], ascending=[True, False], kind="stable")
    keep_mask = ~ordered[key].duplicated(keep="first")
    return ordered[keep_mask], ordered[~keep_mask]

Write the dropped rows to the exception report. Silent de-duplication hides exactly the data problem — two billing accounts for one customer — that a comparison is supposed to surface; the broader treatment is in remove duplicate rows in pandas.

Preventing the Problem at the Source

Normalisation fixes the merge, but every consumer of those exports has to repeat it. Where you control an export, write keys in their canonical form and as text so that Excel cannot re-interpret them either:

# pip install "pandas>=2.2" xlsxwriter
from pathlib import Path
import pandas as pd

def export_with_text_keys(frame: pd.DataFrame, dest: Path, key: str = "customer_id") -> None:
    frame = frame.assign(**{key: normalise_key(frame[key])})
    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        frame.to_excel(writer, sheet_name="Data", index=False)
        book, ws = writer.book, writer.sheets["Data"]
        text = book.add_format({"num_format": "@"})                  # Excel text format
        col = frame.columns.get_loc(key)
        ws.set_column(col, col, 12, text)

The @ text format keeps Excel from stripping zeros when someone edits and re-saves the file. For CSV exports that people open in Excel, zeros are lost on open regardless of how the file was written; fix CSV leading zeros lost in Excel covers the options.

Where Each Mismatch Comes From

Knowing which tool introduced a representation difference tells you where a permanent fix belongs. Most key mismatches trace back to one of a handful of steps between the source system and your merge:

Where key mismatches are introduced Integer versus string keys come from pandas type inference when reading CSV, fixed with a string dtype at read time. Lost leading zeros come from opening a CSV in Excel and re-saving, fixed by exporting keys as text. Trailing spaces come from fixed-width database columns or copy-paste, fixed by trimming on export or read. Float keys such as 417.0 come from columns containing blanks, fixed with nullable string or integer dtypes. Byte order marks come from Excel's UTF-8 CSV export, fixed with utf-8-sig encoding. Mismatch Introduced by Permanent fix int vs str pandas type inference dtype string on read lost zeros Excel open and re-save text format on export trailing spaces fixed-width DB columns trim in export query 417.0 floats blanks in numeric column nullable dtypes BOM on header Excel UTF-8 CSV encoding utf-8-sig

The pattern is that pandas and Excel each try to be helpful by guessing types, and each guess is locally reasonable. Declaring key columns as text at every boundary — database export, CSV read, Excel write — removes the guessing. When you cannot change the upstream system, record the normalisation rules in one shared module that every job imports, so the CRM comparison, the billing reconciliation and the monthly report all agree on what a customer ID is.

Verification

After normalising, the only remaining left_only and right_only keys should be genuine. Assert that no unmatched key on one side has a loose equivalent on the other:

# pip install "pandas>=2.2"
import pandas as pd

def verify_merge(left: pd.DataFrame, right: pd.DataFrame, key: str = KEY) -> None:
    merged = left.merge(right, on=key, how="outer", indicator=True, validate="one_to_one")
    lo = set(merged.loc[merged["_merge"] == "left_only", key].map(loose))
    ro = set(merged.loc[merged["_merge"] == "right_only", key].map(loose))
    near_misses = lo & ro
    assert not near_misses, f"{len(near_misses)} keys still differ only cosmetically, e.g. {sorted(near_misses)[:5]}"
    assert str(left[key].dtype) == str(right[key].dtype) == "string", "key dtypes differ"
    counts = merged["_merge"].value_counts().to_dict()
    print(f"clean merge: {counts}")

loose is the deliberately generous function from the diagnostic. If a key still collides under it after the fix, the normaliser is missing a rule; add it to normalise_key rather than to the verification.

FAQ

Why not just use astype(str) on both keys? It converts NaN to the string "nan", turns 417.0 into "417.0", and keeps whitespace. Use a string dtype plus explicit cleaning.

Is merge_asof or fuzzy matching a better fix? No. They solve different problems — nearest values and approximate text. Unequal representations of the same exact key need normalisation.

Why do left_only and right_only counts differ slightly? The overlap between them is the cosmetic mismatches; the remainder on each side are genuine additions or removals, which rarely balance exactly.

Should the indicator column stay in the report? Rename it to something readable — in_crm_only, in_billing_only, in_both — before writing the exception workbook.

Part of Comparing and Reconciling Spreadsheets with Python.