It should just be a matter of applying the correct set of transformations:
- You can start by adding the week-of-year to each record with the command pyspark.sql.functions.weekofyear(..) and name it something like weekOfYear. See https://spark.apache.org/docs/latest/api/python/pyspark.sql.html?highlight=functions#pyspark.sql.fun...
- To get the day-of-week, you can use pyspark.sql.functions.date_format(..) with the format of "u" which yields the number of the week and name it something like dayOfWeek See https://spark.apache.org/docs/latest/api/python/pyspark.sql.html?highlight=functions#pyspark.sql.fun... and then https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.
- Next would be to add one column for each day of the week (seven steps in all).
- The first part would be to use pyspark.sql.functions.when( condition, value
- ). See https://spark.apache.org/docs/latest/api/python/pyspark.sql.html?highlight=functions#pyspark.sql.fun...
- The condition would be something like col("dayOfWeek") == "1" and the value would be columnWhatever.
- You can then append to the when(..) call an otherwise(..) expression with 0 as in when(condition, value).otherwise(0). See https://spark.apache.org/docs/latest/api/python/pyspark.sql.html?highlight=functions#pyspark.sql.fun...
- Repeat that six more times
At this stage the data will look something like this:
But the real trick is going to be to flatten it and all that should be required is to group by weekOfYear and then select with that the sum of each column (S,M,T,W,T,F,S) and that last call would look something like this:
df.groupBy("weekOfYear")
.sum("Sun", "Mon","Tue", "Wed", "Thu", "Fri", "Sat")--------------- UPDATE ------------
The solution involving sum(..) works if you have numerical data. However, if you have non-numerical data, we need a slightly different technique. In this case, we can still group by weekOfYear but instead of using sum(..) we can use agg(first(...), first(...), ...).
Thanks to Adam for the suggestion of using sum(..) and to @doug for the suggestion of using agg(first(..)).
As we were playing with this, I did put together a sample notebook that demonstrates both solutions .
