在pandas中按范围加入/合并的最佳方法

Question

我经常通过使用范围条件将熊猫用于合并（联接）。

例如，如果有2个数据框：

A （A_id，A_value）

B （B_id，B_low，B_high，B_name）

它们很大，大小大致相同（假设每个记录有2M条）。

我想在A和B之间进行内部联接，因此A_value将在B_low和B_high之间。

使用SQL语法将是：

SELECT *
FROM A,B
WHERE A_value between B_low and B_high

那真的很容易，简短而有效。

同时，在熊猫中，唯一的方法（不使用我发现的循环）是通过在两个表中创建一个虚拟列，对其进行联接（等同于交叉联接），然后过滤掉不需要的行。这听起来很繁复：

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)]掩码在A值上的每个值上应用B上的搜索功能，但这听起来效率也不高，可能需要优化索引。

有没有更优雅和/或更有效的方法来执行此操作？

Answer 1

设置
考虑数据帧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。

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