workout.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import plotly.express as px\n",
    "import requests\n",
    "import json\n",
    "import streamlit as st\n",
    "import mysql.connector as sql\n",
    "\n",
    "# Fetch GeoJSON data\n",
    "url = \"https://gist.githubusercontent.com/jbrobst/56c13bbbf9d97d187fea01ca62ea5112/raw/e388c4cae20aa53cb5090210a42ebb9b765c0a36/india_states.geojson\"\n",
    "response = requests.get(url)\n",
    "data1 = json.loads(response.content)\n",
    "\n",
    "# Extract state names and sort them in alphabetical order\n",
    "state_names = [feature['properties']['ST_NM'] for feature in data1['features']]\n",
    "state_names.sort()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Andaman & Nicobar',\n",
       " 'Andhra Pradesh',\n",
       " 'Arunachal Pradesh',\n",
       " 'Assam',\n",
       " 'Bihar',\n",
       " 'Chandigarh',\n",
       " 'Chhattisgarh',\n",
       " 'Dadra and Nagar Haveli and Daman and Diu',\n",
       " 'Delhi',\n",
       " 'Goa',\n",
       " 'Gujarat',\n",
       " 'Haryana',\n",
       " 'Himachal Pradesh',\n",
       " 'Jammu & Kashmir',\n",
       " 'Jharkhand',\n",
       " 'Karnataka',\n",
       " 'Kerala',\n",
       " 'Ladakh',\n",
       " 'Lakshadweep',\n",
       " 'Madhya Pradesh',\n",
       " 'Maharashtra',\n",
       " 'Manipur',\n",
       " 'Meghalaya',\n",
       " 'Mizoram',\n",
       " 'Nagaland',\n",
       " 'Odisha',\n",
       " 'Puducherry',\n",
       " 'Punjab',\n",
       " 'Rajasthan',\n",
       " 'Sikkim',\n",
       " 'Tamil Nadu',\n",
       " 'Telangana',\n",
       " 'Tripura',\n",
       " 'Uttar Pradesh',\n",
       " 'Uttarakhand',\n",
       " 'West Bengal']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andhra-pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>arunachal-pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>assam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>bihar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>chandigarh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>chhattisgarh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>dadra-&amp;-nagar-haveli-&amp;-daman-&amp;-diu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>delhi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>goa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>gujarat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>haryana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>himachal-pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>jammu-&amp;-kashmir</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>jharkhand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>karnataka</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>kerala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ladakh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>lakshadweep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>madhya-pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>maharashtra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>manipur</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>meghalaya</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>mizoram</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>nagaland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>odisha</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>puducherry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>punjab</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>rajasthan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>sikkim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>tamil-nadu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>telangana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>tripura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>uttar-pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>uttarakhand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>west-bengal</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     0\n",
       "0            andaman-&-nicobar-islands\n",
       "1                       andhra-pradesh\n",
       "2                    arunachal-pradesh\n",
       "3                                assam\n",
       "4                                bihar\n",
       "5                           chandigarh\n",
       "6                         chhattisgarh\n",
       "7   dadra-&-nagar-haveli-&-daman-&-diu\n",
       "8                                delhi\n",
       "9                                  goa\n",
       "10                             gujarat\n",
       "11                             haryana\n",
       "12                    himachal-pradesh\n",
       "13                     jammu-&-kashmir\n",
       "14                           jharkhand\n",
       "15                           karnataka\n",
       "16                              kerala\n",
       "17                              ladakh\n",
       "18                         lakshadweep\n",
       "19                      madhya-pradesh\n",
       "20                         maharashtra\n",
       "21                             manipur\n",
       "22                           meghalaya\n",
       "23                             mizoram\n",
       "24                            nagaland\n",
       "25                              odisha\n",
       "26                          puducherry\n",
       "27                              punjab\n",
       "28                           rajasthan\n",
       "29                              sikkim\n",
       "30                          tamil-nadu\n",
       "31                           telangana\n",
       "32                             tripura\n",
       "33                       uttar-pradesh\n",
       "34                         uttarakhand\n",
       "35                         west-bengal"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# Creating connection with MySQL workbench and retrieve the data from the \"agg_trans\" table as dataframe:\n",
    "mydb = sql.connect(host=\"localhost\",\n",
    "                   user=\"root\", \n",
    "                   password=\"**********\", \n",
    "                   database=\"phonepe_pulse\")\n",
    "mycursor = mydb.cursor()\n",
    "\n",
    "mycursor.execute(\"SELECT state, year, quarter, transaction_type, transaction_count, transaction_amount FROM agg_trans\")\n",
    "data = mycursor.fetchall()\n",
    "columns = [\"state\", \"year\", \"quarter\", \"transaction_type\", \"transaction_count\", \"transaction_amount\"]\n",
    "df = pd.DataFrame(data, columns=columns)\n",
    "df\n",
    "\n",
    "#Find unique values in column 'A'\n",
    "pd.DataFrame(df['state'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "# Assuming your DataFrame 'df' looks like this\n",
    "# df = pd.DataFrame({'states': [...state_names...]})\n",
    "\n",
    "# Define the conversion function\n",
    "def convert_state_name(state):\n",
    "    return state.replace('-', ' ').title()\n",
    "\n",
    "# Apply the conversion function to the 'states' column in the DataFrame\n",
    "df['state'] = df['state'].apply(lambda x: convert_state_name(x))\n",
    "\n",
    "# Assuming you have already converted and updated the DataFrame 'df'\n",
    "df.to_csv('C:/Users/user/Desktop/guvi pro/phonepepro/data/india_map.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>transaction_type</th>\n",
       "      <th>transaction_count</th>\n",
       "      <th>transaction_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Recharge &amp; bill payments</td>\n",
       "      <td>4200</td>\n",
       "      <td>1.845307e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Peer-to-peer payments</td>\n",
       "      <td>1871</td>\n",
       "      <td>1.213866e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Merchant payments</td>\n",
       "      <td>298</td>\n",
       "      <td>4.525072e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>33</td>\n",
       "      <td>1.060142e+04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       state  year  quarter          transaction_type  \\\n",
       "0  Andaman & Nicobar Islands  2018        1  Recharge & bill payments   \n",
       "1  Andaman & Nicobar Islands  2018        1     Peer-to-peer payments   \n",
       "2  Andaman & Nicobar Islands  2018        1         Merchant payments   \n",
       "3  Andaman & Nicobar Islands  2018        1        Financial Services   \n",
       "\n",
       "   transaction_count  transaction_amount  \n",
       "0               4200        1.845307e+06  \n",
       "1               1871        1.213866e+07  \n",
       "2                298        4.525072e+05  \n",
       "3                 33        1.060142e+04  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read the \"india_map\" CSV file into a DataFrame\n",
    "df1 = pd.read_csv('C:/Users/user/Desktop/guvi pro/phonepepro/data/india_map.csv')\n",
    "df1.head(4)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Minimum Transaction Amount: 34.39721181625656\n",
      "Maximum Transaction Amount: 2393380395296.8516\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Assuming df is your DataFrame\n",
    "min_transaction_amount = df['transaction_amount'].min()\n",
    "max_transaction_amount = df['transaction_amount'].max()\n",
    "\n",
    "print(\"Minimum Transaction Amount:\", min_transaction_amount)\n",
    "print(\"Maximum Transaction Amount:\", max_transaction_amount)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2023-07-19 10:34:33.551 \n",
      "  \u001b[33m\u001b[1mWarning:\u001b[0m to view this Streamlit app on a browser, run it with the following\n",
      "  command:\n",
      "\n",
      "    streamlit run C:\\Users\\user\\AppData\\Roaming\\Python\\Python311\\site-packages\\ipykernel_launcher.py [ARGUMENTS]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "DeltaGenerator()"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Geo plot\n",
    "\n",
    "\n",
    "fig_tra = px.choropleth(\n",
    "    df1,\n",
    "    geojson=\"https://gist.githubusercontent.com/jbrobst/56c13bbbf9d97d187fea01ca62ea5112/raw/e388c4cae20aa53cb5090210a42ebb9b765c0a36/india_states.geojson\",\n",
    "    featureidkey='properties.ST_NM',\n",
    "    locations='state',\n",
    "    color='transaction_amount',\n",
    "    color_continuous_scale='thermal',\n",
    "    range_color=(df1['transaction_amount'].min(), df1['transaction_amount'].max()),  # Set custom range\n",
    "    title='Transaction Analysis'\n",
    ")\n",
    "\n",
    "fig_tra.update_geos(fitbounds=\"locations\", visible=False)\n",
    "fig_tra.update_layout(title_font=dict(size=33), title_font_color='#6739b7', height=800)\n",
    "\n",
    "st.plotly_chart(fig_tra, use_container_width=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data Types:\n",
      "state                  object\n",
      "year                    int64\n",
      "quarter                 int64\n",
      "transaction_type       object\n",
      "transaction_count       int64\n",
      "transaction_amount    float64\n",
      "dtype: object\n",
      "Null Values:\n",
      "state                 0\n",
      "year                  0\n",
      "quarter               0\n",
      "transaction_type      0\n",
      "transaction_count     0\n",
      "transaction_amount    0\n",
      "dtype: int64\n",
      "Transaction Amount Summary:\n",
      "count    7.188000e+03\n",
      "mean     3.378017e+10\n",
      "std      1.417097e+11\n",
      "min      3.439721e+01\n",
      "25%      2.680468e+07\n",
      "50%      2.862499e+08\n",
      "75%      5.642295e+09\n",
      "max      2.393380e+12\n",
      "Name: transaction_amount, dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "DeltaGenerator()"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import plotly.express as px\n",
    "import streamlit as st\n",
    "\n",
    "# Read the \"india_map\" CSV file into a DataFrame\n",
    "# Assuming df1 is your DataFrame containing the data\n",
    "df1 = pd.read_csv(r'C:/Users/user/Desktop/guvi pro/phonepepro/data/india_map.csv')\n",
    "df1.head(4)\n",
    "\n",
    "# Debugging statements\n",
    "print(\"Data Types:\")\n",
    "print(df1.dtypes)\n",
    "\n",
    "print(\"Null Values:\")\n",
    "print(df1.isnull().sum())\n",
    "\n",
    "print(\"Transaction Amount Summary:\")\n",
    "print(df1['transaction_amount'].describe())\n",
    "\n",
    "# Calculate the range of transaction_amount for setting zmin and zmax\n",
    "zmin = df1['transaction_amount'].min()\n",
    "zmax = df1['transaction_amount'].max()\n",
    "\n",
    "fig_tra = px.choropleth(\n",
    "    df1,\n",
    "    geojson=\"https://gist.githubusercontent.com/jbrobst/56c13bbbf9d97d187fea01ca62ea5112/raw/e388c4cae20aa53cb5090210a42ebb9b765c0a36/india_states.geojson\",\n",
    "    featureidkey='properties.ST_NM',\n",
    "    locations='state',\n",
    "    color='transaction_amount',\n",
    "    color_continuous_scale='Viridis',  # Change the color scale\n",
    "    range_color=(zmin, zmax),  # Set custom range\n",
    "    title='Transaction Analysis'\n",
    ")\n",
    "\n",
    "fig_tra.update_geos(fitbounds=\"locations\", visible=False)\n",
    "fig_tra.update_layout(title_font=dict(size=33), title_font_color='#6739b7', height=800)\n",
    "\n",
    "st.plotly_chart(fig_tra, use_container_width=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>District</th>\n",
       "      <th>Count</th>\n",
       "      <th>Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>north and middle andaman district</td>\n",
       "      <td>442</td>\n",
       "      <td>9.316631e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>south andaman district</td>\n",
       "      <td>5688</td>\n",
       "      <td>1.256025e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>nicobars district</td>\n",
       "      <td>528</td>\n",
       "      <td>1.139849e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>north and middle andaman district</td>\n",
       "      <td>825</td>\n",
       "      <td>1.317863e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>south andaman district</td>\n",
       "      <td>9395</td>\n",
       "      <td>2.394824e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14631</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>nadia district</td>\n",
       "      <td>12690126</td>\n",
       "      <td>2.804568e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14632</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>birbhum district</td>\n",
       "      <td>7617444</td>\n",
       "      <td>1.614650e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14633</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>purba medinipur district</td>\n",
       "      <td>14484229</td>\n",
       "      <td>3.309949e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14634</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>maldah district</td>\n",
       "      <td>12492746</td>\n",
       "      <td>2.721861e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14635</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>darjiling district</td>\n",
       "      <td>8827502</td>\n",
       "      <td>1.801650e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14636 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           state  year  quarter  \\\n",
       "0      andaman-&-nicobar-islands  2018        1   \n",
       "1      andaman-&-nicobar-islands  2018        1   \n",
       "2      andaman-&-nicobar-islands  2018        1   \n",
       "3      andaman-&-nicobar-islands  2018        2   \n",
       "4      andaman-&-nicobar-islands  2018        2   \n",
       "...                          ...   ...      ...   \n",
       "14631                west-bengal  2022        4   \n",
       "14632                west-bengal  2022        4   \n",
       "14633                west-bengal  2022        4   \n",
       "14634                west-bengal  2022        4   \n",
       "14635                west-bengal  2022        4   \n",
       "\n",
       "                                District     Count        Amount  \n",
       "0      north and middle andaman district       442  9.316631e+05  \n",
       "1                 south andaman district      5688  1.256025e+07  \n",
       "2                      nicobars district       528  1.139849e+06  \n",
       "3      north and middle andaman district       825  1.317863e+06  \n",
       "4                 south andaman district      9395  2.394824e+07  \n",
       "...                                  ...       ...           ...  \n",
       "14631                     nadia district  12690126  2.804568e+10  \n",
       "14632                   birbhum district   7617444  1.614650e+10  \n",
       "14633           purba medinipur district  14484229  3.309949e+10  \n",
       "14634                    maldah district  12492746  2.721861e+10  \n",
       "14635                 darjiling district   8827502  1.801650e+10  \n",
       "\n",
       "[14636 rows x 6 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data query for map_trans :\n",
    "# Creating connection with MySQL workbench and retrieve the data from the \"agg_trans\" table as dataframe:\n",
    "mydb = sql.connect(host=\"localhost\",\n",
    "                   user=\"root\", \n",
    "                   password=\"********\", \n",
    "                   database=\"phonepe_pulse\")\n",
    "mycursor = mydb.cursor()\n",
    "\n",
    "mycursor.execute(\"SELECT state, year, quarter, District, Count, Amount FROM map_trans\")\n",
    "data = mycursor.fetchall()\n",
    "columns = [\"state\", \"year\", \"quarter\", \"District\", \"Count\", \"Amount\"]\n",
    "df3 = pd.DataFrame(data, columns=columns)\n",
    "df3\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# download to local drive as csv file ;\n",
    "df3.to_csv('C:/Users/user/Desktop/guvi pro/phonepepro/data/histogram_district.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>District</th>\n",
       "      <th>RegisteredUser</th>\n",
       "      <th>AppOpens</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>north and middle andaman district</td>\n",
       "      <td>632</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>south andaman district</td>\n",
       "      <td>5846</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>nicobars district</td>\n",
       "      <td>262</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>north and middle andaman district</td>\n",
       "      <td>911</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>south andaman district</td>\n",
       "      <td>8143</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14635</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>nadia district</td>\n",
       "      <td>1359420</td>\n",
       "      <td>33853990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14636</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>birbhum district</td>\n",
       "      <td>855236</td>\n",
       "      <td>20950662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14637</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>purba medinipur district</td>\n",
       "      <td>1346908</td>\n",
       "      <td>38278506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14638</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>maldah district</td>\n",
       "      <td>954892</td>\n",
       "      <td>29023743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14639</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>darjiling district</td>\n",
       "      <td>564562</td>\n",
       "      <td>15982631</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14640 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           State  Year  Quarter  \\\n",
       "0      andaman-&-nicobar-islands  2018        1   \n",
       "1      andaman-&-nicobar-islands  2018        1   \n",
       "2      andaman-&-nicobar-islands  2018        1   \n",
       "3      andaman-&-nicobar-islands  2018        2   \n",
       "4      andaman-&-nicobar-islands  2018        2   \n",
       "...                          ...   ...      ...   \n",
       "14635                west-bengal  2022        4   \n",
       "14636                west-bengal  2022        4   \n",
       "14637                west-bengal  2022        4   \n",
       "14638                west-bengal  2022        4   \n",
       "14639                west-bengal  2022        4   \n",
       "\n",
       "                                District  RegisteredUser  AppOpens  \n",
       "0      north and middle andaman district             632         0  \n",
       "1                 south andaman district            5846         0  \n",
       "2                      nicobars district             262         0  \n",
       "3      north and middle andaman district             911         0  \n",
       "4                 south andaman district            8143         0  \n",
       "...                                  ...             ...       ...  \n",
       "14635                     nadia district         1359420  33853990  \n",
       "14636                   birbhum district          855236  20950662  \n",
       "14637           purba medinipur district         1346908  38278506  \n",
       "14638                    maldah district          954892  29023743  \n",
       "14639                 darjiling district          564562  15982631  \n",
       "\n",
       "[14640 rows x 6 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# for voline plot :\n",
    "mycursor.execute(\"SELECT State, Year, Quarter, District, RegisteredUser, AppOpens FROM map_user\")\n",
    "data = mycursor.fetchall()\n",
    "columns = [\"State\", \"Year\", \"Quarter\", \"District\", \"RegisteredUser\", \"AppOpens\"]\n",
    "df4 = pd.DataFrame(data, columns=columns)\n",
    "df4\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# download to local drive as csv file ;\n",
    "df4.to_csv('C:/Users/user/Desktop/guvi pro/phonepepro/data/violin_plot.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assuming your DataFrame 'df' looks like this\n",
    "# df = pd.DataFrame({'states': [...state_names...]})\n",
    "\n",
    "# Define the conversion function\n",
    "def convert_state_name(state):\n",
    "    return state.replace('-', ' ').title()\n",
    "\n",
    "# Apply the conversion function to the 'states' column in the DataFrame\n",
    "df4['State'] = df4['State'].apply(lambda x: convert_state_name(x))\n",
    "\n",
    "# Assuming you have already converted and updated the DataFrame 'df'\n",
    "df4.to_csv('C:/Users/user/Desktop/guvi pro/phonepepro/data/state_map.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>District</th>\n",
       "      <th>RegisteredUser</th>\n",
       "      <th>AppOpens</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>north and middle andaman district</td>\n",
       "      <td>632</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>south andaman district</td>\n",
       "      <td>5846</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>nicobars district</td>\n",
       "      <td>262</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>north and middle andaman district</td>\n",
       "      <td>911</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>south andaman district</td>\n",
       "      <td>8143</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14635</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>nadia district</td>\n",
       "      <td>1359420</td>\n",
       "      <td>33853990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14636</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>birbhum district</td>\n",
       "      <td>855236</td>\n",
       "      <td>20950662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14637</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>purba medinipur district</td>\n",
       "      <td>1346908</td>\n",
       "      <td>38278506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14638</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>maldah district</td>\n",
       "      <td>954892</td>\n",
       "      <td>29023743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14639</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>darjiling district</td>\n",
       "      <td>564562</td>\n",
       "      <td>15982631</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14640 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           State  Year  Quarter  \\\n",
       "0      Andaman & Nicobar Islands  2018        1   \n",
       "1      Andaman & Nicobar Islands  2018        1   \n",
       "2      Andaman & Nicobar Islands  2018        1   \n",
       "3      Andaman & Nicobar Islands  2018        2   \n",
       "4      Andaman & Nicobar Islands  2018        2   \n",
       "...                          ...   ...      ...   \n",
       "14635                West Bengal  2022        4   \n",
       "14636                West Bengal  2022        4   \n",
       "14637                West Bengal  2022        4   \n",
       "14638                West Bengal  2022        4   \n",
       "14639                West Bengal  2022        4   \n",
       "\n",
       "                                District  RegisteredUser  AppOpens  \n",
       "0      north and middle andaman district             632         0  \n",
       "1                 south andaman district            5846         0  \n",
       "2                      nicobars district             262         0  \n",
       "3      north and middle andaman district             911         0  \n",
       "4                 south andaman district            8143         0  \n",
       "...                                  ...             ...       ...  \n",
       "14635                     nadia district         1359420  33853990  \n",
       "14636                   birbhum district          855236  20950662  \n",
       "14637           purba medinipur district         1346908  38278506  \n",
       "14638                    maldah district          954892  29023743  \n",
       "14639                 darjiling district          564562  15982631  \n",
       "\n",
       "[14640 rows x 6 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read the \"state_map\" CSV file into a DataFrame:\n",
    "df4 = pd.read_csv('C:/Users/user/Desktop/guvi pro/phonepepro/data/state_map.csv')\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>transaction_type</th>\n",
       "      <th>transaction_count</th>\n",
       "      <th>transaction_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Recharge &amp; bill payments</td>\n",
       "      <td>4200</td>\n",
       "      <td>1.845307e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Peer-to-peer payments</td>\n",
       "      <td>1871</td>\n",
       "      <td>1.213866e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Merchant payments</td>\n",
       "      <td>298</td>\n",
       "      <td>4.525072e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>33</td>\n",
       "      <td>1.060142e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>andaman-&amp;-nicobar-islands</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>Others</td>\n",
       "      <td>256</td>\n",
       "      <td>1.846899e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7183</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>Peer-to-peer payments</td>\n",
       "      <td>184380244</td>\n",
       "      <td>6.202222e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7184</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>Merchant payments</td>\n",
       "      <td>171667404</td>\n",
       "      <td>1.408077e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7185</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>Recharge &amp; bill payments</td>\n",
       "      <td>48921147</td>\n",
       "      <td>2.602663e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7186</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>268388</td>\n",
       "      <td>2.611229e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7187</th>\n",
       "      <td>west-bengal</td>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>Others</td>\n",
       "      <td>610414</td>\n",
       "      <td>4.579379e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7188 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          state  year  quarter          transaction_type  \\\n",
       "0     andaman-&-nicobar-islands  2018        1  Recharge & bill payments   \n",
       "1     andaman-&-nicobar-islands  2018        1     Peer-to-peer payments   \n",
       "2     andaman-&-nicobar-islands  2018        1         Merchant payments   \n",
       "3     andaman-&-nicobar-islands  2018        1        Financial Services   \n",
       "4     andaman-&-nicobar-islands  2018        1                    Others   \n",
       "...                         ...   ...      ...                       ...   \n",
       "7183                west-bengal  2022        4     Peer-to-peer payments   \n",
       "7184                west-bengal  2022        4         Merchant payments   \n",
       "7185                west-bengal  2022        4  Recharge & bill payments   \n",
       "7186                west-bengal  2022        4        Financial Services   \n",
       "7187                west-bengal  2022        4                    Others   \n",
       "\n",
       "      transaction_count  transaction_amount  \n",
       "0                  4200        1.845307e+06  \n",
       "1                  1871        1.213866e+07  \n",
       "2                   298        4.525072e+05  \n",
       "3                    33        1.060142e+04  \n",
       "4                   256        1.846899e+05  \n",
       "...                 ...                 ...  \n",
       "7183          184380244        6.202222e+11  \n",
       "7184          171667404        1.408077e+11  \n",
       "7185           48921147        2.602663e+10  \n",
       "7186             268388        2.611229e+08  \n",
       "7187             610414        4.579379e+08  \n",
       "\n",
       "[7188 rows x 6 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mycursor = mydb.cursor()\n",
    "\n",
    "mycursor.execute(\"SELECT state, year, quarter, transaction_type, transaction_count, transaction_amount FROM agg_trans\")\n",
    "data = mycursor.fetchall()\n",
    "columns = [\"state\", \"year\", \"quarter\", \"transaction_type\", \"transaction_count\", \"transaction_amount\"]\n",
    "df = pd.DataFrame(data, columns=columns)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    7.188000e+03\n",
       "mean     3.378017e+10\n",
       "std      1.417097e+11\n",
       "min      3.439721e+01\n",
       "25%      2.680468e+07\n",
       "50%      2.862499e+08\n",
       "75%      5.642295e+09\n",
       "max      2.393380e+12\n",
       "Name: transaction_amount, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df['transaction_amount'].describe()\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}