1

I am trying to resample 1 min bars to 5 min but I am getting incorrect results.

1 min data:

enter image description here

I am using this to resample:

df2.resample("5min").agg({'open':'first',
                          'high':'max',
                          'low:'min',
                          'close':'last'})

I get:

enter image description here

For the second row bar (00:00:00) the high should be 110.34 not 110.35, and the close shoulb be 110.33.

How do I fix this?

EDIT 1 To create data:

import datetime
import pandas as pd
idx = pd.date_range("2021-09-23 23:55", periods=11, freq="1min")
df = pd.DataFrame(index = idx)
data = [110.34,
        110.33,110.34,110.33,110.33,110.33,
        110.32,110.35,110.34,110.32,110.33,
        ]
df['open'] = data
df['high'] = data
df['low'] = data
df['close'] = data

df2 = df.resample("5min").agg({'open':'first',
                          'high':'max',
                          'low':'min',
                          'close':'last'})
print(df)
print("----")
print(df2)
ManInMoon
  • 6,795
  • 15
  • 70
  • 133
  • Your output is as expected. The data for the second row `00:00:00` represents 5 minute data *beginning* at `00:00:00`. i.e. from `00:00:00` to `00:04:00` – not_speshal Sep 27 '21 at 17:05
  • But I expect 00:00:00 to be 23:56:00 to 00:00:00. How can I achieve that? – ManInMoon Sep 27 '21 at 17:15
  • Why would you want 23:56:00 - 00:00:00? Are you trying to do rolling? Because the first should be 23:55:00 - 23:59:00, then the second 5 minute should be 00:00:00 - 00:04:00, and so on. – Onyambu Sep 27 '21 at 17:18
  • I wish the 5 minutes bars to be labelled by the final time. So 00:01:00 to 00:05:00 are labelled as 00:05:00 – ManInMoon Sep 27 '21 at 17:20
  • Is the issue the labelling or rather the results? – Onyambu Sep 27 '21 at 17:25
  • My labels are what I want, but the values are incorrect. e.g. The high of row 2. – ManInMoon Sep 27 '21 at 17:29

1 Answers1

0

We can specify the closed='right' and label='right' optional keyword arguments

d = {'open':'first','high':'max',
        'low':'min','close':'last'}
df.resample("5min", closed='right', label='right').agg(d)

                       open    high     low   close
2021-09-23 23:55:00  110.34  110.34  110.34  110.34
2021-09-24 00:00:00  110.33  110.34  110.33  110.33
2021-09-24 00:05:00  110.32  110.35  110.32  110.33
Shubham Sharma
  • 68,127
  • 6
  • 24
  • 53