{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://influxdb-client.readthedocs.io/en/latest/usage.html#id4\n",
    "\n",
    "or generate source code from Web UI： `Load Data` -> `Source`\n",
    "\n",
    "## Query\n",
    "Flux: https://docs.influxdata.com/influxdb/v2.0/query-data/flux/query-fields/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re, os\n",
    "\n",
    "path = '/data/conf/init.sh' # influxdb_token=... (optional)\n",
    "\n",
    "if os.path.exists(path):\n",
    "    with open(path,'r',encoding='utf-8') as f:\n",
    "        content = f.read()\n",
    "        token = re.search(\"(?<=influxdb_token=).*\", content).group(0)\n",
    "else:\n",
    "    token = input(\"influxdb_token=\")\n",
    "    \n",
    "from influxdb_client import InfluxDBClient\n",
    "\n",
    "client = InfluxDBClient(url=\"http://192.168.88.21:8086\", token=token, org=\"ferro\")\n",
    "query_api = client.query_api()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "query='''\n",
    "from(bucket: \"home\")\n",
    "  |> range(start: -3d)\n",
    "  |> filter(fn: (r) => r[\"_measurement\"] == \"度数\") \n",
    "  |> filter(fn: (r) => r[\"_field\"] == \"当前\") \n",
    "  |> filter(fn: (r) => r[\"tag\"] == \"203\" or r[\"tag\"] == \"205\" )\n",
    "  |> keep(columns: [\"_time\", \"_value\", \"tag\"])\n",
    "'''\n",
    "\n",
    "query = ''.join([ line for line in query.splitlines() if not line.startswith('#') ])\n",
    "\n",
    "df  = client.query_api().query_data_frame(query=query).drop(['result', 'table'], axis=1)\n",
    "df['_time'] =  pd.to_datetime(df['_time'], format='%Y-%m-%d %H:%M:%S.%f%z') # https://strftime.org/\n",
    "df['tag'] = df['tag'].astype('string')\n",
    "\n",
    "df['diff_value'] = df.groupby(['tag'])['_value'].diff().fillna(0)\n",
    "df['diff_time']  = df.groupby(['tag'])['_time'].diff()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "_time         datetime64[ns, UTC]\n",
       "_value                    float64\n",
       "tag                        string\n",
       "diff_value                float64\n",
       "diff_time         timedelta64[ns]\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "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>_time</th>\n",
       "      <th>_value</th>\n",
       "      <th>diff_value</th>\n",
       "      <th>diff_time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tag</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>2021-02-13 01:00:08.576459+00:00</td>\n",
       "      <td>1397.19</td>\n",
       "      <td>1.25</td>\n",
       "      <td>1 days 00:00:00.312928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>2021-02-13 01:00:08.576459+00:00</td>\n",
       "      <td>185.72</td>\n",
       "      <td>1.77</td>\n",
       "      <td>1 days 00:00:00.312928</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               _time   _value  diff_value  \\\n",
       "tag                                                         \n",
       "203 2021-02-13 01:00:08.576459+00:00  1397.19        1.25   \n",
       "205 2021-02-13 01:00:08.576459+00:00   185.72        1.77   \n",
       "\n",
       "                 diff_time  \n",
       "tag                         \n",
       "203 1 days 00:00:00.312928  \n",
       "205 1 days 00:00:00.312928  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_last = df.groupby(['tag']).last()\n",
    "df_last"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_time</th>\n",
       "      <th>_value</th>\n",
       "      <th>tag</th>\n",
       "      <th>diff_value</th>\n",
       "      <th>diff_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-02-11 17:53:55.881142+00:00</td>\n",
       "      <td>1395.56</td>\n",
       "      <td>203</td>\n",
       "      <td>3.89</td>\n",
       "      <td>0 days 16:53:48.762028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-02-12 01:00:08.263531+00:00</td>\n",
       "      <td>1395.94</td>\n",
       "      <td>203</td>\n",
       "      <td>0.38</td>\n",
       "      <td>0 days 07:06:12.382389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-02-13 01:00:08.576459+00:00</td>\n",
       "      <td>1397.19</td>\n",
       "      <td>203</td>\n",
       "      <td>1.25</td>\n",
       "      <td>1 days 00:00:00.312928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021-02-11 17:53:55.881142+00:00</td>\n",
       "      <td>183.62</td>\n",
       "      <td>205</td>\n",
       "      <td>2.51</td>\n",
       "      <td>0 days 16:53:48.762028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2021-02-12 01:00:08.263531+00:00</td>\n",
       "      <td>183.95</td>\n",
       "      <td>205</td>\n",
       "      <td>0.33</td>\n",
       "      <td>0 days 07:06:12.382389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2021-02-13 01:00:08.576459+00:00</td>\n",
       "      <td>185.72</td>\n",
       "      <td>205</td>\n",
       "      <td>1.77</td>\n",
       "      <td>1 days 00:00:00.312928</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             _time   _value  tag  diff_value  \\\n",
       "1 2021-02-11 17:53:55.881142+00:00  1395.56  203        3.89   \n",
       "2 2021-02-12 01:00:08.263531+00:00  1395.94  203        0.38   \n",
       "3 2021-02-13 01:00:08.576459+00:00  1397.19  203        1.25   \n",
       "5 2021-02-11 17:53:55.881142+00:00   183.62  205        2.51   \n",
       "6 2021-02-12 01:00:08.263531+00:00   183.95  205        0.33   \n",
       "7 2021-02-13 01:00:08.576459+00:00   185.72  205        1.77   \n",
       "\n",
       "               diff_time  \n",
       "1 0 days 16:53:48.762028  \n",
       "2 0 days 07:06:12.382389  \n",
       "3 1 days 00:00:00.312928  \n",
       "5 0 days 16:53:48.762028  \n",
       "6 0 days 07:06:12.382389  \n",
       "7 1 days 00:00:00.312928  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_result = df[df.diff_time.notnull()]\n",
    "df_result"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}