Pivot Table Overview

A pivot table is a tool used for summarizing data, allowing you to group and aggregate information based on categorical columns. In the context of PySpark, a pivot table transforms unique values from one column into multiple columns, aggregating values using functions like sum, count, average, etc. This is useful for reshaping data into a more digestible format, especially for reporting or analytics purposes.

1. What a Pivot Does

Pivoting converts a long table, where each row is one observation such as (employee, region, sales), into a wide table where one categorical column's values become column headers. The diagram uses the exact sample data from the code below. The grouping column (employee) defines the output rows, the pivot column (region) defines the output columns, and the aggregation (sum(sales)) fills each cell. Combinations that have no input rows, such as Alice in West, become null. Wide tables are easier to read in reports and are the input format many charting tools and machine learning feature tables expect.

2. PySpark Application Using Pivot Table

The following PySpark code demonstrates how to create a pivot table with the provided sample data:

from pyspark.sql import SparkSession
from pyspark.sql.functions import sum

spark = SparkSession.builder \
    .appName("Pivot Table Example") \
    .getOrCreate()

# Sample data as a list of dictionaries
data = [
    {"employee": "Alice", "region": "North", "sales": 100},
    {"employee": "Bob",   "region": "North", "sales": 200},
    {"employee": "Alice", "region": "South", "sales": 300},
    {"employee": "Bob",   "region": "South", "sales": 400},
    {"employee": "Alice", "region": "East",  "sales": 150},
    {"employee": "Bob",   "region": "West",  "sales": 250}
]

df = spark.createDataFrame(data)

df.show()

# Pivot the table to show sales by employee per region
pivot_df = df.groupBy("employee").pivot("region").agg(sum("sales"))

# Show the pivoted DataFrame
pivot_df.show()
spark.stop()

3. Explanation

groupBy("employee") groups the data by employee.
pivot("region") creates new columns for each unique value in the "region" column (e.g., "North", "South", "East", "West").
agg(sum("sales")) aggregates the sales data by summing the values for each combination of employee and region.

4. Output

The output would look something like this:

Table 1 - Original DataFrame

+--------+-------+-----+
|employee| region|sales|
+--------+-------+-----+
|   Alice|  North|  100|
|     Bob|  North|  200|
|   Alice|  South|  300|
|     Bob|  South|  400|
|   Alice|   East|  150|
|     Bob|   West|  250|
+--------+-------+-----+

Table 2 - Pivoted DataFrame

+--------+-----+-----+------+-----+
|employee| East|North| South| West|
+--------+-----+-----+------+-----+
|   Alice|  150|  100|   300| null|
|     Bob| null|  200|   400|  250|
+--------+-----+-----+------+-----+

5. How Spark Executes It

Without an explicit list of values, Spark must first run a separate job to collect the distinct values of the pivot column, because the output schema has to be known before the query is planned. It then performs a normal grouped aggregation: partial aggregation on each partition, a shuffle by the grouping key, and a final aggregation that produces one conditional aggregate per pivot value. By default Spark limits the number of discovered pivot values to 10,000 (spark.sql.pivotMaxValues) to protect against accidentally creating huge schemas.

6. Worked Examples

from pyspark.sql import functions as F

# 1. Explicit values: no discovery job, fixed column order, missing regions still appear
regions = ["North", "South", "East", "West"]
pivot_df = df.groupBy("employee").pivot("region", regions).agg(F.sum("sales"))

# 2. Replace nulls with 0 for reporting
pivot_df = pivot_df.na.fill(0, subset=regions)

# 3. Several aggregations at once: columns become North_total, North_n, ...
multi = (df.groupBy("employee")
           .pivot("region", regions)
           .agg(F.sum("sales").alias("total"), F.count("*").alias("n")))

# 4. Unpivot (wide back to long), Spark 3.4+
long_df = pivot_df.unpivot("employee", regions, "region", "sales")

# 4b. Unpivot on older versions with stack()
long_df = pivot_df.selectExpr(
    "employee",
    "stack(4, 'North', North, 'South', South, 'East', East, 'West', West) as (region, sales)")

The same result in Spark SQL uses the PIVOT clause:

SELECT * FROM sales
PIVOT (SUM(sales) FOR region IN ('North', 'South', 'East', 'West'));

Every non-pivot, non-aggregated column in the source table becomes an implicit grouping column in SQL PIVOT, so select only the columns you need in a subquery first.

7. When to Use It

Need	Approach
Report with a small, known set of categories	pivot with explicit values
Feature table for ML (one column per category)	pivot, or one-hot encoding for many categories
Thousands of categories	Keep the long format; wide tables with thousands of columns are slow to plan and store
Need to filter or join by category later	Keep long format, pivot only at the presentation layer

8. Pitfalls

High-cardinality pivot columns create very wide schemas and large shuffles.
Category values that are not valid column names (spaces, dots) need backticks when referenced later.
Nulls in the pivot column produce a column named null; filter or fill them first.
Use first() or max() rather than sum() when pivoting non-numeric attributes.
Importing sum from pyspark.sql.functions, as the example does, shadows Python's built-in sum; prefer import pyspark.sql.functions as F.

9. Pivot vs Alternatives

A pivot is one of several ways to get per-category numbers side by side. Conditional aggregation does the same thing explicitly and works in every SQL engine, including ones without a PIVOT clause:

wide = df.groupBy("employee").agg(
    F.sum(F.when(F.col("region") == "North", F.col("sales"))).alias("North"),
    F.sum(F.when(F.col("region") == "South", F.col("sales"))).alias("South"))

This is in fact what Spark generates internally for a pivot with explicit values, so performance is the same; choose the form that reads better. Conditional aggregation is more flexible when each output column needs a different filter or aggregate, for example a revenue total for one category and a distinct customer count for another. A cube or rollup (df.cube("employee", "region")) is the right tool when you also need subtotals and grand totals, which a pivot does not produce. Finally, for display only, it is often cheaper to aggregate in Spark to a small long table, convert it with toPandas(), and pivot in pandas, where reshaping a few thousand rows is instant and the full set of pandas formatting options is available.

Keep in mind that the shape of the output depends on the data unless you pass explicit values: a new region appearing tomorrow silently adds a column, which can break downstream schemas, Delta table writes with schema enforcement, or dashboards that expect fixed columns.