How to transform row-information into columns?

Question

I have the following DataFrame in Spark 2.2 and Scala 2.11.8:

+--------+---------+-------+-------+----+-------+
|event_id|person_id|channel|  group|num1|   num2|
+--------+---------+-------+-------+----+-------+
|     560|     9410|    web|     G1|   0|      5|
|     290|     1430|    web|     G1|   0|      3|
|     470|     1370|    web|     G2|   0|     18|
|     290|     1430|    web|     G2|   0|      5|
|     290|     1430|    mob|     G2|   1|      2|
+--------+---------+-------+-------+----+-------+

Here is the equivalent DataFrame in Scala:

df = sqlCtx.createDataFrame(
    [(560,9410,"web","G1",0,5), 
     (290,1430,"web","G1",0,3), 
     (470,1370,"web","G2",0,18), 
     (290,1430,"web","G2",0,5), 
     (290,1430,"mob","G2",1,2)],
    ["event_id","person_id","channel","group","num1","num2"]
)

The column group can only have two values: G1 and G2. I need to transform these values of the column group into new columns as follows:

+--------+---------+-------+--------+-------+--------+-------+
|event_id|person_id|channel| num1_G1|num2_G1| num1_G2|num2_G2|
+--------+---------+-------+--------+-------+--------+-------+
|     560|     9410|    web|       0|      5|       0|      0|
|     290|     1430|    web|       0|      3|       0|      0|
|     470|     1370|    web|       0|      0|       0|     18|
|     290|     1430|    web|       0|      0|       0|      5|
|     290|     1430|    mob|       0|      0|       1|      2|
+--------+---------+-------+--------+-------+--------+-------+

How can I do it?

Possible duplicate of [Reshaping/Pivoting data in Spark RDD and/or Spark DataFrames](https://stackoverflow.com/questions/30260015/reshaping-pivoting-data-in-spark-rdd-and-or-spark-dataframes) — Jacek Laskowski, Dec 29 '17 at 15:16

MaxU - stand with Ukraine · Accepted Answer · 2017-12-29T14:10:36.510

AFAIK (at least i couldn't find a way to perform PIVOT without aggregation) we must use aggregation function when doing pivoting in Spark

Scala version:

scala> df.groupBy("event_id","person_id","channel")
         .pivot("group")
         .agg(max("num1") as "num1", max("num2") as "num2")
         .na.fill(0)
         .show
+--------+---------+-------+-------+-------+-------+-------+
|event_id|person_id|channel|G1_num1|G1_num2|G2_num1|G2_num2|
+--------+---------+-------+-------+-------+-------+-------+
|     560|     9410|    web|      0|      5|      0|      0|
|     290|     1430|    web|      0|      3|      0|      5|
|     470|     1370|    web|      0|      0|      0|     18|
|     290|     1430|    mob|      0|      0|      1|      2|
+--------+---------+-------+-------+-------+-------+-------+

How to transform row-information into columns?

1 Answers1