使用左连接合并两个DataFrame触发KeyError 'Bizot'的原因

Let's walk through exactly what's causing this error and how to fix your merge.

The Root Cause #

Your KeyError happens because the tigeny_df DataFrame doesn't actually have a column named 'Bizot' when you try to merge. Here's the breakdown of your code that leads to this:

1. You rename a column in esemeny_df to 'Bizot' — that part works fine, so esemeny_df has a valid 'Bizot' column.
2. You set ['Bizot', 'Kell'] as the index of igeny_df. This removes the 'Bizot' column from igeny_df entirely and turns it into a level of the row MultiIndex instead.
3. When you transpose igeny_df to get tigeny_df, the original row indices (including 'Bizot') become the column MultiIndex of tigeny_df. So 'Bizot' is now part of the column labels, not a standalone column you can reference with right_on.

When pandas runs merge(..., right_on='Bizot'), it looks for a column named 'Bizot' in tigeny_df and can't find it — hence the KeyError.

Fixes to Try #

Here are two straightforward ways to adjust your code to make the merge work:

Fix 1: Convert the Column MultiIndex to Columns Before Merging

After transposing, expand the MultiIndex columns into actual columns so 'Bizot' becomes accessible for merging. This preserves your 'Kell' data too:

# Your existing setup code
esemeny_df = x_df.sort_index(axis=0, ascending=False)
esemeny_df = esemeny_df.rename(index=str, columns={'sum': 'Bizot'})

igeny_df = igeny_df.set_index(['Bizot', 'Kell'])
tigeny_df = igeny_df.T

# Expand MultiIndex columns into separate, usable columns
tigeny_df = tigeny_df.stack(level='Kell').reset_index()
tigeny_df = tigeny_df.rename(columns={'level_0': 'Original_Column', 0: 'Metric_Value'})

# Now merge on the 'Bizot' column present in both DataFrames
df = esemeny_df.merge(tigeny_df, how='left', on='Bizot')
print(df)

Fix 2: Merge Using Indices Instead of Columns

If you don't need to expand the MultiIndex, set 'Bizot' as the index of esemeny_df and merge directly using indices:

# Your existing setup code
esemeny_df = x_df.sort_index(axis=0, ascending=False)
esemeny_df = esemeny_df.rename(index=str, columns={'sum': 'Bizot'})

igeny_df = igeny_df.set_index(['Bizot', 'Kell'])
tigeny_df = igeny_df.T

# Set 'Bizot' as esemeny_df's index and merge via indices
esemeny_df = esemeny_df.set_index('Bizot')
df = esemeny_df.merge(tigeny_df, how='left', left_index=True, right_index=True)
print(df)

Note: This will leave you with columns that have MultiIndex labels (from the original 'Kell' level), so you may want to clean those up depending on your end goal.

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