{"id":46625,"date":"2020-12-18T00:00:00","date_gmt":"2020-12-18T08:00:00","guid":{"rendered":"https:\/\/griddb-linux-hte8hndjf8cka8ht.westus-01.azurewebsites.net\/blog\/making-sense-out-of-smart-metering-data-with-griddb-and-jupyter-notebook\/"},"modified":"2025-11-13T12:55:07","modified_gmt":"2025-11-13T20:55:07","slug":"making-sense-out-of-smart-metering-data-with-griddb-and-jupyter-notebook","status":"publish","type":"post","link":"https:\/\/griddb-linux-hte8hndjf8cka8ht.westus-01.azurewebsites.net\/en\/blog\/making-sense-out-of-smart-metering-data-with-griddb-and-jupyter-notebook\/","title":{"rendered":"Making Sense Out of Smart Metering Data With GridDB and Jupyter Notebook"},"content":{"rendered":"

Introduction and Purpose<\/h2>\n

The Use Case<\/h3>\n

Imagine that you are the owner of a building complex. You have installed smart meters all over the place to monitor power usage. Each of the devices creates a timestamp two times in a minute. It saves power usage data in kW and timestamps in the form of epoch seconds. The IoT provider sends you a .CSV file every month. <\/p>\n

Objectives<\/h3>\n

You want to propose an energy-saving plan. To start, you'd like to identify patterns in power usage<\/strong>: how the power is consumed, when less of it is consumed, and when power usage is particularly extensive. For this purpose, you will build different data visualizations that help to catch patterns in time series data without applying such advanced methods as regression analysis.<\/p>\n

Methods<\/h3>\n

The article will explain how to save the extracted raw data into GridDB<\/strong>, and then visualize it in Jupyter Notebook. The latter is very convenient for exploring trends and patterns in the data and for writing reports. In Jupyter, you have cells for code chunks, and you have cells for simple text where you can write down your thoughts, notes, etc. Jupyter Notebook is a powerful tool for data scientists since it allows loading huge amounts of data into it and analyzing them easily.<\/p>\n

Jupyter Notebook works with all Python packages. <\/p>\n

Prerequisites<\/h3>\n

The present tutorial is based upon a previous GridDB blog post that shows how to install Jupyter and access GridDB from it<\/a>.<\/p>\n

To start with the tutorial, you need to finish both aforementioned installations first.<\/p>\n

Saving Data Into the Database<\/h2>\n

We will build the database from a publicly available IoT dataset. It contains smart metering data on power usage in a university campus<\/a>. A one-month data with a total of 86192 is sufficient to illustrate the tutorial steps.
\nLet's get our hands dirty.<\/p>\n

Access the Database<\/h3>\n

First of all, we access this database using JayDeBeApi Python package. <\/p>\n

\n
import<\/span> jaydebeapi\n\nconn = jaydebeapi.connect<\/span>(\"com.toshiba.mwcloud.gs.sql.Driver\"<\/span>,\n                           \"jdbc:gs:\/\/griddb:20001\/defaultCluster\/public?notificationMember:127.0.0.1:20001\"<\/span>,\n                           [\"admin\"<\/span>, \"admin\"<\/span>],\n                          \"\/usr\/share\/java\/gridstore-jdbc-4.5.0.jar\"<\/span>,)\ncurs = conn.cursor<\/span>()\n<\/code><\/pre>\n<\/div>\n
Create a Table<\/h2>\n
We have established a connection to the database and can insert the data. The queries can be written in SQL. We send them to the database using curs.execute()<\/strong>. The first query to start is the one that creates the data table.<\/p>\n
\n
curs.execute('CREATE<\/span> TABLE<\/span> power2(timestamp<\/span> INTEGER<\/span>, power<\/span> FLOAT<\/span>)')<\/span>\n<\/code><\/pre>\n<\/div>\n
Inserting the Data<\/h2>\n
We load the data from the .CSV into the database. We can do it directly via Jupyter, by saving the data into a data frame, and then sending it to GriDB.  If you get new data regularly, you can repeat this step to add new data to the database, or automate its execution.<\/p>\n
Making short checks by calling the info() or head()<\/strong> function is a good habit and helps to spare a lot of headaches. If the data has gaps or looks weird, you notice it in the very beginning.<\/p>\n
\n
import<\/span> pandas as pd\n\npower<\/span> = pd.read_csv('Power.csv'<\/span>, names = ['timestamp'<\/span>, 'power'<\/span>])\npower<\/span>.info()\n<\/code><\/pre>\n<\/div>\n
<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 86192 <\/span>entries, 0 <\/span>to 86191\nData columns (total 2 <\/span>columns):\n #   Column     Non-Null Count  Dtype  <\/span>\n---  ------     --------------  -----  \n 0 <\/span>  timestamp  86192 <\/span>non-null  float64\n 1 <\/span>  power      86192 <\/span>non-null  float64\ndtypes: float64(2)\nmemory usage: 1.3 MB\n<\/code><\/pre>\n
\n
pd.options<\/span>.display<\/span>.float_format<\/span> = '{:.2f}'<\/span>.format<\/span>\npower.head()\n<\/code><\/pre>\n<\/div>\n
\n