pivot & unpivot
Apache Spark · Aggregations & Joins
Theory
pivot turns the distinct values of one column into separate columns: orders.groupBy('region').pivot('product').sum('amount') gives one row per region and one column per product, with the total in each cell and NULL where a region never sold that product. Passing the list of values, pivot('product', ['Laptop', 'Phone']), is faster because Spark does not need an extra job to find the distinct values, and it fixes the column order.
unpivot (also called melt) does the opposite: it turns several columns back into rows of (variable, value). df.unpivot(ids=['region'], values=['Laptop', 'Phone'], variableColumnName='product', valueColumnName='amount') converts a wide report back into a long table, and in SQL you can use the UNPIVOT clause or stack(). For example, a pivot table of monthly sales per region (Jan, Feb, Mar columns) can be unpivoted to region, month, sales for loading into a fact table.
Why we use it
Business users like wide, spreadsheet-style reports (one column per month or product), while storage and further processing prefer long, tidy tables. pivot and unpivot convert between the two shapes without manual CASE expressions.
When to use it
Use pivot for crosstab reports with a small, known set of values; always pass the values list when you can. Use unpivot to normalise wide source files or reports. Avoid pivoting high-cardinality columns, which creates thousands of columns.
How it works
- Long table: region, product, amount
- groupBy the row key (region)
- pivot on the column whose values become headers (product)
- Aggregate the value into each cell (sum amount)
- Need the long shape back? Yes: unpivot the product columns. No: Report the wide table.
- Wide or long result
Syntax
df.groupBy("region").pivot("product", ["Laptop", "Phone"]).agg(F.sum("amount"))
df.unpivot(ids=["region"], values=["Laptop", "Phone"],
variableColumnName="product", valueColumnName="amount")
-- SQL: ... PIVOT (SUM(amount) FOR product IN ('Laptop', 'Phone'))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"))
wide = (orders.groupBy("region")
.pivot("product", ["Laptop", "Phone", "Tablet"])
.agg(F.sum("amount"))
.orderBy("region"))
wide.show()
long = wide.unpivot(ids=["region"], values=["Laptop", "Phone", "Tablet"],
variableColumnName="product", valueColumnName="amount")
long.filter(F.col("amount").isNotNull()).orderBy("region", "product").show()
# Output:
# +------+-------+-------+-------+
# |region| Laptop| Phone| Tablet|
# +------+-------+-------+-------+
# | East| NULL|15000.0| NULL|
# | North|55000.0| NULL|48000.0|
# | South|52000.0| NULL| NULL|
# | West| NULL|30000.0|25000.0|
# +------+-------+-------+-------+
#
# +------+-------+-------+
# |region|product| amount|
# +------+-------+-------+
# | East| Phone|15000.0|
# | North| Laptop|55000.0|
# | North| Tablet|48000.0|
# | South| Laptop|52000.0|
# | West| Phone|30000.0|
# | West| Tablet|25000.0|
# +------+-------+-------+
Key points
- pivot = groupBy(rows).pivot(column).agg(value); one output column per pivot value
- Pass the values list to skip an extra job and fix the column order
- Missing combinations become NULL; use fillna(0) if you need zeros
- unpivot (melt) turns columns back into rows; SQL has PIVOT and UNPIVOT clauses
- Do not pivot high-cardinality columns