Migrating from btrdb-python¶
This package is meant to supersede the previous BTrDB Python API. This guide showcases how to migrate your old code to the PingThings Timeseries package.
In [ ]:
Copied!
import os
import re
from IPython.display import Markdown, display
import os
import re
from IPython.display import Markdown, display
Connecting to the Predictive Grid Platform¶
The first step is to import the APIs and connect to a database. This should be a drop-in replacement, substituting pingthings.timeseries (shortened to pt) for btrdb:
In [ ]:
Copied!
import btrdb
import pingthings.timeseries as pt
import btrdb
import pingthings.timeseries as pt
As before, you can connect by explicitly providing an endpoint and API key. And, as before, it's much better to have the API read in these values from the environment.
In [ ]:
Copied!
# _ = btrdb.connect(conn_str=os.environ["BTRDB_ENDPOINTS"], apikey=os.environ["BTRDB_API_KEY"])
bp_conn = btrdb.connect()
# _ = pt.connect(endpoint=os.environ["BTRDB_ENDPOINTS"], apikey=os.environ["BTRDB_API_KEY"])
pt_conn = pt.connect()
# _ = btrdb.connect(conn_str=os.environ["BTRDB_ENDPOINTS"], apikey=os.environ["BTRDB_API_KEY"])
bp_conn = btrdb.connect()
# _ = pt.connect(endpoint=os.environ["BTRDB_ENDPOINTS"], apikey=os.environ["BTRDB_API_KEY"])
pt_conn = pt.connect()
Note: in this example, we're connecting to and pulling data from the NI4AI database.
In [ ]:
Copied!
display(bp_conn.list_collections()[:10])
print()
display(pt_conn.list_collections()[:10])
display(bp_conn.list_collections()[:10])
print()
display(pt_conn.list_collections()[:10])
['ami/lcl/MAC000002', 'ami/lcl/MAC000003', 'ami/lcl/MAC000004', 'ami/lcl/MAC000005', 'ami/lcl/MAC000006', 'ami/lcl/MAC000007', 'ami/lcl/MAC000008', 'ami/lcl/MAC000009', 'ami/lcl/MAC000010', 'ami/lcl/MAC000011']
['ami/lcl/MAC000002', 'ami/lcl/MAC000003', 'ami/lcl/MAC000004', 'ami/lcl/MAC000005', 'ami/lcl/MAC000006', 'ami/lcl/MAC000007', 'ami/lcl/MAC000008', 'ami/lcl/MAC000009', 'ami/lcl/MAC000010', 'ami/lcl/MAC000011']
In [ ]:
Copied!
display(bp_conn.list_collections(starts_with="sunshine"))
print()
display(pt_conn.list_collections(prefix="sunshine"))
display(bp_conn.list_collections(starts_with="sunshine"))
print()
display(pt_conn.list_collections(prefix="sunshine"))
['sunshine/PMU1', 'sunshine/PMU2', 'sunshine/PMU3', 'sunshine/PMU4', 'sunshine/PMU5', 'sunshine/PMU6']
['sunshine/PMU1', 'sunshine/PMU2', 'sunshine/PMU3', 'sunshine/PMU4', 'sunshine/PMU5', 'sunshine/PMU6']
In [ ]:
Copied!
display({stream.name: stream.uuid for stream in bp_conn.streams_in_collection("sunshine/PMU5")})
print()
display({stream.name: stream.uuid for stream in pt_conn.streams_in_collection("sunshine/PMU5")})
display({stream.name: stream.uuid for stream in bp_conn.streams_in_collection("sunshine/PMU5")})
print()
display({stream.name: stream.uuid for stream in pt_conn.streams_in_collection("sunshine/PMU5")})
/opt/conda/lib/python3.11/site-packages/btrdb/exceptions.py:52: FutureWarning: StreamSet will be the default return object for ``streams_in_collection`` in a future release. return fn(*args, **kwargs)
{'L1ANG': UUID('06e00f19-5ee2-4ae3-911e-6cedbb979d6b'),
'C2MAG': UUID('dbd38fba-4f22-46e1-87be-850f800ec024'),
'C2ANG': UUID('31273550-7d27-4f5e-89ba-e5cae0ddccf4'),
'L2ANG': UUID('5e820477-04bc-4562-a02e-90e3ec1fd083'),
'L3ANG': UUID('e51f6dad-dc3d-4bfd-9092-bb7e6f88f2f7'),
'L3MAG': UUID('a60aa7aa-9ad1-425b-bb2e-74b42a2cd3e0'),
'LSTATE': UUID('c8b4b009-738d-4a35-968b-20575b588429'),
'C3ANG': UUID('b9b0ec24-84c9-42de-afe4-4b19b1ab03b4'),
'L2MAG': UUID('b4de2e84-7816-4dc7-9702-f1c210586e1c'),
'C3MAG': UUID('2f077897-1ddf-4427-857f-53c64f69c1cb'),
'C1MAG': UUID('bd74aa49-8ccc-4f0b-8f83-7195b6c1818e'),
'L1MAG': UUID('edb36769-f56f-47c5-bbda-a177c424cc4f'),
'C1ANG': UUID('fa16194e-a4ac-460c-a75f-c8694a5ad0a9')}
{'L1ANG': UUID('06e00f19-5ee2-4ae3-911e-6cedbb979d6b'),
'C2MAG': UUID('dbd38fba-4f22-46e1-87be-850f800ec024'),
'C2ANG': UUID('31273550-7d27-4f5e-89ba-e5cae0ddccf4'),
'L2ANG': UUID('5e820477-04bc-4562-a02e-90e3ec1fd083'),
'L3ANG': UUID('e51f6dad-dc3d-4bfd-9092-bb7e6f88f2f7'),
'L3MAG': UUID('a60aa7aa-9ad1-425b-bb2e-74b42a2cd3e0'),
'LSTATE': UUID('c8b4b009-738d-4a35-968b-20575b588429'),
'C3ANG': UUID('b9b0ec24-84c9-42de-afe4-4b19b1ab03b4'),
'L2MAG': UUID('b4de2e84-7816-4dc7-9702-f1c210586e1c'),
'C3MAG': UUID('2f077897-1ddf-4427-857f-53c64f69c1cb'),
'C1MAG': UUID('bd74aa49-8ccc-4f0b-8f83-7195b6c1818e'),
'L1MAG': UUID('edb36769-f56f-47c5-bbda-a177c424cc4f'),
'C1ANG': UUID('fa16194e-a4ac-460c-a75f-c8694a5ad0a9')}
In [ ]:
Copied!
bp_stream = bp_conn.stream_from_uuid("2f077897-1ddf-4427-857f-53c64f69c1cb")
display(bp_stream.collection, bp_stream.name)
print()
pt_stream = pt_conn.stream_from_uuid("2f077897-1ddf-4427-857f-53c64f69c1cb")
display(pt_stream.collection, pt_stream.name)
bp_stream = bp_conn.stream_from_uuid("2f077897-1ddf-4427-857f-53c64f69c1cb")
display(bp_stream.collection, bp_stream.name)
print()
pt_stream = pt_conn.stream_from_uuid("2f077897-1ddf-4427-857f-53c64f69c1cb")
display(pt_stream.collection, pt_stream.name)
'sunshine/PMU5'
'C3MAG'
'sunshine/PMU5'
'C3MAG'
Utility Methods¶
The utilities from the timez subpackage can now be found in pt.utils...
In [ ]:
Copied!
from btrdb.utils import timez
from btrdb.utils import timez
In [ ]:
Copied!
display(timez.currently_as_ns())
print()
display(pt.utils.currently_as_ns())
display(timez.currently_as_ns())
print()
display(pt.utils.currently_as_ns())
1745880640757817088
1745880640758724826
In [ ]:
Copied!
display(timez.ns_to_datetime(1728488367976882944))
print()
display(pt.utils.ns_to_datetime(1728488367976882944))
display(timez.ns_to_datetime(1728488367976882944))
print()
display(pt.utils.ns_to_datetime(1728488367976882944))
datetime.datetime(2024, 10, 9, 15, 39, 27, 976883, tzinfo=<UTC>)
datetime.datetime(2024, 10, 9, 15, 39, 27, 976883, tzinfo=datetime.timezone.utc)
In [ ]:
Copied!
display(timez.ns_delta(days=1))
print()
display(pt.utils.ns_delta(days=1))
display(timez.ns_delta(days=1))
print()
display(pt.utils.ns_delta(days=1))
86400000000000
86400000000000
In [ ]:
Copied!
display(timez.to_nanoseconds("2024-10-09"))
print()
display(pt.utils.to_nanoseconds("2024-10-09"))
display(timez.to_nanoseconds("2024-10-09"))
print()
display(pt.utils.to_nanoseconds("2024-10-09"))
1728432000000000000
1728432000000000000
... with the addition of a new helper method for finding the power of 2 ns closest to a desired duration.
In [ ]:
Copied!
point_width = pt.utils.nearest_point_width(hours=3)
print(f"The closest aligned width is 2**{point_width.point_width}, or {point_width.ns}, ns")
point_width = pt.utils.nearest_point_width(hours=3)
print(f"The closest aligned width is 2**{point_width.point_width}, or {point_width.ns}, ns")
The closest aligned width is 2**43, or 8796093022208, ns
Moreover, all valid point-widths are now available in a convenient lookup table as pt.constants.PW. As a little bit of sugar, printing a point-width gives its representation as a timedelta object:
In [ ]:
Copied!
print(pt.constants.PW[46])
print(pt.constants.PW[46])
datetime.timedelta(seconds=70368, microseconds=744178)
Working with Streams¶
Metadata¶
In [ ]:
Copied!
display(bp_stream.tags(), bp_stream.annotations())
print()
display(pt_stream.tags(), pt_stream.annotations())
display(bp_stream.tags(), bp_stream.annotations())
print()
display(pt_stream.tags(), pt_stream.annotations())
{'unit': 'amps', 'ingress': '', 'distiller': '', 'name': 'C3MAG'}
({'location': 'substation',
'impedance': {'source': 'PMU5',
'target': 'PMU2',
'pos_sequence': '0.489+j0.59',
'neg_sequence': '0.971+j1.476'}},
2)
{'name': 'C3MAG', 'unit': 'amps', 'ingress': '', 'distiller': ''}
{'location': 'substation',
'impedance': '{"source": "PMU5", "target": "PMU2", "pos_sequence": "0.489+j0.59", "neg_sequence": "0.971+j1.476"}'}
Note how pt.Stream.annotations() does not include the metadata version.
Getting the latest version is now more explicit:
In [ ]:
Copied!
display(bp_stream.version())
print()
display(pt_stream.get_latest_version())
display(bp_stream.version())
print()
display(pt_stream.get_latest_version())
149229
149229
and the return signatues of earliest, latest have been simplified, no longer including the corresponding version (which can be fetched via the above if not explicitly set):
In [ ]:
Copied!
display(bp_stream.earliest(), bp_stream.latest(), bp_stream.count())
print()
display(pt_stream.earliest(), pt_stream.latest(), pt_stream.count())
display(bp_stream.earliest(), bp_stream.latest(), bp_stream.count())
print()
display(pt_stream.earliest(), pt_stream.latest(), pt_stream.count())
(RawPoint(946684797008333000, 269.8155212402344), 149229)
(RawPoint(1476816263666666000, 302.1278076171875), 149229)
3704481931
Point(time: 946684797008333000, value: 269.8155212402344)
Point(time: 1476816263666666000, value: 302.1278076171875)
3704481931
Raw Point Queries¶
The main difference—and the biggest performance improvement—with the new API is that all data queries now return PyArrow tables. Not only is this data returned much faster and more efficiently, but these tables can be quickly converted into Pandas DataFrames using built-in PyArrow methods.
In [ ]:
Copied!
import pandas as pd
import pandas as pd
In [ ]:
Copied!
start = timez.to_nanoseconds("1999-12-31 23:59:57")
end = start + timez.ns_delta(seconds=0.1)
bp_stream_data = bp_stream.values(start, end)
display(bp_stream_data)
as_array: list[tuple[pd.Timestamp, float]] = []
for raw_point, _ in bp_stream_data:
as_array.append((pd.Timestamp(raw_point.time), raw_point.value))
display(pd.DataFrame(as_array, columns=["time", "value"]))
display(Markdown("---"))
start = pt.utils.to_nanoseconds("1999-12-31 23:59:57")
end = start + pt.utils.ns_delta(seconds=0.1)
pt_stream_data = pt_stream.raw_values(946684797008333000, 946684797008333000 + 1e8)
display(pt_stream_data)
display(pt_stream_data.to_pandas())
start = timez.to_nanoseconds("1999-12-31 23:59:57")
end = start + timez.ns_delta(seconds=0.1)
bp_stream_data = bp_stream.values(start, end)
display(bp_stream_data)
as_array: list[tuple[pd.Timestamp, float]] = []
for raw_point, _ in bp_stream_data:
as_array.append((pd.Timestamp(raw_point.time), raw_point.value))
display(pd.DataFrame(as_array, columns=["time", "value"]))
display(Markdown("---"))
start = pt.utils.to_nanoseconds("1999-12-31 23:59:57")
end = start + pt.utils.ns_delta(seconds=0.1)
pt_stream_data = pt_stream.raw_values(946684797008333000, 946684797008333000 + 1e8)
display(pt_stream_data)
display(pt_stream_data.to_pandas())
[(RawPoint(946684797008333000, 269.8155212402344), 149229), (RawPoint(946684797016666000, 269.4358825683594), 149229), (RawPoint(946684797024999000, 268.82281494140625), 149229), (RawPoint(946684797033333000, 269.13201904296875), 149229), (RawPoint(946684797041666000, 270.1548156738281), 149229), (RawPoint(946684797049999000, 270.3088073730469), 149229), (RawPoint(946684797058333000, 269.6369323730469), 149229), (RawPoint(946684797066666000, 269.450439453125), 149229), (RawPoint(946684797074999000, 269.6058044433594), 149229), (RawPoint(946684797083333000, 269.1439514160156), 149229), (RawPoint(946684797091666000, 268.5997619628906), 149229), (RawPoint(946684797099999000, 268.5179138183594), 149229)]
| time | value | |
|---|---|---|
| 0 | 1999-12-31 23:59:57.008333 | 269.815521 |
| 1 | 1999-12-31 23:59:57.016666 | 269.435883 |
| 2 | 1999-12-31 23:59:57.024999 | 268.822815 |
| 3 | 1999-12-31 23:59:57.033333 | 269.132019 |
| 4 | 1999-12-31 23:59:57.041666 | 270.154816 |
| 5 | 1999-12-31 23:59:57.049999 | 270.308807 |
| 6 | 1999-12-31 23:59:57.058333 | 269.636932 |
| 7 | 1999-12-31 23:59:57.066666 | 269.450439 |
| 8 | 1999-12-31 23:59:57.074999 | 269.605804 |
| 9 | 1999-12-31 23:59:57.083333 | 269.143951 |
| 10 | 1999-12-31 23:59:57.091666 | 268.599762 |
| 11 | 1999-12-31 23:59:57.099999 | 268.517914 |
pyarrow.Table time: timestamp[ns, tz=UTC] not null value: float not null ---- time: [[1999-12-31 23:59:57.008333000Z,1999-12-31 23:59:57.016666000Z,1999-12-31 23:59:57.024999000Z,1999-12-31 23:59:57.033333000Z,1999-12-31 23:59:57.041666000Z,...,1999-12-31 23:59:57.074999000Z,1999-12-31 23:59:57.083333000Z,1999-12-31 23:59:57.091666000Z,1999-12-31 23:59:57.099999000Z,1999-12-31 23:59:57.108333000Z]] value: [[269.81552,269.43588,268.8228,269.13202,270.15482,...,269.6058,269.14395,268.59976,268.5179,268.71313]]
| time | value | |
|---|---|---|
| 0 | 1999-12-31 23:59:57.008333+00:00 | 269.815521 |
| 1 | 1999-12-31 23:59:57.016666+00:00 | 269.435883 |
| 2 | 1999-12-31 23:59:57.024999+00:00 | 268.822815 |
| 3 | 1999-12-31 23:59:57.033333+00:00 | 269.132019 |
| 4 | 1999-12-31 23:59:57.041666+00:00 | 270.154816 |
| 5 | 1999-12-31 23:59:57.049999+00:00 | 270.308807 |
| 6 | 1999-12-31 23:59:57.058333+00:00 | 269.636932 |
| 7 | 1999-12-31 23:59:57.066666+00:00 | 269.450439 |
| 8 | 1999-12-31 23:59:57.074999+00:00 | 269.605804 |
| 9 | 1999-12-31 23:59:57.083333+00:00 | 269.143951 |
| 10 | 1999-12-31 23:59:57.091666+00:00 | 268.599762 |
| 11 | 1999-12-31 23:59:57.099999+00:00 | 268.517914 |
| 12 | 1999-12-31 23:59:57.108333+00:00 | 268.713135 |
For large amounts of data, you should see significantly faster query times with significantly less CPU overhead.
In [ ]:
Copied!
start = timez.to_nanoseconds("2016-5-1")
end = timez.to_nanoseconds("2016-5-2")
%time print(f"1 day of btrdb values comprises {len(bp_stream.values(start, end))} values")
display(Markdown("---"))
start = pt.utils.to_nanoseconds("2016-5-1")
end = pt.utils.to_nanoseconds("2016-5-2")
%time print(f"1 day of btrdb values consumes {pt_stream.raw_values(start, end).shape[0]} values")
start = timez.to_nanoseconds("2016-5-1")
end = timez.to_nanoseconds("2016-5-2")
%time print(f"1 day of btrdb values comprises {len(bp_stream.values(start, end))} values")
display(Markdown("---"))
start = pt.utils.to_nanoseconds("2016-5-1")
end = pt.utils.to_nanoseconds("2016-5-2")
%time print(f"1 day of btrdb values consumes {pt_stream.raw_values(start, end).shape[0]} values")
1 day of btrdb values comprises 10327421 values CPU times: user 17.3 s, sys: 1.23 s, total: 18.6 s Wall time: 18.7 s
1 day of btrdb values consumes 10327421 values CPU times: user 150 ms, sys: 396 ms, total: 545 ms Wall time: 1.34 s
Statistical Queries¶
"StatPoint" queries also now return as PyArrow tables, making them significantly easier to work with. The new API has also been streamlined, taking width in ns or as an explicit PW.
In [ ]:
Copied!
week_ish = pt.utils.nearest_point_width(days=7)
print(f"The nearest aligned width to 1 week is {week_ish} (pointwith={week_ish.point_width})")
week_ish = pt.utils.nearest_point_width(days=7)
print(f"The nearest aligned width to 1 week is {week_ish} (pointwith={week_ish.point_width})")
The nearest aligned width to 1 week is datetime.timedelta(days=6, seconds=44549, microseconds=953421) (pointwith=49)
In [ ]:
Copied!
start = timez.to_nanoseconds("2016-5-1")
end = timez.to_nanoseconds("2016-6-1")
bp_stats = bp_stream.aligned_windows(start, end, pointwidth=49)
display(bp_stats)
as_stat_array: list[tuple[pd.Timestamp, float, float, float, float, float]] = []
for point, _ in bp_stats:
as_stat_array.append(
(pd.Timestamp(point.time), point.min, point.mean, point.max, point.count, point.stddev)
)
display(pd.DataFrame(as_stat_array, columns=["time", "min", "mean", "max", "count", "stddev"]))
display(Markdown("---"))
bp_stats = pt_stream.windowed_values(start, end, width=pt.constants.PW[49])
display(bp_stats)
display(bp_stats.to_pandas())
start = timez.to_nanoseconds("2016-5-1")
end = timez.to_nanoseconds("2016-6-1")
bp_stats = bp_stream.aligned_windows(start, end, pointwidth=49)
display(bp_stats)
as_stat_array: list[tuple[pd.Timestamp, float, float, float, float, float]] = []
for point, _ in bp_stats:
as_stat_array.append(
(pd.Timestamp(point.time), point.min, point.mean, point.max, point.count, point.stddev)
)
display(pd.DataFrame(as_stat_array, columns=["time", "min", "mean", "max", "count", "stddev"]))
display(Markdown("---"))
bp_stats = pt_stream.windowed_values(start, end, width=pt.constants.PW[49])
display(bp_stats)
display(bp_stats.to_pandas())
((StatPoint(1461981029035147264, 166.16246032714844, 245.77867209651018, 351.5482482910156, 65474938, 30.139268588781327), 149229), (StatPoint(1462543978988568576, 194.8922119140625, 268.5108455921535, 373.44482421875, 67508423, 28.56277400251457), 149229), (StatPoint(1463106928941989888, 173.6748504638672, 258.19540845109805, 361.1649475097656, 67516769, 32.87283851829869), 149229), (StatPoint(1463669878895411200, 0.0009941004682332277, 213.16805834630688, 349.7587890625, 67494393, 106.41148944767052), 149229))
| time | min | mean | max | count | stddev | |
|---|---|---|---|---|---|---|
| 0 | 2016-04-30 01:50:29.035147264 | 166.162460 | 245.778672 | 351.548248 | 65474938 | 30.139269 |
| 1 | 2016-05-06 14:12:58.988568576 | 194.892212 | 268.510846 | 373.444824 | 67508423 | 28.562774 |
| 2 | 2016-05-13 02:35:28.941989888 | 173.674850 | 258.195408 | 361.164948 | 67516769 | 32.872839 |
| 3 | 2016-05-19 14:57:58.895411200 | 0.000994 | 213.168058 | 349.758789 | 67494393 | 106.411489 |
pyarrow.Table time: timestamp[ns, tz=UTC] not null min: float not null mean: float not null max: float not null count: uint64 not null stddev: float not null ---- time: [[2016-04-30 01:50:29.035147264Z,2016-05-06 14:12:58.988568576Z,2016-05-13 02:35:28.941989888Z,2016-05-19 14:57:58.895411200Z]] min: [[166.16246,194.89221,173.67485,0.0009941005]] mean: [[245.77867,268.51083,258.1954,213.16806]] max: [[351.54825,373.44482,361.16495,349.7588]] count: [[65474938,67508423,67516769,67494393]] stddev: [[30.139269,28.562775,32.872837,106.41149]]
| time | min | mean | max | count | stddev | |
|---|---|---|---|---|---|---|
| 0 | 2016-04-30 01:50:29.035147264+00:00 | 166.162460 | 245.778671 | 351.548248 | 65474938 | 30.139269 |
| 1 | 2016-05-06 14:12:58.988568576+00:00 | 194.892212 | 268.510834 | 373.444824 | 67508423 | 28.562775 |
| 2 | 2016-05-13 02:35:28.941989888+00:00 | 173.674850 | 258.195404 | 361.164948 | 67516769 | 32.872837 |
| 3 | 2016-05-19 14:57:58.895411200+00:00 | 0.000994 | 213.168060 | 349.758789 | 67494393 | 106.411491 |
If your workflow relies on precise-valued windowed queries (bp_stream.windows()), the new windowed_values queries accepts precise=True as an keyword argument.
Working with Multiple Streams¶
StreamSets, are now more closely embedded with the Client API. Explicitly: streams_in_collection now returns the set of streams pre-wrapped as a StreamSet.
In [ ]:
Copied!
from btrdb.stream import StreamSet
from btrdb.stream import StreamSet
In [ ]:
Copied!
display(type(bp_conn.streams_in_collection("sunshine/PMU1")))
bp_pmu1 = StreamSet(bp_conn.streams_in_collection("sunshine/PMU1"))
print()
pt_pmu1 = pt_conn.streams_in_collection("sunshine/PMU1")
display(type(pt_pmu1))
display(type(bp_conn.streams_in_collection("sunshine/PMU1")))
bp_pmu1 = StreamSet(bp_conn.streams_in_collection("sunshine/PMU1"))
print()
pt_pmu1 = pt_conn.streams_in_collection("sunshine/PMU1")
display(type(pt_pmu1))
/opt/conda/lib/python3.11/site-packages/btrdb/exceptions.py:52: FutureWarning: StreamSet will be the default return object for ``streams_in_collection`` in a future release. return fn(*args, **kwargs)
list
pingthings.timeseries.client.StreamSet
Though you can still manually create StreamSets from lists of Streams
In [ ]:
Copied!
pmu1_voltages = pt.StreamSet([stream for stream in pt_pmu1 if stream.tags()["unit"] == "volts"])
for stream in pmu1_voltages:
print(stream.name)
pmu1_voltages = pt.StreamSet([stream for stream in pt_pmu1 if stream.tags()["unit"] == "volts"])
for stream in pmu1_voltages:
print(stream.name)
L1MAG L3MAG L2MAG
As with Streams, the new StreamSet queries also return data in the form of PyArrow tables, with the column names specifying the UUID of the individual streams. These columns can be renamed as desired directly using PyArrow.
In [ ]:
Copied!
start = timez.to_nanoseconds("2016-1-1 18:00:00")
end = start + timez.ns_delta(seconds=0.1)
display(bp_pmu1.filter(name=re.compile("L.MAG"), start=start, end=end).to_dataframe())
display(Markdown("---"))
display(
pmu1_voltages.raw_values(start, end)
.rename_columns(["time"] + [f"{stream.collection}/{stream.name}" for stream in pmu1_voltages])
.to_pandas()
.set_index("time")
)
start = timez.to_nanoseconds("2016-1-1 18:00:00")
end = start + timez.ns_delta(seconds=0.1)
display(bp_pmu1.filter(name=re.compile("L.MAG"), start=start, end=end).to_dataframe())
display(Markdown("---"))
display(
pmu1_voltages.raw_values(start, end)
.rename_columns(["time"] + [f"{stream.collection}/{stream.name}" for stream in pmu1_voltages])
.to_pandas()
.set_index("time")
)
| sunshine/PMU1/L1MAG | sunshine/PMU1/L3MAG | sunshine/PMU1/L2MAG | |
|---|---|---|---|
| time | |||
| 1451671200008333000 | 7227.012207 | 7202.724121 | 7243.580078 |
| 1451671200016666000 | 7226.926758 | 7202.569336 | 7243.549805 |
| 1451671200024999000 | 7227.196289 | 7202.812988 | 7243.640625 |
| 1451671200033333000 | 7227.389648 | 7202.814453 | 7243.552734 |
| 1451671200041666000 | 7227.293457 | 7202.337891 | 7243.318359 |
| 1451671200049999000 | 7227.145996 | 7202.037109 | 7243.214844 |
| 1451671200058333000 | 7227.182129 | 7201.926758 | 7243.302734 |
| 1451671200066666000 | 7227.270020 | 7201.997559 | 7243.347656 |
| 1451671200074999000 | 7227.134766 | 7202.133301 | 7243.130859 |
| 1451671200083333000 | 7226.884766 | 7202.019043 | 7242.918457 |
| 1451671200091666000 | 7226.933594 | 7201.901855 | 7243.006348 |
| 1451671200099999000 | 7227.280762 | 7201.927246 | 7243.362793 |
| sunshine/PMU1/L1MAG | sunshine/PMU1/L3MAG | sunshine/PMU1/L2MAG | |
|---|---|---|---|
| time | |||
| 2016-01-01 18:00:00.008333+00:00 | 7227.012207 | 7202.724121 | 7243.580078 |
| 2016-01-01 18:00:00.016666+00:00 | 7226.926758 | 7202.569336 | 7243.549805 |
| 2016-01-01 18:00:00.024999+00:00 | 7227.196289 | 7202.812988 | 7243.640625 |
| 2016-01-01 18:00:00.033333+00:00 | 7227.389648 | 7202.814453 | 7243.552734 |
| 2016-01-01 18:00:00.041666+00:00 | 7227.293457 | 7202.337891 | 7243.318359 |
| 2016-01-01 18:00:00.049999+00:00 | 7227.145996 | 7202.037109 | 7243.214844 |
| 2016-01-01 18:00:00.058333+00:00 | 7227.182129 | 7201.926758 | 7243.302734 |
| 2016-01-01 18:00:00.066666+00:00 | 7227.270020 | 7201.997559 | 7243.347656 |
| 2016-01-01 18:00:00.074999+00:00 | 7227.134766 | 7202.133301 | 7243.130859 |
| 2016-01-01 18:00:00.083333+00:00 | 7226.884766 | 7202.019043 | 7242.918457 |
| 2016-01-01 18:00:00.091666+00:00 | 7226.933594 | 7201.901855 | 7243.006348 |
| 2016-01-01 18:00:00.099999+00:00 | 7227.280762 | 7201.927246 | 7243.362793 |
Note also that the old API did not convert the time values from nanoseconds to Timestamps.
Statistical queries are similarly managed, with the additional step of needing to apply a MultiIndex if you want to match the result of agg='all'
In [ ]:
Copied!
start = timez.to_nanoseconds("2016-1-1 18:00:00")
end = timez.to_nanoseconds("2017-1-1 18:00:00")
pw = pt.utils.nearest_point_width(days=30)
bp_df = (
bp_pmu1.filter(name=re.compile("L.MAG"), start=start, end=end)
.aligned_windows(pointwidth=pw.point_width)
.to_dataframe(agg="all")
)
bp_df.index = [pd.Timestamp(i) for i in bp_df.index] # manually convert index to timestamp
display(bp_df)
display(Markdown("---"))
renamer: dict[str, tuple[str]] = {}
for stream in pmu1_voltages:
for metric in ("min", "mean", "max", "count", "stddev"):
renamer[f"{stream.uuid}/{metric}"] = (stream.collection, stream.name, metric)
pt_df = (
pmu1_voltages.windowed_values(start, end, width=pw)
.to_pandas()
.set_index("time")
.rename(columns=renamer)
)
display(pt_df.reindex(pd.MultiIndex.from_tuples(pt_df.columns), axis=1))
start = timez.to_nanoseconds("2016-1-1 18:00:00")
end = timez.to_nanoseconds("2017-1-1 18:00:00")
pw = pt.utils.nearest_point_width(days=30)
bp_df = (
bp_pmu1.filter(name=re.compile("L.MAG"), start=start, end=end)
.aligned_windows(pointwidth=pw.point_width)
.to_dataframe(agg="all")
)
bp_df.index = [pd.Timestamp(i) for i in bp_df.index] # manually convert index to timestamp
display(bp_df)
display(Markdown("---"))
renamer: dict[str, tuple[str]] = {}
for stream in pmu1_voltages:
for metric in ("min", "mean", "max", "count", "stddev"):
renamer[f"{stream.uuid}/{metric}"] = (stream.collection, stream.name, metric)
pt_df = (
pmu1_voltages.windowed_values(start, end, width=pw)
.to_pandas()
.set_index("time")
.rename(columns=renamer)
)
display(pt_df.reindex(pd.MultiIndex.from_tuples(pt_df.columns), axis=1))
| sunshine/PMU1 | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| L1MAG | L3MAG | L2MAG | |||||||||||||
| min | mean | max | count | stddev | min | mean | max | count | stddev | min | mean | max | count | stddev | |
| 2015-12-15 05:58:00.013299712 | 6686.668457 | 7162.191720 | 7262.521484 | 226238399 | 34.160419 | 6343.702637 | 7117.509685 | 7243.531250 | 226238399 | 39.304326 | 6562.459961 | 7144.443595 | 7285.520996 | 226238399 | 45.038278 |
| 2016-02-05 08:57:59.640670208 | 771.582458 | 7154.288364 | 7298.772461 | 149673534 | 34.191939 | 809.082642 | 7123.868194 | 7309.245117 | 149673534 | 36.310027 | 846.452637 | 7148.059164 | 7338.802246 | 149673534 | 38.897277 |
| 2016-03-02 10:27:59.454355456 | 4686.440918 | 7154.052820 | 7305.978516 | 270216261 | 37.179477 | 4284.337402 | 7123.881012 | 7324.551758 | 270216271 | 40.578309 | 6265.968750 | 7148.867008 | 7367.452637 | 270216296 | 45.239399 |
| 2016-03-28 11:57:59.268040704 | 6825.371094 | 7157.580868 | 7301.885254 | 269591739 | 36.514187 | 6722.911133 | 7125.577244 | 7320.832031 | 269591712 | 42.586520 | 6803.852051 | 7156.322588 | 7389.022949 | 269591753 | 47.147822 |
| 2016-04-23 13:27:59.081725952 | 6580.954102 | 7161.233046 | 7300.862305 | 269692154 | 35.614534 | 6822.678223 | 7128.746149 | 7281.977539 | 269692053 | 38.450355 | 6805.867676 | 7160.659675 | 7314.780762 | 269692201 | 41.751122 |
| 2016-05-19 14:57:58.895411200 | 6796.833984 | 7160.458736 | 7286.551270 | 147383982 | 34.164686 | 6616.683105 | 7132.739318 | 7260.202148 | 147383942 | 34.939915 | 6653.491699 | 7158.413277 | 7304.769043 | 147383907 | 38.659853 |
| 2016-06-14 16:27:58.709096448 | 6964.914551 | 7159.378036 | 7325.371582 | 206497053 | 40.149203 | 6907.377930 | 7109.477689 | 7304.166016 | 206497053 | 36.825906 | 6928.157227 | 7130.953575 | 7326.692383 | 206497053 | 35.976938 |
| 2016-07-10 17:57:58.522781696 | 5558.574219 | 7167.452926 | 7294.763184 | 269810992 | 39.238089 | 6341.108398 | 7113.368068 | 7260.762695 | 269810992 | 36.547327 | 6316.791992 | 7131.997280 | 7287.865723 | 269810992 | 38.203994 |
| 2016-08-05 19:27:58.336466944 | 5780.056641 | 7161.174369 | 7307.212891 | 268549058 | 41.321697 | 5629.030762 | 7117.834837 | 7252.260254 | 268549058 | 36.948936 | 6436.246094 | 7131.531703 | 7271.197754 | 268549058 | 38.167856 |
| 2016-08-31 20:57:58.150152192 | 6392.520996 | 7161.894406 | 7318.228516 | 268988146 | 39.961410 | 6674.623535 | 7126.262191 | 7310.357910 | 268988146 | 36.367702 | 6332.310547 | 7142.171841 | 7331.629395 | 268988146 | 37.958689 |
| 2016-09-26 22:27:57.963837440 | 5955.321289 | 7158.670969 | 7300.725098 | 255962541 | 39.977005 | 5741.359863 | 7130.095506 | 7287.339844 | 255962541 | 37.162470 | 5688.133789 | 7142.680745 | 7298.871582 | 255962541 | 38.296652 |
| 2016-10-22 23:57:57.777522688 | 5097.312988 | 7156.974234 | 7283.453613 | 268787149 | 36.330541 | 3451.364014 | 7127.434671 | 7266.221191 | 268787149 | 36.174046 | 5108.195312 | 7151.079672 | 7299.839844 | 268787141 | 37.499896 |
| 2016-11-18 01:27:57.591207936 | 6591.234863 | 7158.855179 | 7281.747559 | 270215978 | 33.485651 | 6529.441895 | 7124.736314 | 7247.689453 | 270215978 | 35.902873 | 5967.048828 | 7153.131924 | 7305.472656 | 270215978 | 39.321384 |
/opt/conda/lib/python3.11/site-packages/pingthings/timeseries/client.py:1636: UserWarning: Multistream Window and Aligned window queries do not currently support non-nullable fields. Will be replaced with Null values. warnings.warn(
| sunshine/PMU1 | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| L1MAG | L3MAG | L2MAG | |||||||||||||
| min | mean | max | count | stddev | min | mean | max | count | stddev | min | mean | max | count | stddev | |
| time | |||||||||||||||
| 2015-12-15 05:58:00.013299712+00:00 | 6686.668457 | 7162.191895 | 7262.521484 | 226238399 | 34.160419 | 6343.702637 | 7117.509766 | 7243.531250 | 226238399 | 39.304325 | 6562.459961 | 7144.443359 | 7285.520996 | 226238399 | 45.038277 |
| 2016-02-05 08:57:59.640670208+00:00 | 771.582458 | 7154.288574 | 7298.772461 | 149673534 | 34.191940 | 809.082642 | 7123.868164 | 7309.245117 | 149673534 | 36.310028 | 846.452637 | 7148.059082 | 7338.802246 | 149673534 | 38.897278 |
| 2016-03-02 10:27:59.454355456+00:00 | 4686.440918 | 7154.052734 | 7305.978516 | 270216261 | 37.179478 | 4284.337402 | 7123.880859 | 7324.551758 | 270216271 | 40.578308 | 6265.968750 | 7148.867188 | 7367.452637 | 270216296 | 45.239399 |
| 2016-03-28 11:57:59.268040704+00:00 | 6825.371094 | 7157.581055 | 7301.885254 | 269591739 | 36.514187 | 6722.911133 | 7125.577148 | 7320.832031 | 269591712 | 42.586521 | 6803.852051 | 7156.322754 | 7389.022949 | 269591753 | 47.147823 |
| 2016-04-23 13:27:59.081725952+00:00 | 6580.954102 | 7161.232910 | 7300.862305 | 269692154 | 35.614536 | 6822.678223 | 7128.746094 | 7281.977539 | 269692053 | 38.450356 | 6805.867676 | 7160.659668 | 7314.780762 | 269692201 | 41.751122 |
| 2016-05-19 14:57:58.895411200+00:00 | 6796.833984 | 7160.458496 | 7286.551270 | 147383982 | 34.164684 | 6616.683105 | 7132.739258 | 7260.202148 | 147383942 | 34.939915 | 6653.491699 | 7158.413086 | 7304.769043 | 147383907 | 38.659855 |
| 2016-06-14 16:27:58.709096448+00:00 | 6964.914551 | 7159.377930 | 7325.371582 | 206497053 | 40.149204 | 6907.377930 | 7109.477539 | 7304.166016 | 206497053 | 36.825905 | 6928.157227 | 7130.953613 | 7326.692383 | 206497053 | 35.976936 |
| 2016-07-10 17:57:58.522781696+00:00 | 5558.574219 | 7167.453125 | 7294.763184 | 269810992 | 39.238091 | 6341.108398 | 7113.368164 | 7260.762695 | 269810992 | 36.547325 | 6316.791992 | 7131.997070 | 7287.865723 | 269810992 | 38.203995 |
| 2016-08-05 19:27:58.336466944+00:00 | 5780.056641 | 7161.174316 | 7307.212891 | 268549058 | 41.321697 | 5629.030762 | 7117.834961 | 7252.260254 | 268549058 | 36.948936 | 6436.246094 | 7131.531738 | 7271.197754 | 268549058 | 38.167854 |
| 2016-08-31 20:57:58.150152192+00:00 | 6392.520996 | 7161.894531 | 7318.228516 | 268988146 | 39.961411 | 6674.623535 | 7126.262207 | 7310.357910 | 268988146 | 36.367702 | 6332.310547 | 7142.171875 | 7331.629395 | 268988146 | 37.958691 |
| 2016-09-26 22:27:57.963837440+00:00 | 5955.321289 | 7158.670898 | 7300.725098 | 255962541 | 39.977005 | 5741.359863 | 7130.095703 | 7287.339844 | 255962541 | 37.162468 | 5688.133789 | 7142.680664 | 7298.871582 | 255962541 | 38.296650 |
| 2016-10-22 23:57:57.777522688+00:00 | 5097.312988 | 7156.974121 | 7283.453613 | 268787149 | 36.330540 | 3451.364014 | 7127.434570 | 7266.221191 | 268787149 | 36.174046 | 5108.195312 | 7151.079590 | 7299.839844 | 268787141 | 37.499897 |
| 2016-11-18 01:27:57.591207936+00:00 | 6591.234863 | 7158.854980 | 7281.747559 | 270215978 | 33.485653 | 6529.441895 | 7124.736328 | 7247.689453 | 270215978 | 35.902874 | 5967.048828 | 7153.131836 | 7305.472656 | 270215978 | 39.321384 |