Best way to join / merge by range in pandas

user_3861108 0
Viewed 23194

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)

B (B_id,B_low, B_high, B_name)

which are big and approximately of the same size (let's say 2M records each).

I would like to make an inner join between A and B, so A_value would be between B_low and B_high.

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)]

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?

pandas join numpy python
6 Answers

conditional_join from pyjanitor may be helpful in the abstraction/convenience;:

# pip install pyjanitor
import pandas as pd
import janitor

inner join

A.conditional_join(B, 
                   ('A_value', 'B_low', '>='), 
                   ('A_value', 'B_high', '<=')
                  )

   A_id  A_value  B_id  B_low  B_high
0     0        5     0      0      10
1     3       35     1     30      40
2     3       35     2     30      50
3     4       45     2     30      50

left join

A.conditional_join(
       B, 
       ('A_value', 'B_low', '>='), 
       ('A_value', 'B_high', '<='), 
       how = 'left'
    )

    A_id  A_value  B_id  B_low  B_high
0      0        5   0.0    0.0    10.0
1      1       15   NaN    NaN     NaN
2      2       25   NaN    NaN     NaN
3      3       35   1.0   30.0    40.0
4      3       35   2.0   30.0    50.0
5      4       45   2.0   30.0    50.0
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
user_71757130

I know this is an old question but for newcomers there is now the pandas.merge_asof function that performs join based on closest match.

In case you want to do a merge so that a column of one DataFrame (df_right) is between 2 columns of another DataFrame (df_left) you can do the following:

df_left = pd.DataFrame({
    "time_from": [1, 4, 10, 21],
    "time_to": [3, 7, 15, 27]
})

df_right = pd.DataFrame({
    "time": [2, 6, 16, 25]
})

df_left

  time_from time_to
0        1       3
1        4       7
2       10      15
3       21      27


df_right

  time
0    2
1    6
2   16
3   25

First, find matches of the right DataFrame that are closest but largest than the left boundary (time_from) of the left DataFrame:

merged = pd.merge_asof(
    left=df_1,
    right=df_2.rename(columns={"time": "candidate_match_1"}),
    left_on="time_from",
    right_on="candidate_match_1",
    direction="forward"
)

merged

  time_from time_to candidate_match_1
0        1       3                 2
1        4       7                 6
2        10      15               16
3        21      27               25

As you can see the candidate match in index 2 is wrongly matched, as 16 is not between 10 and 15.

Then, find matches of the right DataFrame that are closest but smaller than the right boundary (time_to) of the left DataFrame:

merged = pd.merge_asof(
    left=merged,
    right=df_2.rename(columns={"time": "candidate_match_2"}),
    left_on="time_to",
    right_on="candidate_match_2",
    direction="backward"
)

merged

  time_from time_to candidate_match_1   candidate_match_2
0        1        3                2                   2
1        4        7                6                   6
2        10      15               16                   6
3        21      27               25                  25

Finally, keep the matches where the candidate matches are the same, meaning that the value of the right DataFrame are between values of the 2 columns of the left DataFrame:

merged["match"] = None
merged.loc[merged["candidate_match_1"] == merged["candidate_match_2"], "match"] = \
    merged.loc[merged["candidate_match_1"] == merged["candidate_match_2"], "candidate_match_1"]

merged

  time_from time_to candidate_match_1   candidate_match_2   match
0        1       3                 2                   2       2
1        4       7                 6                   6       6
2        10     15                16                   6    None
3        21     27                25                   25     25
user_42590110
Related