Referencing time and (time+10 seconds) to calc normalized price return in Pandas Dataframe

Question

I am trying to normalize price at a certain point in time with respect to price 10 seconds later using this formula: ((price(t+10seconds) – price(t)) / price(t) ) / spread(t)

Both price and spread are columns in my dataframe. And I have indexed my dataframe by timestamp (pd.datetime object) because I figured that would make calculating price(t+10sec) easier.

What I've tried so far:

pos['timestamp'] = pd.to_datetime(pos['timestamp'])
pos.set_index('timestamp')

def normalize_data(pos):
    
    t0 = pd.to_datetime('2021-10-27 09:30:13.201')
    x = pos['mid_price'] 
    y = ((x[t0 + pd.Timedelta('10 sec')] - x)/x) / (spread)
    return y

pos['norm_price'] = normalize_data(pos)

this gives me an error because I'm indexing x[t0+pd.Timedelta('10sec')] but not the other x's in the equation. I also don't think I'm using pd.Timedelta or the x[t0+pd.Time...] correctly and unsure of how to fix all this/define a better function.

Any input would be much appreciated

dataframe

Answer 1

Your problem is here:

pos.set_index('timestamp')

This line of code will return a new dataframe, and leave your original dataframe unchanged. So, your function normalize_data is working on the original version of pos, which does not have the index you want, and neither will x. Change your code to this:

pos = pos.set_index('timestamp')

And that should get things working.