How to transform the below table to required format?

user_14071612 0
Viewed 43

I have following table loaded as a dataframe :

Id  Name   customCount    Custom1    Custom1value  custom2    custom2Value   custom3    custom3Value
1  qwerty     2            Height        171        Age            76         Null         Null
2   asdfg     2           Weight          78       Height         166          Null        Null
3   zxcvb     3            Age            28       SkinColor    white         Height        67
4   tyuio     1           Height         177         Null        Null          Null        Null
5   asdfgh    2           SkinColor     brown        Age          34          Null         Null

I need to change this table into below format :

Id  Name    customCount Height     Weight   Age   SkinColor
1  qwerty       2        171        Null    76      Null
2   asdfg       2        161         78     Null    Null
3   zxcvb       3         67        Null    28      white  
4   tyuio       1        177        Null    Null    Null
5   asdfgh      2        Null       Null    34      brown

I tried for two custom fields columns :

val rawDf= spark.read.option("Header",false).options(Map("sep"->"|")).csv("/sample/data.csv")
rawDf.createOrReplaceTempView("Table")
val dataframe=spark.sql("select distinct * from (select `_c3` from Table union select `_c5` from Table)")
val dfWithDistinctColumns=dataframe.toDF("colNames")
val list=dfWithDistinctColumns.select("colNames").map(x=>x.getString(0)).collect().toList
val rawDfWithSchema=rawDf.toDF("Id","Name",customCount","h1","v1","h2","v2")
val expectedDf=list.foldLeft(rawDfWithSchema)((df1,c)=>(df1.withColumn(c, when(col("h1")===c,col("v1")).when(col("h2")===c,col("v2")).otherwise(null)))).drop("h1","h2","v1","v2")

But I am not able to do union on multiple columns when I try it on 3 custom fields . Can you please give any idea/solution for this?

apache-spark-sql apache-spark scala
1 Answers

You can do a pivot, but you also need to clean up the format of the dataframe first:

val df2 = df.select(
    $"Id", $"Name", $"customCount",
    explode(array(
        array($"Custom1", $"Custom1value"),
        array($"custom2", $"custom2Value"),
        array($"custom3", $"custom3Value")
    )).alias("custom")
).select(
    $"Id", $"Name", $"customCount",
    $"custom"(0).alias("key"),
    $"custom"(1).alias("value")
).groupBy(
    "Id", "Name", "customCount"
).pivot("key").agg(first("value")).drop("null").orderBy("Id")

df2.show
+---+------+-----------+----+------+---------+------+
| Id|  Name|customCount| Age|Height|SkinColor|Weight|
+---+------+-----------+----+------+---------+------+
|  1|qwerty|          2|  76|   171|     null|  null|
|  2| asdfg|          2|null|   166|     null|    78|
|  3| zxcvb|          3|  28|    67|    white|  null|
|  4| tyuio|          1|null|   177|     null|  null|
|  5|asdfgh|          2|  34|  null|    brown|  null|
+---+------+-----------+----+------+---------+------+
user_141657300
Related