Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 48 ยท DataFrames and Spark SQL

Pivot, Unpivot and Union

Reshape Spark DataFrames: pivot rows into columns, unpivot wide tables back, and stack tables with union or unionByName and allowMissingColumns.

Data rarely arrives in the shape your report needs. Sales come as one row per sale, but the manager wants one row per product and one column per month. A survey export has one column per question, but your model wants one row per answer. Two tables with the same meaning but different column order must be stacked into one. These are reshaping tasks, and Spark covers them with pivot, unpivot and the union family.

This lesson uses small tables so you can see each result. It assumes you know groupBy and agg from Grouping and Aggregations. Spark cannot run on this site, so the PySpark snippets are read-only; the TryIts are plain-Python stand-ins that produce the same rows.

Long and wide tables

A long table has one row per observation: (product, month, revenue). A wide table has one row per entity and one column per category: (product, jan, feb, mar). Wide tables read well in a report, long tables are better for filtering, grouping and storing. Pivot turns long into wide; unpivot turns wide into long.

Python
sales = spark.createDataFrame([
    ("pen",    "jan", 10), ("pen",    "feb", 14), ("pen",    "mar", 9),
    ("book",   "jan", 5),  ("book",   "mar", 7),
    ("bag",    "feb", 3),  ("bag",    "feb", 2),
], ["product", "month", "units"])

pivot: rows become columns

pivot follows a groupBy. The grouped columns stay as rows, the distinct values of the pivot column become new columns, and an aggregation fills the cells:

Python
from pyspark.sql import functions as F

wide = sales.groupBy("product").pivot("month").agg(F.sum("units"))
wide.show()
# +-------+----+----+----+
# |product| feb| jan| mar|
# +-------+----+----+----+
# |    bag|   5|null|null|
# |   book|null|   5|   7|
# |    pen|  14|  10|   9|
# +-------+----+----+----+

Notice three things. First, the aggregation is mandatory: two bag rows share feb, so they must be combined (here summed to 5). Second, combinations that never occurred are null, not 0; use fillna(0) if you want zeros. Third, the new columns come out in sorted order (feb, jan, mar), not calendar order.

Always pass the values explicitly when you know them:

Python
months = ["jan", "feb", "mar"]
wide = sales.groupBy("product").pivot("month", months).agg(F.sum("units")).fillna(0)

Without a list, Spark must first run an extra job to find the distinct values, and it fails if there are more than spark.sql.pivotMaxValues (10,000 by default). With a list the columns are fixed and in the order you wrote, and the extra job disappears. Pivoting a column with thousands of values is also a sign that you want a long table instead.

You can aggregate several measures at once: .agg(F.sum("units"), F.max("units")) creates columns such as jan_sum(units); give each an alias to keep names readable.

unpivot: columns become rows

The reverse operation is called unpivot or "melt". Spark 3.4 added DataFrame.unpivot (alias melt):

Python
long = wide.unpivot(
    ids=["product"],                    # columns to keep as they are
    values=["jan", "feb", "mar"],       # columns to turn into rows
    variableColumnName="month",
    valueColumnName="units",
)

On older versions, or in SQL, use the stack generator inside selectExpr:

Python
wide.selectExpr(
    "product",
    "stack(3, 'jan', jan, 'feb', feb, 'mar', mar) as (month, units)"
)

stack(n, label1, value1, ...) produces n rows per input row. Null cells become rows with a null value; filter them out with where units is not null if you do not want them. All value columns must share a compatible type.

Python pivot and unpivot on plain lists

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Stacking tables with union

union appends the rows of one DataFrame to another, like UNION ALL in SQL (duplicates are kept). It matches columns by position, not by name:

Python
a = spark.createDataFrame([(1, "Ann")], ["id", "name"])
b = spark.createDataFrame([("Bo", 2)], ["name", "id"])     # same columns, different order

a.union(b).show()
# +---+----+
# | id|name|
# +---+----+
# |  1| Ann|
# | Bo|   2|      <- names landed in the id column
# +---+----+

That is a silent bug whenever the column order differs. unionByName matches by column name instead and is almost always what you want:

Python
a.unionByName(b).show()             # correct: id=2 goes with name=Bo

If one table has a column the other lacks, unionByName raises an error by default. Add allowMissingColumns=True (Spark 3.1+) and the missing column is filled with null:

Python
c = spark.createDataFrame([(3, "Cy", "ES")], ["id", "name", "country"])
a.unionByName(c, allowMissingColumns=True).show()
# +---+----+-------+
# | id|name|country|
# +---+----+-------+
# |  1| Ann|   null|
# |  3|  Cy|     ES|
# +---+----+-------+

Remember the SQL parallel: Spark's union does not remove duplicates. Follow it with .distinct() if you need UNION semantics. To stack many tables, fold them with functools.reduce(DataFrame.unionByName, frames). Unioning many hundreds of small DataFrames in a loop builds a huge query plan; reading a whole folder in one call (see Reading and Writing Data) is faster.

Python union by position versus by name

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

union never checks types or names. Two columns that line up by position but mean different things are combined without any error, and Spark may even widen types silently (an int and a string become a string). Prefer unionByName and check the schema of the result.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In groupBy('product').pivot('month').agg(F.sum('units')), what fills a cell for a product that has no row in a given month?

    Combinations that never occurred stay null. Use fillna(0) afterwards if you want zeros.
  2. 2. Why pass an explicit list of values to pivot()?

    Without a list Spark first runs a job to collect the distinct values. An explicit list skips that job and keeps your order.
  3. 3. Two DataFrames have the same columns in different order. What does union() do?

    union is positional, like SQL UNION ALL. unionByName matches by name.
  4. 4. What does allowMissingColumns=True do in unionByName?

    Columns present in only one DataFrame are kept, with null values for the rows from the other.

Summary

  • groupBy(...).pivot(col, values).agg(...) turns long data into a wide table; list the values yourself for speed and order, and expect nulls for missing combinations.
  • unpivot (Spark 3.4+, alias melt) or stack in selectExpr turns wide columns back into rows.
  • union is positional and keeps duplicates; unionByName matches by name and is the safe default.
  • allowMissingColumns=True lets tables with different column sets be stacked, with nulls for the gaps.
  • Follow union with distinct() if you need SQL UNION behavior.

Next: see more ways to look at your data in Sampling and Getting Rows Back.