pivot
Turn row values into columns, and why passing the values list up front makes it faster.
On this page
Show code in
Every code block on the page follows this.
You will learn
- How pivot turns row values into columns
- Why passing the list of values makes pivot faster and safer
- How to pivot with several aggregates
- How to pivot in SQL, and by hand with when
groupBy(rows).pivot(col, values).agg(...) creates one output column per distinct value of col. Without the values list, Spark first runs an extra job to find them.What it does
Long data has one row per (region, quarter). A pivot makes it wide: one row per region, one column per quarter, each cell an aggregate.
Step by step
sales (long)
| region | qtr | amount |
|---|---|---|
| North | Q1 | 100 |
| North | Q2 | 150 |
| North | Q1 | 50 |
| South | Q1 | 80 |
| South | Q3 | 120 |
pivoted (wide)
| region | Q1 | Q2 | Q3 |
|---|---|---|---|
| North | 150 | 150 | null |
| South | 80 | null | 120 |
North's two Q1 rows are summed. Combinations with no rows become null, not 0.
Run the example
from pyspark.sql import functions as F result = (sales .groupBy("region") .pivot("qtr", ["Q1", "Q2", "Q3", "Q4"]) .agg(F.sum("amount")) .orderBy("region"))
SELECT * FROM sales PIVOT ( SUM(amount) FOR qtr IN ('Q1', 'Q2', 'Q3', 'Q4') ) ORDER BY region
Switch to PySpark to edit and run this example in your browser.
Q4 has no data and still appears, full of nulls, because we listed it. Listing values gives you a fixed, predictable schema.
Always pass the values
pivot("qtr")
spark.sql.pivotMaxValues).pivot("qtr", ["Q1", ...])
Several aggregates
With more than one aggregate, each output column is named value_alias: .agg(F.sum("amount").alias("revenue"), F.count("*").alias("n")) gives Q1_revenue, Q1_n, and so on.
Pivot by hand
A pivot is a set of conditional aggregates, one per value. Writing it out is sometimes clearer, and lets each column use a different rule:
from pyspark.sql import functions as F result = sales.groupBy("region").agg( F.sum(F.when(F.col("qtr") == "Q1", F.col("amount"))).alias("Q1"), F.sum(F.when(F.col("qtr") == "Q2", F.col("amount"))).alias("Q2"), )
SELECT region, SUM(CASE WHEN qtr = 'Q1' THEN amount END) AS Q1, SUM(CASE WHEN qtr = 'Q2' THEN amount END) AS Q2 FROM sales GROUP BY region
Switch to PySpark to edit and run this example in your browser.
Under the hood
Spark rewrites a pivot into an aggregation, so it costs one shuffle like any groupBy. The SQL PIVOT clause groups implicitly by every column not mentioned in the PIVOT, so select only the columns you want before pivoting, or extra columns silently become grouping keys.
Common mistakes
Not passing the values list
Expecting zeros
Extra columns in a SQL PIVOT
Key takeaways
- pivot makes one column per value of the pivot column.
- Pass the values list: faster and a stable schema.
- Empty cells are null.
- A pivot is just conditional aggregates; you can write them by hand.
Check yourself
3 questions1. What does pivot without a values list cost extra?
Show the answer
A job to find the distinct values. Spark must know the output columns, so it first computes the distinct values of the pivot column.
2. A region has no Q3 sales. What is its Q3 cell after sum-pivoting?
Show the answer
null. There are no rows to sum, so the aggregate is null.
3. Which is equivalent to pivoting quarter with sum(amount)?
Show the answer
sum(when(quarter = value, amount)) for each value, grouped by the row key. Each pivot column is a conditional aggregate over the group.
Practice it
Interview problems that use pivot: write the PySpark, run it, and get graded on hidden tests.
Go deeper
Primary sources: GroupedData.pivot · PIVOT clause