How to find count of Null and Nan values for each column in a PySpark dataframe efficiently?

import numpy as np


data = [
(1, 1, None),
(1, 2, float(5)),
(1, 3, np.nan),
(1, 4, None),
(1, 5, float(10)),
(1, 6, float("nan")),
(1, 6, float("nan")),
]
df = spark.createDataFrame(data, ("session", "timestamp1", "id2"))

Expected output

dataframe with count of nan/null for each column

Note: The previous questions I found in stack overflow only checks for null & not nan. That's why I have created a new question.

I know I can use isnull() function in Spark to find number of Null values in Spark column but how to find Nan values in Spark dataframe?

199920 次浏览
小开
最佳答案

You can use method shown here and replace isNull with isnan:

from pyspark.sql.functions import isnan, when, count, col


df.select([count(when(isnan(c), c)).alias(c) for c in df.columns]).show()
+-------+----------+---+
|session|timestamp1|id2|
+-------+----------+---+
|      0|         0|  3|
+-------+----------+---+

or

df.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in df.columns]).show()
+-------+----------+---+
|session|timestamp1|id2|
+-------+----------+---+
|      0|         0|  5|
+-------+----------+---+
小开

Here is my one liner. Here 'c' is the name of the column

from pyspark.sql.functions import isnan, when, count, col, isNull
    

df.select('c').withColumn('isNull_c',F.col('c').isNull()).where('isNull_c = True').count()
小开

To make sure it does not fail for string, date and timestamp columns:

import pyspark.sql.functions as F
def count_missings(spark_df,sort=True):
"""
Counts number of nulls and nans in each column
"""
df = spark_df.select([F.count(F.when(F.isnan(c) | F.isnull(c), c)).alias(c) for (c,c_type) in spark_df.dtypes if c_type not in ('timestamp', 'string', 'date')]).toPandas()


if len(df) == 0:
print("There are no any missing values!")
return None


if sort:
return df.rename(index={0: 'count'}).T.sort_values("count",ascending=False)


return df

If you want to see the columns sorted based on the number of nans and nulls in descending:

count_missings(spark_df)


# | Col_A | 10 |
# | Col_C | 2  |
# | Col_B | 1  |

If you don't want ordering and see them as a single row:

count_missings(spark_df, False)
# | Col_A | Col_B | Col_C |
# |  10   |   1   |   2   |
小开

For null values in the dataframe of pyspark

Dict_Null = {col:df.filter(df[col].isNull()).count() for col in df.columns}
Dict_Null


# The output in dict where key is column name and value is null values in that column


{'#': 0,
'Name': 0,
'Type 1': 0,
'Type 2': 386,
'Total': 0,
'HP': 0,
'Attack': 0,
'Defense': 0,
'Sp_Atk': 0,
'Sp_Def': 0,
'Speed': 0,
'Generation': 0,
'Legendary': 0}
小开

An alternative to the already provided ways is to simply filter on the column like so

import pyspark.sql.functions as F
df = df.where(F.col('columnNameHere').isNull())

This has the added benefit that you don't have to add another column to do the filtering and it's quick on larger data sets.

小开

I prefer this solution:

df = spark.table(selected_table).filter(condition)


counter = df.count()


df = df.select([(counter - count(c)).alias(c) for c in df.columns])
小开 
from pyspark.sql import DataFrame
import pyspark.sql.functions as fn


# compatiable with fn.isnan. Sourced from
# https://github.com/apache/spark/blob/13fd272cd3/python/pyspark/sql/functions.py#L4818-L4836
NUMERIC_DTYPES = (
'decimal',
'double',
'float',
'int',
'bigint',
'smallilnt',
'tinyint',
)




def count_nulls(df: DataFrame) -> DataFrame:
isnan_compat_cols = {c for (c, t) in df.dtypes if any(t.startswith(num_dtype) for num_dtype in NUMERIC_DTYPES)}


return df.select(
[fn.count(fn.when(fn.isnan(c) | fn.isnull(c), c)).alias(c) for c in isnan_compat_cols]
+ [fn.count(fn.when(fn.isnull(c), c)).alias(c) for c in set(df.columns) - isnan_compat_cols]
)

Builds off of gench and user8183279's answers, but checks via only isnull for columns where isnan is not possible, rather than just ignoring them.

The source code of pyspark.sql.functions seemed to have the only documentation I could really find enumerating these names — if others know of some public docs I'd be delighted.

小开

if you are writing spark sql, then the following will also work to find null value and count subsequently.

spark.sql('select * from table where isNULL(column_value)')

小开

Yet another alternative (improved upon Vamsi Krishna's solutions above):

def check_for_null_or_nan(df):
null_or_nan = lambda x: isnan(x) | isnull(x)
func = lambda x: df.filter(null_or_nan(x)).count()
print(*[f'{i} has {func(i)} nans/nulls' for i in df.columns if func(i)!=0],sep='\n')

check_for_null_or_nan(df)

id2 has 5 nans/nulls

小开

Use the following code to identify the null values in every columns using pyspark.

def check_nulls(dataframe):
'''
Check null values and return the null values in pandas Dataframe
    

INPUT: Spark Dataframe
OUTPUT: Null values
    

'''
# Create pandas dataframe
nulls_check = pd.DataFrame(dataframe.select([count(when(isnull(c), c)).alias(c) for c in dataframe.columns]).collect(),
columns = dataframe.columns).transpose()
nulls_check.columns = ['Null Values']
return nulls_check


#Check null values
null_df = check_nulls(raw_df)
null_df
小开

Here is a readable solution because code is for people as much as computers ;-)

df.selectExpr('sum(int(isnull(<col_name>) or isnan(<col_name>))) as null_or_nan_count'))

提交答案