如何在Pandas中执行带非相等条件的单步合并（类SQL连接）

假设我们有两个需要合并的DataFrame：

import pandas as pd

df1 = pd.DataFrame()
df1["key"] = ["a", "b", "c"]
df1["low"] = [0, 1, 2]
df1["high"] = [2, 4, 6]

df2 = pd.DataFrame()
df2["key"] = ["a", "a", "a", "b", "b", "c"]
df2["value"] = [1, 2, 3, 2, 5, 0]

print(f"df1:\n{df1}")
print(f"df2:\n{df2}")

输出：

df1:
  key  low  high
0   a    0     2
1   b    1     4
2   c    2     6
df2:
  key  value
0   a      1
1   a      2
2   a      3
3   b      2
4   b      5
5   c      0

如果我想要合并这两个DataFrame，只保留key匹配且value在low和high之间的行，我知道可以分两步完成：

df = df1.merge(df2, how="inner", on="key")
df = df.loc[(df["value"] >= df["low"]) & (df["value"] <= df["high"])]
print(f"df:\n{df}")

输出：

df:
  key  low  high  value
0   a    0     2      1
1   a    0     2      2
3   b    1     4      2

但如果DataFrame很大的话，这种方法可能不实用，因为第一步的合并操作可能会耗时很久（详情见下文）。

在SQL中，我们可以很轻松地用如下查询实现这种连接：

SELECT
    df1.key,
    df1.low,
    df1.high,
    df2.value
FROM df1
INNER JOIN df2
ON
    df1.key = df2.key AND
    df2.value BETWEEN df1.low AND df1.high

有没有办法在Python中用一步操作完成这种合并？

编辑1：我需要的是不需要安装pandas之外的额外依赖（比如pyjanitor或其他模块）的解决方案。

编辑2：这里的“一步”指的是用单个pandas操作完成合并，而不是把两个操作（比如.merge和.loc）写在同一行。

为什么不直接链式调用Pandas操作？ #

有些人会给出类似这样的解决方案：

df = df1.merge(df2, how="inner", on="key").loc[lambda r: (r["value"] >= r["low"]) & (r["value"] <= r["high"])]

这个方法虽然能运行，但在数据量较大时并不实用——因为它会先合并两个DataFrame，再进行筛选操作。如果DataFrame很大，df1.merge(df2, how="inner", on="key")的计算成本会非常高。

举个例子，我们给原来的df1和df2添加更多行：

import pandas as pd
import random
random.seed(42)

add_rows = 10

df1 = pd.DataFrame()
df1["key"] = ["a", "b", "c"] + random.choices(["a", "b", "c"], k=add_rows)
df1["low"] = [0, 1, 2] + list(range(100, 100 + add_rows))
df1["high"] = df1["low"] + 2

df2 = pd.DataFrame()
df2["key"] = ["a", "a", "a", "b", "b", "c"] + random.choices(["a", "b", "c"], k=add_rows)
df2["value"] = [1, 2, 3, 2, 5, 0] + list(range(-add_rows, 0))

print(f"df1:\n{df1}")
print(f"df2:\n{df2}")

输出：

df1:
   key  low  high
0    a    0     2
1    b    1     3
2    c    2     4
3    b  100   102
4    a  101   103
5    a  102   104
6    a  103   105
7    c  104   106
8    c  105   107
9    c  106   108
10   a  107   109
11   b  108   110
12   a  109   111
df2:
   key  value
0    a      1
1    a      2
2    a      3
3    b      2
4    b      5
5    c      0
6    a    -10
7    b     -9
8    a     -8
9    a     -7
10   b     -6
11   b     -5
12   a     -4
13   b     -3
14   c     -2
15   a     -1

注意到df2的value列全是负数，而df1的low和high列全是正数，所以不管添加多少行，最终结果始终只有3行。

• 当添加10行时，df1.merge(df2, how="inner", on="key")会生成74行，整个链式操作在我的机器上耗时约0.005秒。
• 当添加1000行时，合并后的DataFrame有33.6万行，最终结果（保持不变）耗时约0.03秒。
• 当添加10000行时，合并后的DataFrame有3330万行，操作耗时1.03秒，甚至可能导致笔记本崩溃。

我们需要的是一种在合并过程中就完成筛选的方法，避免生成不必要的中间大表。

备注：内容来源于stack exchange，提问作者gasbag_1