Convert null values to empty array in Spark DataFrame

by

You can use an UDF:

import org.apache.spark.sql.functions.udf

val array_ = udf(() => Array.empty[Int])

combined with WHEN or COALESCE:

df.withColumn("myCol", when(myCol.isNull, array_()).otherwise(myCol))
df.withColumn("myCol", coalesce(myCol, array_())).show

In the recent versions you can use array function:

import org.apache.spark.sql.functions.{array, lit}

df.withColumn("myCol", when(myCol.isNull, array().cast("array<integer>")).otherwise(myCol))
df.withColumn("myCol", coalesce(myCol, array().cast("array<integer>"))).show

Please note that it will work only if conversion from string to the desired type is allowed.

The same thing can be of course done in PySpark as well. For the legacy solutions you can define udf

from pyspark.sql.functions import udf
from pyspark.sql.types import ArrayType, IntegerType

def empty_array(t):
    return udf(lambda: [], ArrayType(t()))()

coalesce(myCol, empty_array(IntegerType()))

and in the recent versions just use array:

from pyspark.sql.functions import array

coalesce(myCol, array().cast("array<integer>"))

More Related Contents:

  1. Spark SQL: apply aggregate functions to a list of columns
  2. Multiple Aggregate operations on the same column of a spark dataframe
  3. DataFrame join optimization – Broadcast Hash Join
  4. How to access element of a VectorUDT column in a Spark DataFrame?
  5. What is the maximum size for a broadcast object in Spark?
  6. How to loop through each row of dataFrame in pyspark
  7. Why does Spark think this is a cross / Cartesian join
  8. How do I add an persistent column of row ids to Spark DataFrame?
  9. How to exclude multiple columns in Spark dataframe in Python
  10. Why is predicate pushdown not used in typed Dataset API (vs untyped DataFrame API)?
  11. spark.ml StringIndexer throws ‘Unseen label’ on fit()
  12. Querying Spark SQL DataFrame with complex types
  13. Find maximum row per group in Spark DataFrame
  14. Spark Dataframe distinguish columns with duplicated name
  15. How to export a table dataframe in PySpark to csv?
  16. Defining a UDF that accepts an Array of objects in a Spark DataFrame?
  17. How to save/insert each DStream into a permanent table
  18. How to get other columns when using Spark DataFrame groupby?
  19. Spark load data and add filename as dataframe column
  20. PySpark: how to resample frequencies
  21. How to find count of Null and Nan values for each column in a PySpark dataframe efficiently?
  22. Derive multiple columns from a single column in a Spark DataFrame
  23. spark dataframe drop duplicates and keep first
  24. pyspark: count distinct over a window
  25. How to get keys and values from MapType column in SparkSQL DataFrame
  26. reduce result datasets into single dataset
  27. Stratified sampling with pyspark
  28. How to turn off scientific notation in pyspark?
  29. What is an optimized way of joining large tables in Spark SQL
  30. Difference between === null and isNull in Spark DataDrame

Leave a Comment