New feature/enhancement request: Merge dataframes where one value is between two others

[stoffprof]

In contrast to SQL, it is not currently possible to merge dataframes with inequality constraints. Could this be added?

As an example (which I previously posted on StackOverflow), I need to merge two pandas dataframes on an identifier and a condition where a date in one dataframe is between two dates in the other dataframe.

Dataframe A has a date ("fdate") and an ID ("cusip"):

I need to merge this with this dataframe B:

In SQL this would be trivial, but the only way I can see how to do this in pandas is to first merge unconditionally on the identifier, and then filter on the date condition:

df = pd.merge(A, B, how='inner', left_on='cusip', right_on='ncusip')
df = df[(df['fdate']>=df['namedt']) & (df['fdate']<=df['nameenddt'])]

The problem with this approach is that after the merge, but before the filtering step, df can become unnecessarily large. Presumably this could be avoided if the filtering were done as part of the merge process.

[samukweku]

is there still an interest for a conditional join in pandas? asking if it is something i can pick up and work on?

[abubelinha]

Yes @samukweku
I am interested in a conditional join feature where the joining value in one side is not equal but between two values in the other side

[samukweku]

there are a number of options (pd.IntervalIndex is one efficient one), and conditional_join, which is generic, efficient as well and covers more use cases than pd.IntervalIndex. My hope is that this can be built into pandas itself, as it would be generally useful for the community. @abubelinha have a go at conditional_join, there are some examples here from stackoverflow

[samukweku]

@mroeschke I am interested in implementing this (a range join) in pandas - a possible syntax:

df1.range_join([('col1', '>', 'col2'), ('col3', '<', 'col4')])

or we create a join class?

df1.range_join([col('a') < col('b'), col('c') > col('d')])

any thoughts? better approach? implementation will be based on this blogpost. I have implemented it in pyjanitor; i do feel the broader community may prefer it within Pandas (where possible optimisations/improvements are possible)