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

Question

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!

akuiper · Accepted Answer · 2016-11-26 01:48:13Z

1

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

answered Nov 26, 2016 at 1:48

akuiper

216k33 gold badges362 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jim jarnac Over a year ago

This is really useful thx! Is there a way to retrieve the index value (timestamp) of the row returning cummax()?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related