Python · easy

Merge two DataFrames on a key and keep unmatched rows from the left.

Asked in Data Analyst interviews, in the Python round.

Short answer

pd.merge(left, right, on='key', how='left').

Sample data

These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.

users

user_id	name
1	Ada
2	Ben
3	Cara
4	Dev

orders

order_id	user_id	amount
10	1	40
11	1	25
12	3	90

How to answer it

Definition

A left join in pandas: every row of the left frame survives, and the right frame's columns are NaN where there was no match.

Syntax

merged = pd.merge(left, right, on="key", how="left")

Example

merged = pd.merge(
    customers, orders,
    on="customer_id",
    how="left",
    validate="one_to_many",     # raises if the left key is not unique
    indicator=True,             # adds _merge, saying where each row came from
)

print(merged["_merge"].value_counts())
print(len(customers), len(merged))

Output

both          812
left_only     188
right_only      0
Name: _merge, dtype: int64
1000 1247

The row-count check

1000 customers went in and 1247 rows came out, because a customer with three orders produces three rows. That is correct for this join and catastrophic if you expected one row per customer and are about to sum a column. Comparing the counts is the cheapest bug-catcher in pandas, which is why validate and indicator are in the example rather than a footnote.

Key points

how="left" keeps every left row. The default is how="inner", which silently drops non-matches.
validate="one_to_one" or "one_to_many" turns a wrong assumption into an exception instead of wrong numbers.
indicator=True shows how many rows matched, which is how you notice a join key with the wrong dtype.
Check the row count before and after. Growth means fan-out; shrinkage means a left join that was not.
Different key names use left_on and right_on. Overlapping column names need suffixes.

FAQ

merge or join? join merges on the index and suits a key that already is the index. merge is explicit and generally clearer.
Why did my int key stop matching? One side became float or object, often after a read_csv with missing values. Cast both sides.
How do I find only the unmatched rows? Filter where _merge equals left_only.

The 30-second answer

pd.merge with on the key and how set to left, which keeps every row from the left frame and fills the right side with NaN where nothing matched.

Two habits I would show. I pass validate, so if I believe the key is unique on the left, pandas raises instead of silently fanning out. And I compare the row count before and after, because a left join that grows the frame means the right side had duplicate keys, and any sum I take afterwards is inflated. The indicator flag is a quick way to see how many rows actually matched, which is usually how you catch a key with mismatched dtypes.

In the interview

What they are checking: whether you know a left join can fan out, and whether you check the match rate.
Common mistake: leaving how at its default. The default is inner, and the unmatched rows disappear without a message.

Practice this for real

Check your resume against a Data Analyst job description, free, no account needed.
Every data interview question, by role and topic
Scored AI mock interviews, two free, no card