Pyspark - Json Column - Concat Key and Value as a String

Question

I have a dataframe with 2 string columns, and another one with an array strucuture:

-- music: string (nullable = true)
|-- artist: string (nullable = true)
|-- details: array (nullable = false)
|    |-- element: struct (containsNull = true)
|    |    |-- Genre: string (nullable = true)
|    |    |-- Origin: string (nullable = true)

Just to help you, this is a sample data:

music | artist | details
Music_1 | Artist_1 | [{"Genre": "Rock", "Origin": "USA"}]
Music_2 | Artist_3 | [{"Genre": "", "Origin": "USA"}]
Music_3 | Artist_1 | [{"Genre": "Rock", "Origin": "UK"}]

I am trying a simple operation, I guess, just concat the Key and Value by '-'. Basically, what I am trying to do is to get the following strucuture:

music | artist | details
Music_1 | Artist_1 | Genre - Rock, Origin - USA
Music_2 | Artist_3 | Genre -     , Origin - USA
Music_3 | Artist_1 | Genre - Rock, Origin - UK

For that I already tried an approach that was sparate first the key and value in different columns to then I can concat the items:

display(df.select(col("music"), col("artist"), posexplode("details").alias("key","value")))

But I got the following result:

music | artist | key | value
Music_1 | Artist_1 | 0 | [{"Genre": "Rock", "Origin": "USA"}]
Music_2 | Artist_3 | 0 | [{"Genre": "", "Origin": "USA"}]
Music_3 | Artist_1 | 0 | [{"Genre": "Rock", "Origin": "UK"}]

Probably is not the best solution, anyone can help me?

Thanks!

Mohana B C · Accepted Answer · 2021-08-30 18:26:25Z

2

You can use built-in higher order function transform() to get desired result (From spark 2.4).

df = # Input data
df.withColumn('details', expr("transform(details, c-> concat_ws(', ', concat_ws(' - ', 'Genre', c['Genre']), 
    concat_ws(' - ', 'Origin', c['Origin'])))")) \
  .withColumn('details', explode_outer('details')) \
  .show(truncate=False)

+--------+--------------------------+-------+
|artist  |details                   |music  |
+--------+--------------------------+-------+
|Artist_1|Genre - Rock, Origin - USA|Music_1|
|Artist_3|Genre - , Origin - USA    |Music_2|
|Artist_1|Genre - Rock, Origin - UK |Music_3|
+--------+--------------------------+-------+

answered Aug 30, 2021 at 18:26

Mohana B C

5,4721 gold badge13 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

Pyspark - Json Column - Concat Key and Value as a String

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related