Set Operations: Intersect & Except
Python · Combining DataFrames (SQL Joins & Unions)
Theory
SQL's INTERSECT returns rows present in both tables and EXCEPT (MINUS) returns rows in the first table but not the second, both without duplicates. In pandas, intersection of whole rows is a merge on all shared columns: a.merge(b, how='inner').drop_duplicates(). With identical column names, merge uses every common column as the key, so only rows equal in every column survive.
EXCEPT is an anti-join on all columns: m = a.merge(b.drop_duplicates(), how='left', indicator=True), then keep m['_merge'] == 'left_only' and drop the _merge column. For single values, Python sets or Index methods are simplest: pd.Index(a_ids).intersection(b_ids), .difference(b_ids), .union(b_ids) and .symmetric_difference(b_ids). The same ideas compare two snapshots of a table: rows in today but not yesterday are new, rows in yesterday but not today were removed.
Why we use it
Comparing two versions or two systems is a constant task - reconciling a source with a target, finding new or deleted records between snapshots, checking that two lists agree. Set operations answer these directly.
When to use it
Use merge-based intersect/except when comparing whole rows of two DataFrames with the same columns. Use Index or set operations when comparing just keys. Check that dtypes match first, or equal-looking values will not match.
How it works
- Compare table A with table B
- Comparing whole rows? Yes: Merge on all common columns. No: Keys only: pd.Index methods.
- Rows in both (INTERSECT)? Yes: Inner merge + drop_duplicates(). No: Left merge, indicator=True.
- For EXCEPT keep rows where _merge == 'left_only'
- Matching or differing rows
Syntax
intersect: a.merge(b, how='inner').drop_duplicates()
except: m = a.merge(b.drop_duplicates(), how='left', indicator=True)
m[m['_merge'] == 'left_only'].drop(columns='_merge')
keys: pd.Index(x).intersection(y) / .difference(y)Example
both = today.merge(yesterday, how='inner')
new_rows = (today.merge(yesterday, how='left', indicator=True)
.query("_merge == 'left_only'").drop(columns='_merge'))Key points
- merge with no on= uses all common columns as the key
- INTERSECT = inner merge on all columns + drop_duplicates()
- EXCEPT = left merge with indicator=True, keep 'left_only'
- pd.Index has intersection / difference / union / symmetric_difference
- SQL equivalent: SELECT * FROM today INTERSECT / EXCEPT SELECT * FROM yesterday