如何用Pandas基于唯一ID更新DataFrame指定Data列数据

The key here is to map df2's Data values to df1's matching IDs, keeping df1's original Data where no match exists. Since df1 is small (hundreds of rows) compared to df2 (thousands), using a mapping approach is efficient and clean.

Step-by-Step Approach: #

1. Create a lookup mapping from df2: Use ID as the index and Data as the value. This lets us quickly look up Data values by ID.
2. Map and fill: Use df1's ID column to fetch matching Data from the mapping. For IDs not found in df2, fill with df1's original Data values.

Working Code: #

import pandas as pd

# Your original data setup
data1 = '''\
ID Data Data1
1  AA  BB
2  AB  BF
3  AC  BK
4  AD  BL'''
data2 = '''\
ID Data
1  AAB
3  AAL
4  MNL
5  AAP
6  MNX
8  DLP
9  POW'''
df1 = pd.read_csv(pd.compat.StringIO(data1), sep='\s+')
df2 = pd.read_csv(pd.compat.StringIO(data2), sep='\s+')

# Step 1: Create ID-to-Data mapping from df2
data_mapping = df2.set_index('ID')['Data']

# Step 2: Update df1's Data column (copy df1 to avoid modifying original)
df3 = df1.copy()
df3['Data'] = df3['ID'].map(data_mapping).fillna(df3['Data'])

# View the result
print(df3)

Output: #

ID Data Data1
0   1  AAB    BB
1   2   AB    BF
2   3  AAL    BK
3   4  MNL    BL

Why This Works: #

• df2.set_index('ID')['Data'] creates a Series where each index is an ID from df2, and the value is the corresponding Data.
• df3['ID'].map(data_mapping) looks up each ID in df1 against the mapping: returns df2's Data if the ID exists, else NaN.
• .fillna(df3['Data']) replaces those NaN values with df1's original Data, preserving rows where no match was found in df2.

Alternative Merge Approach: #

If you prefer using merge, you can combine the resulting columns after a left join:

df3 = pd.merge(df1, df2, on='ID', how='left')
# Combine Data_y (from df2) and Data_x (from df1)
df3['Data'] = df3['Data_y'].fillna(df3['Data_x'])
# Drop redundant columns
df3 = df3.drop(columns=['Data_x', 'Data_y'])

This also gives the same result, but the mapping method is more efficient for your data size (small df1, large df2).

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