如何在Pandas合并DataFrame时将数据填充到指定自定义列

现有三个Python pandas DataFrame：

df1

df2

df3

当前执行的合并代码：

df_1= pd.merge(df_1, df2, on ='PEOPLE', how ='outer') # Step 1
df_1= pd.merge(df_1, df3, on ='PEOPLE', how ='outer') # Step 2
df_1= df_1.loc[:, ~df_merge.columns.str.contains('^Unnamed')]

执行后会新增AMOUNT_X、AMOUNT_Y列，但需求是将Step1合并的df2数据填充到AMOUNT_custom_A列，Step2合并的df3数据填充到AMOUNT_custom_B列，最终得到包含三列的DataFrame。

解决方案 #

方法1：重命名列后合并 #

先将df2和df3的AMOUNT列重命名为目标列名，再依次与df1合并，直接得到结果：

import pandas as pd

# 重命名df2和df3的AMOUNT列，匹配df1的目标列名
df2_renamed = df2.rename(columns={'AMOUNT': 'AMOUNT_custom_A'})
df3_renamed = df3.rename(columns={'AMOUNT': 'AMOUNT_custom_B'})

# 合并df1与重命名后的df2
df_merged = pd.merge(df1, df2_renamed, on='PEOPLE', how='outer')
# 再合并重命名后的df3
df_merged = pd.merge(df_merged, df3_renamed, on='PEOPLE', how='outer')

print(df_merged)

执行后输出：

方法2：合并后填充目标列 #

如果保留原合并逻辑，可在合并后将自动生成的AMOUNT_x、AMOUNT_y值填充到目标列，再删除多余列：

import pandas as pd

# 执行两次合并（修正原代码变量名错误：df_merge改为df_1）
df_1 = pd.merge(df1, df2, on='PEOPLE', how='outer')
df_1 = pd.merge(df_1, df3, on='PEOPLE', how='outer')
df_1 = df_1.loc[:, ~df_1.columns.str.contains('^Unnamed')]

# 将来自df2的AMOUNT_x填充到AMOUNT_custom_A
df_1['AMOUNT_custom_A'] = df_1['AMOUNT_custom_A'].fillna(df_1['AMOUNT_x'])
# 将来自df3的AMOUNT_y填充到AMOUNT_custom_B
df_1['AMOUNT_custom_B'] = df_1['AMOUNT_custom_B'].fillna(df_1['AMOUNT_y'])

# 删除多余的临时列
df_1 = df_1.drop(['AMOUNT_x', 'AMOUNT_y'], axis=1)

print(df_1)

该方法同样能得到预期的三列结构。

内容的提问来源于stack exchange，提问作者Nguyen Minh Trung