Level 2 · Customers who never ordered

Left joins keep everything on the left

An inner join drops rows that have no match. When the unmatched rows are what you’re looking for, that’s exactly wrong. A left join keeps every row of the left table and fills the right table’s columns with NaN where nothing matched:

customers.merge(orders, on="customer_id", how="left")

Customers who never ordered appear once, with NaN in every order column. Customers with five orders appear five times. That second fact matters: a left join can make the table longer, so it’s not a way to “add information” to customers without changing their count.

indicator: where did each row come from?

indicator=True adds a _merge column with one of three values:

• "both": the row matched.
• "left_only": only in the left table.
• "right_only": only in the right (possible with how="outer").

ordered = orders[["customer_id"]].drop_duplicates()
merged = customers.merge(ordered, on="customer_id", how="left", indicator=True)
merged["_merge"].value_counts()
never = merged[merged["_merge"] == "left_only"]

orders[["customer_id"]] is a one-column table with one row per order, so a customer with five orders appears five times. .drop_duplicates() removes repeated rows and keeps the first of each, leaving every customer ID once:

orders[["customer_id"]].drop_duplicates()   # 1,771 rows: each customer who ordered, once

De-duplicating like this before the merge keeps the merged table at exactly one row per customer, so no fan-out at all. (You’ll see drop_duplicates in more depth in the cleaning chapter.)

indicator is also the best tool for auditing a merge you didn’t expect to lose rows on: run it with how="outer" and look at what’s left_only and right_only.

The isin shortcut

When you only need the left table’s rows, an anti-join with isin is shorter and doesn’t create a big intermediate table:

customers[~customers["customer_id"].isin(orders["customer_id"])]

Read it as “customers whose ID is not in the orders’ customer IDs”. Both approaches give the same rows. Use isin for a quick anti-join and the merge with indicator when you also want to inspect matched rows.

Checking NaN after a left join

Another common way to find unmatched rows is to test a right-hand column for missing values. .isna() returns True where a value is missing (NaN, pandas’ marker for “no value”):

merged = customers.merge(orders, on="customer_id", how="left")
merged[merged["order_id"].isna()]

This works when the column you test is never missing for matched rows. order_id qualifies, but a column like coupon_code, which is often empty on real orders, would wrongly flag lots of buyers. Test a key column, or use indicator.

Who are these customers?

Some are recent signups who simply haven’t bought yet, and some are the duplicate customer records you’ll meet in the cleaning chapter: the same person signed up twice and placed orders only under the first ID. Before Logan emails everyone on this list, those duplicates should be removed, or a loyal customer gets a “you haven’t ordered yet” email.

Common mistakes

• Using an inner join and wondering why the result is empty.
• Testing a column that has gaps of its own for NaN to detect “no match”.
• Forgetting that a left join to a “many” table repeats left rows.

If you know SQL

This is a LEFT JOIN ... WHERE o.customer_id IS NULL, or WHERE customer_id NOT IN (SELECT customer_id FROM orders).