%lsmagic sc.version val unpivotedDf = Seq( ("john", "notebook", 2), ("gary", "notebook", 3), ("john", "small_phone", 2), ("mary", "small_phone", 3), ("john", "large_phone", 3), ("john", "camera", 3) ).toDF("salesperson","device", "amount_sold").sort("salesperson","device") %%dataframe unpivotedDf %%dataframe unpivotedDf.groupBy("salesperson").pivot("device").sum("amount_sold") val pivotedDf = Seq( ("gary", None, None, Some(3),None), ("mary", None, None, None, Some(3)), ("john", Some(3),Some(3),Some(2),Some(2)) ).toDF("salesperson", "camera", "large_phone", "notebook", "small_phone") %%dataframe pivotedDf val unpivotedDf = pivotedDf .selectExpr("salesperson","stack(4,'camera',camera,'large_phone',large_phone,'notebook',notebook,'small_phone',small_phone)") .withColumnRenamed("col0","device") // default name of this column is col0 .withColumnRenamed("col1","amount_sold") // default name of this column is col1 .filter($"amount_sold".isNotNull) // must explicitly remove nulls %%dataframe --limit=100 unpivotedDf.sort("salesperson","device")