Handling Nulls: fillna, dropna & coalesce

Apache Spark · Cleaning & Complex Types

Theory

Real data has missing values, which Spark stores as null. Nulls behave specially: any comparison with null (amount > 100, city == null) is neither true nor false, so filter drops those rows, and most arithmetic with null gives null. Aggregates such as sum, avg and count(col) skip nulls, while count("*") counts every row. To test for null use F.col("x").isNull() or isNotNull(), never == None.

PySpark gives three main tools. df.fillna(value or dict) (same as df.na.fill) replaces nulls with defaults, for example {"city": "Unknown", "qty": 0}. df.dropna(how="any"|"all", thresh=n, subset=[...]) removes rows with missing values. F.coalesce(a, b, c) returns the first non-null value per row, which is ideal for fallbacks like coalesce(phone, alt_phone, lit("NA")). For joins on columns that may be null, use eqNullSafe so null matches null.

Why we use it

Nulls silently change results: filters drop rows, averages ignore them, joins fail to match and string concatenation turns null. Handling them explicitly keeps counts and totals correct and prevents downstream failures in reports and ML features.

When to use it

Use fillna for simple per-column defaults, coalesce when the fallback comes from another column or needs logic, and dropna only when a row is useless without a value (for example a missing order_id). Profile null counts first so you do not drop more data than you expect.

How it works

  1. Profile nulls per column
  2. Is the row useless without this value (e.g. id)? Yes: dropna(subset=[col]). No: Keep the row and fill it.
  3. Is the fallback a constant? Yes: fillna({col: value}). No: coalesce(col, other_col, lit(default)).
  4. Re-check null counts and row counts
  5. Clean DataFrame for joins and aggregates

Syntax

df.fillna({"city": "Unknown", "qty": 0})
df.dropna(how="any", subset=["order_id", "amount"])
df.dropna(thresh=2)                 # keep rows with >= 2 non-nulls
F.coalesce(F.col("a"), F.col("b"), F.lit(0))
F.col("x").isNull(); a.eqNullSafe(b)

Example

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("techtown-spark").getOrCreate()

orders = spark.createDataFrame([
    (1, 101, "North", "Laptop", 1,    55000.0),
    (2, 102, None,    "Phone",  None, 30000.0),
    (3, None, "North", "Mouse", 3,    None),
    (4, 103, "South", None,     1,    52000.0),
    (None, None, None, None,    None, None),
], "order_id INT, customer_id INT, region STRING, product STRING, qty INT, amount DOUBLE")

# 1. Profile: how many nulls per column?
orders.select([F.sum(F.col(c).isNull().cast("int")).alias(c) for c in orders.columns]).show()

# 2. Drop rows that are useless without an id
clean = orders.dropna(subset=["order_id"])

# 3. Defaults per column, and a fallback built from another column
clean = (clean
    .fillna({"region": "Unknown", "qty": 1})
    .withColumn("product", F.coalesce("product", F.lit("Not captured")))
    .withColumn("amount", F.coalesce("amount", F.lit(0.0))))
clean.show()

print("count(*) =", orders.count(), "| count(amount) =", orders.select(F.count("amount")).first()[0])

# Output:
# +--------+-----------+------+-------+---+------+
# |order_id|customer_id|region|product|qty|amount|
# +--------+-----------+------+-------+---+------+
# |       1|          2|     2|      2|  2|     2|
# +--------+-----------+------+-------+---+------+
#
# +--------+-----------+-------+------------+---+-------+
# |order_id|customer_id| region|     product|qty| amount|
# +--------+-----------+-------+------------+---+-------+
# |       1|        101|  North|      Laptop|  1|55000.0|
# |       2|        102|Unknown|       Phone|  1|30000.0|
# |       3|       NULL|  North|       Mouse|  3|    0.0|
# |       4|        103|  South|Not captured|  1|52000.0|
# +--------+-----------+-------+------------+---+-------+
#
# count(*) = 5 | count(amount) = 3

Key points

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic