Find the business days between two columns in a pandas dataframe, which contain NaTs

Question

I have 2 columns in my pandas data frame, and I want to calculate the business dates between them.

Data:

 ID     On hold     Off Hold
 101    09/15/2017  09/16/2017
 102    NA          NA
 103    09/22/2017  09/26/2017
 104    10/12/2017  10/30/2017
 105    NA          NA
 106    08/05/2017  08/06/2017
 107    08/08/2017  08/03/2017
 108    NA          NA

I tried the below code using busday_count from numpy:

 df1['On hold'] = pd.to_datetime(df1['On hold'])
 df1['Off Hold'] = pd.to_datetime(df1['Off Hold'])
 np.busday_count(df1['On hold'].values.astype('datetime64[D]'),df1['Off Hold'].values.astype('datetime64[D]'))

also,

 np.where(pd.notnull(df1['On hold']),np.busday_count(df1['On hold'].values.astype('datetime64[D]'),
                                                df1['Off Hold'].values.astype('datetime64[D]')),0)

The error was :

   Cannot compute a business day count with a NaT (not-a-time) date

Any help will be appreciated :)

How do you intend to treat the days with NaT values? To be ignored or imputed? — ShreyasG, Oct 24 '17 at 06:56
[This](https://stackoverflow.com/questions/37840812/pandas-subtracting-two-date-columns-and-the-result-being-an-integer) might have the answer — ShreyasG, Oct 24 '17 at 07:02

score 1 · Answer 1 · answered Oct 24 '17 at 07:39

1

Using the pd.bdate_range method:

pd.bdate_range(df['On Hold'], df['Off Hold'], freq = 'B')

answered Oct 24 '17 at 07:39

ShreyasG

766
4
11

Can you share the output you got when you ran this? – Megha John Oct 24 '17 at 08:45

Sachit Nagpal · Accepted Answer · 2017-10-24T13:29:30.853

1

You can try the below:

f = df1.dropna()
f['days'] = np.busday_count(pd.to_datetime(f['On hold']).values.astype('datetime64[D]'), \
            pd.to_datetime(f['Off hold']).values.astype('datetime64[D]'))

df1.merge(f[['ID', 'days']],on='ID', how='left')

edited Oct 24 '17 at 13:29

answered Oct 24 '17 at 13:02

Sachit Nagpal

486
4
7

score 0 · Answer 3 · answered Oct 24 '17 at 06:59

0

Try to drop NA first before you do bus day count. You can drop all the NA by using the following.

new_df = df.dropna()

answered Oct 24 '17 at 06:59

Jonathan

8,453
9
51
74

This is just a snippet of the data, if I drop na, it will affect the other columns in the data frame. – Megha John Oct 24 '17 at 07:01

Find the business days between two columns in a pandas dataframe, which contain NaTs

3 Answers3

Linked