Return value based on date criteria within start and end date range and ID in another data frame? (pandas and python)

Question

I have two dataframes, where the df1 contains the column Data and Praca_ID, where I need to look in the df2, who owns DataInicial DataFinal Praca_ID and Tarifa (column to be returned.)

The example of jupyterlab in my github will detail well the problem:

Note line In [46]: which makes the code:

# Procurando tarifa equivalente ao tráfego da data a ser analisada:

dfTrafegoPracas['Tarifa'] = np.where((dfTarifas.Praca == dfTrafegoPracas.Praca) 
& (dfTarifas.VigenciaInicial<=dfTrafegoPracas.Data) & 
(dfTarifas.VigenciaFinal>=dfTrafegoPracas.Data), dfTarifas['Tarifa'], '')

I commented on this step because I can’t make it work..

Answer 1

I managed to solve!

The merge also worked, but was inefficient in performance and processing.

I installed and imported the pandassql,:

 sqlcode = '''
select dfTrafegoPracas.Data,
dfTrafegoPracas.Praca,
dfTrafegoPracas.Sentido,
dfTrafegoPracas.Pista,
dfTrafegoPracas.Categoria,
dfTrafegoPracas.Perfil,
dfTrafegoPracas.Trafego,
dfTrafegoPracas.Multiplicador,
dfTrafegoPracas.Eixos,
dfTrafegoPracas.InitialPath,
dfTarifas.Tarifa
from dfTrafegoPracas
inner join dfTarifas on dfTrafegoPracas.Praca=dfTarifas.Praca
where dfTrafegoPracas.Data >= dfTarifas.VigenciaInicial and dfTrafegoPracas.Data <= dfTarifas.VigenciaFinal
'''


dfTrafegoPracas = ps.sqldf(sqlcode,locals())