Cloning, Extraction & Pushing into MySQL.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing necessary libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from git import Repo\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import mysql.connector"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cloning from repository"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C:\\ProgramFiles\\Projects\\Phonepe_pulse\\Miscellaneous\\Pulse\\data\n"
     ]
    }
   ],
   "source": [
    "repo_url = \"https://github.com/PhonePe/pulse.git\"\n",
    "clone_path = r\"C:\\ProgramFiles\\Projects\\Phonepe_pulse\\Miscellaneous\"\n",
    "\n",
    "if not os.path.exists(clone_path):\n",
    "    os.makedirs(clone_path)\n",
    "\n",
    "repo_path = os.path.join(clone_path, os.path.basename(repo_url).removesuffix('.git').title())\n",
    "\n",
    "Repo.clone_from(repo_url, repo_path)\n",
    "\n",
    "directory = os.path.join(repo_path, 'data')\n",
    "print(directory)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Renaming sub-directories and Extracting necessary paths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to rename messy state names in a proper format\n",
    "\n",
    "def rename(directory):\n",
    "    for root, dirs, files in os.walk(directory):\n",
    "        if 'state' in dirs:\n",
    "            state_dir = os.path.join(root, 'state')\n",
    "            for state_folder in os.listdir(state_dir):\n",
    "                # rename the state folder\n",
    "                old_path = os.path.join(state_dir, state_folder)\n",
    "                new_path = os.path.join(state_dir, state_folder.title().replace('-', ' ').replace('&', 'and'))\n",
    "                os.rename(old_path, new_path)\n",
    "    print(\"Renamed all sub-directories successfully\")\n",
    "                \n",
    "# Function to extract all paths that has sub-directory in the name of 'state'\n",
    "\n",
    "def extract_paths(directory):\n",
    "    path_list = []\n",
    "    for root, dirs, files in os.walk(directory):\n",
    "        if os.path.basename(root) == 'state':\n",
    "            path_list.append(root.replace('\\\\', '/'))\n",
    "    return path_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Renamed all sub-directories successfully\n"
     ]
    }
   ],
   "source": [
    "rename(directory)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['C:/ProgramFiles/Projects/Phonepe_pulse/Miscellaneous/Pulse/data/aggregated/transaction/country/india/state',\n",
       " 'C:/ProgramFiles/Projects/Phonepe_pulse/Miscellaneous/Pulse/data/aggregated/user/country/india/state',\n",
       " 'C:/ProgramFiles/Projects/Phonepe_pulse/Miscellaneous/Pulse/data/map/transaction/hover/country/india/state',\n",
       " 'C:/ProgramFiles/Projects/Phonepe_pulse/Miscellaneous/Pulse/data/map/user/hover/country/india/state',\n",
       " 'C:/ProgramFiles/Projects/Phonepe_pulse/Miscellaneous/Pulse/data/top/transaction/country/india/state',\n",
       " 'C:/ProgramFiles/Projects/Phonepe_pulse/Miscellaneous/Pulse/data/top/user/country/india/state']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_directories = extract_paths(directory)\n",
    "state_directories"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating dataframes from cloned json files"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 1. Aggregate Transaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_path = state_directories[0]\n",
    "state_list = os.listdir(state_path)\n",
    "agg_trans_dict = {\n",
    "                  'State': [], 'Year': [], 'Quarter': [], 'Transaction_type': [],\n",
    "                  'Transaction_count': [], 'Transaction_amount': []\n",
    "                  }\n",
    "\n",
    "for state in state_list:\n",
    "    year_path = state_path + '/' + state + '/'\n",
    "    year_list = os.listdir(year_path)\n",
    "    \n",
    "    for year in year_list:\n",
    "        quarter_path = year_path + year + '/'\n",
    "        quarter_list = os.listdir(quarter_path)\n",
    "        \n",
    "        for quarter in quarter_list:\n",
    "            json_path = quarter_path + quarter\n",
    "            df = pd.read_json(json_path)\n",
    "            \n",
    "            try:\n",
    "                for transaction_data in df['data']['transactionData']:\n",
    "                    \n",
    "                    type = transaction_data['name']\n",
    "                    count = transaction_data['paymentInstruments'][0]['count']\n",
    "                    amount = transaction_data['paymentInstruments'][0]['amount']\n",
    "                    \n",
    "                    # Appending to agg_trans_dict\n",
    "                    \n",
    "                    agg_trans_dict['State'].append(state)\n",
    "                    agg_trans_dict['Year'].append(year)\n",
    "                    agg_trans_dict['Quarter'].append(int(quarter.removesuffix('.json')))\n",
    "                    agg_trans_dict['Transaction_type'].append(type)\n",
    "                    agg_trans_dict['Transaction_count'].append(count)\n",
    "                    agg_trans_dict['Transaction_amount'].append(amount)\n",
    "            except:\n",
    "                pass\n",
    "            \n",
    "agg_trans_df = pd.DataFrame(agg_trans_dict)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 2. Aggregate User"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_path = state_directories[1]\n",
    "state_list = os.listdir(state_path)\n",
    "agg_user_dict = {\n",
    "                 'State': [], 'Year': [], 'Quarter': [], 'Brand': [],\n",
    "                 'Transaction_count': [], 'Percentage': []\n",
    "                 }\n",
    "\n",
    "for state in state_list:\n",
    "    year_path = state_path + '/' + state + '/'\n",
    "    year_list = os.listdir(year_path)\n",
    "    \n",
    "    for year in year_list:\n",
    "        quarter_path = year_path + year + '/'\n",
    "        quarter_list = os.listdir(quarter_path)\n",
    "        \n",
    "        for quarter in quarter_list:\n",
    "            json_path = quarter_path + quarter\n",
    "            df = pd.read_json(json_path)\n",
    "            \n",
    "            try:\n",
    "                for user_data in df['data']['usersByDevice']:\n",
    "\n",
    "                    brand = user_data['brand']\n",
    "                    count = user_data['count']\n",
    "                    percent = user_data['percentage']\n",
    "                    \n",
    "                    # Appending to agg_user_dict\n",
    "                    \n",
    "                    agg_user_dict['State'].append(state)\n",
    "                    agg_user_dict['Year'].append(year)\n",
    "                    agg_user_dict['Quarter'].append(int(quarter.removesuffix('.json')))\n",
    "                    agg_user_dict['Brand'].append(brand)\n",
    "                    agg_user_dict['Transaction_count'].append(count)\n",
    "                    agg_user_dict['Percentage'].append(percent)\n",
    "            except:\n",
    "                pass\n",
    "\n",
    "agg_user_df = pd.DataFrame(agg_user_dict)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 3. Map Transaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_path = state_directories[2]\n",
    "state_list = os.listdir(state_path)\n",
    "map_trans_dict = {\n",
    "                    'State': [], 'Year': [], 'Quarter': [], 'District': [],\n",
    "                    'Transaction_count': [], 'Transaction_amount': []\n",
    "                    }\n",
    "\n",
    "for state in state_list:\n",
    "    year_path = state_path + '/' + state + '/'\n",
    "    year_list = os.listdir(year_path)\n",
    "    \n",
    "    for year in year_list:\n",
    "        quarter_path = year_path + year + '/'\n",
    "        quarter_list = os.listdir(quarter_path)\n",
    "        \n",
    "        for quarter in quarter_list:\n",
    "            json_path = quarter_path + quarter\n",
    "            df = pd.read_json(json_path)\n",
    "            \n",
    "            try:\n",
    "                for transaction_data in df['data']['hoverDataList']:\n",
    "                   \n",
    "                    district = transaction_data['name']\n",
    "                    count = transaction_data['metric'][0]['count']\n",
    "                    amount = transaction_data['metric'][0]['amount']\n",
    "                    \n",
    "                    # Appending to map_trans_dict\n",
    "                    \n",
    "                    map_trans_dict['State'].append(state)\n",
    "                    map_trans_dict['Year'].append(year)\n",
    "                    map_trans_dict['Quarter'].append(int(quarter.removesuffix('.json'))) \n",
    "                    map_trans_dict['District'].append(district.removesuffix(' district').title().replace(' And', ' and').replace('andaman', 'Andaman'))\n",
    "                    map_trans_dict['Transaction_count'].append(count)\n",
    "                    map_trans_dict['Transaction_amount'].append(amount)\n",
    "            except:\n",
    "                pass\n",
    "\n",
    "map_trans_df = pd.DataFrame(map_trans_dict)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 4. Map User"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_path = state_directories[3]\n",
    "state_list = os.listdir(state_path)\n",
    "map_user_dict = {\n",
    "                 'State': [], 'Year': [], 'Quarter': [], 'District': [],\n",
    "                 'Registered_users': [], 'App_opens': []\n",
    "                 }\n",
    "\n",
    "for state in state_list:\n",
    "    year_path = state_path + '/' + state + '/'\n",
    "    year_list = os.listdir(year_path)\n",
    "    \n",
    "    for year in year_list:\n",
    "        quarter_path = year_path + year + '/'\n",
    "        quarter_list = os.listdir(quarter_path)\n",
    "        \n",
    "        for quarter in quarter_list:\n",
    "            json_path = quarter_path + quarter\n",
    "            df = pd.read_json(json_path)\n",
    "            \n",
    "            try:\n",
    "                for district, user_data in df['data']['hoverData'].items():\n",
    "                    \n",
    "                    reg_user_count = user_data['registeredUsers']\n",
    "                    app_open_count = user_data['appOpens']\n",
    "                    \n",
    "                    # Appending to map_user_dict\n",
    "                    \n",
    "                    map_user_dict['State'].append(state)\n",
    "                    map_user_dict['Year'].append(year)\n",
    "                    map_user_dict['Quarter'].append(int(quarter.removesuffix('.json')))\n",
    "                    map_user_dict['District'].append(district.removesuffix(' district').title().replace(' And', ' and').replace('andaman', 'Andaman'))\n",
    "                    map_user_dict['Registered_users'].append(reg_user_count)\n",
    "                    map_user_dict['App_opens'].append(app_open_count)\n",
    "            except:\n",
    "                pass\n",
    "\n",
    "map_user_df = pd.DataFrame(map_user_dict)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 5. Top Transaction District-wise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_path = state_directories[4]\n",
    "state_list = os.listdir(state_path)\n",
    "top_trans_dist_dict = {\n",
    "                        'State': [], 'Year': [], 'Quarter': [], 'District': [],\n",
    "                        'Transaction_count': [], 'Transaction_amount': []\n",
    "                        }\n",
    "\n",
    "for state in state_list:\n",
    "    year_path = state_path + '/' + state + '/'\n",
    "    year_list = os.listdir(year_path)\n",
    "    \n",
    "    for year in year_list:\n",
    "        quarter_path = year_path + year + '/'\n",
    "        quarter_list = os.listdir(quarter_path)\n",
    "        \n",
    "        for quarter in quarter_list:\n",
    "            json_path = quarter_path + quarter\n",
    "            df = pd.read_json(json_path)\n",
    "            \n",
    "            try:\n",
    "                for district_data in df['data']['districts']:\n",
    "                    \n",
    "                    name = district_data['entityName']\n",
    "                    count = district_data['metric']['count']\n",
    "                    amount = district_data['metric']['amount']\n",
    "                    \n",
    "                    # Appending to top_trans_dist_dict\n",
    "                    \n",
    "                    top_trans_dist_dict['State'].append(state)\n",
    "                    top_trans_dist_dict['Year'].append(year)\n",
    "                    top_trans_dist_dict['Quarter'].append(int(quarter.removesuffix('.json')))                    \n",
    "                    top_trans_dist_dict['District'].append(name.title().replace(' And', ' and').replace('andaman', 'Andaman'))\n",
    "                    top_trans_dist_dict['Transaction_count'].append(count)\n",
    "                    top_trans_dist_dict['Transaction_amount'].append(amount)\n",
    "            except:\n",
    "                pass\n",
    "\n",
    "top_trans_dist_df = pd.DataFrame(top_trans_dist_dict)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 6. Top Transaction Pincode-wise "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_path = state_directories[4]\n",
    "state_list = os.listdir(state_path)\n",
    "top_trans_pin_dict = {\n",
    "                        'State': [], 'Year': [], 'Quarter': [], 'Pincode': [],\n",
    "                        'Transaction_count': [], 'Transaction_amount': []\n",
    "                        }\n",
    "\n",
    "for state in state_list:\n",
    "    year_path = state_path + '/' + state + '/'\n",
    "    year_list = os.listdir(year_path)\n",
    "    \n",
    "    for year in year_list:\n",
    "        quarter_path = year_path + year + '/'\n",
    "        quarter_list = os.listdir(quarter_path)\n",
    "        \n",
    "        for quarter in quarter_list:\n",
    "            json_path = quarter_path + quarter\n",
    "            df = pd.read_json(json_path)\n",
    "            \n",
    "            try:\n",
    "                for regional_data in df['data']['pincodes']:\n",
    "                    \n",
    "                    name = regional_data['entityName']\n",
    "                    count = regional_data['metric']['count']\n",
    "                    amount = regional_data['metric']['amount']\n",
    "                    \n",
    "                    # Appending to top_trans_pin_dict\n",
    "                    \n",
    "                    top_trans_pin_dict['State'].append(state)\n",
    "                    top_trans_pin_dict['Year'].append(year)\n",
    "                    top_trans_pin_dict['Quarter'].append(int(quarter.removesuffix('.json')))                    \n",
    "                    top_trans_pin_dict['Pincode'].append(name)\n",
    "                    top_trans_pin_dict['Transaction_count'].append(count)\n",
    "                    top_trans_pin_dict['Transaction_amount'].append(amount)\n",
    "            except:\n",
    "                pass\n",
    "\n",
    "top_trans_pin_df = pd.DataFrame(top_trans_pin_dict)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 7. Top User District-wise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_path = state_directories[5]\n",
    "state_list = os.listdir(state_path)\n",
    "top_user_dist_dict = {\n",
    "                        'State': [], 'Year': [], 'Quarter': [],\n",
    "                        'District': [], 'Registered_users': []\n",
    "                        }\n",
    "\n",
    "for state in state_list:\n",
    "    year_path = state_path + '/' + state + '/'\n",
    "    year_list = os.listdir(year_path)\n",
    "    \n",
    "    for year in year_list:\n",
    "        quarter_path = year_path + year + '/'\n",
    "        quarter_list = os.listdir(quarter_path)\n",
    "        \n",
    "        for quarter in quarter_list:\n",
    "            json_path = quarter_path + quarter\n",
    "            df = pd.read_json(json_path)\n",
    "            \n",
    "            try:\n",
    "                for district_data in df['data']['districts']:\n",
    "                    \n",
    "                    name = district_data['name']\n",
    "                    count = district_data['registeredUsers']\n",
    "                    \n",
    "                    # Appending to top_user_dist_dict\n",
    "                    \n",
    "                    top_user_dist_dict['State'].append(state)\n",
    "                    top_user_dist_dict['Year'].append(year)\n",
    "                    top_user_dist_dict['Quarter'].append(int(quarter.removesuffix('.json')))\n",
    "                    top_user_dist_dict['District'].append(name.title().replace(' And', ' and').replace('andaman', 'Andaman'))\n",
    "                    top_user_dist_dict['Registered_users'].append(count)\n",
    "            except:\n",
    "                pass\n",
    "\n",
    "top_user_dist_df = pd.DataFrame(top_user_dist_dict)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 8. Top User Pincode-wise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_path = state_directories[5]\n",
    "state_list = os.listdir(state_path)\n",
    "top_user_pin_dict = {\n",
    "                        'State': [], 'Year': [], 'Quarter': [],\n",
    "                        'Pincode': [], 'Registered_users': []\n",
    "                        }\n",
    "\n",
    "for state in state_list:\n",
    "    year_path = state_path + '/' + state + '/'\n",
    "    year_list = os.listdir(year_path)\n",
    "    \n",
    "    for year in year_list:\n",
    "        quarter_path = year_path + year + '/'\n",
    "        quarter_list = os.listdir(quarter_path)\n",
    "        \n",
    "        for quarter in quarter_list:\n",
    "            json_path = quarter_path + quarter\n",
    "            df = pd.read_json(json_path)\n",
    "            \n",
    "            try:\n",
    "                for regional_data in df['data']['pincodes']:\n",
    "                    \n",
    "                    name = regional_data['name']\n",
    "                    count = regional_data['registeredUsers']\n",
    "                    \n",
    "                    # Appending to top_user_pin_dict\n",
    "                    \n",
    "                    top_user_pin_dict['State'].append(state)\n",
    "                    top_user_pin_dict['Year'].append(year)\n",
    "                    top_user_pin_dict['Quarter'].append(int(quarter.removesuffix('.json')))\n",
    "                    top_user_pin_dict['Pincode'].append(name)\n",
    "                    top_user_pin_dict['Registered_users'].append(count)\n",
    "            except:\n",
    "                pass\n",
    "\n",
    "top_user_pin_df = pd.DataFrame(top_user_pin_dict)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List of dataframes created so far"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['agg_trans_df',\n",
       " 'agg_user_df',\n",
       " 'map_trans_df',\n",
       " 'map_user_df',\n",
       " 'top_trans_dist_df',\n",
       " 'top_trans_pin_df',\n",
       " 'top_user_dist_df',\n",
       " 'top_user_pin_df']"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_list = [df for df in globals() if isinstance(globals()[df], pd.core.frame.DataFrame) and df.endswith('_df')]\n",
    "\n",
    "df_list"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Renaming Delhi districts to manage inconsistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# As I noticed few district name is mismatched between dfs loaded from pulse and lat_long_df, doing this.\n",
    "\n",
    "def add_suffix_to_districts(df):\n",
    "    if 'District' in df.columns and 'State' in df.columns:\n",
    "        delhi_df = df[df['State'] == 'Delhi']\n",
    "        \n",
    "        districts_to_suffix = [d for d in delhi_df['District'].unique() if d != 'Shahdara']\n",
    "        \n",
    "        df.loc[(df['State'] == 'Delhi') & (df['District'].isin(districts_to_suffix)), 'District'] = df.loc[(df['State'] == 'Delhi') & (df['District'].isin(districts_to_suffix)), 'District'].apply(lambda x: x + ' Delhi' if 'Delhi' not in x else x)\n",
    "\n",
    "    return df\n",
    "\n",
    "for df_name in df_list:\n",
    "    df = globals()[df_name]\n",
    "    add_suffix_to_districts(df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding Latitude and Longitude columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "lat_long_df = pd.read_csv(r\"Miscellaneous\\dist_lat_long.csv\")\n",
    "\n",
    "for df_name in df_list:\n",
    "    df = globals()[df_name]\n",
    "    if 'District' in df.columns:\n",
    "        df = pd.merge(df, lat_long_df, on=['State', 'District'], how='left')\n",
    "        globals()[df_name] = df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding Region column to all dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_region_column(df):\n",
    "    state_groups = {\n",
    "        'Northern Region': ['Jammu and Kashmir', 'Himachal Pradesh', 'Punjab', 'Chandigarh', 'Uttarakhand', 'Ladakh', 'Delhi', 'Haryana'],\n",
    "        'Central Region': ['Uttar Pradesh', 'Madhya Pradesh', 'Chhattisgarh'],\n",
    "        'Western Region': ['Rajasthan', 'Gujarat', 'Dadra and Nagar Haveli and Daman and Diu', 'Maharashtra'],\n",
    "        'Eastern Region': ['Bihar', 'Jharkhand', 'Odisha', 'West Bengal', 'Sikkim'],\n",
    "        'Southern Region': ['Andhra Pradesh', 'Telangana', 'Karnataka', 'Kerala', 'Tamil Nadu', 'Puducherry', 'Goa', 'Lakshadweep', 'Andaman and Nicobar Islands'],\n",
    "        'North-Eastern Region': ['Assam', 'Meghalaya', 'Manipur', 'Nagaland', 'Tripura', 'Arunachal Pradesh', 'Mizoram']\n",
    "    }\n",
    "    \n",
    "    df['Region'] = df['State'].map({state: region for region, states in state_groups.items() for state in states})\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df_name in df_list:\n",
    "    df = globals()[df_name]\n",
    "    add_region_column(df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Columnwise null-count and duplicated_rows-count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "agg_trans_df:\n",
      "Null count: \n",
      "State                 0\n",
      "Year                  0\n",
      "Quarter               0\n",
      "Transaction_type      0\n",
      "Transaction_count     0\n",
      "Transaction_amount    0\n",
      "Region                0\n",
      "dtype: int64\n",
      "Duplicated rows count: \n",
      "0\n",
      "(3594, 7)\n",
      "\n",
      " _________________________ \n",
      "\n",
      "agg_user_df:\n",
      "Null count: \n",
      "State                0\n",
      "Year                 0\n",
      "Quarter              0\n",
      "Brand                0\n",
      "Transaction_count    0\n",
      "Percentage           0\n",
      "Region               0\n",
      "dtype: int64\n",
      "Duplicated rows count: \n",
      "0\n",
      "(6732, 7)\n",
      "\n",
      " _________________________ \n",
      "\n",
      "map_trans_df:\n",
      "Null count: \n",
      "State                 0\n",
      "Year                  0\n",
      "Quarter               0\n",
      "District              0\n",
      "Transaction_count     0\n",
      "Transaction_amount    0\n",
      "Latitude              0\n",
      "Longitude             0\n",
      "Region                0\n",
      "dtype: int64\n",
      "Duplicated rows count: \n",
      "0\n",
      "(14636, 9)\n",
      "\n",
      " _________________________ \n",
      "\n",
      "map_user_df:\n",
      "Null count: \n",
      "State               0\n",
      "Year                0\n",
      "Quarter             0\n",
      "District            0\n",
      "Registered_users    0\n",
      "App_opens           0\n",
      "Latitude            0\n",
      "Longitude           0\n",
      "Region              0\n",
      "dtype: int64\n",
      "Duplicated rows count: \n",
      "0\n",
      "(14640, 9)\n",
      "\n",
      " _________________________ \n",
      "\n",
      "top_trans_dist_df:\n",
      "Null count: \n",
      "State                 0\n",
      "Year                  0\n",
      "Quarter               0\n",
      "District              0\n",
      "Transaction_count     0\n",
      "Transaction_amount    0\n",
      "Latitude              0\n",
      "Longitude             0\n",
      "Region                0\n",
      "dtype: int64\n",
      "Duplicated rows count: \n",
      "0\n",
      "(5920, 9)\n",
      "\n",
      " _________________________ \n",
      "\n",
      "top_trans_pin_df:\n",
      "Null count: \n",
      "State                 0\n",
      "Year                  0\n",
      "Quarter               0\n",
      "Pincode               2\n",
      "Transaction_count     0\n",
      "Transaction_amount    0\n",
      "Region                0\n",
      "dtype: int64\n",
      "Duplicated rows count: \n",
      "0\n",
      "(7139, 7)\n",
      "\n",
      " _________________________ \n",
      "\n",
      "top_user_dist_df:\n",
      "Null count: \n",
      "State               0\n",
      "Year                0\n",
      "Quarter             0\n",
      "District            0\n",
      "Registered_users    0\n",
      "Latitude            0\n",
      "Longitude           0\n",
      "Region              0\n",
      "dtype: int64\n",
      "Duplicated rows count: \n",
      "0\n",
      "(5920, 8)\n",
      "\n",
      " _________________________ \n",
      "\n",
      "top_user_pin_df:\n",
      "Null count: \n",
      "State               0\n",
      "Year                0\n",
      "Quarter             0\n",
      "Pincode             0\n",
      "Registered_users    0\n",
      "Region              0\n",
      "dtype: int64\n",
      "Duplicated rows count: \n",
      "0\n",
      "(7140, 6)\n",
      "\n",
      " _________________________ \n",
      "\n"
     ]
    }
   ],
   "source": [
    "for df_name in df_list:\n",
    "    df = globals()[df_name]\n",
    "    print(f\"{df_name}:\")\n",
    "    print(f\"Null count: \\n{df.isnull().sum()}\")\n",
    "    print(f\"Duplicated rows count: \\n{df.duplicated().sum()}\")\n",
    "    print(df.shape)\n",
    "    print(\"\\n\", 25 * \"_\", \"\\n\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Understanding the dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DATAFRAME INFO:\n",
      "\n",
      "agg_trans_df:\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3594 entries, 0 to 3593\n",
      "Data columns (total 7 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",
      " 6   Region              3594 non-null   object \n",
      "dtypes: float64(1), int64(2), object(4)\n",
      "memory usage: 196.7+ KB\n",
      "\n",
      " _____________________________________________ \n",
      "\n",
      "agg_user_df:\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6732 entries, 0 to 6731\n",
      "Data columns (total 7 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   Brand              6732 non-null   object \n",
      " 4   Transaction_count  6732 non-null   int64  \n",
      " 5   Percentage         6732 non-null   float64\n",
      " 6   Region             6732 non-null   object \n",
      "dtypes: float64(1), int64(2), object(4)\n",
      "memory usage: 368.3+ KB\n",
      "\n",
      " _____________________________________________ \n",
      "\n",
      "map_trans_df:\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14636 entries, 0 to 14635\n",
      "Data columns (total 9 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   Transaction_count   14636 non-null  int64  \n",
      " 5   Transaction_amount  14636 non-null  float64\n",
      " 6   Latitude            14636 non-null  float64\n",
      " 7   Longitude           14636 non-null  float64\n",
      " 8   Region              14636 non-null  object \n",
      "dtypes: float64(3), int64(2), object(4)\n",
      "memory usage: 1.0+ MB\n",
      "\n",
      " _____________________________________________ \n",
      "\n",
      "map_user_df:\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14640 entries, 0 to 14639\n",
      "Data columns (total 9 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   Registered_users  14640 non-null  int64  \n",
      " 5   App_opens         14640 non-null  int64  \n",
      " 6   Latitude          14640 non-null  float64\n",
      " 7   Longitude         14640 non-null  float64\n",
      " 8   Region            14640 non-null  object \n",
      "dtypes: float64(2), int64(3), object(4)\n",
      "memory usage: 1.0+ MB\n",
      "\n",
      " _____________________________________________ \n",
      "\n",
      "top_trans_dist_df:\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5920 entries, 0 to 5919\n",
      "Data columns (total 9 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   State               5920 non-null   object \n",
      " 1   Year                5920 non-null   object \n",
      " 2   Quarter             5920 non-null   int64  \n",
      " 3   District            5920 non-null   object \n",
      " 4   Transaction_count   5920 non-null   int64  \n",
      " 5   Transaction_amount  5920 non-null   float64\n",
      " 6   Latitude            5920 non-null   float64\n",
      " 7   Longitude           5920 non-null   float64\n",
      " 8   Region              5920 non-null   object \n",
      "dtypes: float64(3), int64(2), object(4)\n",
      "memory usage: 416.4+ KB\n",
      "\n",
      " _____________________________________________ \n",
      "\n",
      "top_trans_pin_df:\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 7139 entries, 0 to 7138\n",
      "Data columns (total 7 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",
      " 6   Region              7139 non-null   object \n",
      "dtypes: float64(1), int64(2), object(4)\n",
      "memory usage: 390.5+ KB\n",
      "\n",
      " _____________________________________________ \n",
      "\n",
      "top_user_dist_df:\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5920 entries, 0 to 5919\n",
      "Data columns (total 8 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   State             5920 non-null   object \n",
      " 1   Year              5920 non-null   object \n",
      " 2   Quarter           5920 non-null   int64  \n",
      " 3   District          5920 non-null   object \n",
      " 4   Registered_users  5920 non-null   int64  \n",
      " 5   Latitude          5920 non-null   float64\n",
      " 6   Longitude         5920 non-null   float64\n",
      " 7   Region            5920 non-null   object \n",
      "dtypes: float64(2), int64(2), object(4)\n",
      "memory usage: 370.1+ KB\n",
      "\n",
      " _____________________________________________ \n",
      "\n",
      "top_user_pin_df:\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 7140 entries, 0 to 7139\n",
      "Data columns (total 6 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   Registered_users  7140 non-null   int64 \n",
      " 5   Region            7140 non-null   object\n",
      "dtypes: int64(2), object(4)\n",
      "memory usage: 334.8+ KB\n",
      "\n",
      " _____________________________________________ \n",
      "\n"
     ]
    }
   ],
   "source": [
    "print('DATAFRAME INFO:\\n')\n",
    "\n",
    "for df_name in df_list:\n",
    "    df = globals()[df_name]\n",
    "    print(df_name + ':\\n')\n",
    "    df.info()\n",
    "    print(\"\\n\", 45 * \"_\", \"\\n\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dropping rows with null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "State                 0\n",
       "Year                  0\n",
       "Quarter               0\n",
       "Pincode               0\n",
       "Transaction_count     0\n",
       "Transaction_amount    0\n",
       "Region                0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'top_trans_pin_df' seems to have two null values and they are not of significant proportion so dropping them;\n",
    "\n",
    "top_trans_pin_df.dropna(axis = 'index', inplace = True)\n",
    "top_trans_pin_df.isnull().sum()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing datatype across all dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Year column in all the dataframes seems to be of object dtype so changing it to int object so as to push into MySQL as year;\n",
    "\n",
    "for df_name in df_list:\n",
    "    df = globals()[df_name]\n",
    "    df['Year'] = df['Year'].astype('int')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Outlier count across all dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Everything seems to be alright as far as dtypes and nullvalues are concerned so checking for outliers\n",
    "# Function to check for outliers\n",
    "\n",
    "def count_outliers(df):\n",
    "    outliers = {}\n",
    "    for col in df.select_dtypes(include=[np.number]).columns:\n",
    "        if col in ['Transaction_count', 'Transaction_amount']:\n",
    "            q1 = df[col].quantile(0.25)\n",
    "            q3 = df[col].quantile(0.75)\n",
    "            iqr = q3 - q1\n",
    "            upper_bound = q3 + (1.5 * iqr)\n",
    "            lower_bound = q1 - (1.5 * iqr)\n",
    "            outliers[col] = len(df[(df[col] > upper_bound) | (df[col] < lower_bound)])\n",
    "        else:\n",
    "            continue\n",
    "    return outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OUTLIER COUNT ACROSS DATAFRAMES:\n",
      "\n",
      "agg_trans_df :\n",
      "\n",
      " {'Transaction_count': 652, 'Transaction_amount': 660} \n",
      "\n",
      "\n",
      " _______________________________________________________ \n",
      "\n",
      "agg_user_df :\n",
      "\n",
      " {'Transaction_count': 893} \n",
      "\n",
      "\n",
      " _______________________________________________________ \n",
      "\n",
      "map_trans_df :\n",
      "\n",
      " {'Transaction_count': 1811, 'Transaction_amount': 1771} \n",
      "\n",
      "\n",
      " _______________________________________________________ \n",
      "\n",
      "top_trans_dist_df :\n",
      "\n",
      " {'Transaction_count': 734, 'Transaction_amount': 743} \n",
      "\n",
      "\n",
      " _______________________________________________________ \n",
      "\n",
      "top_trans_pin_df :\n",
      "\n",
      " {'Transaction_count': 999, 'Transaction_amount': 995} \n",
      "\n",
      "\n",
      " _______________________________________________________ \n",
      "\n"
     ]
    }
   ],
   "source": [
    "print('OUTLIER COUNT ACROSS DATAFRAMES:\\n')\n",
    "\n",
    "for df_name in df_list:\n",
    "    df = globals()[df_name]\n",
    "    outliers = count_outliers(df)\n",
    "    if len(outliers) == 0:\n",
    "        pass\n",
    "    else:\n",
    "        print(df_name, \":\\n\\n\", outliers, \"\\n\")\n",
    "        print(\"\\n\", 55 * \"_\", \"\\n\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Outliers in all, seems to be of significant proportion of the dataset. So leaving them as it is;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Unique value count across all dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to check for unique value counts and print if count less than 10;\n",
    "\n",
    "def unique_value_count(df, exclude_cols=[]):\n",
    "    for col in df.columns:\n",
    "        if col in exclude_cols:\n",
    "            continue\n",
    "        unique_vals = df[col].nunique()\n",
    "        print(f\"{col}: {unique_vals} unique values\")\n",
    "        if unique_vals < 10:\n",
    "            print(df[col].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "UNIQUE VALUE COUNT ACROSS DATAFRAMES; \n",
      "\n",
      "agg_trans_df :\n",
      "\n",
      "Transaction_type: 5 unique values\n",
      "['Recharge & bill payments' 'Peer-to-peer payments' 'Merchant payments'\n",
      " 'Financial Services' 'Others']\n",
      "Transaction_count: 3548 unique values\n",
      "Transaction_amount: 3594 unique values\n",
      "Region: 6 unique values\n",
      "['Southern Region' 'North-Eastern Region' 'Eastern Region'\n",
      " 'Northern Region' 'Central Region' 'Western Region']\n",
      "\n",
      " _______________________________________________________ \n",
      "\n",
      "agg_user_df :\n",
      "\n",
      "Brand: 20 unique values\n",
      "Transaction_count: 6501 unique values\n",
      "Region: 6 unique values\n",
      "['Southern Region' 'North-Eastern Region' 'Eastern Region'\n",
      " 'Northern Region' 'Central Region' 'Western Region']\n",
      "\n",
      " _______________________________________________________ \n",
      "\n",
      "map_trans_df :\n",
      "\n",
      "District: 727 unique values\n",
      "Transaction_count: 14566 unique values\n",
      "Transaction_amount: 14636 unique values\n",
      "Latitude: 533 unique values\n",
      "Longitude: 540 unique values\n",
      "Region: 6 unique values\n",
      "['Southern Region' 'North-Eastern Region' 'Eastern Region'\n",
      " 'Northern Region' 'Central Region' 'Western Region']\n",
      "\n",
      " _______________________________________________________ \n",
      "\n",
      "map_user_df :\n",
      "\n",
      "District: 727 unique values\n",
      "Registered_users: 14351 unique values\n",
      "App_opens: 10977 unique values\n",
      "Latitude: 533 unique values\n",
      "Longitude: 540 unique values\n",
      "Region: 6 unique values\n",
      "['Southern Region' 'North-Eastern Region' 'Eastern Region'\n",
      " 'Northern Region' 'Central Region' 'Western Region']\n",
      "\n",
      " _______________________________________________________ \n",
      "\n",
      "top_trans_dist_df :\n",
      "\n",
      "District: 368 unique values\n",
      "Transaction_count: 5910 unique values\n",
      "Transaction_amount: 5920 unique values\n",
      "Latitude: 300 unique values\n",
      "Longitude: 299 unique values\n",
      "Region: 6 unique values\n",
      "['Southern Region' 'North-Eastern Region' 'Eastern Region'\n",
      " 'Northern Region' 'Central Region' 'Western Region']\n",
      "\n",
      " _______________________________________________________ \n",
      "\n",
      "top_trans_pin_df :\n",
      "\n",
      "Pincode: 746 unique values\n",
      "Transaction_count: 7083 unique values\n",
      "Transaction_amount: 7137 unique values\n",
      "Region: 6 unique values\n",
      "['Southern Region' 'North-Eastern Region' 'Eastern Region'\n",
      " 'Northern Region' 'Central Region' 'Western Region']\n",
      "\n",
      " _______________________________________________________ \n",
      "\n",
      "top_user_dist_df :\n",
      "\n",
      "District: 313 unique values\n",
      "Registered_users: 5874 unique values\n",
      "Latitude: 258 unique values\n",
      "Longitude: 258 unique values\n",
      "Region: 6 unique values\n",
      "['Southern Region' 'North-Eastern Region' 'Eastern Region'\n",
      " 'Northern Region' 'Central Region' 'Western Region']\n",
      "\n",
      " _______________________________________________________ \n",
      "\n",
      "top_user_pin_df :\n",
      "\n",
      "Pincode: 426 unique values\n",
      "Registered_users: 6882 unique values\n",
      "Region: 6 unique values\n",
      "['Southern Region' 'North-Eastern Region' 'Eastern Region'\n",
      " 'Northern Region' 'Central Region' 'Western Region']\n",
      "\n",
      " _______________________________________________________ \n",
      "\n"
     ]
    }
   ],
   "source": [
    "print('UNIQUE VALUE COUNT ACROSS DATAFRAMES; \\n')\n",
    "\n",
    "for df_name in df_list:\n",
    "    df = globals()[df_name]\n",
    "    print(df_name, \":\\n\")\n",
    "    unique_value_count(df, exclude_cols = ['State', 'Year', 'Quarter', 'Percentage'])\n",
    "    print(\"\\n\", 55 * \"_\", \"\\n\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Though outliers haven't gave much of an insight, unique value count can be used to gain some good insights"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating CSV files out of the refined dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_dfs_as_csv(df_list):\n",
    "    subfolder = 'Miscellaneous'\n",
    "    if not os.path.exists(subfolder):\n",
    "        os.makedirs(subfolder)\n",
    "        \n",
    "    for df_name in df_list:\n",
    "        df = globals()[df_name]\n",
    "        file_path = os.path.join(subfolder, df_name.replace('_df', '') + '.csv')\n",
    "        df.to_csv(file_path, index=False)\n",
    "\n",
    "# Calling function to execute\n",
    "\n",
    "save_dfs_as_csv(df_list)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL part"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Establishing connection and creating cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = mysql.connector.connect(\n",
    "  host = \"localhost\",\n",
    "  user = \"root\",\n",
    "  password = \"Rooting@7781\"\n",
    ")\n",
    "\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Database creation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"DROP DATABASE IF EXISTS phonepe_pulse\")\n",
    "\n",
    "cursor.execute(\"CREATE DATABASE phonepe_pulse\")\n",
    "\n",
    "cursor.execute(\"USE phonepe_pulse\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Creating tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''CREATE TABLE agg_trans (\n",
    "                    State VARCHAR(255),\n",
    "                    Year YEAR,\n",
    "                    Quarter INTEGER,\n",
    "                    Transaction_type VARCHAR(255),\n",
    "                    Transaction_count INTEGER,\n",
    "                    Transaction_amount FLOAT,\n",
    "                    Region VARCHAR(255),\n",
    "                    PRIMARY KEY (State(255), Year, Quarter, Transaction_type(255), Region(255))\n",
    "                 )''')\n",
    "\n",
    "cursor.execute('''CREATE TABLE agg_user (\n",
    "                    State VARCHAR(255),\n",
    "                    Year YEAR,\n",
    "                    Quarter INTEGER,\n",
    "                    Brand VARCHAR(255),\n",
    "                    Transaction_count INTEGER,\n",
    "                    Percentage FLOAT,\n",
    "                    Region VARCHAR(255),\n",
    "                    PRIMARY KEY (State(255), Year, Quarter, Brand(255), Region(255))\n",
    "                 )''')\n",
    "\n",
    "cursor.execute('''CREATE TABLE map_trans (\n",
    "                    State VARCHAR(255),\n",
    "                    Year YEAR,\n",
    "                    Quarter INTEGER,\n",
    "                    District VARCHAR(255),\n",
    "                    Transaction_count INTEGER,\n",
    "                    Transaction_amount FLOAT,\n",
    "                    Latitude FLOAT,\n",
    "                    Longitude FLOAT,\n",
    "                    Region VARCHAR(255),\n",
    "                    PRIMARY KEY (State(255), Year, Quarter, District(255), Region(255))\n",
    "                 )''')\n",
    "\n",
    "cursor.execute('''CREATE TABLE map_user (\n",
    "                    State VARCHAR(255),\n",
    "                    Year YEAR,\n",
    "                    Quarter INTEGER,\n",
    "                    District VARCHAR(255),\n",
    "                    Registered_users INTEGER,\n",
    "                    App_opens INTEGER,\n",
    "                    Latitude FLOAT,\n",
    "                    Longitude FLOAT,\n",
    "                    Region VARCHAR(255),\n",
    "                    PRIMARY KEY (State(255), Year, Quarter, District(255), Region(255))\n",
    "                 )''')\n",
    "\n",
    "cursor.execute('''CREATE TABLE top_trans_dist (\n",
    "                    State VARCHAR(255),\n",
    "                    Year YEAR,\n",
    "                    Quarter INTEGER,\n",
    "                    District VARCHAR(255),\n",
    "                    Transaction_count INTEGER,\n",
    "                    Transaction_amount FLOAT,\n",
    "                    Latitude FLOAT,\n",
    "                    Longitude FLOAT,\n",
    "                    Region VARCHAR(255),\n",
    "                    PRIMARY KEY (State(255), Year, Quarter, District(255), Region(255))\n",
    "                 )''')\n",
    "\n",
    "cursor.execute('''CREATE TABLE top_trans_pin (\n",
    "                    State VARCHAR(255),\n",
    "                    Year YEAR,\n",
    "                    Quarter INTEGER,\n",
    "                    Pincode VARCHAR(255),\n",
    "                    Transaction_count INTEGER,\n",
    "                    Transaction_amount FLOAT,\n",
    "                    Region VARCHAR(255),\n",
    "                    PRIMARY KEY (State(255), Year, Quarter, Pincode(255), Region(255))\n",
    "                 )''')\n",
    "\n",
    "cursor.execute('''CREATE TABLE top_user_dist (\n",
    "                    State VARCHAR(255),\n",
    "                    Year YEAR,\n",
    "                    Quarter INTEGER,\n",
    "                    District VARCHAR(255),\n",
    "                    Registered_users INTEGER,\n",
    "                    Latitude FLOAT,\n",
    "                    Longitude FLOAT,\n",
    "                    Region VARCHAR(255),\n",
    "                    PRIMARY KEY (State(255), Year, Quarter, District(255), Region(255))\n",
    "                 )''')\n",
    "\n",
    "cursor.execute('''CREATE TABLE top_user_pin (\n",
    "                    State VARCHAR(255),\n",
    "                    Year YEAR,\n",
    "                    Quarter INTEGER,\n",
    "                    Pincode VARCHAR(255),\n",
    "                    Registered_users INTEGER,\n",
    "                    Region VARCHAR(255),\n",
    "                    PRIMARY KEY (State(255), Year, Quarter, Pincode(255), Region(255))\n",
    "                 )''')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pushing data into MySQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "def push_data_into_mysql(conn, cursor, dfs, table_columns):\n",
    "    for table_name in dfs.keys():\n",
    "        df = dfs[table_name]\n",
    "        columns = table_columns[table_name]\n",
    "        placeholders = ', '.join(['%s'] * len(columns))\n",
    "        query = f\"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({placeholders})\"\n",
    "        for _, row in df.iterrows():\n",
    "            data = tuple(row[column] for column in columns)\n",
    "            cursor.execute(query, data)\n",
    "        conn.commit()\n",
    "    print(\"Data successfully pushed into MySQL tables\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Mapping my_sql tables to pandas dataframes that we have created earlier\n",
    "\n",
    "dfs = {\n",
    "    'agg_trans': agg_trans_df,\n",
    "    'agg_user': agg_user_df,\n",
    "    'map_trans': map_trans_df,\n",
    "    'map_user': map_user_df,\n",
    "    'top_trans_dist': top_trans_dist_df,\n",
    "    'top_trans_pin': top_trans_pin_df,\n",
    "    'top_user_dist': top_user_dist_df,\n",
    "    'top_user_pin': top_user_pin_df\n",
    "}\n",
    "\n",
    "# Mapping table name to associated columns for each table\n",
    "\n",
    "table_columns = {\n",
    "    'agg_trans': list(agg_trans_df.columns),\n",
    "    'agg_user': list(agg_user_df.columns),\n",
    "    'map_trans': list(map_trans_df.columns),\n",
    "    'map_user': list(map_user_df.columns),\n",
    "    'top_trans_dist': list(top_trans_dist_df.columns),\n",
    "    'top_trans_pin': list(top_trans_pin_df.columns),\n",
    "    'top_user_dist': list(top_user_dist_df.columns),\n",
    "    'top_user_pin': list(top_user_pin_df.columns)\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data successfully pushed into MySQL tables\n"
     ]
    }
   ],
   "source": [
    "push_data_into_mysql(conn, cursor, dfs, table_columns)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checking whether shape of tables and dataframes are equal or not"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "agg_trans table has 3594 rows and 7 columns and shape matches DataFrame.\n",
      "agg_user table has 6732 rows and 7 columns and shape matches DataFrame.\n",
      "map_trans table has 14636 rows and 9 columns and shape matches DataFrame.\n",
      "map_user table has 14640 rows and 9 columns and shape matches DataFrame.\n",
      "top_trans_dist table has 5920 rows and 9 columns and shape matches DataFrame.\n",
      "top_trans_pin table has 7137 rows and 7 columns and shape matches DataFrame.\n",
      "top_user_dist table has 5920 rows and 8 columns and shape matches DataFrame.\n",
      "top_user_pin table has 7140 rows and 6 columns and shape matches DataFrame.\n"
     ]
    }
   ],
   "source": [
    "# Get list of tables in database\n",
    "\n",
    "cursor.execute(\"SHOW TABLES\")\n",
    "tables = cursor.fetchall()\n",
    "\n",
    "# Loop through tables and get count of rows and columns in MySQL\n",
    "\n",
    "for table in tables:\n",
    "    table_name = table[0]\n",
    "    cursor.execute(f\"SELECT COUNT(*) FROM {table_name}\")\n",
    "    row_count = cursor.fetchone()[0]\n",
    "    cursor.execute(f\"SELECT COUNT(*) FROM information_schema.columns WHERE table_name='{table_name}'\")\n",
    "    column_count = cursor.fetchone()[0]\n",
    "\n",
    "    # Check if shape of DataFrame matches count of rows and columns in table\n",
    "\n",
    "    df = dfs[table_name]\n",
    "    if df.shape == (row_count, column_count):\n",
    "        print(f\"{table_name} table has {row_count} rows and {column_count} columns and shape matches DataFrame.\")\n",
    "    else:\n",
    "        print(f\"{table_name} table has {row_count} rows and {column_count} columns but shape does not match DataFrame.\")\n",
    "\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  }
 ],
 "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.10.11"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}