pandas DataFrame merge 시 키 에러

Introduction

Merging DataFrames is one of the most fundamental operations in pandas, but it is also one of the most error-prone. The KeyError that appears during a merge operation can mean many different things: a missing column name, a mismatched data type, an index misalignment, or even a typo in the column name.

I encountered a particularly tricky version of this while building a financial data pipeline. I was merging market data from two different API sources, and one used integer timestamps while the other used datetime objects. The merge failed silently in some cases and threw a KeyError in others.

Environment

Python 3.12.3
pandas 2.2.1
numpy 1.26.4

Problem

Error 1: KeyError on merge column

import pandas as pd

df1 = pd.DataFrame({
    "product_id": [1, 2, 3],
    "name": ["A", "B", "C"]
})

df2 = pd.DataFrame({
    "product_id": [1, 2, 3],
    "price": [10.0, 20.0, 30.0]
})

# Typo in column name
merged = df1.merge(df2, on="product_id")  # Works fine

# But this fails
merged = df1.merge(df2, on="product_id_typo")

KeyError: 'product_id_typo'

Error 2: KeyError when using left_on and right_on

df_orders = pd.DataFrame({
    "order_id": [1, 2, 3],
    "user_name": ["alice", "bob", "charlie"]
})

df_users = pd.DataFrame({
    "username": ["alice", "bob", "charlie"],
    "email": ["a@test.com", "b@test.com", "c@test.com"]
})

# This works but shows confusing column names
merged = df_orders.merge(df_users, left_on="user_name", right_on="username")

KeyError: 'user_name'
# (when the actual column name has different casing)

Error 3: KeyError with MultiIndex

arrays = [
    ["A", "A", "B", "B"],
    ["one", "two", "one", "two"]
]
index = pd.MultiIndex.from_arrays(arrays, names=["group", "item"])

df1 = pd.DataFrame({"value": [1, 2, 3, 4]}, index=index)

df2 = pd.DataFrame({
    "group": ["A", "A", "B", "B"],
    "item": ["one", "two", "one", "two"],
    "score": [10, 20, 30, 40]
})

# This fails because df1 has a MultiIndex
merged = df1.merge(df2, on=["group", "item"])

KeyError: 'group'

Analysis

The KeyError during merge has several root causes.

Root Cause 1: Column name does not exist. This is the simplest case — the column you specified in on, left_on, or right_on does not exist in one of the DataFrames. Check with df.columns.tolist().

Root Cause 2: Case sensitivity. Python strings are case-sensitive. "Product_ID" and "product_id" are different columns.

Root Cause 3: Data type mismatch. When columns have different data types (int vs string), the merge may fail or produce unexpected results. The error might not always be a KeyError.

Root Cause 4: Index-based merge confusion. When one DataFrame has a non-default index, merging on columns can fail because pandas tries to align by index.

Root Cause 5: Whitespace in column names. CSV files and databases sometimes include leading/trailing whitespace in column names.

Diagnose the issue:

# Check column names
print("df1 columns:", df1.columns.tolist())
print("df2 columns:", df2.columns.tolist())

# Check data types
print("df1 dtypes:\n", df1.dtypes)
print("df2 dtypes:\n", df2.dtypes)

# Check for whitespace
print("df1 columns repr:", [repr(c) for c in df1.columns])

Solution

Fix 1: Clean column names before merging

# Strip whitespace from column names
df1.columns = df1.columns.str.strip()
df2.columns = df2.columns.str.strip()

# Rename columns to match
df2 = df2.rename(columns={"product_id": "id"})
merged = df1.merge(df2, left_on="id", right_on="id")

Fix 2: Normalize data types before merge

# Convert both columns to the same type
df1["product_id"] = df1["product_id"].astype(str)
df2["product_id"] = df2["product_id"].astype(str)

merged = df1.merge(df2, on="product_id")

Fix 3: Reset index before merge

# Reset index to make it a regular column
df1 = df1.reset_index()

# Or merge on index
merged = df1.merge(df2, left_index=True, right_on="product_id")

Fix 4: Use validate parameter to catch issues early

# This raises MergeError if the merge is not one-to-one
merged = df1.merge(df2, on="product_id", validate="one_to_one")

# Or one-to-many
merged = df1.merge(df2, on="product_id", validate="one_to_many")

Fix 5: Safe merge function with error handling

import pandas as pd

def safe_merge(df1, df2, on=None, left_on=None, right_on=None, **kwargs):
    """Merge DataFrames with error checking."""
    merge_cols = on or left_on or right_on
    
    if isinstance(merge_cols, str):
        merge_cols = [merge_cols]
    
    for col in merge_cols:
        if col not in df1.columns:
            raise KeyError(f"Column '{col}' not found in left DataFrame. "
                         f"Available: {df1.columns.tolist()}")
        if col not in df2.columns:
            raise KeyError(f"Column '{col}' not found in right DataFrame. "
                         f"Available: {df2.columns.tolist()}")
    
    # Normalize types
    for col in merge_cols:
        if df1[col].dtype != df2[col].dtype:
            df2[col] = df2[col].astype(df1[col].dtype)
    
    return df1.merge(df2, on=on, left_on=left_on, right_on=right_on, **kwargs)

merged = safe_merge(df1, df2, on="product_id")

Lessons Learned

Always check df.columns.tolist() before attempting a merge to verify column names exist.
Strip whitespace from column names immediately after loading data from CSV or database.
Normalize data types before merging — especially when combining data from different sources.
Use validate parameter to catch merge quality issues early.
Build a safe_merge wrapper for your projects that includes error checking and type normalization.

This blog does not accept any external sponsorships, affiliate marketing, or ad revenue.