GST collections in Delhi, UP and Haryana from the GST portal workbook

Finding and opening the file

Go to the GST portal's statistics download page. It links to one workbook per financial year. We used statewise_GST_collection_2024-25.xlsx, whose headers say "Data upto : 31st Mar 2025". It has three sheets:

• GST_Revenue-All_India: month-wise national collection and refunds, ending in net GST revenue.
• Collections-Statewise: titled "Revenue Collection State Wise Month Wise", with a sub-heading "State Wise Domestic GST Collection", in rupees crore.
• Refund-Statewise: refunds disbursed state-wise, month-wise.

On the collections sheet, the first columns are State CD and State. Then comes a block for the full financial year, followed by a block per month from April 2024 to March 2025. Each block has five columns: CGST, SGST, IGST, CESS and TOTAL. The header is two rows deep and the month cells are real Excel dates. Every sheet ends with a note that the numbers are provisional and may vary slightly on finalisation.

Full-year 2024-25 totals from that sheet (₹ crore):

IGST is 58.7% of Haryana's total against 30.7% for UP. That difference in mix, not just in size, is what the project should explain. Keep in mind what the sheet measures: it is domestic collection recorded against each state. It is not a measure of consumption in that state.

Why an employer would care

Tax advisory firms, CFO offices, state finance departments and economic researchers work with exactly this workbook. The analysis is modest, but the parsing is realistic: two-row headers, date-valued column labels, full-year blocks sitting beside monthly ones, and footnotes inside the data range. Doing it cleanly, with totals reconciled, shows you can be trusted with a finance team's source files.

Cleaning issues

• Two header rows. Combine the month row and the component row into keys like (2024-05, IGST). Forward-fill the month across its five columns.
• The full-year block. Drop it from the monthly table, but keep it to reconcile against: the twelve months should add up to it.
• Non-state rows. The State column includes "Other Territory" and "OIDAR" (online services supplied from abroad), and rows below the table hold import IGST and notes. Exclude them from state comparisons, and say so.
• Territory changes. Daman and Diu and Dadra and Nagar Haveli appear as separate rows. Decide whether to combine them.
• State codes. State CD is an integer in this sheet. It is stored as zero-padded text in the refund sheet, which matters as soon as you join the two.

Code: reshape and profile

The frame below is synthetic. It mimics the sheet's layout for three months with invented numbers. It is not GST data.

import pandas as pd

months = ["2024-04", "2024-05", "2024-06"]
comps = ["CGST", "SGST", "IGST", "CESS", "TOTAL"]
header = pd.MultiIndex.from_product([months, comps], names=["month", "component"])
vals = {
    "State A": [1500, 1900, 5200, 600, 9200, 1400, 1800, 5600, 650, 9450, 1450, 1850, 5300, 640, 9240],
    "State B": [2200, 2900, 2800, 1300, 9200, 2100, 2800, 3000, 1400, 9300, 2150, 2850, 2900, 1350, 9250],
    "State C": [1300, 1500, 3500, 110, 6410, 1250, 1450, 3600, 115, 6415, 1280, 1480, 3550, 112, 6432],
}
wide = pd.DataFrame.from_dict(vals, orient="index", columns=header)
wide.index.name = "state"

tidy = wide.stack(level=["month", "component"], future_stack=True).rename("rs_crore").reset_index()

# Components must add up to TOTAL for every state and month
parts = tidy[tidy["component"] != "TOTAL"].groupby(["state", "month"])["rs_crore"].sum()
total = tidy[tidy["component"] == "TOTAL"].set_index(["state", "month"])["rs_crore"]
gap = (parts - total).round(2)
print("reconciliation gaps (should be 0):", gap[gap != 0].to_dict())

year = tidy[tidy["component"] != "TOTAL"].pivot_table(index="state", columns="component", values="rs_crore", aggfunc="sum")
mix = year.div(year.sum(axis=1), axis=0).mul(100).round(1)
mix["period_total"] = year.sum(axis=1)
print(mix.to_string())

monthly = tidy[tidy["component"] == "TOTAL"].pivot(index="month", columns="state", values="rs_crore")
print(monthly.pct_change().mul(100).round(1).to_string())

stack on both header levels turns fifteen columns per state into one value column, so every later step is a simple group-by. The reconciliation catches a mis-parsed header: in the synthetic data one state-month is off by ten, and the check reports it. On the real sheet, extend the same check to the twelve months against the full-year block.

Analysis plan

1. 1Parse Collections-Statewise into the tidy table, dropping non-state and note rows, and reconcile components, months and the full-year block.
2. 2Profile component mix for Delhi, UP and Haryana, with all-India for context.
3. 3Chart monthly totals and look for year-end and April effects. Describe them; do not attribute causes the data cannot show.
4. 4Add the 2023-24 workbook and compute year-on-year growth by component for each state.
5. 5Write a short note on why IGST's share differs so much between the three states. Mark any explanation you cannot confirm from official material as a hypothesis.

Deliverables

• 100% stacked bar: component mix for the three states and India.
• Line chart: monthly total collections, three states, one financial year.
• Growth table: state, component, 2023-24, 2024-25, growth.
• README: workbook names, "Data upto" dates, sheet and rows used, excluded rows, reconciliation results, the provisional-numbers note.

Interview questions

• What does "state-wise domestic collection" measure, and what does it not?
• How did you handle the two-row header?
• Your months add up to more than the full-year block. List what could cause that.
• Why might a state's IGST share be much higher than its neighbour's?

The companion refund-to-collection project joins this sheet to the refund sheet. Reshaping wide official workbooks is practised throughout the Data Analyst programme, which runs for 3–4 months.