Dataframe explode list columns in multiple rows

Question

I have the below Dataframe with each column containing a list of values of the same size

--------+--------------------+--------------------+--------------------+--------------
       |Country_1|               |Country_2|          |Country_3|          |Country_4|
+--------------------+--------------------+--------------------+--------------------+
|[1, 2, 3, 4, 5, 6 ] | [x1, x2, x3, x4, x5, x6 ]|[y1, y2, y3, y4, y5, y6 ] |[v1, v2, v3, v4, v5, v6 ]

I need to convert each element list into a row so that to further elaborate, from what I have seen around like this post I should use explode function to end up somehow as below:

Country_1   Country_2   Country_3   Country_4
   1            x1         y1          v1
   2            x2         y2          v2
   3            x3         y3          v3
   4            x4         y4          v4
   5            x5         y5          v5
   6            x6         y6          v6

I have tried the below code but haven't made it work so far.

data.withColumn("Country_1Country_2", F.arrays_zip("Country_1","Country_2")).select(*, F.explode("Country_1Country_2").alias("tCountry_1Country_2")).select(*, "tCountry_1Country_2.Country_1", col("Country_1Country_2.Country_2")).show()

David דודו Markovitz · Accepted Answer · 2022-03-11 17:08:27Z

1

# This is not part of the solution, just creation of the data sample
# df = spark.sql("select stack(1, array(1, 2, 3, 4, 5, 6) ,array('x1', 'x2', 'x3', 'x4', 'x5', 'x6') ,array('y1', 'y2', 'y3', 'y4', 'y5', 'y6') ,array('v1', 'v2', 'v3', 'v4', 'v5', 'v6')) as (Country_1, Country_2,Country_3,Country_4)")

df.selectExpr('inline(arrays_zip(*))').show()

+---------+---------+---------+---------+
|Country_1|Country_2|Country_3|Country_4|
+---------+---------+---------+---------+
|        1|       x1|       y1|       v1|
|        2|       x2|       y2|       v2|
|        3|       x3|       y3|       v3|
|        4|       x4|       y4|       v4|
|        5|       x5|       y5|       v5|
|        6|       x6|       y6|       v6|
+---------+---------+---------+---------+

answered Mar 11, 2022 at 17:08

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

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

1 Comment

user2829319 Over a year ago

it was much simpler than I thought! thank you so much @David דודו Markovitz

Collectives™ on Stack Overflow

Dataframe explode list columns in multiple rows

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related