🗺️ 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 Window spec with partitionBy and orderBy
  • ▸ Apply rank() and cumulative sum() over a window
  • ▸ Explain why groupBy causes 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.

groupBy → collapses rows

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.

Window function → preserves rows

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.

Two aggregation modes — NaijaCart orders groupBy("city").agg(sum(...)) Lagos · Electronics · 45000 Abuja · Fashion · 8900 Lagos · Electronics · 12000 Kano · Food · 2500 Lagos · Electronics · 78000 … 5 more rows collapse Lagos · 138700 Kano · 91200 Abuja · 82700 3 rows (one per city) rank().over(Window.partitionBy("city")) Lagos · Electronics · 45000 Abuja · Fashion · 8900 Lagos · Electronics · 12000 Kano · Food · 2500 Lagos · Electronics · 78000 … 5 more rows +column Lagos · 45000 · rank 2 Abuja · 8900 · rank 3 Lagos · 12000 · rank 3 Kano · 2500 · rank 3 Lagos · 78000 · rank 1 10 rows (all preserved)
Memory hook

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.
Import alias pattern: PySpark's 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?

naijacart_groupby.py Lesson 06 · groupBy + agg
verified output
=== NaijaCart — Revenue by City === +-----+------+---------+--------+ | city|orders|total_ngn| avg_ngn| +-----+------+---------+--------+ |Lagos| 4| 138700| 34675.0| | Kano| 3| 91200| 30400.0| |Abuja| 3| 82700|27566.67| +-----+------+---------+--------+

🔗 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.

agg_pattern.py Lesson 06 · .agg() structure

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 like count(1) and sum(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") — like groupBy, 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. For rank(), rows are assigned rank 1, 2, 3 in orderBy sequence. For running totals, rows are summed in orderBy sequence.
naijacart_window.py Lesson 06 · rank window function
verified output
=== Top Order per City (Rank = 1) === +-----+--------+-----------+----------+ | city|order_id| category|amount_ngn| +-----+--------+-----------+----------+ |Abuja| ORD010| Home| 41500| | Kano| ORD009|Electronics| 73700| |Lagos| ORD005|Electronics| 78000| +-----+--------+-----------+----------+

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.

running_total.py Lesson 06 · cumulative sum

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.

Frame boundary cheat sheet

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

Tunde, Senior Data Engineer — 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:

tunde_window_specs.py Lesson 06 · three standard window specs

🔀 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.

Shuffle — groupBy redistributes rows by key Stage 1: 3 input partitions P0 Lagos ORD001 Abuja ORD002 Lagos ORD003 P1 Kano ORD004 Lagos ORD005 Kano ORD006 P2 Abuja ORD007 Lagos ORD008 Abuja ORD010 SHUFFLE (network transfer) Stage 2: by-city partitions Lagos all 4 Lagos rows → sum = 138700 Kano all 3 Kano rows → sum = 91200 Abuja all 3 Abuja rows → sum = 82700 Stage boundary visible in Spark UI

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.

Performance implication: If you groupBy on a high-cardinality column (e.g., 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 on a huge column
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.
Forgetting orderBy after groupBy
Spark's groupBy output is distributed across partitions with no guaranteed order between partitions. Without an explicit .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().
Confusing Window.partitionBy with df.groupBy
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

Question 1 of 3
What is the key difference between groupBy and window functions?
Question 2 of 3
Which PySpark expression gives each row a rank within its partition, ordered by amount descending?
Question 3 of 3
groupBy is which type of transformation in Spark?
0
/ 3
Aggregations checked.

Now try groupBy + window on your own data. Move to Lesson 07 when ready.

🎤 Interview Q&A

Medium What is a shuffle and why does groupBy cause one?
+

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.

Medium What's the difference between rank(), dense_rank(), and row_number()?
+

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.

Hard How would you compute a 7-day rolling average in PySpark?
+

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) + optional rowsBetween/rangeBetween (frame boundary). Once you master the spec, swapping rank() for lag() or avg() is trivial.
  • groupBy is 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.
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

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 →