full-ecommerce-data-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "239a5a56",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter your name: GUJARAT\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ship To State</th>\n",
       "      <th>Hsn/sac</th>\n",
       "      <th>Igst Rate</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Tax Exclusive Gross</th>\n",
       "      <th>Igst Tax</th>\n",
       "      <th>Cgst Rate</th>\n",
       "      <th>Sgst Rate</th>\n",
       "      <th>total_tax</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GUJARAT</td>\n",
       "      <td>5407.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "      <td>803.82</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.15</td>\n",
       "      <td>804.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GUJARAT</td>\n",
       "      <td>6501.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>4</td>\n",
       "      <td>1788.60</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.10</td>\n",
       "      <td>1788.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GUJARAT</td>\n",
       "      <td>61079990.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>204</td>\n",
       "      <td>77882.78</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6.15</td>\n",
       "      <td>6.15</td>\n",
       "      <td>77895.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GUJARAT</td>\n",
       "      <td>61079990.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>1</td>\n",
       "      <td>330.48</td>\n",
       "      <td>16.52</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>330.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GUJARAT</td>\n",
       "      <td>62123000.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8</td>\n",
       "      <td>3062.94</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.20</td>\n",
       "      <td>3063.34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Ship To State     Hsn/sac  Igst Rate  Quantity  Tax Exclusive Gross  \\\n",
       "0       GUJARAT      5407.0       0.00         2               803.82   \n",
       "1       GUJARAT      6501.0       0.00         4              1788.60   \n",
       "2       GUJARAT  61079990.0       0.00       204             77882.78   \n",
       "3       GUJARAT  61079990.0       0.05         1               330.48   \n",
       "4       GUJARAT  62123000.0       0.00         8              3062.94   \n",
       "\n",
       "   Igst Tax  Cgst Rate  Sgst Rate  total_tax  \n",
       "0      0.00       0.15       0.15     804.12  \n",
       "1      0.00       0.10       0.10    1788.80  \n",
       "2      0.00       6.15       6.15   77895.08  \n",
       "3     16.52       0.00       0.00     330.53  \n",
       "4      0.00       0.20       0.20    3063.34  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state = input(\"Enter your name: \")\n",
    "import pandas as pd\n",
    "df = pd.read_csv(\"B2C_sales_data.csv\")\n",
    "df.head()\n",
    "\n",
    "state_group = df.groupby([\"Seller Gstin\", \"Ship To State\"])\n",
    "if state == \"ALL\":\n",
    "    state_group = df.groupby([\"Seller Gstin\"])\n",
    "    delhi_gujarat = state_group.get_group(('24AVJPJ4767F1ZG'))\n",
    "else:\n",
    "    state_group = df.groupby([\"Seller Gstin\", \"Ship To State\"])\n",
    "    delhi_gujarat = state_group.get_group(('24AVJPJ4767F1ZG',state))\n",
    "\n",
    "sales = delhi_gujarat.loc[:, ['Ship To State', 'Hsn/sac','Igst Rate','Quantity','Transaction Type','Tax Exclusive Gross','Igst Tax','Cgst Rate','Sgst Rate']]\n",
    "# sales['Igst Rate'] = sales['Igst Rate'].replace(0.05,5)\n",
    "sales\n",
    "\n",
    "total = sales.assign(total_tax = sales['Tax Exclusive Gross'] + sales['Igst Rate'] + sales['Cgst Rate'] + sales['Sgst Rate'])\n",
    "total\n",
    "\n",
    "total.loc[delhi_gujarat['Transaction Type'].isin(['Refund', 'Cancel']), 'Quantity'] *= -1\n",
    "total = total.drop(total[total['Tax Exclusive Gross'] == 0].index)\n",
    "total\n",
    "\n",
    "all_total = total.groupby(['Ship To State','Hsn/sac', 'Igst Rate']).agg({'Quantity': 'sum', 'Tax Exclusive Gross': 'sum', 'Igst Tax': 'sum','Cgst Rate':'sum','Sgst Rate':'sum', 'total_tax': 'sum'})\n",
    "all_total\n",
    "\n",
    "\n",
    "result = all_total.reset_index()\n",
    "result\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35f42b45",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "result.to_excel('full-ecommerce.xlsx', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6de5adba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter City Name: ALL\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ship To State</th>\n",
       "      <th>Hsn/sac</th>\n",
       "      <th>Igst Rate</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Tax Exclusive Gross</th>\n",
       "      <th>Igst Tax</th>\n",
       "      <th>Cgst Rate</th>\n",
       "      <th>Sgst Rate</th>\n",
       "      <th>total_tax</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>61079990.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-1</td>\n",
       "      <td>-38.10</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-38.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>61079990.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>2</td>\n",
       "      <td>708.58</td>\n",
       "      <td>35.42</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>708.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>62123000.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>1</td>\n",
       "      <td>368.57</td>\n",
       "      <td>18.43</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>368.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ANDHRA PRADESH</td>\n",
       "      <td>6501.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>1</td>\n",
       "      <td>473.33</td>\n",
       "      <td>23.67</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>473.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ANDHRA PRADESH</td>\n",
       "      <td>61079990.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>56</td>\n",
       "      <td>20417.31</td>\n",
       "      <td>1003.59</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20420.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>UTTARAKHAND</td>\n",
       "      <td>62123000.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>3</td>\n",
       "      <td>1220.01</td>\n",
       "      <td>55.29</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1220.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>5407.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>8</td>\n",
       "      <td>2834.31</td>\n",
       "      <td>141.69</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2834.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>6501.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>3</td>\n",
       "      <td>1419.99</td>\n",
       "      <td>71.01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1420.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>61079990.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>141</td>\n",
       "      <td>51169.16</td>\n",
       "      <td>2487.54</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>51177.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>62123000.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>14</td>\n",
       "      <td>5312.38</td>\n",
       "      <td>258.02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5313.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>115 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Ship To State     Hsn/sac  Igst Rate  Quantity  \\\n",
       "0    ANDAMAN & NICOBAR ISLANDS  61079990.0       0.00        -1   \n",
       "1    ANDAMAN & NICOBAR ISLANDS  61079990.0       0.05         2   \n",
       "2    ANDAMAN & NICOBAR ISLANDS  62123000.0       0.05         1   \n",
       "3               ANDHRA PRADESH      6501.0       0.05         1   \n",
       "4               ANDHRA PRADESH  61079990.0       0.05        56   \n",
       "..                         ...         ...        ...       ...   \n",
       "110                UTTARAKHAND  62123000.0       0.05         3   \n",
       "111                WEST BENGAL      5407.0       0.05         8   \n",
       "112                WEST BENGAL      6501.0       0.05         3   \n",
       "113                WEST BENGAL  61079990.0       0.05       141   \n",
       "114                WEST BENGAL  62123000.0       0.05        14   \n",
       "\n",
       "     Tax Exclusive Gross  Igst Tax  Cgst Rate  Sgst Rate  total_tax  \n",
       "0                 -38.10      0.00        0.0        0.0     -38.10  \n",
       "1                 708.58     35.42        0.0        0.0     708.68  \n",
       "2                 368.57     18.43        0.0        0.0     368.62  \n",
       "3                 473.33     23.67        0.0        0.0     473.38  \n",
       "4               20417.31   1003.59        0.0        0.0   20420.46  \n",
       "..                   ...       ...        ...        ...        ...  \n",
       "110              1220.01     55.29        0.0        0.0    1220.16  \n",
       "111              2834.31    141.69        0.0        0.0    2834.81  \n",
       "112              1419.99     71.01        0.0        0.0    1420.14  \n",
       "113             51169.16   2487.54        0.0        0.0   51177.11  \n",
       "114              5312.38    258.02        0.0        0.0    5313.08  \n",
       "\n",
       "[115 rows x 9 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state = input(\"Enter City Name: \")\n",
    "import pandas as pd\n",
    "df = pd.read_csv(\"B2C_sales_data.csv\")\n",
    "df.head()\n",
    "\n",
    "state_group = df.groupby([\"Seller Gstin\", \"Ship To State\"])\n",
    "if state == \"ALL\":\n",
    "    state_group = df.groupby([\"Seller Gstin\"])\n",
    "    delhi_gujarat = state_group.get_group(('24AVJPJ4767F1ZG'))\n",
    "else:\n",
    "    state_group = df.groupby([\"Seller Gstin\", \"Ship To State\"])\n",
    "    delhi_gujarat = state_group.get_group(('24AVJPJ4767F1ZG',state))\n",
    "\n",
    "sales = delhi_gujarat.loc[:, ['Ship To State', 'Hsn/sac','Igst Rate','Quantity','Transaction Type','Tax Exclusive Gross','Igst Tax','Cgst Rate','Sgst Rate']]\n",
    "\n",
    "total = sales.assign(total_tax = sales['Tax Exclusive Gross'] + sales['Igst Rate'] + sales['Cgst Rate'] + sales['Sgst Rate'])\n",
    "\n",
    "total.loc[delhi_gujarat['Transaction Type'].isin(['Refund', 'Cancel']), 'Quantity'] = -1\n",
    "\n",
    "all_total = total.groupby(['Ship To State','Hsn/sac', 'Igst Rate']).agg({'Quantity': 'sum', 'Tax Exclusive Gross': 'sum', 'Igst Tax': 'sum','Cgst Rate':'sum','Sgst Rate':'sum', 'total_tax': 'sum'})\n",
    "\n",
    "result = all_total.reset_index()\n",
    "result\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}