select, where, withColumn in Python

Every SQL clause you know has a DataFrame method. where(condition) is SQL's WHERE. withColumn("name", expression) adds a computed column to every row, like expression AS name in a SELECT list (give it an existing name and it replaces that column). select("a", "b") is the SELECT list: it keeps those columns and drops the rest. orderBy(...) is ORDER BY, and F.desc("col") is DESC. Inside an expression a column is F.col("name"), so F.col("a") * F.col("b") multiplies two columns row by row.

Each method returns a new DataFrame, so you call the next method right on it: a chain. Inside round brackets Python lets a chain run over several lines, one method per line starting with a dot, and it reads top to bottom like a SQL query. For example, on the customers file:

ages = (
    customers.where(F.col("newsletter") == "yes")
    .withColumn("age_next_year", F.col("age") + 1)
    .select("customer_id", "age_next_year")
)

Why this matters at work: a line's total is computed inside the DataFrame, by Spark, wherever the rows live; a Python loop over the rows would pull every row into one program, the very thing Spark is there to avoid. And a sort needs a second column for ties: with only the total, lines with the same total come in no promised order, and on a cluster that order changes between runs, so the same report differs from one day to the next.

Everything inside the brackets only describes the result; show(5) is the action that runs it. Your program prints the 5 biggest paid lines, then the check prints how many lines paid has and its columns:

+--------+---------+-----+
|order_id|  product|total|
+--------+---------+-----+
|    1004|Gift card|  250|
|    1014|Gift card|  250|
|    1024|Gift card|  250|
|    1036|Gift card|  250|
|    1171|Gift card|  250|
+--------+---------+-----+
only showing top 5 rows
1401
['order_id', 'product', 'total']

A Check takes about 10 seconds here, because Spark starts a Java program before your code runs.

Your task: inside the brackets of paid, start from orders and keep the paid lines, add a column total (quantity times unit_price), keep order_id, product and total, and sort by total, biggest first, then by order_id. Below the brackets, show the first 5 rows.

Assignment
Follow the Coding Tutorial to chain where, withColumn, select and orderBy inside the brackets of paid, then run the chain with show(5).

Hint
The first line inside the brackets starts with orders.where(; every next line starts with a dot. In orderBy, F.desc("total") comes first and "order_id" second.