GST refund-to-collection ratios by state, and the join that breaks

Source

From the GST portal's statistics download page, download the annual workbook. We used statewise_GST_collection_2024-25.xlsx (data up to 31 March 2025, provisional). Two of its sheets matter here:

• Collections-Statewise: domestic collections by state, with a full-year block and then monthly blocks of CGST, SGST, IGST, CESS and TOTAL, in rupees crore.
• Refund-Statewise: headed "State Wise Domestic Refund Disbursemet (RFD-05)", spelled that way in the file, with the same block layout. Below the state rows sit refunds on exports disbursed by Customs, which are national only.

Full-year 2024-25 values give a first check for your pipeline:

When we loaded both sheets with pandas, State CD for Delhi came through as the integer 7 from the collections sheet and as the text '07' from the refund sheet. pandas refuses to merge an integer column with a text column, which is helpful. The usual quick fix, converting both to strings, then gives '7' against '07', and the merge runs without error and matches nothing.

Why recruiters notice it

Refunds are a working-capital issue for exporters and a revenue-planning issue for governments, so tax consultants, CFO teams and fiscal analysts track them. The broader skill is catching a join that looks fine but silently loses rows. Many costly reporting errors in business analytics come from exactly this, and interviewers like candidates who check row counts after every merge.

Cleaning issues

1. 1Key types. Normalise State CD to a two-character zero-padded string in both sheets, or join on a cleaned state name and assert that both keys agree.
2. 2Row coverage. Count the states on each side before and after the merge. Use validate="one_to_one" and an indicator column so unmatched rows are visible.
3. 3National-only refund lines. Export refunds through Customs have no state. Exclude them from state ratios, and mention them when comparing with the all-India sheet.
4. 4Timing. A refund in a month often relates to tax paid in an earlier period, so a single month's ratio can exceed what the annual figure suggests. Use annual or rolling ratios for comparison and monthly ones only for flags.
5. 5Provisional figures. Both sheets carry the note that numbers may vary slightly on finalisation. Record the "Data upto" date.

Code: the join, then the ratios

Everything below is synthetic. The state names are placeholders and every value is invented; it copies only the key-type problem found in the real workbook.

import pandas as pd

coll = pd.DataFrame({
    "State CD": [6, 6, 6, 7, 7, 7, 9, 9, 9],
    "State": ["State H"] * 3 + ["State D"] * 3 + ["State U"] * 3,
    "month": ["2024-04", "2024-05", "2024-06"] * 3,
    "collection_cr": [9800, 9500, 9900, 6400, 6300, 6500, 9300, 9100, 9400],
})
refund = pd.DataFrame({
    "State CD": ["06", "06", "06", "07", "07", "07", "09", "09", "09"],
    "month": ["2024-04", "2024-05", "2024-06"] * 3,
    "refund_cr": [610, 1450, 640, 220, 240, 230, 560, 590, 575],
})

try:
    coll.merge(refund, on=["State CD", "month"])
except ValueError as e:
    print("pandas refuses:", str(e).split(".")[0])

# The quick fix: cast both keys to str. It runs, and silently matches nothing.
quick = coll.assign(**{"State CD": coll["State CD"].astype(str)}).merge(
    refund, on=["State CD", "month"], how="inner")
print("quick-fix inner join rows:", len(quick), "of", len(coll))

for df in (coll, refund):
    df["state_cd"] = df["State CD"].astype(str).str.strip().str.zfill(2)
m = coll.merge(refund.drop(columns="State CD"), on=["state_cd", "month"], how="outer",
               validate="one_to_one", indicator=True)
assert (m["_merge"] == "both").all(), m["_merge"].value_counts()

m["ratio_pct"] = 100 * m["refund_cr"] / m["collection_cr"]
annual = m.groupby("State")[["collection_cr", "refund_cr"]].sum()
annual["ratio_pct"] = 100 * annual["refund_cr"] / annual["collection_cr"]

# Flag months far above the state's own typical ratio (robust: median and MAD)
g = m.groupby("State")["ratio_pct"]
m["robust_z"] = (m["ratio_pct"] - g.transform("median")) / (g.transform(lambda s: (s - s.median()).abs().median()) * 1.4826)
m["flag"] = m["robust_z"].abs() > 3.5
print(annual.round(1).to_string())
print(m[["State", "month", "ratio_pct", "robust_z", "flag"]].round(1).to_string(index=False))

The first merge fails loudly. The quick fix is worse, because it returns zero rows and raises no error, and that is the whole lesson. After the fix, the assertion guarantees every state-month matched. The robust z-score compares each month with the state's own median ratio, so one large month stands out even in a state whose ratio is always high.

Analysis plan

• Parse both sheets into tidy monthly tables and assert the full-year block for each.
• Normalise keys, join with validation and report matched counts.
• Compute annual ratios for all states and rank them, excluding rows without a state.
• Compute monthly ratios for Delhi, Haryana and UP, and flag outlier months.
• Repeat for the 2023-24 workbook and compare ratios across the two years.

Deliverables

Interview questions

• Your merge returned no rows and no error. How would you have noticed?
• Why is casting both keys to strings not a real fix?
• What does validate="one_to_one" protect you from?
• Why is a monthly refund ratio harder to interpret than an annual one?
• Refund ratios differ across states. Name two explanations you could test and one you could not.

Start with the GST collections project if you have not parsed the collections sheet yet. Joins and data validation are central to our Data Analyst programme; ask a counsellor through the contact page about fees.