python - 按 ID 合并两个 Excel 文件并合并具有相同名称的列(python、pandas)

Question

我是 stackoverflow 和 pandas for python 的新手。我在帖子 Looking to merge two Excel files by ID into one Excel file using Python 2.7 中找到了部分答案

但是，我还想合并或合并两个同名 Excel 文件中的列。我以为下面的帖子会有我的答案，但我想它的标题不正确:Merging Pandas DataFrames with the same column name

现在我有代码:

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")

文件1.xlsx

ID,JanSales,FebSales,测试
1,100,200辆汽车
2,200,500,
3,300,400,艘船

文件2.xlsx

ID,CreditScore,EMMAScore,测试
2，好，沃森，飞机
3，好的，汤普森，
4,不太好,NA,

我得到的是merged.xlsx

ID,JanSales,FebSales,test_x,CreditScore,EMMAScore,test_y
1,100,200,汽车,NaN,NaN,
2,200,500,,good,Watson,飞机
3,300,400,boats,okay,Thompson,
4,NaN,NaN,,不太好,NaN,

我要的是merged.xlsx

ID,JanSales,FebSales,CreditScore,EMMAScore,测试
1,100,200,NaN,NaN,汽车
2,200,500,good,Watson,飞机
3,300,400，好的，汤普森，船
4,NaN,NaN,不太好,NaN,NaA

在我的真实数据中，有 200 多列对应于我示例中的“测试”列。我希望程序在 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]

上面的片段来自:Prepend prefix to list elements with list comprehension