Browse cheat sheets · PySpark
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
- Problem
- Quick answer
- Code / commands
- 1. Group by one column
- 2. Group by multiple columns
- 3. Count rows with count("*")
- 4. Count non-null values in one column
- 5. Count distinct business keys
- 6. Sum numeric values
- 7. Average values
- 8. Calculate several aggregates in one pass
- 9. Get minimum and maximum timestamps
- 10. Preserve null grouping keys
- 11. Filter invalid grouping keys before aggregation
- 12. Measure null grouping keys separately
- 13. Group by a date column
- 14. Derive a day from a timestamp
- 15. Aggregate by month
- 16. Conditional aggregation
- 17. Conditional sum
- 18. Compute a ratio from aggregate components
- 19. Calculate a weighted average
- 20. Aggregate booleans or flags
- 21. Collect values into an array
- 22. Use collect_list() only when duplicates matter
- 23. Do not assume collect_list() preserves order
- 24. Aggregate into a struct
- 25. Use first() and last() carefully
- 26. Get the latest value using max_by where available
- 27. Get the row associated with the latest timestamp
- 28. Use rollup() for hierarchical totals
- 29. Use cube() for all grouping combinations
- 30. Use grouping_id() with rollups/cubes
- 31. Use pivot() only with bounded categories
- 32. Avoid pivoting customer IDs or transaction IDs
- 33. Reconcile aggregate totals with the source
- 34. Validate output uniqueness at the intended grain
- 35. Check input duplication before summing
- 36. Filter and project before aggregation
- 37. Inspect the physical plan
- 38. Check grouping-key skew
- 39. Do not replace every null key with one sentinel before grouping
- 40. Avoid Python UDF aggregation when built-ins are enough
- What the result means
- What to look for
- Revenue is too high
- Row count drops more than expected
- Distinct count differs sharply from row count
- One stage takes much longer than expected
- collect_list() causes memory pressure
- Average looks wrong
- Production notes
- GroupBy is usually a shuffle-heavy operation
- Built-in aggregates can use partial aggregation
- Grouping-key cardinality affects output size
- Skew matters more than average group size
- Decimal types are preferable for exact financial totals
- Distinct aggregation can be expensive
- Do not use aggregation to hide bad grain
- Common mistakes
- 1. Grouping before defining the output grain
- 2. Using count(col) when row count is required
- 3. Using a simple average when a weighted average is required
- 4. Assuming first() means earliest
- 5. Using max() across several columns to reconstruct the latest row
- 6. Pivoting on high-cardinality fields
- 7. Using collect_list() on unbounded groups
- 8. Ignoring null grouping keys
- 9. Blaming groupBy performance before checking skew
- 10. Running many separate aggregations over the same input
- Next step
- 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.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.