Find highest and lowest bar number from resample

user_11436736 0
Viewed 58

My dataframe contains 30 minute OHLC data. I need to find out which bar had the highest value, and which one had the lowest value for each day. So for example:

28/05/2018 = the highest value was 1.16329 and it occurred on bar 6 for that day.

29/05/2018 = highest value was 1.159 occuring on bar 2

I have used the following formula which resamples into daily data but then I lose the information on what bar of the day the high and low was acheived.

d3 = df.resample('D').agg({'Open':'first', 'High':'max', 'Low':'min', 'Close':'last'})

Date         Time       Open    High    Low     Last
28/05/2018   14:30:00   1.16167 1.16252 1.1613  1.16166
28/05/2018   15:00:00   1.16166 1.16287 1.16159 1.16276
28/05/2018   15:30:00   1.16277 1.16293 1.16177 1.16212
28/05/2018   16:00:00   1.16213 1.16318 1.16198 1.16262
28/05/2018   16:30:00   1.16262 1.16298 1.16258 1.16284
28/05/2018   17:00:00   1.16285 1.16329 1.16264 1.16265
28/05/2018   17:30:00   1.16266 1.163   1.16243 1.16289
28/05/2018   18:00:00   1.16288 1.1629  1.16228 1.16269
28/05/2018   18:30:00   1.16269 1.16278 1.16264 1.16274
28/05/2018   19:00:00   1.16275 1.16277 1.1627  1.16275
28/05/2018   19:30:00   1.16276 1.16284 1.1627  1.1628
28/05/2018   20:00:00   1.16279 1.16288 1.16264 1.16278
28/05/2018   20:30:00   1.16278 1.16289 1.1626  1.16265
28/05/2018   21:00:00   1.16267 1.1627  1.16251 1.16262
29/05/2018   14:30:00   1.15793 1.15827 1.15714 1.15786
29/05/2018   15:00:00   1.15785 1.159   1.15741 1.15814
29/05/2018   15:30:00   1.15813 1.15813 1.15601 1.15647
29/05/2018   16:00:00   1.15647 1.15658 1.15451 1.15539
29/05/2018   16:30:00   1.15539 1.15601 1.15418 1.1551
29/05/2018   17:00:00   1.15508 1.15599 1.15463 1.15527
29/05/2018   17:30:00   1.15528 1.15587 1.15442 1.15465
29/05/2018   18:00:00   1.15465 1.15469 1.15196 1.15261
29/05/2018   18:30:00   1.15261 1.15441 1.15261 1.15349
29/05/2018   19:00:00   1.15348 1.15399 1.15262 1.15399
29/05/2018   19:30:00   1.154   1.15412 1.15239 1.15322
29/05/2018   20:00:00   1.15322 1.15373 1.15262 1.15367
29/05/2018   20:30:00   1.15367 1.15419 1.15351 1.15367
29/05/2018   21:00:00   1.15366 1.15438 1.15352 1.15354
29/05/2018   21:30:00   1.15355 1.15355 1.15354 1.15354
30/05/2018   14:30:00   1.16235 1.16323 1.16133 1.16161
30/05/2018   15:00:00   1.16162 1.16193 1.1602  1.16059

Any ideas on how to acheive this?

pandas dataframe
1 Answers

You could groupby and apply some sorting logic to retain the Time columns, such as:

highs = df.groupby(df.index).apply(lambda x: x.sort_values(by='High').iloc[-1])                                                                                                                                                              
lows = df.groupby(df.index).apply(lambda x: x.sort_values(by='Low').iloc[0])

Output:

# Highs
                Time     Open     High      Low     Last
Date                                                    
2018-05-28  17:00:00  1.16285  1.16329  1.16264  1.16265
2018-05-29  15:00:00  1.15785  1.15900  1.15741  1.15814
2018-05-30  14:30:00  1.16235  1.16323  1.16133  1.16161


# Lows
                 Time     Open     High      Low     Last
Date                                                    
2018-05-28  14:30:00  1.16167  1.16252  1.16130  1.16166
2018-05-29  18:00:00  1.15465  1.15469  1.15196  1.15261
2018-05-30  15:00:00  1.16162  1.16193  1.16020  1.16059

EDIT

To join then, something like that should do it:

new_df = pd.concat([highs.Time.rename('time_of_high'), lows.Time.rename('time_of_low')], 1)

Output:

           time_of_high time_of_low
Date                               
28/05/2018     17:00:00    14:30:00
29/05/2018     15:00:00    18:00:00
30/05/2018     14:30:00    15:00:00
user_93773660
Related