Explode array data into rows in spark [duplicate]

Question

I have a dataset in the following way:

FieldA    FieldB    ArrayField
1         A         {1,2,3}
2         B         {3,5}

I would like to explode the data on ArrayField so the output will look in the following way:

FieldA    FieldB    ExplodedField
1         A         1
1         A         2
1         A         3
2         B         3
2         B         5

I mean I want to generate an output line for each item in the array the in ArrayField while keeping the values of the other fields.

How would you implement it in Spark. Notice that the input dataset is very large.

have you looked at the explode() function?

mtoto
– mtoto

2017-06-08 13:18:01 +00:00
Commented Jun 8, 2017 at 13:18 — mtoto
– mtoto, Commented Jun 8, 2017 at 13:18

rogue-one · Accepted Answer · 2017-06-08 13:34:39Z

84

The explode function should get that done.

pyspark version:

>>> df = spark.createDataFrame([(1, "A", [1,2,3]), (2, "B", [3,5])],["col1", "col2", "col3"])
>>> from pyspark.sql.functions import explode
>>> df.withColumn("col3", explode(df.col3)).show()
+----+----+----+
|col1|col2|col3|
+----+----+----+
|   1|   A|   1|
|   1|   A|   2|
|   1|   A|   3|
|   2|   B|   3|
|   2|   B|   5|
+----+----+----+

Scala version

scala> val df = Seq((1, "A", Seq(1,2,3)), (2, "B", Seq(3,5))).toDF("col1", "col2", "col3")
df: org.apache.spark.sql.DataFrame = [col1: int, col2: string ... 1 more field]

scala> df.withColumn("col3", explode($"col3")).show()
+----+----+----+
|col1|col2|col3|
+----+----+----+
|   1|   A|   1|
|   1|   A|   2|
|   1|   A|   3|
|   2|   B|   3|
|   2|   B|   5|
+----+----+----+

edited Jun 8, 2017 at 13:34

answered Jun 8, 2017 at 13:27

rogue-one

11.6k8 gold badges56 silver badges79 bronze badges

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

Comments

koiralo · Accepted Answer · 2019-08-13 13:35:38Z

3

You can use explode function Below is the simple example for your case

import org.apache.spark.sql.functions._
import spark.implicits._

  val data = spark.sparkContext.parallelize(Seq(
    (1, "A", List(1,2,3)),
    (2, "B", List(3, 5))
  )).toDF("FieldA", "FieldB", "FieldC")

    data.withColumn("ExplodedField", explode($"FieldC")).drop("FieldC")

Hope this helps!

edited Aug 13, 2019 at 13:35

answered Jun 8, 2017 at 13:28

koiralo

23.2k6 gold badges57 silver badges77 bronze badges

Comments

Ryan Widmaier · Accepted Answer · 2017-06-08 13:28:49Z

2

explode does exactly what you want. Docs:

http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.functions.explode

Also, here is an example from a different question using it:

https://stackoverflow.com/a/44418598/1461187

answered Jun 8, 2017 at 13:28

Ryan Widmaier

8,6232 gold badges33 silver badges33 bronze badges

Collectives™ on Stack Overflow

Explode array data into rows in spark [duplicate]

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related