Module 3: Rebuilding Fact Orders With The Dataframe Api

Grouping by store and by product

Next →

Description

With revenue already calculated in lesson 4, this lesson asks the real business question: how much did each store sell? How much did each product bring in? The DataFrame API's .groupBy().agg() answers both questions — and, following the criterion lesson 4 already hinted at, this is the lesson where you finally apply F.round(), exactly once, on the already-summed result.

Connection to the module. This lesson produces the two breakdowns — by store and by product — that lesson 6 is going to compare, number by number, against the ones you already calculated with dict, DuckDB, Polars, and SQL in the three previous guides. Without this .groupBy(), there's nothing to verify.

An analogy: sorting the already-assembled cards into piles, and summing each one

After lesson 4, you have forty complete cards — each with store, product, quantity, price, and the subtotal already calculated. Grouping them is, literally, sorting them into piles on a table: one pile per store, or one pile per product, and then summing each pile's subtotal separately. .groupBy("store_id") makes the first set of piles; .groupBy("product_id") makes the second; and .agg(F.sum("revenue")) sums each pile, done all at once for the three or four piles together, instead of adding them up one by one by hand.

Worked example: two groupings, with and without rounding

Step 1 — Pick back up fact_orders_df from lesson 4

# group_by_store_and_product.py
from pyspark.sql import SparkSession
from pyspark.sql import functions as F
from pyspark.sql.functions import col
from pyspark.sql.types import (
    StructType, StructField, StringType, IntegerType, DoubleType, TimestampType,
)

spark = SparkSession.builder.appName("kiosko-spark").master("local[*]").getOrCreate()

orders_schema = StructType([
    StructField("order_id", StringType(), False),
    StructField("store_id", StringType(), False),
    StructField("product_id", StringType(), False),
    StructField("quantity", IntegerType(), False),
    StructField("unit_price", DoubleType(), False),
    StructField("order_ts", TimestampType(), False),
])
dim_store_schema = StructType([
    StructField("store_id", StringType(), False),
    StructField("store_name", StringType(), False),
    StructField("city", StringType(), False),
])
dim_product_schema = StructType([
    StructField("product_id", StringType(), False),
    StructField("product_name", StringType(), False),
    StructField("category", StringType(), False),
    StructField("unit_cost", DoubleType(), False),
])

orders_df = spark.read.csv("orders_2026-08-*.csv", schema=orders_schema, header=True, enforceSchema=False)
dim_store_df = spark.read.csv("dim_store.csv", schema=dim_store_schema, header=True, enforceSchema=False)
dim_product_df = spark.read.csv("dim_product.csv", schema=dim_product_schema, header=True, enforceSchema=False)

fact_orders_df = (
    orders_df
    .join(dim_store_df, "store_id")
    .join(dim_product_df, "product_id")
    .withColumn("revenue", col("quantity") * col("unit_price"))
)

Step 2 — First, without rounding (the problem lesson 4 anticipated)

print("=== groupBy WITHOUT F.round() ===")
fact_orders_df.groupBy("store_id").agg(F.sum("revenue").alias("total_revenue")).orderBy("store_id").show()

What to expect (executed in this run):

=== groupBy WITHOUT F.round() ===
+--------+------------------+
|store_id|     total_revenue|
+--------+------------------+
|     S01|              38.3|
|     S02|              38.8|
|     S03|29.049999999999997|
+--------+------------------+

There it is, exactly as lesson 4 anticipated: S03 shows 29.049999999999997, not 29.05 — the same floating-point artifact, now visible after summing sixteen values, not in a single row. S01 and S02 come out clean in this particular case (because of how their binary sums happen to line up), but don't count on that luck in general.

Step 3 — With F.round(), applied exactly once on the sum

print("=== groupBy WITH F.round() ===")
revenue_by_store = (
    fact_orders_df
    .groupBy("store_id")
    .agg(F.round(F.sum("revenue"), 2).alias("total_revenue"))
    .orderBy("store_id")
)
revenue_by_store.show()

print("=== By product, with order count and unit count ===")
revenue_by_product = (
    fact_orders_df
    .groupBy("product_id")
    .agg(
        F.round(F.sum("revenue"), 2).alias("total_revenue"),
        F.count("*").alias("num_orders"),
        F.sum("quantity").alias("total_units"),
    )
    .orderBy("product_id")
)
revenue_by_product.show()

spark.stop()

What to expect (executed in this run):

=== groupBy WITH F.round() ===
+--------+-------------+
|store_id|total_revenue|
+--------+-------------+
|     S01|         38.3|
|     S02|         38.8|
|     S03|        29.05|
+--------+-------------+

=== By product, with order count and unit count ===
+----------+-------------+----------+-----------+
|product_id|total_revenue|num_orders|total_units|
+----------+-------------+----------+-----------+
|      P001|        33.55|        16|         61|
|      P002|         21.6|        10|         18|
|      P003|         10.5|         7|         14|
|      P004|         40.5|         7|          9|
+----------+-------------+----------+-----------+

F.round(F.sum("revenue"), 2) fixes exactly the same case: S03 now shows 29.05, clean. And the breakdown by product brings, besides revenue, two columns you already know from a different angle: num_orders — the count of order lines per product (P001: 16, P002: 10, P003: 7, P004: 7) — is exactly the same count you already saw in the module 1 project, now derived from a different groupBy instead of a separate count. The consistency between those two numbers, calculated in two different lessons with two different queries, is additional evidence that both are correct.

Diagram: two different groupings, over the same forty rows

flowchart TD
    A["fact_orders_df\n40 rows, revenue calculated"]
    A -->|"groupBy('store_id')\n.agg(F.round(F.sum('revenue'), 2))"| B["By store:\nS01=38.3, S02=38.8, S03=29.05"]
    A -->|"groupBy('product_id')\n.agg(F.round(F.sum('revenue'), 2))"| C["By product:\nP001=33.55, P002=21.6,\nP003=10.5, P004=40.5"]
    B -.->|"sum"| D["106.15"]
    C -.->|"sum"| D

Going deeper: F.sum() is an aggregation function, not a regular Column

Notice the difference between col("quantity") (used in lesson 4) and F.sum("revenue") (used here): the first refers to a column that already exists, row by row; the second is an aggregation function — it only makes sense inside an .agg() following a .groupBy() (or over the whole DataFrame, ungrouped, as you saw in lesson 4's comparison). pyspark.sql.functions (almost always imported as F, the convention this guide uses from this lesson onward) brings dozens of these functions — F.sum(), F.avg(), F.count(), F.max(), F.min(), and many more — all designed to work inside an .agg(). The syntax resembles, on purpose, SQL's GROUP BY: .groupBy("store_id") is GROUP BY store_id, and each function inside .agg() is a SELECT column — F.sum("revenue") is, no more no less, SUM(revenue).

It's also worth noting you can group by more than one column at once, if the business question requires it — for example, .groupBy("store_id", "product_id") would answer "how much did each product sell, at each store?", a finer granularity than either of this lesson's two groupBy calls. This guide doesn't need that granularity to verify the 106.15 total, but the syntax is identical: adding one more column to .groupBy().

Common mistakes

Rounding the revenue column before aggregating, instead of rounding the sum. What happens: someone, wanting to avoid the floating-point problem earlier, writes F.sum(F.round(col("revenue"), 2)) instead of F.round(F.sum(col("revenue")), 2) — rounding each individual row before summing it, exactly the pattern lesson 4 advised against. Why it happens: both expressions look interchangeable at first glance, because they use the same two functions. How to spot it: compare the two results over the same data — in Kiosko, at this scale, they probably match, but this guide's criterion (and the three previous ones in the ecosystem) is to apply rounding exactly once, at the end, to keep the error from accumulating unpredictably at larger scale. How to fix it: order matters — aggregate first (F.sum("revenue")), round after (F.round(..., 2)), as this lesson's worked example does.

Forgetting .orderBy() after .groupBy(), and trusting the result comes out sorted. What happens: someone looks at .groupBy("store_id").agg(...)'s result and assumes the rows are going to come out in a fixed order (say, S01, S02, S03), without adding .orderBy("store_id"). Why it happens: in this particular case, with only three stores, the result does usually come out sorted — but that's an implementation coincidence, not a guarantee, exactly the same lesson you already learned in module 2 about .show() without .orderBy(). How to spot it: if your code depends on a groupBy's rows coming out in a specific order with no explicit .orderBy(), you have the same silent risk you already saw before. How to fix it: always add .orderBy() when the result's order matters for comparing against an expected value — exactly what lesson 6 of this module does.

Confusing F.count("*") with F.sum("quantity"). What happens: someone uses F.count("*") (which counts rows, meaning order lines) when they actually wanted F.sum("quantity") (which sums units sold), or the other way around. Why it happens: both answer similar-sounding questions — "how much of this product?" — but at different levels: number of orders versus number of units. How to spot it: in this lesson's worked example, P001 has num_orders = 16 but total_units = 61 — sixteen different order lines, but sixty-one water bottles sold in total, because several orders request more than one unit. If your analysis mixes up these two numbers without noticing, the business conclusions are going to be wrong. How to fix it: name aggregated columns clearly (num_orders versus total_units, as in the worked example) so the difference is impossible to miss when reading the result.

Exercises

Exercise 1 — Group by store and by product at once. Using .groupBy("store_id", "product_id"), calculate rounded revenue for every store-product combination. Confirm that summing S01's four rows (one per product) gives 38.3, the same total you already know for that store.

See solution 
revenue_by_store_product = (
    fact_orders_df
    .groupBy("store_id", "product_id")
    .agg(F.round(F.sum("revenue"), 2).alias("total_revenue"))
    .orderBy("store_id", "product_id")
)
revenue_by_store_product.show(20)

Expected output (executed in this run):

+--------+----------+-------------+
|store_id|product_id|total_revenue|
+--------+----------+-------------+
|     S01|      P001|         11.0|
|     S01|      P002|          4.8|
|     S01|      P003|          4.5|
|     S01|      P004|         18.0|
|     S02|      P001|        10.45|
|     S02|      P002|          9.6|
|     S02|      P003|         5.25|
|     S02|      P004|         13.5|
|     S03|      P001|         12.1|
|     S03|      P002|          7.2|
|     S03|      P003|         0.75|
|     S03|      P004|          9.0|
+--------+----------+-------------+

S01: 11.0 + 4.8 + 4.5 + 18.0 = 38.3 — exact, confirming the finer breakdown (store and product together) is consistent with the lesson's store-only breakdown.

Exercise 2 — Calculate revenue by category, without adding any new column to fact_orders_df. Using .groupBy("category") — a column already coming from dim_product thanks to lesson 3's JOIN — calculate total revenue by category, and confirm beverages is the category with the most revenue.

See solution 
revenue_by_category = (
    fact_orders_df
    .groupBy("category")
    .agg(F.round(F.sum("revenue"), 2).alias("total_revenue"))
    .orderBy(F.desc("total_revenue"))
)
revenue_by_category.show()

Expected output:

+-----------+-------------+
|   category|total_revenue|
+-----------+-------------+
|  beverages|        44.05|
|electronics|         40.5|
|     snacks|         21.6|
+-----------+-------------+

beverages (P001 + P003 = 33.55 + 10.5 = 44.05) is, indeed, the category with the most revenue — the same result, with the same three values, you already calculated with SQL in data-modeling-for-analytics-guide. Notice this query never touched dim_store or needed any new JOIN: category was already available in fact_orders_df since lesson 3, because the JOIN against dim_product brought it along.

Exercise 3 — Explain, without code, why num_orders and total_units can differ for the same product. In 2-3 sentences, using P001's real numbers (num_orders=16, total_units=61), explain why these two numbers don't have to match.

See solution

num_orders counts rows in fact_orders_df — each row is a different order line, regardless of how many units it requested — while total_units sums the quantity column across those same rows. If every order requested exactly one unit, the two numbers would always match; but since quantity varies by order (some request one bottle of water, others request five or six, as you saw in module 1), the sum of units (61) ends up higher than the order count (16). The difference between the two numbers is, itself, business information: sixteen different customers bought bottled water that week, but on average they bought more than three and a half bottles per order.

Summary and next step

In this lesson you grouped fact_orders_df by store and by product with .groupBy().agg(), and saw, with executed evidence, both the floating-point problem the sum inherits from lesson 4 (29.049999999999997) and its correct fix (F.round(F.sum(...), 2), applied exactly once on the aggregated total, not on each row). The two resulting breakdowns — S01=38.3, S02=38.8, S03=29.05 by store, P001=33.55, P002=21.6, P003=10.5, P004=40.5 by product — are the numbers lesson 6 is going to verify against the three previous guides in the ecosystem.

Before moving on you should be able to: write a .groupBy().agg(F.round(F.sum(...), 2)) from memory; explain the difference between F.count("*") and F.sum("quantity"); and explain why the order of round-then-sum matters, with a concrete example from this lesson.

Lesson 6 is this whole module's central lesson: it takes these two breakdowns and compares them, with assert, against dict, DuckDB, Polars, and SQL's exact numbers.

Resources

← PreviousComputing revenue, the same way, four engines laterNext →Verifying the same 106.15 total