Count number of days between dates, ignoring weekends using pyspark

user_8236076 0
Viewed 2697

How can I calculate number of days between two dates ignoring weekends, using pyspark?

This is the exact same question as here, only I need to do this with pyspark.

I tried using a udf:

import numpy as np
from pyspark.sql.functions import udf
from pyspark.sql.types import IntegerType

@udf(returnType=IntegerType())
def dateDiffWeekdays(end, start):
    return int(np.busday_count(start, end)) # numpy returns an `numpy.int64` type.

When using this udf, I get an error message:

ModuleNotFoundError: No module named 'numpy'

Does anyone know how to solve this? Or better yet, to solve this without a udf in native pyspark?

EDIT: I have numpy installed. Outside of a udf it works just fine.

pyspark python
2 Answers

For Spark 2.4+ it is possible to get the number of days without the usage of numpy or udf. Using the built-in SQL functions is sufficient.

Following roughly this answer we can

  1. create an array of dates containing all days between begin and end by using sequence
  2. transform the single days into a struct holding the day and its day of week value
  3. filter out the days that are Saturdays and Sundays
  4. get the size of the remaining array
#create an array containing all days between begin and end
(df.withColumn('days', F.expr('sequence(begin, end, interval 1 day)'))
#keep only days where day of week (dow) <= 5 (Friday)
.withColumn('weekdays', F.expr('filter(transform(days, day->(day, extract(dow_iso from day))), day -> day.col2 <=5).day')) 
#count how many days are left
.withColumn('no_of_weekdays', F.expr('size(weekdays)')) 
#drop the intermediate columns
.select('begin', 'end', 'no_of_weekdays') 
.show(truncate=False))

Output:

+----------+----------+--------------+
|begin     |end       |no_of_weekdays|
+----------+----------+--------------+
|2020-09-19|2020-09-20|0             |
|2020-09-21|2020-09-24|4             |
|2020-09-21|2020-09-25|5             |
|2020-09-21|2020-09-26|5             |
|2020-09-21|2020-10-02|10            |
|2020-09-19|2020-10-03|10            |
+----------+----------+--------------+

For Spark <= 2.3 you would have to use an udf. If numpy is a problem a solution inspired by this answer can be used.

from datetime import timedelta
@F.udf
def dateDiffWeekdays(end, start):
    daygenerator = (start + timedelta(x) for x in range((end - start).days + 1))
    return sum(1 for day in daygenerator if day.isoweekday() <= 5)

df.withColumn("no_of_weekdays", dateDiffWeekdays(df.end, df.begin)).show()
user_21298010

following @werner approach, I got the result but there were some discrepancies with the usage of buit-in DOW_ISO function.

"DAYOFWEEK_ISO",("DOW_ISO") - ISO 8601 based day of the week for datetime as Monday(1) to Sunday(7) (ps: ref)

Using weekday(date) - Returns the day of the week for date/timestamp (0 = Monday, 1 = Tuesday, ..., 6 = Sunday). This met the requirment.

F.expr('filter(transform(days, day->(day, weekday(day))), day -> day.col2 <= 4).day')
user_167659470
Related