2019 年度 OSS リテラシ 3 : 代表値・欠損値処理
はじめに
センサーから得られたデータの代表値 (平均値・最大・最小・標準偏差) を計算し, それを MariaDB のテーブルに登録し, grafana で可視化する.
代表値の計算を Ruby などにやらせても良いが, ここでは MySQL の関数を用いて計算することにする.
今回の実習では, ラズパイから送られてきた 10 秒毎の瞬間値から 10 分平均値, 1 時間毎の値の抽出, 1 日の最低・最大・平均値を作成し, それらのデータを grafana で表示できるようにする.
- 10 秒間隔のデータ => 10 分平均値
- 10 分間の中にデータが一定数以上無い場合には欠損値に.
- 10 分間隔の平均値 => 1 時間間隔の平均値 (10 分間隔のデータから, 毎正時の値を抽出)
- 1 時間間隔の平均値 => 1 日単位の平均値・最大値・最小値・標準偏差
- データが 24 点ない場合は欠損値に.
統計量の作り方は気象庁のやり方を真似ている (全く同じではないが). 詳細は気象観測統計の解説 を参照のこと.
以下の作業はサーバ (VM) で行うべきものである. ラズパイで行うことはない (きちんとデータが送られている限りは).
準備
データの確認
前回までの内容で, ラズパイからデータが送られてきたデータが grafana で可視化されているはずである. 各自の管理するサーバ上で動いている grafana において, 以下のような画面が表示されているか確認すること.
また, MySQL を直接確認すると, 約 10 秒間隔でデータがテーブルに入力されていることがわかる (以下の例は都合により 1 分間隔であるが).
$ mysql -u hogehoge -p
Enter password: (適宜入力)
...(略)...
MariaDB [(none)]> use iotex
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [iotex]> select time,temp from monitoring order by time;
...(略)...
| 2019-01-15 09:32:00 | 19.68 |
| 2019-01-15 09:33:00 | 19.14 |
| 2019-01-15 09:34:00 | 18.84 |
| 2019-01-15 09:35:00 | 18.62 |
| 2019-01-15 09:36:00 | 18.49 |
| 2019-01-15 09:37:00 | 18.35 |
| 2019-01-15 09:38:00 | 18.3 |
| 2019-01-15 09:39:00 | 18.95 |
| 2019-01-15 09:40:00 | 19.85 |
| 2019-01-15 09:41:00 | 19.8 |
| 2019-01-15 09:42:00 | 19.85 |
なお, MySQL のテーブルには以下のカラムが存在するが, 接続したものセンサー以外の値は NULL になっている.
- hostname (ホスト名)
- time (時刻)
- temp (温度, SENSIRION SHT75 (1 台目))
- temp2 (温度, SENSIRION SHT75 (2 台目))
- temp3 (温度, SENSIRION SHT75 (3 台目))
- humi (湿度, SENSIRION SHT75 (1 台目))
- humi2 (湿度, SENSIRION SHT75 (2 台目))
- humi3 (湿度, SENSIRION SHT75 (3 台目))
- dp (露点温度, SENSIRION SHT75 (1 台目))
- dp2 (露点温度, SENSIRION SHT75 (2 台目))
- dp3 (露点温度, SENSIRION SHT75 (3 台目))
- pres (圧力, BMP180)
- bmptemp (温度, BMP180)
- dietemp (基板の温度, TMP007)
- objtemp (放射温度 (壁の温度), TMP007)
- lux (照度, TSL2561)
- sitemp (温度, Si7021)
- sihumi (湿度, Si7021)
- eco2 (CO2 濃度, SGP30)
- tvoc (総揮発性有機化合物量, SGP30)
必要なパッケージのインストール
# apt-get update # apt-get install ruby-mysql2 ruby-activesupport
MySQL の接続情報の用意
データベース接続情報 (db_info.yml) を用意する. データベース接続情報 (db_info.yml) は絶対に ~/public_html 以下には置かないこと. なお, yml 形式のファイルでは, コロン (:) の後に空白を 1 つ入れる必要があることに注意せよ.
$ cd $ cd iotex-server/conf $ chmod 640 db_info.yml $ sudo chgrp www-data db_info.yml $ ls -l -rw-r----- 1 hogehoge www-data 84 8月 28 12:24 db_info.yml $ vi db_info.yml ADPT: "mysql2" SERV: "localhost" USER: "<username>" (適宜修正すること. "<" と ">" は削除すること) PASS: "<password>" (適宜修正すること. "<" と ">" は削除すること) DBNM: "iotex"
データ処理で注意すること : 欠損値
何らかの理由でセンサー (ラズパイ) がフリーズすると, その時間帯のデータは取得できない = データが欠損する, ということが生じる. grafana に限らず dcl や gnuplot でも同じであるが, データ欠損があることを教えてあげないと 描画ツールは以下のように欠損部分をつないだ不自然な線グラフを作ってしまう. 本来はデータが無いはずなのに変なデータが存在しているように見えてしまい, これはデータ処理を行う上で大変まずい.
データに欠損値がある場合には, そこに NULL や取り得ない数字 (999 など)を入れておく. そして, 描画ツールに欠損値はどのように表されているか教える必要がある.
grafana はデフォルトで NULL は欠損値として扱うので, 以下では欠損値に NULL を入れることにする. NULL を欠損値として扱っていることを確かめるには, グラフの編集 (edit) ページを開き, "Display" タグを開くと良い.
欠損値を正しく設定すれば, 以下の図のように欠損値の部分は折れ線でつながれなくなる.
10 分平均値を作成する
まずはテーブル monitoring に保存されている温度データを 10 分平均して別テーブルに入力する SQL 文を順を追って考えてみる.
SQL のターミナルを立ち上げる.
$ mysql -u hogehoge -p
Enter password: (適宜入力)
...(略)...
MariaDB [(none)]> use iotex
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
テストデータが入っていることを確認する. 簡単のために温度のみ表示する.
MariaDB [iotex]> select hostname,time,temp from monitoring where hostname = 'j55' and time > '2019-08-01 00:00:00' and time <= '2019-08-01 00:10:00'; +----------+---------------------+--------------------+ | hostname | time | temp | +----------+---------------------+--------------------+ | j55 | 2019-08-01 00:01:00 | 29.586666666666666 | | j55 | 2019-08-01 00:02:00 | 29.578333333333333 | | j55 | 2019-08-01 00:03:00 | 29.578333333333333 | | j55 | 2019-08-01 00:04:00 | 29.581666666666663 | | j55 | 2019-08-01 00:05:00 | 29.58 | | j55 | 2019-08-01 00:06:00 | 29.578333333333333 | | j55 | 2019-08-01 00:07:00 | 29.575000000000003 | | j55 | 2019-08-01 00:08:00 | 29.575 | | j55 | 2019-08-01 00:09:00 | 29.573333333333334 | | j55 | 2019-08-01 00:10:00 | 29.576666666666668 | +----------+---------------------+--------------------+ 10 rows in set (0.001 sec)
上記時間帯について平均操作を行う. 気象のデータは前 10 分で平均するのが普通であるが, 何も指定しないと出力では平均開始時刻に対する温度の平均値という形で表示されてしまう (前 10 分平均でなく後 10 分平均の形で表示されてしまう).
MariaDB [iotex]> select hostname,time,avg(temp) from monitoring where hostname = 'j55' and time > '2019-08-01 00:00:00' and time <= '2019-08-01 00:10:00'; +----------+---------------------+-------------------+ | hostname | time | avg(temp) | +----------+---------------------+-------------------+ | j55 | 2019-08-01 00:01:00 | 29.57833333333333 | +----------+---------------------+-------------------+ 1 row in set (0.001 sec)
明示的に表示する時刻を変える場合は, 例にあるように時刻を手動で書き入れてしまえ良い.
MariaDB [iotex]> select hostname,'2019-08-01 00:10:00',avg(temp) from monitoring where hostname = 'j55' and time > '2019-08-01 00:00:00' and time <= '2019-08-01 00:10:00'; +----------+---------------------+--------------------+ | hostname | 2019-08-01 00:10:00 | avg(temp) | +----------+---------------------+--------------------+ | j55 | 2019-08-01 00:01:00 | 29.57833333333333 | +----------+---------------------+--------------------+ 1 row in set (0.001 sec)
同様にしてホスト名も例にあるように時刻を手動で書き入れてしまえば, そのように表示される.
MariaDB [iotex]> select 'hoge','2019-08-01 00:10:00',avg(temp) from monitoring where hostname = 'j55' and time > '2019-08-01 00:00:00' and time < '2019-08-01 00:10:00'; +------+---------------------+--------------------+ | hoge | 2019-08-01 00:10:00 | avg(temp) | +------+---------------------+--------------------+ | hoge | 2019-08-01 00:10:00 | 29.57833333333333 | +------+---------------------+--------------------+ 1 row in set (0.001 sec)
このようにして平均操作を行った結果を別のテーブルにそのまま入力することができる.
MariaDB [iotex]> insert into monitoring_10min (hostname,time,temp) select 'hoge','2019-08-01 00:10:00',avg(temp) from monitoring where hostname = 'j55' and time > '2019-08-01 00:00:00' and time < '2019-08-01 00:10:00'; Query OK, 1 row affected (0.001 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [iotex]> select hostname,time,temp from monitoring_10min where time = '2019-08-01 00:10:00'; +----------+---------------------+--------------------+ | hostname | time | temp | +----------+---------------------+--------------------+ | hoge | 2019-08-01 00:10:00 | 29.57833333333333 | +----------+---------------------+--------------------+ 1 row in set (0.032 sec)
平均を作成する際に, 指定された時間帯に一定以上のデータ数が含まるか調べることをよく行う. 例えば, 指定された時間帯に 5 点以上のデータがあれば平均し, そうでなければ null (欠損値) を入れる場合には 以下のような SQL 文を書けば良い.
MariaDB [iotex]> insert into monitoring_10min (time,hostname,temp) select '2019-08-01 00:20:00',hostname,(case when count(temp) >= 5 then avg(temp) else null end) from monitoring where time > '2019-08-01 00:10:00' and time <= '2019-08-01 00:20:00' and hostname = 'j55'; Query OK, 1 row affected (0.002 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [iotex]> select hostname,time,temp from monitoring_10min where time = '2019-08-01 00:20:00'; +----------+---------------------+-------------------+ | hostname | time | temp | +----------+---------------------+-------------------+ | j55 | 2019-08-01 00:20:00 | 29.57133333333333 | +----------+---------------------+-------------------+ 1 row in set (0.028 sec)
上記の SQL 文を踏まえて, Ruby スクリプトを作成する. 以下に例を示すので, プログラム中のコメントを参考にやっていることを理解して欲しい. 特に, '(case when count(temp) >= 5 then avg(temp) else null end)' というような文字列を予め作成し, それを配列 columns2 に保管して使っている. 配列の要素をカンマ区切りの文字列に変換するのに join メソッドを使っている. なお, group by hostname を使うと欠損値処理ができない (その時刻にデータを持つホストに対してしか平均操作がなされない) ので, 存在するホスト名を調べておいて, そのホスト名でループを回している.
$ vi ~/make-10min.rb
#!/usr/bin/env ruby
# coding: utf-8
require 'yaml'
require 'mysql2'
require 'active_support/time'
###
### 変数宣言
###
# データベースへの接続情報の置き場.
# ~/public_html 以下には置かないこと.
conf = "/home/hogehoge/iotex-server/conf/db_info.yml" # <= 書き換えること!!
# データベースのテーブル名 (既に定義済み)
mytable_from = "monitoring"
mytable_to = "monitoring_10min"
# 設定ファイルの読み込み
mydb = YAML.load_file( conf )
# データベースへの接続
client = Mysql2::Client.new(
:host => "#{mydb["SERV"]}",
:username => "#{mydb["USER"]}",
:password => "#{mydb["PASS"]}",
:database => "#{mydb["DBNM"]}"
)
###
### 時刻の設定
###
# 平均を開始する時間 (デフォルト値)
time_from = DateTime.new( 2019, 12, 1, 0, 0, 0, "JST")
# テーブルに既に値が入っている場合は開始時刻 time_from の値を更新する.
sql = "SELECT time FROM #{mytable_to} ORDER BY time DESC LIMIT 1"
client.query(sql).each do |item|
if item["time"].present?
time_from = Time.parse( item["time"].to_s )
end
end
# 平均操作を終了する時間. 現在時刻にセット.
time_end = DateTime.now
#p "+++++ START : #{time_from}, END : #{time_end} +++++"
###
### カラム名の取得
###
columns1 = Array.new
columns2 = Array.new
sql = "show columns from #{mytable_from}"
client.query(sql).each do |item|
if item["Field"] != "hostname" && item["Field"] != "time"
# カラム名の保管
columns1.push( item["Field"] )
# 各カラムについて, 5 点以上データが入っていれば平均を行う SQL 文を作る.
columns2.push( "(case when count(#{item["Field"]}) > 5 then avg(#{item["Field"]}) else null end) as #{item["Field"]}" )
end
end
#p columns1
#p columns2
###
### ホスト名の取得
###
hosts = Array.new
sql = "SELECT DISTINCT hostname FROM #{mytable_from}"
client.query(sql).each do |item|
hosts.push( item["hostname"] ) unless item["hostname"] == ''
end
#p hosts
###
### 前 10 分平均値の計算とテーブルへの代入
###
# 変数の初期化
time0 = time_from
time1 = time_from + 10.minutes
# ループを回しながら 10 分平均をとる.
while ( time1 < time_end ) do
p "#{time0} ... #{time1}"
# ホスト名のループを回す. 欠損値処理のため.
hosts.each do |host|
# SQL 文作成.
# 配列 column1, column2 内の要素をカンマ区切りで並べるために join メソッドを用いる.
# SELECT で指定する時刻はシングルクォートで囲むこと ('#{time1.strftime('%Y-%m-%d %H:%M:%S')}')
sql = "INSERT INTO #{mytable_to} (time,hostname,#{columns1.join(',')})
SELECT '#{time1.strftime('%Y-%m-%d %H:%M:%S')}','#{host}',#{columns2.join(',')}
FROM #{mytable_from}
WHERE time > '#{time0.strftime('%Y-%m-%d %H:%M:%S')}'
AND time <= '#{time1.strftime('%Y-%m-%d %H:%M:%S')}'
AND hostname LIKE '#{host}'"
# p sql
# SQL 文実行
client.query(sql)
end
# 時刻の更新
time0 = time1
time1 = time1 + 10.minutes
end
実際に動かしてみる. 標準出力に平均をとるのに用いた時間帯が表示される. 最初の 1 回目はプログラムの実行にそこそこ時間がかかる.
$ ruby ~/make-10min.rb "2019-01-15 22:00:00 +0900 ... 2019-01-15 22:10:00 +0900" "2019-01-15 22:10:00 +0900 ... 2019-01-15 22:20:00 +0900" "2019-01-15 22:20:00 +0900 ... 2019-01-15 22:30:00 +0900" "2019-01-15 22:30:00 +0900 ... 2019-01-15 22:40:00 +0900" "2019-01-15 22:40:00 +0900 ... 2019-01-15 22:50:00 +0900" "2019-01-15 22:50:00 +0900 ... 2019-01-15 23:00:00 +0900" "2019-01-15 23:00:00 +0900 ... 2019-01-15 23:10:00 +0900" "2019-01-15 23:10:00 +0900 ... 2019-01-15 23:20:00 +0900" "2019-01-15 23:20:00 +0900 ... 2019-01-15 23:30:00 +0900"
MySQL に接続して, データがテーブル monitoring_10min に保管されているか確かめること.
$ mysql -u hogehoge -p
Enter password: (適宜入力)
...(略)...
MariaDB [(none)]> use iotex
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [iotex]> select time,temp from monitoring_10min order by time;
...(略)...
| 2019-01-15 09:30:00 | 19.68 |
| 2019-01-15 09:40:00 | 19.14 |
crontab を設定して定期に実行させるようにする. ラズパイから送られてくる時間が毎分 0 秒とはならないので, 30 秒ほど sleep で調整している.
$ crontab -e MAILTO="" PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin */10 * * * * sleep 30; ruby make-10min.rb
1 時間おきの値を抽出する.
10 分間隔のデータから毎時 0 分のデータを SQL の命令を用いて取り出す. 気象分野ではこのように取り出した 1 時間おきの値を, 1 時間ごとの代表値とみなす. 次に行う 1 日の平均・最小・最大・標準偏差は, 1 時間おき (00 分) のデータを使って計算する.
先の 10 分間隔の平均値を作るスクリプト (make-10min.rb) を参照して新たなスクリプト (make-1hour.rb) を作成する. 略されている部分は自分で補完して動くようにせよ.
$ vi ~/make-1hour.rb ...(前略)... ### ### 変数宣言 ### ...(略)... ### ### 時刻の設定 ### ...(略)... ### ### 毎正時の値を抽出 ### # SQL 文作成. # 時刻 time_from より後の時刻について, 時刻の末尾が '00:00' となる行を mytable_from より取り出し, # その行を mytable_to に代入すれば良い. sql = # .... 自分で考えよ .... #p sql # SQL 文実行 client.query(sql)
実際に動かしてみる.
$ ruby ~/make-1hour.rb
MySQL に接続して, データがテーブル monitoring_1hour に保管されているか確かめること.
$ mysql -u hogehoge -p
Enter password: (適宜入力)
...(略)...
MariaDB [(none)]> use iotex
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [iotex]> select time,temp from monitoring_1hour order by time;
...(略)...
| 2019-01-15 09:30:00 | 19.68 |
| 2019-01-15 09:40:00 | 19.14 |
crontab を設定して定期に実行させるようにする. スクリプトを実行する時刻は毎時 11 分としている.
$ crontab -e MAILTO="" PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin */10 * * * * sleep 30; ruby make-10min.rb 11 * * * * ruby make-1hour.rb <= 追加
1 日単位での統計量の計算
SQL の AVG, MAX, MIN, STDDEV 関数を用いて 1 日ごとに平均・最大・最小・標準偏差を求める. 1 日の平均・最小・最大・標準偏差は 1 時間おき (00 分) のデータを使って計算するため, 1 日 24 点分のデータが有るかないかをチェックし, 有る場合のみ計算を行なうようにする. なお, 略されている部分は先の 10 分間隔の平均値を作るスクリプト (make-10min.rb) を参照しながら自分で補完せよ.
$ vi ~/make-1day.rb
...(前略)...
###
### 変数宣言
###
...(略)...
###
### 平均, 最小, 最大, 標準偏差のそれぞれについてループを回す.
###
# 関数名を op に代入してループを回す.
["avg", "min", "max", "stddev"].each do |op|
####
#### 時刻の設定
####
# 操作を開始する時間 (デフォルト値)
...(略)...
# テーブルに既に値が入っている場合は開始時刻 time_from の値を更新する.
sql = "SELECT time FROM #{mytable_to}_#{op} ORDER BY time DESC LIMIT 1"
client.query(sql).each do |item|
if item["time"].present?
# 1 日分づらす. 後ろ 1 日分の平均なので.
time_from = Time.parse( item["time"].to_s ) + 1.days
end
end
# 平均を終了する時間
...(略)...
####
#### カラム名の取得
####
columns1 = Array.new
columns2 = Array.new
sql = "show columns from #{mytable_from}"
client.query(sql).each do |item|
if item["Field"] != "hostname" && item["Field"] != "time"
# カラム名の保管
columns1.push( item["Field"] )
# 各カラムについて, 24 時間分のデータが入っていれば統計操作を行う SQL 文を作る.
columns2.push( "(case when count(#{item["Field"]})=24 then #{op}(#{item["Field"]}) else null end) as #{item["Field"]}" )
end
end
####
#### 1 日分 (0:00 ~ 23:00) のデータから最大・最小・平均・標準偏差を計算
####
# 時刻の初期化
time0 = time_from
time1 = time_from + 1.days
# 時刻のループ
while ( time1 < time_end ) do
p "#{time0} ... #{time1}"
# SQL 文の作成. 自分で考えてみよ.
# 時刻 00:00 から 23:00 の間で統計操作することで得られた値を, その日の代表値とすること.
# 出力先のテーブル名は "#{mytable_to}_#{op}" で表すことができることに注意せよ.
# 1 時間毎のデータは欠損値処理してあるので, ホスト名でループを回さずに GROUP BY hostname で一括処理することができる.
sql = # ....
# SQL の実行
client.query(sql)
# ループを回すための処理
time0 = time1
time1 = time1 + 1.days
end
end
スクリプトを実際に動かしてみる.
$ ruby ~/make-1day.rb
MySQL に接続して, データがテーブル monitoring_1day_min, monitoring_1day_max, monitoring_1day_avg, monitoring_1day_stddev に保管されているか確かめること.
$ mysql -u hogehoge -p
Enter password: (適宜入力)
...(略)...
MariaDB [(none)]> use iotex
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [iotex]> select time,temp from monitoring_1day_min order by time;
...(略)...
| 2019-12-28 00:00:00 | 13.78 |
| 2019-12-29 00:00:00 | 12.74909090909091 |
| 2019-12-30 00:00:00 | 13.65 |
| 2019-12-31 00:00:00 | 13.393 |
その他の monitoring_1day_max, monitoring_1day_avg, monitoring_1day_stddev に
ついても必ず確認すること.
さらに, crontab に設定し, 定期的に実行できるようにする. 以下の例では毎日 00:15 に実行している.
$ crontab -e MAILTO="" PATH=/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin */10 * * * * sleep 30; ruby make-10min.rb 11 * * * * ruby make-1hour.rb 15 0 * * * ruby make-1day.rb <= 追加
課題: 代表値のグラフ表示
これまでに作成した grafana のダッシュボードを改良し, 気温について, 10 分平均値, 1 時間平均値, 1 日平均値 (最低, 最高, 平均, 標準偏差), 平均値の比較図 (10 分平均, 1 時間平均, 1 日平均) の 4 つのグラフを作成せよ. 作成したダッシュボードのスナップショットを wbt に登録せよ.
なお, 平均値を重ね書きする場合は以下のようにSQL を書くと良い. ホスト名は適宜書き換えること (省略すると, 全ホストのデータが表示される).
SELECT UNIX_TIMESTAMP(time) as time_sec, temp as "instantaneous" FROM monitoring WHERE $__timeFilter(time) AND hostname = 'j52' ORDER BY time SELECT UNIX_TIMESTAMP(time) as time_sec, temp as "10min avg" FROM monitoring_10min WHERE $__timeFilter(time) AND hostname = 'j52' ORDER BY time SELECT UNIX_TIMESTAMP(time) as time_sec, temp as "1hour avg" FROM monitoring_1hour WHERE $__timeFilter(time) AND hostname = 'j52' ORDER BY time
発展課題
蒸し暑さを表す指標として「不快指数」がある. この指数を計算してデータベースへの登録・グラフ表示を行ってみよ. すでにテーブル monitoring_10min などには, 不快指数を格納するためのカラム (didx, didx2, didx3) が存在する.
不快指数 = 0.81 * 温度 + 0.01 * 湿度 * (0.99 * 温度 - 14.3) + 46.3
- 興味に応じて統計量を計算してそれをデータベースに格納・表示してみよ.