unpivot
Turn columns into rows (wide to long) with unpivot or the SQL stack() function.
On this page
Show code in
Every code block on the page follows this.
You will learn
- How unpivot turns columns into rows
- The SQL UNPIVOT clause and the older stack() function
- What happens to nulls and mixed types
- When to unpivot, and when to keep data wide
df.unpivot(ids, values, "name_col", "value_col") (alias melt, Spark 3.4+) turns each listed column into its own row. It is the reverse of pivot: wide to long.What it does
Wide tables are good for reports and bad for analysis: adding a quarter means adding a column, and "total per quarter" needs a sum per column. Unpivoting gives one row per (region, quarter), which every aggregation, filter and join handles naturally.
Step by step
wide
| region | Q1 | Q2 | Q3 |
|---|---|---|---|
| North | 150 | 150 | null |
| South | 80 | 60 | 120 |
long
| region | quarter | amount |
|---|---|---|
| North | Q1 | 150 |
| North | Q2 | 150 |
| North | Q3 | null |
| South | Q1 | 80 |
| South | Q2 | 60 |
| South | Q3 | 120 |
Each region row becomes one row per value column. The DataFrame unpivot keeps the null for North Q3.
Run the example
result = quarterly.unpivot( ids=["region"], values=["Q1", "Q2", "Q3"], variableColumnName="quarter", valueColumnName="amount", )
SELECT region, quarter, amount FROM quarterly UNPIVOT INCLUDE NULLS ( amount FOR quarter IN (Q1, Q2, Q3) )
Switch to PySpark to edit and run this example in your browser.
Nulls
The SQL UNPIVOT clause drops rows whose value is null by default (EXCLUDE NULLS); add INCLUDE NULLS to keep them, as above. The DataFrame unpivot keeps them; filter afterwards if you do not want them. Decide deliberately: a missing quarter and a zero quarter are different facts.
Before 3.4: stack()
Older Spark versions use the SQL generator stack(n, name1, value1, name2, value2, ...), which emits n rows per input row:
quarterly.select("region", F.expr( "stack(3, 'Q1', Q1, 'Q2', Q2, 'Q3', Q3) AS (quarter, amount)"))
SELECT region, stack(3, 'Q1', Q1, 'Q2', Q2, 'Q3', Q3) AS (quarter, amount) FROM quarterly
stack keeps nulls. You will still see it in many codebases and interview answers.
Types
All value columns end up in one column, so they must share a type. If Q1 is an integer and Q2 a double, Spark widens to double. If one is a string, the DataFrame unpivot raises an error asking for a common type: cast them first.
Under the hood
unpivot is implemented with the same Expand operator as rollup: each input row is copied once per value column. It is narrow (no shuffle) but multiplies the row count by the number of value columns.
Common mistakes
Losing nulls with SQL UNPIVOT
Mixing types
Unpivoting hundreds of columns of a huge table
Key takeaways
- unpivot (melt) turns value columns into rows: wide to long.
- SQL UNPIVOT excludes nulls by default; the DataFrame method keeps them.
- stack() is the pre-3.4 way and keeps nulls.
- Value columns must share a type.
Check yourself
3 questions1. A row has 4 value columns. How many rows does unpivot produce from it?
Show the answer
4. One output row per value column.
2. Without INCLUDE NULLS, what does SQL UNPIVOT do with a null value?
Show the answer
Drops that row. EXCLUDE NULLS is the default.
3. Which function unpivots in Spark versions before 3.4?
Show the answer
stack. stack(n, name, value, ...) emits n rows per input row.
Practice it
Interview problems that use unpivot: write the PySpark, run it, and get graded on hidden tests.
Go deeper
Primary sources: DataFrame.unpivot · UNPIVOT clause