6

I have a DataFrame which is indexed with the last day of the month. Sometimes this date is a weekday and sometimes it is a weekend. Ignoring holidays, I'm looking to offset the date to the next business date if the date is on a weekend and leave the result unchanged if it is already on a weekday.

Some example data would be

import pandas as pd
idx = [pd.to_datetime('20150430'), pd.to_datetime('20150531'), 
       pd.to_datetime('20150630')]
df = pd.DataFrame(0, index=idx, columns=['A'])
df

            A
2015-04-30  0
2015-05-31  0
2015-06-30  0

df.index.weekday
array([3, 6, 1], dtype=int32)

Something like the following works, however I would appreciate if someone has a solution that is a little more straightforward.

idx = df.index.copy()
wknds = (idx.weekday == 5) | (idx.weekday == 6)
idx2 = idx[~wknds]
idx2 = idx2.append(idx[wknds] + pd.datetools.BDay(1))
idx2 = idx2.order()
df.index = idx2
df

            A
2015-04-30  0
2015-06-01  0
2015-06-30  0
mgilbert
  • 3,495
  • 4
  • 22
  • 39
  • It'll probably easier to just use 'BM' i.e. the last business day of the month. – Andy Hayden Sep 29 '15 at 04:56
  • 1
    @Andy Hayden Could you elaborate? I'm not actually creating the data, this was just for the example. The data comes as such, and I would like to lag the index to the next closest business day if its not already a business day. – mgilbert Sep 29 '15 at 12:29

4 Answers4

11

You can add 0*BDay() 

from pandas.tseries.offsets import BDay
df.index = df.index.map(lambda x : x + 0*BDay())

You can also use this with a Holiday calendar with CDay(calendar) in case there are holidays.

mgilbert
  • 3,495
  • 4
  • 22
  • 39
phil_20686
  • 4,000
  • 21
  • 38
1

You can map the index with a lambda function, and set the result back to the index.

df.index = df.index.map(lambda x: x if x.dayofweek < 5 else x + pd.DateOffset(7-x.dayofweek))

df
            A
2015-04-30  0
2015-06-01  0
2015-06-30  0
matt_s
  • 1,037
  • 1
  • 10
  • 17
1

Using DataFrame.resample

A more idiomatic method would be to resample to business days:

df.resample('B', label='right', closed='right').first().dropna() 

              A
2015-04-30  0.0
2015-06-01  0.0
2015-06-30  0.0
Erfan
  • 40,971
  • 8
  • 66
  • 78
0

Can also use a variation of the logic: a)given input date = 'inputdate', go back one business day using pandas date_range which has business days input; then b) go forward one business day using the same. To do this, you generate a vector with 2 inputs using data_range and select the min or max value to return the appropriate single value. So this could look as follows:

a) get business day before:

date_1b_bef = min(pd.date_range(start=inputdate, periods = 2, freq='-1B'))

b) get business day after the 'business day before':

date_1b_aft = max(pd.date_range(start=date_1b_bef, periods = 2, freq='1B'))

or substituting a) into b) to get one line:

date_1b_aft = max(pd.date_range(start=min(pd.date_range(start=inputdate, periods = 2, freq='-1B')), periods = 2, freq='1B'))

This can also be used with relativedelta to get the business day after some calendar period offset from inputdate. For example:

a) get the business day (using 'following' convention if offset day is not a business day) for 1 calendar month prior to 'input date':

date_1mbef_fol = max(pd.date_range(min(pd.date_range(start=inputdate + relativedelta(months=-1), periods = 2, freq='-1B')), periods = 2, freq = '1B'))

b) get the business day (using 'preceding' convention if offset day is not a business day) for 1 year prior to 'input date':

date_1ybef_pre = min(pd.date_range(max(pd.date_range(start=inputdate + relativedelta(years=-1), periods = 2, freq='1B')), periods = 2, freq = '-1B'))

AlexK
  • 2,855
  • 9
  • 16
  • 27
sanyearng
  • 1
  • 1
    You will need to explain where `relativedelta` comes from (i.e. include the necessary import statements if it is part of an existing library). Please also post the output for your solution. – AlexK May 18 '21 at 01:29