The anti-join: find the trades your ledger is missing

Ask anyone who has worked a fund back office - Luxembourg, Dublin, anywhere - what they actually do all day, and the honest answer is reconciliation: our ledger versus the custodian's, the TA's register versus the order file, yesterday's positions versus today's minus trades. The core operation is always the same question: what is on one side and not the other? That is an anti-join, and pandas does it in one merge:

import pandas as pd
ours = pd.DataFrame({"trade_id": [101, 102, 103, 104], "qty": [100, 250, 50, 75]})
cpty = pd.DataFrame({"trade_id": [101, 102, 104, 105], "qty": [100, 250, 80, 30]})
rec = ours.merge(cpty, on="trade_id", how="outer", suffixes=("_ours", "_cpty"), indicator=True)
print(rec[(rec["_merge"] != "both") | (rec["qty_ours"] != rec["qty_cpty"])])

Output - the full break report:

   trade_id  qty_ours  qty_cpty      _merge
2       103      50.0       NaN   left_only
3       104      75.0      80.0        both
4       105       NaN      30.0  right_only

Read it like an ops analyst would:

• left_only (103) - we booked it, the counterparty has no record. Chase the confirmation.
• right_only (105) - they claim a trade we never booked. The dangerous kind: it moves cash you are not expecting.
• both but quantities differ (104) - matched trade, broken economics. A partial fill, a bad amendment, or someone fat-fingered 80 for 75.

The load-bearing argument is indicator=True: it adds the _merge column that classifies every row as both, left_only or right_only. Without it you are writing three separate joins and stitching the results. The how="outer" keeps both unmatched sides - an inner join would silently discard exactly the rows a reconciliation exists to find.

Two production notes. First, reconcile on the real key (trade ID + ISIN + settlement date), not on quantities - matching on values you are trying to verify is circular. Second, watch the dtype trap in the output: unmatched rows force qty from int to float because of the NaN. Harmless in a report, but if downstream code compares qty == 75 after a roundtrip through CSV, that .0 will come for you.

The same pattern in SQL is a LEFT JOIN ... WHERE right.key IS NULL - worth knowing both dialects, because the custodian file is a DataFrame but the ledger is usually a table.