Skip to main content
ChineduLB
ChineduLB
Silver ⭐️⭐️⭐️
April 15, 2020
Solved

Get column values in comma separated value

I have a source table Like

IDUSERDEPT
1User1Admin
2User1Accounts
3User2Finance
4User3Sales
5User3Finance

 

I want to generate a DataFrame like this

IDUSERDEPARTMENT
1User1Admin,Accounts
2User2Finance
3User3Sales,Finance
Shu_ashu
Shu_ashuAnswer
Gold ⭐️⭐️⭐️
April 16, 2020

Hi @ChineduLB ,

You can use `.groupBy` and `concat_ws(",",collect_list)` functions and to generate `ID` use `row_number` window function.

 

val df=Seq(("1","User1","Admin"),("2","User1","Accounts"),("3","User2","Finance"),("4","User3","Sales"),("5","User3","Finance")).toDF("ID","USER","DEPT")

import org.apache.spark.sql.expressions.Window

df.groupBy("USER"). agg(concat_ws(",",collect_list("DEPT")).alias("DEPARTMENT")). withColumn("ID",row_number().over(w)). select("ID","USER","DEPARTMENT").show()

View in thread

3 replies

pauldefusco
Silver ⭐️⭐️⭐️
pauldefusco
Silver ⭐️⭐️⭐️
April 15, 2020

Hi Chinedu,

 

This should help: https://stackoverflow.com/questions/48406304/groupby-and-concat-array-columns-pyspark

 

Thanks,

Paul

Translate
    ChineduLB
    ChineduLBAuthor
    Silver ⭐️⭐️⭐️
    April 15, 2020

    Thanks @pauldefusco

    I would like to do it in spark - scala

    Translate
    Shu_ashu
    Shu_ashuAnswer
    Gold ⭐️⭐️⭐️
    April 16, 2020

    Hi @ChineduLB ,

    You can use `.groupBy` and `concat_ws(",",collect_list)` functions and to generate `ID` use `row_number` window function.

     

    val df=Seq(("1","User1","Admin"),("2","User1","Accounts"),("3","User2","Finance"),("4","User3","Sales"),("5","User3","Finance")).toDF("ID","USER","DEPT")

    import org.apache.spark.sql.expressions.Window

    df.groupBy("USER"). agg(concat_ws(",",collect_list("DEPT")).alias("DEPARTMENT")). withColumn("ID",row_number().over(w)). select("ID","USER","DEPARTMENT").show()

    Translate
    Terms & ConditionsAccessibility statement