phonePe_data_extraaction.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Quater</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</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</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</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</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</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  Quater          Transaction_type  \\\n",
       "0  Andaman & Nicobar  2018       1  Recharge & bill payments   \n",
       "1  Andaman & Nicobar  2018       1     Peer-to-peer payments   \n",
       "2  Andaman & Nicobar  2018       1         Merchant payments   \n",
       "3  Andaman & Nicobar  2018       1        Financial Services   \n",
       "4  Andaman & Nicobar  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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# agg_state_list=os.listdir(r\"E:\\phonepee\\pulse-master\\pulse-master\\data\\aggregated\\transaction\\country\\india\\state\")\n",
    "# print(agg_state_list)\n",
    "path1=r\"E://phonepee//pulse-master//pulse-master//data//aggregated//transaction//country//india//state//\"\n",
    "agg_transaction_list=os.listdir(path1)\n",
    "# print(agg_state_list)\n",
    "columns_name={'State':[], 'Year':[],'Quater':[],'Transaction_type':[], 'Transaction_count':[], 'Transaction_amount':[]}\n",
    "for i in agg_transaction_list:\n",
    "    p_i=path1+i+\"//\"\n",
    "    # print(p_i)\n",
    "    year1=os.listdir(p_i)\n",
    "    # print(Agg_yr)\n",
    "    for j in year1:\n",
    "        p_j=p_i+j+\"//\"\n",
    "        agg_yr_list=os.listdir(p_j)\n",
    "        for k in agg_yr_list:\n",
    "            p_k=p_j+k \n",
    "            Data=open(p_k,'r')\n",
    "            D=json.load(Data)\n",
    "            D\n",
    "            for z in D['data']['transactionData']:\n",
    "              Name=z['name']\n",
    "              count=z['paymentInstruments'][0]['count']\n",
    "              amount=z['paymentInstruments'][0]['amount']\n",
    "              columns_name['Transaction_type'].append(Name)\n",
    "              columns_name['Transaction_count'].append(count)\n",
    "              columns_name['Transaction_amount'].append(amount)\n",
    "              columns_name['State'].append(i)\n",
    "              columns_name['Year'].append(j)\n",
    "              columns_name['Quater'].append(int(k.strip('.json')))\n",
    "df_agg_transaction=pd.DataFrame(columns_name)\n",
    "mappings = {\n",
    "#     'carrot': 'veg',\n",
    "#     'papaya': 'fruit'\n",
    "'andaman-&-nicobar-islands': 'Andaman & Nicobar',\n",
    "'andhra-pradesh':  'Andhra Pradesh',\n",
    "'arunachal-pradesh':'Arunachal Pradesh',\n",
    "'assam':'Assam',\n",
    "'bihar':\"Bihar\",\n",
    "'chandigarh':'Chandigarh',\n",
    "'chhattisgarh':'Chhattisgarh',\n",
    "'dadra-&-nagar-haveli-&-daman-&-diu':'Dadra and Nagar Haveli and Daman and Diu',\n",
    "'delhi':'Delhi',\n",
    "'goa':'Goa',\n",
    "'gujarat':'Gujarat',\n",
    "'haryana':'Haryana',\n",
    "'himachal-pradesh':'Himachal Pradesh',\n",
    "'jammu-&-kashmir':'Jammu & Kashmir',\n",
    "'jharkhand':'Jharkhand',\n",
    "'karnataka':'Karnataka',\n",
    "'kerala':'Kerala',\n",
    "'ladakh':'Ladakh',\n",
    "'lakshadweep':'Lakshadweep',\n",
    "'madhya-pradesh':'Madhya Pradesh',\n",
    "'maharashtra':'Maharashtra',\n",
    "'manipur':'Manipur',\n",
    "'meghalaya':'Meghalaya',\n",
    "'mizoram':'Mizoram',\n",
    "'nagaland':'Nagaland',\n",
    "'odisha':'Odisha',\n",
    "'puducherry':'Puducherry',\n",
    "'punjab':'Punjab',\n",
    "'rajasthan':'Rajasthan',\n",
    "'sikkim':'Sikkim',\n",
    "'tamil-nadu':'Tamil Nadu',\n",
    "'telangana':'Telangana',\n",
    "'tripura':'Tripura',\n",
    "'uttar-pradesh':'Uttar Pradesh',\n",
    "'uttarakhand':'Uttarakhand',\n",
    "'west-bengal':'West Bengal'\n",
    "}\n",
    " \n",
    "df_agg_transaction['State'] = df_agg_transaction['State'].map(mappings)\n",
    "df_agg_transaction.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Quater</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  Quater          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": [
    "df_agg_transaction.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Quater</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</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</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</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</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</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  Quater   Brands  Count  Percentage\n",
       "0  Andaman & Nicobar  2018       1   Xiaomi   1665    0.247033\n",
       "1  Andaman & Nicobar  2018       1  Samsung   1445    0.214392\n",
       "2  Andaman & Nicobar  2018       1     Vivo    982    0.145697\n",
       "3  Andaman & Nicobar  2018       1     Oppo    501    0.074332\n",
       "4  Andaman & Nicobar  2018       1  OnePlus    332    0.049258"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path2=r\"E://phonepee//pulse-master//pulse-master//data//aggregated//user//country//india//state//\"\n",
    "agg_user_list=os.listdir(path2)\n",
    "# print(agg_state_list)\n",
    "columns_name={'State':[], 'Year':[],'Quater':[],'Brands':[], 'Count':[], 'Percentage':[]}\n",
    "for i in agg_user_list:\n",
    "    p_i=path2+i+\"//\"\n",
    "    # print(p_i)\n",
    "    year1=os.listdir(p_i)\n",
    "    # print(Agg_yr)\n",
    "    for j in year1:\n",
    "        p_j=p_i+j+\"//\"\n",
    "        agg_yr_list=os.listdir(p_j)\n",
    "        for k in agg_yr_list:\n",
    "            p_k=p_j+k\n",
    "            Data=open(p_k,'r')\n",
    "            D=json.load(Data)\n",
    "            # print(D)\n",
    "            if D['data']['usersByDevice'] is not None:\n",
    "                for z in D['data']['usersByDevice']:\n",
    "                  brand=z['brand']\n",
    "                  count=z['count']\n",
    "                  percentage=z['percentage']\n",
    "                  columns_name['Brands'].append(brand)\n",
    "                  columns_name['Count'].append(count)\n",
    "                  columns_name['Percentage'].append(percentage)\n",
    "                  columns_name['State'].append(i)\n",
    "                  columns_name['Year'].append(j)\n",
    "                  columns_name['Quater'].append(int(k.strip('.json')))\n",
    "\n",
    "df_agg_user=pd.DataFrame(columns_name)\n",
    "mappings = {\n",
    "#     'carrot': 'veg',\n",
    "#     'papaya': 'fruit'\n",
    "'andaman-&-nicobar-islands': 'Andaman & Nicobar',\n",
    "'andhra-pradesh':  'Andhra Pradesh',\n",
    "'arunachal-pradesh':'Arunachal Pradesh',\n",
    "'assam':'Assam',\n",
    "'bihar':\"Bihar\",\n",
    "'chandigarh':'Chandigarh',\n",
    "'chhattisgarh':'Chhattisgarh',\n",
    "'dadra-&-nagar-haveli-&-daman-&-diu':'Dadra and Nagar Haveli and Daman and Diu',\n",
    "'delhi':'Delhi',\n",
    "'goa':'Goa',\n",
    "'gujarat':'Gujarat',\n",
    "'haryana':'Haryana',\n",
    "'himachal-pradesh':'Himachal Pradesh',\n",
    "'jammu-&-kashmir':'Jammu & Kashmir',\n",
    "'jharkhand':'Jharkhand',\n",
    "'karnataka':'Karnataka',\n",
    "'kerala':'Kerala',\n",
    "'ladakh':'Ladakh',\n",
    "'lakshadweep':'Lakshadweep',\n",
    "'madhya-pradesh':'Madhya Pradesh',\n",
    "'maharashtra':'Maharashtra',\n",
    "'manipur':'Manipur',\n",
    "'meghalaya':'Meghalaya',\n",
    "'mizoram':'Mizoram',\n",
    "'nagaland':'Nagaland',\n",
    "'odisha':'Odisha',\n",
    "'puducherry':'Puducherry',\n",
    "'punjab':'Punjab',\n",
    "'rajasthan':'Rajasthan',\n",
    "'sikkim':'Sikkim',\n",
    "'tamil-nadu':'Tamil Nadu',\n",
    "'telangana':'Telangana',\n",
    "'tripura':'Tripura',\n",
    "'uttar-pradesh':'Uttar Pradesh',\n",
    "'uttarakhand':'Uttarakhand',\n",
    "'west-bengal':'West Bengal'\n",
    "}\n",
    " \n",
    "df_agg_user['State'] = df_agg_user['State'].map(mappings)\n",
    "df_agg_user.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Quater</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  Quater   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": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_agg_user.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "path3=r\"E://phonepee//pulse-master//pulse-master//data//map//transaction//hover//country//india//state//\"\n",
    "map_tran_list=os.listdir(path3)\n",
    "# print(agg_state_list)\n",
    "# columns_name={'name':[]}\n",
    "columns_name={'State':[], 'Year':[],'Quater':[],'district':[], 'type':[], 'count':[],'amount':[]}\n",
    "for i in map_tran_list:\n",
    "    p_i=path3+i+\"//\"\n",
    "    # print(p_i)\n",
    "    year1=os.listdir(p_i)\n",
    "    # print(Agg_yr)\n",
    "    for j in year1:\n",
    "        p_j=p_i+j+\"//\"\n",
    "        agg_yr_list=os.listdir(p_j)\n",
    "        for k in agg_yr_list:\n",
    "            p_k=p_j+k\n",
    "            Data=open(p_k,'r')\n",
    "            D=json.load(Data)\n",
    "            D\n",
    "            # if D['data'] is not None:\n",
    "            for z in D['data']['hoverDataList']:\n",
    "                    name=z['name']\n",
    "                    type=z['metric'][0]['type']\n",
    "                    count=z['metric'][0]['count'] \n",
    "                    amount=z['metric'][0]['amount']   \n",
    "                    columns_name['district'].append(name)\n",
    "                    columns_name['type'].append(type)\n",
    "                    columns_name['count'].append(count)\n",
    "                    columns_name['amount'].append(amount)\n",
    "                    columns_name['State'].append(i)\n",
    "                    columns_name['Year'].append(j)\n",
    "                    columns_name['Quater'].append(int(k.strip('.json')))\n",
    "\n",
    "df_map_transaction=pd.DataFrame(columns_name)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>Quater</th>\n",
       "      <th>district</th>\n",
       "      <th>type</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>TOTAL</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>TOTAL</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>TOTAL</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>TOTAL</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>TOTAL</td>\n",
       "      <td>9395</td>\n",
       "      <td>2.394824e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State  Year  Quater                           district  \\\n",
       "0  andaman-&-nicobar-islands  2018       1  north and middle andaman district   \n",
       "1  andaman-&-nicobar-islands  2018       1             south andaman district   \n",
       "2  andaman-&-nicobar-islands  2018       1                  nicobars district   \n",
       "3  andaman-&-nicobar-islands  2018       2  north and middle andaman district   \n",
       "4  andaman-&-nicobar-islands  2018       2             south andaman district   \n",
       "\n",
       "    type  count        amount  \n",
       "0  TOTAL    442  9.316631e+05  \n",
       "1  TOTAL   5688  1.256025e+07  \n",
       "2  TOTAL    528  1.139849e+06  \n",
       "3  TOTAL    825  1.317863e+06  \n",
       "4  TOTAL   9395  2.394824e+07  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_map_transaction.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Quater</th>\n",
       "      <th>district</th>\n",
       "      <th>registeredUsers</th>\n",
       "      <th>appOpens</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar</td>\n",
       "      <td>2018</td>\n",
       "      <td>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</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</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</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</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  Quater                           district  \\\n",
       "0  Andaman & Nicobar  2018       1  north and middle andaman district   \n",
       "1  Andaman & Nicobar  2018       1             south andaman district   \n",
       "2  Andaman & Nicobar  2018       1                  nicobars district   \n",
       "3  Andaman & Nicobar  2018       2  north and middle andaman district   \n",
       "4  Andaman & Nicobar  2018       2             south andaman district   \n",
       "\n",
       "   registeredUsers  appOpens  \n",
       "0              632         0  \n",
       "1             5846         0  \n",
       "2              262         0  \n",
       "3              911         0  \n",
       "4             8143         0  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path4=r\"E://phonepee//pulse-master//pulse-master//data//map//user//hover//country//india//state//\"\n",
    "map_user_list=os.listdir(path4)\n",
    "columns_name={'State':[], 'Year':[],'Quater':[],'district':[], 'registeredUsers':[], 'appOpens':[]}\n",
    "for i in map_user_list:\n",
    "    p_i=path4+i+\"//\"\n",
    "    # print(p_i)\n",
    "    year1=os.listdir(p_i)\n",
    "    # print(Agg_yr)\n",
    "    for j in year1:\n",
    "        p_j=p_i+j+\"//\"\n",
    "        agg_yr_list=os.listdir(p_j)\n",
    "        for k in agg_yr_list:\n",
    "            p_k=p_j+k\n",
    "            Data=open(p_k,'r')\n",
    "            D=json.load(Data)\n",
    "            D\n",
    "            for z in D['data']['hoverData'].items():\n",
    "                # print(z)\n",
    "                district =z[0]\n",
    "                registeredUsers=z[1]['registeredUsers']\n",
    "                appOpens=z[1]['appOpens'] \n",
    "                columns_name['district'].append(district)\n",
    "                columns_name['registeredUsers'].append(registeredUsers)\n",
    "                columns_name['appOpens'].append(appOpens)\n",
    "                columns_name['State'].append(i)\n",
    "                columns_name['Year'].append(j)\n",
    "                columns_name['Quater'].append(int(k.strip('.json')))\n",
    "\n",
    "df_map_user=pd.DataFrame(columns_name)\n",
    "mappings = {\n",
    "#     'carrot': 'veg',\n",
    "#     'papaya': 'fruit'\n",
    "'andaman-&-nicobar-islands': 'Andaman & Nicobar',\n",
    "'andhra-pradesh':  'Andhra Pradesh',\n",
    "'arunachal-pradesh':'Arunachal Pradesh',\n",
    "'assam':'Assam',\n",
    "'bihar':\"Bihar\",\n",
    "'chandigarh':'Chandigarh',\n",
    "'chhattisgarh':'Chhattisgarh',\n",
    "'dadra-&-nagar-haveli-&-daman-&-diu':'Dadra and Nagar Haveli and Daman and Diu',\n",
    "'delhi':'Delhi',\n",
    "'goa':'Goa',\n",
    "'gujarat':'Gujarat',\n",
    "'haryana':'Haryana',\n",
    "'himachal-pradesh':'Himachal Pradesh',\n",
    "'jammu-&-kashmir':'Jammu & Kashmir',\n",
    "'jharkhand':'Jharkhand',\n",
    "'karnataka':'Karnataka',\n",
    "'kerala':'Kerala',\n",
    "'ladakh':'Ladakh',\n",
    "'lakshadweep':'Lakshadweep',\n",
    "'madhya-pradesh':'Madhya Pradesh',\n",
    "'maharashtra':'Maharashtra',\n",
    "'manipur':'Manipur',\n",
    "'meghalaya':'Meghalaya',\n",
    "'mizoram':'Mizoram',\n",
    "'nagaland':'Nagaland',\n",
    "'odisha':'Odisha',\n",
    "'puducherry':'Puducherry',\n",
    "'punjab':'Punjab',\n",
    "'rajasthan':'Rajasthan',\n",
    "'sikkim':'Sikkim',\n",
    "'tamil-nadu':'Tamil Nadu',\n",
    "'telangana':'Telangana',\n",
    "'tripura':'Tripura',\n",
    "'uttar-pradesh':'Uttar Pradesh',\n",
    "'uttarakhand':'Uttarakhand',\n",
    "'west-bengal':'West Bengal'\n",
    "}\n",
    " \n",
    "df_map_user['State'] = df_map_user['State'].map(mappings)\n",
    "df_map_user.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Quater</th>\n",
       "      <th>district</th>\n",
       "      <th>registeredUsers</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  Quater                           district  \\\n",
       "0  andaman-&-nicobar-islands  2018       1  north and middle andaman district   \n",
       "1  andaman-&-nicobar-islands  2018       1             south andaman district   \n",
       "2  andaman-&-nicobar-islands  2018       1                  nicobars district   \n",
       "3  andaman-&-nicobar-islands  2018       2  north and middle andaman district   \n",
       "4  andaman-&-nicobar-islands  2018       2             south andaman district   \n",
       "\n",
       "   registeredUsers  appOpens  \n",
       "0              632         0  \n",
       "1             5846         0  \n",
       "2              262         0  \n",
       "3              911         0  \n",
       "4             8143         0  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_map_user.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "path5=r\"E://phonepee//pulse-master//pulse-master//data//top//transaction//country//india//state//\"\n",
    "top_transaction_list=os.listdir(path5)\n",
    "# print(agg_state_list)\n",
    "columns_name={'State':[], 'Year':[],'Quater':[],'district':[], 'type':[], 'count':[],'amount':[]}\n",
    "for i in top_transaction_list:\n",
    "    p_i=path5+i+\"//\"\n",
    "    # print(p_i)\n",
    "    year1=os.listdir(p_i)\n",
    "    # print(Agg_yr)\n",
    "    for j in year1:\n",
    "        p_j=p_i+j+\"//\"\n",
    "        agg_yr_list=os.listdir(p_j)\n",
    "        for k in agg_yr_list:\n",
    "            p_k=p_j+k \n",
    "            Data=open(p_k,'r')\n",
    "            D=json.load(Data)\n",
    "            D\n",
    "            for z in D['data']['districts']:\n",
    "              Name=z['entityName']\n",
    "              type=z['metric']['type']\n",
    "              count=z['metric']['count']\n",
    "              amount=z['metric']['amount']\n",
    "              columns_name['district'].append(Name)\n",
    "              columns_name['type'].append(type)\n",
    "              columns_name['count'].append(count)\n",
    "              columns_name['amount'].append(amount)\n",
    "              columns_name['State'].append(i)\n",
    "              columns_name['Year'].append(j)\n",
    "              columns_name['Quater'].append(int(k.strip('.json')))\n",
    "df_top_transaction=pd.DataFrame(columns_name)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Quater</th>\n",
       "      <th>district</th>\n",
       "      <th>type</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>south andaman</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>5688</td>\n",
       "      <td>1.256025e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>528</td>\n",
       "      <td>1.139849e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>north and middle andaman</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>442</td>\n",
       "      <td>9.316631e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>south andaman</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>9395</td>\n",
       "      <td>2.394824e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>nicobars</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>1120</td>\n",
       "      <td>3.072437e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State  Year  Quater                  district   type  \\\n",
       "0  andaman-&-nicobar-islands  2018       1             south andaman  TOTAL   \n",
       "1  andaman-&-nicobar-islands  2018       1                  nicobars  TOTAL   \n",
       "2  andaman-&-nicobar-islands  2018       1  north and middle andaman  TOTAL   \n",
       "3  andaman-&-nicobar-islands  2018       2             south andaman  TOTAL   \n",
       "4  andaman-&-nicobar-islands  2018       2                  nicobars  TOTAL   \n",
       "\n",
       "   count        amount  \n",
       "0   5688  1.256025e+07  \n",
       "1    528  1.139849e+06  \n",
       "2    442  9.316631e+05  \n",
       "3   9395  2.394824e+07  \n",
       "4   1120  3.072437e+06  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_top_transaction.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "path6=r\"E://phonepee//pulse-master//pulse-master//data//top//user//country//india//state//\"\n",
    "top_user_list=os.listdir(path6)\n",
    "columns_name={'State':[], 'Year':[],'Quater':[],'district_name':[], 'register_dist':[], 'pincode':[],'registeredUsers':[]}\n",
    "for i in top_user_list:\n",
    "    p_i=path6+i+\"//\"\n",
    "    # print(p_i)\n",
    "    year1=os.listdir(p_i)\n",
    "    for j in year1:\n",
    "        p_j=p_i+j+\"//\"\n",
    "        agg_yr_list=os.listdir(p_j)\n",
    "        for k in agg_yr_list:\n",
    "            p_k=p_j+k \n",
    "            Data=open(p_k,'r')\n",
    "            D=json.load(Data)\n",
    "            # D\n",
    "            for z in D['data']['districts']:\n",
    "              district=z['name']\n",
    "              registeredUsers=z['registeredUsers']\n",
    "\n",
    "              \n",
    "            for y in D['data']['pincodes']:\n",
    "              pincode=y['name']\n",
    "              registeredUsers=y['registeredUsers']\n",
    "              columns_name['district_name'].append(district)\n",
    "              columns_name['register_dist'].append(registeredUsers)\n",
    "              columns_name['pincode'].append(pincode)\n",
    "              columns_name['registeredUsers'].append(registeredUsers)\n",
    "              columns_name['State'].append(i)\n",
    "              columns_name['Year'].append(j)\n",
    "              columns_name['Quater'].append(int(k.strip('.json')))\n",
    "df_top_user=pd.DataFrame(columns_name)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>Quater</th>\n",
       "      <th>district_name</th>\n",
       "      <th>register_dist</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>nicobars</td>\n",
       "      <td>1608</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>nicobars</td>\n",
       "      <td>1108</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>nicobars</td>\n",
       "      <td>1075</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>nicobars</td>\n",
       "      <td>1006</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>nicobars</td>\n",
       "      <td>272</td>\n",
       "      <td>744104</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State  Year  Quater district_name  register_dist  \\\n",
       "0  andaman-&-nicobar-islands  2018       1      nicobars           1608   \n",
       "1  andaman-&-nicobar-islands  2018       1      nicobars           1108   \n",
       "2  andaman-&-nicobar-islands  2018       1      nicobars           1075   \n",
       "3  andaman-&-nicobar-islands  2018       1      nicobars           1006   \n",
       "4  andaman-&-nicobar-islands  2018       1      nicobars            272   \n",
       "\n",
       "  pincode  registeredUsers  \n",
       "0  744103             1608  \n",
       "1  744101             1108  \n",
       "2  744105             1075  \n",
       "3  744102             1006  \n",
       "4  744104              272  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_top_user.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_agg_transaction.to_csv('agg_transaction',index=0)\n",
    "df_agg_user.to_csv('agg_user',index=0)\n",
    "df_map_transaction.to_csv('map_transaction',index=0)\n",
    "df_map_user.to_csv('map_user',index=0)\n",
    "df_top_transaction.to_csv('top_transaction',index=0)\n",
    "df_top_user.to_csv('top_user',index=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install mysql-connector-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydb= mysql.connector.connect(\n",
    "    host ='localhost',\n",
    "    user='root',\n",
    "    password=''\n",
    ")\n",
    "mycursor=mydb.cursor(buffered=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mycursor.execute('create database phonepee')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute('use phonepee')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "##### path1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"\"\"\n",
    "                 create table agg_transaction( State varchar (200),\n",
    "                 Year int,\n",
    "                 Quater int,\n",
    "                 Transaction_type varchar(200),\n",
    "                 Transaction_count int,\n",
    "                 Transaction_amount float )\"\"\")\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mycursor.execute('drop table agg_transaction')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#####path2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"\"\"\n",
    "                 create table agg_user (\n",
    "                     State varchar (200),\n",
    "                     Year int ,\n",
    "                     Quater int,\n",
    "                     Brands varchar (20),\n",
    "                     Count int,\n",
    "                     Percentage float\n",
    "                 )\n",
    "                 \"\"\")\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mycursor.execute('drop table agg_user')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#### path3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"\"\"\n",
    "                 create table map_transaction (\n",
    "                     State varchar (200),\n",
    "                     Year int ,\n",
    "                     Quater int,\n",
    "                     district varchar (100),\n",
    "                     type varchar (25),\n",
    "                     Count int,\n",
    "                     amount float\n",
    "                 )\n",
    "                 \"\"\")\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mycursor.execute('drop table map_transaction')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### path4 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute('''\n",
    "                 create table map_user (\n",
    "                     State varchar (100),\n",
    "                     Year int,\n",
    "                     Quater int,\n",
    "                     district varchar (100),\n",
    "                     registeredUsers int,\n",
    "                     appOpens int)'''\n",
    "                     )\n",
    "mydb.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mycursor.execute('drop table map_user')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "####   path5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"\"\"\n",
    "                 create table top_transaction (\n",
    "                     State varchar (200),\n",
    "                     Year int ,\n",
    "                     Quater int,\n",
    "                     district varchar (100),\n",
    "                     type varchar (25),\n",
    "                     Count int,\n",
    "                     amount float\n",
    "                 )\n",
    "                 \"\"\")\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mycursor.execute('drop table top_transaction')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### path 6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"\"\"\n",
    "                 create table top_user (\n",
    "                     State varchar (200),\n",
    "                     Year int ,\n",
    "                     Quater int,\n",
    "                     district_name varchar (100),\n",
    "                     register_dist int,\n",
    "                     pincode int,\n",
    "                     registeredUsers int\n",
    "                 )\n",
    "                 \"\"\")\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mycursor.execute('drop table top_user')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['State', 'Year', 'Quater', 'Transaction_type', 'Transaction_count',\n",
       "       'Transaction_amount'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a=pd.read_csv('agg_transaction')\n",
    "# a.\n",
    "df_agg_transaction.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#path 1\n",
    "for row in df_agg_transaction.itertuples():\n",
    "    query='''insert into agg_transaction(State,Year,Quater,Transaction_type,Transaction_count,Transaction_amount) \n",
    "                     values (%s,%s,%s,%s,%s,%s)'''\n",
    "    sql_values=(row.State,row.Year,row.Quater,row.Transaction_type,row.Transaction_count,row.Transaction_amount)\n",
    "    mycursor.execute(query,sql_values)\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#paath 2\n",
    "for row in df_agg_user.itertuples():\n",
    "    query='''insert into agg_user(State,Year,Quater,Brands,Count,Percentage) \n",
    "                     values (%s,%s,%s,%s,%s,%s)'''\n",
    "    sql_values=(row.State,row.Year,row.Quater,row.Brands,row.Count,row.Percentage)\n",
    "    mycursor.execute(query,sql_values)\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "#path 3\n",
    "for row in df_map_transaction.itertuples():\n",
    "    query='''insert into map_transaction \n",
    "                     values (%s,%s,%s,%s,%s,%s,%s)'''\n",
    "    sql_values=(row.State,row.Year,row.Quater,row.district,row.type,row.count,row.amount)\n",
    "    mycursor.execute(query,sql_values)\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#path 4\n",
    "for row in df_map_user.itertuples():\n",
    "    query='''insert into map_user \n",
    "                     values (%s,%s,%s,%s,%s,%s)'''\n",
    "    sql_values=(row.State,row.Year,row.Quater,row.district,row.registeredUsers,row.appOpens)\n",
    "    mycursor.execute(query,sql_values)\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "#path 5\n",
    "for row in df_top_transaction.itertuples():\n",
    "    query='''insert into top_transaction \n",
    "                     values (%s,%s,%s,%s,%s,%s,%s)'''\n",
    "    sql_values=(row.State,row.Year,row.Quater,row.district,row.type,row.count,row.amount)\n",
    "    mycursor.execute(query,sql_values)\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "#path 6\n",
    "for row in df_top_user.itertuples():\n",
    "    query='''insert into top_user \n",
    "                     values (%s,%s,%s,%s,%s,%s,%s)'''\n",
    "    sql_values=(row.State,row.Year,row.Quater,row.district_name,row.register_dist,row.pincode,row.registeredUsers)\n",
    "    mycursor.execute(query,sql_values)\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_agg_transaction.to_csv('agg_transaction',index=0)\n",
    "df_agg_user.to_csv('agg_user',index=0)\n",
    "df_map_transaction.to_csv('map_transaction',index=0)\n",
    "df_map_user.to_csv('map_user',index=0)\n",
    "df_top_transaction.to_csv('top_transaction',index=0)\n",
    "df_top_user.to_csv('top_user',index=0)"
   ]
  }
 ],
 "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.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}