0

I'm looking to add a custom business day offset column to a date column:

>> import pandas as pd
>> from pandas.tseries.offsets import CustomBusinessDay

>> df = pd.DataFrame({'ship_date_et': ['2018-10-01' for x in range(10)], 'offset': [x for x in range(10)]})

>> df['offset'] = pd.to_timedelta(df['offset'], unit='D')

>> df['ship_date_et'] = pd.to_datetime(df['ship_date_et'])

>> df.dtypes
offset          timedelta64[ns]
ship_date_et     datetime64[ns]

>> df
    offset ship_date_et
0   0 days  2018-10-01
1   1 days  2018-10-01
2   2 days  2018-10-01
3   3 days  2018-10-01
4   4 days  2018-10-01
5   5 days  2018-10-01
6   6 days  2018-10-01
7   7 days  2018-10-01
8   8 days  2018-10-01
9   9 days  2018-10-01

>> holidays = ['2018-10-10'] # '2018-10-10' just a made-up holiday

>> cdays = CustomBusinessDay(holidays=holidays, weekmask='Mon Tue Wed Thu Fri') 

>> df['ship_date_et'] + df['offset'].apply(cdays)
0   2018-10-02
1   2018-10-03
2   2018-10-04
3   2018-10-05
4   2018-10-06
5   2018-10-07
6   2018-10-08
7   2018-10-09
8   2018-10-10
9   2018-10-11
dtype: datetime64[ns]

Which is crazy wrong. The weekend (2018-10-06 and 2018-10-07) isn't calculated (which Pandas docs says that CDay incorporates Weekends). Which is beside the point that I've only defined 2 weekmask days (Mon & Tue).

I'm truly confused and frustrated because this works with normal BDay:

>> df['ship_date_et'] + df['offset'].dt.days.apply(BDay) # Doing dt.days to get integer for BDay since we defined df['offset'] as a `timedelta`
0   2018-10-01
1   2018-10-02
2   2018-10-03
3   2018-10-04
4   2018-10-05
5   2018-10-08
6   2018-10-09
7   2018-10-10
8   2018-10-11
9   2018-10-12

My desired result: 

>> df['ship_date_et'] + df['offset'].apply(cdays)
0   2018-10-01
1   2018-10-02
2   2018-10-03
3   2018-10-04
4   2018-10-05
5   2018-10-08
6   2018-10-09
7   2018-10-11
8   2018-10-12
9   2018-10-15

I've read through the docs (both numpy busday and Pandas) and scoured the internet e.g., here and here), but cannot find a reason for what's happening. The most concerning part is that my cdays definition, at index=0 is 0 days, but the result returns my ship_date_et days + 1.

Of course, there is an issue too about using apply and the: 

PerformanceWarning: Adding/subtracting array of DateOffsets to Series not vectorized "Series not vectorized"

Pandas gives.

I would appreciate any feedback or input. Thanks!

Tristan Hall
  • 1
  • 2

2 Answers2

0

It looks like you're only applying cdays to the offset and not the ship_date+offset.

(df['ship_date_et'] + df['offset']).apply(cdays)
dlstadther
  • 395
  • 5
  • 15
0

@dlstadther, while your answer did give me date offsets, it was not seeing the weekends or holidays as zeros. I think this has to do with my implementation of df['offset'] as type timedelta. This resulted in:

>> (df['ship_date_et'] + df['offset']).apply(cdays)
0   2018-10-02
1   2018-10-03
2   2018-10-04
3   2018-10-05
4   2018-10-08
5   2018-10-08
6   2018-10-08
7   2018-10-09
8   2018-10-11
9   2018-10-11
dtype: datetime64[ns]

which wasn't seeing the weekends and holidays as zeros, non-existent, whatever you want to think, and continuing the counter after them. I'd say that was due to my inability to ask a good question.

My Answer:

With a little bit of sleep and finagling: 

>> df['new'] = df['ship_date_et'] + df['offset'].dt.days*cdays

is what I was looking for.

>> df
offset ship_date_et        new
0 0 days   2018-10-01 2018-10-01
1 1 days   2018-10-01 2018-10-02
2 2 days   2018-10-01 2018-10-03
3 3 days   2018-10-01 2018-10-04
4 4 days   2018-10-01 2018-10-05
5 5 days   2018-10-01 2018-10-08
6 6 days   2018-10-01 2018-10-09
7 7 days   2018-10-01 2018-10-11
8 8 days   2018-10-01 2018-10-12
9 9 days   2018-10-01 2018-10-15

where "holidays" and weekends in this example are skipped over, and the counter continues past them just like BDay.

If df['offset'] is of type int, will not have to do .dt.days.

Tristan Hall
  • 1
  • 2