Pandas新手问询：基于共同列从另一DataFrame添加列并保留原索引

Hey there! Great call on deduplicating df2 first— that's the critical step to avoid duplicate rows popping up in your final df1. Let's break down exactly how to get your desired result:

Step 1: Deduplicate df2 #

Since each ItemId in df2 maps to the same column_to_merge value (like all ItemId=1 entries have apple), we can safely remove duplicates based on the ItemId column. This ensures we only have one row per unique ItemId to merge with df1:

# Keep only unique ItemId entries (retains the first occurrence by default)
df2_dedup = df2.drop_duplicates(subset=['ItemId'])

If you ever run into a case where an ItemId has conflicting column_to_merge values, you can adjust this step (e.g., use groupby('ItemId').first() to pick the first value, or aggregate as needed). But for your example data, this simple deduplication works perfectly.

Step 2: Merge with df1 (Preserve Original Index) #

Now we'll merge the deduplicated df2 with df1. We'll use a left join to keep all rows from df1, and only pull in matching column_to_merge values from df2. We'll also make sure to only select the columns we need from df2 to avoid cluttering the result:

# Merge df1 with the relevant columns from deduplicated df2
df1_merged = df1.merge(df2_dedup[['ItemId', 'column_to_merge']], 
                       on='ItemId', 
                       how='left')

The how='left' parameter guarantees that df1's original rows and its index are preserved exactly as they were.

Final Result #

Running the above code will give you exactly the output you're expecting:

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