Extracting array index in Spark Dataframe

Question

I have a Dataframe with a Column of Array Type For example :

val df = List(("a", Array(1d,2d,3d)), ("b", Array(4d,5d,6d))).toDF("ID", "DATA")
df: org.apache.spark.sql.DataFrame = [ID: string, DATA: array<double>]

scala> df.show
+---+---------------+
| ID|           DATA|
+---+---------------+
|  a|[1.0, 2.0, 3.0]|
|  b|[4.0, 5.0, 6.0]|
+---+---------------+

I wish to explode the array and have index like

+---+------------------+
| ID|  DATA_INDEX| DATA|
+---+------------------+
|  a|1           | 1.0 |
|  a|2           | 2.0 |
|  a|3           | 3.0 |
|  b|1           | 4.0 |
|  b|2           | 5.0 |
|  b|3           | 6.0 |
+---+------------+-----+

I wish be able to do that with scala, and Sparlyr or SparkR I'm using spark 1.6

koiralo · Accepted Answer · 2018-03-09 14:38:27Z

8

There is a posexplode function available in spark functions

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

   df.select("ID", posexplode($"DATA))

PS: This is only available after 2.1.0 versions

edited Mar 9, 2018 at 14:38

answered Mar 9, 2018 at 14:30

koiralo

23.2k6 gold badges57 silver badges77 bronze badges

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

Comments

philantrovert · Accepted Answer · 2018-03-09 14:44:48Z

4

With Spark 1.6, you can register you dataframe as a temporary table and then run Hive QL over it to get the desired result.

df.registerTempTable("tab")

sqlContext.sql("""
    select 
       ID, exploded.DATA_INDEX + 1 as DATA_INDEX, exploded.DATA  
    from 
       tab 
    lateral view posexplode(tab.DATA) exploded as DATA_INDEX, DATA 
""").show

+---+----------+----+
| ID|DATA_INDEX|DATA|
+---+----------+----+
|  a|         1| 1.0|
|  a|         2| 2.0|
|  a|         3| 3.0|
|  b|         1| 4.0|
|  b|         2| 5.0|
|  b|         3| 6.0|
+---+----------+----+

answered Mar 9, 2018 at 14:44

philantrovert

10.1k3 gold badges42 silver badges65 bronze badges

1 Comment

kingledion Over a year ago

Does not work in spark 1.6.3: "failure: ``union'' expected but identifier view found: lateral view posexplode(intakeDf.sentences) exploded as sentId, sent"

Collectives™ on Stack Overflow

Extracting array index in Spark Dataframe

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related