Now: Getting KeyTips ready for release
Nazmul HosenSay hello

Python · 28 Oct 2026

Reconcile two ledgers in five lines of pandas

An outer merge with indicator and validate finds every break, and tells you when the keys themselves are the problem.

VLOOKUP in both directions gets slow and error-prone once a reconciliation reaches real volume. pandas does the whole thing in one merge:

recon = gl.merge(
    subledger, on="invoice", how="outer",
    suffixes=("_gl", "_sub"),
    indicator=True, validate="one_to_one",
)
recon["diff"] = recon["amount_gl"].fillna(0) - recon["amount_sub"].fillna(0)
breaks = recon[(recon["_merge"] != "both") | (recon["diff"].abs() > 0.005)]

Why those two arguments matter

  • indicator=True adds a _merge column showing left_only, right_only or both, so you can see which side is missing an item.
  • validate="one_to_one" raises an error if an invoice appears twice on either side. Without it, duplicates fan out into extra rows and quietly distort your totals.

Keep it tied out

Before you trust the output, check that the differences add up to the difference in the control totals:

assert abs(breaks["diff"].sum() - (gl["amount"].sum() - subledger["amount"].sum())) < 0.01

Then send the breaks to Excel for review with breaks.to_excel("breaks.xlsx", index=False), which needs openpyxl installed.

← All tips