Data Analysis · pandas Core · lesson 10 of 12
Merging and joining
about 16 minutes · free · runs in your browser
Bringing two tables together
pd.merge is pandas' SQL join:
pd.merge(orders, customers, on="customer_id") # inner by default
pd.merge(orders, customers, on="customer_id", how="left") # keep all orders
The how argument is the whole game:
| how | Keeps |
|---|---|
inner (default) | only rows matching in both |
left | every row of the left table |
right | every row of the right table |
outer | every row of both |
A left join fills the missing side with NaN, which is exactly how you find the
orders whose customer is unknown.
To stack tables instead of joining them, pd.concat([a, b]) glues rows together.
Your turn: join the orders to the customers keeping every order, then count how many orders had no matching customer.
You start from this, and edit it in the browser:
import pandas as pd
orders = pd.DataFrame({
"order_id": [1, 2, 3, 4],
"customer_id": [10, 11, 99, 10],
"total": [50, 30, 20, 75],
})
customers = pd.DataFrame({
"customer_id": [10, 11, 12],
"name": ["Ada", "Grace", "Alan"],
})
# Set joined (keeping every order) and orphan_count.