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

  1. Long table: region, product, amount
  2. groupBy the row key (region)
  3. pivot on the column whose values become headers (product)
  4. Aggregate the value into each cell (sum amount)
  5. Need the long shape back? Yes: unpivot the product columns. No: Report the wide table.
  6. 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

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