Joins in Python

An order line knows who bought it, its customer_id, but not where they live: the city sits in customers.csv. A join puts the rows of two DataFrames side by side wherever a key matches, exactly like SQL's JOIN. Data engineers join all the time, because each fact lives in its own table: orders in one, customers in another.

In PySpark you call .join on the left DataFrame and name the key as a string: left.join(right, "key"). That is SQL's JOIN … USING (key), so the result keeps one key column, placed first. (A condition such as left.key == right.key, SQL's ON, keeps both columns, and asking for "key" later stops with an error.) Adding how="left" makes it a LEFT JOIN: every row of the left side stays, with NULL where nothing matched.

ages = spark.read.csv("customers.csv", header=True, inferSchema=True).select("customer_id", "age")
with_age = orders.join(ages, "customer_id", how="left")

Why how="left"? The default is an inner join, which quietly drops every row without a match. At work that means money vanishes from a report and nobody gets a warning. This lesson reads one day's export, orders/2026-05-06.csv, where paid order 5058 has a blank customer_id: the left join keeps it, with a NULL city.

The join, groupBy, agg and orderBy only describe work; revenue.show() is the action that runs them all. Your program must print this (the last line is the check printing joined.columns):

+------+-------+
|  city|revenue|
+------+-------+
|Madrid|    105|
| Porto|     75|
|  Lyon|     38|
|  NULL|      2|
+------+-------+

['customer_id', 'order_id', 'product', 'quantity', 'unit_price', 'status', 'ordered_at', 'city']

Your task: the starting code reads the day's orders into orders, keeps the paid lines in paid, and reads customers with only customer_id and city. Left-join paid with customers on "customer_id" into joined, build revenue inside its brackets (group by city, sum quantity * unit_price as revenue, biggest first), then show it. A Check takes about 10 seconds here, because Spark starts a Java program first.

Assignment
Follow the Coding Tutorial to join the paid orders with the customers and print the revenue of each city.

Hint
Give the key as the string "customer_id" and add how="left", so the order with no customer stays. After the join, city is a column of every line: groupBy, agg and orderBy work on joined as on any DataFrame.