GSTR-1 HSN Summary Python (pandas) Script Generator

import pandas as pd

df = pd.read_csv('invoice_lines.csv', dtype={'hsn_code': str})

# Clean HSN: strip spaces and keep digits only
df['hsn_code'] = df['hsn_code'].astype(str).str.replace(r'\D', '', regex=True)

num_cols = ['qty', 'taxable_value', 'igst_amount', 'cgst_amount', 'sgst_amount']
for c in num_cols:
    df[c] = pd.to_numeric(df[c], errors='coerce').fillna(0)

summary = (
    df.groupby(['hsn_code', 'uom', 'gst_rate'], as_index=False)
      .agg({
          'item_description': 'first',
          'qty': 'sum',
          'taxable_value': 'sum',
          'igst_amount': 'sum',
          'cgst_amount': 'sum',
          'sgst_amount': 'sum',
      })
)

summary['total_value'] = (
    summary['taxable_value'] + summary['igst_amount']
    + summary['cgst_amount'] + summary['sgst_amount']
)

summary = summary.round(2)
summary.to_excel('hsn_summary.xlsx', index=False)
print('HSN rows:', len(summary))
print('Taxable total:', round(summary['taxable_value'].sum(), 2))

# Compare with the source file totals before using the summary
src_taxable = round(df['taxable_value'].sum(), 2)
sum_taxable = round(summary['taxable_value'].sum(), 2)
assert src_taxable == sum_taxable, 'Taxable value does not tie out'
blank_hsn = df[df['hsn_code'] == '']
print('Lines with blank HSN:', len(blank_hsn))