PhonePe Project ETL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4e4b1f87",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import git\n",
    "import json\n",
    "import pandas as pd\n",
    "import requests\n",
    "import plotly.express as px\n",
    "import warnings\n",
    "warnings.filterwarnings(action = 'ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5bc05a79",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "fatal: destination path 'pulse' already exists and is not an empty directory.\n"
     ]
    }
   ],
   "source": [
    "! git clone https://github.com/PhonePe/pulse.git"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc653fed",
   "metadata": {},
   "source": [
    "# Aggregated Transcation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "650775dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "def Aggregated_Trans():\n",
    "    path = \"C:/Users/Logambal/Desktop/Phonepe-Project/pulse/data/aggregated/transaction/country/india/state/\"\n",
    "\n",
    "    agg_state_list = os.listdir(path)\n",
    "\n",
    "    data = {'State': [], 'Year': [], 'Quarter': [], 'Transaction_Type': [],'Transaction_Count': [], 'Transaction_Amount': []}\n",
    "\n",
    "    for i in agg_state_list:\n",
    "        path_i = path +i +'/'                \n",
    "        agg_year_list = os.listdir(path_i)      # 2018,2019,2020,2021,2022,2023\n",
    "\n",
    "        for j in agg_year_list:\n",
    "            path_j = path_i + j +'/'       \n",
    "            agg_year_json = os.listdir(path_j)  # 1.json,2.json,3.json,4.json\n",
    "\n",
    "            for k in agg_year_json:\n",
    "                path_k = path_j + k             # india/state/delhi/2018/1.json\n",
    "                f = open(path_k, 'r')\n",
    "                d = json.load(f)\n",
    "\n",
    "                for z in d['data']['transactionData']:\n",
    "                        name = z['name']\n",
    "                        count = z['paymentInstruments'][0]['count']\n",
    "                        amount = z['paymentInstruments'][0]['amount']\n",
    "\n",
    "                        data['State'].append(i)\n",
    "                        data['Year'].append(j)\n",
    "                        data['Quarter'].append('Q'+str(k[0]))\n",
    "                        data['Transaction_Type'].append(name)\n",
    "                        data['Transaction_Count'].append(count)\n",
    "                        data['Transaction_Amount'].append(amount)\n",
    "\n",
    "    return data\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "5954fea1",
   "metadata": {},
   "outputs": [
    {
     "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>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Transaction_Type</th>\n",
       "      <th>Transaction_Count</th>\n",
       "      <th>Transaction_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Recharge &amp; bill payments</td>\n",
       "      <td>4200</td>\n",
       "      <td>1.845307e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Peer-to-peer payments</td>\n",
       "      <td>1871</td>\n",
       "      <td>1.213866e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Merchant payments</td>\n",
       "      <td>298</td>\n",
       "      <td>4.525072e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>33</td>\n",
       "      <td>1.060142e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Others</td>\n",
       "      <td>256</td>\n",
       "      <td>1.846899e+05</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4129</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>Merchant payments</td>\n",
       "      <td>296965002</td>\n",
       "      <td>2.004404e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4130</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>Peer-to-peer payments</td>\n",
       "      <td>271150280</td>\n",
       "      <td>8.310501e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4131</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>Recharge &amp; bill payments</td>\n",
       "      <td>63055684</td>\n",
       "      <td>3.920985e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4132</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>389751</td>\n",
       "      <td>4.249856e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4133</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>Others</td>\n",
       "      <td>532474</td>\n",
       "      <td>4.232851e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4134 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          State  Year Quarter          Transaction_Type  \\\n",
       "0     andaman-&-nicobar-islands  2018      Q1  Recharge & bill payments   \n",
       "1     andaman-&-nicobar-islands  2018      Q1     Peer-to-peer payments   \n",
       "2     andaman-&-nicobar-islands  2018      Q1         Merchant payments   \n",
       "3     andaman-&-nicobar-islands  2018      Q1        Financial Services   \n",
       "4     andaman-&-nicobar-islands  2018      Q1                    Others   \n",
       "...                         ...   ...     ...                       ...   \n",
       "4129                west-bengal  2023      Q3         Merchant payments   \n",
       "4130                west-bengal  2023      Q3     Peer-to-peer payments   \n",
       "4131                west-bengal  2023      Q3  Recharge & bill payments   \n",
       "4132                west-bengal  2023      Q3        Financial Services   \n",
       "4133                west-bengal  2023      Q3                    Others   \n",
       "\n",
       "      Transaction_Count  Transaction_Amount  \n",
       "0                  4200        1.845307e+06  \n",
       "1                  1871        1.213866e+07  \n",
       "2                   298        4.525072e+05  \n",
       "3                    33        1.060142e+04  \n",
       "4                   256        1.846899e+05  \n",
       "...                 ...                 ...  \n",
       "4129          296965002        2.004404e+11  \n",
       "4130          271150280        8.310501e+11  \n",
       "4131           63055684        3.920985e+10  \n",
       "4132             389751        4.249856e+08  \n",
       "4133             532474        4.232851e+08  \n",
       "\n",
       "[4134 rows x 6 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.DataFrame(Aggregated_Trans())\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e49f1907",
   "metadata": {},
   "source": [
    "# Aggregated User"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0dcd0535",
   "metadata": {},
   "outputs": [],
   "source": [
    "def Aggregated_User():\n",
    "    path_1 = \"C:/Users/Logambal/Desktop/Phonepe-Project/pulse/data/aggregated/user/country/india/state/\"\n",
    "    agg_state_list = os.listdir(path_1)\n",
    "    data1 = {'State': [], 'Year': [], 'Quarter': [], 'User_Brand': [], 'User_Count': [], 'User_Percentage': []}\n",
    "    for i in agg_state_list:\n",
    "        path_i = path_1 + i + '/'\n",
    "        agg_year_list = os.listdir(path_i)\n",
    "\n",
    "        for j in agg_year_list:\n",
    "            path_j = path_i + j + '/'\n",
    "            agg_year_json = os.listdir(path_j)\n",
    "\n",
    "            for k in agg_year_json:\n",
    "                path_k = path_j + k\n",
    "                f = open(path_k, 'r')\n",
    "                d1 = json.load(f)\n",
    "                try:\n",
    "                    for z in d1['data']['usersByDevice']:\n",
    "                        brand = z['brand']\n",
    "                        count = z['count']\n",
    "                        percentage = z['percentage']*100\n",
    "\n",
    "                        data1['State'].append(i)\n",
    "                        data1['Year'].append(j)\n",
    "                        data1['Quarter'].append('Q'+str(k[0]))\n",
    "                        data1['User_Brand'].append(brand)\n",
    "                        data1['User_Count'].append(count)\n",
    "                        data1['User_Percentage'].append(percentage)\n",
    "                except:\n",
    "                    pass\n",
    "        return data1        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "005ec996",
   "metadata": {},
   "outputs": [
    {
     "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>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>User_Brand</th>\n",
       "      <th>User_Count</th>\n",
       "      <th>User_Percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Xiaomi</td>\n",
       "      <td>1665</td>\n",
       "      <td>24.703264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>1445</td>\n",
       "      <td>21.439169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Vivo</td>\n",
       "      <td>982</td>\n",
       "      <td>14.569733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Oppo</td>\n",
       "      <td>501</td>\n",
       "      <td>7.433234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>OnePlus</td>\n",
       "      <td>332</td>\n",
       "      <td>4.925816</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2022</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Tecno</td>\n",
       "      <td>1770</td>\n",
       "      <td>2.320155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2022</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Apple</td>\n",
       "      <td>1634</td>\n",
       "      <td>2.141883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2022</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Huawei</td>\n",
       "      <td>1441</td>\n",
       "      <td>1.888895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2022</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Motorola</td>\n",
       "      <td>1050</td>\n",
       "      <td>1.376363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2022</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Others</td>\n",
       "      <td>4558</td>\n",
       "      <td>5.974727</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>187 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         State  Year Quarter User_Brand  User_Count  \\\n",
       "0    andaman-&-nicobar-islands  2018      Q1     Xiaomi        1665   \n",
       "1    andaman-&-nicobar-islands  2018      Q1    Samsung        1445   \n",
       "2    andaman-&-nicobar-islands  2018      Q1       Vivo         982   \n",
       "3    andaman-&-nicobar-islands  2018      Q1       Oppo         501   \n",
       "4    andaman-&-nicobar-islands  2018      Q1    OnePlus         332   \n",
       "..                         ...   ...     ...        ...         ...   \n",
       "182  andaman-&-nicobar-islands  2022      Q1      Tecno        1770   \n",
       "183  andaman-&-nicobar-islands  2022      Q1      Apple        1634   \n",
       "184  andaman-&-nicobar-islands  2022      Q1     Huawei        1441   \n",
       "185  andaman-&-nicobar-islands  2022      Q1   Motorola        1050   \n",
       "186  andaman-&-nicobar-islands  2022      Q1     Others        4558   \n",
       "\n",
       "     User_Percentage  \n",
       "0          24.703264  \n",
       "1          21.439169  \n",
       "2          14.569733  \n",
       "3           7.433234  \n",
       "4           4.925816  \n",
       "..               ...  \n",
       "182         2.320155  \n",
       "183         2.141883  \n",
       "184         1.888895  \n",
       "185         1.376363  \n",
       "186         5.974727  \n",
       "\n",
       "[187 rows x 6 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1=pd.DataFrame(Aggregated_User())\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c21c5f3",
   "metadata": {},
   "source": [
    "# Map Transcation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a652cfa7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def Map_Trans():\n",
    "    path_2=\"C:/Users/Logambal/Desktop/Phonepe-Project/pulse/data/map/transaction/hover/country/india/state/\"\n",
    "    map_trans_list=os.listdir(path_2)\n",
    "    data2= {'State': [], 'Year': [], 'Quarter': [],'District_Name':[],'District_Count':[],'District_Amount':[]}\n",
    "    for i in map_trans_list:\n",
    "        path_i=path_2 + i +'/'\n",
    "        map_year_list=os.listdir(path_i)\n",
    "        for j in map_year_list:\n",
    "            path_j=path_i + j + '/'\n",
    "            map_json_list=os.listdir(path_j)\n",
    "            for k in map_json_list:\n",
    "                path_k=path_j + k\n",
    "                f = open(path_k, 'r')\n",
    "                d2 = json.load(f)\n",
    "                for z in d2['data']['hoverDataList']:\n",
    "                    name =z['name'].split(' district')[0]\n",
    "                    count=z['metric'][0]['count']\n",
    "                    amount=z['metric'][0]['amount']\n",
    "\n",
    "                    data2['State'].append(i)\n",
    "                    data2['Year'].append(j)\n",
    "                    data2['Quarter'].append('Q'+str(k[0]))\n",
    "                    data2['District_Name'].append(name)\n",
    "                    data2['District_Count'].append(count)\n",
    "                    data2['District_Amount'].append(amount)\n",
    "    return data2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d343cf7a",
   "metadata": {},
   "outputs": [
    {
     "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>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>District_Name</th>\n",
       "      <th>District_Count</th>\n",
       "      <th>District_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>442</td>\n",
       "      <td>9.316631e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>5688</td>\n",
       "      <td>1.256025e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>528</td>\n",
       "      <td>1.139849e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>825</td>\n",
       "      <td>1.317863e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>9395</td>\n",
       "      <td>2.394824e+07</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16827</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>nadia</td>\n",
       "      <td>30146530</td>\n",
       "      <td>5.787869e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16828</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>birbhum</td>\n",
       "      <td>17597047</td>\n",
       "      <td>3.090737e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16829</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>purba medinipur</td>\n",
       "      <td>34244893</td>\n",
       "      <td>6.813718e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16830</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>maldah</td>\n",
       "      <td>28539407</td>\n",
       "      <td>5.299766e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16831</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>darjiling</td>\n",
       "      <td>14747572</td>\n",
       "      <td>2.463195e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16832 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           State  Year Quarter             District_Name  \\\n",
       "0      andaman-&-nicobar-islands  2018      Q1  north and middle andaman   \n",
       "1      andaman-&-nicobar-islands  2018      Q1             south andaman   \n",
       "2      andaman-&-nicobar-islands  2018      Q1                  nicobars   \n",
       "3      andaman-&-nicobar-islands  2018      Q2  north and middle andaman   \n",
       "4      andaman-&-nicobar-islands  2018      Q2             south andaman   \n",
       "...                          ...   ...     ...                       ...   \n",
       "16827                west-bengal  2023      Q3                     nadia   \n",
       "16828                west-bengal  2023      Q3                   birbhum   \n",
       "16829                west-bengal  2023      Q3           purba medinipur   \n",
       "16830                west-bengal  2023      Q3                    maldah   \n",
       "16831                west-bengal  2023      Q3                 darjiling   \n",
       "\n",
       "       District_Count  District_Amount  \n",
       "0                 442     9.316631e+05  \n",
       "1                5688     1.256025e+07  \n",
       "2                 528     1.139849e+06  \n",
       "3                 825     1.317863e+06  \n",
       "4                9395     2.394824e+07  \n",
       "...               ...              ...  \n",
       "16827        30146530     5.787869e+10  \n",
       "16828        17597047     3.090737e+10  \n",
       "16829        34244893     6.813718e+10  \n",
       "16830        28539407     5.299766e+10  \n",
       "16831        14747572     2.463195e+10  \n",
       "\n",
       "[16832 rows x 6 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2=pd.DataFrame(Map_Trans())\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3802b351",
   "metadata": {},
   "source": [
    "# Map User"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a5c56414",
   "metadata": {},
   "outputs": [],
   "source": [
    "def Map_User():\n",
    "    path_3 = \"C:/Users/Logambal/Desktop/Phonepe-Project/pulse/data/map/user/hover/country/india/state/\"\n",
    "    map_user_list = os.listdir(path_3)\n",
    "\n",
    "    data3 = {'State': [], 'Year': [], 'Quarter': [], 'District_Name': [], 'Registered_User': [], 'App_Opens': []}\n",
    "\n",
    "    for i in map_user_list:\n",
    "        path_i = path_3 + i + '/'\n",
    "        map_year_list = os.listdir(path_i)\n",
    "\n",
    "        for j in map_year_list:\n",
    "            path_j = path_i + j + '/'\n",
    "            map_year_json = os.listdir(path_j)\n",
    "\n",
    "            for k in map_year_json:\n",
    "                path_k = path_j + k\n",
    "                f = open(path_k, 'r')\n",
    "                d3 = json.load(f)\n",
    "\n",
    "                for z_key, z_value in d3['data']['hoverData'].items():\n",
    "                    district = z_key.split(' district')[0]\n",
    "                    reg_user = z_value['registeredUsers']\n",
    "                    app_opens = z_value['appOpens']\n",
    "\n",
    "                    data3['State'].append(i)\n",
    "                    data3['Year'].append(j)\n",
    "                    data3['Quarter'].append('Q'+str(k[0]))\n",
    "                    data3['District_Name'].append(district)\n",
    "                    data3['Registered_User'].append(reg_user)\n",
    "                    data3['App_Opens'].append(app_opens)\n",
    "    return data3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1d219b5d",
   "metadata": {},
   "outputs": [
    {
     "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>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>District_Name</th>\n",
       "      <th>Registered_User</th>\n",
       "      <th>App_Opens</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>632</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>5846</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>262</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>911</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>8143</td>\n",
       "      <td>0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16831</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>nadia</td>\n",
       "      <td>1544294</td>\n",
       "      <td>12674328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16832</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>birbhum</td>\n",
       "      <td>974363</td>\n",
       "      <td>18961683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16833</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>purba medinipur</td>\n",
       "      <td>1530289</td>\n",
       "      <td>18593769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16834</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>maldah</td>\n",
       "      <td>1091634</td>\n",
       "      <td>28020285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16835</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>darjiling</td>\n",
       "      <td>637665</td>\n",
       "      <td>21732323</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16836 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           State  Year Quarter             District_Name  \\\n",
       "0      andaman-&-nicobar-islands  2018      Q1  north and middle andaman   \n",
       "1      andaman-&-nicobar-islands  2018      Q1             south andaman   \n",
       "2      andaman-&-nicobar-islands  2018      Q1                  nicobars   \n",
       "3      andaman-&-nicobar-islands  2018      Q2  north and middle andaman   \n",
       "4      andaman-&-nicobar-islands  2018      Q2             south andaman   \n",
       "...                          ...   ...     ...                       ...   \n",
       "16831                west-bengal  2023      Q3                     nadia   \n",
       "16832                west-bengal  2023      Q3                   birbhum   \n",
       "16833                west-bengal  2023      Q3           purba medinipur   \n",
       "16834                west-bengal  2023      Q3                    maldah   \n",
       "16835                west-bengal  2023      Q3                 darjiling   \n",
       "\n",
       "       Registered_User  App_Opens  \n",
       "0                  632          0  \n",
       "1                 5846          0  \n",
       "2                  262          0  \n",
       "3                  911          0  \n",
       "4                 8143          0  \n",
       "...                ...        ...  \n",
       "16831          1544294   12674328  \n",
       "16832           974363   18961683  \n",
       "16833          1530289   18593769  \n",
       "16834          1091634   28020285  \n",
       "16835           637665   21732323  \n",
       "\n",
       "[16836 rows x 6 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3=pd.DataFrame(Map_User())\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8792cbb",
   "metadata": {},
   "source": [
    "# Top Transcation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7e22e242",
   "metadata": {},
   "outputs": [],
   "source": [
    "def Top_Trans():\n",
    "    path_4=\"C:/Users/Logambal/Desktop/Phonepe-Project/pulse/data/top/transaction/country/india/state/\"\n",
    "    top_trans_list=os.listdir(path_4)\n",
    "\n",
    "    data4 = {'State': [], 'Year': [], 'Quarter': [], 'District_Name': [],'Transaction_Count': [], 'Transaction_Amount': []}\n",
    "    for i in top_trans_list:\n",
    "        path_i = path_4 + i + '/'\n",
    "        top_year_list=os.listdir(path_i)\n",
    "\n",
    "        for j in top_year_list:\n",
    "            path_j = path_i + j + '/'\n",
    "            top_json_list = os.listdir(path_j)\n",
    "\n",
    "            for k in top_json_list:\n",
    "                path_k = path_j + k\n",
    "                f = open(path_k,'r')\n",
    "                d4 = json.load(f)\n",
    "\n",
    "                for z in d4['data']['districts']:\n",
    "                    name = z['entityName']\n",
    "                    count = z['metric']['count']\n",
    "                    amount = z['metric']['amount']\n",
    "\n",
    "\n",
    "                    data4['State'].append(i)\n",
    "                    data4['Year'].append(j)\n",
    "                    data4['Quarter'].append('Q'+str(k[0]))\n",
    "                    data4['District_Name'].append(name)\n",
    "                    data4['Transaction_Count'].append(count)\n",
    "                    data4['Transaction_Amount'].append(amount)\n",
    "                    \n",
    "    return data4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b9ad679c",
   "metadata": {},
   "outputs": [
    {
     "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>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>District_Name</th>\n",
       "      <th>Transaction_Count</th>\n",
       "      <th>Transaction_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>5688</td>\n",
       "      <td>1.256025e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>528</td>\n",
       "      <td>1.139849e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>442</td>\n",
       "      <td>9.316631e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>9395</td>\n",
       "      <td>2.394824e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>1120</td>\n",
       "      <td>3.072437e+06</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6803</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>hooghly</td>\n",
       "      <td>32021392</td>\n",
       "      <td>5.624223e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6804</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>howrah</td>\n",
       "      <td>31734226</td>\n",
       "      <td>5.800237e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6805</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>paschim bardhaman</td>\n",
       "      <td>30585672</td>\n",
       "      <td>4.340349e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6806</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>paschim medinipur</td>\n",
       "      <td>30418455</td>\n",
       "      <td>5.043177e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6807</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>nadia</td>\n",
       "      <td>30146530</td>\n",
       "      <td>5.787869e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6808 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          State  Year Quarter             District_Name  \\\n",
       "0     andaman-&-nicobar-islands  2018      Q1             south andaman   \n",
       "1     andaman-&-nicobar-islands  2018      Q1                  nicobars   \n",
       "2     andaman-&-nicobar-islands  2018      Q1  north and middle andaman   \n",
       "3     andaman-&-nicobar-islands  2018      Q2             south andaman   \n",
       "4     andaman-&-nicobar-islands  2018      Q2                  nicobars   \n",
       "...                         ...   ...     ...                       ...   \n",
       "6803                west-bengal  2023      Q3                   hooghly   \n",
       "6804                west-bengal  2023      Q3                    howrah   \n",
       "6805                west-bengal  2023      Q3         paschim bardhaman   \n",
       "6806                west-bengal  2023      Q3         paschim medinipur   \n",
       "6807                west-bengal  2023      Q3                     nadia   \n",
       "\n",
       "      Transaction_Count  Transaction_Amount  \n",
       "0                  5688        1.256025e+07  \n",
       "1                   528        1.139849e+06  \n",
       "2                   442        9.316631e+05  \n",
       "3                  9395        2.394824e+07  \n",
       "4                  1120        3.072437e+06  \n",
       "...                 ...                 ...  \n",
       "6803           32021392        5.624223e+10  \n",
       "6804           31734226        5.800237e+10  \n",
       "6805           30585672        4.340349e+10  \n",
       "6806           30418455        5.043177e+10  \n",
       "6807           30146530        5.787869e+10  \n",
       "\n",
       "[6808 rows x 6 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4 = pd.DataFrame(Top_Trans())\n",
    "df4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3cfe646",
   "metadata": {},
   "source": [
    "# Top User"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "ecb4b691",
   "metadata": {},
   "outputs": [],
   "source": [
    "def Top_User():\n",
    "    path_5 = \"C:/Users/Logambal/Desktop/Phonepe-Project/pulse/data/top/user/country/india/state/\"\n",
    "    top_user_list = os.listdir(path_5)\n",
    "\n",
    "    data5 = {'State': [], 'Year': [], 'Quarter': [],'District_Name': [],'Registered_Users': []}\n",
    "\n",
    "    for i in top_user_list:\n",
    "        path_i = path_5 + i + '/'\n",
    "        top_year_list = os.listdir(path_i)\n",
    "\n",
    "        for j in top_year_list:\n",
    "            path_j = path_i + j + '/'\n",
    "            top_json_list = os.listdir(path_j)\n",
    "\n",
    "            for k in top_json_list:\n",
    "                path_k = path_j + k\n",
    "                f = open(path_k,'r')\n",
    "                d5 = json.load(f)\n",
    "\n",
    "                for z in d5['data']['districts']:\n",
    "                    name = z['name']\n",
    "                    user = z['registeredUsers']\n",
    "\n",
    "\n",
    "                    data5['State'].append(i)\n",
    "                    data5['Year'].append(j)\n",
    "                    data5['Quarter'].append('Q' + str(k[0]))\n",
    "                    data5['District_Name'].append(name)\n",
    "                    data5['Registered_Users'].append(user)\n",
    "                    \n",
    "    return data5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "9483b3c1",
   "metadata": {},
   "outputs": [
    {
     "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>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>District_Name</th>\n",
       "      <th>Registered_Users</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>5846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>8143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6803</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>howrah</td>\n",
       "      <td>1608895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6804</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>nadia</td>\n",
       "      <td>1544294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6805</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>purba medinipur</td>\n",
       "      <td>1530289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6806</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>paschim medinipur</td>\n",
       "      <td>1384076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6807</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>purba bardhaman</td>\n",
       "      <td>1272153</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6808 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          State  Year Quarter             District_Name  \\\n",
       "0     andaman-&-nicobar-islands  2018      Q1             south andaman   \n",
       "1     andaman-&-nicobar-islands  2018      Q1  north and middle andaman   \n",
       "2     andaman-&-nicobar-islands  2018      Q1                  nicobars   \n",
       "3     andaman-&-nicobar-islands  2018      Q2             south andaman   \n",
       "4     andaman-&-nicobar-islands  2018      Q2  north and middle andaman   \n",
       "...                         ...   ...     ...                       ...   \n",
       "6803                west-bengal  2023      Q3                    howrah   \n",
       "6804                west-bengal  2023      Q3                     nadia   \n",
       "6805                west-bengal  2023      Q3           purba medinipur   \n",
       "6806                west-bengal  2023      Q3         paschim medinipur   \n",
       "6807                west-bengal  2023      Q3           purba bardhaman   \n",
       "\n",
       "      Registered_Users  \n",
       "0                 5846  \n",
       "1                  632  \n",
       "2                  262  \n",
       "3                 8143  \n",
       "4                  911  \n",
       "...                ...  \n",
       "6803           1608895  \n",
       "6804           1544294  \n",
       "6805           1530289  \n",
       "6806           1384076  \n",
       "6807           1272153  \n",
       "\n",
       "[6808 rows x 5 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5 = pd.DataFrame(Top_User())\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "1ef90623",
   "metadata": {},
   "outputs": [],
   "source": [
    "mapping = {\n",
    "    'andaman-&-nicobar-islands': 'Andaman & Nicobar',\n",
    "    'andhra-pradesh': 'Andhra Pradesh',\n",
    "    'arunachal-pradesh': 'Arunachal Pradesh',\n",
    "    'assam': 'Assam',\n",
    "    'bihar': 'Bihar',\n",
    "    'chandigarh': 'Chandigarh',\n",
    "    'chhattisgarh': 'Chhattisgarh',\n",
    "    'dadra-&-nagar-haveli-&-daman-&-diu': 'Dadra and Nagar Haveli and Daman and Diu',\n",
    "    'delhi': 'Delhi',\n",
    "    'goa': 'Goa',\n",
    "    'gujarat': 'Gujarat',\n",
    "    'haryana': 'Haryana',\n",
    "    'himachal-pradesh': 'Himachal Pradesh',\n",
    "    'jammu-&-kashmir': 'Jammu & Kashmir',\n",
    "    'jharkhand': 'Jharkhand',\n",
    "    'karnataka': 'Karnataka',\n",
    "    'kerala': 'Kerala',\n",
    "    'lakshadweep': 'Lakshadweep',\n",
    "    'ladakh':'Ladakh',\n",
    "    'madhya-pradesh': 'Madhya Pradesh',\n",
    "    'maharashtra': 'Maharashtra',\n",
    "    'manipur': 'Manipur',\n",
    "    'meghalaya': 'Meghalaya',\n",
    "    'mizoram': 'Mizoram',\n",
    "    'nagaland': 'Nagaland',\n",
    "    'odisha': 'Odisha',\n",
    "    'puducherry': 'Puducherry',\n",
    "    'punjab': 'Punjab',\n",
    "    'rajasthan': 'Rajasthan',\n",
    "    'sikkim': 'Sikkim',\n",
    "    'tamil-nadu': 'Tamil Nadu',\n",
    "    'telangana': 'Telangana',\n",
    "    'tripura': 'Tripura',\n",
    "    'uttar-pradesh': 'Uttar Pradesh',\n",
    "    'uttarakhand': 'Uttarakhand',\n",
    "    'west-bengal': 'West Bengal'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "b39f19c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def States_India(df, mapping):\n",
    "    df['State'] = df['State'].map(mapping)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "96ce3f1b",
   "metadata": {},
   "outputs": [
    {
     "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>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>District_Name</th>\n",
       "      <th>Registered_Users</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>5846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>8143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q2</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>13600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q2</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>2617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>83305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>14216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2023</td>\n",
       "      <td>Q3</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>2734</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>69 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                State  Year Quarter             District_Name  \\\n",
       "0   Andaman & Nicobar  2018      Q1             south andaman   \n",
       "1   Andaman & Nicobar  2018      Q1  north and middle andaman   \n",
       "2   Andaman & Nicobar  2018      Q1                  nicobars   \n",
       "3   Andaman & Nicobar  2018      Q2             south andaman   \n",
       "4   Andaman & Nicobar  2018      Q2  north and middle andaman   \n",
       "..                ...   ...     ...                       ...   \n",
       "64  Andaman & Nicobar  2023      Q2  north and middle andaman   \n",
       "65  Andaman & Nicobar  2023      Q2                  nicobars   \n",
       "66  Andaman & Nicobar  2023      Q3             south andaman   \n",
       "67  Andaman & Nicobar  2023      Q3  north and middle andaman   \n",
       "68  Andaman & Nicobar  2023      Q3                  nicobars   \n",
       "\n",
       "    Registered_Users  \n",
       "0               5846  \n",
       "1                632  \n",
       "2                262  \n",
       "3               8143  \n",
       "4                911  \n",
       "..               ...  \n",
       "64             13600  \n",
       "65              2617  \n",
       "66             83305  \n",
       "67             14216  \n",
       "68              2734  \n",
       "\n",
       "[69 rows x 5 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "States_India(df, mapping)\n",
    "States_India(df1, mapping)\n",
    "States_India(df2, mapping)\n",
    "States_India(df3, mapping)\n",
    "States_India(df4, mapping)\n",
    "States_India(df5, mapping)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d93e281e",
   "metadata": {},
   "source": [
    "# EDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "92daa763",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No.of rows and Columns in df: (4134, 6)\n",
      "No.of rows and Columns in df1: (187, 6)\n",
      "No.of rows and Columns in df2: (16832, 6)\n",
      "No.of rows and Columns in df3: (16836, 6)\n",
      "No.of rows and Columns in df4: (6808, 6)\n",
      "No.of rows and Columns in df5: (6808, 5)\n"
     ]
    }
   ],
   "source": [
    "print(\"No.of rows and Columns in df:\",df.shape)\n",
    "print(\"No.of rows and Columns in df1:\",df1.shape)\n",
    "print(\"No.of rows and Columns in df2:\",df2.shape)\n",
    "print(\"No.of rows and Columns in df3:\",df3.shape)\n",
    "print(\"No.of rows and Columns in df4:\",df4.shape)\n",
    "print(\"No.of rows and Columns in df5:\",df5.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "c19f2281",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Information of df:\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4134 entries, 0 to 4133\n",
      "Data columns (total 6 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   State               4134 non-null   object \n",
      " 1   Year                4134 non-null   object \n",
      " 2   Quarter             4134 non-null   object \n",
      " 3   Transaction_Type    4134 non-null   object \n",
      " 4   Transaction_Count   4134 non-null   int64  \n",
      " 5   Transaction_Amount  4134 non-null   float64\n",
      "dtypes: float64(1), int64(1), object(4)\n",
      "memory usage: 193.9+ KB\n",
      "----------------------------------------------------------------\n",
      "Information of df1:\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 187 entries, 0 to 186\n",
      "Data columns (total 6 columns):\n",
      " #   Column           Non-Null Count  Dtype  \n",
      "---  ------           --------------  -----  \n",
      " 0   State            187 non-null    object \n",
      " 1   Year             187 non-null    object \n",
      " 2   Quarter          187 non-null    object \n",
      " 3   User_Brand       187 non-null    object \n",
      " 4   User_Count       187 non-null    int64  \n",
      " 5   User_Percentage  187 non-null    float64\n",
      "dtypes: float64(1), int64(1), object(4)\n",
      "memory usage: 8.9+ KB\n",
      "----------------------------------------------------------------\n",
      "Information of df2:\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 16832 entries, 0 to 16831\n",
      "Data columns (total 6 columns):\n",
      " #   Column           Non-Null Count  Dtype  \n",
      "---  ------           --------------  -----  \n",
      " 0   State            16832 non-null  object \n",
      " 1   Year             16832 non-null  object \n",
      " 2   Quarter          16832 non-null  object \n",
      " 3   District_Name    16832 non-null  object \n",
      " 4   District_Count   16832 non-null  int64  \n",
      " 5   District_Amount  16832 non-null  float64\n",
      "dtypes: float64(1), int64(1), object(4)\n",
      "memory usage: 789.1+ KB\n",
      "----------------------------------------------------------------\n",
      "Information of df3:\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 16836 entries, 0 to 16835\n",
      "Data columns (total 6 columns):\n",
      " #   Column           Non-Null Count  Dtype \n",
      "---  ------           --------------  ----- \n",
      " 0   State            16836 non-null  object\n",
      " 1   Year             16836 non-null  object\n",
      " 2   Quarter          16836 non-null  object\n",
      " 3   District_Name    16836 non-null  object\n",
      " 4   Registered_User  16836 non-null  int64 \n",
      " 5   App_Opens        16836 non-null  int64 \n",
      "dtypes: int64(2), object(4)\n",
      "memory usage: 789.3+ KB\n",
      "----------------------------------------------------------------\n",
      "Information of df4:\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6808 entries, 0 to 6807\n",
      "Data columns (total 6 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   State               6808 non-null   object \n",
      " 1   Year                6808 non-null   object \n",
      " 2   Quarter             6808 non-null   object \n",
      " 3   District_Name       6808 non-null   object \n",
      " 4   Transaction_Count   6808 non-null   int64  \n",
      " 5   Transaction_Amount  6808 non-null   float64\n",
      "dtypes: float64(1), int64(1), object(4)\n",
      "memory usage: 319.3+ KB\n",
      "----------------------------------------------------------------\n",
      "Information of df5:\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6808 entries, 0 to 6807\n",
      "Data columns (total 5 columns):\n",
      " #   Column            Non-Null Count  Dtype \n",
      "---  ------            --------------  ----- \n",
      " 0   State             6808 non-null   object\n",
      " 1   Year              6808 non-null   object\n",
      " 2   Quarter           6808 non-null   object\n",
      " 3   District_Name     6808 non-null   object\n",
      " 4   Registered_Users  6808 non-null   int64 \n",
      "dtypes: int64(1), object(4)\n",
      "memory usage: 266.1+ KB\n"
     ]
    }
   ],
   "source": [
    "print(\"Information of df:\")\n",
    "df.info()\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Information of df1:\")\n",
    "df1.info()\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Information of df2:\")\n",
    "df2.info()\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Information of df3:\")\n",
    "df3.info()\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Information of df4:\")\n",
    "df4.info()\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Information of df5:\")\n",
    "df5.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "8690c317",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Checking The Null Values(df):\n",
      "State                 0\n",
      "Year                  0\n",
      "Quarter               0\n",
      "Transaction_Type      0\n",
      "Transaction_Count     0\n",
      "Transaction_Amount    0\n",
      "dtype: int64\n",
      "----------------------------------------------------------------\n",
      "Checking The Null Values(df1):\n",
      "State              0\n",
      "Year               0\n",
      "Quarter            0\n",
      "User_Brand         0\n",
      "User_Count         0\n",
      "User_Percentage    0\n",
      "dtype: int64\n",
      "----------------------------------------------------------------\n",
      "Checking The Null Values(df2):\n",
      "State              0\n",
      "Year               0\n",
      "Quarter            0\n",
      "District_Name      0\n",
      "District_Count     0\n",
      "District_Amount    0\n",
      "dtype: int64\n",
      "----------------------------------------------------------------\n",
      "Checking The Null Values(df3):\n",
      "State              0\n",
      "Year               0\n",
      "Quarter            0\n",
      "District_Name      0\n",
      "Registered_User    0\n",
      "App_Opens          0\n",
      "dtype: int64\n",
      "----------------------------------------------------------------\n",
      "Checking The Null Values(df4):\n",
      "State                 0\n",
      "Year                  0\n",
      "Quarter               0\n",
      "District_Name         0\n",
      "Transaction_Count     0\n",
      "Transaction_Amount    0\n",
      "dtype: int64\n",
      "----------------------------------------------------------------\n",
      "Checking The Null Values(df5):\n",
      "State                 0\n",
      "Year                  0\n",
      "Quarter               0\n",
      "Transaction_Type      0\n",
      "Transaction_Count     0\n",
      "Transaction_Amount    0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(\"Checking The Null Values(df):\")\n",
    "print(df.isnull().sum())\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Checking The Null Values(df1):\")\n",
    "print(df1.isnull().sum())\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Checking The Null Values(df2):\")\n",
    "print(df2.isnull().sum())\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Checking The Null Values(df3):\")\n",
    "print(df3.isnull().sum())\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Checking The Null Values(df4):\")\n",
    "print(df4.isnull().sum())\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Checking The Null Values(df5):\")\n",
    "print(df.isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "8cb5cb71",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Checking The Duplicates Values(df): 0\n",
      "Checking The Duplicates Values(df1): 0\n",
      "Checking The Duplicates Values(df2): 0\n",
      "Checking The Duplicates Values(df3): 0\n",
      "Checking The Duplicates Values(df4): 0\n",
      "Checking The Duplicates Values(df5): 0\n"
     ]
    }
   ],
   "source": [
    "print(\"Checking The Duplicates Values(df):\" , df.duplicated().sum())\n",
    "print(\"Checking The Duplicates Values(df1):\",df1.duplicated().sum())\n",
    "print(\"Checking The Duplicates Values(df2):\",df2.duplicated().sum())\n",
    "print(\"Checking The Duplicates Values(df3):\",df3.duplicated().sum())\n",
    "print(\"Checking The Duplicates Values(df4):\",df4.duplicated().sum())\n",
    "print(\"Checking The Duplicates Values(df5):\",df5.duplicated().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "6e646061",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Checking Unique No.Of Values in each Columns(df):\n",
      "State                   36\n",
      "Year                     6\n",
      "Quarter                  4\n",
      "Transaction_Type         5\n",
      "Transaction_Count     4079\n",
      "Transaction_Amount    4134\n",
      "dtype: int64\n",
      "----------------------------------------------------------------\n",
      "Checking Unique No.Of Values in each Columns(df1):\n",
      "State                1\n",
      "Year                 5\n",
      "Quarter              4\n",
      "User_Brand          12\n",
      "User_Count         183\n",
      "User_Percentage    186\n",
      "dtype: int64\n",
      "----------------------------------------------------------------\n",
      "Checking Unique No.Of Values in each Columns(df2):\n",
      "State                 36\n",
      "Year                   6\n",
      "Quarter                4\n",
      "District_Name        723\n",
      "District_Count     16756\n",
      "District_Amount    16832\n",
      "dtype: int64\n",
      "----------------------------------------------------------------\n",
      "Checking Unique No.Of Values in each Columns(df3):\n",
      "State                 36\n",
      "Year                   6\n",
      "Quarter                4\n",
      "District_Name        723\n",
      "Registered_User    16484\n",
      "App_Opens          13172\n",
      "dtype: int64\n",
      "----------------------------------------------------------------\n",
      "Checking Unique No.Of Values in each Columns(df4):\n",
      "State                   36\n",
      "Year                     6\n",
      "Quarter                  4\n",
      "District_Name          371\n",
      "Transaction_Count     6798\n",
      "Transaction_Amount    6808\n",
      "dtype: int64\n",
      "----------------------------------------------------------------\n",
      "Checking Unique No.Of Values in each Columns(df5):\n",
      "State                 36\n",
      "Year                   6\n",
      "Quarter                4\n",
      "District_Name        309\n",
      "Registered_Users    6753\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(\"Checking Unique No.Of Values in each Columns(df):\")\n",
    "print(df.nunique())\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Checking Unique No.Of Values in each Columns(df1):\")\n",
    "print(df1.nunique())\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Checking Unique No.Of Values in each Columns(df2):\")\n",
    "print(df2.nunique())\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Checking Unique No.Of Values in each Columns(df3):\")\n",
    "print(df3.nunique())\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Checking Unique No.Of Values in each Columns(df4):\")\n",
    "print(df4.nunique())\n",
    "print(\"----------------------------------------------------------------\")\n",
    "print(\"Checking Unique No.Of Values in each Columns(df5):\")\n",
    "print(df5.nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5319cad",
   "metadata": {},
   "source": [
    "# Pushing To SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "c269e055",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "038ff9da",
   "metadata": {},
   "outputs": [],
   "source": [
    "myconnection = pymysql.connect(host = '127.0.0.1',user='root',passwd='Logi@2908')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7d271d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur = myconnection.cursor()\n",
    "cur.execute(\"create database Phonepe\")\n",
    "myconnection = pymysql.connect(host = '127.0.0.1',user='root',passwd='Logi@2908',database = \"Phonepe\")\n",
    "cur = myconnection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "346926e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"USE Phonepe\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c044fba",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"CREATE TABLE Aggregated_Transaction (State VARCHAR(255), Year INT, Quarter varchar(255), Transaction_type VARCHAR(255), Transaction_count INT, Transaction_amount FLOAT)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1abbe45",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"insert into Aggregated_Transaction (State,Year,Quarter,Transaction_type,Transaction_count,Transaction_amount) values (%s,%s,%s,%s,%s,%s)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52889e4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(0,len(df)):\n",
    "    cur.execute(sql,tuple(df.iloc[i]))\n",
    "    myconnection.commit()         "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "1c4916e3",
   "metadata": {},
   "outputs": [
    {
     "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>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>User_Brand</th>\n",
       "      <th>User_Count</th>\n",
       "      <th>User_Percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Xiaomi</td>\n",
       "      <td>1665</td>\n",
       "      <td>24.703264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>1445</td>\n",
       "      <td>21.439169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Vivo</td>\n",
       "      <td>982</td>\n",
       "      <td>14.569733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>Oppo</td>\n",
       "      <td>501</td>\n",
       "      <td>7.433234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>OnePlus</td>\n",
       "      <td>332</td>\n",
       "      <td>4.925816</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               State  Year Quarter User_Brand  User_Count  User_Percentage\n",
       "0  Andaman & Nicobar  2018      Q1     Xiaomi        1665        24.703264\n",
       "1  Andaman & Nicobar  2018      Q1    Samsung        1445        21.439169\n",
       "2  Andaman & Nicobar  2018      Q1       Vivo         982        14.569733\n",
       "3  Andaman & Nicobar  2018      Q1       Oppo         501         7.433234\n",
       "4  Andaman & Nicobar  2018      Q1    OnePlus         332         4.925816"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51343ee5",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"CREATE TABLE Aggregated_User(State VARCHAR(255), Year INT, Quarter varchar(255), User_brand VARCHAR(255), User_count INT, User_percentage FLOAT)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b68c4078",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"insert into Aggregated_User(State,Year,Quarter,User_brand,User_count,User_percentage) values (%s,%s,%s,%s,%s,%s)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef10712a",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(0,len(df1)):\n",
    "    cur.execute(sql,tuple(df1.iloc[i]))\n",
    "    myconnection.commit()         "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "ff8fc4ec",
   "metadata": {},
   "outputs": [
    {
     "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>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>District_Name</th>\n",
       "      <th>District_Count</th>\n",
       "      <th>District_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>442</td>\n",
       "      <td>9.316631e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>5688</td>\n",
       "      <td>1.256025e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>528</td>\n",
       "      <td>1.139849e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>825</td>\n",
       "      <td>1.317863e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>9395</td>\n",
       "      <td>2.394824e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               State  Year Quarter             District_Name  District_Count  \\\n",
       "0  Andaman & Nicobar  2018      Q1  north and middle andaman             442   \n",
       "1  Andaman & Nicobar  2018      Q1             south andaman            5688   \n",
       "2  Andaman & Nicobar  2018      Q1                  nicobars             528   \n",
       "3  Andaman & Nicobar  2018      Q2  north and middle andaman             825   \n",
       "4  Andaman & Nicobar  2018      Q2             south andaman            9395   \n",
       "\n",
       "   District_Amount  \n",
       "0     9.316631e+05  \n",
       "1     1.256025e+07  \n",
       "2     1.139849e+06  \n",
       "3     1.317863e+06  \n",
       "4     2.394824e+07  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe1c9332",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"CREATE TABLE Map_Transaction(State VARCHAR(255), Year INT, Quarter varchar(255), District_name VARCHAR(255), District_count INT, District_amount FLOAT)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00895c1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"insert into Map_Transaction(State,Year,Quarter,District_name,District_count,District_amount) values (%s,%s,%s,%s,%s,%s)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4fbcb03",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(0,len(df2)):\n",
    "    cur.execute(sql,tuple(df2.iloc[i]))\n",
    "    myconnection.commit()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "550bfd55",
   "metadata": {},
   "outputs": [
    {
     "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>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>District_Name</th>\n",
       "      <th>Registered_User</th>\n",
       "      <th>App_Opens</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>632</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>5846</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>262</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>911</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>8143</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               State  Year Quarter             District_Name  Registered_User  \\\n",
       "0  Andaman & Nicobar  2018      Q1  north and middle andaman              632   \n",
       "1  Andaman & Nicobar  2018      Q1             south andaman             5846   \n",
       "2  Andaman & Nicobar  2018      Q1                  nicobars              262   \n",
       "3  Andaman & Nicobar  2018      Q2  north and middle andaman              911   \n",
       "4  Andaman & Nicobar  2018      Q2             south andaman             8143   \n",
       "\n",
       "   App_Opens  \n",
       "0          0  \n",
       "1          0  \n",
       "2          0  \n",
       "3          0  \n",
       "4          0  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a297c8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"CREATE TABLE Map_User(State VARCHAR(255), Year INT, Quarter varchar(255), District_name VARCHAR(255), Registered_User INT, App_Opens FLOAT)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56597f2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"insert into Map_User(State,Year,Quarter,District_name,Registered_User,App_Opens) values (%s,%s,%s,%s,%s,%s)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ad4a500",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(0,len(df3)):\n",
    "    cur.execute(sql,tuple(df3.iloc[i]))\n",
    "    myconnection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "a8b8308a",
   "metadata": {},
   "outputs": [
    {
     "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>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>District_Name</th>\n",
       "      <th>Transaction_Count</th>\n",
       "      <th>Transaction_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>5688</td>\n",
       "      <td>1.256025e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>528</td>\n",
       "      <td>1.139849e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>442</td>\n",
       "      <td>9.316631e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>9395</td>\n",
       "      <td>2.394824e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>1120</td>\n",
       "      <td>3.072437e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               State  Year Quarter             District_Name  \\\n",
       "0  Andaman & Nicobar  2018      Q1             south andaman   \n",
       "1  Andaman & Nicobar  2018      Q1                  nicobars   \n",
       "2  Andaman & Nicobar  2018      Q1  north and middle andaman   \n",
       "3  Andaman & Nicobar  2018      Q2             south andaman   \n",
       "4  Andaman & Nicobar  2018      Q2                  nicobars   \n",
       "\n",
       "   Transaction_Count  Transaction_Amount  \n",
       "0               5688        1.256025e+07  \n",
       "1                528        1.139849e+06  \n",
       "2                442        9.316631e+05  \n",
       "3               9395        2.394824e+07  \n",
       "4               1120        3.072437e+06  "
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1802f926",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"CREATE TABLE Top_Transaction(State VARCHAR(255), Year INT, Quarter varchar(255), District_name VARCHAR(255),Transaction_count INT, Transaction_amount FLOAT)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf9906ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"insert into Top_Transaction(State,Year,Quarter,District_name,Transaction_count,Transaction_amount) values (%s,%s,%s,%s,%s,%s)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a703166",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(0,len(df4)):\n",
    "    cur.execute(sql,tuple(df4.iloc[i]))\n",
    "    myconnection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "d0fd32d9",
   "metadata": {},
   "outputs": [
    {
     "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>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>District_Name</th>\n",
       "      <th>Registered_Users</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>5846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q1</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>8143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>Q2</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>911</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State  Year Quarter             District_Name  \\\n",
       "0  andaman-&-nicobar-islands  2018      Q1             south andaman   \n",
       "1  andaman-&-nicobar-islands  2018      Q1  north and middle andaman   \n",
       "2  andaman-&-nicobar-islands  2018      Q1                  nicobars   \n",
       "3  andaman-&-nicobar-islands  2018      Q2             south andaman   \n",
       "4  andaman-&-nicobar-islands  2018      Q2  north and middle andaman   \n",
       "\n",
       "   Registered_Users  \n",
       "0              5846  \n",
       "1               632  \n",
       "2               262  \n",
       "3              8143  \n",
       "4               911  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8803dd12",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"CREATE TABLE Top_User(State VARCHAR(255), Year INT, Quarter varchar(255),District_Name VARCHAR(255),Registered_Users INT)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bceed8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"insert into Top_User(State,Year,Quarter,District_Name,Registered_Users) values (%s,%s,%s,%s,%s)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47f7eb8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(0,len(df5)):\n",
    "    cur.execute(sql,tuple(df5.iloc[i]))\n",
    "    myconnection.commit()"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Raw Cell Format",
  "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.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}