Pondicherry.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\", 'This pattern has match groups')\n",
    "\n",
    "\n",
    "# Excel Files\n",
    "vrdata = pd.read_excel('VR_DATA.XLSX', index_col=None)\n",
    "forecast = pd.read_excel('Purchase Forecast for analytics.xlsx', index_col=None)\n",
    "\n",
    "# Data Preprocessing\n",
    "\n",
    "features_vrdata = vrdata[['GSTIN No.','Name 1','Month','VR Type','Flag','Reference','VR Match code','OptiGST : Section Type','Taxable','IGST','CGST','SGST']].dropna(how = 'any').reset_index(drop = True)\n",
    "features_forecast = forecast[['GSTIN Number','Vendor Name','Projected month of Accounting (MMYYYY)','CGST','SGST','IGST']].reset_index(drop = True)\n",
    "\n",
    "features_vrdata.columns = ['GSTNO','V_NAME','MONTH','VR_TYPE','FLAG','REFERENCE','MATCH','SECTION','TAXABLE','CGST','SGST','IGST']\n",
    "features_forecast.columns = ['GSTNO','V_NAME','MONTH','CGST','SGST','IGST']\n",
    "\n",
    "features_forecast['V_NAME'] = features_forecast['V_NAME'].map(lambda x: re.sub(r'\\W+', '', x))\n",
    "features_forecast['V_NAME'] = features_forecast['V_NAME'].str.upper()\n",
    "features_vrdata['V_NAME'] = features_vrdata['V_NAME'].map(lambda x: re.sub(r'\\W+', '', x))\n",
    "features_vrdata['V_NAME'] = features_vrdata['V_NAME'].str.upper()\n",
    "\n",
    "features_vrdata['MONTH'] = pd.to_datetime(features_vrdata['MONTH'], format='%m%Y').dt.to_period('m') \n",
    "features_forecast['MONTH'] = pd.to_datetime(features_forecast['MONTH'], format='%m%Y').dt.to_period('m') \n",
    "\n",
    "features_forecast['CGST'] = features_forecast['CGST'].fillna(0)\n",
    "features_forecast['SGST'] = features_forecast['SGST'].fillna(0)\n",
    "features_forecast['IGST'] = features_forecast['IGST'].fillna(0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "q_forecast = features_forecast.groupby(['GSTNO','MONTH'],as_index=False )['CGST','SGST','IGST'].sum()\n",
    "q_forecast['TAX'] = q_forecast['IGST']+q_forecast['CGST']+q_forecast['SGST']\n",
    "\n",
    "vr_fr = features_vrdata[features_vrdata.GSTNO != 0].reset_index(drop=True)\n",
    "\n",
    "vr_fr = pd.merge(features_vrdata,features_forecast[['GSTNO']],left_on=['GSTNO'],right_on=['GSTNO']).drop_duplicates().reset_index(drop=True)\n",
    "\n",
    "checkgstr2a = vr_fr.loc[vr_fr['VR_TYPE'] == 'GSTR2A'].reset_index(drop=True)\n",
    "\n",
    "checkgstr2 = vr_fr.loc[vr_fr['VR_TYPE'] == 'GSTR2'].reset_index(drop=True)\n",
    "\n",
    "checkgstr2a = checkgstr2a[checkgstr2a.MATCH != 0].reset_index(drop=True)\n",
    "\n",
    "# checkgstr2a= checkgstr2a[(checkgstr2a.FLAG == \"A\")  | (checkgstr2a.FLAG == \"M\") ].reset_index(drop=True)\n",
    "\n",
    "checkgstr2a = checkgstr2a[['GSTNO','IGST','CGST','SGST']]\n",
    "\n",
    "checkgstr2a = checkgstr2a.groupby(['GSTNO'],as_index=False).sum()\n",
    "\n",
    "checkgstr2 = checkgstr2[['GSTNO','IGST','CGST','SGST']]\n",
    "\n",
    "checkgstr2 = checkgstr2.groupby(['GSTNO'],as_index=False).sum()\n",
    "\n",
    "merge = pd.merge(checkgstr2[['GSTNO','CGST','IGST','SGST']],checkgstr2a[['GSTNO','CGST','IGST','SGST']],on=['GSTNO'])\n",
    "\n",
    "merge = merge.groupby(['GSTNO'],as_index=False).sum()\n",
    "\n",
    "merge['GSTR2_TAX'] = merge['IGST_x']+merge['CGST_x']+merge['SGST_x']\n",
    "\n",
    "merge['GSTR2A_TAX'] = merge['IGST_y']+merge['CGST_y']+merge['SGST_y']\n",
    "\n",
    "merge['DIFF'] = abs(merge['GSTR2_TAX']-merge['GSTR2A_TAX'])\n",
    "\n",
    "merge['PROB'] = (1-(merge['DIFF']/merge['GSTR2_TAX']))*100\n",
    "\n",
    "features_forecast['TAX'] = features_forecast['SGST']+features_forecast['IGST']+features_forecast['CGST']\n",
    "\n",
    "merge = pd.merge(merge[['GSTNO','GSTR2_TAX','GSTR2A_TAX','DIFF','PROB']],features_forecast[['GSTNO','V_NAME','MONTH','TAX']],on=['GSTNO'])\n",
    "\n",
    "merge['ITC'] = merge['TAX'] * merge['PROB']/100\n",
    "\n",
    "merge.to_csv('PondResult.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "54.01106763486345"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prob = merge['ITC'].sum()/merge['TAX'].sum()*100\n",
    "\n",
    "prob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}

TamilNadu.ipynb