查找包含 NaT 的 Pandas 数据框中两列之间的工作日 [英] Find the business days between two columns in a pandas dataframe, which contain NaTs

问题描述

我的 Pandas 数据框中有 2 列，我想计算它们之间的营业日期.

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

数据:

 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

我使用 numpy 中的 busday_count 尝试了以下代码:

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]'))

还有，

 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)

错误是:

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

任何帮助将不胜感激:)

Any help will be appreciated :)

推荐答案

您可以尝试以下方法:

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')

这篇关于查找包含 NaT 的 Pandas 数据框中两列之间的工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！