CODEWITHSUNDEEP
pythonpandas
mgilbert
mgilbert

Reputation: 3645

Pandas offset DatetimeIndex to next business if date is not a business day

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

Upvotes: 7

Views: 12398

Answers (4)

sanyearng
sanyearng

Reputation: 1

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'))

Upvotes: 0

Erfan
Erfan

Reputation: 42886

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

Upvotes: 1

phil_20686
phil_20686

Reputation: 4080

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.

Upvotes: 12

matt_s
matt_s

Reputation: 1075

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

Upvotes: 1

Related Questions