Casting string to ArrayType(DoubleType) pyspark dataframe

Question

I have a dataframe in spark with the following schema: schema:

StructType(List(StructField(id,StringType,true),
StructField(daily_id,StringType,true),
StructField(activity,StringType,true)))

Column activity is a String, sample content:

{1.33,0.567,1.897,0,0.78}

I need to cast column Activity to a ArrayType(DoubleType)

In order to get that done i have run the following command:

df = df.withColumn("activity",split(col("activity"),",\s*").cast(ArrayType(DoubleType())))

The new schema of the dataframe changed accordingly:

StructType(List(StructField(id,StringType,true),
StructField(daily_id,StringType,true),
StructField(activity,ArrayType(DoubleType,true),true)))

However, the data now looks like this: [NULL,0.567,1.897,0,NULL]

It changed the first and last element of the array of strings to NULL. I can't figure out why Spark is doing this with the dataframe.

Can please help here on what is the issue?

Many Thanks

Answer 1

Because

Below code is not replacing { & }

df.withColumn("activity",F.split(F.col("activity"),",\s*")).show(truncate=False)
+-------------------------------+
|activity                       |
+-------------------------------+
|[{1.33, 0.567, 1.897, 0, 0.78}]|
+-------------------------------+

When you try to cast these {1.33 & 0.78} string values to DoubleType you will get null as output.

df.withColumn("activity",F.split(F.col("activity"),",\s*").cast(ArrayType(DoubleType()))).show(truncate=False)
+----------------------+
|activity              |
+----------------------+
|[, 0.567, 1.897, 0.0,]|
+----------------------+

Change this

df.withColumn("activity",split(col("activity"),",\s*").cast(ArrayType(DoubleType())))

to

from pyspark.sql import functions as F
from pyspark.sql.types import ArrayType
from pyspark.sql.types import DoubleType

df.select(F.split(F.regexp_replace(F.col("activity"),"[{ }]",""),",").cast("array<double>").alias("activity"))

Answer 2

This is happening because your first and last letter is the bracket itself, hence converting it to null

testdf.withColumn('activity',f.split(f.col('activity').substr(f.lit(2),f.length(f.col('activity'))-2),',').cast(t.ArrayType(t.DoubleType()))).show(2, False)

Answer 3

Try this-

   val df = Seq("{1.33,0.567,1.897,0,0.78}").toDF("activity")
    df.show(false)
    df.printSchema()
    /**
      * +-------------------------+
      * |activity                 |
      * +-------------------------+
      * |{1.33,0.567,1.897,0,0.78}|
      * +-------------------------+
      *
      * root
      * |-- activity: string (nullable = true)
      */
    val processedDF = df.withColumn("activity",
      split(regexp_replace($"activity", "[^0-9.,]", ""), ",").cast("array<double>"))
    processedDF.show(false)
    processedDF.printSchema()

    /**
      * +-------------------------------+
      * |activity                       |
      * +-------------------------------+
      * |[1.33, 0.567, 1.897, 0.0, 0.78]|
      * +-------------------------------+
      *
      * root
      * |-- activity: array (nullable = true)
      * |    |-- element: double (containsNull = true)
      */

Answer 4

A simple approach (without regex) using Spark SQL:

df2=(df1
     .withColumn('col1',expr("""
     transform(
     split(
     substring(activity,2,length(activity)-2),','),
     x->DOUBLE(x))
     """))
    )