11. `groupBy` and `agg` — summarizing data

Aggregation collapses many rows into a summary: revenue per category, orders per country, average price per segment. In PySpark that's groupBy(...) followed by agg(...).

The quickest aggregate is count:

orders.dropDuplicates().groupBy("category").count().show()

We dropDuplicates() first because orders has that one duplicated row (order 1021) — leaving it in would over-count electronics by one. Cleaning before aggregating is a habit worth forming.

agg with the common functions

agg takes one or more aggregate expressions. The workhorses live on F: sum, avg, min, max, count:

orders.dropDuplicates().groupBy("category").agg(
    F.sum("quantity"),
    F.avg("unit_price"),
    F.min("unit_price"),
    F.max("unit_price"),
).show()

Those auto-generated column names (sum(quantity), …) are ugly to work with — you'll alias them in the next lesson.

count("*") vs countDistinct

Two different questions. count("*") counts rows; countDistinct counts distinct values of a column:

orders.dropDuplicates().agg(
    F.count("*").alias("total_orders"),
    F.countDistinct("customer_id").alias("unique_customers"),
    F.countDistinct("category").alias("categories"),
).show()

No groupBy there — agg straight on the DataFrame aggregates the whole thing into one row. count("*") also counts rows with nulls, whereas count("col") skips rows where that column is null — a distinction that bites when you least expect it.

Your turn

For each country in orders (deduplicated), count the number of orders. Return country and the count. Assign to result.

result = orders  # <- dropDuplicates, groupBy country, count()
result.show()

result = orders.dropDuplicates().groupBy("country").count()