Extracting a value 5 minutes away from the current row

Question

I have the following DataFrame

import pandas as pd, numpy as np
pd.DataFrame({'Instrument':['A2M','A2M','A2M','A2M','A2M','A2M','A2M','A2M','A2M','A2M'], 
'Date':['2020-01-02','2020-01-02','2020-01-02','2020-01-02', '2020-01-02','2020-01-02','2020-01-02','2020-01-02','2020-01-02','2020-01-02'],
'Time':['09:59:50.024','09:59:50.623','10:00:15.124','10:00:15.148','10:00:42.552','10:04:49.782','10:05:14.896','10:05:41.719','10:05:45.050','10:08:19.170'], 
'midquote': [0.0,0.0,14.145,14.145,14.245,14.360,14.305,14.235,14.295,14.285]})

I want to extract the midquote 5 minutes after the time of each row. It's impossible to get exact 5 minutes difference so the time closest to 5 minutes (must be less than) is preferable.

Desired output

pd.DataFrame({'Instrument':['A2M','A2M','A2M','A2M','A2M','A2M','A2M','A2M','A2M','A2M'], 
'Date':['2020-01-02','2020-01-02','2020-01-02','2020-01-02', '2020-01-02','2020-01-02','2020-01-02','2020-01-02','2020-01-02','2020-01-02'],
'Time':['09:59:50.024','09:59:50.623','10:00:15.124','10:00:15.148','10:00:42.552','10:04:49.782','10:05:14.896','10:05:41.719','10:05:45.050','10:08:19.170'], 
'midquote': [0.0,0.0,14.145,14.145,14.245,14.360,14.305,14.235,14.295,14.285], 
'timestamp':['2020-01-02 09:59:50.024000','2020-01-02 09:59:50.623000','2020-01-02 10:00:15.124000','2020-01-02 10:00:15.148000','2020-01-02 10:00:42.552000','2020-01-02 10:04:49.782000','2020-01-02 10:05:14.896000','2020-01-02 10:05:41.719000','2020-01-02 10:05:45.050000','2020-01-02 10:08:19.170000'],
'timestamp_5_min':['2020-01-02 10:04:49.782000','2020-01-02 10:04:49.782000','2020-01-02 10:05:14.896000','2020-01-02 10:05:14.896000','2020-01-02 10:05:41.719000','nan','nan','nan','nan','nan'],
 'midquote_5_min':[14.360,14.305,14.235,14.295,14.285,'nan','nan','nan','nan','nan']})

I tried the following code but it didn't work

df['timestamp'] = pd.to_datetime(df.Date + ' ' + df.Time)
df.sort_values(by=['timestamp'], inplace=True)
df['midquote'].fillna(0, inplace=True)
mid = df[['Instrument','timestamp','midquote']]
mid['5_min_timestamp'] = mid.timestamp
mid.rename(columns={'midquote':'5_min_midquote'}, inplace=True)
pd.merge_asof(df, mid, on='timestamp', by=['Instrument'], tolerance=pd.Timedelta("5min"), direction='forward', allow_exact_matches=False)

Also, if I did not sort_values, it will give an error of left keys must be sorted

Please help.

Thank you.

user_6366770 · Answer

The crux of the problem was using .searchsorted to to return the index + 1 of the nearest date/time between the column Time and the column that is Time + 5 minutes called timestamp_5_min. From there, I did some messy merging, column renaming and column dropping, which could probably be cleaner but gets the job done.

input:

import pandas as pd, numpy as np, datetime as dt

df = pd.DataFrame({'Instrument':['A2M','A2M','A2M','A2M','A2M','A2M','A2M','A2M','A2M','A2M'], 
'Date':['2020-01-02','2020-01-02','2020-01-02','2020-01-02', '2020-01-02','2020-01-02','2020-01-02','2020-01-02','2020-01-02','2020-01-02'],
'Time':['09:59:50.024','09:59:50.623','10:00:15.124','10:00:15.148','10:00:42.552','10:04:49.782','10:05:14.896','10:05:41.719','10:05:45.050','10:08:19.170'], 
'midquote': [0.0,0.0,14.145,14.145,14.245,14.360,14.305,14.235,14.295,14.285]})

code:

df['Date'] = pd.to_datetime(df['Date'])
df['Time'] = pd.to_datetime(df['Time'])
df['timestamp_5_min'] = pd.to_datetime(df['Time'] + pd.Timedelta(minutes=5))
df['timestamp_5_min_new'] = df['Time'].searchsorted(df['timestamp_5_min'])-1
df1 = df[['Time', 'midquote']]
df2 = pd.merge(df,df1,how='left',left_on='timestamp_5_min_new', right_index=True)
df2.loc[df2['timestamp_5_min_new'] == max(df2['timestamp_5_min_new']), ['timestamp_5_min_new', 'Time_y', 'midquote_y']] = np.nan
df2 = df2.drop(['timestamp_5_min_new','timestamp_5_min', 'midquote_x'], axis=1).rename({'Time_x': 'Time', 'Time_y': 'timestamp_5_min', 'midquote_y' : 'midquote'}, axis=1)
df2

output:

    Instrument Date Time                    timestamp_5_min         midquote
0   A2M 2020-01-02  2020-07-16 09:59:50.024 2020-07-16 10:04:49.782 14.360
1   A2M 2020-01-02  2020-07-16 09:59:50.623 2020-07-16 10:04:49.782 14.360
2   A2M 2020-01-02  2020-07-16 10:00:15.124 2020-07-16 10:05:14.896 14.305
3   A2M 2020-01-02  2020-07-16 10:00:15.148 2020-07-16 10:05:14.896 14.305
4   A2M 2020-01-02  2020-07-16 10:00:42.552 2020-07-16 10:05:41.719 14.235
5   A2M 2020-01-02  2020-07-16 10:04:49.782 NaT                     NaN
6   A2M 2020-01-02  2020-07-16 10:05:14.896 NaT                     NaN
7   A2M 2020-01-02  2020-07-16 10:05:41.719 NaT                     NaN
8   A2M 2020-01-02  2020-07-16 10:05:45.050 NaT                     NaN
9   A2M 2020-01-02  2020-07-16 10:08:19.170 NaT                     NaN

Answer #2 with Grouping by Instrument. This makes it a little bit more challenging, but the below gets the job done.

I have changed some of the sample data, so that there are two categories now, and I have changed the time difference requirement to 25 seconds.

Step #1 - Use .groupby, .transform 'lambda x and searchsorted together in order to find the index of the nearest time value closest to 25 seconds (the parameter passed to ppd.Timedelta) for this exercise.

Step #2 - The line where I create the dictionary a is essentially used in order to find the max index by group for Instrument which is 3 and 9 respectively.

Step #3 - From there, create a temporary that shows the max index for each instrument and any row where the max index is > than the timestamp_5_min_new column. This essentially means there are no rows after that group that are within 25 seconds, so they are turned to NaN in the np.where() statement. From there, just clean up the columns and column names.

input:

import pandas as pd, numpy as np, datetime as dt
df = pd.DataFrame({'Instrument':
                   ['B3M','B3M','B3M','B3M','A2M','A2M','A2M','A2M','A2M','A2M'],
                   'Date':['2020-01-02','2020-01-02','2020-01-02','2020-01-02', '2020-01-02',
                           '2020-01-02','2020-01-02','2020-01-02','2020-01-02','2020-01-02'],
                   'Time':['09:59:50.024','09:59:50.623','10:00:15.124','10:00:15.148','10:00:42.552',
                           '10:04:49.782','10:05:14.896','10:05:41.719','10:05:45.050','10:08:19.170'],
                   'midquote': [0.0,0.0,14.145,14.145,14.245,14.360,14.305,14.235,14.295,14.285]})
df['Date'] = pd.to_datetime(df['Date'])
df['Time'] = pd.to_datetime(df['Time'])

code:

df['timestamp_5_min'] = pd.to_datetime(df['Time'] + pd.Timedelta(seconds=25))
df['timestamp_5_min_new'] = df.groupby(['Instrument'])['Time'].transform(lambda x: x).searchsorted(df.groupby(['Instrument'])['timestamp_5_min'].transform(lambda x: x))
a = {v: k for k, v in df['Instrument'].drop_duplicates(keep='last').to_dict().items()}
df['temp'] = df['Instrument'].map(a)
df1 = df[['Time', 'midquote']]
df2 = pd.merge(df,df1,how='left',left_on='timestamp_5_min_new', right_index=True)
df2['Time_y'] = np.where((df2['timestamp_5_min_new'] > df2['temp']), np.datetime64('NaT'), df2['timestamp_5_min'])
df2['midquote_y'] = np.where((df2['timestamp_5_min_new'] > df2['temp']), np.nan, df2['midquote_y'])
df2 = df2.drop(['timestamp_5_min_new','timestamp_5_min', 'midquote_x', 'temp'], axis=1).rename({'Time_x': 'Time', 'Time_y': 'timestamp_5_min', 'midquote_y' : 'midquote'}, axis=1)
df2

output:

    Instrument  Date    Time                    timestamp_5_min       midquote
0   B3M     2020-01-02  2020-07-17 09:59:50.024 2020-07-17 10:00:15.024 14.145
1   B3M     2020-01-02  2020-07-17 09:59:50.623 NaT                 NaN
2   B3M     2020-01-02  2020-07-17 10:00:15.124 NaT                 NaN
3   B3M     2020-01-02  2020-07-17 10:00:15.148 NaT                 NaN
4   A2M     2020-01-02  2020-07-17 10:00:42.552 2020-07-17 10:01:07.552 14.360
5   A2M     2020-01-02  2020-07-17 10:04:49.782 2020-07-17 10:05:14.782 14.305
6   A2M     2020-01-02  2020-07-17 10:05:14.896 2020-07-17 10:05:39.896 14.235
7   A2M     2020-01-02  2020-07-17 10:05:41.719 2020-07-17 10:06:06.719 14.285
8   A2M     2020-01-02  2020-07-17 10:05:45.050 2020-07-17 10:06:10.050 14.285
9   A2M     2020-01-02  2020-07-17 10:08:19.170 NaT                 NaN

Extracting a value 5 minutes away from the current row

1 Answers