Spark explode multiple columns of row in multiple rows

Question

I have a problem with converting one row using three 3 columns into 3 rows
For example:

 <pre>
<b>ID</b> |  <b>String</b>  |  <b>colA</b> | <b>colB</b> | <b>colC</b>
<em>1</em>  | <em>sometext</em> |   <em>1</em>   |  <em>2</em>   |  <em>3</em>
</pre>

I need to convert it into:

<pre>
<b>ID</b> |  <b>String</b>  |  <b>resultColumn</b>
<em>1</em>  | <em>sometext</em> |     <em>1</em>  
<em>1</em>  | <em>sometext</em> |     <em>2</em>   
<em>1</em>  | <em>sometext</em> |     <em>3</em>    
</pre>

I just have dataFrame which is connected with first schema(table).

val df: dataFrame

Note: I can do it using RDD, but do we have other way? Thanks

greg hor · Accepted Answer · 2018-04-28 17:16:51Z

1

Assuming that df has the schema of your first snippet, I would try:

df.select($"ID", $"String", explode(array($"colA", $"colB",$"colC")).as("resultColumn"))

I you further want to keep the column names, you can use a trick that consists in creating a column of arrays that contains the array of the value and the name. First create your expression

val expr = explode(array(array($"colA", lit("colA")), array($"colB", lit("colB")), array($"colC", lit("colC"))))

then use getItem (since you can not use generator on nested expressions, you need 2 select here)

df.select($"ID, $"String", expr.as("tmp")).select($"ID", $"String", $"tmp".getItem(0).as("resultColumn"), $"tmp".getItem(1).as("columnName"))

It is a bit verbose though, there might be more elegant way to do this.

edited Apr 28, 2018 at 17:16

answered Jan 23, 2018 at 22:01

greg hor

8329 silver badges21 bronze badges

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

3 Comments

Eldinea Over a year ago

Hi Greg, how would you keep the column name into a separate column ?

greg hor Over a year ago

@Eldinea, I edited my answer to address your question as well

Eldinea Over a year ago

Actually you can easily do it with posexplode : posexplode(array($"col1",$"col2") And you will have two columns : pos for the number of the column and col for the value spark.apache.org/docs/latest/api/sql/#posexplode

Collectives™ on Stack Overflow

Spark explode multiple columns of row in multiple rows

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related