🗺️ What this lesson covers
Tunde and Funmi at NaijaCart need to answer daily operations questions: which city drove the most revenue this week? Which order was the biggest per city? What is the running revenue total by region? All three require aggregation — but a different kind of aggregation each time.
PySpark gives you two distinct tools for this work. groupBy + agg collapses many rows into one summary row per group. Window functions compute a value across a group of rows, but instead of collapsing the data, they add a new column to each existing row. Knowing which tool to reach for — and why — separates engineers who write readable pipelines from engineers who produce confusing, slow ones.
By the end of this lesson you will be able to:
- ▸ Write
groupBy().agg()with multiple named aggregate functions - ▸ Build a
Windowspec withpartitionByandorderBy - ▸ Apply
rank()and cumulativesum()over a window - ▸ Explain why
groupBycauses a shuffle and why that matters
📐 Two aggregation modes
The fundamental question is: do you want one row per group, or do you want all rows preserved with a new computed column? The answer determines which API you reach for.
10 orders in Lagos → 1 summary row for Lagos. You lose the individual order rows. Perfect for dashboards, reports, and summaries where the detail is not needed downstream.
10 orders in Lagos → 10 rows, each with a new rank_in_city column. The original data is intact. Perfect for top-N filtering, running totals, and day-over-day comparisons.
If you want fewer rows out than you put in, use groupBy. If you want the same number of rows out but with new columns, use a window function.
🧮 Common aggregate functions
All of these live in pyspark.sql.functions. They are used inside .agg() for groupBy aggregations, and also work inside .over(windowSpec) for window-based aggregations.
| Function | What it computes | Notes |
|---|---|---|
count("*") |
Number of rows in the group | count("*") includes nulls; count(col("x")) skips null values in column x |
sum("col") |
Total of all values in the column | Works on numeric columns only; returns null if all values are null |
avg("col") |
Arithmetic mean of the column | Always returns Double type, even if input is IntegerType |
max("col") / min("col") |
Maximum or minimum value in the group | Works on strings too (lexicographic order) — useful for latest/earliest string timestamps |
collect_list("col") |
All values in the group as a Python list | Can be very large for big groups — the entire list must fit in the executor's memory. Use with caution on unbounded data. |
sum, max, min, and round shadow Python's built-ins if imported without an alias. Always import as from pyspark.sql.functions import sum as _sum, max as _max, round as _round.
🏙️ groupBy demo — NaijaCart revenue by city
Funmi runs this query every morning on the previous day's orders. It answers: which city drove the most revenue, how many orders did each city place, and what was the average order value?
🔗 The .agg() pattern — chaining multiple functions
The .agg() method accepts any number of column expressions separated by commas. Each expression is an aggregate function applied to the grouped data, optionally renamed with .alias(). This is the recommended pattern over calling .count(), .sum(), or .mean() as separate method chains — it computes all aggregates in a single Spark job rather than one job per function.
Key details about this pattern:
- ▸ Multiple groupBy keys:
.groupBy("city", "category")groups on the combination of both columns — you get one output row per unique (city, category) pair. - ▸ Always alias: Without
.alias(), Spark generates names likecount(1)andsum(amount_ngn), which break downstream code that selects by name. - ▸ Always orderBy after agg: Spark distributes groupBy results across partitions. Without an explicit
orderBy, the output row order is non-deterministic — it will differ between runs.
🪟 Window functions deep dive
A window function operates on a window of rows defined by a WindowSpec. The key difference from groupBy: Spark computes the aggregate for the group, but attaches the result as a new column on each individual row — every input row survives in the output.
Building a WindowSpec
A WindowSpec has two main clauses:
- ▸
partitionBy("col")— likegroupBy, this defines the boundary of each window. Rows with the same partition key are in the same window. Rows in different partitions do not affect each other's computation. - ▸
orderBy(col("col").desc())— inside a window, ordering defines rank and cumulative computation order. Forrank(), rows are assigned rank 1, 2, 3 in orderBy sequence. For running totals, rows are summed in orderBy sequence.
Notice that we still have all 10 original rows in ranked — withColumn never removes rows. The .filter(col("rank_in_city") == 1) call afterwards is what narrows it to one row per city. This is the standard top-N pattern: window → rank → filter.
📈 Running total with rowsBetween
A running total (cumulative sum) is a window function where the frame includes all rows from the start of the partition up to and including the current row. Spark uses .rowsBetween() to define the frame boundary.
Window.unboundedPreceding means "start from the very first row in this partition". The second argument 0 means "end at the current row". So for each row, Spark sums all amounts from the first order in that city up to and including this order — a classic running total.
rowsBetween(Window.unboundedPreceding, 0) — running total (from start to current row)
rowsBetween(-6, 0) — 7-day rolling window (current row + 6 preceding)
rowsBetween(Window.unboundedPreceding, Window.unboundedFollowing) — partition-level aggregate on every row (same as groupBy but with rows preserved)
👨💻 Tunde's pattern at NaijaCart
"At NaijaCart we run 3 different window specs per pipeline: rank (for top-N), running total (for dashboards), and lag (for day-over-day change). Same window syntax, different functions. Once you understand partitionBy + orderBy, the function you drop in is almost irrelevant — it's the spec that takes the thinking."
Tunde's three standard window specs, reused across NaijaCart's pipelines:
🔀 groupBy is a wide transformation
When you call groupBy, Spark must ensure that all rows sharing the same group key end up on the same partition — because aggregation requires seeing every row for a given key together. To do this, Spark shuffles data across the network.
This network transfer is called a shuffle. It is the most expensive operation in Spark — rows are serialised, written to disk as shuffle files, transferred over the network, and then read back by the receiving executor. Wide transformations like groupBy, join, and distinct all cause a shuffle. This is why they show up as a stage boundary in the Spark UI — Spark must complete all map-side computation before starting the reduce side.
order_id), Spark creates a partition for every unique key. This can produce thousands of tiny shuffle files — a "small files problem". Always groupBy on low-to-medium cardinality columns (city, category, date) or pre-aggregate with reduceByKey before shuffling.
⚠️ Common mistakes
collect_list gathers all values for a group into a single list object that must fit in one executor's memory. On a million-row partition this can cause an OOM (out-of-memory) error that kills the executor. Use it only when you know the group cardinality is small, or pre-filter aggressively before collecting..orderBy() after .agg(), the same code can return rows in a different order on every run. This makes tests that assert on row order flaky. Always add .orderBy() before .show() or .collect().df.groupBy("city").agg(...) produces 3 rows (one per city). Window.partitionBy("city") inside withColumn still produces 10 rows — the partition is a logical boundary within the window computation, not a row-collapsing operation. A common mistake is to use partitionBy thinking it will reduce rows, then be surprised when the DataFrame still has the original row count.🧠 Quiz — 3 questions
Now try groupBy + window on your own data. Move to Lesson 07 when ready.
🎤 Interview Q&A
A shuffle is Spark's mechanism for redistributing data across partitions so that all rows belonging to the same group key end up on the same executor. When you call groupBy("city"), Spark cannot aggregate "Lagos" rows if they are spread across three different executors — it would compute three separate partial sums with no way to combine them. The shuffle fixes this: Spark hashes each row's group key, assigns it to a target partition (all "Lagos" rows → partition N), serialises the rows, writes them to local disk as shuffle files, transfers them over the network to the executor owning partition N, and then the executor reads and aggregates its local copy. This is a wide transformation — Spark cannot start the aggregation stage until all the map-side (pre-shuffle) computation is complete, which is why groupBy creates a stage boundary in the Spark UI. Shuffles are expensive because of disk I/O (shuffle files), network I/O (data transfer), and memory pressure (sort-merge join of shuffle blocks). Minimising unnecessary shuffles — by pre-filtering, broadcasting small DataFrames, or using reduceByKey before shuffling — is one of the most impactful Spark performance optimisations.
All three assign a number to each row within a window partition ordered by some column, but they handle ties differently:
row_number() — assigns a unique sequential integer (1, 2, 3, 4) to every row with no gaps and no ties. If two rows have the same order-by value, one is arbitrarily assigned a lower number. Use this when you need exactly one row per rank, regardless of ties (e.g., take the first row per user).
rank() — tied rows get the same rank, but the next rank after a tie skips numbers to account for the tied rows. Example: two rows tied for rank 2 → both get rank 2, next distinct row gets rank 4 (gap). Use this in competitions or Top-N where ties should be acknowledged and count toward the position numbering.
dense_rank() — tied rows get the same rank, and the next rank increments by 1 with no gaps. Example: two rows tied for rank 2 → both get rank 2, next distinct row gets rank 3 (no gap). Use this when you want "rank within category" without gaps causing confusion (e.g., "there are 5 distinct revenue tiers").
For NaijaCart's top-order-per-city query, rank() is appropriate: if two orders are exactly tied in amount, both should be considered "top" and both get rank 1. row_number() would arbitrarily exclude one of them.
A 7-day rolling average requires a window function with a frame that includes the current row and the 6 preceding rows (7 rows total), ordered by date within each partition. The standard approach:
from pyspark.sql.functions import avg
from pyspark.sql.window import Window
rolling_spec = (Window
.partitionBy("city")
.orderBy(col("order_date").cast("timestamp").cast("long"))
.rowsBetween(-6, 0))
df.withColumn("rolling_7d_avg", avg("amount_ngn").over(rolling_spec))
Two important subtleties: First, rowsBetween(-6, 0) counts physical rows, not calendar days. If your data has missing days (e.g., no orders on Sunday), the window will reach further back in calendar time than 7 days to collect 7 rows. If you need exactly 7 calendar days regardless of missing data, use rangeBetween with epoch seconds: .orderBy(col("order_date").cast("long")).rangeBetween(-6*86400, 0) — this bounds by column value rather than row count, so it correctly handles sparse data.
Second, the first 6 rows in each partition will have fewer than 7 rows in their window frame (they have no preceding rows). The average of those rows will be computed over 1–6 rows, not 7. Whether this is acceptable depends on the business requirement. If you need a minimum window size, add a filter after: .filter(row_count_col >= 7).
✅ Key takeaways
groupBy().agg()collapses many rows to one row per group. Use it for summary reports, dashboards, and any output where individual rows are not needed downstream.- Window functions —
rank(),sum(),lag(),avg()with.over(windowSpec)— preserve all rows and add a computed column. Use them for top-N filtering, running totals, and period-over-period comparisons. - A
WindowSpec=partitionBy(group boundary) +orderBy(sort within the group) + optionalrowsBetween/rangeBetween(frame boundary). Once you master the spec, swappingrank()forlag()oravg()is trivial. groupByis a wide transformation — it causes a shuffle that redistributes data across executors by key. This makes it the most expensive common transformation in PySpark, and a stage boundary in the Spark UI. Minimise unnecessary groupBy calls by filtering early and pre-aggregating where possible.
The foundation our AI platform engineers stand on
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →