How to split pipe-separated column into multiple rows?

Question

I have a dataframe that contains the following:

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

I would like to obtain a second dataframe (from the first one), that contains the following:

movieId / movieName / genre
1         example1    action
1         example1    thriller
1         example1    romance
2         example2    fantastic
2         example2    action

How I can do that?

Jacek Laskowski · Accepted Answer · 2018-11-21T13:42:57.133

36

I'd use split standard function.

scala> movies.show(truncate = false)
+-------+---------+-----------------------+
|movieId|movieName|genre                  |
+-------+---------+-----------------------+
|1      |example1 |action|thriller|romance|
|2      |example2 |fantastic|action       |
+-------+---------+-----------------------+

scala> movies.withColumn("genre", explode(split($"genre", "[|]"))).show
+-------+---------+---------+
|movieId|movieName|    genre|
+-------+---------+---------+
|      1| example1|   action|
|      1| example1| thriller|
|      1| example1|  romance|
|      2| example2|fantastic|
|      2| example2|   action|
+-------+---------+---------+

// You can use \\| for split instead
scala> movies.withColumn("genre", explode(split($"genre", "\\|"))).show
+-------+---------+---------+
|movieId|movieName|    genre|
+-------+---------+---------+
|      1| example1|   action|
|      1| example1| thriller|
|      1| example1|  romance|
|      2| example2|fantastic|
|      2| example2|   action|
+-------+---------+---------+

p.s. You could use Dataset.flatMap to achieve the same result which is something Scala devs would enjoy more I'm sure.

edited Nov 21 '18 at 13:42

answered May 14 '17 at 18:05

Jacek Laskowski

72,696
27
242
420

'@Jacek..you mention about other answers, but there is only one answer which is yours.. – stack0114106 Nov 21 '18 at 12:18
@stack0114106 Thanks. They seem to have been deleted in the meantime. Cut the noise from the answer. – Jacek Laskowski Nov 21 '18 at 13:43
@JacekLaskowski How can we split, if we have two columns with | seperator. For example in addition to genre if we also have an 'actors' column having multiple names. – amk Apr 27 '19 at 18:20
@Ahmad Care to ask a separate question so we have more space and visibility to discuss your use case? Thanks. – Jacek Laskowski Apr 28 '19 at 15:41

stack0114106 · Answer 2 · 2018-11-21T14:46:16.907

Using RDD

val df = Seq((1,"example1","action|thriller|romance"),(2,"example2","fantastic|action")).toDF("Id","name","genre")
df.rdd.flatMap( x=>{ val p = x.getAs[String]("genre"); for { a <- p.split("[|]") } yield (x(0),x(1),a)} ).foreach(println)

Results:

(1,example1,action)
(2,example2,fantastic)
(1,example1,thriller)
(2,example2,action)
(1,example1,romance)

To converting back to DF

val rdd1 = df.rdd.flatMap( x=>{ val p = x.getAs[String]("genre"); for { a <- p.split("[|]") } yield Row(x(0),x(1),a)} )
spark.createDataFrame(rdd1,df.schema.copy(Array(StructField("Id",IntegerType),StructField("name",StringType))).add(StructField("genre2",StringType))).show(false)

How to split pipe-separated column into multiple rows?

2 Answers2

Linked

Related