I have several sensors which frequently report their state to InfluxDB. I now would like to get the amount of hours per day the sensor's state is ON (1). The graph looks like this:
My Python script looks like this:
from influxdb_client import InfluxDBClient
import pandas as pd
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
pd.set_option('display.max_colwidth', None)
token = 'XXX'
org = 'XXX'
bucket = 'XXX'
query= '''
from(bucket: "XXX")
|> range(start:-6h)
|> filter(fn: (r) => r["name"] == "sensor1")
|> filter(fn: (r) => r["_measurement"] == "undefined")
|> filter(fn: (r) => r["_field"] == "STATE")
|> toInt()
'''
client = InfluxDBClient(url='https://westeurope-1.azure.cloud2.influxdata.com', token=token, org=org, debug=False)
df = client.query_api().query_data_frame(org=org, query=query)
df = df.reindex(columns=['_value', '_time'])
display(df)
_value _time
0 1 2021-11-12 13:22:32 00:00
1 0 2021-11-12 13:22:34 00:00
2 0 2021-11-12 13:26:32 00:00
3 0 2021-11-12 13:26:34 00:00
4 1 2021-11-12 13:28:05 00:00
5 0 2021-11-12 13:28:06 00:00
6 0 2021-11-12 13:29:04 00:00
7 0 2021-11-12 13:29:06 00:00
8 1 2021-11-12 13:39:04 00:00
9 0 2021-11-12 13:39:06 00:00
10 0 2021-11-12 13:41:09 00:00
11 0 2021-11-12 13:41:10 00:00
12 1 2021-11-12 13:52:33 00:00
13 0 2021-11-12 13:52:34 00:00
14 0 2021-11-12 14:00:08 00:00
15 0 2021-11-12 14:00:10 00:00
16 1 2021-11-12 14:07:32 00:00
17 0 2021-11-12 14:07:34 00:00
18 1 2021-11-12 14:17:52 00:00
19 0 2021-11-12 14:17:53 00:00
20 0 2021-11-12 14:18:45 00:00
21 0 2021-11-12 14:18:46 00:00
22 1 2021-11-12 14:22:33 00:00
23 0 2021-11-12 14:22:34 00:00
24 0 2021-11-12 14:32:05 00:00
25 0 2021-11-12 14:32:06 00:00
26 0 2021-11-12 14:33:06 00:00
27 0 2021-11-12 14:33:45 00:00
28 0 2021-11-12 14:33:46 00:00
29 1 2021-11-12 14:37:33 00:00
30 0 2021-11-12 14:37:34 00:00
31 0 2021-11-12 14:48:20 00:00
32 0 2021-11-12 14:48:21 00:00
33 1 2021-11-12 14:52:33 00:00
34 0 2021-11-12 14:52:34 00:00
35 0 2021-11-12 14:56:04 00:00
36 0 2021-11-12 14:56:06 00:00
37 0 2021-11-12 14:57:55 00:00
38 0 2021-11-12 14:57:56 00:00
39 1 2021-11-12 15:22:33 00:00
40 0 2021-11-12 15:22:34 00:00
41 0 2021-11-12 15:23:04 00:00
42 0 2021-11-12 15:23:06 00:00
43 1 2021-11-12 15:26:04 00:00
44 0 2021-11-12 15:26:06 00:00
45 0 2021-11-12 15:26:33 00:00
46 0 2021-11-12 15:26:34 00:00
47 1 2021-11-12 15:37:32 00:00
48 0 2021-11-12 15:37:34 00:00
49 0 2021-11-12 15:41:05 00:00
50 0 2021-11-12 15:41:06 00:00
51 1 2021-11-12 15:52:32 00:00
52 0 2021-11-12 15:52:34 00:00
53 1 2021-11-12 16:03:13 00:00
54 0 2021-11-12 16:03:14 00:00
55 0 2021-11-12 16:20:04 00:00
56 0 2021-11-12 16:20:06 00:00
57 1 2021-11-12 16:22:05 00:00
58 0 2021-11-12 16:37:04 00:00
59 0 2021-11-12 16:37:06 00:00
60 1 2021-11-12 16:37:33 00:00
61 0 2021-11-12 16:37:34 00:00
62 0 2021-11-12 16:43:05 00:00
63 0 2021-11-12 16:43:06 00:00
64 0 2021-11-12 16:43:51 00:00
65 0 2021-11-12 16:43:53 00:00
66 1 2021-11-12 16:56:05 00:00
67 0 2021-11-12 16:56:06 00:00
68 0 2021-11-12 16:56:32 00:00
69 0 2021-11-12 16:56:34 00:00
70 1 2021-11-12 17:07:33 00:00
71 1 2021-11-12 17:07:34 00:00
72 0 2021-11-12 17:08:05 00:00
73 0 2021-11-12 17:08:06 00:00
74 1 2021-11-12 17:10:05 00:00
75 0 2021-11-12 17:10:06 00:00
76 0 2021-11-12 17:11:09 00:00
77 0 2021-11-12 17:11:10 00:00
78 1 2021-11-12 17:22:32 00:00
79 0 2021-11-12 17:22:33 00:00
80 1 2021-11-12 17:22:45 00:00
81 1 2021-11-12 17:24:08 00:00
82 0 2021-11-12 17:24:13 00:00
83 0 2021-11-12 17:24:14 00:00
84 1 2021-11-12 17:25:05 00:00
85 0 2021-11-12 17:25:06 00:00
86 0 2021-11-12 18:14:37 00:00
87 0 2021-11-12 18:14:38 00:00
88 0 2021-11-12 18:45:44 00:00
89 0 2021-11-12 18:45:46 00:00
df['end'] = df['_time'].shift(-1)
df['duration'] = df['end'] - df['_time']
display(df)
_value _time end duration
0 1 2021-11-12 13:22:32 00:00 2021-11-12 13:22:34 00:00 0 days 00:00:02
1 0 2021-11-12 13:22:34 00:00 2021-11-12 13:26:32 00:00 0 days 00:03:58
2 0 2021-11-12 13:26:32 00:00 2021-11-12 13:26:34 00:00 0 days 00:00:02
3 0 2021-11-12 13:26:34 00:00 2021-11-12 13:28:05 00:00 0 days 00:01:31
4 1 2021-11-12 13:28:05 00:00 2021-11-12 13:28:06 00:00 0 days 00:00:01
5 0 2021-11-12 13:28:06 00:00 2021-11-12 13:29:04 00:00 0 days 00:00:58
6 0 2021-11-12 13:29:04 00:00 2021-11-12 13:29:06 00:00 0 days 00:00:02
7 0 2021-11-12 13:29:06 00:00 2021-11-12 13:39:04 00:00 0 days 00:09:58
8 1 2021-11-12 13:39:04 00:00 2021-11-12 13:39:06 00:00 0 days 00:00:02
9 0 2021-11-12 13:39:06 00:00 2021-11-12 13:41:09 00:00 0 days 00:02:03
10 0 2021-11-12 13:41:09 00:00 2021-11-12 13:41:10 00:00 0 days 00:00:01
11 0 2021-11-12 13:41:10 00:00 2021-11-12 13:52:33 00:00 0 days 00:11:23
12 1 2021-11-12 13:52:33 00:00 2021-11-12 13:52:34 00:00 0 days 00:00:01
13 0 2021-11-12 13:52:34 00:00 2021-11-12 14:00:08 00:00 0 days 00:07:34
14 0 2021-11-12 14:00:08 00:00 2021-11-12 14:00:10 00:00 0 days 00:00:02
15 0 2021-11-12 14:00:10 00:00 2021-11-12 14:07:32 00:00 0 days 00:07:22
16 1 2021-11-12 14:07:32 00:00 2021-11-12 14:07:34 00:00 0 days 00:00:02
17 0 2021-11-12 14:07:34 00:00 2021-11-12 14:17:52 00:00 0 days 00:10:18
18 1 2021-11-12 14:17:52 00:00 2021-11-12 14:17:53 00:00 0 days 00:00:01
19 0 2021-11-12 14:17:53 00:00 2021-11-12 14:18:45 00:00 0 days 00:00:52
20 0 2021-11-12 14:18:45 00:00 2021-11-12 14:18:46 00:00 0 days 00:00:01
21 0 2021-11-12 14:18:46 00:00 2021-11-12 14:22:33 00:00 0 days 00:03:47
22 1 2021-11-12 14:22:33 00:00 2021-11-12 14:22:34 00:00 0 days 00:00:01
23 0 2021-11-12 14:22:34 00:00 2021-11-12 14:32:05 00:00 0 days 00:09:31
24 0 2021-11-12 14:32:05 00:00 2021-11-12 14:32:06 00:00 0 days 00:00:01
25 0 2021-11-12 14:32:06 00:00 2021-11-12 14:33:06 00:00 0 days 00:01:00
26 0 2021-11-12 14:33:06 00:00 2021-11-12 14:33:45 00:00 0 days 00:00:39
27 0 2021-11-12 14:33:45 00:00 2021-11-12 14:33:46 00:00 0 days 00:00:01
28 0 2021-11-12 14:33:46 00:00 2021-11-12 14:37:33 00:00 0 days 00:03:47
29 1 2021-11-12 14:37:33 00:00 2021-11-12 14:37:34 00:00 0 days 00:00:01
30 0 2021-11-12 14:37:34 00:00 2021-11-12 14:48:20 00:00 0 days 00:10:46
31 0 2021-11-12 14:48:20 00:00 2021-11-12 14:48:21 00:00 0 days 00:00:01
32 0 2021-11-12 14:48:21 00:00 2021-11-12 14:52:33 00:00 0 days 00:04:12
33 1 2021-11-12 14:52:33 00:00 2021-11-12 14:52:34 00:00 0 days 00:00:01
34 0 2021-11-12 14:52:34 00:00 2021-11-12 14:56:04 00:00 0 days 00:03:30
35 0 2021-11-12 14:56:04 00:00 2021-11-12 14:56:06 00:00 0 days 00:00:02
36 0 2021-11-12 14:56:06 00:00 2021-11-12 14:57:55 00:00 0 days 00:01:49
37 0 2021-11-12 14:57:55 00:00 2021-11-12 14:57:56 00:00 0 days 00:00:01
38 0 2021-11-12 14:57:56 00:00 2021-11-12 15:22:33 00:00 0 days 00:24:37
39 1 2021-11-12 15:22:33 00:00 2021-11-12 15:22:34 00:00 0 days 00:00:01
40 0 2021-11-12 15:22:34 00:00 2021-11-12 15:23:04 00:00 0 days 00:00:30
41 0 2021-11-12 15:23:04 00:00 2021-11-12 15:23:06 00:00 0 days 00:00:02
42 0 2021-11-12 15:23:06 00:00 2021-11-12 15:26:04 00:00 0 days 00:02:58
43 1 2021-11-12 15:26:04 00:00 2021-11-12 15:26:06 00:00 0 days 00:00:02
44 0 2021-11-12 15:26:06 00:00 2021-11-12 15:26:33 00:00 0 days 00:00:27
45 0 2021-11-12 15:26:33 00:00 2021-11-12 15:26:34 00:00 0 days 00:00:01
46 0 2021-11-12 15:26:34 00:00 2021-11-12 15:37:32 00:00 0 days 00:10:58
47 1 2021-11-12 15:37:32 00:00 2021-11-12 15:37:34 00:00 0 days 00:00:02
48 0 2021-11-12 15:37:34 00:00 2021-11-12 15:41:05 00:00 0 days 00:03:31
49 0 2021-11-12 15:41:05 00:00 2021-11-12 15:41:06 00:00 0 days 00:00:01
50 0 2021-11-12 15:41:06 00:00 2021-11-12 15:52:32 00:00 0 days 00:11:26
51 1 2021-11-12 15:52:32 00:00 2021-11-12 15:52:34 00:00 0 days 00:00:02
52 0 2021-11-12 15:52:34 00:00 2021-11-12 16:03:13 00:00 0 days 00:10:39
53 1 2021-11-12 16:03:13 00:00 2021-11-12 16:03:14 00:00 0 days 00:00:01
54 0 2021-11-12 16:03:14 00:00 2021-11-12 16:20:04 00:00 0 days 00:16:50
55 0 2021-11-12 16:20:04 00:00 2021-11-12 16:20:06 00:00 0 days 00:00:02
56 0 2021-11-12 16:20:06 00:00 2021-11-12 16:22:05 00:00 0 days 00:01:59
57 1 2021-11-12 16:22:05 00:00 2021-11-12 16:37:04 00:00 0 days 00:14:59
58 0 2021-11-12 16:37:04 00:00 2021-11-12 16:37:06 00:00 0 days 00:00:02
59 0 2021-11-12 16:37:06 00:00 2021-11-12 16:37:33 00:00 0 days 00:00:27
60 1 2021-11-12 16:37:33 00:00 2021-11-12 16:37:34 00:00 0 days 00:00:01
61 0 2021-11-12 16:37:34 00:00 2021-11-12 16:43:05 00:00 0 days 00:05:31
62 0 2021-11-12 16:43:05 00:00 2021-11-12 16:43:06 00:00 0 days 00:00:01
63 0 2021-11-12 16:43:06 00:00 2021-11-12 16:43:51 00:00 0 days 00:00:45
64 0 2021-11-12 16:43:51 00:00 2021-11-12 16:43:53 00:00 0 days 00:00:02
65 0 2021-11-12 16:43:53 00:00 2021-11-12 16:56:05 00:00 0 days 00:12:12
66 1 2021-11-12 16:56:05 00:00 2021-11-12 16:56:06 00:00 0 days 00:00:01
67 0 2021-11-12 16:56:06 00:00 2021-11-12 16:56:32 00:00 0 days 00:00:26
68 0 2021-11-12 16:56:32 00:00 2021-11-12 16:56:34 00:00 0 days 00:00:02
69 0 2021-11-12 16:56:34 00:00 2021-11-12 17:07:33 00:00 0 days 00:10:59
70 1 2021-11-12 17:07:33 00:00 2021-11-12 17:07:34 00:00 0 days 00:00:01
71 1 2021-11-12 17:07:34 00:00 2021-11-12 17:08:05 00:00 0 days 00:00:31
72 0 2021-11-12 17:08:05 00:00 2021-11-12 17:08:06 00:00 0 days 00:00:01
73 0 2021-11-12 17:08:06 00:00 2021-11-12 17:10:05 00:00 0 days 00:01:59
74 1 2021-11-12 17:10:05 00:00 2021-11-12 17:10:06 00:00 0 days 00:00:01
75 0 2021-11-12 17:10:06 00:00 2021-11-12 17:11:09 00:00 0 days 00:01:03
76 0 2021-11-12 17:11:09 00:00 2021-11-12 17:11:10 00:00 0 days 00:00:01
77 0 2021-11-12 17:11:10 00:00 2021-11-12 17:22:32 00:00 0 days 00:11:22
78 1 2021-11-12 17:22:32 00:00 2021-11-12 17:22:33 00:00 0 days 00:00:01
79 0 2021-11-12 17:22:33 00:00 2021-11-12 17:22:45 00:00 0 days 00:00:12
80 1 2021-11-12 17:22:45 00:00 2021-11-12 17:24:08 00:00 0 days 00:01:23
81 1 2021-11-12 17:24:08 00:00 2021-11-12 17:24:13 00:00 0 days 00:00:05
82 0 2021-11-12 17:24:13 00:00 2021-11-12 17:24:14 00:00 0 days 00:00:01
83 0 2021-11-12 17:24:14 00:00 2021-11-12 17:25:05 00:00 0 days 00:00:51
84 1 2021-11-12 17:25:05 00:00 2021-11-12 17:25:06 00:00 0 days 00:00:01
85 0 2021-11-12 17:25:06 00:00 2021-11-12 18:14:37 00:00 0 days 00:49:31
86 0 2021-11-12 18:14:37 00:00 2021-11-12 18:14:38 00:00 0 days 00:00:01
87 0 2021-11-12 18:14:38 00:00 2021-11-12 18:45:44 00:00 0 days 00:31:06
88 0 2021-11-12 18:45:44 00:00 2021-11-12 18:45:46 00:00 0 days 00:00:02
89 0 2021-11-12 18:45:46 00:00 NaT NaT
print('ON State: {}'.format(df.query('_value == 1')['duration'].sum()))
print('OFF State: {}'.format(df.query('_value == 0')['duration'].sum()))
ON State: 0 days 00:17:24
OFF State: 0 days 05:05:50
As you can see above the sensor was ON for about 17 minutes in the last 6 hours, which is clearly wrong as you can see in the graph. Why is this the case? Can someone help me finding the issue?
Thanks a lot!
CodePudding user response：
I suspect you're shifting the wrong way:
# instead of .shift(1), .shift(-1)
# and change name appropriately
df['_start'] = df._time.shift(1)
# not really necessary to explicitly call pd.to_timedelta
# but makes it clear what's happening
df['_duration'] = pd.to_timedelta(df._time - df._start)
print(f'ON: {df.loc[df._value.eq(1)]._duration.sum()}')
print(f'OFF: {df.loc[df._value.eq(0)]._duration.sum()}')
gives
ON: 0 days 02:34:21
OFF: 0 days 02:48:53