I have a dataframe with two dates, among other things. I need to filter out rows that have more than two working days difference between these two dates. I must take into consideration weekends and holidays.
*Assuming 10/17/2023 is a holiday...
Example df:
NAME DATE1 DATE2
CASE1 10/12/2023 10/13/2023 <--- one day difference
CASE2 10/12/2023 10/16/2023 <--- two days difference (weekend)
CASE3 10/12/2023 10/18/2023 <--- three days difference (weekends and holidays)
...
CASEX 10/12/2023 10/19/2023 <--- four days difference (weekends and holidays)
I need to save CASE3 and CASEX (which has more than two days difference) in another dataframe and delete it from this one.
My approach:
date1 = "10/12/2023"
date2 = "10/19/2023"
date1 = pd.to_datetime(date1, format="%m/%d/%Y").date()
date2 = pd.to_datetime(date2, format="%m/%d/%Y").date()
holidays = [pd.to_datetime("10/17/2023",format="%m/%d/%Y").date()]
days = np.busday_count(date1, date2, holidays=holidays)
In "days" I have the correct number. But I don't get to implement it in dataframe to filter it and extract the rows.
Define a list of holidays
holidays = np.array([pd.to_datetime("10/17/2023", format="%m/%d/%Y")], dtype='datetime64[D]')
Parse the strings in date columns to datetime type
df['DATE1'] = pd.to_datetime(df['DATE1'], format="%m/%d/%Y")
df['DATE2'] = pd.to_datetime(df['DATE2'], format="%m/%d/%Y")
# NAME DATE1 DATE2
# 0 CASE1 2023-10-12 2023-10-13
# 1 CASE2 2023-10-12 2023-10-16
# 2 CASE3 2023-10-12 2023-10-18
# 3 CASEX 2023-10-12 2023-10-19
Cast the dates to datetime64[D] types then use busy_day count to get the diff
days = np.busday_count(df['DATE1'].values.astype("datetime64[D]"),
df['DATE2'].values.astype("datetime64[D]"),
holidays=holidays)
# array([1, 2, 3, 4])
Use boolean indexing to filter the rows
valid_rows = df[days <= 2]
invalid_rows = df[days > 2]
# valid_rows
# NAME DATE1 DATE2
# 0 CASE1 2023-10-12 2023-10-13
# 1 CASE2 2023-10-12 2023-10-16
# invalid_rows
# NAME DATE1 DATE2
# 2 CASE3 2023-10-12 2023-10-18
# 3 CASEX 2023-10-12 2023-10-19