How To Check Where Datetime Is In Between Two Datetimes In Pandas

I have first pandas dataframe like following trans_df code price date time product 12023 71.23 01-01-2018 06:23:00 MS 12023 61

Solution 1:

Use:

#convert dates with times to datetimes
master_price['effective_date_from'] = (pd.to_datetime(master_price['effective_date_from'], 
                                       format='%d-%m-%Y') + 
                                      pd.to_timedelta(master_price['time_from']))
master_price['effective_date_to'] = (pd.to_datetime(master_price['effective_date_to'], 
                                     format='%d-%m-%Y') + 
                                     pd.to_timedelta(master_price['time_to']))
trans_df['date'] = (pd.to_datetime(trans_df['date'], format='%d-%m-%Y') +
                    pd.to_timedelta(trans_df['time']))

#join together and filter between 
df = trans_df.merge(master_price, on=['code','product'], how='left')
df = df[df.date.between(df.effective_date_from, df.effective_date_to)]

#add only filterd rows to original
df = trans_df.merge(df, on=['code','product','date','time'], how='left')
cols = ['effective_date_from', 'effective_date_to', 'time_to','time_from','price_x']
df = df.drop(cols, axis=1)
#first test missing values then match.mismatch
df['flag'] = np.select([df['price_y'].isnull(), 
                        df['price_y'] == df['price']], 
                       [np.nan, 'match'], default='mismatch')
df = df.rename(columns={'price_y':'actual_price'})
print (df)
    code  price                date      time product  actual_price      flag
0  12023  71.23 2018-01-01 06:23:00  06:23:00      MS         71.23     match
1  12023  61.00 2018-01-01 07:56:00  07:56:00      HS         61.00     match
2  12023  71.23 2018-01-01 08:34:00  08:34:00      MS         71.23     match
3  12023  71.30 2018-01-01 06:03:00  06:03:00      MS         71.23  mismatch
4  12023  61.00 2018-01-01 11:43:00  11:43:00      HS         61.00     match
5  12023  71.23 2018-01-01 10:11:00  10:11:00      MS         71.23     match
6  12023  71.23 2018-01-01 04:23:00  04:23:00      MS           NaN       nan
7  12023  72.23 2018-01-02 10:11:00  10:11:00      MS         72.23     match
8  12023  72.23 2018-01-02 04:23:00  04:23:00      MS         71.23  mismatch