Add workdays to pandas df date columns based of other column

user_9796966 0
Viewed 269

Is there a way to increment a date field in a pandas data frame by the number of working days specified in an another column?

np.random.seed(10)
df = pd.DataFrame({'Date':pd.date_range(start=dt.datetime(2020,7,1), end = dt.datetime(2020,7,10))})
df['Offset'] = np.random.randint(0,10, len(df))

        Date  Offset
0 2020-07-01       9
1 2020-07-02       4
2 2020-07-03       0
3 2020-07-04       1
4 2020-07-05       9
5 2020-07-06       0
6 2020-07-07       1
7 2020-07-08       8
8 2020-07-09       9
9 2020-07-10       0

I would expect this to work, however it throws and error:

df['Date'] + pd.tseries.offsets.BusinessDay(n = df['Offset'])

TypeError: n argument must be an integer, got <class 'pandas.core.series.Series'>

pd.to_timedelta does not support working days.

pandas dataframe python-3.x
1 Answers

Like I mentioned in my comment, you are trying to pass an entire Series as an integer. instead you want to apply the function row wise:

df['your_answer'] = df.apply(lambda x:x['Date'] + pd.tseries.offsets.BusinessDay(n= x['Offset']), axis=1)

df
        Date  Offset your_answer
0 2020-07-01       9  2020-07-14
1 2020-07-02       7  2020-07-13
2 2020-07-03       3  2020-07-08
3 2020-07-04       2  2020-07-07
4 2020-07-05       7  2020-07-14
5 2020-07-06       7  2020-07-15
6 2020-07-07       7  2020-07-16
7 2020-07-08       2  2020-07-10
8 2020-07-09       1  2020-07-10
9 2020-07-10       0  2020-07-10

Line of code broken down:

# notice how this returns every value of that column
df.apply(lambda x:x['Date'], axis=1)

0   2020-07-01
1   2020-07-02
2   2020-07-03
3   2020-07-04
4   2020-07-05
5   2020-07-06
6   2020-07-07
7   2020-07-08
8   2020-07-09
9   2020-07-10

# same thing with `Offset`
df.apply(lambda x:x['Offset'], axis=1)
0    9
1    7
2    3
3    2
4    7
5    7
6    7
7    2
8    1
9    0

Since pd.tseries.offsets.BusinessDay(n=foo_bar) takes an integer and not a series. We use the two columns in the apply() together - It's as if you are looping each number in the Offset column into the offsets.BusinessDay() function

user_66966570
Related