How to append column values in Spark SQL?

Question

I have the below table:

+-------+---------+---------+
|movieId|movieName|    genre|
+-------+---------+---------+
|      1| example1|   action|
|      1| example1| thriller|
|      1| example1|  romance|
|      2| example2|fantastic|
|      2| example2|   action|
+-------+---------+---------+

What I am trying to achieve is to append the genre values together where the id and name are the same. Like this:

+-------+---------+---------------------------+
|movieId|movieName|    genre                  |
+-------+---------+---------------------------+
|      1| example1|   action|thriller|romance |
|      2| example2|   action|fantastic        |
+-------+---------+---------------------------+

Shaido · Accepted Answer · 2018-01-15 09:49:08Z

2

Use groupBy and collect_list to get a list of all items with the same movie name. Then combine these to a string using concat_ws (if the order is important, first use sort_array). Small example with given sample dataframe:

val df2 = df.groupBy("movieId", "movieName")
  .agg(collect_list($"genre").as("genre"))
  .withColumn("genre", concat_ws("|", sort_array($"genre")))

Gives the result:

+-------+---------+-----------------------+
|movieId|movieName|genre                  |
+-------+---------+-----------------------+
|1      |example1 |action|thriller|romance|
|2      |example2 |action|fantastic       |
+-------+---------+-----------------------+

answered Jan 15, 2018 at 9:49

Shaido

28.6k26 gold badges76 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to append column values in Spark SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related