Handling Nulls in Spark

Here's a filter that looks correct:

df.filter(col("Rotten_Tomatoes_Rating") == None).count()  # 0

Six of the ten movies in the dataset below have no Rotten Tomatoes rating. Spark returns zero rows, and no error.

That's because null in Spark isn't a value you can compare against. It's a marker that means "unknown", and almost anything you do with an unknown gives you back another unknown.

Null is not zero, not an empty string, and not false. It's "we don't know".

Consider the question "How many books does Krishna own?" The answer may be 0 (we know he owns none) or null (we don't know how many he owns). Treat those two the same and your numbers quietly go wrong.

This is the first post in my Apache Spark series, where I work through Spark from the DataFrame API down to how jobs actually run. It covers how nulls behave in Spark, and the functions for checking, replacing, sorting and dropping them.

Null Is a Marker, Not a Value#

Spark treats nulls the way SQL does. Any operation involving a null generally yields null, unless you handle it explicitly.

Three rules explain most null surprises:

1. Null propagation: If you do an arithmetic operation (e.g., colA + colB) and either colA or colB is null, the result is null unless a function handles the null explicitly (like coalesce).

2. Comparison: Comparing anything with null, even null itself, gives null, and a filter treats null as "no". That's why col("age") == None matches nothing. Use isNull() or isNotNull() instead.

3. Equi-Null safe join: Spark SQL provides nullSafeEq or eqNullSafe (<=> in SQL) to compare two columns including nulls. This means if both sides are null, it returns true.

The Dataset#

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, isnull, when, coalesce

spark = SparkSession.builder \
    .appName("NullHandlingExample") \
    .getOrCreate()

data = [
    (6.1,   1071,  "12-Jun-98",  None,  None,                "The Land Girls"),
    (6.9,   207,   "7-Aug-98",   None,  None,                "First Love, Last Rites"),
    (6.8,   865,   "28-Aug-98",  None,  None,                "I Married a Strange Person"),
    (5.8,   3275,  "1-Jul-86",  "13",  None,                "Pirates"),
    (3.4,   165,   "9-Oct-98",   "62",  "Original Screenplay","Slam"),
    (None,  None,  "15-Jan-99",  None,  None,                "Mississippi Mermaid"),
    (7.7,   15133, "4-Apr-99",   None,  None,                "Following"),
    (3.8,   353,   "9-Apr-99",   None,  "Original Screenplay","Foolish"),
    (5.8,   3275,  "1-Jul-86",   "25",  None,                "Pirates"),
    (7.0,   2906,  "31-Dec-46",  "86",  None,                "Duel in the Sun")
]

columns = ["IMDB_Rating", "IMDB_Votes", "Release_Date", "Rotten_Tomatoes_Rating", "Source", "Title"]
df = spark.createDataFrame(data, columns)

df.show()

Rule 2 is the one behind the filter at the top of this post. Run it on these ten movies, then swap the condition:

coalesce Picks the First Value That Exists#

• In Spark SQL and DataFrame APIs, coalesce() is used to return the first non-null value among its arguments.
• If you have multiple columns and want a single column which picks up a non-null value from one of them in order, or you want to replace a single column’s null value with a fallback.

rating_df = df.select(col("Title"),  
              col("Rotten_Tomatoes_Rating"),  
              col("IMDB_Rating"),  
              coalesce(col("Rotten_Tomatoes_Rating"), col("IMDB_Rating") * 10).alias("Rating"))

rating_df.show(10, False)

Check with isNull, Never == None#

Back to the filter from the start of this post. Here it is next to the one that works:

df.filter(col("Rotten_Tomatoes_Rating") == None).count()    # 0
df.filter(col("Rotten_Tomatoes_Rating").isNull()).count()   # 6

To check for nulls, use:

• isNull()
• isNotNull()
• eqNullSafe(): compares two columns, treating null values as equal.

# Check if Rotten_Tomatoes_Rating is null
check_nulls = df.select(col("Title")
               ,col("Rotten_Tomatoes_Rating"),col("IMDB_Rating")
               ,coalesce(col("Rotten_Tomatoes_Rating")
               , col("IMDB_Rating") * 10).alias("Rating")).where(col("Rating").isNotNull())
check_nulls.show(10, False)

# Check for equi-null safe join

df_eq_null_safe = df.filter(col("IMDB_Rating").eqNullSafe(col("Rotten_Tomatoes_Rating")))
df_eq_null_safe.show()

Sorting Puts Nulls First Unless You Say Otherwise#

When you order a DataFrame in Spark, null handling can change your result. By default, Spark sorts nulls first in ascending order and last in descending order.

1. asc_nulls_first: Ascending order, placing nulls at the top.
2. asc_nulls_last: Ascending order, placing nulls at the bottom.
3. desc_nulls_first: Descending order, placing nulls at the top.
4. desc_nulls_last: Descending order, placing nulls at the bottom

# Move nulls to the end in descending order

move_nulls_to_end = df.orderBy(col("Rotten_Tomatoes_Rating").desc_nulls_last())
move_nulls_to_end.show()

Replace Nulls with a Default#

Spark provides multiple ways to replace null values:

• DataFrame.na.fill(value, subset=None) – Fill null values in specified columns.
• DataFrame.fillna(value, subset=None) – Same as na.fill, a common alias.

# Replace nulls with a value for Source column
replace_nulls = df.na.fill("Un Source",["Source"])
replace_nulls.show(5, False)

# Replacing multiple columns with nulls

fill_values = {
    "IMDB_Rating": 0.0,
    "IMDB_Votes":  0,
    "Source":      "Unknown Source"
}

df_fill_multiple = df.na.fill(fill_values)
df_fill_multiple.show(5, False)

Drop Rows with Missing Values#

Sometimes you might want to drop rows that contain any null or all nulls, or conditionally drop rows with null in specific columns.

Example 1:

# Drop rows with any null value

df_drop_any = df.na.drop("any") # "any" means if any column in the row has null, that row is dropped.
df_drop_any.show(5, False)

Example 2:

# Drop rows with all null values
df_drop_all = df.na.drop("all") # "all" means a row must have all columns as null to be dropped.
df_drop_all.show()

Example 3:

# Drop rows if "IMDB_Rating" or "IMDB_Votes" is null

df_drop_subset = df.na.drop("any", subset=["IMDB_Rating", "IMDB_Votes"])
df_drop_subset.show()

Spark SQL's Other Null Functions#

1. equal_null: Returns true if both expressions are equal (including both being NULL), else false.
2. ifnull: Returns the second expression if the first is NULL, otherwise returns the first. Same as coalesce.
3. nvl: Same as ifnull—substitutes a NULL value with a provided fallback. Same as coalesce.
4. nullif: Returns NULL if both expressions match, otherwise returns the first expression.
5. nvl2: Returns the second expression if the first is not NULL, otherwise returns the third.

special_nulls = df.selectExpr(
    "Title",
    "Rotten_Tomatoes_Rating",
    "IMDB_Rating",
    "equal_null(Rotten_Tomatoes_Rating, IMDB_Rating) as equal_null",
    "ifnull(Rotten_Tomatoes_Rating, IMDB_Rating * 10) as if_null", 
    "nvl(Rotten_Tomatoes_Rating, IMDB_Rating * 10) as nvl",
    "nullif(Rotten_Tomatoes_Rating, IMDB_Rating * 10) as nullif",
    "nvl2(Rotten_Tomatoes_Rating, IMDB_Rating * 10, 0.0)" # if (first != null) second else third
)

special_nulls.show(10, False)

Cheat Sheet#

The next time a filter returns zero rows and no error, check whether it compared something with null.

Next in the series: Under the hood of a Spark job.