比较两个数据框并在python pandas中追加数据

Question

我有一个示例数据框df1

id  user_id     name             email       
1     1        John         [e-mail]
2     2        Alves        [e-mail]
3     3        Kristein     [e-mail]
4     4        James        [e-mail]

第二数据帧df2

id      user       user_email_1            user_email_2         status
1      Sanders     [e-mail]                          active
2      Alves       [e-mail]   [e-mail]     active
3      Micheal     [e-mail]                          active
4      James       [e-mail]                            delete

如何将df2的状态数据添加到df1

df1的user_id和df2的id

df1的name和df2的user

df1的email与user_email_1匹配或df2的user_email_2匹配并删除不匹配的记录?

期望结果df1:

id   user_id    name       email                status
2      2        Alves     [e-mail]     active
4      4        James     [e-mail]     delete

例如:

由于df1中的[e-mail]与user_email_2匹配，因此附加了状态数据。

Answer 1

您应该使用merge

df1.merge(df2.reset_index(), how='inner', left_on=['name', 'email', 'id'], right_on=['user', 'user_email', 'index'])

Answer 2

重新安排你的数据框df2，只得到一个user_email列，然后合并两个数据框，并保留想要的列:

df2 = df2.set_index(['id', 'user', 'status']).stack() 
.rename('user_email').reset_index()
out = pd.merge(df1, df2, left_on=['user_id', 'name', 'email'],
right_on=['id', 'user', 'user_email'],
suffixes=('', '2')) 
[['id', 'user_id', 'name', 'email', 'status']]

>>> out
id  user_id   name              email  status
0   2        2  Alves  [e-mail]  active
1   4        4  James  [e-mail]  delete

在哪里使用列user_email_2

列user_email_1和user_email_2被堆叠成一列。转换之后，df2看起来像:

>>> df2
id     user  status       level_3            user_email
0   1  Sanders  active  user_email_1   [e-mail]
1   2    Alves  active  user_email_1  [e-mail]
2   2    Alves  active  user_email_2     [e-mail]
3   3  Micheal  active  user_email_1   [e-mail]
4   4    James  delete  user_email_1     [e-mail]