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
- Profile nulls per column
- Is the row useless without this value (e.g. id)? Yes: dropna(subset=[col]). No: Keep the row and fill it.
- Is the fallback a constant? Yes: fillna({col: value}). No: coalesce(col, other_col, lit(default)).
- Re-check null counts and row counts
- 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
- Use isNull()/isNotNull(), never == None: comparisons with null are not true
- sum/avg/count(col) skip nulls; count('*') counts every row
- fillna only fills columns whose type matches the value (a string fill skips int columns)
- coalesce returns the first non-null argument: great for fallbacks
- Null keys never match in a normal join; use eqNullSafe if they should
- Profile null counts before dropna so you know what you are removing