How to Pivot in PySpark: Turn Rows into Columns

Turning rows into columns — a pivot — is one of the most requested operations when preparing data for reports. Doing a pivot in PySpark comes down to combining three methods (groupBy, pivot and agg), and the result is ready to feed a dashboard or a target table. The reverse path is here too, for when you need to go back to long format.

Prerequisites

• A PySpark environment: a notebook in Databricks or Microsoft Fabric, or a local install with pip install pyspark.
• An active SparkSession. Databricks and Fabric already give you one, called spark.
• Basic Python: lists, strings and chained method calls.

Step 1: Create a sample DataFrame

Start with a small DataFrame of sales by month and region. This is the typical "long" format: one row per month and region combination.

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("pivot-demo").getOrCreate()

dados = [
    ("2026-01", "Norte", 1200.0),
    ("2026-01", "Sul", 900.0),
    ("2026-02", "Norte", 1500.0),
    ("2026-02", "Sul", 1100.0),
    ("2026-03", "Norte", 1300.0),
    ("2026-03", "Centro", 700.0),
]

vendas = spark.createDataFrame(dados, ["mes", "regiao", "valor"])
vendas.show()

Step 2: Pivot with groupBy, pivot and agg

A pivot in PySpark always needs three pieces: what stays in the rows (groupBy), the column whose values become columns (pivot), and what fills each cell (agg). The aggregation is not optional: since several rows can land in the same cell, Spark requires you to say how to combine them.

pivot_vendas = (
    vendas
    .groupBy("mes")
    .pivot("regiao")
    .agg(F.sum("valor"))
)

pivot_vendas.show()

The result has one row per month and one column per region: mes, Centro, Norte and Sul.

Step 3: Pass the list of values (faster)

Without a list, Spark has to scan the data just to discover which regions exist — an extra job that gets expensive on a large DataFrame. Passing the list avoids that work and also fixes the column order.

regioes = ["Norte", "Centro", "Sul"]

pivot_rapido = (
    vendas
    .groupBy("mes")
    .pivot("regiao", regioes)
    .agg(F.sum("valor"))
)

pivot_rapido.show()

Careful: if a value is missing from the list, its rows are simply dropped. The list is a filter too.

Step 4: Handle the nulls

Where there was no data (Centro in January, for example), the cell is null. In reports you almost always want a zero instead.

pivot_limpo = pivot_rapido.fillna(0, subset=regioes)
pivot_limpo.show()

Step 5: Several aggregations at once

You can pass more than one aggregation to agg. Spark creates one column per combination of value and aggregation, named in the value_aggregation format.

pivot_multi = (
    vendas
    .groupBy("mes")
    .pivot("regiao", regioes)
    .agg(
        F.sum("valor").alias("total"),
        F.count("valor").alias("n")
    )
)

pivot_multi.printSchema()
# mes, Norte_total, Norte_n, Centro_total, Centro_n, Sul_total, Sul_n

Step 6: The most common error — too many distinct values

If you pivot on a column with many distinct values (a customer ID, for example), Spark returns an error warning that the pivot column has more distinct values than the allowed limit — controlled by the spark.sql.pivotMaxValues setting, which defaults to 10000. It is a useful guardrail: every distinct value becomes a column, and a table with thousands of columns is rarely what you want.

Instead of raising the limit, reduce the values before the pivot: keep the top N and group the rest into "Outros".

top = [r["regiao"] for r in
       vendas.groupBy("regiao")
             .agg(F.sum("valor").alias("t"))
             .orderBy(F.desc("t"))
             .limit(2)
             .collect()]

vendas_top = vendas.withColumn(
    "regiao_grp",
    F.when(F.col("regiao").isin(top), F.col("regiao")).otherwise(F.lit("Outros"))
)

vendas_top.groupBy("mes").pivot("regiao_grp").agg(F.sum("valor")).show()

Step 7: Reverse the pivot (unpivot)

The reverse path — back to long format — is done with stack in a SQL expression, or with the unpivot method from Spark 3.4 onwards.

# Classic: works on any version
longo = pivot_limpo.selectExpr(
    "mes",
    "stack(3, 'Norte', Norte, 'Centro', Centro, 'Sul', Sul) as (regiao, valor)"
)

# Spark 3.4 or later
longo = pivot_limpo.unpivot(
    ids=["mes"],
    values=regioes,
    variableColumnName="regiao",
    valueColumnName="valor"
)

longo.show()

Verify the result

Two quick checks tell you whether the pivot is correct. First, the schema should have exactly one column per region:

pivot_limpo.printSchema()

Second, and more important: the total must not change. The sum of every cell in the result has to match the sum of the original column.

total_original = vendas.select(F.sum("valor").alias("total"))

total_pivot = pivot_limpo.select(
    F.sum(F.col("Norte") + F.col("Centro") + F.col("Sul")).alias("total")
)

total_original.show()
total_pivot.show()   # 6700.0

If the totals do not match, the likely cause is a value missing from the pivot list — remember that this list also filters.

Conclusion

With groupBy, pivot and agg you can turn any long table into the wide format reports ask for, handle the nulls, and go back when you need to. The natural next step is to save the result as a Delta table and connect it to Power BI. One tip: if the pivot is slow, always start by passing the list of values from Step 3 — it is the optimisation with the best return for the least effort. And in your own table, does the pivot really belong in Spark, or would it sit better in the visualisation layer?