Reconcile GSTR-2B with your purchase register in pandas

What the accounts team actually needs from you

The request usually arrives as "tell me which invoices are not in 2B". That is one bucket out of four. A useful report answers all of these in one sheet:

Keep the buckets separate. A single "difference" number hides the fact that a ₹3,960 missing invoice and a ₹90 amount difference need different people.

Prepare both files in the same shape

Download the B2B details from the GST portal and export the purchase register from your accounting software for the same return period. Header names differ between versions and tools, so do not code against them directly. Rename the columns you need into five fixed names: supplier_gstin, invoice_no, invoice_date, taxable, tax. Here tax is the total of IGST, CGST and SGST for the line; keep the heads separately if your team reconciles them head by head.

Read GSTINs and invoice numbers as text. If Excel or pandas guesses them as numbers, an invoice like 000123 silently becomes 123 in one file and not the other.

Normalise the invoice number, but only in a key column

Invoice numbers are where most false mismatches come from. The same document appears as INV/0045 in the supplier's filing and INV/45 in your books. The fix is a separate key column: strip leading zeros from each run of digits first, then remove spaces and separators. Order matters. If you delete the slashes first, W/26-27/009 becomes one long digit run and its zeros no longer look leading.

Never overwrite the original invoice_no. The accounts team will search the supplier's invoice copy using the text they see, not your key.

The reconciliation script

This runs on the synthetic data included. Replace the two DataFrames with pd.read_excel(..., dtype=str) reads of your own files and convert the amount columns to numbers after reading.

import pandas as pd

# Synthetic data. Column names are ours; map your download's headers onto them.
books = pd.DataFrame({
    "supplier_gstin": ["09AAACX1234A1ZV", "09AAACX1234A1ZV", "07AAFCY5678B1Z3",
                       "27AABCZ9012C1ZI", "06AAGCW3456D1ZE"],
    "invoice_no": ["INV/0045", "INV/0046", "Y-2026-118", "Z 7781", "W/26-27/009"],
    "invoice_date": ["03-08-2026", "11-08-2026", "14-08-2026", "20-08-2026", "28-08-2026"],
    "taxable": [50000.00, 12000.00, 80000.00, 15000.00, 22000.00],
    "tax": [9000.00, 2160.00, 14400.00, 2700.00, 3960.00],
})
gstr2b = pd.DataFrame({
    "supplier_gstin": ["09AAACX1234A1ZV", "09AAACX1234A1ZV", "07AAFCY5678B1Z3",
                       "27AABCZ9012C1ZI", "24AAHCV7890E1Z0"],
    "invoice_no": ["INV/45", "INV/0046", "Y-2026-118", "Z7781", "V/331"],
    "invoice_date": ["03-08-2026", "11-08-2026", "14-08-2026", "20-08-2026", "25-08-2026"],
    "taxable": [50000.00, 12000.00, 80000.00, 15500.00, 9000.00],
    "tax": [9000.00, 2160.00, 14400.00, 2790.00, 1620.00],
})

def norm_invoice(s: pd.Series) -> pd.Series:
    s = s.astype(str).str.upper().str.strip()
    s = s.str.replace(r"(?<!\d)0+(?=\d)", "", regex=True)   # INV/0045 -> INV/45
    return s.str.replace(r"[\s\-/\\.]", "", regex=True)       # INV/45 -> INV45

for df in (books, gstr2b):
    df["inv_key"] = norm_invoice(df["invoice_no"])
    df["invoice_date"] = pd.to_datetime(df["invoice_date"], format="%d-%m-%Y")

m = books.merge(gstr2b, on=["supplier_gstin", "inv_key"], how="outer",
                suffixes=("_books", "_2b"), indicator=True, validate="one_to_one")

TOL = 1.00  # rupees; rounding differences up to this are treated as matched

def status(r):
    if r["_merge"] == "left_only":
        return "In books, not in 2B"
    if r["_merge"] == "right_only":
        return "In 2B, not in books"
    same_tax = abs(r["tax_books"] - r["tax_2b"]) <= TOL
    same_base = abs(r["taxable_books"] - r["taxable_2b"]) <= TOL
    return "Matched" if same_tax and same_base else "Amount mismatch"

m["status"] = m.apply(status, axis=1)
m["tax_difference"] = m["tax_2b"].fillna(0) - m["tax_books"].fillna(0)
cols = ["supplier_gstin", "invoice_no_books", "invoice_no_2b",
        "tax_books", "tax_2b", "tax_difference", "status"]
print(m[cols].to_string(index=False))

summary = m.groupby("status").agg(invoices=("status", "size"),
                                  tax_books=("tax_books", "sum"),
                                  tax_2b=("tax_2b", "sum"))
print(summary)

# Control totals: every rupee in both sources must appear in the report.
assert round(m["tax_books"].sum(), 2) == round(books["tax"].sum(), 2)
assert round(m["tax_2b"].sum(), 2) == round(gstr2b["tax"].sum(), 2)

On this data, three invoices match (including INV/0045 against INV/45), one has a ₹90 tax difference, one purchase of ₹3,960 tax is missing from 2B, and one 2B line of ₹1,620 has not been booked.

Why `validate="one_to_one"` is not optional

Normalisation can collide. INV/1-23 and INV/12-3 both become INV123. If that happens, pandas would quietly produce extra rows and your matched tax would be overstated. The validate argument raises an error instead. When it fires, look at the duplicated keys and decide whether that supplier needs a gentler rule, such as keeping hyphens.

The two assert lines at the end are the second guard. If the report's books column does not add up to the purchase register's tax total, something was dropped or doubled and the output should not go out.

Handling the awkward cases

• Same invoice, wrong GSTIN. A supplier with several registrations sometimes reports under a different state's GSTIN. After the main join, take the two "not in" buckets and match them again on invoice key plus amount only. List those pairs as "probable GSTIN mismatch" for a human to confirm; do not auto-match them.
• Credit notes. Keep them as negative tax in both files, or reconcile them as a separate document type. Mixing signs is the usual reason a total refuses to tie.
• Period differences. An invoice dated 29 August that the supplier reports late will show in a later month's statement. Keep an open-items file carried forward month to month rather than treating each month as fresh.

Once this works for one month, the natural next step is a reusable monthly job with a date cutoff and a saved output per period. That kind of repeatable reconciliation is practice work in our Data Analyst programme; if you want to see where your current skills stand first, try the free resume skill check. For the validation of the GSTIN column itself, see Validate GSTIN and PAN formats, including the GSTIN check digit.