jim basquiat jim basquiat - 14 days ago 5
Python Question

python pandas - use timestamp to retrieve the data from another dataframe

I have a value

entry_time=2000-01-03 00:00:00


I have 2 dataframes:

timestamp price
2000-01-03 00:00:00 25
2000-01-03 00:30:00 -10
2000-01-03 01:00:00 100
2000-01-03 01:30:00 -690
2000-01-03 02:00:00 650
2000-01-03 02:30:00 320
2000-01-03 03:00:00 150
2000-01-03 03:30:00 160


and the second one "data2" (below):

exit_time answer
2000-01-03 01:00:00 true
2000-01-03 01:30:00 true
2000-01-03 02:00:00 true
2000-01-03 02:30:00 false
2000-01-03 03:00:00 true


I want to perform the following operation: for each row compute the max value between entry_time and exit_time

exit_time answer max
2000-01-03 01:00:00 true 100
2000-01-03 01:30:00 true 100
2000-01-03 02:00:00 true 650
2000-01-03 02:30:00 false 650
2000-01-03 03:00:00 true 650


To do so i need to retrieve the value at each timestamp from data to use it in data2. I tried many variant of such formula but it led me nowhere.

data2['max']= data[entry_time:data[data2.index.values]].price.max()


Thanks!

Answer

Seems like you need cummax():

data2.set_index('exit_time', inplace=True)    
data2['max'] = data[data.timestamp >= entry_time].set_index('timestamp').price.cummax()

data2
#                     answer    max
#          exit_time        
#2000-01-03 01:00:00    True    100
#2000-01-03 01:30:00    True    100
#2000-01-03 02:00:00    True    650
#2000-01-03 02:30:00    False   650
#2000-01-03 03:00:00    True    650