Examples/pokemon-trainer/cookbook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The Pokemon Cookbook\n",
    "This cookbook teaches you the concepts of the InfluxDB 3.0 Python Client library using a novel example of Pokemon data. The scenerio is to keep track of each trainer and the number of different pokemon they have caught.\n",
    "\n",
    "<p align=\"center\">\n",
    "<img height=\"300\" src=\"https://www.nicepng.com/png/full/62-622961_no-one-knows-if-people-eat-pokmon-png.png\">\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Here we include all the imports required from influxdb_client_3\n",
    "from influxdb_client_3 import InfluxDBClient3, InfluxDBError, WriteOptions, write_client_options\n",
    "import pandas as pd\n",
    "import random"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing Data\n",
    "The first step is to write data into InfluxDB. We will use the `write_api` to write data into InfluxDB. In this example we are going to utilise `batching mode` to write data in batches. This is the most efficient way to write data into InfluxDB. To do this we are going to first setup some paramters for our client."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This class handles the callbacks for the batching\n",
    "class BatchingCallback(object):\n",
    "\n",
    "    def success(self, conf, data: str):\n",
    "        print(f\"Written batch: {conf}\")\n",
    "\n",
    "    def error(self, conf, data: str, exception: InfluxDBError):\n",
    "        print(f\"Cannot write batch: {conf}, data: {data} due: {exception}\")\n",
    "\n",
    "    def retry(self, conf, data: str, exception: InfluxDBError):\n",
    "        print(f\"Retryable error occurs for batch: {conf}, data: {data} retry: {exception}\")\n",
    "\n",
    "callback = BatchingCallback()\n",
    "\n",
    "# This is the configuration for the batching. This is wrapped in a WriteOptions object. Within this example you\n",
    "# can see the different options that can be set for the batching.\n",
    "# Batch size is the number of points to write before the batch is written to the server.\n",
    "# Flush interval is the time in milliseconds to wait before the batch is written to the server.\n",
    "# Jitter interval is the time in milliseconds to wait before the batch is written to the server.\n",
    "# Retry interval is the time in milliseconds to wait before retrying a failed batch.\n",
    "# Max retries is the maximum number of times to retry a failed batch.\n",
    "# exponential base is the base for the exponential retry delay.\n",
    "write_options = WriteOptions(batch_size=100,\n",
    "                                        flush_interval=10_000,\n",
    "                                        jitter_interval=2_000,\n",
    "                                        retry_interval=5_000,\n",
    "                                        max_retries=5,\n",
    "                                        max_retry_delay=30_000,\n",
    "                                        exponential_base=2)\n",
    "\n",
    "\n",
    "# This is the configuration for the write client. This is wrapped in a WriteClientOptions object.\n",
    "# As you can see we incldue the BatchingCallback object we created earlier, plus the write_options.\n",
    "wco = write_client_options(success_callback=callback.success,\n",
    "                          error_callback=callback.error,\n",
    "                          retry_callback=callback.retry,\n",
    "                          write_options=write_options\n",
    "                        )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Client Setup\n",
    "Now that we have done the inital configurations of our write paramters its time to include these within our client initalization. The InfluxDB 3.0 Client can both write and query data. For now we will use it to write data based upon our configuration."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# In this example we are using the InfluxDBClient3 object to connect to the InfluxDB Cloud instance.\n",
    "# We are also passing in the write_client_options we created earlier.\n",
    "# The token and host are required to connect to the InfluxDB Serverless instance.\n",
    "# Note: that Org is optional with Dedicated instances.\n",
    "client = InfluxDBClient3(\n",
    "    token=\"phVXVXPBZ12vyNvVGJJX6vngFa4zyCYIzgDTYRJsPyIUSi3r78agctL2ksCH1GlUxtIWdGFWL5ScxQxUnbQOAQ==\",\n",
    "    host=\"eu-central-1-1.aws.cloud2.influxdata.com\",\n",
    "    database=\"pokemon-codex\", enable_gzip=True, write_client_options=wco)\n",
    "\n",
    "now =  pd.Timestamp.now(tz='UTC').floor('ms')\n",
    "\n",
    "# Lists of possible trainers\n",
    "trainers = [\"ash\", \"brock\", \"misty\", \"gary\", \"jessie\", \"james\"]\n",
    "\n",
    "# Read the CSV into a DataFrame. (Credit to @ritchie46 for the dataset)\n",
    "pokemon_df = pd.read_csv(\"./pokemon.csv\")\n",
    "\n",
    "# Creating an empty list to store the data\n",
    "data = []\n",
    "\n",
    "# Dictionary to keep track of the number of times each trainer has caught each Pokémon\n",
    "trainer_pokemon_counts = {}\n",
    "\n",
    "# Number of entries we want to create\n",
    "num_entries = 1000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generate some data\n",
    "Now that we have our client setup lets start generating some data we can write to InfluxDB. Following the Pokemon example we will create a list of trainers and the number of pokemon they have caught. Trainers will catch pokemon randomly selected from our list stored within the Pandas DataFrame `pokemon_df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>trainer</th>\n",
       "      <th>id</th>\n",
       "      <th>num</th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "      <th>attack</th>\n",
       "      <th>defense</th>\n",
       "      <th>hp</th>\n",
       "      <th>speed</th>\n",
       "      <th>type1</th>\n",
       "      <th>type2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:44:44.001000+00:00</th>\n",
       "      <td>ash</td>\n",
       "      <td>0104</td>\n",
       "      <td>1</td>\n",
       "      <td>Cubone</td>\n",
       "      <td>9</td>\n",
       "      <td>50</td>\n",
       "      <td>95</td>\n",
       "      <td>50</td>\n",
       "      <td>35</td>\n",
       "      <td>Ground</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:44:44.001000+00:00</th>\n",
       "      <td>brock</td>\n",
       "      <td>0146</td>\n",
       "      <td>1</td>\n",
       "      <td>Moltres</td>\n",
       "      <td>19</td>\n",
       "      <td>100</td>\n",
       "      <td>90</td>\n",
       "      <td>90</td>\n",
       "      <td>90</td>\n",
       "      <td>Fire</td>\n",
       "      <td>Flying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:44:44.001000+00:00</th>\n",
       "      <td>jessie</td>\n",
       "      <td>0071</td>\n",
       "      <td>1</td>\n",
       "      <td>Victreebel</td>\n",
       "      <td>6</td>\n",
       "      <td>105</td>\n",
       "      <td>65</td>\n",
       "      <td>80</td>\n",
       "      <td>70</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:44:44.001000+00:00</th>\n",
       "      <td>jessie</td>\n",
       "      <td>0070</td>\n",
       "      <td>1</td>\n",
       "      <td>Weepinbell</td>\n",
       "      <td>19</td>\n",
       "      <td>90</td>\n",
       "      <td>50</td>\n",
       "      <td>65</td>\n",
       "      <td>55</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:44:44.001000+00:00</th>\n",
       "      <td>brock</td>\n",
       "      <td>0102</td>\n",
       "      <td>1</td>\n",
       "      <td>Exeggcute</td>\n",
       "      <td>13</td>\n",
       "      <td>40</td>\n",
       "      <td>80</td>\n",
       "      <td>60</td>\n",
       "      <td>40</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Psychic</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:44:44.001000+00:00</th>\n",
       "      <td>james</td>\n",
       "      <td>0138</td>\n",
       "      <td>5</td>\n",
       "      <td>Omanyte</td>\n",
       "      <td>20</td>\n",
       "      <td>40</td>\n",
       "      <td>100</td>\n",
       "      <td>35</td>\n",
       "      <td>35</td>\n",
       "      <td>Rock</td>\n",
       "      <td>Water</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:44:44.001000+00:00</th>\n",
       "      <td>misty</td>\n",
       "      <td>0056</td>\n",
       "      <td>3</td>\n",
       "      <td>Mankey</td>\n",
       "      <td>20</td>\n",
       "      <td>80</td>\n",
       "      <td>35</td>\n",
       "      <td>40</td>\n",
       "      <td>70</td>\n",
       "      <td>Fighting</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:44:44.001000+00:00</th>\n",
       "      <td>gary</td>\n",
       "      <td>0134</td>\n",
       "      <td>2</td>\n",
       "      <td>Vaporeon</td>\n",
       "      <td>7</td>\n",
       "      <td>65</td>\n",
       "      <td>60</td>\n",
       "      <td>130</td>\n",
       "      <td>65</td>\n",
       "      <td>Water</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:44:44.001000+00:00</th>\n",
       "      <td>jessie</td>\n",
       "      <td>0001</td>\n",
       "      <td>2</td>\n",
       "      <td>Bulbasaur</td>\n",
       "      <td>7</td>\n",
       "      <td>49</td>\n",
       "      <td>49</td>\n",
       "      <td>45</td>\n",
       "      <td>45</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:44:44.001000+00:00</th>\n",
       "      <td>james</td>\n",
       "      <td>0111</td>\n",
       "      <td>3</td>\n",
       "      <td>Rhyhorn</td>\n",
       "      <td>9</td>\n",
       "      <td>85</td>\n",
       "      <td>95</td>\n",
       "      <td>80</td>\n",
       "      <td>25</td>\n",
       "      <td>Ground</td>\n",
       "      <td>Rock</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 trainer    id num        name  level  attack  \\\n",
       "timestamp                                                                       \n",
       "2023-07-27 10:44:44.001000+00:00     ash  0104   1      Cubone      9      50   \n",
       "2023-07-27 10:44:44.001000+00:00   brock  0146   1     Moltres     19     100   \n",
       "2023-07-27 10:44:44.001000+00:00  jessie  0071   1  Victreebel      6     105   \n",
       "2023-07-27 10:44:44.001000+00:00  jessie  0070   1  Weepinbell     19      90   \n",
       "2023-07-27 10:44:44.001000+00:00   brock  0102   1   Exeggcute     13      40   \n",
       "...                                  ...   ...  ..         ...    ...     ...   \n",
       "2023-07-27 10:44:44.001000+00:00   james  0138   5     Omanyte     20      40   \n",
       "2023-07-27 10:44:44.001000+00:00   misty  0056   3      Mankey     20      80   \n",
       "2023-07-27 10:44:44.001000+00:00    gary  0134   2    Vaporeon      7      65   \n",
       "2023-07-27 10:44:44.001000+00:00  jessie  0001   2   Bulbasaur      7      49   \n",
       "2023-07-27 10:44:44.001000+00:00   james  0111   3     Rhyhorn      9      85   \n",
       "\n",
       "                                  defense   hp  speed     type1    type2  \n",
       "timestamp                                                                 \n",
       "2023-07-27 10:44:44.001000+00:00       95   50     35    Ground      NaN  \n",
       "2023-07-27 10:44:44.001000+00:00       90   90     90      Fire   Flying  \n",
       "2023-07-27 10:44:44.001000+00:00       65   80     70     Grass   Poison  \n",
       "2023-07-27 10:44:44.001000+00:00       50   65     55     Grass   Poison  \n",
       "2023-07-27 10:44:44.001000+00:00       80   60     40     Grass  Psychic  \n",
       "...                                   ...  ...    ...       ...      ...  \n",
       "2023-07-27 10:44:44.001000+00:00      100   35     35      Rock    Water  \n",
       "2023-07-27 10:44:44.001000+00:00       35   40     70  Fighting      NaN  \n",
       "2023-07-27 10:44:44.001000+00:00       60  130     65     Water      NaN  \n",
       "2023-07-27 10:44:44.001000+00:00       49   45     45     Grass   Poison  \n",
       "2023-07-27 10:44:44.001000+00:00       95   80     25    Ground     Rock  \n",
       "\n",
       "[1000 rows x 11 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.display import display, HTML\n",
    "\n",
    "# Generating random data\n",
    "for i in range(num_entries):\n",
    "    trainer = random.choice(trainers)\n",
    "    \n",
    "    # Randomly select a row from pokemon_df\n",
    "    random_pokemon = pokemon_df.sample().iloc[0]\n",
    "    caught = random_pokemon['Name']\n",
    "    \n",
    "    # Count the number of times this trainer has caught this Pokémon\n",
    "    if (trainer, caught) in trainer_pokemon_counts:\n",
    "        trainer_pokemon_counts[(trainer, caught)] += 1\n",
    "    else:\n",
    "        trainer_pokemon_counts[(trainer, caught)] = 1\n",
    "    \n",
    "    # Get the number for this combination of trainer and Pokémon\n",
    "    num = trainer_pokemon_counts[(trainer, caught)]\n",
    "\n",
    "    entry = {\n",
    "        \"trainer\": trainer,\n",
    "        \"id\": f\"{0000 + random_pokemon['#']:04d}\",\n",
    "        \"num\": str(num),\n",
    "        \"name\": caught,\n",
    "        \"level\": random.randint(5, 20),\n",
    "        \"attack\": random_pokemon['Attack'],\n",
    "        \"defense\": random_pokemon['Defense'],\n",
    "        \"hp\": random_pokemon['HP'],\n",
    "        \"speed\": random_pokemon['Speed'],\n",
    "        \"type1\": random_pokemon['Type 1'],\n",
    "        \"type2\": random_pokemon['Type 2'],\n",
    "        \"timestamp\": now\n",
    "    }\n",
    "    data.append(entry)\n",
    "\n",
    "# Convert the list of dictionaries to a DataFrame\n",
    "caught_pokemon_df = pd.DataFrame(data).set_index('timestamp')\n",
    "\n",
    "# Print the DataFrame\n",
    "display(caught_pokemon_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write Data to InfluxDB\n",
    "We will now write our newley created trainer data to InfluxDB. To do this we simply call client.write and pass in our dataframe. We then provide a static measurement name of `caught`. We also provide a series of tags to help identify our data. In this case we use the columns `['trainer', 'id', 'num']`. Note that we didn't provide our time column this is due to the fact we set this column as our `dataframe_index`. This means that the index column will be used as the time column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "\n",
    "try:\n",
    "    client.write(caught_pokemon_df, data_frame_measurement_name='caught',\n",
    "             data_frame_tag_columns=['trainer', 'id', 'num'])\n",
    "except Exception as e:\n",
    "    print(f\"Error writing point: {e}\")\n",
    "\n",
    "# Wait for the batch to be written\n",
    "time.sleep(2)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Querying Data\n",
    "We have now stored 1000 registered pokemon catches within InfluxDB. We can now query this data using the InfluxDB 3.0 Python Client to gain some insights into our data. We are going to use Plotly to visualise our data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>table_catalog</th>\n",
       "      <th>table_schema</th>\n",
       "      <th>table_name</th>\n",
       "      <th>column_name</th>\n",
       "      <th>data_type</th>\n",
       "      <th>is_nullable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>public</td>\n",
       "      <td>iox</td>\n",
       "      <td>caught</td>\n",
       "      <td>attack</td>\n",
       "      <td>Int64</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>public</td>\n",
       "      <td>iox</td>\n",
       "      <td>caught</td>\n",
       "      <td>defense</td>\n",
       "      <td>Int64</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>public</td>\n",
       "      <td>iox</td>\n",
       "      <td>caught</td>\n",
       "      <td>hp</td>\n",
       "      <td>Int64</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>public</td>\n",
       "      <td>iox</td>\n",
       "      <td>caught</td>\n",
       "      <td>id</td>\n",
       "      <td>Dictionary(Int32, Utf8)</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>public</td>\n",
       "      <td>iox</td>\n",
       "      <td>caught</td>\n",
       "      <td>level</td>\n",
       "      <td>Int64</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>public</td>\n",
       "      <td>iox</td>\n",
       "      <td>caught</td>\n",
       "      <td>name</td>\n",
       "      <td>Utf8</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>public</td>\n",
       "      <td>iox</td>\n",
       "      <td>caught</td>\n",
       "      <td>num</td>\n",
       "      <td>Dictionary(Int32, Utf8)</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>public</td>\n",
       "      <td>iox</td>\n",
       "      <td>caught</td>\n",
       "      <td>speed</td>\n",
       "      <td>Int64</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>public</td>\n",
       "      <td>iox</td>\n",
       "      <td>caught</td>\n",
       "      <td>time</td>\n",
       "      <td>Timestamp(Nanosecond, None)</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>public</td>\n",
       "      <td>iox</td>\n",
       "      <td>caught</td>\n",
       "      <td>trainer</td>\n",
       "      <td>Dictionary(Int32, Utf8)</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>public</td>\n",
       "      <td>iox</td>\n",
       "      <td>caught</td>\n",
       "      <td>type1</td>\n",
       "      <td>Utf8</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>public</td>\n",
       "      <td>iox</td>\n",
       "      <td>caught</td>\n",
       "      <td>type2</td>\n",
       "      <td>Utf8</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   table_catalog table_schema table_name column_name  \\\n",
       "0         public          iox     caught      attack   \n",
       "1         public          iox     caught     defense   \n",
       "2         public          iox     caught          hp   \n",
       "3         public          iox     caught          id   \n",
       "4         public          iox     caught       level   \n",
       "5         public          iox     caught        name   \n",
       "6         public          iox     caught         num   \n",
       "7         public          iox     caught       speed   \n",
       "8         public          iox     caught        time   \n",
       "9         public          iox     caught     trainer   \n",
       "10        public          iox     caught       type1   \n",
       "11        public          iox     caught       type2   \n",
       "\n",
       "                      data_type is_nullable  \n",
       "0                         Int64         YES  \n",
       "1                         Int64         YES  \n",
       "2                         Int64         YES  \n",
       "3       Dictionary(Int32, Utf8)         YES  \n",
       "4                         Int64         YES  \n",
       "5                          Utf8         YES  \n",
       "6       Dictionary(Int32, Utf8)         YES  \n",
       "7                         Int64         YES  \n",
       "8   Timestamp(Nanosecond, None)          NO  \n",
       "9       Dictionary(Int32, Utf8)         YES  \n",
       "10                         Utf8         YES  \n",
       "11                         Utf8         YES  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# These are just some library imports for Plotly so we can make use of the interactive graphs.\n",
    "import plotly.express as px\n",
    "import plotly.io as pio\n",
    "pio.renderers.default = \"vscode\"\n",
    "\n",
    "# Lets start with a simple query to understand our schema.\n",
    "query = '''SHOW COLUMNS FROM caught'''\n",
    "\n",
    "# We can use the query method to run a query against the database.\n",
    "# Under the hood this creates a flight ticket and uses the FlightClient to run the query.\n",
    "# For this example we are using the pandas mode, which will return a pandas DataFrame.\n",
    "# Language also allows us to specify the query language, in this case we are using SQL.\n",
    "table = client.query(query=query, language='sql', mode='pandas')\n",
    "\n",
    "display(table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Simple InfluxQL Query\n",
    "The first query we will run is a simple InfluxQL query to get the number of pokemon caught by each trainer. We will then use Plotly to visualise this data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "alignmentgroup": "True",
         "hovertemplate": "trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "ash",
         "marker": {
          "color": "#636efa",
          "pattern": {
           "shape": ""
          }
         },
         "name": "ash",
         "offsetgroup": "ash",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash"
         ],
         "xaxis": "x",
         "y": [
          146
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "brock",
         "marker": {
          "color": "#EF553B",
          "pattern": {
           "shape": ""
          }
         },
         "name": "brock",
         "offsetgroup": "brock",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "brock"
         ],
         "xaxis": "x",
         "y": [
          172
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "gary",
         "marker": {
          "color": "#00cc96",
          "pattern": {
           "shape": ""
          }
         },
         "name": "gary",
         "offsetgroup": "gary",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "gary"
         ],
         "xaxis": "x",
         "y": [
          162
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "james",
         "marker": {
          "color": "#ab63fa",
          "pattern": {
           "shape": ""
          }
         },
         "name": "james",
         "offsetgroup": "james",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "james"
         ],
         "xaxis": "x",
         "y": [
          162
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "jessie",
         "marker": {
          "color": "#FFA15A",
          "pattern": {
           "shape": ""
          }
         },
         "name": "jessie",
         "offsetgroup": "jessie",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "jessie"
         ],
         "xaxis": "x",
         "y": [
          155
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "misty",
         "marker": {
          "color": "#19d3f3",
          "pattern": {
           "shape": ""
          }
         },
         "name": "misty",
         "offsetgroup": "misty",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "misty"
         ],
         "xaxis": "x",
         "y": [
          166
         ],
         "yaxis": "y"
        }
       ],
       "layout": {
        "barmode": "relative",
        "legend": {
         "title": {
          "text": "trainer"
         },
         "tracegroupgap": 0
        },
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "heatmapgl": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmapgl"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "fillpattern": {
             "fillmode": "overlay",
             "size": 10,
             "solidity": 0.2
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "title": {
         "text": "Number of Pokémon caught in the last hour"
        },
        "xaxis": {
         "anchor": "y",
         "categoryarray": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "categoryorder": "array",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "trainer"
         }
        },
        "yaxis": {
         "anchor": "x",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "count"
         }
        }
       }
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Lets start with a simple query to understand our schema.\n",
    "query = '''SELECT count(\"name\") FROM caught WHERE time > now() - 1h GROUP BY trainer'''\n",
    "\n",
    "# We can use the query method to run a query against the database.\n",
    "# Under the hood this creates a flight ticket and uses the FlightClient to run the query.\n",
    "# For this example we are using the pandas mode, which will return a pandas DataFrame.\n",
    "# Language also allows us to specify the query language, in this case we are using SQL.\n",
    "table = client.query(query=query, language='influxql', mode='pandas')\n",
    "\n",
    "fig1 = px.bar(table, x=\"trainer\", y=\"count\",color='trainer' ,title='Number of Pokémon caught in the last hour')\n",
    "fig1.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Bug<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Bug",
         "marker": {
          "color": "#636efa",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Bug",
         "offsetgroup": "Bug",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          13,
          19,
          12,
          13,
          14,
          13
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Electric<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Electric",
         "marker": {
          "color": "#EF553B",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Electric",
         "offsetgroup": "Electric",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          7,
          13,
          11,
          5,
          8,
          6
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Fairy<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Fairy",
         "marker": {
          "color": "#00cc96",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Fairy",
         "offsetgroup": "Fairy",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          3,
          5,
          2,
          3,
          2,
          4
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Fighting<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Fighting",
         "marker": {
          "color": "#ab63fa",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Fighting",
         "offsetgroup": "Fighting",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          4,
          7,
          9,
          10,
          9,
          12
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Fire<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Fire",
         "marker": {
          "color": "#FFA15A",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Fire",
         "offsetgroup": "Fire",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          20,
          16,
          12,
          13,
          13,
          4
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Ghost<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Ghost",
         "marker": {
          "color": "#19d3f3",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Ghost",
         "offsetgroup": "Ghost",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          3,
          4,
          1,
          6,
          3,
          2
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Grass<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Grass",
         "marker": {
          "color": "#FF6692",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Grass",
         "offsetgroup": "Grass",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          12,
          13,
          12,
          11,
          11,
          14
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Ground<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Ground",
         "marker": {
          "color": "#B6E880",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Ground",
         "offsetgroup": "Ground",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          4,
          7,
          10,
          12,
          7,
          9
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Ice<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Ice",
         "marker": {
          "color": "#FF97FF",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Ice",
         "offsetgroup": "Ice",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          3,
          2,
          1,
          1,
          4,
          1
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Normal<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Normal",
         "marker": {
          "color": "#FECB52",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Normal",
         "offsetgroup": "Normal",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          19,
          24,
          21,
          14,
          22,
          29
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Poison<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Poison",
         "marker": {
          "color": "#636efa",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Poison",
         "offsetgroup": "Poison",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          12,
          13,
          12,
          17,
          11,
          15
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Psychic<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Psychic",
         "marker": {
          "color": "#EF553B",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Psychic",
         "offsetgroup": "Psychic",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          7,
          6,
          8,
          10,
          6,
          7
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Rock<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Rock",
         "marker": {
          "color": "#00cc96",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Rock",
         "offsetgroup": "Rock",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          11,
          9,
          11,
          15,
          16,
          12
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Water<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Water",
         "marker": {
          "color": "#ab63fa",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Water",
         "offsetgroup": "Water",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "brock",
          "gary",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          28,
          32,
          39,
          32,
          25,
          36
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Dragon<br>trainer=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "Dragon",
         "marker": {
          "color": "#FFA15A",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Dragon",
         "offsetgroup": "Dragon",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "brock",
          "gary",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          2,
          1,
          4,
          2
         ],
         "yaxis": "y"
        }
       ],
       "layout": {
        "barmode": "group",
        "legend": {
         "title": {
          "text": "type1"
         },
         "tracegroupgap": 0
        },
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "heatmapgl": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmapgl"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "fillpattern": {
             "fillmode": "overlay",
             "size": 10,
             "solidity": 0.2
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "title": {
         "text": "Number of Pokémon caught in the last hour grouped by type"
        },
        "xaxis": {
         "anchor": "y",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "trainer"
         }
        },
        "yaxis": {
         "anchor": "x",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "count"
         }
        }
       }
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Lets start with a simple query to understand our schema.\n",
    "query = '''SELECT count(\"name\") FROM caught WHERE time > now() - 1h GROUP BY trainer,type1'''\n",
    "\n",
    "# We can use the query method to run a query against the database.\n",
    "# Under the hood this creates a flight ticket and uses the FlightClient to run the query.\n",
    "# For this example we are using the pandas mode, which will return a pandas DataFrame.\n",
    "# Language also allows us to specify the query language, in this case we are using SQL.\n",
    "table = client.query(query=query, language='influxql' , mode='pandas')\n",
    "\n",
    "fig2 = px.bar(table, x=\"trainer\", y=\"count\", color='type1', barmode= 'group', title='Number of Pokémon caught in the last hour grouped by type')\n",
    "fig2.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Working with Arrow tables\n",
    "So within the last section we discussed converting returned queries directly to Pandas Dataframes. However, we can also utilise their raw format, Arrow tables. Arrow tables are a columnar format that is more efficient for working with data.\n",
    "\n",
    "Lets first start by adding more data with a random timestamp between now and 1 hour ago"
   ]
  },
  {
   "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>trainer</th>\n",
       "      <th>id</th>\n",
       "      <th>num</th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "      <th>attack</th>\n",
       "      <th>defense</th>\n",
       "      <th>hp</th>\n",
       "      <th>speed</th>\n",
       "      <th>type1</th>\n",
       "      <th>type2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:13:38.242000+00:00</th>\n",
       "      <td>james</td>\n",
       "      <td>0015</td>\n",
       "      <td>1</td>\n",
       "      <td>Beedrill</td>\n",
       "      <td>8</td>\n",
       "      <td>90</td>\n",
       "      <td>40</td>\n",
       "      <td>65</td>\n",
       "      <td>75</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Poison</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:24:38.242000+00:00</th>\n",
       "      <td>misty</td>\n",
       "      <td>0101</td>\n",
       "      <td>3</td>\n",
       "      <td>Electrode</td>\n",
       "      <td>17</td>\n",
       "      <td>50</td>\n",
       "      <td>70</td>\n",
       "      <td>60</td>\n",
       "      <td>140</td>\n",
       "      <td>Electric</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:10:38.242000+00:00</th>\n",
       "      <td>brock</td>\n",
       "      <td>0025</td>\n",
       "      <td>3</td>\n",
       "      <td>Pikachu</td>\n",
       "      <td>10</td>\n",
       "      <td>55</td>\n",
       "      <td>40</td>\n",
       "      <td>35</td>\n",
       "      <td>90</td>\n",
       "      <td>Electric</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:47:38.242000+00:00</th>\n",
       "      <td>misty</td>\n",
       "      <td>0135</td>\n",
       "      <td>2</td>\n",
       "      <td>Jolteon</td>\n",
       "      <td>20</td>\n",
       "      <td>65</td>\n",
       "      <td>60</td>\n",
       "      <td>65</td>\n",
       "      <td>130</td>\n",
       "      <td>Electric</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:14:38.242000+00:00</th>\n",
       "      <td>ash</td>\n",
       "      <td>0022</td>\n",
       "      <td>2</td>\n",
       "      <td>Fearow</td>\n",
       "      <td>13</td>\n",
       "      <td>90</td>\n",
       "      <td>65</td>\n",
       "      <td>65</td>\n",
       "      <td>100</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Flying</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:40:38.242000+00:00</th>\n",
       "      <td>james</td>\n",
       "      <td>0133</td>\n",
       "      <td>10</td>\n",
       "      <td>Eevee</td>\n",
       "      <td>13</td>\n",
       "      <td>55</td>\n",
       "      <td>50</td>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>Normal</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:11:38.242000+00:00</th>\n",
       "      <td>james</td>\n",
       "      <td>0110</td>\n",
       "      <td>12</td>\n",
       "      <td>Weezing</td>\n",
       "      <td>10</td>\n",
       "      <td>90</td>\n",
       "      <td>120</td>\n",
       "      <td>65</td>\n",
       "      <td>60</td>\n",
       "      <td>Poison</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:19:38.242000+00:00</th>\n",
       "      <td>james</td>\n",
       "      <td>0072</td>\n",
       "      <td>8</td>\n",
       "      <td>Tentacool</td>\n",
       "      <td>5</td>\n",
       "      <td>40</td>\n",
       "      <td>35</td>\n",
       "      <td>40</td>\n",
       "      <td>70</td>\n",
       "      <td>Water</td>\n",
       "      <td>Poison</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:18:38.242000+00:00</th>\n",
       "      <td>brock</td>\n",
       "      <td>0110</td>\n",
       "      <td>17</td>\n",
       "      <td>Weezing</td>\n",
       "      <td>12</td>\n",
       "      <td>90</td>\n",
       "      <td>120</td>\n",
       "      <td>65</td>\n",
       "      <td>60</td>\n",
       "      <td>Poison</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-27 10:30:38.242000+00:00</th>\n",
       "      <td>misty</td>\n",
       "      <td>0094</td>\n",
       "      <td>16</td>\n",
       "      <td>Gengar</td>\n",
       "      <td>11</td>\n",
       "      <td>65</td>\n",
       "      <td>60</td>\n",
       "      <td>60</td>\n",
       "      <td>110</td>\n",
       "      <td>Ghost</td>\n",
       "      <td>Poison</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10000 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 trainer    id num       name  level  attack  \\\n",
       "timestamp                                                                      \n",
       "2023-07-27 10:13:38.242000+00:00   james  0015   1   Beedrill      8      90   \n",
       "2023-07-27 10:24:38.242000+00:00   misty  0101   3  Electrode     17      50   \n",
       "2023-07-27 10:10:38.242000+00:00   brock  0025   3    Pikachu     10      55   \n",
       "2023-07-27 10:47:38.242000+00:00   misty  0135   2    Jolteon     20      65   \n",
       "2023-07-27 10:14:38.242000+00:00     ash  0022   2     Fearow     13      90   \n",
       "...                                  ...   ...  ..        ...    ...     ...   \n",
       "2023-07-27 10:40:38.242000+00:00   james  0133  10      Eevee     13      55   \n",
       "2023-07-27 10:11:38.242000+00:00   james  0110  12    Weezing     10      90   \n",
       "2023-07-27 10:19:38.242000+00:00   james  0072   8  Tentacool      5      40   \n",
       "2023-07-27 10:18:38.242000+00:00   brock  0110  17    Weezing     12      90   \n",
       "2023-07-27 10:30:38.242000+00:00   misty  0094  16     Gengar     11      65   \n",
       "\n",
       "                                  defense  hp  speed     type1   type2  \n",
       "timestamp                                                               \n",
       "2023-07-27 10:13:38.242000+00:00       40  65     75       Bug  Poison  \n",
       "2023-07-27 10:24:38.242000+00:00       70  60    140  Electric     NaN  \n",
       "2023-07-27 10:10:38.242000+00:00       40  35     90  Electric     NaN  \n",
       "2023-07-27 10:47:38.242000+00:00       60  65    130  Electric     NaN  \n",
       "2023-07-27 10:14:38.242000+00:00       65  65    100    Normal  Flying  \n",
       "...                                   ...  ..    ...       ...     ...  \n",
       "2023-07-27 10:40:38.242000+00:00       50  55     55    Normal     NaN  \n",
       "2023-07-27 10:11:38.242000+00:00      120  65     60    Poison     NaN  \n",
       "2023-07-27 10:19:38.242000+00:00       35  40     70     Water  Poison  \n",
       "2023-07-27 10:18:38.242000+00:00      120  65     60    Poison     NaN  \n",
       "2023-07-27 10:30:38.242000+00:00       60  60    110     Ghost  Poison  \n",
       "\n",
       "[10000 rows x 11 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n"
     ]
    }
   ],
   "source": [
    "num_entries = 10000 # You can reduce this if required. The more data the more interesting the results.\n",
    "now =  pd.Timestamp.now(tz='UTC').floor('ms')\n",
    "data = []\n",
    "\n",
    "\n",
    "# Generating random data\n",
    "for i in range(num_entries):\n",
    "    # Randomise the timestamp\n",
    "    timestamp = now - pd.Timedelta(minutes=random.randint(0, 60))\n",
    "    trainer = random.choice(trainers)\n",
    "    \n",
    "    # Randomly select a row from pokemon_df\n",
    "    random_pokemon = pokemon_df.sample().iloc[0]\n",
    "    caught = random_pokemon['Name']\n",
    "    \n",
    "    # Count the number of times this trainer has caught this Pokémon\n",
    "    if (trainer, caught) in trainer_pokemon_counts:\n",
    "        trainer_pokemon_counts[(trainer, caught)] += 1\n",
    "    else:\n",
    "        trainer_pokemon_counts[(trainer, caught)] = 1\n",
    "    \n",
    "    # Get the number for this combination of trainer and Pokémon\n",
    "    num = trainer_pokemon_counts[(trainer, caught)]\n",
    "\n",
    "    entry = {\n",
    "        \"trainer\": trainer,\n",
    "        \"id\": f\"{0000 + random_pokemon['#']:04d}\",\n",
    "        \"num\": str(num),\n",
    "        \"name\": caught,\n",
    "        \"level\": random.randint(5, 20),\n",
    "        \"attack\": random_pokemon['Attack'],\n",
    "        \"defense\": random_pokemon['Defense'],\n",
    "        \"hp\": random_pokemon['HP'],\n",
    "        \"speed\": random_pokemon['Speed'],\n",
    "        \"type1\": random_pokemon['Type 1'],\n",
    "        \"type2\": random_pokemon['Type 2'],\n",
    "        \"timestamp\": timestamp\n",
    "    }\n",
    "    data.append(entry)\n",
    "\n",
    "# Convert the list of dictionaries to a DataFrame\n",
    "caught_pokemon_df = pd.DataFrame(data).set_index('timestamp')\n",
    "\n",
    "# Print the DataFrame\n",
    "display(caught_pokemon_df)\n",
    "\n",
    "try:\n",
    "    client.write(caught_pokemon_df, data_frame_measurement_name='kanto',\n",
    "             data_frame_tag_columns=['trainer', 'id', 'num'])\n",
    "except Exception as e:\n",
    "    print(f\"Error writing point: {e}\")\n",
    "\n",
    "# Wait for the batch to be written\n",
    "time.sleep(5)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now lets return the result as an Arrow table. We can recreate he same aggregation we did on server side with the pyarrow library. the `mode='all'` parameter tells InfluxDB to return all data within the query result as a Arrow table. We can then use the `to_pandas()` method to convert the Arrow table to a Pandas DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "pyarrow.Table\n",
      "iox::measurement: string not null\n",
      "time: timestamp[ns] not null\n",
      "attack: int64\n",
      "defense: int64\n",
      "hp: int64\n",
      "id: string\n",
      "level: int64\n",
      "name: string\n",
      "num: string\n",
      "speed: int64\n",
      "trainer: string\n",
      "type1: string\n",
      "type2: string\n",
      "----\n",
      "iox::measurement: [[\"kanto\",\"kanto\",\"kanto\",\"kanto\",\"kanto\",...,\"kanto\",\"kanto\",\"kanto\",\"kanto\",\"kanto\"],[\"kanto\",\"kanto\",\"kanto\",\"kanto\",\"kanto\",...,\"kanto\",\"kanto\",\"kanto\",\"kanto\",\"kanto\"]]\n",
      "time: [[2023-07-27 09:48:38.242000000,2023-07-27 09:48:38.242000000,2023-07-27 09:48:38.242000000,2023-07-27 09:48:38.242000000,2023-07-27 09:48:38.242000000,...,2023-07-27 10:38:38.242000000,2023-07-27 10:38:38.242000000,2023-07-27 10:38:38.242000000,2023-07-27 10:38:38.242000000,2023-07-27 10:38:38.242000000],[2023-07-27 10:38:38.242000000,2023-07-27 10:38:38.242000000,2023-07-27 10:38:38.242000000,2023-07-27 10:38:38.242000000,2023-07-27 10:38:38.242000000,...,2023-07-27 10:48:38.242000000,2023-07-27 10:48:38.242000000,2023-07-27 10:48:38.242000000,2023-07-27 10:48:38.242000000,2023-07-27 10:48:38.242000000]]\n",
      "attack: [[82,82,100,64,64,...,60,85,85,100,100],[47,72,45,41,41,...,64,64,134,134,110]]\n",
      "defense: [[83,83,123,58,58,...,44,69,69,110,110],[52,57,48,40,40,...,45,45,95,95,90]]\n",
      "hp: [[80,80,80,58,58,...,35,60,60,75,75],[55,61,70,38,38,...,41,41,91,91,106]]\n",
      "id: [[\"0003\",\"0003\",\"0003\",\"0005\",\"0005\",...,\"0023\",\"0024\",\"0024\",\"0028\",\"0028\"],[\"0029\",\"0033\",\"0035\",\"0037\",\"0037\",...,\"0147\",\"0147\",\"0149\",\"0149\",\"0150\"]]\n",
      "level: [[9,9,18,18,12,...,15,5,8,15,5],[11,15,10,16,19,...,10,11,16,6,17]]\n",
      "name: [[\"Venusaur\",\"Venusaur\",\"VenusaurMega Venusaur\",\"Charmeleon\",\"Charmeleon\",...,\"Ekans\",\"Arbok\",\"Arbok\",\"Sandslash\",\"Sandslash\"],[\"Nidoran♀\",\"Nidorino\",\"Clefairy\",\"Vulpix\",\"Vulpix\",...,\"Dratini\",\"Dratini\",\"Dragonite\",\"Dragonite\",\"Mewtwo\"]]\n",
      "num: [[\"4\",\"6\",\"8\",\"11\",\"3\",...,\"12\",\"6\",\"7\",\"7\",\"7\"],[\"8\",\"6\",\"16\",\"1\",\"10\",...,\"1\",\"3\",\"3\",\"5\",\"11\"]]\n",
      "speed: [[80,80,80,80,80,...,55,80,80,65,65],[41,65,35,65,65,...,50,50,80,80,130]]\n",
      "...\n"
     ]
    },
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Grass<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Grass",
         "marker": {
          "color": "#636efa",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Grass",
         "offsetgroup": "Grass",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "misty",
          "gary",
          "james",
          "brock",
          "jessie"
         ],
         "xaxis": "x",
         "y": [
          121,
          137,
          134,
          129,
          130,
          138
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Fire<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Fire",
         "marker": {
          "color": "#EF553B",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Fire",
         "offsetgroup": "Fire",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "james",
          "brock",
          "ash",
          "jessie",
          "misty",
          "gary"
         ],
         "xaxis": "x",
         "y": [
          153,
          151,
          128,
          142,
          132,
          161
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Water<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Water",
         "marker": {
          "color": "#00cc96",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Water",
         "offsetgroup": "Water",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "misty",
          "james",
          "brock",
          "gary",
          "ash",
          "jessie"
         ],
         "xaxis": "x",
         "y": [
          333,
          323,
          324,
          283,
          315,
          345
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Bug<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Bug",
         "marker": {
          "color": "#ab63fa",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Bug",
         "offsetgroup": "Bug",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "james",
          "misty",
          "ash",
          "brock",
          "gary",
          "jessie"
         ],
         "xaxis": "x",
         "y": [
          160,
          147,
          157,
          142,
          140,
          154
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Normal<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Normal",
         "marker": {
          "color": "#FFA15A",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Normal",
         "offsetgroup": "Normal",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "jessie",
          "gary",
          "ash",
          "brock",
          "james",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          240,
          261,
          231,
          239,
          237,
          230
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Electric<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Electric",
         "marker": {
          "color": "#19d3f3",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Electric",
         "offsetgroup": "Electric",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "jessie",
          "gary",
          "misty",
          "brock",
          "james",
          "ash"
         ],
         "xaxis": "x",
         "y": [
          90,
          107,
          85,
          88,
          102,
          97
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Poison<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Poison",
         "marker": {
          "color": "#FF6692",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Poison",
         "offsetgroup": "Poison",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "misty",
          "jessie",
          "james",
          "brock",
          "ash",
          "gary"
         ],
         "xaxis": "x",
         "y": [
          147,
          138,
          154,
          147,
          154,
          137
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Fairy<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Fairy",
         "marker": {
          "color": "#B6E880",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Fairy",
         "offsetgroup": "Fairy",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "jessie",
          "brock",
          "gary",
          "james",
          "misty",
          "ash"
         ],
         "xaxis": "x",
         "y": [
          20,
          12,
          25,
          20,
          21,
          20
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Ground<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Ground",
         "marker": {
          "color": "#FF97FF",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Ground",
         "offsetgroup": "Ground",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "ash",
          "jessie",
          "misty",
          "gary",
          "james",
          "brock"
         ],
         "xaxis": "x",
         "y": [
          70,
          87,
          85,
          63,
          87,
          64
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Fighting<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Fighting",
         "marker": {
          "color": "#FECB52",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Fighting",
         "offsetgroup": "Fighting",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "gary",
          "ash",
          "brock",
          "james",
          "jessie",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          63,
          65,
          69,
          75,
          65,
          90
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Psychic<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Psychic",
         "marker": {
          "color": "#636efa",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Psychic",
         "offsetgroup": "Psychic",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "gary",
          "brock",
          "jessie",
          "james",
          "ash",
          "misty"
         ],
         "xaxis": "x",
         "y": [
          85,
          92,
          66,
          83,
          70,
          72
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Rock<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Rock",
         "marker": {
          "color": "#EF553B",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Rock",
         "offsetgroup": "Rock",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "jessie",
          "ash",
          "brock",
          "gary",
          "misty",
          "james"
         ],
         "xaxis": "x",
         "y": [
          113,
          97,
          82,
          102,
          97,
          90
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Ghost<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Ghost",
         "marker": {
          "color": "#00cc96",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Ghost",
         "offsetgroup": "Ghost",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "gary",
          "jessie",
          "ash",
          "brock",
          "misty",
          "james"
         ],
         "xaxis": "x",
         "y": [
          45,
          44,
          40,
          44,
          38,
          44
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Dragon<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Dragon",
         "marker": {
          "color": "#ab63fa",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Dragon",
         "offsetgroup": "Dragon",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "james",
          "jessie",
          "gary",
          "misty",
          "ash",
          "brock"
         ],
         "xaxis": "x",
         "y": [
          33,
          39,
          34,
          31,
          47,
          27
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "type1=Ice<br>trainer=%{x}<br>name_count=%{y}<extra></extra>",
         "legendgroup": "Ice",
         "marker": {
          "color": "#FFA15A",
          "pattern": {
           "shape": ""
          }
         },
         "name": "Ice",
         "offsetgroup": "Ice",
         "orientation": "v",
         "showlegend": true,
         "textposition": "auto",
         "type": "bar",
         "x": [
          "jessie",
          "misty",
          "ash",
          "james",
          "gary",
          "brock"
         ],
         "xaxis": "x",
         "y": [
          22,
          14,
          28,
          15,
          14,
          22
         ],
         "yaxis": "y"
        }
       ],
       "layout": {
        "barmode": "group",
        "legend": {
         "title": {
          "text": "type1"
         },
         "tracegroupgap": 0
        },
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "heatmapgl": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmapgl"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "fillpattern": {
             "fillmode": "overlay",
             "size": 10,
             "solidity": 0.2
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "title": {
         "text": "Number of Pokémon caught in the last hour grouped by type"
        },
        "xaxis": {
         "anchor": "y",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "trainer"
         }
        },
        "yaxis": {
         "anchor": "x",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "name_count"
         }
        }
       }
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "table = client.query(query='''SELECT * FROM kanto ORDER BY time''', language='influxql', mode='all')\n",
    "print(table)\n",
    "\n",
    "# PyArrow Aggregation\n",
    "aggregation = table.group_by([\"trainer\", \"type1\"]).aggregate([(\"name\", \"count\")]).to_pandas()\n",
    "fig3 = px.bar(aggregation, x=\"trainer\", y=\"name_count\", color='type1', barmode= 'group', title='Number of Pokémon caught in the last hour grouped by type')\n",
    "fig3.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving to file\n",
    "We can also save our query results to file. This is useful if we want to save our data for later use. We can save our data in a number of formats including CSV, JSON, Parquet and Apache Arrow. Lets save our data as a parquet file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyarrow.parquet as pq\n",
    "\n",
    "# Write the table to a parquet file\n",
    "pq.write_table(table, 'kanto.parquet')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Uploading a file to InfluxDB\n",
    "This allows to show show off another feature of the InfluxDB 3.0 Python Client. We can parse our file directly to InfluxDB. This is useful if we want to upload data from a local file. Lets upload our parquet file to InfluxDB."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n",
      "Written batch: ('pokemon-codex', '6a841c0c08328fb1', 'ns')\n"
     ]
    }
   ],
   "source": [
    "client.write_file('kanto.parquet', measurement_name='kanto2', database='pokemon-codex', timestamp_column='time', tag_columns=['trainer', 'id', 'num'])\n",
    "time.sleep(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Group by time query\n",
    "Finally lets run a group by time query to get the number of pokemon caught by each trainer over the last hour grouped into 10 minute intervals. We will then use Plotly to visualise this data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "hovertemplate": "trainer=ash<br>time=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "ash",
         "line": {
          "color": "#636efa",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "ash",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          "2023-07-27T09:50:00",
          "2023-07-27T10:00:00",
          "2023-07-27T10:10:00",
          "2023-07-27T10:20:00",
          "2023-07-27T10:30:00",
          "2023-07-27T10:40:00",
          "2023-07-27T10:50:00"
         ],
         "xaxis": "x",
         "y": [
          204,
          252,
          256,
          265,
          265,
          270,
          0
         ],
         "yaxis": "y"
        },
        {
         "hovertemplate": "trainer=brock<br>time=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "brock",
         "line": {
          "color": "#EF553B",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "brock",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          "2023-07-27T09:50:00",
          "2023-07-27T10:00:00",
          "2023-07-27T10:10:00",
          "2023-07-27T10:20:00",
          "2023-07-27T10:30:00",
          "2023-07-27T10:40:00",
          "2023-07-27T10:50:00"
         ],
         "xaxis": "x",
         "y": [
          211,
          286,
          280,
          260,
          261,
          235,
          0
         ],
         "yaxis": "y"
        },
        {
         "hovertemplate": "trainer=gary<br>time=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "gary",
         "line": {
          "color": "#00cc96",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "gary",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          "2023-07-27T09:50:00",
          "2023-07-27T10:00:00",
          "2023-07-27T10:10:00",
          "2023-07-27T10:20:00",
          "2023-07-27T10:30:00",
          "2023-07-27T10:40:00",
          "2023-07-27T10:50:00"
         ],
         "xaxis": "x",
         "y": [
          223,
          302,
          275,
          276,
          255,
          231,
          0
         ],
         "yaxis": "y"
        },
        {
         "hovertemplate": "trainer=james<br>time=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "james",
         "line": {
          "color": "#ab63fa",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "james",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          "2023-07-27T09:50:00",
          "2023-07-27T10:00:00",
          "2023-07-27T10:10:00",
          "2023-07-27T10:20:00",
          "2023-07-27T10:30:00",
          "2023-07-27T10:40:00",
          "2023-07-27T10:50:00"
         ],
         "xaxis": "x",
         "y": [
          236,
          275,
          286,
          277,
          280,
          256,
          0
         ],
         "yaxis": "y"
        },
        {
         "hovertemplate": "trainer=jessie<br>time=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "jessie",
         "line": {
          "color": "#FFA15A",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "jessie",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          "2023-07-27T09:50:00",
          "2023-07-27T10:00:00",
          "2023-07-27T10:10:00",
          "2023-07-27T10:20:00",
          "2023-07-27T10:30:00",
          "2023-07-27T10:40:00",
          "2023-07-27T10:50:00"
         ],
         "xaxis": "x",
         "y": [
          204,
          261,
          265,
          290,
          293,
          265,
          0
         ],
         "yaxis": "y"
        },
        {
         "hovertemplate": "trainer=misty<br>time=%{x}<br>count=%{y}<extra></extra>",
         "legendgroup": "misty",
         "line": {
          "color": "#19d3f3",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "misty",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          "2023-07-27T09:50:00",
          "2023-07-27T10:00:00",
          "2023-07-27T10:10:00",
          "2023-07-27T10:20:00",
          "2023-07-27T10:30:00",
          "2023-07-27T10:40:00",
          "2023-07-27T10:50:00"
         ],
         "xaxis": "x",
         "y": [
          215,
          276,
          266,
          268,
          270,
          265,
          0
         ],
         "yaxis": "y"
        }
       ],
       "layout": {
        "legend": {
         "title": {
          "text": "trainer"
         },
         "tracegroupgap": 0
        },
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "heatmapgl": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmapgl"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "fillpattern": {
             "fillmode": "overlay",
             "size": 10,
             "solidity": 0.2
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "title": {
         "text": "Number of Pokémon caught in the last hour grouped by trainer and time"
        },
        "xaxis": {
         "anchor": "y",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "time"
         }
        },
        "yaxis": {
         "anchor": "x",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "count"
         }
        }
       }
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "query='''SELECT count(\"name\") FROM kanto2 WHERE time > now() - 1h GROUP BY time(10m),trainer'''\n",
    "table = client.query(query=query, language='influxql', mode='all')\n",
    "\n",
    "fig4 = px.line(table, x=\"time\", y=\"count\", color='trainer', title='Number of Pokémon caught in the last hour grouped by trainer and time')\n",
    "fig4.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conclusion\n",
    "We have now covered the basics of the InfluxDB 3.0 Python Client. I hope you found this novel cook book informative and fun. If you have any questions, bugs or feature requests please raise an issue on the [GitHub repo](https://github.com/InfluxCommunity/influxdb3-python/issues)\n",
    "\n",
    "\n",
    "<p align=\"center\">\n",
    "<img height=\"100\", width=\"100\" src=\"https://i.pinimg.com/originals/18/15/44/181544facabe62d30c52e94b369f0f3a.png\">\n",
    "</p>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}