带有 WHERE 子句的 JOIN 的 Pandas 模拟 [英] Pandas analogue of JOIN with WHERE clause
问题描述
我正在 python 的 Pandas 中加入两个数据框(A 和 B).
I'm doing joining of two dataframe (A and B) in python's pandas.
目标是接收来自 B 的所有纯行(sql 模拟-在 A.client_id=B.client_id 上右连接 B，其中 A.client_id 为空)
The goal is to receive all the pure rows from B (sql analogue- right join B on A.client_id=B.client_id where A.client_id is null)
在 Pandas 中，我只知道这个操作是进行合并，但我不知道如何设置条件(where 子句):
In pandas all I know for this operation is to do merging but I don't know how to set up the conditions (where clause):
x=pd.merge(A,B,how='right',on=['client_id','client_id']
推荐答案
option 1indicator=True
A.merge(B, on='client_id', how='right', indicator=True) \
.query('_merge == "right_only"').drop('_merge', 1)
设置
A = pd.DataFrame(dict(client_id=[1, 2, 3], valueA=[4, 5, 6]))
B = pd.DataFrame(dict(client_id=[3, 4, 5], valueB=[7, 8, 9]))
结果
更多说明indicator=True 在合并结果中放置另一列，指示该行结果是来自左侧、右侧还是两者.
more explanation
indicator=True puts another column in the results of the merge that indicates whether that rows results are from the left, right, or both.
A.merge(B, on='client_id', how='outer', indicator=True)
所以，我只是使用 query 来过滤掉 right_only 指示符，然后删除该列.
So, I just use query to filter out the right_only indicator then drop that column.
选项 2
不是真正的合并.您可以再次使用 query 仅提取 B 的行，其中 'client_id' 不在 A
option 2
not really a merge. You can use query again to only pull rows of B where its 'client_id's are not in A
B.query('client_id not in @A.client_id')
或者说同样事情的等价方式(但更快)
or an equivalent way of saying the same thing (but faster)
B[~B.client_id.isin(A.client_id)]
这篇关于带有 WHERE 子句的 JOIN 的 Pandas 模拟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
