在 pandas 中按范围加入/合并的最佳方式 [英] Best way to join / merge by range in pandas

问题描述

我经常通过使用范围条件使用 Pandas 进行合并(连接).

I'm frequently using pandas for merge (join) by using a range condition.

例如，如果有 2 个数据帧:

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)

它们很大且大小大致相同(假设每个有 200 万条记录).

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.

与此同时，在 Pandas 中，唯一的方法(不使用我发现的循环)是在两个表中创建一个虚拟列，对其进行连接(相当于交叉连接)，然后过滤掉不需要的行.这听起来沉重而复杂:

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) & 在每个A 值上应用B 上的搜索函数.(x<=B.B_high)] 掩码，但听起来效率也很低，可能需要索引优化.

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

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 中不匹配的部分.

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屋！