Join Two Tables: merge in Python

Each line of orders.csv says who bought it with a customer_id, but not where that customer lives: the city is in customers.csv. To see an order line next to its customer's city, you join the two tables on the column they share. In pandas that's merge: orders.merge(customers, on="customer_id") gives back a new table in which each order line gets the columns of the customer with the same customer_id.

A small example: two tables that share a size column.

shirts = pd.DataFrame({"shirt": ["Red", "Blue", "Green"], "size": ["M", "S", "XL"]})
prices = pd.DataFrame({"size": ["S", "M"], "price": [10, 12]})
both = shirts.merge(prices, on="size")
all_shirts = shirts.merge(prices, on="size", how="left")

both has 2 rows: Red with price 12 and Blue with price 10. Green is gone, because no row of prices has size XL: by default, merge keeps only the rows that match in both tables, like SQL's JOIN. With how="left", it keeps every row of the left table, the one you call merge on, like SQL's LEFT JOIN: all_shirts has 3 rows, and Green's price is NaN, a missing value.

To compare the tables you get, len(table) gives back a table's number of rows, and isna().sum() on a column counts its missing values.

Your starting code reads both files and keeps the newsletter customers in subscribers. Your program must print:

   order_id product  city
0      1001    Lamp  Rome
1      1001     Mug  Rome
2      1001     Pen  Rome
Order lines: 1497 inner: 767 left: 1497
No match: 730

Your task: merge customers into orders on customer_id and print the order_id, product and city of its first 3 rows. Then merge subscribers into orders twice, the default way into inner and with how="left" into left, print the row counts of orders, inner and left, and print how many rows of left have no newsletter value.

Assignment
Follow the Coding Tutorial to give each order line its customer's city, then compare a default merge and a left merge with the subscribers.

Hint
Call merge on the table whose rows you care about, orders, and pass it the other table and on="customer_id". A kept line with no subscriber gets NaN in every column that comes from subscribers, newsletter included.