Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 48 ยท DataFrames and Spark SQL

Grouping and Aggregations

Summarize Spark DataFrames with groupBy and agg, count and sum nulls correctly, build pivot tables, subtotals with rollup and cube, and approximate distinct counts.

Raw rows are rarely the answer. People want totals per country, the average order per customer, the number of distinct visitors per day. In Spark that is the job of aggregations: you split a DataFrame into groups, reduce each group to a few numbers, and get one row per group back.

Aggregations are where Spark's distributed design shows. Each partition first computes partial results for the groups it holds, then partial results are exchanged in a shuffle and merged. Understanding that two-step shape explains why some aggregates are cheap, why some are risky, and why approx_count_distinct exists. This lesson continues from Filtering, Sorting and Deduplicating.

groupBy and agg

groupBy returns a GroupedData object, not a DataFrame. Nothing is computed until you call an aggregation on it, and that call returns a normal DataFrame.

Python
from pyspark.sql import functions as F

orders.groupBy("country").count()

orders.groupBy("country").agg(
    F.count("*").alias("orders"),
    F.sum("amount").alias("revenue"),
    F.avg("amount").alias("avg_amount"),
    F.min("amount").alias("smallest"),
    F.max("amount").alias("largest"),
)

orders.groupBy("country", "channel").agg(F.sum("amount").alias("revenue"))

Always give aggregates an alias; otherwise the column is called something like sum(amount), which is awkward to reference later. Calling agg with no groupBy aggregates the whole DataFrame into a single row: orders.agg(F.sum("amount")).

The shortcut methods groupBy(...).sum("amount"), .avg(), .max() and .min() work too, but agg lets you compute many different aggregates in one pass over the data, which is cheaper than several separate jobs.

flowchart LR
    P1["Partition 1<br/>partial sums per country"] --> S["Shuffle by country"]
    P2["Partition 2<br/>partial sums per country"] --> S
    P3["Partition 3<br/>partial sums per country"] --> S
    S --> M["Merge partials<br/>one row per country"]

Aggregation in two steps: partial aggregation per partition, then a shuffle and a merge.

Because partial results are tiny compared with the rows, the shuffle moves little data. This is the same idea as reduceByKey in Key-Value (Pair) RDDs, done for you by the optimizer.

Nulls inside aggregates

Aggregate functions follow SQL rules, and they matter:

  • count("*") counts rows, including rows full of nulls.
  • count("amount") counts only rows where amount is not null.
  • sum, avg, min and max ignore nulls. An average of 10, 20, null is 15, not 10.
  • If every value in a group is null (or the group is empty for a global aggregate), sum, avg, min and max return null, while count returns 0.
  • countDistinct("x") ignores nulls as well.
  • groupBy itself treats null as a normal group key: all rows with a null key land in one group.

If you want nulls to count as zero in a sum, say so with coalesce. If you want to know how many values are missing, compare count("*") with count("amount").

The stand-in below uses pandas, whose groupby(...).agg(...) accepts the same kind of named results. Note that size() plays the role of count("*") and count() plays the role of count(col).

Python groupBy and agg with null-aware counts

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.

Counting distinct values

countDistinct (SQL: COUNT(DISTINCT x)) gives the exact number of different values per group. It is expensive: Spark must keep or shuffle every distinct value, so memory and network use grow with the number of distinct values, not with the number of groups.

Python
orders.groupBy("country").agg(
    F.countDistinct("customer_id").alias("customers"),
    F.approx_count_distinct("customer_id", rsd=0.02).alias("customers_approx"),
)

approx_count_distinct uses a HyperLogLog++ sketch. Each partition builds a small, fixed-size summary of the values it saw, the summaries are merged, and the estimate is read from the merged sketch. The rsd argument is the maximum relative standard deviation you accept; the default is 0.05 (about 5%). A smaller value gives better accuracy and a larger sketch. For dashboards with billions of events, a 2% error is usually fine and the job is far cheaper. For invoices, use the exact count.

Related collection aggregates are collect_list (all values, with duplicates), collect_set (distinct values) and first/last. Handle them with care: each group's collection must fit in one executor's memory, and the order of the elements is not guaranteed unless you sort before aggregating.

Pivot: rows to columns

pivot turns the distinct values of one column into separate columns, a classic cross-tab.

Python
orders.groupBy("country").pivot("channel").sum("amount")
orders.groupBy("country").pivot("channel", ["web", "app"]).sum("amount")

Pass the list of values explicitly whenever you know them. Without it, Spark must run an extra job first to find the distinct values (and it refuses if there are more than spark.sql.pivotMaxValues, default 10,000). Combinations that do not exist in the data come out as null.

rollup and cube: subtotals

groupBy gives only the finest level. Reports often want subtotals and a grand total as well:

  • rollup("country", "channel") produces the groups (country, channel), (country) and the grand total (), a hierarchy from detailed to coarse.
  • cube("country", "channel") produces every combination: (country, channel), (country), (channel) and ().

In the result, a column that was rolled up shows null. That is ambiguous when the real data also contains nulls, so use grouping("country") (1 when the column was aggregated away, 0 otherwise) or grouping_id(...) to tell them apart.

Python
orders.rollup("country", "channel").agg(
    F.sum("amount").alias("revenue"),
    F.grouping_id().alias("level"),
).orderBy("country", "channel")

The next stand-in builds the same results with pandas, so you can see exactly which rows pivot, rollup and cube produce, and then a small sketch in the spirit of approx_count_distinct.

Python pivot, rollup and cube by hand

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.

Python A tiny distinct-count sketch

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.

Real versus stand-in. Spark computes partial aggregates in parallel on each partition, shuffles and merges them, and its approx_count_distinct uses HyperLogLog++ (a different sketch from the K-minimum-values one above, but with the same fixed-size, mergeable idea). The pandas code runs on one machine and only reproduces the results and their shape: which groups exist, where nulls appear, and what pivot, rollup and cube return.

Choosing between the tools

You want Use
One row per group with several numbers groupBy(...).agg(...)
Exact distinct count countDistinct (costly)
Fast, approximate distinct count approx_count_distinct
Categories as columns pivot (give the value list)
Subtotals along a hierarchy rollup
Subtotals for all combinations cube
A group value on every row (not collapsed) window function, see Window Functions

Common mistakes

  • Forgetting alias, then struggling with names like sum(amount).
  • Running several groupBy jobs when one agg with several functions would do.
  • Expecting count("amount") to count rows (it skips nulls) or avg to treat nulls as zero.
  • Using collect_list on a huge group and running out of executor memory.
  • Pivoting without an explicit value list, or on a high-cardinality column.
  • Reading a null in a rollup result as "unknown" when it means "all values".

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A group has the amounts 10, 20 and null. What do avg('amount') and count('amount') return?

    Aggregates ignore nulls: the average is (10 + 20) / 2 = 15 and count of a column counts only non-null values. count('*') would give 3.
  2. 2. Why is an aggregation usually cheaper than moving all the rows of each group together?

    Partial aggregation per partition shrinks the data before the shuffle, and the partials are merged afterwards.
  3. 3. What does rollup('country', 'channel') add compared with groupBy('country', 'channel')?

    rollup adds the hierarchy (country) and () to the detail level. cube would add (channel) too.
  4. 4. When is approx_count_distinct a good choice?

    It uses a small mergeable sketch with a configurable error (rsd, default 5%), trading a little accuracy for much lower memory and shuffle cost.
  5. 5. Why should you pass a list of values to pivot when you know them?

    An explicit list avoids the extra pass over the data and also fixes the column set and its order.

Summary

  • groupBy plus agg computes several named aggregates in one pass; always use alias.
  • Spark aggregates per partition first, then shuffles and merges small partial results.
  • count("*") counts rows, count(col) counts non-null values, and sum, avg, min, max ignore nulls and return null for all-null groups.
  • countDistinct is exact but costly; approx_count_distinct trades a small error for a fixed-size sketch.
  • pivot turns values into columns (give the list), rollup adds hierarchical subtotals, cube adds all combinations, and grouping_id tells real nulls from rolled-up ones.

Next: get data in and out of Spark in Reading and Writing Data.