How to sum two columns containing null values in a dataframe in Spark/PySpark?

user_1759084 0
Viewed 2064

I have a dataframe in following format -

Col1    |cnt_Test1     |cnt_Test2
_______________________________________
Stud1   | null        | 2
Stud2   | 3           | 4
Stud3   | 1           | null

I want to create a new column by aggregating cnt_Test1 and cnt_Test2 to get the following result -

Col1    |cnt_Test1     |cnt_Test2     | new_Count
____________________________________________________
Stud1   | null        | 2              | 2
Stud2   | 3           | 4              | 7
Stud3   | 1           | null           | 1

However, I am getting the following output - where sum of a null and long integer is null

Col1    |cnt_Test1     |cnt_Test2     | new_Count
____________________________________________________
Stud1   | null        | 2              | null
Stud2   | 3           | 4              | 7
Stud3   | 1           | null           | null
pyspark
2 Answers

You need to use coalesce function like below

df = spark.createDataFrame(
[
("Stud1",None,2),
("Stud1",3,4),
("Stud1",1, None)], 
("col1","cnt_Test1", "cnt_Test2"))


# Import functions
import pyspark.sql.functions as f

df1 = df.withColumn("new_count", f.coalesce(f.col('cnt_Test1'), f.lit(0)) + f.coalesce(f.col('cnt_Test2'), f.lit(0)))
user_62372410

You can also do it as a two step:

df2 = df.na.fill(0)
df2.withColumn("new_Count", df2["cnt_Test1"] + df2["cnt_Test2"]).show()
user_97140340
Related