python - Resample pandas DataFrame of OHLC data into three-day bars ignoring weekends

Question

I have daily price data with Open, High, Low, Close and the date as the index. I want to create three-day bars, but I want the bars to "wrap around the weekend," so that I end up with MTuW, TuWTh, WThF, ThFM, and FMTu bars.

(MTuW = Monday Tuesday Wednesday)

(FMTu = Friday Monday Tuesday)

(FSaSu = Friday Saturday Sunday)

I tried

df = df.asfreq('B')
df = df.resample('3D').mean()

But that seems to work properly only for MTuW, TuWTh, and WThF bars. The other bars don't seem to skip the weekend days, meaning that what is intended to be a FMTu bar is actually a FSaSu bar, or more accurately simply a Friday bar.

I believe there is a way to make this work perhaps with

from pandas.tseries.offsets import BDay

But it's not clear to me exactly how to make it work.

标签： pythonpython-3.xpandaspython-datetime

Answer 1

I didn't test this but I think you just need to use:

df = df.resample('3B').mean()

Update

To also accommodate for holidays, use a custom rule to resample:

from pandas.tseries.offsets import CustomBusinessDay
from pandas.tseries.holiday import USFederalHolidayCalendar

bday_us = CustomBusinessDay(calendar=USFederalHolidayCalendar())

df = df.resample(bday_us).mean()

I used USFederalHolidayCalendar here. To get an overview of the holidays in such a holiday class:

>>> pprint(USFederalHolidayCalendar().rules)
[Holiday: New Years Day (month=1, day=1, observance=<function nearest_workday at 0x10db3ce18>),
 Holiday: Dr. Martin Luther King Jr. (month=1, day=1, offset=<DateOffset: weekday=MO(+3)>),
 Holiday: Presidents Day (month=2, day=1, offset=<DateOffset: weekday=MO(+3)>),
 Holiday: MemorialDay (month=5, day=31, offset=<DateOffset: weekday=MO(-1)>),
 Holiday: July 4th (month=7, day=4, observance=<function nearest_workday at 0x10db3ce18>),
 Holiday: Labor Day (month=9, day=1, offset=<DateOffset: weekday=MO(+1)>),
 Holiday: Columbus Day (month=10, day=1, offset=<DateOffset: weekday=MO(+2)>),
 Holiday: Veterans Day (month=11, day=11, observance=<function nearest_workday at 0x10db3ce18>),
 Holiday: Thanksgiving (month=11, day=1, offset=<DateOffset: weekday=TH(+4)>),
 Holiday: Christmas (month=12, day=25, observance=<function nearest_workday at 0x10db3ce18>)]

Obviously such a holiday object can be customised and built from scratch too. More info on holiday classes: https://pandas.pydata.org/pandas-docs/stable/timeseries.html#holidays-holiday-calendars