{
"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
}