python - 如何检查pandas中两个日期时间之间的日期时间

Question

我有第一个 pandas 数据框，如下所示

 trans_df
 code     price       date           time        product
 12023    71.23       01-01-2018     06:23:00    MS  
 12023    61          01-01-2018     07:56:00    HS
 12023    71.23       01-01-2018     08:34:00    MS
 12023    71.30       01-01-2018     06:03:00    MS  
 12023    61          01-01-2018     11:43:00    HS
 12023    71.23       01-01-2018     10:11:00    MS
 12023    71.23       01-01-2018     04:23:00    MS
 12023    72.23       02-01-2018     10:11:00    MS
 12023    72.23       02-01-2018     04:23:00    MS

现在，我有了主价格数据框，我可以从中检查价格是否在 trans_df 中设置。正确与否取决于交易日期和时间在 effective_date_from 之间和effective_date_to在master_price对于该特定产品

master_price
code  price  effective_date_from    effective_date_to  time_from   time_to  product
12023 71.23  01-01-2018             02-01-2018         06:00:00    05:59:00  MS     
12023 61     01-01-2018             02-01-2018         06:00:00    05:59:00  HS 
12023 72.23  02-01-2018             03-01-2018         06:00:00    05:59:00  MS  

所需的数据框是

 trans_df

 code     price       date           time        product  flag      actual_price
 12023    71.23       01-01-2018     06:23:00    MS       match     71.23    
 12023    61          01-01-2018     07:56:00    HS       match     61
 12023    71.23       01-01-2018     08:34:00    MS       match     71.23
 12023    71.30       01-01-2018     06:03:00    MS       mismatch  71.23 
 12023    61          01-01-2018     11:43:00    HS       match     61
 12023    71.23       01-01-2018     10:11:00    MS       match     71.23
 12023    71.23       01-01-2018     04:23:00    MS       nan       nan
 12023    72.23       02-01-2018     10:11:00    MS       match     72.23
 12023    72.23       02-01-2018     04:23:00    MS       match     72.23

Answer 1

用途:

#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