pyspark - unable to convert null value to 0

Question

I'm working with databricks and I don't understand why I'm not able to convert null value to 0 in what it seems like a regular integer column.

I've tried these two options:

@udf(IntegerType())
def null_to_zero(x):
  """
  Helper function to transform Null values to zeros
  """
  return 0 if x == 'null' else x

and later:

.withColumn("col_test", null_to_zero(col("col")))

and everything is returned as null.

and the second option simply doesn't have any impact .na.fill(value=0,subset=["col"])

What do I'm missing here? Is this a specific behavior of null values with databricks?

question from:https://stackoverflow.com/questions/66055870/unable-to-convert-null-value-to-0

Answer 1

The nulls are represented as None, not as a string null. For your case it's better to use coalesce function instead, like this (example based on docs):

from pyspark.sql.functions import coalesce, lit
cDf = spark.createDataFrame([(None, None), (1, None), (None, 2)], ("a", "b"))
cDf.withColumn("col_test", coalesce(cDf["a"], lit(0.0))).show()

will give you desired behavior:

+----+----+--------+
|   a|   b|col_test|
+----+----+--------+
|null|null|     0.0|
|   1|null|     1.0|
|null|   2|     0.0|
+----+----+--------+

If you need more complex logic, then you can use when/otherwise, with condition on null:

cDf.withColumn("col_test", when(cDf["a"].isNull(), lit(0.0)).otherwise(cDf["a"])).show()