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.bothbut 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.