CODEWITHSUNDEEP
pythonpandas
jim jarnac
jim jarnac

Reputation: 5152

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!

Upvotes: 0

Views: 107

Answers (1)

akuiper
akuiper

Reputation: 214927

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

Upvotes: 1

Related Questions