How to shift dates in pandas dataframe with reference to a particular column having business days?

Question

I am trying to reference a particular column called 'Unloading point' in order to map my dates to these particular business days. So the dates present in 'Date' column should be mapped based on 'Unloading point'. If there is no data in 'Unloading point' column and if date falls on a weekend then it should just rollback to nearest business day. I am quite new to pandas dataframes. Any help will be deeply appreciated.

Date	Unloading point	Expected Output
5/30/2021	MON-TUE-WED-THU-FRI	5/31/2021
6/11/2021	MON-TUE-WED-THU-FRI	6/11/2021
6/5/2021	THU	6/3/2021
6/4/2021	THU	6/3/2021
5/27/2021	THU	5/27/2021
5/29/2021	THU	5/27/2021
5/29/2021		5/28/2021
6/6/2021	MON-TUE-WED-THU-FRI	6/7/2021
6/1/2021		6/1/2021
5/29/2021	TUE	5/25/2021
6/1/2021		6/1/2021
7/31/2021	THU	7/29/2021
6/1/2021	WED	6/2/2021
5/26/2021	WED	5/26/2021
6/14/2021	MON-TUE-WED-THU-FRI	6/14/2021
5/27/2021	MON-TUE-WED	5/26/2021
6/15/2021	MON-TUE-WED	6/15/2021
5/22/2021	TUE-WED	5/19/2021
6/10/2021	MON-TUE-WED	6/10/2021
6/24/2021	TUE-FRI	6/22/2021

I don't get all your conditions. You sad shift the Date to some working day if there is an empty unloading point. But what do you do, if the unloading point is filled. — thomas
To start with calendar issued in python, you could look at calendar — thomas
@thomas If the unloading point is empty, then it should just check if the date is business day or not. If it falls on a weekend, it should rollback to nearest business day. In case if this column has some value for eg. (TUE-WED), then date should rollback to the nearest B day referenced in this column, here(WED). So if date is 22/5/2021 which is (SAT) should role back to 19/5/2021 which is (WED) — SamirJ

dicristina dicristina · Accepted Answer · 2021-08-03T06:22:27

To answer this question we can use Custom Business Days. The constructor of these objets takes a weekmask parameter which contains a list of days of the week which are considered business days, for example "Mon Tue Wed". It is also possible to specify holidays. These objects work much like the BusinessDay class but with the the freedom to specify the business days as we please.

Answer

The key here is to create a CDay object for each row.

from pandas.tseries.offsets import CDay  # Same as CustomBusinessDay

# Make DimetimeIndex
df['date'] = pd.to_datetime(df['date'])
# Format the weekmask correctly
df['weekmask'] = df['weekmask'].str.replace('-', ' ').str.title()

def roll(s):
    # The default weekmask is Monday to Friday.
    cday = CDay(weekmask=s.weekmask) if s.weekmask else CDay()
    next_day = cday.rollforward(s.date)
    prev_day = cday.rollback(s.date)
    return next_day if next_day - s.date < s.date - prev_day else prev_day

df['roll'] = df.apply(roll, axis='columns')

Result

         date             weekmask       roll
0  2021-05-30  Mon Tue Wed Thu Fri 2021-05-31
1  2021-06-11  Mon Tue Wed Thu Fri 2021-06-11
2  2021-06-05                  Thu 2021-06-03
3  2021-06-04                  Thu 2021-06-03
4  2021-05-27                  Thu 2021-05-27
5  2021-05-29                  Thu 2021-05-27
6  2021-05-29                 None 2021-05-28
7  2021-06-06  Mon Tue Wed Thu Fri 2021-06-07
8  2021-06-01                 None 2021-06-01
9  2021-05-29                  Tue 2021-06-01
10 2021-06-01                 None 2021-06-01
11 2021-07-31                  Thu 2021-07-29
12 2021-06-01                  Wed 2021-06-02
13 2021-05-26                  Wed 2021-05-26
14 2021-06-14  Mon Tue Wed Thu Fri 2021-06-14
15 2021-05-27          Mon Tue Wed 2021-05-26
16 2021-06-15          Mon Tue Wed 2021-06-15
17 2021-05-22              Tue Wed 2021-05-19
18 2021-06-10          Mon Tue Wed 2021-06-09
19 2021-06-24              Tue Fri 2021-06-25

How to shift dates in pandas dataframe with reference to a particular column having business days?

1 Answers

Answer

Result