Find the closest date with conditions-CodePudding

There are two pandas tables, each containing two columns. In the first time, there is also a heart rhythm. Second time is the systolic pressure.

Write the code that creates a third table, in which for each blood pressure measurement, the same line contains the time and value of the nearest heart rate measurement, if it was done necessarily before the blood pressure measurement and not earlier than 15 minutes ago

I tried to solve it with truncate and iloc but I didn't succeed.

import pandas as pd
df_hr = pd.DataFrame({'time': 
    [datetime.datetime(2022,1,1,7,40), 
     datetime.datetime(2022,1,1,9,50), 
     datetime.datetime(2022,1,1,10,1)],
    'hr':
    [60, 90, 100]}).set_index('time')

df_bp = pd.DataFrame({'time': 
    [datetime.datetime(2022,1,1,10),
     datetime.datetime(2022,1,1,8)],
    'bp': [140, 120]}).set_index('time')

CodePudding user response：

Lets do merge_asof with direction='backward' and tolerance of 15min:

pd.merge_asof(
    df_bp.sort_index(), 
    df_hr.sort_index(), 
    on='time', 
    direction='backward',
    tolerance=pd.Timedelta('15min'), 
)

Note: The keyword argument direction=backward selects the last row in the right DataFrame whose 'on' key is less than or equal to the left's key

Result

                 time   bp    hr
0 2022-01-01 08:00:00  120   NaN
1 2022-01-01 10:00:00  140  90.0