find next working day in a pandas column

Question

I have a pandas df that represents the open days of a shop looking like:

         Dates  Open
0   2016-01-01     0
1   2016-01-02     0
2   2016-01-03     0
3   2016-01-04     1
4   2016-01-05     1
5   2016-01-06     1
6   2016-01-07     1
7   2016-01-08     1
8   2016-01-09     0
9   2016-01-10     0
10  2016-01-11     1
11  2016-01-12     1
12  2016-01-13     1
13  2016-01-14     1
14  2016-01-15     1
15  2016-01-16     0
16  2016-01-17     0
17  2016-01-18     1
18  2016-01-19     1
19  2016-01-20     1
20  2016-01-21     1
21  2016-01-22     1
22  2016-01-23     0
23  2016-01-24     0
24  2016-01-25     1
25  2016-01-26     1
26  2016-01-27     1
27  2016-01-28     1
28  2016-01-29     1

this can be recreated by:

Dates =['2016-01-01',
 '2016-01-02',
 '2016-01-03',
 '2016-01-04',
 '2016-01-05',
 '2016-01-06',
 '2016-01-07',
 '2016-01-08',
 '2016-01-09',
 '2016-01-10',
 '2016-01-11',
 '2016-01-12',
 '2016-01-13',
 '2016-01-14',
 '2016-01-15',
 '2016-01-16',
 '2016-01-17',
 '2016-01-18',
 '2016-01-19',
 '2016-01-20',
 '2016-01-21',
 '2016-01-22',
 '2016-01-23',
 '2016-01-24',
 '2016-01-25',
 '2016-01-26',
 '2016-01-27',
 '2016-01-28',
 '2016-01-29']

Open = [0,0,0,1,1,1,1,1,0,0,1,1,1,1,1,0,0,1,1,1,1,1,0,0,1,1,1,1,1]


df = DataFrame({'Dates':Dates, 'Open':Open})

the column open represents the days in which the shop is open for deliveries. I want to create a new column with the next open day for each date in the leftmost column. I cannot use predefined workingdays functions but I have to use the Open column to determine whether or not the shop is open. The desired outcome would be:

         Dates  Open     Desired
0   2016-01-01     0  2016-01-04
1   2016-01-02     0  2016-01-04
2   2016-01-03     0  2016-01-04
3   2016-01-04     1  2016-01-05
4   2016-01-05     1  2016-01-06
5   2016-01-06     1  2016-01-07
6   2016-01-07     1  2016-01-08
7   2016-01-08     1  2016-01-11
8   2016-01-09     0  2016-01-11
9   2016-01-10     0  2016-01-11
10  2016-01-11     1  2016-01-12
11  2016-01-12     1  2016-01-13
12  2016-01-13     1  2016-01-14
13  2016-01-14     1  2016-01-15
14  2016-01-15     1  2016-01-18
15  2016-01-16     0  2016-01-18
16  2016-01-17     0  2016-01-18
17  2016-01-18     1  2016-01-19
18  2016-01-19     1  2016-01-20
19  2016-01-20     1  2016-01-21
20  2016-01-21     1  2016-01-22
21  2016-01-22     1  2016-01-25
22  2016-01-23     0  2016-01-25
23  2016-01-24     0  2016-01-25
24  2016-01-25     1  2016-01-26
25  2016-01-26     1  2016-01-27
26  2016-01-27     1  2016-01-28
27  2016-01-28     1  2016-01-29
28  2016-01-29     1

EdChum · Answer 1 · 2016-01-15T10:24:17.647

6

IIUC then you can add Business Day offset after converting your strings to datetime dtype using to_datetime:

In [141]:
df['Dates'] = pd.to_datetime(df['Dates'])
df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 29 entries, 0 to 28
Data columns (total 2 columns):
Dates    29 non-null datetime64[ns]
Open     29 non-null int64
dtypes: datetime64[ns](1), int64(1)
memory usage: 696.0 bytes

In [146]:
from pandas.tseries.offsets import *
df['Desired'] = df['Dates'] + BDay()
df

Out[146]:
        Dates  Open    Desired
0  2016-01-01     0 2016-01-04
1  2016-01-02     0 2016-01-04
2  2016-01-03     0 2016-01-04
3  2016-01-04     1 2016-01-05
4  2016-01-05     1 2016-01-06
5  2016-01-06     1 2016-01-07
6  2016-01-07     1 2016-01-08
7  2016-01-08     1 2016-01-11
8  2016-01-09     0 2016-01-11
9  2016-01-10     0 2016-01-11
10 2016-01-11     1 2016-01-12
11 2016-01-12     1 2016-01-13
12 2016-01-13     1 2016-01-14
13 2016-01-14     1 2016-01-15
14 2016-01-15     1 2016-01-18
15 2016-01-16     0 2016-01-18
16 2016-01-17     0 2016-01-18
17 2016-01-18     1 2016-01-19
18 2016-01-19     1 2016-01-20
19 2016-01-20     1 2016-01-21
20 2016-01-21     1 2016-01-22
21 2016-01-22     1 2016-01-25
22 2016-01-23     0 2016-01-25
23 2016-01-24     0 2016-01-25
24 2016-01-25     1 2016-01-26
25 2016-01-26     1 2016-01-27
26 2016-01-27     1 2016-01-28
27 2016-01-28     1 2016-01-29
28 2016-01-29     1 2016-02-01

edited Jan 15 '16 at 10:24

answered Jan 15 '16 at 10:10

EdChum

376,765
198
813
562

thanks for your answer. However, as I specified I cannot use predefined working days functions. This was just an example and the days the shop may be closed/open may vary – Blue Moon Jan 15 '16 at 10:39
You can use [custom business days](http://pandas.pydata.org/pandas-docs/stable/timeseries.html#custom-business-days-experimental) for this – EdChum Jan 15 '16 at 11:32
Does this do what you want: `holidays = df.loc[df['Open'] == 0, 'Dates'] df['Dates'] + BDay(holidays = holidays)`? – EdChum Jan 15 '16 at 12:00
I ge the error: "TypeError: cannot concatenate 'str' and 'BusinessDay'" "objects for the secon line – Blue Moon Jan 15 '16 at 12:15
You still need to convert the 'Dates' col first to datetime, have you done this? – EdChum Jan 15 '16 at 12:16
I converted the column but now I get : TypeError: cannot use a non-absolute DateOffset in datetime/timedelta operations [] – Blue Moon Jan 15 '16 at 12:19
What version pandas and numpy are you using? – EdChum Jan 15 '16 at 12:21
pandas:'0.16.2', numpy:'1.10.1' – Blue Moon Jan 15 '16 at 12:23
Don't know, this works for me using pandas 0.17.1 and numpy 1.9.3 – EdChum Jan 15 '16 at 13:01

find next working day in a pandas column

1 Answers1

Linked