[Python] Is there a way to merge Pandas DataFrames on a condition other than key equality?...

Suppose we have two DataFrames to be merged:

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", "b", "b", "c"]
df2["value"] = [1, 3, 2, 5, 0]

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


Output:

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


If I want to merge these DataFrames such that I only keep the rows where "key" matches and "value" is between "low" and "high", I know how to do that in two steps:

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


Output:

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


But that approach might not be practical if the DataFrames are large, because merging the DataFrames in the first step could take a long time.

In SQL we can easily do the join with a query like this:

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


Is there a way to execute this merge in Python in one step?

Edit 1: I'm looking for a solution without installing any additional dependencies, such as pyjanitor or other modules besides pandas.

Edit 2: By "one step" I mean that I want to execute the merge with a single pandas operation, not two operations (like .merge and .loc) combined on a single line.

Continue reading...