python pandas合并多个excel_按ID合并两个Excel文件，并合并同名列（python、pandas）...

好的，这里有一个更动态的方法，在合并之后，我们假设会发生冲突并导致“column_name_x”或“u 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]