排查Pandas DataFrame合并时出现的KeyError: 'Cust_id_2'问题

Hey there! Let's walk through why you're hitting these KeyErrors and fix them up—this is a super common pandas merge pitfall.

First, the immediate KeyError: 'Cust_id_2' #

Your current merge code has a critical oversight: when you write df2[['year_freq_score']], you're only selecting that single column from df2. That means the Cust_id_2 column gets dropped entirely before the merge, so pandas can't find it when you specify right_on='Cust_id_2'. That's exactly why you're seeing that error.

Let's fix this, plus address your earlier attempts #

Let's go through the correct approaches for each of your merge attempts:

1. Fixing your current right_on approach

Keep the Cust_id_2 column in df2 during the merge (don't filter it out), then drop it afterward if you don't need it:

# Merge with the full df2 (includes Cust_id_2 for matching)
result = pd.merge(df, df2, how='left', left_on='Cust_id', right_on='Cust_id_2')
# Optional: drop the duplicate ID column if you don't need it
result = result.drop('Cust_id_2', axis=1)

2. Using a matching column name (cleaner for future code)

Since df uses Cust_id, rename df2's Cust_id_2 to match, then merge on the shared column name:

# Rename df2's ID column to match df's naming
df2_clean = df2.rename(columns={'Cust_id_2': 'Cust_id'})
# Now merge using the shared column name directly
result = pd.merge(df, df2_clean, how='left', on='Cust_id')

3. Fixing the index-based merge you tried earlier

If you want to use the index for merging, make sure you're setting the right column as the index in df2, then pair left_on with right_index=True:

# Set Cust_id_2 as df2's index for matching
df2_indexed = df2.set_index('Cust_id_2')
# Merge using df's Cust_id column and df2's index
result = pd.merge(df, df2_indexed, how='left', left_on='Cust_id', right_index=True)

Quick debugging tip #

Before merging, always double-check the columns present in your DataFrames with print(df2.columns)—this will catch cases where you've accidentally filtered out a column you need for matching.

内容的提问来源于stack exchange，提问作者Neil