2024 年度 OSS リテラシ 3 : 欠損値処理
はじめに
本資料では，データ処理の基礎として，欠損値処理について取り扱う．
欠損値処理
何らかの理由でセンサーのネットワークが切れると, その時間帯のデータは取得できない = データが欠損する, ということが生じる.
データが欠損した場合，それを明示的に教えないと，描画ツール (grafana など) は 以下のように欠損部分をつないだ不自然な線グラフを作ってしまう. データが無いはずなのにデータが存在しているように見えてしまうため， データ処理上大変まずい.
データに欠損値がある場合には, その時刻に NULL や取り得ない数字 (999 など)を入れておくのが一般的である. grafana はデフォルトで NULL は欠損値として扱うので, 今回はデータ欠損が生じた時刻の温度を NULL にすることにする． 欠損値を正しく設定すれば, 以下の図のように欠損値の部分は折れ線でつながれなくなる.
準備
データ欠損が生じた時刻の温度に NULL を入れるために Ruby からデータベース操作することにする． そのために必要なパッケージをインストールする.
$ sudo -s # apt-get update # apt-get install ruby-mysql2 ruby-activesupport
作業を行う前に，念の為に自分のデータベースをバックアップしておく．
# mysqldump -u root -p iotex > backup.sql
バックアップファイルを見ると，データベース iotex の全データが含まれていることが分かる．
# lv backup.sql
次に，サンプルのテーブルを作成するためのダンプファイル (sample2024.sql) を 取得し，それを MariaDB のデータベース iotex に登録する．このダンプファイルを 用いることでテーブル「sample2024」がデータベース iotex 内に作成される． この sample2024 テーブルには教員のマイコンで取得したデータが数時間分含まれている．
# wget https://www.gfd-dennou.org/arch/iotex/oss/2024/sample2024.sql # mysql -u root -p iotex < sample2024.sql
管理者権限から一般ユーザ権限に戻る．
# exit $
Ruby スクリプトの作成
以下のようなスクリプト (~/check_miss.rb) を作成する． XXXXX となっている部分は自分で考えて穴埋めしてほしい．
データベースへの接続情報の置き場の指定 (変数 conf) については， 前期のデータベースの資料 を参照されたい．
このスクリプトは，データベースから特定のホストについて 観測時間を全て取り出し，既定の秒数 (delt) 間隔に時刻が存在するか確認し， もし当該時刻が無い場合には，その時刻・ホスト名・温度 (NULL)， をテーブルに入力している．
操作対象となるテーブルは sample2024 と j4oss3 の 2 つである． 一度に両テーブルを操作するのではなく，後述の課題で行うように， テーブル名を都度変更することを勧める．
なお，データベース (j4oss3) でプライマリキーを指定通りに時刻とホスト名に していれば，既存の時刻のデータを上書きすることは無い．
#!/usr/bin/env ruby
# coding: utf-8
require 'yaml'
require 'mysql2'
require 'active_support/time'
###
### 注意
###
# 本スクリプトは等間隔に各データの時刻の秒数が同じであることを前提としている．
# 例えば以下のようなコマンドで，データが揃っているか確認すること．
#
# > select time from j4oss3 where time not like '%XX'; (XX には末尾の秒数を入れる)
#
# もし揃っていないものがあれば，それは削除する．テストで入れたデータと思われるので．
#
# > create table j4oss3bk as select * from j4oss3; (テーブルのバックアップを取る)
# > delete from j4oss3 where time not like '%XX'; (揃っていない場合は当該データを削除)
###
### 変数宣言
###
table = "XXXXX" # テーブル名 (sample2024, j4oss3 の 2 通り)
name = "XXXXX" # マイコンの名前 (sample2024 の場合は sugiyama)
delt = 60 # 時間間隔 [秒]
###
### データベース
###
conf = "XXXXXXXXXXXXX" # データベースへの接続情報の置き場.
mydb = YAML.load_file( conf ) # 設定ファイルの読み込み
# データベースへの接続
client = Mysql2::Client.new(
:host => "#{mydb["SERV"]}",
:username => "#{mydb["USER"]}",
:password => "#{mydb["PASS"]}",
:database => "#{mydb["DBNM"]}"
)
###
### データベースより時刻情報を取り出す
###
time_list = Array.new # データベースから取り出した時刻を入力する配列を用意
# SQL 文の作成
# 変数 name, table で設定したセンサのデータを取り出す．取り出す対象のカラムは time のみで良い．時刻の昇順に並べる．
sql = "SELECT time XXXXXX #{table} XXXXXXXXX #{name} XXXXXXXX"
# SQL を実行して得られた時刻を配列に入れる
client.query(sql).each do |item|
if item["time"].present?
time_list.push( Time.parse( item["time"].to_s ) ) # 時刻のフォーマットに変換するために Time.parse を用いる.
end
end
###
### データベース操作
### 変数 delt に設定された時刻毎にデータが入っているか確認する．
### もし存在しない場合には，その時刻を欠損値とする．
###
time = time_list[0] #最初の時刻
time_end = time_list[-1] #最後の時刻
while ( time < time_end ) do
if time_list.include?( time ) #時刻が含まれていなければ
puts "#{time} is OK"
else
# テーブルに時刻・ホスト名・温度 (NULL)・IP (NULL) を入れる
# 時刻の指定 → '#{time.strftime("%Y-%m-%d %H:%M:%S")}'
sql = "XXXX XXXXX #{table} (time, name, temp) values (XXXXXXX, #{name}, XXXXXX )"
puts "#{sql}"
client.query(sql)
end
time += delt # 60 秒進める (1 分間隔で計測しているため)
end
スクリプトの実行 (1)
配布したダンプファイル (sample2024.sql) によって作られた テーブル sample2024 (name は sugiyama) について操作する．
このテーブルは，以下のコマンドを実行すれば分かる通り， 一部データが欠損している．
# sudo mysql -u root -p iotex -e "select * from sample2024 where time > '2024-11-12 14:20:00' and time < '2024-11-12 17:20:00' order by time asc" Enter password: +----------+---------------------+---------+---------------+ | name | time | temp | ip | +----------+---------------------+---------+---------------+ | sugiyama | 2024-11-12 14:21:00 | 28.5893 | 160.18.136.29 | | sugiyama | 2024-11-12 14:22:00 | 28.783 | 160.18.136.29 | | sugiyama | 2024-11-12 14:23:00 | 28.6538 | 160.18.136.29 | | sugiyama | 2024-11-12 14:24:00 | 28.2352 | 160.18.136.29 | | sugiyama | 2024-11-12 14:25:00 | 28.3638 | 160.18.136.29 | | sugiyama | 2024-11-12 14:26:00 | 28.3638 | 160.18.136.29 | | sugiyama | 2024-11-12 14:27:00 | 28.2352 | 160.18.136.29 | | sugiyama | 2024-11-12 14:28:00 | 28.3638 | 160.18.136.29 | | sugiyama | 2024-11-12 14:29:00 | 28.396 | 160.18.136.29 | <--- | sugiyama | 2024-11-12 17:14:00 | 27.3387 | 160.18.136.29 | <--- | sugiyama | 2024-11-12 17:15:00 | 27.1793 | 160.18.136.29 | | sugiyama | 2024-11-12 17:16:00 | 27.2112 | 160.18.136.29 | | sugiyama | 2024-11-12 17:17:00 | 27.243 | 160.18.136.29 | | sugiyama | 2024-11-12 17:18:00 | 27.0838 | 160.18.136.29 | | sugiyama | 2024-11-12 17:19:00 | 27.1156 | 160.18.136.29 | +----------+---------------------+---------+---------------+
作成したスクリプトを実行する．
$ ruby ~/check_miss.rb
スクリプトの実行後，上記と同じ SQL コマンドを実行し，欠損値処理ができていることを確認すること．
# sudo mysql -u root -p iotex -e "select * from sample2024 where time > '2024-11-12 14:20:00' and time < '2024-11-12 17:20:00' order by time asc" Enter password: +----------+---------------------+---------+---------------+ | name | time | temp | ip | +----------+---------------------+---------+---------------+ | sugiyama | 2024-11-12 14:21:00 | 28.5893 | 160.18.136.29 | | sugiyama | 2024-11-12 14:22:00 | 28.783 | 160.18.136.29 | | sugiyama | 2024-11-12 14:23:00 | 28.6538 | 160.18.136.29 | | sugiyama | 2024-11-12 14:24:00 | 28.2352 | 160.18.136.29 | | sugiyama | 2024-11-12 14:25:00 | 28.3638 | 160.18.136.29 | | sugiyama | 2024-11-12 14:26:00 | 28.3638 | 160.18.136.29 | | sugiyama | 2024-11-12 14:27:00 | 28.2352 | 160.18.136.29 | | sugiyama | 2024-11-12 14:28:00 | 28.3638 | 160.18.136.29 | | sugiyama | 2024-11-12 14:29:00 | 28.396 | 160.18.136.29 | | sugiyama | 2024-11-12 14:30:00 | NULL | NULL | | sugiyama | 2024-11-12 14:31:00 | NULL | NULL | | sugiyama | 2024-11-12 14:32:00 | NULL | NULL | | sugiyama | 2024-11-12 14:33:00 | NULL | NULL | | sugiyama | 2024-11-12 14:34:00 | NULL | NULL | | sugiyama | 2024-11-12 14:35:00 | NULL | NULL | | sugiyama | 2024-11-12 14:36:00 | NULL | NULL | ......(以下略).....
スクリプトの実行 (2)
先に作成したスクリプト check_miss.rb 内の変数 name, table を編集して， 自分の観測データの入っているテーブル j4oss3 について同様に欠損値処理を行う．
スクリプトを実行する前に，テストで入れたデータが残っていないか確認し， 残っていればそれを削除する．データ送信時には，学生番号の末尾 2 桁の数 字で送信のタイミングを決めたはずなので，その秒数以外が混ざっていないか 確認する．以下の例では XX としてるが，ここは送信のタイミング (学生番号 の末尾 2 桁) に変更すること．
$ sudo mysql -u root -p iotex -e "select time from j4oss3 where time not like '%XX'"
もしテストデータが混ざっていたなら，テーブルのバックアップを取ってから， 不要なデータを削除する．
$ sudo mysql -u root -p iotex -e "create table j4oss3bk as select * from j4oss3; $ sudo mysql -u root -p iotex -e "delete from j4oss3 where time not like '%XX'";
その後，check_miss.rb 内の変数 name, table を自分のものに修正してから スクリプトを実行し，欠損値処理がなされていることを確認すること．
$ ruby ~/check_miss.rb
自動実行
動作確認が終わったら，コマンドの自動実行の設定を行う．
定期的にコマンドを実行する場合には cron コマンドを用いる． 以下のように書くと，毎時 0 分に特定のコマンドを実行することができる．
$ crontab -e (末尾に追加) 0 * * * * ruby ~/check_miss.rb