在 pandas 中按范围加入/合并的最佳方法 [英] Best way to join / merge by range in pandas
问题描述
我经常使用大熊猫通过范围条件进行合并(联接).
I'm frequently using pandas for merge (join) by using a range condition.
例如,如果有两个数据帧:
For instance if there are 2 dataframes:
A (A_id,A_value)
A (A_id, A_value)
B (B_id,B_low,B_high,B_name)
B (B_id,B_low, B_high, B_name)
大并且大约相同的大小(假设每个记录有2M).
which are big and approximately of the same size (let's say 2M records each).
我想在A和B之间建立内部联接,因此A_value会在B_low和B_high之间.
I would like to make an inner join between A and B, so A_value would be between B_low and B_high.
使用将如下的SQL语法:
Using SQL syntax that would be:
SELECT *
FROM A,B
WHERE A_value between B_low and B_high
那真的很容易,简短而有效.
and that would be really easy, short and efficient.
与此同时,在熊猫中,唯一的方法(不使用我发现的循环)是通过在两个表中创建一个虚拟列,对其进行联接(等效于交叉联接),然后过滤掉不需要的行.听起来很繁琐:
Meanwhile in pandas the only way (that's not using loops that I found), is by creating a dummy column in both tables, join on it (equivalent to cross-join) and then filter out unneeded rows. That sounds heavy and complex:
A['dummy'] = 1
B['dummy'] = 1
Temp = pd.merge(A,B,on='dummy')
Result = Temp[Temp.A_value.between(Temp.B_low,Temp.B_high)]
我遇到的另一种解决方案是使用B[(x>=B.B_low) & (x<=B.B_high)]
掩码在B的每个值上应用搜索功能,但这听起来效率也不高,可能需要优化索引.
Another solution that I had is by applying on each of A value a search function on B by usingB[(x>=B.B_low) & (x<=B.B_high)]
mask, but it sounds inefficient as well and might require index optimization.
是否有更优雅和/或更有效的方法来执行此操作?
Is there a more elegant and/or efficient way to perform this action?
推荐答案
设置
考虑数据帧A
和B
Setup
Consider the dataframes A
and B
A = pd.DataFrame(dict(
A_id=range(10),
A_value=range(5, 105, 10)
))
B = pd.DataFrame(dict(
B_id=range(5),
B_low=[0, 30, 30, 46, 84],
B_high=[10, 40, 50, 54, 84]
))
A
A_id A_value
0 0 5
1 1 15
2 2 25
3 3 35
4 4 45
5 5 55
6 6 65
7 7 75
8 8 85
9 9 95
B
B_high B_id B_low
0 10 0 0
1 40 1 30
2 50 2 30
3 54 3 46
4 84 4 84
numpy
最简单的方法是使用numpy
广播.
我们寻找A_value
的每个实例都大于或等于B_low
,而同时A_value
小于或等于B_high
.
numpy
The ✌easiest✌ way is to use numpy
broadcasting.
We look for every instance of A_value
being greater than or equal to B_low
while at the same time A_value
is less than or equal to B_high
.
a = A.A_value.values
bh = B.B_high.values
bl = B.B_low.values
i, j = np.where((a[:, None] >= bl) & (a[:, None] <= bh))
pd.DataFrame(
np.column_stack([A.values[i], B.values[j]]),
columns=A.columns.append(B.columns)
)
A_id A_value B_high B_id B_low
0 0 5 10 0 0
1 3 35 40 1 30
2 3 35 50 2 30
3 4 45 50 2 30
为解决评论并提供类似于左联接的内容,我添加了A
不匹配的部分.
To address the comments and give something akin to a left join, I appended the part of A
that doesn't match.
pd.DataFrame(
np.column_stack([A.values[i], B.values[j]]),
columns=A.columns.append(B.columns)
).append(
A[~np.in1d(np.arange(len(A)), np.unique(i))],
ignore_index=True, sort=False
)
A_id A_value B_id B_low B_high
0 0 5 0.0 0.0 10.0
1 3 35 1.0 30.0 40.0
2 3 35 2.0 30.0 50.0
3 4 45 2.0 30.0 50.0
4 1 15 NaN NaN NaN
5 2 25 NaN NaN NaN
6 5 55 NaN NaN NaN
7 6 65 NaN NaN NaN
8 7 75 NaN NaN NaN
9 8 85 NaN NaN NaN
10 9 95 NaN NaN NaN
这篇关于在 pandas 中按范围加入/合并的最佳方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!