0

I have a 1-minute OHLC CSV file with date using UTC

df = pd.read_csv('...', usecols=['Date','Open','High','Low','Close'],
    index_col=['Date'], parse_dates=['Date'])

I am resampling it using:

ohlc_head = {'Open':'first', 'High':'max', 'Low':'min', 'Close': 'last'}
df_resamples = df.resample('4h', base=21).agg(ohlc_head).dropna(how='any')

This will resample from Sunday 21:00 onward, so it is 21:00, 01:00, 05:00, ..., until Friday 17:00 Where 21:00 UTC is the market open time.

However, when there is daylight saving switch, the first minute in the week starts at Sunday 22:00 and finishes at 18:00.

How can you alternate the resampling to be at 22:00 when the first minute on Sunday starts at 22:00 and 21:00 when the first minute on Sunday start at 21:00? In brief, the resampling should start automatically at the first minute in the CSV (which is the start of the week on Sunday) and continue in the same pattern until it encounters another start and so on.

Adam
  • 3,872
  • 6
  • 36
  • 66
  • Quick and dirty: process day by day, pass hours of the first sample as `base`. You should also consider providing sample input. – Always Right Never Left Oct 18 '20 at 13:30
  • Preprocess; make another Series from the first that shifts the DST periods? – wwii Oct 18 '20 at 13:32
  • @AlwaysRightNeverLeft I can even do it week by week and choose the base depending on the week start. But I wanted to see if there is an option in Pandas first. – Adam Oct 18 '20 at 14:36
  • @wwii I can do a for-loop and resample week by week, I was looking for a built-in solution – Adam Oct 18 '20 at 14:41

1 Answers1

0

I figured out an easy solution, which is localising time, resampling, then changing to UTC again:

df.index = df.index.tz_localize('UTC').tz_convert('Europe/London')
ohlc_dict = {'Open':'first', 'High':'max', 'Low':'min', 'Close': 'last'}
df = df.resample('4h', base=21).agg(ohlc_dict).dropna(how='any')
df.index = df.index.tz_convert('UTC')
Adam
  • 3,872
  • 6
  • 36
  • 66