Pandas Join Two Dataframes According to Range and Date

I have two dataframes like this:

DATE        MAX_AMOUNT  MIN_AMOUNT  MAX_DAY  MIN_DAY    RATE
01/09/2022  20          15          10       5          0.01
01/09/2022  25          20          15       10         0.02
03/09/2022  30          10           5       3          0.03
03/09/2022  40          30          20       5          0.04
04/09/2022  10           5          10       1          0.05


ID  DATE        AMOUNT  DAY 
1   01/09/2022  18      7   
2   01/09/2022  22      11  
3   01/09/2022  30      20  
4   03/09/2022  35      10  
5   04/09/2022  35      10  

I want to bring the RATE values to the second df in accordance with the DATE. Also, the AMOUNT and DAY values in the relevant DATE must be within the appropriate range (MAX_AMOUNT & MIN_AMOUNT, MAX_DAY & MIN_DAY).

Desired output like this:

ID  DATE        AMOUNT  DAY RATE
1   01/09/2022  18      7   0.01
2   01/09/2022  22      11  0.02
3   01/09/2022  30      20  
4   03/09/2022  35      10  0.04
5   04/09/2022  35      10  

Could you please help me about this?

Answer

Use merge first with filter columns by Series.between and then use Series.map for RATE column with first matched ID – added DataFrame.drop_duplicates:

df = df2.merge(df1, on='DATE')
df = (df[df['AMOUNT'].between(df['MIN_AMOUNT'], df['MAX_AMOUNT']) & 
         df['DAY'].between(df['MIN_DAY'], df['MAX_DAY'])])

df2['RATE'] = df2['ID'].map(df.drop_duplicates('ID').set_index('ID')['RATE'])
print (df2)
   ID        DATE  AMOUNT  DAY  RATE
0   1  01/09/2022      18    7  0.01
1   2  01/09/2022      22   11  0.02
2   3  01/09/2022      30   20   NaN
3   4  03/09/2022      35   10  0.04
4   5  04/09/2022      35   10   NaN