Pyspark: cast array with nested struct to string

Question

I have pyspark dataframe with a column named Filters: "array>"

I want to save my dataframe in csv file, for that i need to cast the array to string type.

I tried to cast it: DF.Filters.tostring() and DF.Filters.cast(StringType()), but both solutions generate error message for each row in the columns Filters:

org.apache.spark.sql.catalyst.expressions.UnsafeArrayData@56234c19

The code is as follows

from pyspark.sql.types import StringType

DF.printSchema()

|-- ClientNum: string (nullable = true)
|-- Filters: array (nullable = true)
    |-- element: struct (containsNull = true)
          |-- Op: string (nullable = true)
          |-- Type: string (nullable = true)
          |-- Val: string (nullable = true)

DF_cast = DF.select ('ClientNum',DF.Filters.cast(StringType())) 

DF_cast.printSchema()

|-- ClientNum: string (nullable = true)
|-- Filters: string (nullable = true)

DF_cast.show()

| ClientNum | Filters 
|  32103    | org.apache.spark.sql.catalyst.expressions.UnsafeArrayData@d9e517ce
|  218056   | org.apache.spark.sql.catalyst.expressions.UnsafeArrayData@3c744494

Sample JSON data:

{"ClientNum":"abc123","Filters":[{"Op":"foo","Type":"bar","Val":"baz"}]}

Thanks !!

Can you print schema and show data before the transformation. Also print schema after the transformation. — Abhishek Bansal
– Abhishek Bansal, Commented Apr 11, 2017 at 13:30
M not able to recreate the issue. Can you show data before the transformation. — Abhishek Bansal
– Abhishek Bansal, Commented Apr 11, 2017 at 13:50

Garren S · Accepted Answer · 2017-10-05 16:05:19Z

9

I created a sample JSON dataset to match that schema:

{"ClientNum":"abc123","Filters":[{"Op":"foo","Type":"bar","Val":"baz"}]}

select(s.col("ClientNum"),s.col("Filters").cast(StringType)).show(false)

+---------+------------------------------------------------------------------+
|ClientNum|Filters                                                           |
+---------+------------------------------------------------------------------+
|abc123   |org.apache.spark.sql.catalyst.expressions.UnsafeArrayData@60fca57e|
+---------+------------------------------------------------------------------+

Your problem is best solved using the explode() function which flattens an array, then the star expand notation:

s.selectExpr("explode(Filters) AS structCol").selectExpr("structCol.*").show()
+---+----+---+
| Op|Type|Val|
+---+----+---+
|foo| bar|baz|
+---+----+---+

To make it a single column string separated by commas:

s.selectExpr("explode(Filters) AS structCol").select(F.expr("concat_ws(',', structCol.*)").alias("single_col")).show()
+-----------+
| single_col|
+-----------+
|foo,bar,baz|
+-----------+

Explode Array reference: Flattening Rows in Spark

Star expand reference for "struct" type: How to flatten a struct in a spark dataframe?

edited Oct 5, 2017 at 16:05

answered Apr 12, 2017 at 18:07

Garren S

5,8223 gold badges34 silver badges45 bronze badges

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

3 Comments

alfredox Over a year ago

This creates columns in the top structure rather than a single column with the contents of all the columns as a string

Garren S Over a year ago

@alfredox Updated to add single column version.

Ceren Over a year ago

How do we join this back to the original ids? @GarrenS

Vzzarr · Accepted Answer · 2021-05-12 09:48:55Z

8

For me in Pyspark the function to_json() did the job.

As a plus compared to the simple casting to String, it keeps the "struct keys" as well (not only the "struct values"). So for the reported example I would have something like:

[{"Op":"foo","Type":"bar","Val":"baz"}]

This was much more useful to me since that I had to write results to a Postgres table. In this format I can easily use supported JSON functions in Postgres

edited May 12, 2021 at 9:48

answered Sep 30, 2019 at 13:51

Vzzarr

5,8924 gold badges61 silver badges103 bronze badges

1 Comment

Uzzi Emuchay Over a year ago

This doesn't seem to work with nested struct type

Abhishek Bansal · Accepted Answer · 2017-04-11 13:26:06Z

-3

You can try this:

DF = DF.withColumn('Filters', DF.Filters.cast("string"))

answered Apr 11, 2017 at 13:26

Abhishek Bansal

1,5831 gold badge9 silver badges8 bronze badges

2 Comments

Omar14 Over a year ago

I tried, same result : org.apache.spark.sql.catalyst.expressions.UnsafeArrayData@3

iurii_n Over a year ago

I'd say you have to run UDF where you can apply some logic to convert array to string and then select new column

Collectives™ on Stack Overflow

Pyspark: cast array with nested struct to string

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related