join - Merge pandas dataframes where one value is between two others -

i need merge 2 pandas dataframes on identifier , condition date in 1 dataframe between 2 dates in other dataframe.

dataframe has date ("fdate") , id ("cusip"):

i need merge this dataframe b:

on a.cusip==b.ncusip , a.fdate between b.namedt , b.nameenddt.

in sql trivial, way can see how in pandas first merge unconditionally on identifier, , filter on 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'])] 

is best way this? seems better if 1 filter within merge avoid having potentially large dataframe after merge before filter has completed.

as say, pretty easy sql, why not in sql?

import pandas pd import sqlite3  #we'll use firelynx's tables: presidents = pd.dataframe({"name": ["bush", "obama", "trump"],                            "president_id":[43, 44, 45]}) terms = pd.dataframe({'start_date': pd.date_range('2001-01-20', periods=5, freq='48m'),                       'end_date': pd.date_range('2005-01-21', periods=5, freq='48m'),                       'president_id': [43, 43, 44, 44, 45]}) war_declarations = pd.dataframe({"date": [datetime(2001, 9, 14), datetime(2003, 3, 3)],                                  "name": ["war in afghanistan", "iraq war"]}) #make db in memory conn = sqlite3.connect(':memory:') #write tables terms.to_sql('terms', conn, index=false) presidents.to_sql('presidents', conn, index=false) war_declarations.to_sql('wars', conn, index=false)  qry = '''     select           start_date prestermstart,         end_date prestermend,         wars.date warstart,         presidents.name pres             terms join wars on         date between start_date , end_date join presidents on         terms.president_id = presidents.president_id     ''' df = pd.read_sql_query(qry, conn) 

df:

         prestermstart          prestermend             warstart  pres 0  2001-01-31 00:00:00  2005-01-31 00:00:00  2001-09-14 00:00:00  bush 1  2001-01-31 00:00:00  2005-01-31 00:00:00  2003-03-03 00:00:00  bush