Nested Pivot in Spark Dataframe

Question

I have below Two DF

MasterDF

NumberDF(Creating using Hive load)

Desire output:

Logic to populate

For Field1 need to pick sch_id where CAT='PAY' and SUB_CAT='client'
For Field2 need to pick sch_id where CAT='PAY' and SUB_CAT='phr'
For Field3 need to pick pay_id where CAT='credit' and SUB_CAT='spGrp'

Currently before joining I performing filter on NumberDF and the picking the value EX:

 masterDF.as("master").join(NumberDF.filter(col("CAT")==="PAY" && col("SUB_CAT")==="phr").as("number"), "$master.id" ==="$number.id" , "leftouter" )
 .select($"master.*", $"number.sch_id".as("field1") )

above approach would need multiple join. I look into pivot function but it does solve my problem

Note: Please ignore the syntax error in code

score 0 · Answer 1 · answered Mar 22 '19 at 10:37

Better solution to do this is to pivot DataFrame (numberDF) by column (subject) before joining with studentDF.

pyspark code looks like this

numberDF = spark.createDataFrame([(1, "Math", 80), (1, "English", 60), (1, "Science", 80)], ["id", "subject", "marks"])
studentDF = spark.createDataFrame([(1, "Vikas")],["id","name"])

>>> numberDF.show()
+---+-------+-----+
| id|subject|marks|
+---+-------+-----+
|  1|   Math|   80|
|  1|English|   60|
|  1|Science|   80|
+---+-------+-----+

>>> studentDF.show()
+---+-----+
| id| name|
+---+-----+
|  1|Vikas|
+---+-----+

pivotNumberDF = numberDF.groupBy("id").pivot("subject").sum("marks")

>>> pivotNumberDF.show()
+---+-------+----+-------+
| id|English|Math|Science|
+---+-------+----+-------+
|  1|     60|  80|     80|
+---+-------+----+-------+

>>> studentDF.join(pivotNumberDF, "id").show()
+---+-----+-------+----+-------+
| id| name|English|Math|Science|
+---+-----+-------+----+-------+
|  1|Vikas|     60|  80|     80|
+---+-----+-------+----+-------+

ref: http://spark.apache.org/docs/2.4.0/api/python/pyspark.sql.html

Vikas Singh · Accepted Answer · 2019-03-23T17:13:27.630

Finally I have implemented it using Pivot

flights.groupBy("ID", "CAT")
      .pivot("SUB_CAT", Seq("client", "phr", "spGrp")).agg(avg("SCH_ID").as("SCH_ID"), avg("pay_id").as("pay_id"))
      .groupBy("ID")
      .pivot("CAT", Seq("credit", "price"))
      .agg(
        avg("client_SCH_ID").as("client_sch_id"), avg("client_pay_id").as("client_pay_id")
        , avg("phr_SCH_ID").as("phr_SCH_ID"), avg("phr_pay_id").as("phr_pay_id")
        , avg("spGrp_SCH_ID").as("spGrp_SCH_ID"), avg("spGrp_pay_id").as("spGrp_pay_id")
      )

First Pivot would Return table like

+---+------+-------------+--------------+-----------+------------+-------------+--------------+
| ID|   CAT|client_SCH_ID|client_pay_id |phr_SCH_ID |phr_pay_id  |spnGrp_SCH_ID|spnGrp_pay_id |
+---+------+-------------+--------------+-----------+------------+-------------+--------------+
|  1|credit|          5.0|         105.0|        4.0|       104.0|          6.0|         106.0|
|  1| pay  |          2.0|         102.0|        1.0|       101.0|          3.0|         103.0|
+---+------+-------------+--------------+-----------+------------+-------------+--------------+

After second Pivot it would be like

+---+--------------------+---------------------+------------------+-------------------+--------------------+---------------------+-----------------+------------------+-----------------+------------------+-----------------+------------------+
| ID|credit_client_sch_id|credit_client_pay_id | credit_phr_SCH_ID| credit_phr_pay_id |credit_spnGrp_SCH_ID|credit_spnGrp_pay_id |pay_client_sch_id|pay_client_pay_id |   pay_phr_SCH_ID|   pay_phr_pay_id |pay_spnGrp_SCH_ID|pay_spnGrp_pay_id |
+---+--------------------+---------------------+------------------+-------------------+--------------------+---------------------+-----------------+------------------+-----------------+------------------+-----------------+------------------+
|  1|                 5.0|                105.0|               4.0|              104.0|                 6.0|                106.0|             2.0|              102.0|              1.0|             101.0|              3.0|             103.0|
+---+--------------------+---------------------+------------------+-------------------+--------------------+---------------------+-----------------+------------------+-----------------+------------------+-----------------+------------------+

Though I am not sure about performance.

score -3 · Answer 3 · edited Mar 22 '19 at 12:59

df.createOrReplaceTempView("NumberDF")

df.createOrReplaceTempView("MasterDf")

val sqlDF = spark.sql("select m.id,t1.fld1,t2.fld2,t3.fld3,m.otherfields 
from 
(select id, (case when n.cat='pay' and n.sub_cat ='client' then n.sch_id end) fld1
from NumberDF n where case when n.cat='pay' and n.sub_cat ='client' then n.sch_id end is not null ) t1 ,
(select id, (case when  n.cat='pay' and n.sub_cat ='phr' then n.sch_id end) fld2
from NumberDF n where case when  n.cat='pay' and n.sub_cat ='phr' then n.sch_id end is not null ) t2,
(select id, (case when  n.cat='credit' and n.sub_cat ='spGrp' then n.pay_id end) fld3
from NumberDF n where case when  n.cat='credit' and n.sub_cat ='spGrp' then n.pay_id end is not null ) t3,
MasterDf m ")

sqlDF.show()

Perhaps you would like to also *explain* this answer? – tripleee Mar 22 '19 at 12:59 — tripleee, Mar 22 '19 at 12:59
May be not optimized but will solve your prob – user3841042 Mar 22 '19 at 13:03 — user3841042, Mar 22 '19 at 13:03

Nested Pivot in Spark Dataframe

3 Answers3