How do you find rows in one DataFrame that have no match in another?
Use a left merge with indicator=True and keep rows marked left_only, or filter with ~isin on the key column. This is the pandas version of a SQL anti-join, useful for finding missing records.
Explain it simply
You have a list of registered students and a list of students who submitted an assignment. Who has not submitted? You are asking for registered students with no match in the submissions list. SQL calls this an anti-join.
Example
import pandas as pd
a = pd.DataFrame({"id": [1, 2, 3], "name": ["A", "B", "C"]})
b = pd.DataFrame({"id": [2, 3, 4], "mark": [70, 80, 90]})
m = a.merge(b, on="id", how="left", indicator=True)
print(m["_merge"].tolist()) # ['left_only', 'both', 'both']
print(a[~a["id"].isin(b["id"])]["name"].tolist()) # ['A']Both approaches find student A. The indicator=True column also tells you whether each row exists in both tables or one, which is excellent for reconciling two sources.
Join type effects on row counts here: inner gives 2 rows, left gives 3 and outer gives 4.
Before merging, check for duplicates in the key columns: a repeated id on either side multiplies rows. validate="one_to_one" makes pandas raise an error if the keys are not unique.
What to say in the interview
"I do a left merge with indicator=True and filter on left_only, or use isin with negation. I also check key uniqueness with the validate argument, since duplicate keys silently multiply rows."
Common mistakes
- Using an inner merge and then wondering why the unmatched rows disappeared.