split a array columns into rows pyspark

Question

I have a DataFrame similar to following:

new_df = spark.createDataFrame([
    ([['hello', 'productcode'], ['red','color']], 7),
    ([['hi', 'productcode'], ['blue', 'color']], 8),
    ([['hoi', 'productcode'], ['black','color']], 7)
], ["items", "frequency"])
new_df.show(3, False)

# +------------------------------------------------------------+---------+
# |items                                                       |frequency|
# +------------------------------------------------------------+---------+
# |[WrappedArray(hello, productcode), WrappedArray(red, color)]|7        |
# |[WrappedArray(hi, productcode), WrappedArray(blue, color)]  |8        |
# |[WrappedArray(hoi, productcode), WrappedArray(black, color)]|7        |
# +------------------------------------------------------------+---------+

I need to generate a new DataFrame similar to following:

# +-------------------------------------------
# |productcode     | color         |frequency|
# +-------------------------------------------
# |hello           | red          |       7  |
# |hi              | blue         |       8  |
# |hoi             | black        |       7  |
# +--------------------------------------------

new_df.select(col("items").getItem(0).getItem(0).alias('productcode'),col("items").getItem(1).getItem(0).alias('color'),col("frequency")).show() — Alex
– Alex, Commented Jan 17, 2018 at 11:29

2 revs, 2 users 65% · Accepted Answer · 2018-01-17 12:23:49Z

4

You can convert items to map:

from pyspark.sql.functions import *
from operator import itemgetter

@udf("map<string, string>")
def as_map(vks):
    return {k: v for v, k in vks}

remapped = new_df.select("frequency", as_map("items").alias("items"))

Collect the keys:

keys = remapped.select("items").rdd \
   .flatMap(lambda x: x[0].keys()).distinct().collect()

And select:

remapped.select([col("items")[key] for key in keys] + ["frequency"]) 

+------------+------------------+---------+
|items[color]|items[productcode]|frequency|
+------------+------------------+---------+
|         red|             hello|        7|
|        blue|                hi|        8|
|       black|               hoi|        7|
+------------+------------------+---------+

edited Jan 17, 2018 at 12:23

community wiki

2 revs, 2 users 65%
user9229019

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

1 Comment

user2805885 Over a year ago

Thanks for the reply. But my dataframe has 3 elements and the expected result is different. I don't really need the column name again in row

Collectives™ on Stack Overflow

split a array columns into rows pyspark

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related