Data_Extraction_and_Transformation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "30d9505d-9f30-4548-9b78-d7d26f478471",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "494fcf44-d27f-4730-9ed4-c6cb6ba47eb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import mysql.connector as sql\n",
    "import streamlit as st\n",
    "import plotly.express as px\n",
    "import os\n",
    "import json\n",
    "from streamlit_option_menu import option_menu\n",
    "from PIL import Image"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd4024de-1484-4644-96fc-a1cf64c18b34",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Data Extraction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef175749-a6b6-4b34-afeb-676399d9ac09",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Cloning Data from Github"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "783828f1-8e7e-4920-9e28-b7151d84e15e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# os.environ[\"GIT_PYTHON_REFRESH\"] = \"quiet\"\n",
    "# import git\n",
    "# from git.repo.base import Repo\n",
    "# Repo.clone_from(\"https://github.com/PhonePe/pulse.git\", \"C:/Users/SANTHOSH/Project_3_PhonepePulse/data\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ee691d3-ea1c-44cb-ac09-e4fce52834e5",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Data transformation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e38c410-a76d-4c36-af8f-e4a2b506ea08",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Dataframe of aggregated Transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ee04b44-e217-4a7d-87a0-b9c6e260c6ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "path1 = \"C:/Users/Project_3_PhonepePulse/Phonepe_data/data/aggregated/transaction/country/india/state/\"\n",
    "agg_trans_list = os.listdir(path1)\n",
    "\n",
    "columns1 = {'State': [], 'Year': [], 'Quarter': [], 'Transaction_type': [], 'Transaction_count': [],\n",
    "            'Transaction_amount': []}\n",
    "for state in agg_trans_list:\n",
    "    cur_state = path1 + state + \"/\"\n",
    "    agg_year_list = os.listdir(cur_state)\n",
    "    \n",
    "    for year in agg_year_list:\n",
    "        cur_year = cur_state + year + \"/\"\n",
    "        agg_file_list = os.listdir(cur_year)\n",
    "        \n",
    "        for file in agg_file_list:\n",
    "            cur_file = cur_year + file\n",
    "            data = open(cur_file, 'r')\n",
    "            A = json.load(data)\n",
    "            \n",
    "            for i in A['data']['transactionData']:\n",
    "                name = i['name']\n",
    "                count = i['paymentInstruments'][0]['count']\n",
    "                amount = i['paymentInstruments'][0]['amount']\n",
    "                columns1['Transaction_type'].append(name)\n",
    "                columns1['Transaction_count'].append(count)\n",
    "                columns1['Transaction_amount'].append(amount)\n",
    "                columns1['State'].append(state)\n",
    "                columns1['Year'].append(year)\n",
    "                columns1['Quarter'].append(int(file.strip('.json')))\n",
    "                \n",
    "df_agg_trans = pd.DataFrame(columns1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6911ce30-3f41-4dc0-b2ef-1e8cab0bf459",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3594, 6)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_agg_trans.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fbf49bb-36b8-4fe4-adbc-fa3d58656020",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Dataframe of aggregated user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7fd40f2d-b882-4473-8cec-a2be159c6baa",
   "metadata": {},
   "outputs": [],
   "source": [
    "path2 = \"C:/Users/Project_3_PhonepePulse/Phonepe_data/data/aggregated/user/country/india/state/\"\n",
    "\n",
    "agg_user_list = os.listdir(path2)\n",
    "\n",
    "columns2 = {'State': [], 'Year': [], 'Quarter': [], 'Brands': [], 'Count': [],\n",
    "            'Percentage': []}\n",
    "for state in agg_user_list:\n",
    "    cur_state = path2 + state + \"/\"\n",
    "    agg_year_list = os.listdir(cur_state)\n",
    "    \n",
    "    for year in agg_year_list:\n",
    "        cur_year = cur_state + year + \"/\"\n",
    "        agg_file_list = os.listdir(cur_year)\n",
    "\n",
    "        for file in agg_file_list:\n",
    "            cur_file = cur_year + file\n",
    "            data = open(cur_file, 'r')\n",
    "            B = json.load(data)\n",
    "            try:\n",
    "                for i in B[\"data\"][\"usersByDevice\"]:\n",
    "                    brand_name = i[\"brand\"]\n",
    "                    counts = i[\"count\"]\n",
    "                    percents = i[\"percentage\"]\n",
    "                    columns2[\"Brands\"].append(brand_name)\n",
    "                    columns2[\"Count\"].append(counts)\n",
    "                    columns2[\"Percentage\"].append(percents)\n",
    "                    columns2[\"State\"].append(state)\n",
    "                    columns2[\"Year\"].append(year)\n",
    "                    columns2[\"Quarter\"].append(int(file.strip('.json')))\n",
    "            except:\n",
    "                pass\n",
    "df_agg_user = pd.DataFrame(columns2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3d30a5f6-5a1f-4484-8908-b9de329ab2ec",
   "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>Brands</th>\n",
       "      <th>Count</th>\n",
       "      <th>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>1</td>\n",
       "      <td>Xiaomi</td>\n",
       "      <td>1665</td>\n",
       "      <td>0.247033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>1445</td>\n",
       "      <td>0.214392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Vivo</td>\n",
       "      <td>982</td>\n",
       "      <td>0.145697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Oppo</td>\n",
       "      <td>501</td>\n",
       "      <td>0.074332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>OnePlus</td>\n",
       "      <td>332</td>\n",
       "      <td>0.049258</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>6727</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>1</td>\n",
       "      <td>Lenovo</td>\n",
       "      <td>330017</td>\n",
       "      <td>0.015056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6728</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>1</td>\n",
       "      <td>Infinix</td>\n",
       "      <td>284678</td>\n",
       "      <td>0.012987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6729</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>1</td>\n",
       "      <td>Asus</td>\n",
       "      <td>280347</td>\n",
       "      <td>0.012790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6730</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>1</td>\n",
       "      <td>Apple</td>\n",
       "      <td>277752</td>\n",
       "      <td>0.012671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6731</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>1</td>\n",
       "      <td>Others</td>\n",
       "      <td>2196334</td>\n",
       "      <td>0.100199</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6732 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          State  Year  Quarter   Brands    Count  Percentage\n",
       "0     andaman-&-nicobar-islands  2018        1   Xiaomi     1665    0.247033\n",
       "1     andaman-&-nicobar-islands  2018        1  Samsung     1445    0.214392\n",
       "2     andaman-&-nicobar-islands  2018        1     Vivo      982    0.145697\n",
       "3     andaman-&-nicobar-islands  2018        1     Oppo      501    0.074332\n",
       "4     andaman-&-nicobar-islands  2018        1  OnePlus      332    0.049258\n",
       "...                         ...   ...      ...      ...      ...         ...\n",
       "6727                west-bengal  2022        1   Lenovo   330017    0.015056\n",
       "6728                west-bengal  2022        1  Infinix   284678    0.012987\n",
       "6729                west-bengal  2022        1     Asus   280347    0.012790\n",
       "6730                west-bengal  2022        1    Apple   277752    0.012671\n",
       "6731                west-bengal  2022        1   Others  2196334    0.100199\n",
       "\n",
       "[6732 rows x 6 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_agg_user"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8afac9e-7bde-42da-84d5-41b18aa06b5a",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Dataframe of map transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6d30a7a8-3a93-4980-a008-5bae3f5d3fb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "path3 = \"C:/Users/Project_3_PhonepePulse/Phonepe_data/data/map/transaction/hover/country/india/state/\"\n",
    "\n",
    "map_trans_list = os.listdir(path3)\n",
    "\n",
    "columns3 = {'State': [], 'Year': [], 'Quarter': [], 'District': [], 'Count': [],\n",
    "            'Amount': []}\n",
    "\n",
    "for state in map_trans_list:\n",
    "    cur_state = path3 + state + \"/\"\n",
    "    map_year_list = os.listdir(cur_state)\n",
    "    \n",
    "    for year in map_year_list:\n",
    "        cur_year = cur_state + year + \"/\"\n",
    "        map_file_list = os.listdir(cur_year)\n",
    "        \n",
    "        for file in map_file_list:\n",
    "            cur_file = cur_year + file\n",
    "            data = open(cur_file, 'r')\n",
    "            C = json.load(data)\n",
    "            \n",
    "            for i in C[\"data\"][\"hoverDataList\"]:\n",
    "                district = i[\"name\"]\n",
    "                count = i[\"metric\"][0][\"count\"]\n",
    "                amount = i[\"metric\"][0][\"amount\"]\n",
    "                columns3[\"District\"].append(district)\n",
    "                columns3[\"Count\"].append(count)\n",
    "                columns3[\"Amount\"].append(amount)\n",
    "                columns3['State'].append(state)\n",
    "                columns3['Year'].append(year)\n",
    "                columns3['Quarter'].append(int(file.strip('.json')))\n",
    "                \n",
    "df_map_trans = pd.DataFrame(columns3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ffd7147d-1d9e-4ddb-843c-b9f6b0c2ab5e",
   "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</th>\n",
       "      <th>Count</th>\n",
       "      <th>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>1</td>\n",
       "      <td>north and middle andaman district</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>1</td>\n",
       "      <td>south andaman district</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>1</td>\n",
       "      <td>nicobars district</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>2</td>\n",
       "      <td>north and middle andaman district</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>2</td>\n",
       "      <td>south andaman district</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>14631</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>nadia district</td>\n",
       "      <td>12690126</td>\n",
       "      <td>2.804568e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14632</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>birbhum district</td>\n",
       "      <td>7617444</td>\n",
       "      <td>1.614650e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14633</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>purba medinipur district</td>\n",
       "      <td>14484229</td>\n",
       "      <td>3.309949e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14634</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>maldah district</td>\n",
       "      <td>12492746</td>\n",
       "      <td>2.721861e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14635</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>darjiling district</td>\n",
       "      <td>8827502</td>\n",
       "      <td>1.801650e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14636 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           State  Year  Quarter  \\\n",
       "0      andaman-&-nicobar-islands  2018        1   \n",
       "1      andaman-&-nicobar-islands  2018        1   \n",
       "2      andaman-&-nicobar-islands  2018        1   \n",
       "3      andaman-&-nicobar-islands  2018        2   \n",
       "4      andaman-&-nicobar-islands  2018        2   \n",
       "...                          ...   ...      ...   \n",
       "14631                west-bengal  2022        4   \n",
       "14632                west-bengal  2022        4   \n",
       "14633                west-bengal  2022        4   \n",
       "14634                west-bengal  2022        4   \n",
       "14635                west-bengal  2022        4   \n",
       "\n",
       "                                District     Count        Amount  \n",
       "0      north and middle andaman district       442  9.316631e+05  \n",
       "1                 south andaman district      5688  1.256025e+07  \n",
       "2                      nicobars district       528  1.139849e+06  \n",
       "3      north and middle andaman district       825  1.317863e+06  \n",
       "4                 south andaman district      9395  2.394824e+07  \n",
       "...                                  ...       ...           ...  \n",
       "14631                     nadia district  12690126  2.804568e+10  \n",
       "14632                   birbhum district   7617444  1.614650e+10  \n",
       "14633           purba medinipur district  14484229  3.309949e+10  \n",
       "14634                    maldah district  12492746  2.721861e+10  \n",
       "14635                 darjiling district   8827502  1.801650e+10  \n",
       "\n",
       "[14636 rows x 6 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_map_trans"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7bc01c56-3e33-4623-9cb3-58c47f1aaa39",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Dataframe of map user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "21de8c92-7c89-4ac6-adf0-2fd200bcd55a",
   "metadata": {},
   "outputs": [],
   "source": [
    "path4 = \"C:/Users/Project_3_PhonepePulse/Phonepe_data/data/map/user/hover/country/india/state/\"\n",
    "\n",
    "map_user_list = os.listdir(path4)\n",
    "\n",
    "columns4 = {\"State\": [], \"Year\": [], \"Quarter\": [], \"District\": [],\n",
    "            \"RegisteredUser\": [], \"AppOpens\": []}\n",
    "\n",
    "for state in map_user_list:\n",
    "    cur_state = path4 + state + \"/\"\n",
    "    map_year_list = os.listdir(cur_state)\n",
    "    \n",
    "    for year in map_year_list:\n",
    "        cur_year = cur_state + year + \"/\"\n",
    "        map_file_list = os.listdir(cur_year)\n",
    "        \n",
    "        for file in map_file_list:\n",
    "            cur_file = cur_year + file\n",
    "            data = open(cur_file, 'r')\n",
    "            D = json.load(data)\n",
    "            \n",
    "            for i in D[\"data\"][\"hoverData\"].items():\n",
    "                district = i[0]\n",
    "                registereduser = i[1][\"registeredUsers\"]\n",
    "                appOpens = i[1]['appOpens']\n",
    "                columns4[\"District\"].append(district)\n",
    "                columns4[\"RegisteredUser\"].append(registereduser)\n",
    "                columns4[\"AppOpens\"].append(appOpens)\n",
    "                columns4['State'].append(state)\n",
    "                columns4['Year'].append(year)\n",
    "                columns4['Quarter'].append(int(file.strip('.json')))\n",
    "                \n",
    "df_map_user = pd.DataFrame(columns4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "20dbad3a-7a2f-4fd2-845e-46d8c98e7fbd",
   "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</th>\n",
       "      <th>RegisteredUser</th>\n",
       "      <th>AppOpens</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>1</td>\n",
       "      <td>north and middle andaman district</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>1</td>\n",
       "      <td>south andaman district</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>1</td>\n",
       "      <td>nicobars district</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>2</td>\n",
       "      <td>north and middle andaman district</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>2</td>\n",
       "      <td>south andaman district</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>14635</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>nadia district</td>\n",
       "      <td>1359420</td>\n",
       "      <td>33853990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14636</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>birbhum district</td>\n",
       "      <td>855236</td>\n",
       "      <td>20950662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14637</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>purba medinipur district</td>\n",
       "      <td>1346908</td>\n",
       "      <td>38278506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14638</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>maldah district</td>\n",
       "      <td>954892</td>\n",
       "      <td>29023743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14639</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>darjiling district</td>\n",
       "      <td>564562</td>\n",
       "      <td>15982631</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14640 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           State  Year  Quarter  \\\n",
       "0      andaman-&-nicobar-islands  2018        1   \n",
       "1      andaman-&-nicobar-islands  2018        1   \n",
       "2      andaman-&-nicobar-islands  2018        1   \n",
       "3      andaman-&-nicobar-islands  2018        2   \n",
       "4      andaman-&-nicobar-islands  2018        2   \n",
       "...                          ...   ...      ...   \n",
       "14635                west-bengal  2022        4   \n",
       "14636                west-bengal  2022        4   \n",
       "14637                west-bengal  2022        4   \n",
       "14638                west-bengal  2022        4   \n",
       "14639                west-bengal  2022        4   \n",
       "\n",
       "                                District  RegisteredUser  AppOpens  \n",
       "0      north and middle andaman district             632         0  \n",
       "1                 south andaman district            5846         0  \n",
       "2                      nicobars district             262         0  \n",
       "3      north and middle andaman district             911         0  \n",
       "4                 south andaman district            8143         0  \n",
       "...                                  ...             ...       ...  \n",
       "14635                     nadia district         1359420  33853990  \n",
       "14636                   birbhum district          855236  20950662  \n",
       "14637           purba medinipur district         1346908  38278506  \n",
       "14638                    maldah district          954892  29023743  \n",
       "14639                 darjiling district          564562  15982631  \n",
       "\n",
       "[14640 rows x 6 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_map_user"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ac4453d-bd64-42f6-be93-f5822d6afd77",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Dataframe of top transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a9ab0314-abf6-44e2-a714-53bf12888964",
   "metadata": {},
   "outputs": [],
   "source": [
    "path5 = \"C:/Users/Project_3_PhonepePulse/Phonepe_data/data/top/transaction/country/india/state/\"\n",
    "\n",
    "top_trans_list = os.listdir(path5)\n",
    "columns5 = {'State': [], 'Year': [], 'Quarter': [], 'Pincode': [], 'Transaction_count': [],\n",
    "            'Transaction_amount': []}\n",
    "\n",
    "for state in top_trans_list:\n",
    "    cur_state = path5 + state + \"/\"\n",
    "    top_year_list = os.listdir(cur_state)\n",
    "    \n",
    "    for year in top_year_list:\n",
    "        cur_year = cur_state + year + \"/\"\n",
    "        top_file_list = os.listdir(cur_year)\n",
    "        \n",
    "        for file in top_file_list:\n",
    "            cur_file = cur_year + file\n",
    "            data = open(cur_file, 'r')\n",
    "            E = json.load(data)\n",
    "            \n",
    "            for i in E['data']['pincodes']:\n",
    "                name = i['entityName']\n",
    "                count = i['metric']['count']\n",
    "                amount = i['metric']['amount']\n",
    "                columns5['Pincode'].append(name)\n",
    "                columns5['Transaction_count'].append(count)\n",
    "                columns5['Transaction_amount'].append(amount)\n",
    "                columns5['State'].append(state)\n",
    "                columns5['Year'].append(year)\n",
    "                columns5['Quarter'].append(int(file.strip('.json')))\n",
    "df_top_trans = pd.DataFrame(columns5)\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "43214cb6-3d8d-4c45-9bff-94d003511d4b",
   "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>Pincode</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>1</td>\n",
       "      <td>744101</td>\n",
       "      <td>1622</td>\n",
       "      <td>2.769298e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>744103</td>\n",
       "      <td>1223</td>\n",
       "      <td>2.238042e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>744102</td>\n",
       "      <td>969</td>\n",
       "      <td>3.519060e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>744105</td>\n",
       "      <td>685</td>\n",
       "      <td>1.298561e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>744104</td>\n",
       "      <td>340</td>\n",
       "      <td>1.039715e+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>7134</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>722101</td>\n",
       "      <td>2900058</td>\n",
       "      <td>5.748321e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7135</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>700135</td>\n",
       "      <td>2471048</td>\n",
       "      <td>3.527457e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7136</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>732101</td>\n",
       "      <td>2407008</td>\n",
       "      <td>5.052109e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7137</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>700091</td>\n",
       "      <td>2348447</td>\n",
       "      <td>2.176640e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7138</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>711101</td>\n",
       "      <td>1765314</td>\n",
       "      <td>4.048329e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7139 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          State  Year  Quarter Pincode  Transaction_count  \\\n",
       "0     andaman-&-nicobar-islands  2018        1  744101               1622   \n",
       "1     andaman-&-nicobar-islands  2018        1  744103               1223   \n",
       "2     andaman-&-nicobar-islands  2018        1  744102                969   \n",
       "3     andaman-&-nicobar-islands  2018        1  744105                685   \n",
       "4     andaman-&-nicobar-islands  2018        1  744104                340   \n",
       "...                         ...   ...      ...     ...                ...   \n",
       "7134                west-bengal  2022        4  722101            2900058   \n",
       "7135                west-bengal  2022        4  700135            2471048   \n",
       "7136                west-bengal  2022        4  732101            2407008   \n",
       "7137                west-bengal  2022        4  700091            2348447   \n",
       "7138                west-bengal  2022        4  711101            1765314   \n",
       "\n",
       "      Transaction_amount  \n",
       "0           2.769298e+06  \n",
       "1           2.238042e+06  \n",
       "2           3.519060e+06  \n",
       "3           1.298561e+06  \n",
       "4           1.039715e+06  \n",
       "...                  ...  \n",
       "7134        5.748321e+09  \n",
       "7135        3.527457e+09  \n",
       "7136        5.052109e+09  \n",
       "7137        2.176640e+09  \n",
       "7138        4.048329e+09  \n",
       "\n",
       "[7139 rows x 6 columns]"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_top_trans"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6826c6d-cc47-4188-b48f-641142970769",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Dataframe of top users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f7d32db2-5028-4da2-9d84-c7cab299c032",
   "metadata": {},
   "outputs": [],
   "source": [
    "path6 = \"C:/Users/Project_3_PhonepePulse/Phonepe_data/data/top/user/country/india/state/\"\n",
    "top_user_list = os.listdir(path6)\n",
    "columns6 = {'State': [], 'Year': [], 'Quarter': [], 'Pincode': [],\n",
    "            'RegisteredUsers': []}\n",
    "\n",
    "for state in top_user_list:\n",
    "    cur_state = path6 + state + \"/\"\n",
    "    top_year_list = os.listdir(cur_state)\n",
    "    \n",
    "    for year in top_year_list:\n",
    "        cur_year = cur_state + year + \"/\"\n",
    "        top_file_list = os.listdir(cur_year)\n",
    "        \n",
    "        for file in top_file_list:\n",
    "            cur_file = cur_year + file\n",
    "            data = open(cur_file, 'r')\n",
    "            F = json.load(data)\n",
    "            \n",
    "            for i in F['data']['pincodes']:\n",
    "                name = i['name']\n",
    "                registeredUsers = i['registeredUsers']\n",
    "                columns6['Pincode'].append(name)\n",
    "                columns6['RegisteredUsers'].append(registeredUsers)\n",
    "                columns6['State'].append(state)\n",
    "                columns6['Year'].append(year)\n",
    "                columns6['Quarter'].append(int(file.strip('.json')))\n",
    "df_top_user = pd.DataFrame(columns6)         "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "1de5867e-5d0c-4692-831f-64860858bca3",
   "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>Pincode</th>\n",
       "      <th>RegisteredUsers</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>1</td>\n",
       "      <td>744103</td>\n",
       "      <td>1608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>744101</td>\n",
       "      <td>1108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>744105</td>\n",
       "      <td>1075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>744102</td>\n",
       "      <td>1006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>744104</td>\n",
       "      <td>272</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>7135</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>700015</td>\n",
       "      <td>108457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7136</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>742304</td>\n",
       "      <td>105471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7137</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>721101</td>\n",
       "      <td>105279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7138</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>700091</td>\n",
       "      <td>102363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7139</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>700150</td>\n",
       "      <td>101966</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7140 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          State  Year  Quarter Pincode  RegisteredUsers\n",
       "0     andaman-&-nicobar-islands  2018        1  744103             1608\n",
       "1     andaman-&-nicobar-islands  2018        1  744101             1108\n",
       "2     andaman-&-nicobar-islands  2018        1  744105             1075\n",
       "3     andaman-&-nicobar-islands  2018        1  744102             1006\n",
       "4     andaman-&-nicobar-islands  2018        1  744104              272\n",
       "...                         ...   ...      ...     ...              ...\n",
       "7135                west-bengal  2022        4  700015           108457\n",
       "7136                west-bengal  2022        4  742304           105471\n",
       "7137                west-bengal  2022        4  721101           105279\n",
       "7138                west-bengal  2022        4  700091           102363\n",
       "7139                west-bengal  2022        4  700150           101966\n",
       "\n",
       "[7140 rows x 5 columns]"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_top_user"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ef47900-8e16-43ae-bc6e-91c41bfe6008",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Converting Dataframes to CSV files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "14b0d942-b868-40f2-b6e4-5b641ba03bd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_agg_trans.to_csv('agg_trans.csv',index=False)\n",
    "df_agg_user.to_csv('agg_user.csv',index=False)\n",
    "df_map_trans.to_csv('map_trans.csv',index=False)\n",
    "df_map_user.to_csv('map_user.csv',index=False)\n",
    "df_top_trans.to_csv('top_trans.csv',index=False)\n",
    "df_top_user.to_csv('top_user.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88afc952-7d23-48d5-8b81-5278cebf388f",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Creating connection with MySQL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2e2361f-124d-408e-b81e-f6aa663b5d4c",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Connecting with SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "57d50487-be1f-4c46-94bd-dd973fe6f3fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "mydb = sql.connect(host=\"localhost\",\n",
    "                   user=\"root\",\n",
    "                   password=\"Jafar@1996\",\n",
    "                   database= \"phonepe_pulse\"\n",
    "                  )\n",
    "mycursor = mydb.cursor(buffered=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5c12889-3681-40ef-afe3-e2572afa2cc2",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Creating a new Database and tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "feda9ef1-6484-49ed-a354-1fae48867f1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# mycursor.execute(\"CREATE DATABASE phonepe_pulse\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "587c7c46-9cb0-4fe3-9f5f-802a94f148d3",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Creating agg_trans table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "cbdd838b-378c-4b0f-969a-a126a97bf357",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"create table agg_trans (State varchar(100), Year int, Quarter int, Transaction_type varchar(100), Transaction_count int, Transaction_amount double)\")\n",
    "\n",
    "for i,row in df_agg_trans.iterrows():\n",
    "    #here %S means string values \n",
    "    sql = \"INSERT INTO agg_trans VALUES (%s,%s,%s,%s,%s,%s)\"\n",
    "    mycursor.execute(sql, tuple(row))\n",
    "    # the connection is not auto committed by default, so we must commit to save our changes\n",
    "    mydb.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87de1509-7e8c-4ba6-b8ec-e4cddcee41f6",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Creating agg_user table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "ff0f9395-98b1-4c84-9d48-1f7f8c40a96c",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"create table agg_user (State varchar(100), Year int, Quarter int, Brands varchar(100), Count int, Percentage double)\")\n",
    "\n",
    "for i,row in df_agg_user.iterrows():\n",
    "    sql = \"INSERT INTO agg_user VALUES (%s,%s,%s,%s,%s,%s)\"\n",
    "    mycursor.execute(sql, tuple(row))\n",
    "    mydb.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "789caefe-39d3-4c41-80be-844701b6d5c5",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Creating map_trans table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "d58ba1b2-ff3f-4eb2-b193-d1ff8d9d9a5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"create table map_trans (State varchar(100), Year int, Quarter int, District varchar(100), Count int, Amount double)\")\n",
    "\n",
    "for i,row in df_map_trans.iterrows():\n",
    "    sql = \"INSERT INTO map_trans VALUES (%s,%s,%s,%s,%s,%s)\"\n",
    "    mycursor.execute(sql, tuple(row))\n",
    "    mydb.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e603b7aa-162a-41ee-88f3-f7be1de541a6",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Creating map_user table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "e08b1e3f-81e7-4131-ba86-f86dd170faa0",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"create table map_user (State varchar(100), Year int, Quarter int, District varchar(100), Registered_user int, App_opens int)\")\n",
    "\n",
    "for i,row in df_map_user.iterrows():\n",
    "    sql = \"INSERT INTO map_user VALUES (%s,%s,%s,%s,%s,%s)\"\n",
    "    mycursor.execute(sql, tuple(row))\n",
    "    mydb.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25506714-b67c-4d96-a8cd-57eb390bfadb",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Creating top_trans table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "a2f80c00-e218-4c24-8a07-861480cf28c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"create table top_trans (State varchar(100), Year int, Quarter int, Pincode int, Transaction_count int, Transaction_amount double)\")\n",
    "\n",
    "for i,row in df_top_trans.iterrows():\n",
    "    sql = \"INSERT INTO top_trans VALUES (%s,%s,%s,%s,%s,%s)\"\n",
    "    mycursor.execute(sql, tuple(row))\n",
    "    mydb.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abb8659d-a1bf-494d-bf85-f8279e5b0a06",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### Creating top_user table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "fb20dd8b-61ce-4148-8bcf-bceb30cb79d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"create table top_user (State varchar(100), Year int, Quarter int, Pincode int, Registered_users int)\")\n",
    "\n",
    "for i,row in df_top_user.iterrows():\n",
    "    sql = \"INSERT INTO top_user VALUES (%s,%s,%s,%s,%s)\"\n",
    "    mycursor.execute(sql, tuple(row))\n",
    "    mydb.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89aab829-3d07-4a7e-85df-7b8f242df1b0",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "### List of tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "4b64439f-c4d9-468c-aa97-8bbdbbebdd46",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('agg_trans',),\n",
       " ('agg_user',),\n",
       " ('map_trans',),\n",
       " ('map_user',),\n",
       " ('top_trans',),\n",
       " ('top_user',)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mycursor.execute(\"show tables\")\n",
    "mycursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55688695-6209-4157-94fb-ba044bb22d7d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}