Browse cheat sheets · PySpark

Technology: PySparkCategory: Data TransformationDifficulty: IntermediateUpdated

GroupBy and Aggregations

Aggregate PySpark DataFrames safely by defining output grain, handling null grouping keys, choosing correct aggregate functions, validating totals, and controlling shuffle-heavy groupBy workloads.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Group by one column
  5. 2. Group by multiple columns
  6. 3. Count rows with count("*")
  7. 4. Count non-null values in one column
  8. 5. Count distinct business keys
  9. 6. Sum numeric values
  10. 7. Average values
  11. 8. Calculate several aggregates in one pass
  12. 9. Get minimum and maximum timestamps
  13. 10. Preserve null grouping keys
  14. 11. Filter invalid grouping keys before aggregation
  15. 12. Measure null grouping keys separately
  16. 13. Group by a date column
  17. 14. Derive a day from a timestamp
  18. 15. Aggregate by month
  19. 16. Conditional aggregation
  20. 17. Conditional sum
  21. 18. Compute a ratio from aggregate components
  22. 19. Calculate a weighted average
  23. 20. Aggregate booleans or flags
  24. 21. Collect values into an array
  25. 22. Use collect_list() only when duplicates matter
  26. 23. Do not assume collect_list() preserves order
  27. 24. Aggregate into a struct
  28. 25. Use first() and last() carefully
  29. 26. Get the latest value using max_by where available
  30. 27. Get the row associated with the latest timestamp
  31. 28. Use rollup() for hierarchical totals
  32. 29. Use cube() for all grouping combinations
  33. 30. Use grouping_id() with rollups/cubes
  34. 31. Use pivot() only with bounded categories
  35. 32. Avoid pivoting customer IDs or transaction IDs
  36. 33. Reconcile aggregate totals with the source
  37. 34. Validate output uniqueness at the intended grain
  38. 35. Check input duplication before summing
  39. 36. Filter and project before aggregation
  40. 37. Inspect the physical plan
  41. 38. Check grouping-key skew
  42. 39. Do not replace every null key with one sentinel before grouping
  43. 40. Avoid Python UDF aggregation when built-ins are enough
  44. What the result means
  45. What to look for
  46. Revenue is too high
  47. Row count drops more than expected
  48. Distinct count differs sharply from row count
  49. One stage takes much longer than expected
  50. collect_list() causes memory pressure
  51. Average looks wrong
  52. Production notes
  53. GroupBy is usually a shuffle-heavy operation
  54. Built-in aggregates can use partial aggregation
  55. Grouping-key cardinality affects output size
  56. Skew matters more than average group size
  57. Decimal types are preferable for exact financial totals
  58. Distinct aggregation can be expensive
  59. Do not use aggregation to hide bad grain
  60. Common mistakes
  61. 1. Grouping before defining the output grain
  62. 2. Using count(col) when row count is required
  63. 3. Using a simple average when a weighted average is required
  64. 4. Assuming first() means earliest
  65. 5. Using max() across several columns to reconstruct the latest row
  66. 6. Pivoting on high-cardinality fields
  67. 7. Using collect_list() on unbounded groups
  68. 8. Ignoring null grouping keys
  69. 9. Blaming groupBy performance before checking skew
  70. 10. Running many separate aggregations over the same input
  71. Next step
  72. Related deep dives

Problem

You need to summarize a PySpark DataFrame by customer, merchant, date, region, or another business grain.

The syntax is straightforward, but production errors often come from:

  • aggregating at the wrong grain
  • double counting after a join
  • losing null grouping keys
  • confusing row count with non-null count
  • averaging values that should be weighted
  • using collect_list() on unbounded groups
  • performing expensive custom aggregation in Python
  • creating extreme shuffle or skew
  • grouping on high-cardinality keys without understanding the cost

The goal is to make aggregation logic explicit, correct, and scalable.

Quick answer

Define the output grain first.

Example:

Output grain:
one row per merchant_id + sale_date

Then aggregate:

from pyspark.sql import functions as F

summary = (
    sales
    .groupBy(
        "merchant_id",
        "sale_date",
    )
    .agg(
        F.count("*").alias("row_count"),
        F.countDistinct("sale_id").alias("sale_count"),
        F.sum("amount").alias("revenue"),
        F.avg("amount").alias("avg_sale_amount"),
    )
)

Before trusting the result, validate:

Did row count change as expected?
Do totals reconcile?
Are duplicate source rows inflating metrics?
Are null grouping keys being kept or dropped intentionally?

Aggregation correctness comes before Spark tuning.

Code / commands

1. Group by one column

summary = (
    df
    .groupBy("merchant_id")
    .count()
)

This returns one row per distinct merchant_id.

Use count() when you need row count per group.


2. Group by multiple columns

summary = (
    df
    .groupBy(
        "merchant_id",
        "sale_date",
    )
    .agg(
        F.sum("amount").alias("revenue")
    )
)

The grouping columns define the output grain.

Document them.


3. Count rows with count("*")

summary = (
    df
    .groupBy("merchant_id")
    .agg(
        F.count("*").alias("row_count")
    )
)

This counts rows regardless of whether individual columns contain nulls.

Use this when the question is:

How many records are in each group?

4. Count non-null values in one column

summary = (
    df
    .groupBy("merchant_id")
    .agg(
        F.count("sale_id").alias("non_null_sale_id_count")
    )
)

This counts only non-null sale_id values.

Do not confuse this with row count.


5. Count distinct business keys

summary = (
    df
    .groupBy("merchant_id")
    .agg(
        F.countDistinct("sale_id").alias("distinct_sales")
    )
)

Use this when source rows may contain multiple records per sale.

This can help distinguish:

physical row count

from:

business event count

6. Sum numeric values

summary = (
    df
    .groupBy("merchant_id")
    .agg(
        F.sum("amount").alias("revenue")
    )
)

Null values are ignored by sum().

If all values in a group are null, the sum result can be null.

Do not automatically replace null with zero unless that matches the business rule.


7. Average values

summary = (
    df
    .groupBy("merchant_id")
    .agg(
        F.avg("amount").alias("avg_amount")
    )
)

Null values are ignored.

Ask whether a simple average is the correct business metric.


8. Calculate several aggregates in one pass

summary = (
    df
    .groupBy("merchant_id")
    .agg(
        F.count("*").alias("row_count"),
        F.countDistinct("sale_id").alias("sale_count"),
        F.sum("amount").alias("revenue"),
        F.avg("amount").alias("avg_amount"),
        F.min("amount").alias("min_amount"),
        F.max("amount").alias("max_amount"),
    )
)

This is usually better than running several separate groupBy jobs over the same input.


9. Get minimum and maximum timestamps

summary = (
    df
    .groupBy("merchant_id")
    .agg(
        F.min("event_time").alias("first_event_time"),
        F.max("event_time").alias("last_event_time"),
    )
)

This gives the earliest and latest timestamp values.

It does not return the entire row associated with those timestamps.

Use a window when you need the actual latest row.


10. Preserve null grouping keys

Spark groups null values together.

Example:

summary = (
    df
    .groupBy("country")
    .count()
)

Rows where country is null form their own group.

That differs from some pandas defaults.

If null grouping keys are invalid, filter or quarantine them explicitly.


11. Filter invalid grouping keys before aggregation

valid = df.filter(
    F.col("merchant_id").isNotNull()
)

summary = (
    valid
    .groupBy("merchant_id")
    .agg(
        F.sum("amount").alias("revenue")
    )
)

Use this only when null keys are truly invalid.

Do not drop them silently without measuring impact.


12. Measure null grouping keys separately

null_key_count = (
    df
    .filter(
        F.col("merchant_id").isNull()
    )
    .count()
)

print(
    "Rows with null merchant_id:",
    null_key_count,
)

For large production workloads, combine this with other metrics in one aggregation where possible.


13. Group by a date column

daily = (
    df
    .groupBy("sale_date")
    .agg(
        F.sum("amount").alias("revenue")
    )
)

Make sure sale_date is actually a date and not arbitrary text.


14. Derive a day from a timestamp

daily = (
    df
    .withColumn(
        "sale_date",
        F.to_date("sale_time"),
    )
    .groupBy("sale_date")
    .agg(
        F.sum("amount").alias("revenue")
    )
)

Be careful with timezone semantics.

If sale_time is UTC but reporting is local-time based, convert to the business timezone before deriving the date.


15. Aggregate by month

monthly = (
    df
    .withColumn(
        "sale_month",
        F.date_trunc(
            "month",
            F.col("sale_time"),
        ),
    )
    .groupBy("sale_month")
    .agg(
        F.sum("amount").alias("revenue")
    )
)

This keeps a timestamp-like month boundary.

Use a dedicated date key if your model requires date-only semantics.


16. Conditional aggregation

Count successful rows:

summary = (
    df
    .groupBy("merchant_id")
    .agg(
        F.sum(
            F.when(
                F.col("status") == "success",
                1,
            ).otherwise(0)
        ).alias("success_count")
    )
)

This is often clearer and more efficient than creating many filtered DataFrames and aggregating each separately.


17. Conditional sum

summary = (
    df
    .groupBy("merchant_id")
    .agg(
        F.sum(
            F.when(
                F.col("status") == "approved",
                F.col("amount"),
            ).otherwise(F.lit(0))
        ).alias("approved_amount")
    )
)

Use zero only if excluded rows should contribute zero to the metric.

If null semantics matter, encode them deliberately.


18. Compute a ratio from aggregate components

summary = (
    df
    .groupBy("merchant_id")
    .agg(
        F.count("*").alias("total_rows"),
        F.sum(
            F.when(
                F.col("status") == "success",
                1,
            ).otherwise(0)
        ).alias("success_rows"),
    )
    .withColumn(
        "success_rate",
        F.when(
            F.col("total_rows") > 0,
            F.col("success_rows")
            / F.col("total_rows"),
        ),
    )
)

Build the ratio from explicit numerator and denominator.

Do not average row-level percentages unless that is mathematically correct.


19. Calculate a weighted average

Suppose:

price × quantity

should be weighted by quantity.

summary = (
    df
    .groupBy("product_id")
    .agg(
        F.sum(
            F.col("price")
            * F.col("quantity")
        ).alias("weighted_value"),
        F.sum("quantity").alias("total_quantity"),
    )
    .withColumn(
        "weighted_avg_price",
        F.when(
            F.col("total_quantity") != 0,
            F.col("weighted_value")
            / F.col("total_quantity"),
        ),
    )
)

Do not use avg(price) when each row represents different weight.


20. Aggregate booleans or flags

Example:

summary = (
    df
    .groupBy("customer_id")
    .agg(
        F.max(
            F.col("is_high_risk").cast("int")
        ).alias("has_high_risk")
    )
)

If any row is true, result becomes 1.

Make the output type/business meaning explicit afterward if needed.


21. Collect values into an array

summary = (
    df
    .groupBy("merchant_id")
    .agg(
        F.collect_set("country").alias("countries")
    )
)

Use collect_set() when unique values per group are useful.

Be careful with unbounded groups.

One large group can create a very large in-memory array.


22. Use collect_list() only when duplicates matter

summary = (
    df
    .groupBy("customer_id")
    .agg(
        F.collect_list("event_type").alias("events")
    )
)

This preserves duplicates but does not guarantee a meaningful event order unless you explicitly model ordering.

Do not use collect_list() to build huge arrays for high-volume groups.


23. Do not assume collect_list() preserves order

If order matters, use a structured pattern.

Example:

events = (
    df
    .groupBy("customer_id")
    .agg(
        F.sort_array(
            F.collect_list(
                F.struct(
                    "event_time",
                    "event_type",
                )
            )
        ).alias("events")
    )
)

This sorts the collected structs by their fields.

Be explicit about ascending/descending semantics required by the business rule.


24. Aggregate into a struct

summary = (
    df
    .groupBy("merchant_id")
    .agg(
        F.struct(
            F.sum("amount").alias("revenue"),
            F.countDistinct("sale_id").alias("sales"),
        ).alias("metrics")
    )
)

Use nested outputs only when the downstream consumer benefits from a struct.

Flat analytical tables are often easier to query.


25. Use first() and last() carefully

summary = (
    df
    .groupBy("customer_id")
    .agg(
        F.first(
            "status",
            ignorenulls=True,
        ).alias("some_status")
    )
)

Without an explicit ordering strategy, first() does not mean:

earliest by timestamp

and last() does not mean:

latest by timestamp

Use a window or an ordered aggregate pattern when temporal order matters.


26. Get the latest value using max_by where available

In Spark versions that support it:

summary = (
    df
    .groupBy("customer_id")
    .agg(
        F.max_by(
            F.col("status"),
            F.col("event_time"),
        ).alias("latest_status")
    )
)

This returns the value associated with the maximum ordering expression.

Use a window when you need several columns from the same latest row or need complex tie-breaking.


27. Get the row associated with the latest timestamp

Use a window:

from pyspark.sql import Window

w = (
    Window
    .partitionBy("customer_id")
    .orderBy(
        F.col("event_time").desc(),
        F.col("event_id").desc(),
    )
)

latest = (
    df
    .withColumn(
        "_rn",
        F.row_number().over(w),
    )
    .filter(F.col("_rn") == 1)
    .drop("_rn")
)

Do not use independent max() calls across columns to reconstruct a row.


28. Use rollup() for hierarchical totals

summary = (
    df
    .rollup(
        "region",
        "merchant_id",
    )
    .agg(
        F.sum("amount").alias("revenue")
    )
)

This can produce:

region + merchant total
region subtotal
grand total

Nulls in grouping columns can represent subtotal rows.

Use grouping() / grouping_id() when you need to distinguish true null values from subtotal markers.


29. Use cube() for all grouping combinations

summary = (
    df
    .cube(
        "region",
        "channel",
    )
    .agg(
        F.sum("amount").alias("revenue")
    )
)

cube() can produce many aggregation combinations.

Use it carefully because output size grows with the number of grouping dimensions.


30. Use grouping_id() with rollups/cubes

summary = (
    df
    .rollup(
        "region",
        "merchant_id",
    )
    .agg(
        F.sum("amount").alias("revenue"),
        F.grouping_id().alias("grouping_id"),
    )
)

This helps distinguish:

real null dimension value

from:

subtotal/grand-total row

31. Use pivot() only with bounded categories

pivoted = (
    df
    .groupBy("merchant_id")
    .pivot(
        "status",
        [
            "approved",
            "declined",
            "pending",
        ],
    )
    .agg(
        F.count("*")
    )
)

Supplying the expected pivot values avoids Spark having to discover every distinct category first.

Do not pivot on unbounded/high-cardinality fields.


32. Avoid pivoting customer IDs or transaction IDs

Bad:

df.groupBy("merchant_id").pivot("customer_id")

This can create an enormous number of columns.

Pivot only on small, controlled category sets.


33. Reconcile aggregate totals with the source

Before:

source_total = (
    df
    .agg(
        F.sum("amount").alias("amount")
    )
    .first()["amount"]
)

After:

summary_total = (
    summary
    .agg(
        F.sum("revenue").alias("amount")
    )
    .first()["amount"]
)

Then compare.

If the transformation only changes grain, totals should reconcile unless the business logic intentionally excludes/changes rows.


34. Validate output uniqueness at the intended grain

For:

merchant_id + sale_date

check:

duplicates = (
    summary
    .groupBy(
        "merchant_id",
        "sale_date",
    )
    .count()
    .filter(F.col("count") > 1)
)

if duplicates.limit(1).count() > 0:
    raise RuntimeError(
        "Aggregation output grain is not unique"
    )

A correct groupBy() should naturally produce unique grouping combinations.

If duplicates appear later, a downstream join may have changed the grain.


35. Check input duplication before summing

(
    df
    .groupBy("sale_id")
    .count()
    .filter(F.col("count") > 1)
    .show(50, truncate=False)
)

If one sale should appear once but appears several times, aggregation can hide the underlying duplication while inflating totals.

Fix the grain upstream when appropriate.


36. Filter and project before aggregation

Prefer:

small = (
    df
    .filter(
        F.col("sale_date") >= "2026-10-01"
    )
    .select(
        "merchant_id",
        "sale_date",
        "sale_id",
        "amount",
    )
)

summary = (
    small
    .groupBy(
        "merchant_id",
        "sale_date",
    )
    .agg(
        F.sum("amount").alias("revenue")
    )
)

Do not carry large unused payload columns into shuffle.


37. Inspect the physical plan

summary.explain("formatted")

Look for:

  • Exchange
  • hash aggregation
  • sort aggregation
  • partial aggregation
  • repartitioning

Grouping normally requires a shuffle unless the data is already partitioned in a useful way.


38. Check grouping-key skew

(
    df
    .groupBy("merchant_id")
    .count()
    .orderBy(
        F.desc("count")
    )
    .show(
        50,
        truncate=False,
    )
)

A small number of huge groups can create straggling tasks.

Common causes:

  • dominant tenant/customer
  • null/sentinel key
  • malformed data
  • highly uneven business distribution

39. Do not replace every null key with one sentinel before grouping

Risky:

df = df.fillna(
    {
        "merchant_id": 0
    }
)

This can collapse many unrelated rows into one giant group.

Only use a sentinel when it has real business meaning.


40. Avoid Python UDF aggregation when built-ins are enough

Prefer:

F.sum("amount")
F.avg("amount")
F.max("event_time")
F.countDistinct("sale_id")

over custom Python aggregation logic.

Built-in Spark functions stay inside Spark’s optimized execution engine.

Use pandas UDFs or custom aggregation only when the business logic cannot be expressed with built-ins.

What the result means

Function Meaning
count("*") Number of rows in the group
count("col") Number of non-null values in col
countDistinct() Distinct non-null values
sum() Sum of non-null values
avg() Mean of non-null values
min() / max() Minimum/maximum value
collect_set() Unique values collected into an array
collect_list() Values collected into an array, duplicates preserved
rollup() Hierarchical subtotals + grand total
cube() All combinations of grouping dimensions
pivot() Converts category values into columns
grouping_id() Identifies subtotal/grand-total grouping level

The most important concept is:

groupBy columns = output grain

If the grain is wrong, every metric built on top of it can be wrong.

What to look for

Revenue is too high

Check:

  • duplicate source keys
  • row multiplication from earlier joins
  • wrong grouping grain
  • duplicate input files
  • replayed batches

Aggregation can hide duplicate rows while still inflating totals.

Row count drops more than expected

That is normal when grouping many rows into fewer groups, but verify:

  • intended grain
  • null key handling
  • filters applied before aggregation

Distinct count differs sharply from row count

This may indicate:

  • multiple physical rows per business event
  • duplicates
  • valid line-item grain
  • repeated status/history records

Understand the source grain before deciding it is wrong.

One stage takes much longer than expected

Check grouping-key skew and shuffle volume.

A hot key can dominate aggregation time.

collect_list() causes memory pressure

One or more groups may be accumulating very large arrays.

Use a different data model if group cardinality is unbounded.

Average looks wrong

Check whether:

  • nulls are excluded
  • weighting is required
  • the input was duplicated
  • the unit/grain is correct

Production notes

GroupBy is usually a shuffle-heavy operation

Spark often has to redistribute rows by grouping key.

Reduce rows and columns before aggregation when the business logic allows it.

Built-in aggregates can use partial aggregation

Spark can often aggregate locally before the shuffle and then combine results.

This is one reason built-in aggregate functions are preferable to custom Python logic.

Grouping-key cardinality affects output size

Grouping by:

merchant_id + date

may produce millions of groups.

Grouping by:

country

may produce only dozens.

High cardinality is not inherently wrong, but it changes shuffle and memory behavior.

Skew matters more than average group size

A workload can look balanced on average while one group contains a huge percentage of rows.

Inspect the largest groups when stages have stragglers.

Decimal types are preferable for exact financial totals

Use appropriate DecimalType precision/scale for money-like values where exact arithmetic matters.

Avoid introducing floating-point error into financial reconciliation unnecessarily.

Distinct aggregation can be expensive

countDistinct() and similar distinct operations can require significant shuffle/state.

Use them only when the business metric actually requires distinct semantics.

Do not use aggregation to hide bad grain

If a join doubled every sale and you later group back to one row per sale, the result may still be wrong.

Fix the relationship upstream.

Common mistakes

1. Grouping before defining the output grain

The grouping columns are the business contract of the result.

Define them first.

2. Using count(col) when row count is required

count(col) ignores nulls.

Use count("*") for rows.

3. Using a simple average when a weighted average is required

This produces mathematically wrong business metrics.

4. Assuming first() means earliest

Without ordering, it does not.

Use a window or ordered aggregate.

5. Using max() across several columns to reconstruct the latest row

Each maximum can come from a different source row.

Select the actual row.

6. Pivoting on high-cardinality fields

This can create an impractical number of columns.

7. Using collect_list() on unbounded groups

One group can become too large for memory.

8. Ignoring null grouping keys

Spark groups nulls together.

Decide whether that group is valid.

9. Blaming groupBy performance before checking skew

One hot key can control the entire stage.

10. Running many separate aggregations over the same input

Combine related aggregates in one agg() when practical.

Next step

Use the result to choose the next action.

  • Totals are too high → inspect upstream duplicates and join cardinality.
  • Distinct count differs from row count → confirm source grain and repeated-event semantics.
  • Average is misleading → determine whether weighting is required.
  • Latest value is wrong → use window logic or an ordered aggregate.
  • One group dominates → investigate skew and sentinel/null keys.
  • Aggregation is slow → filter/project earlier and inspect shuffle in the Spark plan.
  • Output needs top-N/latest rows rather than one scalar per group → move to Window Functions.
  • Result must be persisted → choose an appropriate Delta write pattern and validate the target grain.

← All cheat sheets