CODEWITHSUNDEEP
pythonpandas
cphill
cphill

Reputation: 5914

Pandas Manipulating Freq for Business Day DateRange

I am trying to add a set of common date related columns to my data frame and my approach to building these date columns is off the .date_range() pandas method that will have the date range for my dataframe.

While I can use methods like .index.day or .index.weekday_name for general date columns, I would like to set a business day column based on date_range I constructed, but not sure if I can use the freq attribute nickname 'B' or if I need to create a new date range.

Further, I am hoping to not count those business days based on a list of holiday dates that I have.

Here is my setup:

Holiday table

holiday_table = holiday_table.set_index('date')
holiday_table_dates = holiday_table.index.to_list() # ['2019-12-31', etc..]

Base Date Table

data_date_range = pd.date_range(start=date_range_start, end=date_range_end)

df = pd.DataFrame({'date': data_date_range}).set_index('date')

df['day_index'] = df.index.day

# Weekday Name
df['weekday_name'] = df.index.weekday_name

# Business day
df['business_day'] = data_date_range.freq("B")

Error at df['business_day'] = data_date_range.freq("B"):

---> 13 df['business_day'] = data_date_range.freq("B")
ApplyTypeError: Unhandled type: str

Upvotes: 1

Views: 1620

Answers (1)

It_is_Chris
It_is_Chris

Reputation: 14083

OK, I think I understand your question now. You are looking to create a a new column of working business days (excluding your custom holidays). In my example i just used the regular US holidays from pandas but you already have your holidays as a list in holiday_table_dates but you should still be able to follow the general layout of my example for your specific use. I also used the assumption that you are OK with boolean values for your business_day column:

import pandas as pd
from pandas.tseries.holiday import USFederalHolidayCalendar as h_cal

# sample data
data_date_range = pd.date_range(start='1/1/2019', end='12/31/2019')
df = pd.DataFrame({'date': data_date_range}).set_index('date')
df['day_index'] = df.index.day
# Weekday Name
df['weekday_name'] = df.index.weekday_name

# this is just a sample using US holidays
hday = h_cal().holidays(df.index.min(), df.index.max())
# b is the same date range as bove just with the freq set to business days
b = pd.date_range(start='1/1/2019', end='12/31/2019', freq='B')
# find all the working business day where b is not a holiday
bday = b[~b.isin(hday)]
# create a boolean col where the date index is in your custom business day we just created
df['bday'] = df.index.isin(bday)


            day_index weekday_name   bday
date                                     
2019-01-01          1      Tuesday  False
2019-01-02          2    Wednesday   True
2019-01-03          3     Thursday   True
2019-01-04          4       Friday   True
2019-01-05          5     Saturday  False

Upvotes: 1

Related Questions