1

I'd like to create a monthly date range within Pandas taking in consideration of business days.

such as: dt_range = pd.date_range(start='2017-12-20', periods=5, freq='M')

Meaning: starting on 2017-12-20, roll on every 20th for 5 months, if such roll date is not a business day, take the following roll date (but keep the 20th for next observation).

Likewise: ending on a date, roll backwards, each 20th for 5 months. such as:

dt_range = pd.date_range(end='2018-05-20', periods=5, freq='M')

I have been consulting Pandas offset aliases however, it seems my way of thinking does not sync with theirs, as they are focused on month end, month start and I cannot find a simple monthly roll. It is a bit of information overload, and I am sure it exist an easy way, and therefore I would like to ask for some help/guidance to find the answer.

Jaco
  • 1,564
  • 2
  • 9
  • 33

1 Answers1

1

One way to solve it:

dt_range = pd.date_range(start='2017-12-20', periods=5, freq='MS') + pd.DateOffset(days=18) + pd.tseries.offsets.BusinessDay(1)

Output:

DatetimeIndex(['2018-01-22', '2018-02-20', '2018-03-20', '2018-04-20',
               '2018-05-21'],
              dtype='datetime64[ns]', freq=None)

I just go to the first day of the month with the date_range(.., freq='MS) and then I add 18 days to get to 19th day. Then I use offsets.BusinessDay as described in this SO post to find the next business day.

If you want to include your start day, you have to start one month earlier. This behavior is somewhat peculiar, but easy to account for.

above_c_level
  • 3,579
  • 3
  • 22
  • 37
  • Thanks, perhaps a tips for someone to improve Pandas with this. I will adopt your answer with a slight change, e.g. Assume 2017-12-01 as start, I would need to do as follows to get the correct date series: roll_date = 1 dt_range = pd.date_range(start='2017-12-01', periods=5, freq='MS') + pd.DateOffset(days=roll_date-1) + pd.tseries.offsets.BusinessDay(-1) + pd.tseries.offsets.BusinessDay(1) ,, of course, this does not take into account a modified following schedule – Jaco Jul 30 '20 at 13:17