An anti join returns the rows of one table that have no match in another table. SQL has NOT EXISTS and LEFT JOIN ... WHERE right.key IS NULL. pandas has no how="anti" option in merge, so people improvise, and the improvised versions fail in quiet ways: duplicated rows, keys that never match because one side is an integer, or NaN keys that match when they should not.
This tutorial shows the three reliable ways to do a pandas anti join, wraps the best one in a small function, and then walks through the key problems that make anti joins return wrong answers. The running example is a common data-maintenance task: you have two snapshots of a "stock code → concept tag" table and want to know which tags were added and which were removed between them.
Code runs on pandas 3.1. All codes and tags below are placeholders.
Two snapshots of a tag table
import pandas as pd
import numpy as np
old = pd.DataFrame({
"code": ["000101", "000101", "000101", "300202", "300202", "600303", "600303", "688404"],
"concept": ["Robotics", "AI Chips", "Auto Parts", "Batteries", "Energy Storage",
"Cloud", "AI Chips", "Semiconductors"],
})
new = pd.DataFrame({
"code": ["000101", "000101", "000101", "300202", "300202", "300202", "600303", "688404", "688404"],
"concept": ["Robotics", "AI Chips", "Humanoid Robots", "Batteries", "Energy Storage",
"Sodium-ion", "Cloud", "Semiconductors", "AI Chips"],
})
print(len(old), "old rows,", len(new), "new rows")
8 old rows, 9 new rows
The key here is the pair (code, concept). A tag was added if the pair exists in new but not in old, and removed if it exists in old but not in new. Both questions are anti joins, in opposite directions.
Method 1: merge with indicator=True
The standard pandas anti join is a left merge with indicator=True, followed by a filter on the _merge column.
m = new.merge(old, on=["code", "concept"], how="left", indicator=True)
print(m["_merge"].value_counts())
added = m.loc[m["_merge"] == "left_only", ["code", "concept"]]
print(added)
_merge
both 6
left_only 3
right_only 0
Name: count, dtype: int64
code concept
2 000101 Humanoid Robots
5 300202 Sodium-ion
8 688404 AI Chips
_merge takes three values: both (the pair is in both frames), left_only (only in new) and right_only (only in old; always 0 in a left merge). The three left_only rows are the added tags: a new humanoid-robot tag on 000101, a sodium-ion tag on 300202, and an AI-chip tag on 688404.
A reusable anti_join function
Two things make the bare merge fragile: if the right frame has extra columns they get pulled in, and if it has duplicate keys the result gets extra rows (more on that below). A small function fixes both by keeping only the key columns from the right side and de-duplicating them.
def anti_join(left, right, on):
m = left.merge(right[on].drop_duplicates(), on=on, how="left", indicator=True)
return m.loc[m["_merge"] == "left_only", left.columns]
removed = anti_join(old, new, ["code", "concept"])
print(removed)
code concept
2 000101 Auto Parts
6 600303 AI Chips
Two tags disappeared: Auto Parts on 000101 and AI Chips on 600303. Same function, arguments swapped.
Method 2: MultiIndex.isin for multi-column keys
If you prefer to avoid a merge, build a MultiIndex from the key columns of each frame and use isin. This keeps the original index of the left frame, which is handy when you want to drop or flag rows in place.
key_new = pd.MultiIndex.from_frame(new[["code", "concept"]])
key_old = pd.MultiIndex.from_frame(old[["code", "concept"]])
added2 = new[~key_new.isin(key_old)]
print(added2.index.tolist())
print(added2.reset_index(drop=True).equals(added.reset_index(drop=True)))
[2, 5, 8]
True
Same three rows, at the same positions (2, 5 and 8) of new. On large frames both methods are fine; use the one that reads better in your code. Duplicate keys on the right side do not hurt isin, which is one point in its favour.
Method 3: Series.isin for a single key column
When the key is one column, the anti join is a one-liner. Which concept names appear in the new snapshot but nowhere in the old one?
brand_new = new.loc[~new["concept"].isin(old["concept"]), "concept"].unique()
print(list(brand_new))
['Humanoid Robots', 'Sodium-ion']
Do not use this pattern for a multi-column key by testing each column separately. ~a.isin(x) | ~b.isin(y) is not the same as "the pair is missing": the pair (688404, AI Chips) is new even though both 688404 and AI Chips exist in old on their own.
Bug 1: duplicate keys on the right multiply rows
If the right frame contains the same key twice, a merge returns one row per match. In an anti join this does not change which keys are left_only, but anything you do with the merged frame, like counting, gets inflated.
old_dup = pd.concat([old, old.iloc[[0, 0]]], ignore_index=True)
m_bad = new.merge(old_dup, on=["code", "concept"], how="left", indicator=True)
print(len(new), "->", len(m_bad), "rows after merge")
print(len(anti_join(new, old_dup, ["code", "concept"])), "added rows with drop_duplicates")
9 -> 11 rows after merge
3 added rows with drop_duplicates
The left frame grew from 9 to 11 rows because one pair appears three times on the right. The drop_duplicates inside anti_join prevents that. You can also pass validate="many_to_one" to merge to make pandas raise an error if the right keys are not unique.
Bug 2: codes that lost their leading zeros
This is the most common reason an anti join "finds" that everything is new. Stock codes, postcodes and account numbers look numeric, so read_csv parses them as integers and drops leading zeros. 000101 becomes 101.
from io import StringIO
csv_text = "code,concept\n101,Robotics\n101,AI Chips\n300202,Batteries\n"
snap = pd.read_csv(StringIO(csv_text))
print(snap.dtypes.to_dict())
print(new["code"].isin(snap["code"]).sum(), "matches with isin")
try:
new.merge(snap, on=["code", "concept"], how="left", indicator=True)
except ValueError as e:
print("ValueError:", str(e)[:80])
{'code': dtype('int64'), 'concept': <StringDtype(storage='python', na_value=nan)>}
0 matches with isin
ValueError: You are trying to merge on str and int64 columns for key 'code'. If you wish to
merge at least refuses to join a string key to an integer key. isin gives no warning at all: it just returns zero matches, so an anti join based on isin would report every row as missing. The fix is to read identifiers as strings and pad them back to their fixed width.
snap2 = pd.read_csv(StringIO(csv_text), dtype={"code": str})
snap2["code"] = snap2["code"].str.zfill(6)
print(snap2["code"].tolist(), new["code"].isin(snap2["code"]).sum(), "matches after fix")
['000101', '000101', '300202'] 6 matches after fix
dtype={"code": str} keeps whatever the file contains; zfill(6) repairs files that were already saved without the zeros (for example after a round trip through a spreadsheet).
Bug 3: NaN keys match each other in pandas
In SQL, NULL = NULL is not true, so rows with a missing key never match. pandas merge does the opposite: missing values in key columns match other missing values.
a = pd.DataFrame({"code": ["000101", None], "concept": ["Robotics", "AI Chips"]})
b = pd.DataFrame({"code": [None], "concept": ["AI Chips"]})
print(anti_join(a, b, ["code", "concept"]))
code concept
0 000101 Robotics
The row with a missing code was treated as "found" because the right frame also had a missing code with the same concept, so it vanished from the anti join. If a missing key means "unknown" in your data, handle those rows separately before joining, for example with a[a["code"].isna()], and run the anti join only on rows with complete keys.
Bug 4: whitespace and case differences
Tags typed by people or scraped from pages often carry trailing spaces or inconsistent capitalisation. To a join, "AI Chips" and " AI Chips" are different keys. Normalise both sides with the same function before comparing.
messy = pd.DataFrame({"code": ["000101", "000101"], "concept": [" AI Chips", "robotics "]})
def norm(df):
out = df.copy()
out["code"] = out["code"].astype(str).str.strip().str.zfill(6)
out["concept"] = out["concept"].str.strip().str.casefold()
return out
print(len(anti_join(messy, old, ["code", "concept"])), "unmatched raw")
print(len(anti_join(norm(messy), norm(old), ["code", "concept"])), "unmatched after normalising")
2 unmatched raw
0 unmatched after normalising
casefold() is a stronger version of lower() that also handles non-English characters correctly. Keep the original column for display if you need the original spelling.
A full added/removed report with one outer merge
If you need both directions at once, do a single outer merge with indicator=True. left_only rows are removed tags, right_only rows are added tags, and both rows are unchanged.
full = norm(old).merge(norm(new), on=["code", "concept"], how="outer", indicator=True)
full["change"] = full["_merge"].map({"left_only": "removed", "right_only": "added", "both": "kept"}).astype(str)
print(full["change"].value_counts())
per_code = (full[full["change"] != "kept"]
.groupby("code")["change"].value_counts()
.unstack(fill_value=0))
print(per_code)
change
kept 6
added 3
removed 2
Name: count, dtype: int64
change added removed
code
000101 1 1
300202 1 0
600303 0 1
688404 1 0
The .astype(str) matters: _merge is a categorical column, and mapping it keeps the categorical type, which makes later groupby and unstack calls show categories with zero rows. Turning it into plain strings keeps the report clean.
The same frame can be pivoted by tag, to see which concepts gained or lost members:
per_concept = full.pivot_table(index="concept", columns="change", values="code",
aggfunc="count", fill_value=0)
print(per_concept.loc[per_concept[["added", "removed"]].sum(axis=1) > 0])
change added kept removed
concept
ai chips 1 1 1
auto parts 0 0 1
humanoid robots 1 0 0
sodium-ion 1 0 0
"ai chips" both gained a member (688404) and lost one (600303) while keeping one (000101). Two tags are brand new. This is the kind of summary you want in a daily data-quality log: a few lines that show what changed, instead of a full table diff.
Which anti join method should you use?
-
Single key column:
left[~left[col].isin(right[col])]. Short and fast. -
Multi-column key, keep original index:
MultiIndex.from_frame(...).isin(...). -
Multi-column key, readable and explicit: a left merge with
indicator=True, wrapped in a function that de-duplicates the right keys. -
Both directions at once: one outer merge with
indicator=True.
Whatever you pick, fix key types and normalise text first. Most wrong anti join results come from the keys, not from the method.
FAQ
Does pandas merge support how="anti"?
No. pandas merge supports left, right, outer, inner and cross. An anti join is built from a left merge with indicator=True plus a filter, or from isin.
Is merge with indicator slower than isin?
For typical table sizes the difference is small. merge builds a new frame with an extra column, so it uses more memory on very large inputs; isin only builds a boolean mask.
How do I anti join on the index?
Use left[~left.index.isin(right.index)], or left.index.difference(right.index) if you only need the labels.
Why did my anti join return every row?
Almost always a key type or format mismatch: integer vs string codes, lost leading zeros, extra spaces or different capitalisation. Check dtypes and a few sample keys from both sides.
Conclusion
A pandas anti join is easy to write and easy to get wrong. Use a left merge with indicator=True and de-duplicated right keys, or MultiIndex.isin for multi-column keys, and use one outer merge when you need added and removed rows together. Before any of that, read identifiers as strings, restore leading zeros, decide how missing keys should behave, and normalise text. With those habits, comparing two snapshots of a tag table becomes a reliable daily check instead of a source of silent errors.
If you work with stock-to-concept tags and want to look up which concept labels are attached to an A-share stock, Lava's stock concepts lookup lists them together with recent news-flow heat.
中文简介:本文讲 pandas 中如何做反连接(anti join,即找出“在 A 表但不在 B 表”的行):用 merge(indicator=True) 后筛选 left_only 并封装成可复用函数,用 MultiIndex.isin 处理多列键,用 Series.isin 处理单列键;并逐一演示导致结果出错的常见坑——右表重复键导致行数膨胀、股票代码被 read_csv 读成整数丢失前导零(isin 不报错、直接零匹配)、pandas 中 NaN 键会互相匹配(与 SQL 相反)、空格与大小写不一致;最后用一次 outer merge 生成“新增/删除/保留”的标签变更报告,并按代码和按概念汇总。代码与标签均为占位示例。
Top comments (0)