如何合并重叠的列

Question

我有两个这样的数据集

import pandas as pd
import numpy as np
df1 = pd.DataFrame({'id': [1, 2,3,4,5], 'first': [np.nan,np.nan,1,0,np.nan], 'second': [1,np.nan,np.nan,np.nan,0]})
df2 = pd.DataFrame({'id': [1, 2,3,4,5, 6], 'first': [np.nan,1,np.nan,np.nan,0, 1], 'third': [1,0,np.nan,1,1, 0]})

我想要得到

result = pd.merge(df1, df2,  left_index=True, right_index=True,on='id', how= 'outer')
result['first']= result[["first_x", "first_y"]].sum(axis=1)
result.loc[(result['first_x'].isnull()) & (result['first_y'].isnull()), 'first'] = np.nan
result.drop(['first_x','first_y'] , 1)

  id    second  third   first
0   1   1.0      1.0    NaN
1   2   NaN      0.0    1.0
2   3   NaN      NaN    1.0
3   4   NaN      1.0    0.0
4   5   0.0      1.0    0.0
5   6   NaN      0.0    1.0

问题在于真实数据集包含了大约200个变量，而我的方法非常漫长。如何使它更容易？谢谢

- Edward

Answer 1

你应该能够使用 combine_first:

>>> df1.set_index('id').combine_first(df2.set_index('id'))
    first  second  third
id                      
1     NaN       1      1
2       1     NaN      0
3       1     NaN    NaN
4       0     NaN      1
5       0       0      1
6       1     NaN      0

- Alexander

Answer 2

如Alexander所提到的，应该使用combine_first。如果您想将id列保留为一列，则只需使用：

merged = df1.merge(df2)

- Joshua Kim