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

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} 

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 | 

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.

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() 

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]) 

here's a method that avoids any pitfalls with isnan or isNull and works with any datatype

# spark is a pyspark.sql.SparkSession object def count_nulls(df: ): cache = df.cache() row_count = cache.count() return spark.createDataFrame( [[row_count - cache.select(col_name).na.drop().count() for col_name in cache.columns]], # schema=[(col_name, 'integer') for col_name in cache.columns] schema=cache.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'))