Find the closest datetime location in a pandas dataframe, subtract 500 milliseconds from it and store it in a new dataframe

Question

timestamp of failure :

ts_failure = datetime.datetime(2019, 5, 15, 19, 48, 2, 495)

Closest timestamp to the timestamp of failure in the dataframe is (2019, 5, 15, 19, 48, 2, 479)

Dataframe:

Timestamp  
2019-05-15 19:48:02.477  
2019-05-15 19:48:02.478  
2019-05-15 19:48:02.479  

I want to find the closest timestamp near the failure timestamp and subtract 500 milliseconds using time delta and print out the new data frame which has timestamps from (t-500)ms to closest time to the time of failure.

I tried to find the nearest index using iloc but received an obvious error saying I cannot compare timestamp and int. df.index.get_loc(ts_failure, method='nearest')

TypeError: '<' not supported between instances of 'Timestamp' and 'int'

Cannot take the abs difference since I want the closest timestamp in the range of milliseconds.

df.iloc[(df['Timestamp']-ts_failure).abs().argsort()[:]]

Would be grateful for any help :)

Answer 1

You should change the representation of ts_failure:

ts_failure = pd.to_datetime(pd.Timestamp(2019, 5, 15, 19, 48, 2, 495000)) 
df.iloc[df.set_index('Timestamp').index.get_loc(ts_failure, method='nearest')]

Timestamp   2019-05-15 19:48:02.479
Name: 2, dtype: datetime64[ns]

Please note, that pandas timestamp is always in nano seconds. So multiply the last value of your timestamp by 1000.

Answer 2

Try this:

ts_failure = datetime.datetime(2019, 5, 15, 19, 48, 2, 495)
nearest_idx = np.searchsorted(df['Timestamp'], ts_failure) - 1
nearest_time = df.iloc[nearest_idx]['Timestamp']
t_500ms = nearest_time - pd.Timedelta(milliseconds = 500)
df.index = df['Timestamp']
df2 = df.loc[t_500ms: nearest_time]