How to get all rows with null value in any column in pyspark

user_14982219 0
Viewed 1052

I need to find a way to get all rows that have null values in a pyspark dataframe.

For example, I have following dateframe:

   +-------+-------+-------+
   |   c_00|   c_01|   c_02|
   +-------+-------+-------+
 1 |  null |  0.141|  0.141|
 2 |   0.17|   0.17|   0.17|
 3 |   0.25|   null|   0.25|
 4 |  0.135|  0.135|  0.135|

I want all rows with null values in any column

   +-------+-------+-------+
   |   c_00|   c_01|   c_02|
   +-------+-------+-------+
 1 |  null |  0.141|  0.141|
 3 |   0.25|   null|   0.25|
pyspark apache-spark-sql apache-spark dataframe
1 Answers

Filter by chaining multiple OR conditions c_00 is null or c_01 is null OR ...

You can use python functools.reduce to construct the filter expression dynamically from the dataframe columns:

from functools import reduce
from pyspark.sql import functions as F

df = spark.createDataFrame([
    (None, 0.141, 0.141), (0.17, 0.17, 0.17),
    (0.25, None, 0.25), (0.135, 0.135, 0.135)
], ["c_00", "c_01", "c_02"])

cols = [F.col(c) for c in df.columns]
filter_expr = reduce(lambda a, b: a | b.isNull(), cols[1:], cols[0].isNull())

df.filter(filter_expr).show()
#+----+-----+-----+
#|c_00| c_01| c_02|
#+----+-----+-----+
#|null|0.141|0.141|
#|0.25| null| 0.25|
#+----+-----+-----+

Or using array with exists function:

filter_expr = F.exists(F.array(*df.columns), lambda x: x.isNull())
user_13865510
Related