Handling Null Values

Overview

Learn how to locate, fill, and drop missing or null values in a Spark DataFrame to prevent pipeline failures.

What You Will Learn

Detailed Concept Explanation

Missing data (represented as null in Spark) is common in raw data. Spark provides a dedicated .na sub-API on DataFrames to handle missing values:

• df.na.drop(): Drops rows containing nulls. You can specify whether to drop if any column is null or all columns are null.
• df.na.fill(value): Replaces null values with a default value.
• coalesce(*cols): Evaluates columns in order and returns the first non-null value.

Code Examples

Input Dataset Preview

Below is the user registrations dataset containing missing values:

Python (PySpark) Implementation

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("HandlingNulls").getOrCreate()
data = [("Alice", 25), ("Bob", None), (None, None)]
df = spark.createDataFrame(data, ["user", "age"])

# Fill null age with 0 and drop rows where user is null
cleaned_df = df.na.fill({"age": 0}).na.drop(subset=["user"])
cleaned_df.show()

Expected Output

+----+---+
|user|age|
+----+---+
|Alice| 25|
| Bob|  0|
+----+---+

Execution Plan Diagram (Python & Scala)

Scala Implementation

import org.apache.spark.sql.SparkSession

val spark = SparkSession.builder().appName("NullsScala").getOrCreate()
import spark.implicits._

val df = Seq(
  ("Alice", Some(25)),
  ("Bob", None),
  (null.asInstanceOf[String], None)
).toDF("user", "age")

val cleaned = df.na.fill(Map("age" -> 0)).na.drop(Array("user"))
cleaned.show()

Expected Output

+----+---+
|user|age|
+----+---+
|Alice| 25|
| Bob|  0|
+----+---+

SQL Implementation

SELECT 
    COALESCE(user, 'Anonymous') AS user, 
    COALESCE(age, 0) AS age 
FROM registrations
WHERE user IS NOT NULL;

Expected Output

Executing this SQL query returns:

Common Mistakes

• Type Mismatch in Fill: Trying to fill an integer column with a string default (e.g. df.na.fill("N/A")). Spark will ignore the replacement unless the column matches the string type. Use a map to fill columns with type-matching defaults.

Best Practices

• Specify Subset: When dropping nulls, always specify a subset of key columns (e.g. .na.drop(subset=["id"])) instead of dropping the entire row if any column is null.

Interview Perspective

Interactive Challenges

Related Topics

• Working with Columns
• Filtering Data
• Selecting Columns