I have the following dataframe:
|id|year|
|--|----|
|a |2015|
|a |2016|
|a |2017|
|b |2015|
|b |2017|
I can pivot it to get:
|id|2015|2016|2017|
|--|----|----|----|
|a | 1 | 1 | 1 |
|b | 1 | 0 | 1 |
using:
val result = data.groupBy("id").pivot("year").count().na.fill(0)
I want to add a total column (The sum of all years). What is the most efficient way of getting this?
|id|2015|2016|2017|Total|
|--|----|----|----|-----|
|a | 1 | 1 | 1 | 3 |
|b | 1 | 0 | 1 | 2 |
I currently take the above result and sum the years columns.
val columnsToSum = result.columns.filterNot(c=> Seq("id").contains(c))
result.withColumn("Total", columnsToSum.map(col(_)).reduce(_ + _))
Is there a better way to do this (a built in function)?