如何用Pandas合并df2列到df1并替换重复值？避免冗余列与重复行

我尝试将df2中的列值合并到df1中，使用df1.merge(df2, how='outer')出现了重复行，不符合需求；使用on参数又会生成带_x和_y的冗余列，也不满足要求。

示例场景：当df1和df2中sub=site1时，需用df2中的'fred'替换df1的'own'值。

# Pandas Merge test:

import pandas as pd

df1 = pd.DataFrame({'sub': ['site1', 'site2', 'site3'], 'iss': ['enc1', 'enc2', 'enc3'], 'rem': [1, 3, 5], 'own': ['andy', 'brian', 'cody']})
df2 = pd.DataFrame({'sub': ['data1', 'data2', 'site1'], 'rem': [2, 4, 6], 'own': ['david', 'edger', 'fred']})

>>> df1
     sub   iss  rem    own
0  site1  enc1    1   andy
1  site2  enc2    3  brian
2  site3  enc3    5   cody

>>> df2
     sub  rem    own
0  data1    2  david
1  data2    4  edger
2  site1    6   fred

>>> df1.merge(df2, how='outer')
     sub   iss  rem    own
0  site1  enc1    1   andy
1  site2  enc2    3  brian
2  site3  enc3    5   cody
3  data1   NaN    2  david
4  data2   NaN    4  edger
5  site1   NaN    6   fred

>>> df1.merge(df2, on='sub', how='outer')
     sub   iss  rem_x  own_x  rem_y  own_y
0  site1  enc1    1.0   andy    6.0   fred
1  site2  enc2    3.0  brian    NaN    NaN
2  site3  enc3    5.0   cody    NaN    NaN
3  data1   NaN    NaN    NaN    2.0  david
4  data2   NaN    NaN    NaN    4.0  edger

sub   iss  rem    own
0  site1  enc1    1   fred
1  site2  enc2    3  brian
2  site3  enc3    5   cody
3  data1   NaN    2  david
4  data2   NaN    4  edger

方法一：先更新匹配行，再追加新行 #

先替换df1中与df2匹配的sub对应的own值，再把df2中df1没有的行追加进去：

import pandas as pd

df1 = pd.DataFrame({'sub': ['site1', 'site2', 'site3'], 'iss': ['enc1', 'enc2', 'enc3'], 'rem': [1, 3, 5], 'own': ['andy', 'brian', 'cody']})
df2 = pd.DataFrame({'sub': ['data1', 'data2', 'site1'], 'rem': [2, 4, 6], 'own': ['david', 'edger', 'fred']})

# 更新df1中匹配sub的own值
df1.loc[df1['sub'].isin(df2['sub']), 'own'] = df2.set_index('sub')['own'].reindex(df1['sub']).values

# 筛选df2中df1没有的sub行并追加
new_rows = df2[~df2['sub'].isin(df1['sub'])]
result = pd.concat([df1, new_rows], ignore_index=True)

print(result)

方法二：合并后处理冗余列 #

先通过on='sub'做外连接，再对每个列进行取值逻辑处理：

import pandas as pd

df1 = pd.DataFrame({'sub': ['site1', 'site2', 'site3'], 'iss': ['enc1', 'enc2', 'enc3'], 'rem': [1, 3, 5], 'own': ['andy', 'brian', 'cody']})
df2 = pd.DataFrame({'sub': ['data1', 'data2', 'site1'], 'rem': [2, 4, 6], 'own': ['david', 'edger', 'fred']})

# 基于sub做外连接
merged = df1.merge(df2, on='sub', how='outer')
# 处理各列：iss取df1的值，rem优先取df1的值，own优先取df2的值
merged['iss'] = merged['iss_x']
merged['rem'] = merged['rem_x'].fillna(merged['rem_y'])
merged['own'] = merged['own_y'].fillna(merged['own_x'])
# 保留需要的列
result = merged[['sub', 'iss', 'rem', 'own']]

print(result)

两种方法都能得到期望的输出，可根据实际数据规模和需求选择。

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