How to split a list of objects to separate columns in pyspark dataframe

Question

I have a column in dataframe as list of objects(Array of structs) like

column: [{key1:value1}, {key2:value2}, {key3:value3}]

I want to split this column into separate columns with key name as column name and value as column value in same row.
Final result like

key1:value1, key2:value2, key3:value3

How to achieve this in pyspark ?

E.g.

Sample data to create dataframe:

my_new_schema = StructType([
    StructField('id', LongType()),
    StructField('countries', ArrayType(StructType([
        StructField('name', StringType()),
        StructField('capital', StringType())
    ])))
])
l = [(1, [
        {'name': 'Italy', 'capital': 'Rome'},
        {'name': 'Spain', 'capital': 'Madrid'}
    ])
]
    
dz = spark.createDataFrame(l, schema=my_new_schema)
# we have array of structs:
dz.show(truncate=False)
+---+--------------------------------+
|id |countries                       |
+---+--------------------------------+
|1  |[{Italy, Rome}, {Spain, Madrid}]|
+---+--------------------------------+

Expected output:

+---+--------+---------+
|id |Italy   |  Spain  |
+---+------------------+
|1  |Rome    | Madrid  |
+---+--------+---------+

blackbishop · Accepted Answer · 2021-12-11 15:00:46Z

1

inline the countries array then pivot the country name column:

import pyspark.sql.functions as F

dz1 = dz.selectExpr(
    "id", 
    "inline(countries)"
).groupBy("id").pivot("name").agg(
    F.first("capital")
)

dz1.show()
#+---+-----+------+
#|id |Italy|Spain |
#+---+-----+------+
#|1  |Rome |Madrid|
#+---+-----+------+

answered Dec 11, 2021 at 15:00

blackbishop

32.8k11 gold badges61 silver badges86 bronze badges

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

2 Comments

Manoj kumar Over a year ago

is it possible to do pivot on condition.. let's say if the struct has another property continent:Europe, i want to pivot the country name and capital as earlier but only if the continent value is Europe.. is this possible to do

blackbishop Over a year ago

@Manojkumar simply add a filter before grouping: dz1 = dz.selectExpr("id", "inline(countries)").filter("continent = 'Europe'")...

Collectives™ on Stack Overflow

How to split a list of objects to separate columns in pyspark dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related