coalesce
Take the first non-null value from several columns, and why it is not the same as fillna.
On this page
Show code in
Every code block on the page follows this.
You will learn
- How coalesce picks the first non-null value
- The difference between coalesce, fillna and nvl
- How to give a default to a missing join match
- Why F.coalesce and DataFrame.coalesce are unrelated
F.coalesce(a, b, c) returns the first argument that is not null, row by row. Use it for fallbacks and defaults. Not to be confused with df.coalesce(n), which reduces the number of partitions.What it does
It evaluates its arguments left to right and returns the first non-null one. If all are null, the result is null. Arguments must have compatible types.
Step by step
Input
| id | work_email | home_email |
|---|---|---|
| 1 | a@work.com | a@home.com |
| 2 | null | b@home.com |
| 3 | null | null |
Output
| id | best_email |
|---|---|
| 1 | a@work.com |
| 2 | b@home.com |
| 3 | none |
A literal as the last argument guarantees a non-null result.
Run the example
from pyspark.sql import functions as F result = contacts.select( "id", F.coalesce("work_email", "home_email", F.lit("none")).alias("best_email"), F.coalesce("work_email", "home_email", "phone").alias("any_contact"), )
SELECT id, COALESCE(work_email, home_email, 'none') AS best_email, COALESCE(work_email, home_email, phone) AS any_contact FROM contacts
Switch to PySpark to edit and run this example in your browser.
Note F.lit("none"): in PySpark a plain string argument means a column name, so a literal must be wrapped in lit.
coalesce and its relatives
| Function | Does | Notes |
|---|---|---|
F.coalesce(a, b, ...) | First non-null of any number of expressions | The general tool. |
nvl(a, b) / ifnull(a, b) | coalesce with exactly two arguments | SQL functions, also F.ifnull since 3.5. |
df.fillna(value, subset) | Replace nulls in whole columns with a constant | Values only, no fallback to another column. Type must match the column. |
nullif(a, b) | Null when a equals b, else a | The reverse: turn sentinel values like 0 or "" into null. |
Defaults after a left join
A left join leaves nulls where the right side had no match. coalesce turns them into a business default, for example zero orders:
customers.join(order_counts, "customer_id", "left") \ .withColumn("orders", F.coalesce("orders", F.lit(0)))
SELECT c.*, COALESCE(o.orders, 0) AS orders FROM customers c LEFT JOIN order_counts o USING (customer_id)
F.coalesce vs df.coalesce
F.coalesce(cols...)
df.coalesce(n)
They share a name and nothing else. The Small file problem lesson covers df.coalesce(n).
Common mistakes
Passing a string literal without lit
F.coalesce("email", "unknown") looks for a column called unknown and fails.Mixing types
Replacing null with 0 by reflex
Key takeaways
F.coalescereturns the first non-null argument per row.- Wrap literals in
F.lit. - Use it to give unmatched left-join rows a default, but only when the default is true.
df.coalesce(n)is a different thing: it reduces partitions.
Check yourself
3 questions1. What does coalesce(null, '', 'x') return?
Show the answer
An empty string. An empty string is not null, so it is the first non-null value. Use nullif to treat blanks as missing.
2. Why does F.coalesce("a", "n/a") fail in PySpark?
Show the answer
"n/a" is treated as a column name. Plain strings are column names in the DataFrame API; use F.lit("n/a").
3. What does df.coalesce(1) do?
Show the answer
Reduces the DataFrame to one partition. The DataFrame method reduces the partition count. It is unrelated to F.coalesce.
Practice it
Interview problems that use coalesce: write the PySpark, run it, and get graded on hidden tests.
Go deeper
when / otherwisePySpark functions
eqNullSafe (<=>)Spark internals
The small file problem
Primary sources: functions.coalesce · DataFrame.fillna