Pivot of Column

 

val orderData = Seq(("ordernumber1", "Shipt", 3000 , "Texas"),

("ordernumber1", "DriveUp", 4600, "Florida"),

("ordernumber2", "ShipToHome", 4100 , "California"),

("ordernumber3", "Shipt", 3000, "Pennsylvania"),

("ordernumber4", "DriveUp", 5000, "Texas"),

("ordernumber5", "ShipToHome", 3300, "Florida"),

("ordernumber6", "ShipToHome", 3900, "California"),

("ordernumber7", "ShipToHome", 7000, "Pennsylvania"),

("ordernumber8", "DriveUp", 4100, "Texas"))

val df = orderData.toDF("orderNumber", "orderType", "amount" , "state" )

+------------+----------+------+------------+

|orderNumber |orderType |amount|state |

+------------+----------+------+------------+

|ordernumber1|Shipt |3000 |Texas |

|ordernumber1|DriveUp |4600 |Florida |

|ordernumber2|ShipToHome|4100 |California |

|ordernumber3|Shipt |3000 |Pennsylvania|

|ordernumber4|DriveUp |5000 |Texas |

|ordernumber5|ShipToHome|3300 |Florida |

|ordernumber6|ShipToHome|3900 |California |

|ordernumber7|ShipToHome|7000 |Pennsylvania|

|ordernumber8|DriveUp |4100 |Texas |

+------------+----------+------+------------+

val df2 = df.groupBy("orderType").pivot("state").agg(max("amount") as "maxAmount")

df2.show(false)

+----------+----------+-------+------------+-----+

|orderType |California|Florida|Pennsylvania|Texas|

+----------+----------+-------+------------+-----+

|ShipToHome|4100 |3300 |7000 |null |

|Shipt |null |null |3000 |3000 |

|DriveUp |null |4600 |null |5000 |

+----------+----------+-------+------------+-----+

val df2 = df.groupBy("orderType").pivot("state").agg(max("amount") as "maxAmount" , min("amount") as "minAmount")

+----------+--------------------+--------------------+-----------------+-----------------+----------------------+----------------------+---------------+---------------+

|orderType |California_maxAmount|California_minAmount|Florida_maxAmount|Florida_minAmount|Pennsylvania_maxAmount|Pennsylvania_minAmount|Texas_maxAmount|Texas_minAmount|

+----------+--------------------+--------------------+-----------------+-----------------+----------------------+----------------------+---------------+---------------+

|ShipToHome|4100 |3900 |3300 |3300 |7000 |7000 |null |null |

|Shipt |null |null |null |null |3000 |3000 |3000 |3000 |

|DriveUp |null |null |4600 |4600 |null |null |5000 |4100 |

+----------+--------------------+--------------------+-----------------+-----------------+----------------------+----------------------+---------------+---------------+