Data Cloning and Extraction form the GitHub Repo.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8191ac41",
   "metadata": {},
   "source": [
    "## Importing Necessary Python Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b375972e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install GitPython"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2263bbd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import git\n",
    "from git.repo.base import Repo\n",
    "import os\n",
    "import json\n",
    "import mysql.connector as sql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ab4fe8d",
   "metadata": {},
   "source": [
    "## >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9de3482e",
   "metadata": {},
   "source": [
    "## Cloning the Phonepe Repository Locally (Extracting the Data) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "20ca075c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# os.environ[\"GIT_PYTHON_REFRESH\"] = \"quiet\"\n",
    "# Repo.clone_from(\"https://github.com/PhonePe/pulse.git\", \"C:/Users/sadhi/Desktop/Phonepe Project/data_extracted\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f8b7f034",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['andaman-&-nicobar-islands', 'andhra-pradesh', 'arunachal-pradesh', 'assam', 'bihar', 'chandigarh', 'chhattisgarh', 'dadra-&-nagar-haveli-&-daman-&-diu', 'delhi', 'goa', 'gujarat', 'haryana', 'himachal-pradesh', 'jammu-&-kashmir', 'jharkhand', 'karnataka', 'kerala', 'ladakh', 'lakshadweep', 'madhya-pradesh', 'maharashtra', 'manipur', 'meghalaya', 'mizoram', 'nagaland', 'odisha', 'puducherry', 'punjab', 'rajasthan', 'sikkim', 'tamil-nadu', 'telangana', 'tripura', 'uttar-pradesh', 'uttarakhand', 'west-bengal']\n"
     ]
    }
   ],
   "source": [
    "directory1 = \"C:/Users/sadhi/Desktop/Phonepe Project/data_extracted/data/aggregated/transaction/country/india/state/\"\n",
    "A_transactionsList = os.listdir(directory1)\n",
    "print(A_transactionsList)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4089c92e",
   "metadata": {},
   "source": [
    "## >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9088c104",
   "metadata": {},
   "source": [
    "## Tranforming the Extracted Data form .json format to Pandas DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f31bc42",
   "metadata": {},
   "source": [
    "### Aggregated Transations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8e0c7876",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3594 entries, 0 to 3593\n",
      "Data columns (total 6 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   State               3594 non-null   object \n",
      " 1   Year                3594 non-null   object \n",
      " 2   Quarter             3594 non-null   int64  \n",
      " 3   Transaction_type    3594 non-null   object \n",
      " 4   Transaction_count   3594 non-null   int64  \n",
      " 5   Transaction_amount  3594 non-null   float64\n",
      "dtypes: float64(1), int64(2), object(3)\n",
      "memory usage: 168.6+ KB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Transaction_type</th>\n",
       "      <th>Transaction_count</th>\n",
       "      <th>Transaction_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Recharge &amp; bill payments</td>\n",
       "      <td>4200</td>\n",
       "      <td>1.845307e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Peer-to-peer payments</td>\n",
       "      <td>1871</td>\n",
       "      <td>1.213866e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Merchant payments</td>\n",
       "      <td>298</td>\n",
       "      <td>4.525072e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>33</td>\n",
       "      <td>1.060142e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Others</td>\n",
       "      <td>256</td>\n",
       "      <td>1.846899e+05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State  Year  Quarter          Transaction_type  \\\n",
       "0  andaman-&-nicobar-islands  2018        1  Recharge & bill payments   \n",
       "1  andaman-&-nicobar-islands  2018        1     Peer-to-peer payments   \n",
       "2  andaman-&-nicobar-islands  2018        1         Merchant payments   \n",
       "3  andaman-&-nicobar-islands  2018        1        Financial Services   \n",
       "4  andaman-&-nicobar-islands  2018        1                    Others   \n",
       "\n",
       "   Transaction_count  Transaction_amount  \n",
       "0               4200        1.845307e+06  \n",
       "1               1871        1.213866e+07  \n",
       "2                298        4.525072e+05  \n",
       "3                 33        1.060142e+04  \n",
       "4                256        1.846899e+05  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "directory1 = \"C:/Users/sadhi/Desktop/Phonepe Project/data_extracted/data/aggregated/transaction/country/india/state/\"\n",
    "A_transactionsList = os.listdir(directory1)\n",
    "\n",
    "# Intialising a Dictionary that will be used to form a Pandas DataFrame and contains the column names of that DataFrame\n",
    "col1 = {\n",
    "    'State': [], \n",
    "    'Year': [], \n",
    "    'Quarter': [], \n",
    "    'Transaction_type': [], \n",
    "    'Transaction_count': [],\n",
    "    'Transaction_amount': []\n",
    "}\n",
    "\n",
    "\n",
    "# Using nested for Loops to access the files in a particular directory (Phonepe data)\n",
    "# state --> year --> file\n",
    "for state in A_transactionsList:\n",
    "    current_state = directory1 + state + \"/\"\n",
    "    A_yearList = os.listdir(current_state)\n",
    "    \n",
    "    for year in A_yearList:\n",
    "        current_year = current_state + year + \"/\"\n",
    "        A_fileList = os.listdir(current_year)\n",
    "        \n",
    "        for file in A_fileList:\n",
    "            current_file = current_year + file\n",
    "            data1 = open(current_file, 'r')\n",
    "            F1 = json.load(data1)\n",
    "            \n",
    "            # Extracting the relevent data from the .json file and appending it into the list inside the dictionary col1\n",
    "            for i in F1['data']['transactionData']:\n",
    "                name = i['name']\n",
    "                count = i['paymentInstruments'][0]['count']\n",
    "                amount = i['paymentInstruments'][0]['amount']\n",
    "                col1['State'].append(state)\n",
    "                col1['Year'].append(year)\n",
    "                col1['Quarter'].append(int(file.strip('.json')))\n",
    "                col1['Transaction_type'].append(name)\n",
    "                col1['Transaction_count'].append(count)\n",
    "                col1['Transaction_amount'].append(amount)\n",
    "                \n",
    "df_A_Transactions = pd.DataFrame(col1)\n",
    "print(df_A_Transactions.info())\n",
    "df_A_Transactions.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccf73846",
   "metadata": {},
   "source": [
    "### Aggregated Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fa00c4d4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6732 entries, 0 to 6731\n",
      "Data columns (total 6 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   State       6732 non-null   object \n",
      " 1   Year        6732 non-null   object \n",
      " 2   Quarter     6732 non-null   int64  \n",
      " 3   Brands      6732 non-null   object \n",
      " 4   Count       6732 non-null   int64  \n",
      " 5   Percentage  6732 non-null   float64\n",
      "dtypes: float64(1), int64(2), object(3)\n",
      "memory usage: 315.7+ KB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "  </tbody>\n",
       "</table>\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"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "directory2 = \"C:/Users/sadhi/Desktop/Phonepe Project/data_extracted/data/aggregated/user/country/india/state/\"\n",
    "A_usersList = os.listdir(directory2)\n",
    "\n",
    "# Intialising a Dictionary that will be used to form a Pandas DataFrame and contains the column names of that DataFrame\n",
    "col2 = {\n",
    "    'State': [], \n",
    "    'Year': [], \n",
    "    'Quarter': [], \n",
    "    'Brands': [], \n",
    "    'Count': [],\n",
    "    'Percentage': []\n",
    "}\n",
    "\n",
    "\n",
    "# Using nested for Loops to access the files in a particular directory (Phonepe data)\n",
    "# state --> year --> file\n",
    "for state in A_usersList:\n",
    "    current_state = directory2 + state + \"/\"\n",
    "    A_yearList = os.listdir(current_state)\n",
    "    \n",
    "    for year in A_yearList:\n",
    "        current_year = current_state + year + \"/\"\n",
    "        A_fileList = os.listdir(current_year)\n",
    "        \n",
    "        for file in A_fileList:\n",
    "            current_file = current_year + file\n",
    "            data2 = open(current_file, 'r')\n",
    "            F2 = json.load(data2)\n",
    "            \n",
    "            # Extracting the relevent data from the .json file and appending it into the list inside the dictionary col2\n",
    "            try:\n",
    "                for i in F2['data']['usersByDevice']:\n",
    "                    brand_name = i[\"brand\"]\n",
    "                    counts = i[\"count\"]\n",
    "                    percents = i[\"percentage\"]\n",
    "                    col2[\"State\"].append(state)\n",
    "                    col2[\"Year\"].append(year)\n",
    "                    col2[\"Quarter\"].append(int(file.strip('.json')))\n",
    "                    col2[\"Brands\"].append(brand_name)\n",
    "                    col2[\"Count\"].append(counts)\n",
    "                    col2[\"Percentage\"].append(percents)\n",
    "            except:\n",
    "                pass\n",
    "            \n",
    "df_A_User = pd.DataFrame(col2)\n",
    "print(df_A_User.info())\n",
    "df_A_User.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ba4f7fb",
   "metadata": {},
   "source": [
    "### --------------------------------------------------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eef2d994",
   "metadata": {},
   "source": [
    "### Map Transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "919d6cfc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14636 entries, 0 to 14635\n",
      "Data columns (total 6 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   State     14636 non-null  object \n",
      " 1   Year      14636 non-null  object \n",
      " 2   Quarter   14636 non-null  int64  \n",
      " 3   District  14636 non-null  object \n",
      " 4   Count     14636 non-null  int64  \n",
      " 5   Amount    14636 non-null  float64\n",
      "dtypes: float64(1), int64(2), object(3)\n",
      "memory usage: 686.2+ KB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "  </tbody>\n",
       "</table>\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",
       "                            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  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "directory3 = \"C:/Users/sadhi/Desktop/Phonepe Project/data_extracted/data/map/transaction/hover/country/india/state/\"\n",
    "M_transactionsList = os.listdir(directory3)\n",
    "\n",
    "# Intialising a Dictionary that will be used to form a Pandas DataFrame and contains the column names of that DataFrame\n",
    "col3 = {\n",
    "    'State': [], \n",
    "    'Year': [], \n",
    "    'Quarter': [], \n",
    "    'District': [], \n",
    "    'Count': [],\n",
    "    'Amount': []\n",
    "}\n",
    "\n",
    "\n",
    "# Using nested for Loops to access the files in a particular directory (Phonepe data)\n",
    "# state --> year --> file\n",
    "for state in M_transactionsList:\n",
    "    current_state = directory3 + state + \"/\"\n",
    "    A_yearList = os.listdir(current_state)\n",
    "    \n",
    "    for year in A_yearList:\n",
    "        current_year = current_state + year + \"/\"\n",
    "        A_fileList = os.listdir(current_year)\n",
    "        \n",
    "        for file in A_fileList:\n",
    "            current_file = current_year + file\n",
    "            data3 = open(current_file, 'r')\n",
    "            F3 = json.load(data3)\n",
    "            \n",
    "            # Extracting the relevent data from the .json file and appending it into the list inside the dictionary col3\n",
    "            try:\n",
    "                for i in F3['data']['hoverDataList']:\n",
    "                    district = i[\"name\"]\n",
    "                    count = i[\"metric\"][0][\"count\"]\n",
    "                    amount = i[\"metric\"][0][\"amount\"]\n",
    "                    col3[\"State\"].append(state)\n",
    "                    col3[\"Year\"].append(year)\n",
    "                    col3[\"Quarter\"].append(int(file.strip('.json')))\n",
    "                    col3[\"District\"].append(district)\n",
    "                    col3[\"Count\"].append(count)\n",
    "                    col3[\"Amount\"].append(amount)\n",
    "            except:\n",
    "                pass\n",
    "            \n",
    "df_M_Transactions = pd.DataFrame(col3)\n",
    "print(df_M_Transactions.info())\n",
    "df_M_Transactions.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a575842c",
   "metadata": {},
   "source": [
    "### Map Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "731ce08e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14640 entries, 0 to 14639\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Non-Null Count  Dtype \n",
      "---  ------          --------------  ----- \n",
      " 0   State           14640 non-null  object\n",
      " 1   Year            14640 non-null  object\n",
      " 2   Quarter         14640 non-null  int64 \n",
      " 3   District        14640 non-null  object\n",
      " 4   RegisteredUser  14640 non-null  int64 \n",
      " 5   AppOpens        14640 non-null  int64 \n",
      "dtypes: int64(3), object(3)\n",
      "memory usage: 686.4+ KB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "  </tbody>\n",
       "</table>\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",
       "                            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  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "directory4 = \"C:/Users/sadhi/Desktop/Phonepe Project/data_extracted/data/map/user/hover/country/india/state/\"\n",
    "M_usersList = os.listdir(directory4)\n",
    "\n",
    "# Intialising a Dictionary that will be used to form a Pandas DataFrame and contains the column names of that DataFrame\n",
    "col4 = {\n",
    "    'State': [], \n",
    "    'Year': [], \n",
    "    'Quarter': [], \n",
    "    'District': [], \n",
    "    'RegisteredUser': [],\n",
    "    'AppOpens': []\n",
    "}\n",
    "\n",
    "\n",
    "# Using nested for Loops to access the files in a particular directory (Phonepe data)\n",
    "# state --> year --> file\n",
    "for state in M_usersList:\n",
    "    current_state = directory4 + state + \"/\"\n",
    "    A_yearList = os.listdir(current_state)\n",
    "    \n",
    "    for year in A_yearList:\n",
    "        current_year = current_state + year + \"/\"\n",
    "        A_fileList = os.listdir(current_year)\n",
    "        \n",
    "        for file in A_fileList:\n",
    "            current_file = current_year + file\n",
    "            data4 = open(current_file, 'r')\n",
    "            F4 = json.load(data4)\n",
    "            \n",
    "            # Extracting the relevent data from the .json file and appending it into the list inside the dictionary col4\n",
    "            try:\n",
    "                for i in F4['data']['hoverData'].items():\n",
    "                    district = i[0]\n",
    "                    registeredUser = i[1][\"registeredUsers\"]\n",
    "                    appOpens = i[1][\"appOpens\"]\n",
    "                    col4[\"State\"].append(state)\n",
    "                    col4[\"Year\"].append(year)\n",
    "                    col4[\"Quarter\"].append(int(file.strip('.json')))\n",
    "                    col4[\"District\"].append(district)\n",
    "                    col4[\"RegisteredUser\"].append(registeredUser)\n",
    "                    col4[\"AppOpens\"].append(appOpens)\n",
    "            except:\n",
    "                pass\n",
    "            \n",
    "df_M_User = pd.DataFrame(col4)\n",
    "print(df_M_User.info())\n",
    "df_M_User.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28e3f4ff",
   "metadata": {},
   "source": [
    "### --------------------------------------------------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfc21ffa",
   "metadata": {},
   "source": [
    "### Top Transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "10a1f1f1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 7139 entries, 0 to 7138\n",
      "Data columns (total 6 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   State               7139 non-null   object \n",
      " 1   Year                7139 non-null   object \n",
      " 2   Quarter             7139 non-null   int64  \n",
      " 3   Pincode             7137 non-null   object \n",
      " 4   Transaction_count   7139 non-null   int64  \n",
      " 5   Transaction_amount  7139 non-null   float64\n",
      "dtypes: float64(1), int64(2), object(3)\n",
      "memory usage: 334.8+ KB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "  </tbody>\n",
       "</table>\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",
       "   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  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "directory5 = \"C:/Users/sadhi/Desktop/Phonepe Project/data_extracted/data/top/transaction/country/india/state/\"\n",
    "T_transactionsList = os.listdir(directory5)\n",
    "\n",
    "# Intialising a Dictionary that will be used to form a Pandas DataFrame and contains the column names of that DataFrame\n",
    "col5 = {\n",
    "    'State': [], \n",
    "    'Year': [], \n",
    "    'Quarter': [], \n",
    "    'Pincode': [], \n",
    "    'Transaction_count': [],\n",
    "    'Transaction_amount': []\n",
    "}\n",
    "\n",
    "\n",
    "# Using nested for Loops to access the files in a particular directory (Phonepe data)\n",
    "# state --> year --> file\n",
    "for state in T_transactionsList:\n",
    "    current_state = directory5 + state + \"/\"\n",
    "    A_yearList = os.listdir(current_state)\n",
    "    \n",
    "    for year in A_yearList:\n",
    "        current_year = current_state + year + \"/\"\n",
    "        A_fileList = os.listdir(current_year)\n",
    "        \n",
    "        for file in A_fileList:\n",
    "            current_file = current_year + file\n",
    "            data5 = open(current_file, 'r')\n",
    "            F5 = json.load(data5)\n",
    "            \n",
    "            # Extracting the relevent data from the .json file and appending it into the list inside the dictionary col5\n",
    "            try:\n",
    "                for i in F5['data']['pincodes']:\n",
    "                    pincode = i[\"entityName\"]\n",
    "                    count = i[\"metric\"][\"count\"]\n",
    "                    amount = i[\"metric\"][\"amount\"]\n",
    "                    col5[\"State\"].append(state)\n",
    "                    col5[\"Year\"].append(year)\n",
    "                    col5[\"Quarter\"].append(int(file.strip('.json')))\n",
    "                    col5[\"Pincode\"].append(pincode)\n",
    "                    col5[\"Transaction_count\"].append(count)\n",
    "                    col5[\"Transaction_amount\"].append(amount)\n",
    "            except:\n",
    "                pass\n",
    "            \n",
    "df_T_Transactions = pd.DataFrame(col5)\n",
    "print(df_T_Transactions.info())\n",
    "df_T_Transactions.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbef4216",
   "metadata": {},
   "source": [
    "### Top Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "af8aab6f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 7140 entries, 0 to 7139\n",
      "Data columns (total 5 columns):\n",
      " #   Column           Non-Null Count  Dtype \n",
      "---  ------           --------------  ----- \n",
      " 0   State            7140 non-null   object\n",
      " 1   Year             7140 non-null   object\n",
      " 2   Quarter          7140 non-null   int64 \n",
      " 3   Pincode          7140 non-null   object\n",
      " 4   RegisteredUsers  7140 non-null   int64 \n",
      "dtypes: int64(2), object(3)\n",
      "memory usage: 279.0+ KB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "  </tbody>\n",
       "</table>\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"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "directory6 = \"C:/Users/sadhi/Desktop/Phonepe Project/data_extracted/data/top/user/country/india/state/\"\n",
    "T_usersList = os.listdir(directory6)\n",
    "\n",
    "# Intialising a Dictionary that will be used to form a Pandas DataFrame and contains the column names of that DataFrame\n",
    "col6 = {\n",
    "    'State': [], \n",
    "    'Year': [], \n",
    "    'Quarter': [], \n",
    "    'Pincode': [], \n",
    "    'RegisteredUsers': []\n",
    "}\n",
    "\n",
    "\n",
    "# Using nested for Loops to access the files in a particular directory (Phonepe data)\n",
    "# state --> year --> file\n",
    "for state in T_usersList:\n",
    "    current_state = directory6 + state + \"/\"\n",
    "    A_yearList = os.listdir(current_state)\n",
    "    \n",
    "    for year in A_yearList:\n",
    "        current_year = current_state + year + \"/\"\n",
    "        A_fileList = os.listdir(current_year)\n",
    "        \n",
    "        for file in A_fileList:\n",
    "            current_file = current_year + file\n",
    "            data6 = open(current_file, 'r')\n",
    "            F6 = json.load(data6)\n",
    "            \n",
    "            # Extracting the relevent data from the .json file and appending it into the list inside the dictionary col6\n",
    "            try:\n",
    "                for i in F6['data']['pincodes']:\n",
    "                    pincode = i[\"name\"]\n",
    "                    registeredUser = i[\"registeredUsers\"]\n",
    "                    col6[\"State\"].append(state)\n",
    "                    col6[\"Year\"].append(year)\n",
    "                    col6[\"Quarter\"].append(int(file.strip('.json')))\n",
    "                    col6[\"Pincode\"].append(pincode)\n",
    "                    col6[\"RegisteredUsers\"].append(registeredUser)\n",
    "            except:\n",
    "                pass\n",
    "            \n",
    "df_T_User = pd.DataFrame(col6)\n",
    "print(df_T_User.info())\n",
    "df_T_User.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "429ee593",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_A_Transactions.to_csv('a_transactions.csv',index=False)\n",
    "df_A_User.to_csv('a_user.csv',index=False)\n",
    "df_M_Transactions.to_csv('m_transactions.csv',index=False)\n",
    "df_M_User.to_csv('m_user.csv',index=False)\n",
    "df_T_Transactions.to_csv('t_transactions.csv',index=False)\n",
    "df_T_User.to_csv('t_user.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08534b8c",
   "metadata": {},
   "source": [
    "## >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8596afa",
   "metadata": {},
   "source": [
    "## MySQL Workbench Connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f58d7c5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "mydb = sql.connect(host=\"your hostname goes here\",\n",
    "                   user=\"your username goes here\",\n",
    "                   password=\"your password goes here\",\n",
    "                   database= \"your database name goes here\"\n",
    "                  )\n",
    "mycursor = mydb.cursor(buffered=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "705b8bba",
   "metadata": {},
   "source": [
    "### Aggregated Transactions Table (a_transactions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "55ce52f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute('''create table a_transactions \n",
    "                    (     State varchar(255),   \n",
    "                          Year int, \n",
    "                          Quarter int, \n",
    "                          Transaction_type varchar(255), \n",
    "                          Transaction_count int, \n",
    "                          Transaction_amount double\n",
    "                    )'''\n",
    "                )\n",
    "\n",
    "for index,row in df_A_Transactions.iterrows():\n",
    "    # 6 %s because we need to insert values into 6 columns assigned above\n",
    "    sql1 = \"INSERT INTO a_transactions VALUES (%s,%s,%s,%s,%s,%s)\" \n",
    "    mycursor.execute(sql1, tuple(row))\n",
    "    mydb.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c01ce66",
   "metadata": {},
   "source": [
    "### Aggregated User Table (a_user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "9624848d",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute('''create table a_user\n",
    "                    (     State varchar(255),   \n",
    "                          Year int, \n",
    "                          Quarter int, \n",
    "                          Brands varchar(255), \n",
    "                          Count int, \n",
    "                          Percentage double\n",
    "                    )'''\n",
    "                )\n",
    "\n",
    "\n",
    "for index,row in df_A_User.iterrows():\n",
    "    # 6 %s because we need to insert values into 6 columns assigned above\n",
    "    sql2 = \"INSERT INTO a_user VALUES (%s,%s,%s,%s,%s,%s)\"\n",
    "    mycursor.execute(sql2, tuple(row))\n",
    "    mydb.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea520c64",
   "metadata": {},
   "source": [
    "### Map Transactions Table (m_transactions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7ecc6a21",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute('''create table m_transactions\n",
    "                    (     State varchar(255),   \n",
    "                          Year int, \n",
    "                          Quarter int, \n",
    "                          District varchar(255), \n",
    "                          Count int, \n",
    "                          Amount double\n",
    "                    )'''\n",
    "                )\n",
    "\n",
    "\n",
    "for index,row in df_M_Transactions.iterrows():\n",
    "    # 6 %s because we need to insert values into 6 columns assigned above\n",
    "    sql3 = \"INSERT INTO m_transactions VALUES (%s,%s,%s,%s,%s,%s)\"\n",
    "    mycursor.execute(sql3, tuple(row))\n",
    "    mydb.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36da0a77",
   "metadata": {},
   "source": [
    "### Map User Table (m_user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f4daf937",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute('''create table m_user\n",
    "                    (     State varchar(255),   \n",
    "                          Year int, \n",
    "                          Quarter int, \n",
    "                          District varchar(255), \n",
    "                          Registered_user int, \n",
    "                          App_opens int\n",
    "                    )'''\n",
    "                )\n",
    "\n",
    "\n",
    "for index,row in df_M_User.iterrows():\n",
    "    # 6 %s because we need to insert values into 6 columns assigned above\n",
    "    sql4 = \"INSERT INTO m_user VALUES (%s,%s,%s,%s,%s,%s)\"\n",
    "    mycursor.execute(sql4, tuple(row))\n",
    "    mydb.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e00894c",
   "metadata": {},
   "source": [
    "### Top Transactions Table (t_transactions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ca483b8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute('''create table t_transactions\n",
    "                    (     State varchar(255),   \n",
    "                          Year int, \n",
    "                          Quarter int, \n",
    "                          Pincode int, \n",
    "                          Transaction_count int, \n",
    "                          Transaction_amount double\n",
    "                    )'''\n",
    "                )\n",
    "\n",
    "\n",
    "for index,row in df_T_Transactions.iterrows():\n",
    "    # 6 %s because we need to insert values into 6 columns assigned above\n",
    "    sql5 = \"INSERT INTO t_transactions VALUES (%s,%s,%s,%s,%s,%s)\"\n",
    "    mycursor.execute(sql5, tuple(row))\n",
    "    mydb.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95117c74",
   "metadata": {},
   "source": [
    "### Top User Table (t_user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "7beecc97",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute('''create table t_user\n",
    "                    (     State varchar(255),   \n",
    "                          Year int, \n",
    "                          Quarter int, \n",
    "                          Pincode int, \n",
    "                          Registered_users int\n",
    "                    )'''\n",
    "                )\n",
    "\n",
    "\n",
    "for index,row in df_T_User.iterrows():\n",
    "    # 5 %s because we need to insert values into 5 columns assigned above\n",
    "    sql6 = \"INSERT INTO t_user VALUES (%s,%s,%s,%s,%s)\"\n",
    "    mycursor.execute(sql6, tuple(row))\n",
    "    mydb.commit()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}