Querying Building-Specific Data

For column definitions, see Columns for Data Extracted from Buildings.

Querying Equipment

Using the API, we can retrieve the data from all the buildings that belong to our organization:

>>> # Get a list of all the buildings under your Organization
>>> pd.json_normalize(client.get_all_buildings())
    id  org_id             name  ... point_count info.note info
0   66       6   T`Challa House  ...          81            NaN
1  427       6  Office Building  ...        4219       NaN  NaN
2  428       6       Laboratory  ...        2206       NaN  NaN
3  429       6         Hogwarts  ...        4394       NaN  NaN

The first column of this dataframe (‘id’) contains the building identifier number.

In order to retrieve the equipment for a particular building (e.g. Laboratory, id: 428), we use client.get_building_equipment():

>>> # Get a list of all equipment in a building
>>> all_equipment = pd.DataFrame(client.get_building_equipment(428))
>>> all_equipment[['id', 'building_id', 'equip_id',  'points', 'tags']]
        id  building_id        equip_id                                             points                     tags
0    27293          428      crac-T-105  [{'id': 291731, 'building_id': 428, 'last_upda...             [crac, hvac]
1    27294          428   exhaustFan-01  [{'id': 290783, 'building_id': 428, 'last_upda...  [fan, hvac, exhaustFan]
2    27295          428  exhaustFan-021  [{'id': 289684, 'building_id': 428, 'last_upda...  [fan, hvac, exhaustFan]
3    27296          428  exhaustFan-022  [{'id': 289655, 'building_id': 428, 'last_upda...  [fan, hvac, exhaustFan]
...

Querying Specific Points

In order to query specific points, first we need to import the PointSelector class:

>>> # Set parameters for querying sensor data
>>> from onboard.client.models import PointSelector
>>> query = PointSelector()

There are multiple ways to select points using the PointSelector. The user can select all the points that are associated with one or more lists containing any of the following:

'organizations', 'buildings', 'point_ids', 'point_names', 'point_hashes',
'point_ids', 'point_names', 'point_topics', 'equipment', 'equipment_types'

For example, here we make a query that returns all the points of the type ‘Real Power’ OR of the type ‘Zone Temperature’ that belong to the ‘Laboratory’ building:

>>> query = PointSelector()
>>> query.point_types = ['Real Power', 'Zone Temperature']
>>> query.buildings = ['Laboratory']
>>> selection = client.select_points(query)

We can add to our query to e.g. further require that returned points must be associated with the ‘fcu’ equipment type:

>>> query = PointSelector()
>>> query.point_types = ['Real Power', 'Zone Temperature']
>>> query.equipment_types = ['fcu']
>>> query.buildings = ['Laboratory']
>>> selection = client.select_points(query)
>>> selection
{'buildings': [428],
'equipment': [27356, 27357],
'equipment_types': [9],
'orgs': [6],
'point_types': [77],
'points': [289701, 289575]}

In this example, the points with ID=162801, and 162795 are the only ones that satisfy the requirements of our query.

We can get more information about these points by calling the function get_points_by_ids() on selection[‘points’]:

>>> # Get Metadata for the sensors you would like to query
>>> sensor_metadata = client.get_points_by_ids(selection['points'])
>>> sensor_metadata_df = pd.DataFrame(sensor_metadata)
>>> sensor_metadata_df[['id', 'building_id', 'first_updated', 'last_updated', 'type', 'value', 'units']]
       id  building_id  first_updated  last_updated              type value              units
0  289575          428   1.626901e+12  1.641928e+12  Zone Temperature  66.0  degreesFahrenheit
1  289701          428   1.626901e+12  1.641928e+12  Zone Temperature  61.0  degreesFahrenheit

sensor_metadata_df now contains a dataframe with rows for each point. Based on the information about these points, we can observe that none of the points of our list belongs to the point type ‘Real Power’, but only to the point type ‘Zone Temperature’

Exporting Data to .csv

Data extracted using the API can be exported to a .csv or excel file using Pandas:

>>> # Save Metadata to .csv file
>>> sensor_metadata_df.to_csv('~/metadata_query.csv')

Querying Time-Series Data

To query time-series data first we need to import modules from datetime, models and dataframes.

>>> from datetime import datetime, timezone, timedelta
>>> import pytz
>>> from onboard.client.models import TimeseriesQuery, PointData
>>> from onboard.client.dataframes import points_df_from_streaming_timeseries

We select the range of dates we want to query, in UTC format:

>>> # Enter Start & End Time Stamps in UTC
>>> # Example "2018-06-03T12:00:00Z"
>>>
>>> # get data from the past week
>>> start = datetime.now(pytz.utc) - timedelta(days=7)
>>> end = datetime.now(pytz.utc)

Now we are ready to query the time-series data for the points we previously selected in the specified time-period

>>> # Get time series data for the sensors you would like to query
>>> timeseries_query = TimeseriesQuery(point_ids = selection['points'], start = start, end = end)
>>> sensor_data = points_df_from_streaming_timeseries(client.stream_point_timeseries(timeseries_query))
>>> sensor_data
                         timestamp 289575 289701
0      2022-01-04T19:34:11.741000Z   68.0   None
1      2022-01-04T19:34:19.143000Z   None   62.0
2      2022-01-04T19:35:12.133000Z   68.0   None
...

This returns a dataframe containing columns for the timestamp and for each requested point.

Here, we set the timestamp as the index and forward fill the data for plotting

>>> sensor_data_clean = sensor_data.set_index('timestamp').astype(float).ffill()
>>>
>>> # Edit the indexes just for visualization purposes
>>> indexes = [i.split('T')[0] for i in list(sensor_data_clean.index)]
>>> sensor_data_clean.index = indexes
>>>
>>> fig = sensor_data_clean.plot(figsize=(15,8), fontsize = 12)
>>>
>>> # Adding some formatting
>>> fig.set_ylabel('Farenheit',fontdict={'fontsize':15})
>>> fig.set_xlabel('time stamp',fontdict={'fontsize':15})
_images/plot.png