Conditional columns

Label order lines with explicit boundaries, branch precedence, and fallbacks.

Step 1 of 6 · Learn

Classify each line

when checks each order line; otherwise supplies the fallback. The example labels line_total of at least 100 as High. line_total includes every unit on that line, not the entire order or a single unit. These labels are rules for this exercise.

Lesson reference: PySpark and Scala

Classify each line

PySpark example

from pyspark.sql.functions import col, when

result = order_items.withColumn(
    "line_band", when(col("line_total") >= 100, "High").otherwise("Standard")
).select("order_item_id", "line_band").orderBy("order_item_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

val result = order_items.withColumn(
    "line_band", when(col("line_total") >= 100, "High").otherwise("Standard")
).select("order_item_id", "line_band").orderBy("order_item_id")
result.show(false)

Order your conditions

Chained when uses the first matching branch. Quantity at least three is Multi-unit; among other lines, totals at least 120 are Review and the rest Standard. Line 6 matches both conditions, so the first branch determines its label.

PySpark example

from pyspark.sql.functions import col, when

result = order_items.withColumn(
    "packing", when(col("quantity") >= 3, "Multi-unit")
    .when(col("line_total") >= 120, "Review")
    .otherwise("Standard")
).select("order_item_id", "packing").orderBy("order_item_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

val result = order_items.withColumn(
    "packing", when(col("quantity") >= 3, "Multi-unit")
    .when(col("line_total") >= 120, "Review")
    .otherwise("Standard")
).select("order_item_id", "packing").orderBy("order_item_id")
result.show(false)

Choose a fallback

Without otherwise, unmatched rows receive null. The example labels quantity one as Single and everything else Other. All quantities here are positive; in a dataset with nulls, a null condition would also take the fallback.

PySpark example

from pyspark.sql.functions import col, when

result = order_items.withColumn(
    "quantity_label", when(col("quantity") == 1, "Single").otherwise("Other")
).select("order_item_id", "quantity_label").orderBy("order_item_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

val result = order_items.withColumn(
    "quantity_label", when(col("quantity") === 1, "Single").otherwise("Other")
).select("order_item_id", "quantity_label").orderBy("order_item_id")
result.show(false)

example.pyPySpark

from pyspark.sql.functions import col, when result = order_items.withColumn( "line_band", when(col("line_total") >= 100, "High").otherwise("Standard") ).select("order_item_id", "line_band").orderBy("order_item_id") result.show()

1. Read order_itemsStart with 9 records; preserve the original DataFrame.

2. Classify each lineApply the displayed expression to the source records.

3. Inspect the resultLine 4 totals 50; line 8 totals 49.99. The inclusive boundary separates them.

Source order_items9 rows

order_item_id	order_id	product_id	quantity	line_total
1	1001	201	1	59.5
2	1001	202	1	30
3	1002	203	2	99
4	1002	206	1	50
5	1003	204	1	35
6	1004	203	3	149.97
7	1004	205	1	70.02
8	1005	203	1	49.99
9	1006	201	2	120

Classify each line

Result9 rows

order_item_id	line_band
1	Standard
2	Standard
3	Standard
4	Standard
5	Standard
6	High
7	Standard
8	Standard
9	High

Line 4 totals 50; line 8 totals 49.99. The inclusive boundary separates them.

The example is loaded in the editor. Run it as written, then try a small change.

Runs on the Spark backend. First startup may take a moment.

Run your code to see the result.