Pivot tables

Compare products across line quantities using explicit pivot columns.

Step 1 of 6 · Learn

Choose pivot columns

groupBy defines one output row per product. pivot turns quantity values into columns; the explicit list fixes column order and avoids a separate discovery of values. This example counts lines containing one or two units. These are line counts, not units sold. Zero fills absent combinations for this report.

Lesson reference: PySpark and Scala

Choose pivot columns

PySpark example

from pyspark.sql.functions import col, count, sum, coalesce, lit, concat

result = order_items.groupBy("product_id").pivot(
    "quantity", [1, 2]
).agg(count("*")).na.fill(0).orderBy("product_id")
result.show()

Scala example

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

val result = order_items.groupBy("product_id").pivot(
  "quantity", Seq(1, 2)
).agg(count("*")).na.fill(0).orderBy("product_id")
result.show()

Change the metric

The layout and metric are separate choices. Keep the quantity columns, but switch from counting lines to summing line_total. The total already covers all units on a line; multiplying by quantity again would overstate the value. Zero here represents an absent combination, not a general rule for missing measurements.

PySpark example

from pyspark.sql.functions import col, count, sum, coalesce, lit, concat

result = order_items.groupBy("product_id").pivot(
    "quantity", [1, 2, 3]
).agg(count("*")).na.fill(0).orderBy("product_id")
result.show()

Scala example

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

val result = order_items.groupBy("product_id").pivot(
  "quantity", Seq(1, 2, 3)
).agg(count("*")).na.fill(0).orderBy("product_id")
result.show()

Filter before pivoting

A source filter changes contributions and can remove whole product rows. The example includes every line. Filtering line_total before groupBy asks a different question from filtering one of the finished quantity columns.

PySpark example

from pyspark.sql.functions import col, count, sum, coalesce, lit, concat

result = order_items.groupBy("product_id").pivot(
    "quantity", [1, 2, 3]
).agg(sum("line_total")).na.fill(0).orderBy("product_id")
result.show()

Scala example

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

val result = order_items.groupBy("product_id").pivot(
  "quantity", Seq(1, 2, 3)
).agg(sum("line_total")).na.fill(0).orderBy("product_id")
result.show()

example.pyPySpark

from pyspark.sql.functions import col, count, sum, coalesce, lit, concat result = order_items.groupBy("product_id").pivot( "quantity", [1, 2] ).agg(count("*")).na.fill(0).orderBy("product_id") result.show()

1. Read order linesNine lines reference six catalog products.

2. Choose pivot columnsGroup by product and pivot the explicit quantity values.

3. Inspect the outputCheck the output keys and metric before comparing totals.

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

Choose pivot columns

Result6 rows

product_id	1	2
201	1	1
202	1	0
203	1	1
204	1	0
205	1	0
206	1	0

Compare the source columns with the transformed result.

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.