Data extraction.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8f8a9efa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: streamlit in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (1.23.1)\n",
      "Requirement already satisfied: pyarrow>=4.0 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from streamlit) (12.0.0)\n",
      "Requirement already satisfied: altair<6,>=4.0 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from streamlit) (5.0.1)\n",
      "Requirement already satisfied: tenacity<9,>=8.0.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (8.0.1)\n",
      "Requirement already satisfied: importlib-metadata<7,>=1.4 in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (4.11.3)\n",
      "Requirement already satisfied: protobuf<5,>=3.20 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from streamlit) (3.20.3)\n",
      "Requirement already satisfied: gitpython!=3.1.19,<4,>=3 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from streamlit) (3.1.31)\n",
      "Requirement already satisfied: numpy<2,>=1 in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (1.21.5)\n",
      "Requirement already satisfied: packaging<24,>=14.1 in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (21.3)\n",
      "Requirement already satisfied: typing-extensions<5,>=4.0.1 in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (4.3.0)\n",
      "Requirement already satisfied: rich<14,>=10.11.0 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from streamlit) (13.4.1)\n",
      "Requirement already satisfied: pydeck<1,>=0.1.dev5 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from streamlit) (0.8.1b0)\n",
      "Requirement already satisfied: click<9,>=7.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (8.0.4)\n",
      "Requirement already satisfied: python-dateutil<3,>=2 in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (2.8.2)\n",
      "Requirement already satisfied: pandas<3,>=0.25 in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (1.4.4)\n",
      "Requirement already satisfied: toml<2 in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (0.10.2)\n",
      "Requirement already satisfied: tornado<7,>=6.0.3 in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (6.1)\n",
      "Requirement already satisfied: tzlocal<5,>=1.1 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from streamlit) (4.3)\n",
      "Requirement already satisfied: blinker<2,>=1.0.0 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from streamlit) (1.6.2)\n",
      "Requirement already satisfied: pillow<10,>=6.2.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (9.2.0)\n",
      "Requirement already satisfied: watchdog in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (2.1.6)\n",
      "Requirement already satisfied: validators<1,>=0.2 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from streamlit) (0.20.0)\n",
      "Requirement already satisfied: requests<3,>=2.4 in c:\\programdata\\anaconda3\\lib\\site-packages (from streamlit) (2.28.1)\n",
      "Requirement already satisfied: cachetools<6,>=4.0 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from streamlit) (5.3.1)\n",
      "Requirement already satisfied: pympler<2,>=0.9 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from streamlit) (1.0.1)\n",
      "Requirement already satisfied: jsonschema>=3.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from altair<6,>=4.0->streamlit) (4.16.0)\n",
      "Requirement already satisfied: jinja2 in c:\\programdata\\anaconda3\\lib\\site-packages (from altair<6,>=4.0->streamlit) (2.11.3)\n",
      "Requirement already satisfied: toolz in c:\\programdata\\anaconda3\\lib\\site-packages (from altair<6,>=4.0->streamlit) (0.11.2)\n",
      "Requirement already satisfied: colorama in c:\\programdata\\anaconda3\\lib\\site-packages (from click<9,>=7.0->streamlit) (0.4.5)\n",
      "Requirement already satisfied: gitdb<5,>=4.0.1 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from gitpython!=3.1.19,<4,>=3->streamlit) (4.0.10)\n",
      "Requirement already satisfied: zipp>=0.5 in c:\\programdata\\anaconda3\\lib\\site-packages (from importlib-metadata<7,>=1.4->streamlit) (3.8.0)\n",
      "Requirement already satisfied: pyparsing!=3.0.5,>=2.0.2 in c:\\programdata\\anaconda3\\lib\\site-packages (from packaging<24,>=14.1->streamlit) (3.0.9)\n",
      "Requirement already satisfied: pytz>=2020.1 in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas<3,>=0.25->streamlit) (2022.1)\n",
      "Requirement already satisfied: six>=1.5 in c:\\programdata\\anaconda3\\lib\\site-packages (from python-dateutil<3,>=2->streamlit) (1.16.0)\n",
      "Requirement already satisfied: urllib3<1.27,>=1.21.1 in c:\\programdata\\anaconda3\\lib\\site-packages (from requests<3,>=2.4->streamlit) (1.26.11)\n",
      "Requirement already satisfied: charset-normalizer<3,>=2 in c:\\programdata\\anaconda3\\lib\\site-packages (from requests<3,>=2.4->streamlit) (2.0.4)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in c:\\programdata\\anaconda3\\lib\\site-packages (from requests<3,>=2.4->streamlit) (2022.9.14)\n",
      "Requirement already satisfied: idna<4,>=2.5 in c:\\programdata\\anaconda3\\lib\\site-packages (from requests<3,>=2.4->streamlit) (3.3)\n",
      "Requirement already satisfied: markdown-it-py<3.0.0,>=2.2.0 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from rich<14,>=10.11.0->streamlit) (2.2.0)\n",
      "Requirement already satisfied: pygments<3.0.0,>=2.13.0 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from rich<14,>=10.11.0->streamlit) (2.15.1)\n",
      "Requirement already satisfied: pytz-deprecation-shim in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from tzlocal<5,>=1.1->streamlit) (0.1.0.post0)\n",
      "Requirement already satisfied: tzdata in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from tzlocal<5,>=1.1->streamlit) (2023.3)\n",
      "Requirement already satisfied: decorator>=3.4.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from validators<1,>=0.2->streamlit) (5.1.1)\n",
      "Requirement already satisfied: smmap<6,>=3.0.1 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from gitdb<5,>=4.0.1->gitpython!=3.1.19,<4,>=3->streamlit) (5.0.0)\n",
      "Requirement already satisfied: MarkupSafe>=0.23 in c:\\programdata\\anaconda3\\lib\\site-packages (from jinja2->altair<6,>=4.0->streamlit) (2.0.1)\n",
      "Requirement already satisfied: attrs>=17.4.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from jsonschema>=3.0->altair<6,>=4.0->streamlit) (21.4.0)\n",
      "Requirement already satisfied: pyrsistent!=0.17.0,!=0.17.1,!=0.17.2,>=0.14.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from jsonschema>=3.0->altair<6,>=4.0->streamlit) (0.18.0)\n",
      "Requirement already satisfied: mdurl~=0.1 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from markdown-it-py<3.0.0,>=2.2.0->rich<14,>=10.11.0->streamlit) (0.1.2)\n",
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: mysql.connector in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (2.2.9)\n",
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: plotly in c:\\programdata\\anaconda3\\lib\\site-packages (5.9.0)\n",
      "Requirement already satisfied: tenacity>=6.2.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from plotly) (8.0.1)\n",
      "Defaulting to user installation because normal site-packages is not writeable\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR: Could not find a version that satisfies the requirement os (from versions: none)\n",
      "ERROR: No matching distribution found for os\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: gitpython in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (3.1.31)\n",
      "Requirement already satisfied: gitdb<5,>=4.0.1 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from gitpython) (4.0.10)\n",
      "Requirement already satisfied: smmap<6,>=3.0.1 in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (from gitdb<5,>=4.0.1->gitpython) (5.0.0)\n"
     ]
    }
   ],
   "source": [
    "#Installing libraries\n",
    "!pip install streamlit\n",
    "!pip install mysql.connector\n",
    "!pip install plotly\n",
    "!pip install os\n",
    "!pip install gitpython"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "99c7d470",
   "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 PIL import Image"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ca13ae6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# cloning data from github\n",
    "#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/akash/Phonepe_pulse/data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b4805d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# DATA TRANSFORMATION "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "455662eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dataframe of aggregated Transactions\n",
    "path1 = \"C:/Users/akash/Phonepe_pulse/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": 4,
   "id": "2b0196f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3594, 6)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_agg_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5a9ff011",
   "metadata": {},
   "outputs": [],
   "source": [
    "# data frame of aggregated user\n",
    "path2 = \"C:/Users/akash/Phonepe_pulse/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": 6,
   "id": "2c6dd863",
   "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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_agg_user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "35ba8dc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data frame of map transactions\n",
    "path3 = \"C:/Users/akash/Phonepe_pulse/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": 8,
   "id": "9636fc7b",
   "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": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_map_trans"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "79ea482a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data frame of map user\n",
    "path4 = \"C:/Users/akash/Phonepe_pulse/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": 10,
   "id": "da4e7696",
   "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": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_map_user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9250aaaf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data frame of top transactions\n",
    "path5 = \"C:/Users/akash/Phonepe_pulse/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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d0f21c0c",
   "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": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_top_trans"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a0cb7b58",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data frame of top users\n",
    "path6 = \"C:/Users/akash/Phonepe_pulse/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": 14,
   "id": "f073cf3c",
   "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": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_top_user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a6d35006",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Coverting Data frames to csv files\n",
    "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": "code",
   "execution_count": 16,
   "id": "ff46b498",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: pymysql in c:\\users\\akash\\appdata\\roaming\\python\\python39\\site-packages (1.0.2)\n"
     ]
    }
   ],
   "source": [
    "!pip install pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "04f3b8aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating connection with MySQL\n",
    "# Connecting with SQL\n",
    "mydb = sql.connect(host=\"localhost\",\n",
    "                   user=\"root\",\n",
    "                   password=\"\",\n",
    "                   #database= \"Phonepe_pulse\"\n",
    "                  )\n",
    "mycursor = mydb.cursor(buffered=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "b1c8bd4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating new database and tables\n",
    "mycursor.execute(\"CREATE DATABASE Phonepe_pulse\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "92b63e1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"USE Phonepe_pulse\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a8d13d6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating agg_trans table\n",
    "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": "code",
   "execution_count": 20,
   "id": "af8e8368",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating agg_user table\n",
    "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": "code",
   "execution_count": 21,
   "id": "29167368",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating map_trans table\n",
    "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": "code",
   "execution_count": 22,
   "id": "a0a784a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating map_user table\n",
    "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": "code",
   "execution_count": 23,
   "id": "05d50ee5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating top_trans table\n",
    "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": "code",
   "execution_count": 24,
   "id": "d6aae4de",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating top_user table\n",
    "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": "code",
   "execution_count": 25,
   "id": "728450e3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('agg_trans',),\n",
       " ('agg_user',),\n",
       " ('map_trans',),\n",
       " ('map_user',),\n",
       " ('top_trans',),\n",
       " ('top_user',)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# List of tables\n",
    "mycursor.execute(\"show tables\")\n",
    "mycursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60bd93db",
   "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}