Get previous business day in a DataFrame

Question

I have a DataFrame with two columns, a date and a category. I want to create a new date column according to the rule: if category is B then the value should the business day closest to the date (only from the past or the day itself), else it's the value of the date column itself.

I define business day as any day which isn't on a weekend, nor present in the list holidays defined in the minimal example below.

Please consider the following DataFrame df:

import datetime as dt
import pandas as pd
from IPython.display import display

holidays = [dt.datetime(2018, 10, 11)]
df = pd.DataFrame({"day": ["2018-10-10", "2018-10-11", "2018-10-12",
                       "2018-10-13", "2018-10-14", "2018-10-15"
                      ],
               "category":["A", "B", "C", "B", "C", "A"]
              }
)

df["day"] = pd.to_datetime(df.day, format="%Y-%m-%d")
display(df)

         day category
0 2018-10-10        A
1 2018-10-11        B
2 2018-10-12        C
3 2018-10-13        B
4 2018-10-14        C
5 2018-10-15        A

How do I get a third column whose values are the ones listed below?

2018-10-10
2018-10-10
2018-10-12
2018-10-12
2018-10-14
2018-10-15

I have a created a function that finds the last business day when working with lists, if that's any help.

# creates a list whose elements are all days in the years 2017, 2018 and 2019
days = [dt.datetime(2017, 1 , 1) + dt.timedelta(k) for k in range(365*3)]


def lastt_bus_day(date):
    return max(
        [d for d in days if d.weekday() not in [5, 6]
                            and d not in holidays
                            and d <= date
        ]
    )

for d in df.day:
    print(last_bus_day(d))
#prints
2018-10-10 00:00:00
2018-10-10 00:00:00
2018-10-12 00:00:00
2018-10-12 00:00:00
2018-10-12 00:00:00
2018-10-15 00:00:00

Answer 1

Pandas supports providing your own holidays via Custom Business Days.

The benefit of this solution is it supports adjacent holidays seamlessly; for example, Boxing Day & Christmas in some regions.

# define custom business days
weekmask = 'Mon Tue Wed Thu Fri'
holidays = ['2018-10-11']

bday = pd.tseries.offsets.CustomBusinessDay(holidays=holidays, weekmask=weekmask)

# construct mask to identify when days must be sutracted
m1 = df['category'] == 'B'
m2 = df['day'].dt.weekday.isin([5, 6]) | df['day'].isin(holidays)

# apply conditional logic
df['day'] = np.where(m1 & m2, df['day'] - bday, df['day'])

print(df)

  category        day
0        A 2018-10-10
1        B 2018-10-10
2        C 2018-10-12
3        B 2018-10-12
4        C 2018-10-14
5        A 2018-10-15

Edit: On the basis of your comment, "I just realised I didn't ask exactly what I wanted. I want to find the previous business day", you can simply use:

df['day'] -= bday

Answer 2

By using pandas BDay

df.day.update(df.loc[(df.category=='B')&((df.day.dt.weekday.isin([5,6])|(df.day.isin(holidays )))),'day']-pd.tseries.offsets.BDay(1))
df
Out[22]: 
  category        day
0        A 2018-10-10
1        B 2018-10-10
2        C 2018-10-12
3        B 2018-10-12
4        C 2018-10-14
5        A 2018-10-15