PhonePay_Data_Extraction.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0e2e9867",
   "metadata": {},
   "source": [
    "## Aggregated\n",
    "    Data_Aggregated_Transaction_Table 3694\n",
    "    Data_Aggregated_Transaction_Summary_Table 740\n",
    "    Data_Aggregated_User_Table 6919\n",
    "    Data_Aggregated_User_Summary_Table 740\n",
    "\n",
    "## Mapped\n",
    "    Data_Map_Transaction_Table 15356\n",
    "    Data_Map_User_Table 6868\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2548e92",
   "metadata": {},
   "source": [
    "# Data_Aggregated_Transaction_Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "125d819b",
   "metadata": {},
   "outputs": [],
   "source": [
    "Data_Aggregated_Transaction_Table = pd.DataFrame({}) \n",
    "Data_Aggregated_Transaction_Summary_Table = pd.DataFrame({}) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "262a6a15",
   "metadata": {},
   "outputs": [],
   "source": [
    "# INITIALIZATION\n",
    "import os\n",
    "from os import walk\n",
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "\n",
    "def Aggregated_Transaction_Table_fun(state,year,quarter,path):\n",
    "    global Data_Aggregated_Transaction_Table\n",
    "    global Data_Aggregated_Transaction_Summary_Table\n",
    "    dft = pd.read_json(path)\n",
    "    \n",
    "    dataFrom=dft['data']['from']\n",
    "    dataTo=dft['data']['to'] \n",
    "    T_row={'State':state,'Year': year,'Quarter':quarter,'Data From':dataFrom,'Data To':dataTo}\n",
    "    Data_Aggregated_Transaction_Summary_Table=Data_Aggregated_Transaction_Summary_Table.append(T_row,ignore_index = True)\n",
    "    \n",
    "    DAT_temp=dft['data']['transactionData']\n",
    "    if DAT_temp:      \n",
    "        for i in DAT_temp:\n",
    "            DAT_row={ 'Payment Mode':i['name'], 'Total Transactions count':i['paymentInstruments'][0]['count'], 'Total Amount':i['paymentInstruments'][0]['amount'],'Quarter':quarter,'Year': year,'State':state}  \n",
    "            Data_Aggregated_Transaction_Table = Data_Aggregated_Transaction_Table.append(DAT_row, ignore_index = True)\n",
    "        \n",
    "# PATH FOR ALL STATES IN AGGREGATED TRANSACTIONS\n",
    "t_s= r'C:\\Users\\91939\\2MY_DATA_SCIENCE\\12_My_DS_Tasks\\project2_Phonepay\\data\\aggregated\\transaction\\country\\india\\state'\n",
    "t_path = r'C:\\Users\\91939\\2MY_DATA_SCIENCE\\12_My_DS_Tasks\\project2_Phonepay\\data\\aggregated\\transaction\\country\\india\\state'\n",
    "t_states = os.listdir(t_path) # NAMES OF ALL DIRECTORIES IN STATES (36 STATES)\n",
    "\n",
    "for i in t_states:                     # ITERATE ALL STATES\n",
    "    #print(i)\n",
    "    p=t_s+'\\\\'+i                       # PICK ONE STATE PATH\n",
    "    states_year=os.listdir(p)          # PICK 2018 TO 2022 DIRECTORIES IN ONE STATE\n",
    "    for j in states_year:              # ITERATE YEARS 2018 TO 2022\n",
    "        #print(j)\n",
    "        pt=p+'\\\\'+j                    # PICK ONE YEAR PATH\n",
    "        f=[]\n",
    "        for (dirpath, dirnames, filenames) in walk(pt):\n",
    "            f.extend(filenames)        # PICK ALL THE QFILES IN SELECTED YEAR \n",
    "            break\n",
    "        for k in f:                    # ITERATE THROUGH QFILES 1.JSON TO 4.JSON\n",
    "            fp=pt+'\\\\'+k               # PICK ONE QFILE PATH\n",
    "            fn=Path(fp).stem           # NOTE DOWN QUARTER\n",
    "            #print(i,j,fn)\n",
    "            Aggregated_Transaction_Table_fun(i,j,fn,fp) # CALL FUNCTION WITH STATE,YEAR,QFILE,QUARTER\n",
    "            #print(fp) \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "607823a0",
   "metadata": {},
   "source": [
    "#### Data_Aggregated_Transaction_Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "90eb5752",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3694\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>Payment Mode</th>\n",
       "      <th>Total Transactions count</th>\n",
       "      <th>Total Amount</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Year</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Recharge &amp; bill payments</td>\n",
       "      <td>4200</td>\n",
       "      <td>1.845307e+06</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Peer-to-peer payments</td>\n",
       "      <td>1871</td>\n",
       "      <td>1.213866e+07</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Merchant payments</td>\n",
       "      <td>298</td>\n",
       "      <td>4.525072e+05</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Financial Services</td>\n",
       "      <td>33</td>\n",
       "      <td>1.060142e+04</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Others</td>\n",
       "      <td>256</td>\n",
       "      <td>1.846899e+05</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Payment Mode  Total Transactions count  Total Amount Quarter  \\\n",
       "0  Recharge & bill payments                      4200  1.845307e+06       1   \n",
       "1     Peer-to-peer payments                      1871  1.213866e+07       1   \n",
       "2         Merchant payments                       298  4.525072e+05       1   \n",
       "3        Financial Services                        33  1.060142e+04       1   \n",
       "4                    Others                       256  1.846899e+05       1   \n",
       "\n",
       "   Year                      State  \n",
       "0  2018  andaman-&-nicobar-islands  \n",
       "1  2018  andaman-&-nicobar-islands  \n",
       "2  2018  andaman-&-nicobar-islands  \n",
       "3  2018  andaman-&-nicobar-islands  \n",
       "4  2018  andaman-&-nicobar-islands  "
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Data_Aggregated_Transaction_Table.to_csv('Data_Aggregated_Transaction_Table.csv',index=False)  \n",
    "print(len(Data_Aggregated_Transaction_Table))\n",
    "Data_Aggregated_Transaction_Table.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7266942",
   "metadata": {},
   "source": [
    "#### Data_Aggregated_Transaction_Summary_Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "b9f27722",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "740\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>Data From</th>\n",
       "      <th>Data To</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>1514745000000</td>\n",
       "      <td>1522175400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>1522521000000</td>\n",
       "      <td>1530124200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "      <td>1530383400000</td>\n",
       "      <td>1538073000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>1538332200000</td>\n",
       "      <td>1545935400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>1546281000000</td>\n",
       "      <td>1553711400000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State  Year Quarter      Data From        Data To\n",
       "0  andaman-&-nicobar-islands  2018       1  1514745000000  1522175400000\n",
       "1  andaman-&-nicobar-islands  2018       2  1522521000000  1530124200000\n",
       "2  andaman-&-nicobar-islands  2018       3  1530383400000  1538073000000\n",
       "3  andaman-&-nicobar-islands  2018       4  1538332200000  1545935400000\n",
       "4  andaman-&-nicobar-islands  2019       1  1546281000000  1553711400000"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Data_Aggregated_Transaction_Summary_Table.to_csv('Data_Aggregated_Transaction_Summary_Table.csv',index=False)  \n",
    "print(len(Data_Aggregated_Transaction_Summary_Table))\n",
    "Data_Aggregated_Transaction_Summary_Table.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "712b2ab5",
   "metadata": {},
   "source": [
    "# Data_Aggregated_User_Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "30a69ff3",
   "metadata": {},
   "outputs": [],
   "source": [
    "Data_Aggregated_User_Table = pd.DataFrame({}) \n",
    "Data_Aggregated_User_Summary_Table = pd.DataFrame({}) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "f5108eaf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# INITIALIZATION\n",
    "import os\n",
    "from os import walk\n",
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "\n",
    "def Aggregated_User_Table_fun(state,year,quarter,path):\n",
    "    global Data_Aggregated_User_Table\n",
    "    global Data_Aggregated_User_Summary_Table\n",
    "    dfu = pd.read_json(path)\n",
    "    \n",
    "    registeredUsers=dfu['data']['aggregated']['registeredUsers']\n",
    "    appOpens=dfu['data']['aggregated']['appOpens']\n",
    "    U_row={'State':state,'Year': year,'Quarter':quarter,'Registered Users':registeredUsers,'AppOpenings':appOpens}\n",
    "    Data_Aggregated_User_Summary_Table= Data_Aggregated_User_Summary_Table.append(U_row, ignore_index = True )\n",
    "\n",
    "    DAU_temp=dfu['data']['usersByDevice']\n",
    "    if DAU_temp:  \n",
    "        for i in DAU_temp:\n",
    "            DAU_row={ 'Brand Name':i['brand'], 'Registered Users Count':i['count'], 'Percentage Share of Brand':i['percentage'],'Quarter':quarter,'Year': year,'State':state}  \n",
    "            Data_Aggregated_User_Table = Data_Aggregated_User_Table.append(DAU_row, ignore_index = True)\n",
    "\n",
    "s= r'C:\\Users\\91939\\2MY_DATA_SCIENCE\\12_My_DS_Tasks\\project2_Phonepay\\data\\aggregated\\user\\country\\india\\state'\n",
    "path = r'C:\\Users\\91939\\2MY_DATA_SCIENCE\\12_My_DS_Tasks\\project2_Phonepay\\data\\aggregated\\user\\country\\india\\state'\n",
    "states = os.listdir(u_path)\n",
    "\n",
    "for i in states:\n",
    "    #print(i)\n",
    "    p=s+'\\\\'+i\n",
    "    states_year=os.listdir(p)\n",
    "    for j in states_year:\n",
    "        #print(j)\n",
    "        pt=p+'\\\\'+j\n",
    "        f=[]\n",
    "        for (dirpath, dirnames, filenames) in walk(pt):\n",
    "            f.extend(filenames)\n",
    "            break\n",
    "        for k in f:\n",
    "            fp=pt+'\\\\'+k            \n",
    "            fn=Path(fp).stem\n",
    "            #print(i,j,fn)\n",
    "            Aggregated_User_Table_fun(i,j,fn,fp)\n",
    "            #print(fp)  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db79bc88",
   "metadata": {},
   "source": [
    "#### Data_Aggregated_User_Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "0498966e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6919\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>Brand Name</th>\n",
       "      <th>Registered Users Count</th>\n",
       "      <th>Percentage Share of Brand</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Year</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Xiaomi</td>\n",
       "      <td>1665</td>\n",
       "      <td>0.247033</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>1445</td>\n",
       "      <td>0.214392</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Vivo</td>\n",
       "      <td>982</td>\n",
       "      <td>0.145697</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Oppo</td>\n",
       "      <td>501</td>\n",
       "      <td>0.074332</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>OnePlus</td>\n",
       "      <td>332</td>\n",
       "      <td>0.049258</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Brand Name  Registered Users Count  Percentage Share of Brand Quarter  Year  \\\n",
       "0     Xiaomi                    1665                   0.247033       1  2018   \n",
       "1    Samsung                    1445                   0.214392       1  2018   \n",
       "2       Vivo                     982                   0.145697       1  2018   \n",
       "3       Oppo                     501                   0.074332       1  2018   \n",
       "4    OnePlus                     332                   0.049258       1  2018   \n",
       "\n",
       "                       State  \n",
       "0  andaman-&-nicobar-islands  \n",
       "1  andaman-&-nicobar-islands  \n",
       "2  andaman-&-nicobar-islands  \n",
       "3  andaman-&-nicobar-islands  \n",
       "4  andaman-&-nicobar-islands  "
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Data_Aggregated_User_Table.to_csv('Data_Aggregated_User_Table.csv',index=False) \n",
    "print(len(Data_Aggregated_User_Table))\n",
    "Data_Aggregated_User_Table.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "363c5253",
   "metadata": {},
   "source": [
    "#### Data_Aggregated_User_Summary_Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "e1fc6b08",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "740\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>Registered Users</th>\n",
       "      <th>AppOpenings</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>6740</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>2</td>\n",
       "      <td>9405</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>3</td>\n",
       "      <td>12149</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>4</td>\n",
       "      <td>15222</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>18596</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State  Year Quarter  Registered Users  AppOpenings\n",
       "0  andaman-&-nicobar-islands  2018       1              6740            0\n",
       "1  andaman-&-nicobar-islands  2018       2              9405            0\n",
       "2  andaman-&-nicobar-islands  2018       3             12149            0\n",
       "3  andaman-&-nicobar-islands  2018       4             15222            0\n",
       "4  andaman-&-nicobar-islands  2019       1             18596            0"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Data_Aggregated_User_Summary_Table.to_csv('Data_Aggregated_User_Summary_Table.csv',index=False) \n",
    "print(len(Data_Aggregated_User_Summary_Table))\n",
    "Data_Aggregated_User_Summary_Table.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a974d21",
   "metadata": {},
   "source": [
    "# Data_Map_Transaction_Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "bdb84ccb",
   "metadata": {},
   "outputs": [],
   "source": [
    "Data_Map_Transaction_Table = pd.DataFrame({})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "eca737d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# INITIALIZATION\n",
    "import os\n",
    "from os import walk\n",
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "\n",
    "def Data_Map_Transaction_Table_Fun(state,year,quarter,path):\n",
    "    global Data_Map_Transaction_Table\n",
    "    dfmt = pd.read_json(path)\n",
    "    DMT_temp=dfmt['data']['hoverDataList'] \n",
    "    if DMT_temp:\n",
    "        for i in DMT_temp:\n",
    "            DMT_row={ 'Place Name':i['name'], 'Total Transactions count':i['metric'][0]['count'], 'Total Amount':i['metric'][0]['amount'],'Quarter':quarter,'Year': year,'State':state}  \n",
    "            Data_Map_Transaction_Table = Data_Map_Transaction_Table.append(DMT_row, ignore_index = True)  \n",
    "\n",
    "t_s= r'C:\\Users\\91939\\2MY_DATA_SCIENCE\\12_My_DS_Tasks\\project2_Phonepay\\data\\map\\transaction\\hover\\country\\india\\state'\n",
    "t_path = r'C:\\Users\\91939\\2MY_DATA_SCIENCE\\12_My_DS_Tasks\\project2_Phonepay\\data\\map\\transaction\\hover\\country\\india\\state'\n",
    "t_states = os.listdir(t_path) \n",
    "\n",
    "for i in t_states:\n",
    "    #print(i)\n",
    "    p=t_s+'\\\\'+i\n",
    "    states_year=os.listdir(p)\n",
    "    for j in states_year:\n",
    "        #print(j)\n",
    "        pt=p+'\\\\'+j\n",
    "        f=[]\n",
    "        for (dirpath, dirnames, filenames) in walk(pt):\n",
    "            f.extend(filenames)\n",
    "            break\n",
    "        for k in f:\n",
    "            fp=pt+'\\\\'+k            \n",
    "            fn=Path(fp).stem\n",
    "            #print(i,j,fn)\n",
    "            Data_Map_Transaction_Table_Fun(i,j,fn,fp)\n",
    "            #print(fp) \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "770cd335",
   "metadata": {},
   "source": [
    "#### Data_Map_Transaction_Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "e958d1ee",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "15356\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>Place Name</th>\n",
       "      <th>Total Transactions count</th>\n",
       "      <th>Total Amount</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Year</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>north and middle andaman district</td>\n",
       "      <td>442</td>\n",
       "      <td>9.316631e+05</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>south andaman district</td>\n",
       "      <td>5688</td>\n",
       "      <td>1.256025e+07</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>nicobars district</td>\n",
       "      <td>528</td>\n",
       "      <td>1.139849e+06</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>north and middle andaman district</td>\n",
       "      <td>825</td>\n",
       "      <td>1.317863e+06</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>south andaman district</td>\n",
       "      <td>9395</td>\n",
       "      <td>2.394824e+07</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Place Name  Total Transactions count  Total Amount  \\\n",
       "0  north and middle andaman district                       442  9.316631e+05   \n",
       "1             south andaman district                      5688  1.256025e+07   \n",
       "2                  nicobars district                       528  1.139849e+06   \n",
       "3  north and middle andaman district                       825  1.317863e+06   \n",
       "4             south andaman district                      9395  2.394824e+07   \n",
       "\n",
       "  Quarter  Year                      State  \n",
       "0       1  2018  andaman-&-nicobar-islands  \n",
       "1       1  2018  andaman-&-nicobar-islands  \n",
       "2       1  2018  andaman-&-nicobar-islands  \n",
       "3       2  2018  andaman-&-nicobar-islands  \n",
       "4       2  2018  andaman-&-nicobar-islands  "
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Data_Map_Transaction_Table.to_csv('Data_Map_Transaction_Table.csv',index=False) \n",
    "print(len(Data_Map_Transaction_Table))\n",
    "Data_Map_Transaction_Table.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1abc2798",
   "metadata": {},
   "source": [
    "# Data_Map_User_Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "1667dcc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "Data_Map_User_Table = pd.DataFrame({}) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "d6d957bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# INITIALIZATION\n",
    "import os\n",
    "from os import walk\n",
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "\n",
    "def Data_Map_User_Table_Fun(state,year,quarter,path):\n",
    "    global Data_Map_User_Table\n",
    "    dfmu = pd.read_json(path)\n",
    "    \n",
    "    DMU_temp=dfmu['data']['hoverData'] \n",
    "    if DMU_temp:\n",
    "        for i in DMU_temp:\n",
    "            #print(i, DMU_temp[i]['registeredUsers'],DMU_temp[i]['appOpens'])\n",
    "            DMU_row={ 'Place Name':i, 'Registered Users Count':DMU_temp[i]['registeredUsers'], 'App Openings':DMU_temp[i]['appOpens'],'Quarter':quarter,'Year': year,'State':state}  \n",
    "            Data_Map_User_Table = Data_Map_User_Table.append(DMU_row, ignore_index = True)\n",
    "\n",
    "t_s= r'C:\\Users\\91939\\2MY_DATA_SCIENCE\\12_My_DS_Tasks\\project2_Phonepay\\data\\map\\user\\hover\\country\\india\\state'\n",
    "t_path = r'C:\\Users\\91939\\2MY_DATA_SCIENCE\\12_My_DS_Tasks\\project2_Phonepay\\data\\map\\user\\hover\\country\\india\\state'\n",
    "t_states = os.listdir(t_path) \n",
    "\n",
    "for i in t_states:\n",
    "    #print(i)\n",
    "    p=t_s+'\\\\'+i\n",
    "    states_year=os.listdir(p)\n",
    "    for j in states_year:\n",
    "        #print(j)\n",
    "        pt=p+'\\\\'+j\n",
    "        f=[]\n",
    "        for (dirpath, dirnames, filenames) in walk(pt):\n",
    "            f.extend(filenames)\n",
    "            break\n",
    "        for k in f:\n",
    "            fp=pt+'\\\\'+k            \n",
    "            fn=Path(fp).stem\n",
    "            #print(i,j,fn)\n",
    "            Data_Map_User_Table_Fun(i,j,fn,fp)\n",
    "            #print(fp) \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c011e4f",
   "metadata": {},
   "source": [
    "#### Data_Map_User_Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "1e35d50b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6868\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>Place Name</th>\n",
       "      <th>Registered Users Count</th>\n",
       "      <th>App Openings</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Year</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>north and middle andaman district</td>\n",
       "      <td>632</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>south andaman district</td>\n",
       "      <td>5846</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>nicobars district</td>\n",
       "      <td>262</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>north and middle andaman district</td>\n",
       "      <td>911</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>south andaman district</td>\n",
       "      <td>8143</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Place Name  Registered Users Count  App Openings  \\\n",
       "0  north and middle andaman district                     632             0   \n",
       "1             south andaman district                    5846             0   \n",
       "2                  nicobars district                     262             0   \n",
       "3  north and middle andaman district                     911             0   \n",
       "4             south andaman district                    8143             0   \n",
       "\n",
       "  Quarter  Year                      State  \n",
       "0       1  2018  andaman-&-nicobar-islands  \n",
       "1       1  2018  andaman-&-nicobar-islands  \n",
       "2       1  2018  andaman-&-nicobar-islands  \n",
       "3       2  2018  andaman-&-nicobar-islands  \n",
       "4       2  2018  andaman-&-nicobar-islands  "
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Data_Map_User_Table.to_csv('Data_Map_User_Table.csv',index=False) \n",
    "print(len(Data_Map_User_Table))\n",
    "Data_Map_User_Table.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbff6a43",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}