groupBy and agg

In SQL you add up rows per group with GROUP BY. In Spark you write the same query as methods on a DataFrame: groupBy names the column to group on, and agg (short for aggregate) lists what each group adds up or counts. The result has one row per group: the group column, then one column per aggregate. Data engineers write this every day (revenue per category, orders per day). On a cluster, each machine first adds up its own share of the rows, so nobody has to pull millions of rows into a Python loop.

The pieces: F.sum(…) is SQL's SUM, F.count("*") is COUNT(*), .alias("name") is AS name, and .orderBy(F.desc("col")) is ORDER BY col DESC. Without F.desc, the smallest value comes first. groupBy on its own gives a GroupedData, not a table: only agg turns it back into a DataFrame. Here is the idea on another column, with the items sold and the top price of each product:

items = (
    orders.groupBy("product")
    .agg(
        F.sum("quantity").alias("items"),
        F.max("unit_price").alias("top_price"),
    )
    .orderBy("product")
)
items.show()

The brackets around the chain let you write one method per line: a line that starts with . calls a method on the result of the line above. groupBy, agg and orderBy only describe the result. show() is the action that makes Spark do the work. A Check takes about 10 seconds here, because Spark starts a Java program before your code runs.

Your code must print this table. Then the check prints revenue.columns:

+--------+-------+-----+
|category|revenue|lines|
+--------+-------+-----+
|   gifts|  20750|  179|
|    home|  16809|  361|
| kitchen|  12682|  348|
|  office|   6737|  513|
+--------+-------+-----+

['category', 'revenue', 'lines']

Your task: the starting code reads orders.csv and keeps the paid lines in paid. Inside the brackets of revenue, group paid by category, add up quantity times unit_price as revenue, count each group's lines as lines, and sort by revenue, biggest first. Below the brackets, print the result with revenue.show().

Assignment
Follow the Coding Tutorial to add up the paid order lines per category with groupBy and agg, then sort the groups and show them.

Hint
The aggregates go inside .agg( … ). Separate them with commas and name each one with .alias. Don't repeat category in agg, because groupBy keeps it as the first column. The multiplication goes inside the sum: F.col("quantity") * F.col("unit_price").