How to filter null values in pyspark dataframe?

Question

Suppose we have a simple dataframe:

from pyspark.sql.types import *

schema = StructType([
StructField('id', LongType(), False),
StructField('name', StringType(), False),
StructField('count', LongType(), True),
])
df = spark.createDataFrame([(1,'Alice',None), (2,'Bob',1)], schema)

The question is how to detect null values? I tried the following:

df.where(df.count == None).show()
df.where(df.count is 'null').show()
df.where(df.count == 'null').show()

It results in error:

condition should be string or Column

I know the following works:

df.where("count is null").show()

But is there a way to achieve with without the full string? I.e. df.count...?

Anahcolus · Accepted Answer · 2017-12-29 05:47:52Z

9

Another way of doing the same is by using filter api

from pyspark.sql import functions as F
df.filter(F.isnull("count")).show()

answered Dec 29, 2017 at 5:47

Anahcolus

42.1k6 gold badges75 silver badges101 bronze badges

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

2 Comments

Miroslav Stola Over a year ago

Is there any significant difference between where and filter? I mean generally, not only in this case.

Anahcolus Over a year ago

@MiroslavStola,where is an alias for filter. filter is standard for functional programming whereas where is for those who prefer SQL ways.

Steven · Accepted Answer · 2020-04-03 08:27:24Z

8

You can use Spark Function isnull

from pyspark.sql import functions as F
df.where(F.isnull(F.col("count"))).show()

or directly with the method isNull

df.where(F.col("count").isNull()).show()

edited Apr 3, 2020 at 8:27

answered Dec 28, 2017 at 13:57

Steven

15.4k7 gold badges49 silver badges80 bronze badges

2 Comments

labyrinth Over a year ago

And for those unfamiliar with pyspark syntax likeme, .isNotNull() gives you everywhere that is not null.

Wassadamo Over a year ago

~F.col("count").isNull() also works to provide the negation

Collectives™ on Stack Overflow

How to filter null values in pyspark dataframe?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related