pandas merge KeyError, int64 vs object, and rows not matching (tested)

When df.merge(other, on="id") raises KeyError: 'id', raises ValueError: You are trying to merge on int64 and object columns, or returns fewer rows than expected, the keys differ in name, dtype or exact text. We reproduced and fixed each case on pandas 2.3.3 and 3.1.0.

1. KeyError: the key has a different name on each side

import pandas as pd

orders = pd.DataFrame({"cust_id": [1, 2], "total": [9, 5]})
custs = pd.DataFrame({"id": [1, 2], "name": ["ann", "bob"]})
try:
    orders.merge(custs, on="id")
except KeyError as e:
    print("KeyError:", e)
print(orders.merge(custs, left_on="cust_id", right_on="id").values.tolist())

Output (identical on pandas 2.3.3 and 3.1.0):

KeyError: 'id'
[[1, 9, 1, 'ann'], [2, 5, 2, 'bob']]

on="id" needs a column called id in both DataFrames. When the names differ, use left_on= and right_on=. If the name looks identical on both sides, print list(df.columns): a header space or BOM is the usual cause (see KeyError but the column exists), and a column moved into the index by set_index needs left_index=True / right_index=True or reset_index().

2. ValueError: merging int64 and object (or str) keys

import pandas as pd

orders = pd.DataFrame({"id": [1, 2], "total": [9, 5]})
custs = pd.DataFrame({"id": ["1", "2"], "name": ["ann", "bob"]})   # ids read as text
try:
    orders.merge(custs, on="id")
except ValueError as e:
    print("ValueError:", e)
custs["id"] = custs["id"].astype("int64")
print(orders.merge(custs, on="id").values.tolist())

Output with pandas 2.3.3:

ValueError: You are trying to merge on int64 and object columns for key 'id'. If you wish to proceed you should use pd.concat
[[1, 9, 'ann'], [2, 5, 'bob']]

Output with pandas 3.1.0:

ValueError: You are trying to merge on int64 and str columns for key 'id'. If you wish to proceed you should use pd.concat
[[1, 9, 'ann'], [2, 5, 'bob']]

One side read the IDs as text (common with CSV files, Excel exports and JSON). pandas refuses to compare 1 with "1"; pandas 3 names the text dtype str instead of object. Ignore the "use pd.concat" hint, which is about stacking rows, not joining. Convert one side so both keys have the same dtype, e.g. astype("int64") or astype(str) (keep leading zeros as strings).

3. No error, but the merge is empty or drops rows

import pandas as pd

a = pd.DataFrame({"code": ["A1", "B2"], "x": [1, 2]})
b = pd.DataFrame({"code": ["A1 ", "b2"], "y": [3, 4]})            # trailing space, case
print(len(a.merge(b, on="code")))                                # 0 rows, no error
m = a.merge(b, on="code", how="outer", indicator=True)
print(m[["code", "_merge"]].values.tolist())
b["code"] = b["code"].str.strip().str.upper()
print(a.merge(b, on="code").values.tolist())

Output (identical on pandas 2.3.3 and 3.1.0):

0
[['A1', 'left_only'], ['A1 ', 'right_only'], ['B2', 'left_only'], ['b2', 'right_only']]
[['A1', 1, 3], ['B2', 2, 4]]

The default how="inner" silently drops keys that do not match exactly, so trailing spaces and case differences give fewer rows (here 0) without any error. how="outer", indicator=True adds a _merge column showing which keys matched only on one side. Normalise both key columns with .str.strip() (and .str.upper() or .str.lower() if case should not matter) before merging.

Related: KeyError: not in index · Error tokenizing data · merge CSV files in Python.

Written with AI assistance. Measured on 2026-10-23 in the python:3-alpine Docker image (Python 3.14) with pandas 2.3.3 and pandas 3.1.0. Each block was run verbatim on both versions and the output pasted unedited; where the versions differ both outputs are shown. PlainHeaders is our extension; Python and pandas are free.