通过ID合并两个Excel文件并合并同名列（Python, Pandas）

Question

我刚开始接触stackoverflow和Python中的pandas库。在一篇帖子中，我找到了部分答案，链接是如何用Python 2.7将两个Excel文件按ID合并成一个Excel文件

不过，我还想把两个Excel文件中同名的列合并在一起。我以为下面这篇帖子会有我想要的答案，但我猜它的标题不太准确：合并具有相同列名的Pandas数据框

现在我有以下代码：

import pandas as pd

file1 = pd.read_excel("file1.xlsx")
file2 = pd.read_excel("file2.xlsx")

file3 = file1.merge(file2, on="ID", how="outer")

file3.to_excel("merged.xlsx")

file1.xlsx

ID,JanSales,FebSales,test
1,100,200,cars
2,200,500,
3,300,400,boats

file2.xlsx

ID,CreditScore,EMMAScore,test
2,good,Watson,planes
3,okay,Thompson,
4,not-so-good,NA,

我得到的结果是merged.xlsx

ID,JanSales,FebSales,test_x,CreditScore,EMMAScore,test_y
1,100,200,cars,NaN,NaN,
2,200,500,,good,Watson,planes
3,300,400,boats,okay,Thompson,
4,NaN,NaN,,not-so-good,NaN,

我想要的结果是merged.xlsx

ID,JanSales,FebSales,CreditScore,EMMAScore,test
1,100,200,NaN,NaN,cars
2,200,500,good,Watson,planes
3,300,400,okay,Thompson,boats
4,NaN,NaN,not-so-good,NaN,NaN

在我的实际数据中，有200多列对应于我示例中的“test”列。我希望程序能找到file1.xlsx和file2.xlsx中同名的列，并将它们合并到最终的文件中。

Answer 1

好的，这里有一种更灵活的方法。在合并数据时，我们假设会出现重复的列名，结果可能会变成 'column_name_x' 或 '_y'。

首先，我们要找出所有重复的列名，并把 'ID' 从这个列表中去掉。

In [51]:

common_columns = list(set(list(df1.columns)) & set(list(df2.columns)))
common_columns.remove('ID')
common_columns
Out[51]:
['test']

接下来，我们可以遍历这个列表，创建新的列，并使用 where 来根据哪个值不为空来有条件地赋值。

In [59]:

for col in common_columns:
    df3[col] = df3[col+'_x'].where(df3[col+'_x'].notnull(), df3[col+'_y'])
df3
Out[59]:
   ID  JanSales  FebSales test_x  CreditScore EMMAScore  test_y    test
0   1       100       200   cars          NaN       NaN     NaN    cars
1   2       200       500    NaN         good    Watson  planes  planes
2   3       300       400  boats         okay  Thompson     NaN   boats
3   4       NaN       NaN    NaN  not-so-good       NaN     NaN     NaN

[4 rows x 8 columns]

最后，只需要删除所有多余的列就可以了：

In [68]:

clash_names = [elt+suffix for elt in common_columns for suffix in ('_x','_y') ]
clash_names
df3.drop(labels=clash_names, axis=1,inplace=True)
df3
Out[68]:
   ID  JanSales  FebSales  CreditScore EMMAScore    test
0   1       100       200          NaN       NaN    cars
1   2       200       500         good    Watson  planes
2   3       300       400         okay  Thompson   boats
3   4       NaN       NaN  not-so-good       NaN     NaN

[4 rows x 6 columns]

上面的代码片段来自这个链接：使用列表推导为列表元素添加前缀