pandas: to_timedelta with business days

Question

I want to loop through a date range that has only business days in it, i.e. no weekends. To do that, I have the following command that basically picks every third Friday in a month and makes the column window equal to 2, 20 days from this third Friday. This code works totally fine.

for beg in pd.bdate_range("2000-01-01", "2017-05-01"):     
     beg= third_friday
     df["window"].loc[beg: beg + pd.to_timedelta(20,"D")] = 2
     if month==12:
         year=year+1
         month=0
     if year>=2017 and month>=3:
         break
     month = month +3
     monthcal = c.monthdatescalendar(year,month)
     third_friday = [day for week in monthcal for day in week if \
                day.weekday() == calendar.FRIDAY and \
               day.month == month][2]

However, the 20 in the

df["window"].loc[beg: beg + pd.to_timedelta(20,"D")] = 2

command refers to 20 days INCLUDING weekends, but I want it to refer to 20 WEEKDAYS; e.g. something like this:

df["window"].loc[beg: beg + pd.to_timedelta(20, "Weekdays_only")] = 2

Is there an easy fix so that I can replace the "D" with something else or do I have to rewrite everything?

Moreover, I also want to mark the days around the third Fridays with different values, e.g. day +1 after third_friday is 1 and day+2 is 2. To do that, I wrote a second for loop. Here the full example:

for beg in pd.bdate_range("2000-01-01", "2017-05-01"):     
 beg= third_friday
 lower_counter = 0
 for j in range(0,-21,-1):    
   df["window_counter"].loc[beg - pd.to_timedelta(j,"D"):beg] = lower_counter       
   lower_counter = j         

 df["window"].loc[beg: beg + pd.to_timedelta(20,"D")] = 2
 if month==12:
     year=year+1
     month=0
 if year>=2017 and month>=3:
     break
 month = month +3
 monthcal = c.monthdatescalendar(year,month)
 third_friday = [day for week in monthcal for day in week if \
            day.weekday() == calendar.FRIDAY and \
           day.month == month][2]

Have a look at this [SO post](https://stackoverflow.com/questions/31588148/adding-business-days-to-datetime-column) — Scott Boston, May 31 '17 at 15:22

Matti Lyra · Accepted Answer · 2019-03-19T07:35:10.817

6

I believe you are looking for the BDay date off set

import pandas as pd
from pandas.tseries.offsets import *

new_date = beg + BDay(20)

http://pandas.pydata.org/pandas-docs/stable/timeseries.html#dateoffset-objects

edited Mar 19 '19 at 07:35

answered May 31 '17 at 15:21

Matti Lyra

12,828
8
49
67

If I do like `df["window"].loc[beg: beg + pd.to_timedelta(20, bday(1))] = 2` I get NameError: name 'bday' is not defined. – freddy888 May 31 '17 at 15:28
you don't pass it to `to_timedelta` the `BDay` constructor already gives you back a custom time offset object which you just add to the start time - see edit above – Matti Lyra May 31 '17 at 15:35
Okay thanks. However, I run the whole stuff in a function and when I do the `from pandas.tseries.offsets import *` I get SyntaxError: import * only allowed at module level. – freddy888 May 31 '17 at 15:45
move the import to the top of the file so that it's on the module level not in the function – Matti Lyra May 31 '17 at 15:48
I did via `from pandas.tseries.offsets import BDay` and it works! Thanks! – freddy888 May 31 '17 at 15:53

score 1 · Answer 2 · answered May 31 '17 at 15:21

1

20 weekdays is exactly 28 calendar days away.

Thus

df["window"].loc[beg: beg + pd.to_timedelta(28,"D")] = 2

Should work

answered May 31 '17 at 15:21

WNG

3,705
2
22
31

Thanks, I editet the above example. I also want to mark the days how far they are away from the third Friday of each month. I edited my above example. Then, the 28 days solution wont work anymore. – freddy888 May 31 '17 at 15:34

pandas: to_timedelta with business days

2 Answers2

Linked

Related