Calculate difference between two time columns in pandas as a new column excluding weekends, when the columns may contain NaT

user_12367592 0
Viewed 635

I need to calculate the difference between two pandas df columns which contain dates. I need the difference to be in days and not to contain weekends.

I know I can use

 np.busday_count()

to achieve this. I also know that for this function to work correctly I may need to swap to the datatype to datetime[64].

My problem is that my data may contain NaT in either input column for some values due to the source of the data. np.nusday_count() does not like this.

Is there a way I can calculate the difference between two columns containing dates, in days, while excluding the weekends, not running the calculation/returning a blank for anywhere where either column 1 or 2 would be NaT and input the data into a new column in the dataframe. I've been trying unsuccessfully for hours.

Frustratingly, if I did not have to ignore weekends, this can all be done very simply by just doing

df['Difference in Days'] = df['Column 2] - df['Column 1']

For what I want to achieve the initial df would be like this

Column 1       Column 2
07/10/20       09/10/20
07/10/20       12/10/20
NaT            09/10/20
07/10/20       NaT

With the final df like this

Column 1       Column 2    Difference in Days
07/10/20       09/10/20    2
07/10/20       12/10/20    3
NaT            09/10/20    Blank
07/10/20       NaT         Blank
pandas numpy python
3 Answers

You can create a custom function to catch the ValueError you are getting due to the NaT values:

First transform the values into datetime format:

for i in df:
  df[i] = pd.to_datetime(df[i],infer_datetime_format=True,errors='coerce',dayfirst=True)

Custom function wit the Try/Except clause to catch the errors, and return "Blank".

def diff_days(row):
  try:
    return np.busday_count(row['Column 1'].date(), row['Column 2'].date())
  except ValueError:
    return "Blank"

Using apply:

df['Diff'] = df.apply(lambda x: diff_days(x),axis=1)

Returns:

    Column 1   Column 2   Diff
0 2020-07-10 2020-10-09     2
1 2020-07-10 2020-10-12     3 
2        NaT 2020-09-10  Blank
3 2020-07-10        NaT  Blank
user_118970070

Here's a vectorized solution: you can filter out the NaTs before sending them into np.busday_count:

# If you haven't done so already, convert the columns to Timestamp
cols = ['Column 1', 'Column 2']
df[cols] = df[cols].apply(lambda col: pd.to_datetime(col, dayfirst=True, errors='coerce'))

# Look for the NaTs and remove them
mask = df[cols].notnull().all(axis=1)

# numpy does not like datetime64[ns] so convert to datetime64[D]
c1, c2 = df.loc[mask, cols].T.to_numpy('datetime64[D]')

# And the result
df['Diff'] = pd.Series(np.busday_count(c1, c2), index=mask[mask].index)
user_25389390

I assume that both columns have been converted to datetime.

The first step is to create wrk - a DataFrame with rows outside the weekends and wrkV - the same dates converted to Numpy array, alse of datetime but with D resolution:

wrk = df[df['Column 2'].dt.dayofweek.lt(5) & df['Column 1'].dt.dayofweek.lt(5)]
wrkV = wrk.values.astype('<M8[D]')

And to generate your new column, run:

df['Difference in Days'] = pd.Series(np.busday_count(wrkV[:, 0], wrkV[:, 1]), index=wrk.index)
df['Difference in Days'].fillna('Blank', inplace=True)

The result is:

    Column 1   Column 2 Difference in Days
0 2020-10-07 2020-10-09                  2
1 2020-10-07 2020-10-12                  3
2        NaT 2020-10-09              Blank
3 2020-10-07        NaT              Blank
user_73884770
Related