when/otherwise & expressions

Apache Spark · DataFrames

Theory

F.when(condition, value) is Spark's version of SQL CASE WHEN. You chain as many .when() calls as you need and finish with .otherwise(default); Spark checks the conditions top to bottom and returns the value of the first one that is true. Without otherwise, rows that match nothing get NULL. The result is a column expression, so you use it inside withColumn, select, filter, groupBy or orderBy.

The same logic can be written as a SQL string with F.expr("CASE WHEN amount >= 50000 THEN 'High' ... END"). Combine it with other helpers: F.coalesce picks the first non-null value, isin checks a list, and F.greatest/F.least compare columns. For example, banding orders by amount gives 55000 as High, 30000 as Medium and 1500 as Low, and you can then group by the band to count orders in each.

Why we use it

Business rules such as customer tiers, discount slabs, status flags and data clean-up are conditional logic. when/otherwise expresses them as optimized column expressions instead of slow Python UDFs.

When to use it

Use when/otherwise for if/else rules on columns, mapping codes to labels, or building flags for conditional aggregation (sum(when(...))). Order conditions from most specific to least. For long lookup lists, a join with a small mapping table is easier to maintain.

How it works

  1. For each row
  2. amount >= 50000? Yes: band = High. No: Check the next condition.
  3. amount >= 20000? Yes: band = Medium. No: Go to otherwise.
  4. otherwise: band = Low
  5. Column added to the output

Syntax

F.when(cond1, v1).when(cond2, v2).otherwise(default)
F.expr("CASE WHEN cond THEN v ELSE d END")
F.sum(F.when(F.col("region") == "North", F.col("amount")).otherwise(0))
F.coalesce(c1, c2, F.lit(0))

Example

from pyspark.sql import SparkSession, functions as F

spark = (SparkSession.builder.master("local[1]")
         .config("spark.ui.enabled", "false")
         .getOrCreate())

orders = spark.createDataFrame([
    (101, 1, "North", "Laptop", 1, 55000.0, "2026-01-05"),
    (102, 2, "West",  "Phone",  2, 30000.0, "2026-01-06"),
    (103, 1, "North", "Mouse",  3,  1500.0, "2026-01-06"),
    (104, 3, "South", "Laptop", 1, 52000.0, "2026-01-07"),
    (105, 4, "West",  "Tablet", 1, 25000.0, "2026-01-08"),
    (106, 2, "East",  "Phone",  1, 15000.0, "2026-01-08"),
    (107, 5, "North", "Tablet", 2, 48000.0, "2026-01-09"),
    (108, 3, "South", "Mouse",  4,  2000.0, "2026-01-10"),
], "order_id INT, customer_id INT, region STRING, product STRING, "
   "qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))

banded = orders.withColumn(
    "band",
    F.when(F.col("amount") >= 50000, "High")
     .when(F.col("amount") >= 20000, "Medium")
     .otherwise("Low"))
banded.select("order_id", "amount", "band").show()

# Conditional aggregation with when()
orders.agg(
    F.sum(F.when(F.col("region") == "North", F.col("amount")).otherwise(0)).alias("north_sales"),
    F.count(F.when(F.col("qty") > 1, True)).alias("multi_qty_orders")).show()

# Same rule as a SQL CASE expression
orders.select("order_id", F.expr(
    "CASE WHEN product IN ('Laptop', 'Tablet') THEN 'Computing' ELSE 'Other' END"
).alias("category")).limit(3).show()

# Output:
# +--------+-------+------+
# |order_id| amount|  band|
# +--------+-------+------+
# |     101|55000.0|  High|
# |     102|30000.0|Medium|
# |     103| 1500.0|   Low|
# |     104|52000.0|  High|
# |     105|25000.0|Medium|
# |     106|15000.0|   Low|
# |     107|48000.0|Medium|
# |     108| 2000.0|   Low|
# +--------+-------+------+
#
# +-----------+----------------+
# |north_sales|multi_qty_orders|
# +-----------+----------------+
# |   104500.0|               4|
# +-----------+----------------+
#
# +--------+---------+
# |order_id| category|
# +--------+---------+
# |     101|Computing|
# |     102|    Other|
# |     103|    Other|
# +--------+---------+

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