Efficient way to find columns that contain ANY null values

Question

I want to return a list of all columns that contain at least 1 null value. All of the other similar questions I have seen on StackOverflow are filtering the column where the value is null, but this is definitely sub-optimal since it has to find ALL the null values, but I just want to find ONE null value.

I could filter the column where the value is null, and then if the count of this result is greater than 1, then I know the column contains a null value. However, as I said this is suboptimal as it first finds all null values.

Is there any way to do this?

Furthermore, is there any way to do this without looping over all the columns?

Seems hard. Maybe transposing your data and summing over? Could this be of use: stackoverflow.com/questions/44627386/… — pinegulf
– pinegulf, Commented Apr 26, 2021 at 10:33

werner · Accepted Answer · 2021-05-09 21:01:12Z

2

Spark's SQL function any can check if any value of a column meets a condition.

from pyspark.sql import functions as F

data = [[1,2,3],[None, 5, 6], [7, None, 9]]
df = spark.createDataFrame(data, schema=["col1", "col2", "col3"])

cols = [f"any({col} is null) as {col}_contains_null" for col in df.columns]
df.selectExpr(cols).show()

Output:

+------------------+------------------+------------------+
|col1_contains_null|col2_contains_null|col3_contains_null|
+------------------+------------------+------------------+
|              true|              true|             false|
+------------------+------------------+------------------+

answered May 9, 2021 at 21:01

werner

15k6 gold badges36 silver badges56 bronze badges

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

Comments

Loochie · Accepted Answer · 2022-02-02 22:25:58Z

2

If you are simply looking for the column names that contain any null value you may try this -

cols_with_nulls = [x for x in df.columns if df.filter(F.col(x).isNull()).count() > 0]

answered Feb 2, 2022 at 22:25

Loochie

2,47215 silver badges20 bronze badges

Comments

Piotrek kertoiP · Accepted Answer · 2025-03-31 11:24:32Z

Here's some code in Scala to get list of NAMES of columns which are never null:

case class NotNullCols(colNames: Seq[String])

def getNeverNullColNames(dataFrame: DataFrame)(implicit spark: SparkSession): Seq[String] = {
  import spark.implicits._
  val colsNullabilities = dataFrame.columns
    .map((columnName: String) => {
      struct(
        lit(columnName) as "col_name",
        expr(s"any($columnName is null)") as "can_be_null"
      )
    })
  val colsNullabilityArray = array(colsNullabilities: _*)
  val neverNullColNamesSparkArray = transform(
    filter(colsNullabilityArray, el => !el("can_be_null")),
    el => el("col_name")
  )
  dataFrame
    .select(neverNullColNamesSparkArray as "colNames")
    .as[NotNullCols]
    .collect
    .head
    .colNames
}

Collectives™ on Stack Overflow

Efficient way to find columns that contain ANY null values

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related