はじめに
車の故障診断コネクターにOBD2アダプターを取り付けて、車で走行するごとにその時の走行距離、走行時間、ガソリン消費量推定値などを記録しているのですが、そこそこの走行回数のデータが集まってきたのでヒストグラムにまとめてみたくなりました。マイクロソフト・エクセルなどの表計算ソフトを使ってもいいのですが、データの更新があるたびにワークシートを開いたり、「FREQUENCY関数を入力したら"Enterキー"ではなく、"Ctrlキー + Shiftキー + Enterキー"を同時に押す」とか、この為だけの操作を覚えるのも面倒ですよね．．．。ブラウザでファイルを選択してボタンを押したら度数分布表を作ってくれたりヒストグラムにしてくれないかな、ということで、Node.js、Express、MySQLとChart.jsの組み合わせで作成してみました。
Visual Studio Codeのインストール
ブラウザで操作できるようなものを作る＝JavaScriptかな(?)さらに、エディターはVisual Studio Code(VS-code)が便利そう、ということでインストールしました。VS-Codeもいろんなサイトで紹介されていますが、以下のサイトを参考にしました。
Node.js、Expressのインストール
データベースを操作するにはサーバーサイドでJavaScriptを作動させるNode.jsを使うらしい、ということで「Node.jsをインストールする」を参考にインストールしました。途中に出てくる「Automatically install the necessary tools. ...」のチェックも、必要になった時に対応すれば良く、今はしなくていいらしいです。ちょっと迷いました。
さらに、サーバーサイドのリクエストの内容の解析はExpressを使うと開発効率がよいそうですので、活用させていただきます。Node.js、Expressとも、「ゼロからはじめるExpress + Node.jsを使ったアプリ開発」を参考にさせていただいてインストールしました。
VS-Codeの「表示」メニューの中にある「ターミナル」を選択すると、ウィンドウの下部にターミナルが表示され、ここではWindowsコマンドが使えます。適切なディレクトリを作成し、移動します。
mkdir test001
cd test001
npm init
npm initで質問が返ってきますが、とりあえずは全てリターンでよさそうです。すべてリターンならnpm init -yでも良かったようです。
ローカルのcsvファイルを読み込む
「Express.js ファイルアップロード (multer 編)」によると、'multer'という'Express.js ミドルウェア'を使うとよさそうですので、これを使ってみます。
npm install express
npm install multer
操作イメージとしては、ブラウザでローカルホストにアクセスすると、読み込みファイルの選択画面が表示され、ファイルを選択すると読み込む、という基本的な動きの実現を目指します。
app.jsの内容
まず、VS-Codeのエクスプローラー画面から、今回作ったディレクトリに新しいファイルを作ります。「ファイル」メニューから「新しいファイル...」か、エクスプローラー画面でディレクトリを選択して右クリックでもよさそうです。app.jsという名前にすることが多そうなのでそれに従いました。
処理内容としては、最初に、expressとmulterを使う準備をしています。その次に、「読み込みファイルの選択画面」を用意したいのですが、「【express】テンプレートファイルを使ってViewを作成する」等によると「views」ディレクトリ内にhtml等で記述したファイルを置くことがデフォルトのようなので、「views」ディレクトリを作成し、そこにindex.htmlというファイルを作成しました。ひょっとするとデフォルトなので指定はいらないのかもしれませんが、index.htmlファイルを置いた場所を、app.use(express.static('views'));で指定しておきます。index.htmlの中身は後で用意します。
次に、app.get()の第１引数に「/」を指定して、トップページを開いたときにどうするかを指定します。sendFile()メソッドでindex.htmlを指定すると、ブラウザで http://localhost:3000/にアクセスした時にindex.htmlの内容が表示されます。3000は最後のapp.listen(3000);の3000と同じで、今回はこのポート番号を使うことにしました。
さらに、multer({dest: 'uploaded'})のdest:で指定されたディレクトリが読み込んだファイルの格納場所を指定しています。最初に作ったディレクトリ(ここではtest001)の中にその名前のディレクトリが作られます。
index.htmlには読み込むファイルを指定する処理を記述し、どのファイルにするかを知らせるボタンを設定しますが、そのボタンを押したときにどう処理するか、はapp.post('/upload', ...)の第２引数に記述します。ここでは、upload.single('filename')がファイルのアップロード処理です。dest:で指定されたディレクトリに、自動でつけられたファイル名でファイルが作られます。
const express = require('express')
const multer = require('multer')
const app = express()
app.use(express.static('views'));
app.get('/', function (req, res) {
res.sendFile('index.html');
});
const upload = multer({dest: 'uploaded'})
app.post('/upload', upload.single('filename'), (req, res) => {
res.send(req.file.originalname + 'ファイルのアップロードが完了しました。')}
);
app.listen(3000);
index.htmlの内容
index.htmlはHTMLの<form>タグによくある形だそうで、「ファイルを選択」でファイルを選択し、「送信」ボタンを押すと<form>タグのactionに指定された送信先、ここでは「'/upload'」に選択したファイルのファイル名が送信されます。
<!DOCTYPE html>
<html lang="ja"></html>
<head>
<meta charset="UTF-8">
<title>File Upload</title>
</head>
<body>
<form action="/upload" method="POST" enctype="multipart/form-data">
<input type="file" name="filename">
<button type="submit">送信</button>
</form>
</body>
</html>
動作確認
node app.js
でサーバーを起動し、ブラウザで http://localhost:3000/にアクセスします。画面はGoogle Chrome バージョン: 110.0.5481.178（Official Build）です。
ファイルを選択し、「送信」ボタンを押すとtest001ディレクトリの下に「uploaded」というディレクトリが生成され、そこに自動でつけられたであろうファイル名のファイルが作られ、クリックすると先程のcsvファイルの内容が読み込まれていました。
戸惑った点
index.htmlの<form>タグのactionで指定した"/upload"とapp.post()の第１引数の'/upload'と、index.htmlの<form>タグの<input>タグにあるname="filename"の"filename"とapp.post()の第２引数のupload.single('filename')の'filename'はそれぞれ一致させるのがポイントのようで、色んなサイトからコピーして持ってくると、この組み合わせが微妙に違って上手くいかなかったりしたので要注意。
csvファイルの内容を配列に格納
アップロードしたcsvファイルをデータベースに格納するには、csvファイルの内容を読み出し、その内容に応じてSQL文を作って実行する必要があります。その前に、まずはcsvファイルの内容を、「fs」モジュールで読み出し、配列に格納します。
fs.readFile(ファイルのパス, 文字コード, コールバック関数)
ということなので、ファイルのパスはreq.file.pathで先程アップロードしたファイルのパスを指定し、文字コードはutf8、ファイルを読み込んだあとに実行したいコールバック関数として、改行コードでデータを１行ごとに区切り、さらに１行分のデータをカンマで区切り、最初の行を項目名、２行目からをデータとして２次元配列として格納する処理を記述しました。app.post('/upload', ...)を以下のように変更しています。ちなみに、fsモジュールNode.jsがはじめから提供しているモジュールなので、Node.jsのインストールがしてあれば、fsモジュールのインストールの必要はないそうです。
...
app.post('/upload', upload.single('filename'), (req, res) => {
fs.readFile(req.file.path,'utf8', function(err, data) {
const LF = String.fromCharCode(10); // 改行コード (LF)
const lines = data.split(LF); // 改行コード単位で分割
// タイトル行
const headers = lines[0].replace('\r', '').split(",");
const tableData = [];
for (let i = 1; i < lines.length;++i) {
const cells = lines[i].replace('\r', '').split(",");
if(cells.length === headers.length) tableData.push(cells);
};
console.log('columnNames: ', columnNames);
console.log('tableData: ', tableData);
});
res.send(req.file.originalname + 'ファイルのアップロードが完了しました。');
});
用意したcsvファイルです。見やすくするため表計算ソフトで読み込んだ画面ですが、実際は','区切りのcsvファイルです。1行目は何の値か、を書いたタイトル行で、以下、ある時刻でのそれらの値が時間の経過とともに下に向けて並んでいるような形式です。
コンソールで確認すると、上手く格納できているようです。
columnNames: [
'Start_date',
'End_date',
'Time_duration_h',
...
'FuelEconomy_km_L',
'totalDistance_km',
'totalFuelConsumption_L',
'battery_V'
]
tableData: [
[
'2xxx/xx/10 08:27:17',
'2xxx/xx/10 08:53:10.667',
'0.431541944444444',
...
'7.13976237351054',
'154.04555',
'21.129303',
'12.3'
],
[
'2xxx/xx/10 17:34:36',
'2xxx/xx/10 17:37:04.238',
'0.0409602777777778',
...
'4.56783305830288',
'154.63397',
'21.258118',
'13.8'
],
...
MySQLを使って度数分布表を作成
インストールと起動
マイクロソフト・エクセルを使わない、かつ、データが増えてもその都度の操作をなるべく少なくしたい、ということでデータベース化することにしました。Windowsでローカルで使えるデータベースもいろいろあるようですが、MySQLにしました。MySQL 8 が最新ですが、5.7とインストールの様子がちょっと違うようで、多少困惑するところがあって確認したら5.7の記事だった、ということがありました。8 について書かれたサイトを参考にインストール、Windowsサービスに登録して起動しました。ここまでは、以下のようなサイトを参考にしました。
MySQL接続ドライバーのインストール
express.jsからMySQLへ接続するためには、MySQLへ接続するためのドライバをインストールする必要があります、ということでこれもインストール
npm install mysql
データベースへの接続ですが、以下のように、createConnectionメソッドでhost、user、パスワードを指定。パスワードはMySQLインストール時に設定した管理者パスワードです。
const mysql = require('mysql');
const connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : 'password'
});
mysql community 8.0.30.0では「Node.jsでMySQL 8.0へ接続しようとする時に発生するエラー」が出ましたので、コマンドプロンプトからMySQLに接続して、
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'
と唱えました。ただし、このままコピペしてしまうとパスワードが password になってしまうので要注意！
MySQLの使い方
MySQLの使い方は「MySQLの使い方」を参考にしました。以下、まず、データベースを作成し、その中にテーブルを作成、csvファイルから読み込んだデータを格納し、グループごとに集計することで度数分布表を作ります。
データベース作成
データベースは、CREATE DATABASE 文 で作ります。作ったデータベースを USE 文で選択しておきます。データベース名は'my_db'としました。CREATE DATABASE 文は IF NOT EXISTS を付けました。
//データベース作成
const DB_NAME = 'my_db';
connection.query('CREATE DATABASE IF NOT EXISTS ' + DB_NAME, (err) => {
if (err) throw err;
});
//データベース選択
connection.query('USE ' + DB_NAME);
テーブルの作成
次に、csvファイルの項目名(カラム名)と同じ項目名(カラム名)を持つテーブルを作ります。CREATE TABLE [IF NOT EXISTS] tbl_name(col_name data_type, ...)というように指定するそうなので、まず、テーブル名が必要です。とりあえず、csvファイル名の拡張子を除いた部分をテーブル名にしました。拡張子を外すメソッドが見つからなかったので、'.'区切りで一番右の部位を除き、残りを再度'.'でつないだものからさらに'-'記号があると面倒なので'_'に置き換えました。他にもSQL文にすると配慮が必要な文字があるかもしれませんがそれは見つけ次第対応したいと思います。
const basename = req.file.originalname.split('.').slice(0, -1).join('.').replace(/-/g,'_');
const tableName = basename.replace(' ','_');
その次に、カラム名、型、とならんだテキスト文が必要です。さらに、今回は走行開始日時がないデータは省きたい、かつ、重複もないはずなので、NOT NULL制約と、プライマリーキーの設定もつけたいと思います。今回のデータは、走行開始日時の'Start_date'、走行終了日時の'End_date'、その差の走行時間'Time duration_h'その他のデータで構成されています。走行時間'Time duration_h'は単位を時間とし、例えば走行時間30分なら0.5(時間)とします。'Start_date'、'End_date'は時間なのでデータ型は DATETIME 型、それ以外のデータは FLOAT 型にしました。なので、csvファイル名と１行目(タイトル行)の項目名から、
CREATE TABLE IF NOT EXISTS csvファイル名(Start_date datetime not null primary key, End_date datetime, XXX float, ...
という文を作る必要があります。テーブル名を文字列で第1引数に、項目名を配列で第2引数に渡すとSQL文を文字列として返す関数を作りました。
function createTableStr(tableName, columnNames){
let str = 'CREATE TABLE ' + tableName + '(';
// console.log('columnNames.length = ' + length);
for(let i = 0; i < columnNames.length; i++){
if(i > 0) str += ',';
const col = columnNames[i];
if (col == 'Start_date') {
str = str + col + ' datetime not null primary key';
} else if (col == 'End_date') {
str = str + col + ' datetime';
} else {
str = str + col + ' float';
}
}
str += ');';
console.log('createTableStr: ', str);
return(str);
};
これをapp.post('/upload', ...)の中で呼び出し、queryメソッドで実行します。今回は、同じ名前のテーブルがあった場合は一旦削除する処理を追加しています。
app.post('/upload', upload.single('filename'), (req, res) => {
fs.readFile(req.file.path,'utf8', function(err, data) {
const LF = String.fromCharCode(10); // 改行コード (LF)
const lines = data.split(LF); // 改行コード単位で分割
// タイトル行
const headers = lines[0].replace('\r', '').split(",");
const basename = req.file.originalname.split('.').slice(0, -1).join('.').replace(/-/g,'_');
const tableName = basename.replace(' ','_');
connection.query('DROP TABLE IF EXISTS ' + tableName + ';', function (err) {
if (err) throw err;
});
const createsql = createTableStr(tableName, headers);
connection.query(createsql, function (err) {
if (err) throw err;
});
});
});
データの格納
テーブルにデータを追加していくのはINSERT文ですが、一気に追加できるそうなので、やってみます。
INSERT INTO tbl_name (col_name1, col_name2, ...) VALUES (value1, value2, ...), (value1, value2, ...), ...
というように書けばいいようなので、こちらも読み込んだcsvファイルから生成できるようにしました。テーブル名を文字列で第1引数に、項目名を配列で第2引数に、データを2次元配列で渡すと、データを一気に追加するSQL分を返します。データは、ある時刻の走行距離、スピードなどの各項目の値が行方向に並んでいて、それが各時刻ごとに列方向に並んでいる、とします。datetime型の２つのデータは'(カンマ)で囲まないとうまくいかなかったので、2次元配列状のデータの第0列と第1列は場所固定で'(カンマ)で囲う、としました。もっとほかのパターンが出てきたらその時に、より一般的な条件を考えます。また、値がないときは0を入れています。
function insertStr(tableName, columnNames, tableData){
let str = 'insert ignore into ' + tableName + '(';
for(let i = 0; i < columnNames.length; i++){
if(i > 0) str += ',';
str += columnNames[i];
}
str += ')';
for (i = 0; i < tableData.length; i++) {
str = i == 0 ? str + " values (" : str + ",(";
const row = tableData[i];
for (let j = 0;j < row.length; j++){
if(j > 0) str += ',';
if (j == 0 || j == 1) // datetime型なので
str += ('\'' + row[j] + '\'');
else{
if(row[j].length == 0)
str += '0'; // float は 0. とか、0.0はダメらしい
else
str += row[j];
}
}
str += ')';
}
console.log('insertStr: ', str);
return(str);
};
これもqueryメソッドで実行します。
app.post('/upload', upload.single('filename'), (req, res) => {
fs.readFile(req.file.path,'utf8', function(err, data) {
const LF = String.fromCharCode(10); // 改行コード (LF)
const lines = data.split(LF); // 改行コード単位で分割
// タイトル行
const headers = lines[0].replace('\r', '').split(",");
const basename = req.file.originalname.split('.').slice(0, -1).join('.').replace(/-/g,'_');
const tableName = basename.replace(' ','_');
connection.query('DROP TABLE IF EXISTS ' + tableName + ';', function (err) {
if (err) throw err;
});
const createsql = createTableStr(tableName, headers);
connection.query(createsql, function (err) {
if (err) throw err;
});
const tableData = [];
for (let i = 1; i < lines.length;++i) {
const cells = lines[i].replace('\r', '').split(",");
if(cells.length === headers.length) tableData.push(cells);
};
const insertsql = insertStr(tableName, headers,tableData);
connection.query(insertsql, (err) => {
if (err) throw err;
});
});
});
燃費を度数分布表でまとめる
走行ごとの燃費値で度数分布表をつくります。燃費値は、「FuelEconomy_km_L」という名前の列にある、という想定です。
次に、あるルールに従ってグルーピングする、GROUP BY という条件設定の仕方を使いました。度数分布表の階級の幅をいくつにするかは重要ですが、ここでは2km/L刻みに階級を設定したいと思います。DIV 2とすることで、燃費値を2で割った値の整数部分(整数除算)でグルーピングし、その値が同じデータが何個あるか？という検索の仕方です。さらに、整数除算値 * 2 の値を階級を表す名前として、class という名前を付けて取得します。さらに、classの値で ORDER BY しているので、2km/L 〇〇回、4km/L △△回、という感じで、階級値が小さいものから並んで取得できると期待します。
SELECT (FuelEconomy_km_L DIV 2) * 2 AS class, COUNT(*) AS counts from テーブル名 GROUP BY FuelEconomy_km_L DIV 2 ORDER BY class;
これもapp.post('/upload', ...)の中で呼び出し、queryメソッドで実行します。
app.post('/upload', upload.single('filename'), (req, res) => {
fs.readFile(req.file.path,'utf8', function(err, data) {
const LF = String.fromCharCode(10); // 改行コード (LF)
const lines = data.split(LF); // 改行コード単位で分割
// タイトル行
const headers = lines[0].replace('\r', '').split(",");
const basename = req.file.originalname.split('.').slice(0, -1).join('.').replace(/-/g,'_');
const tableName = basename.replace(' ','_');
connection.query('DROP TABLE IF EXISTS ' + tableName + ';', function (err) {
if (err) throw err;
});
const createsql = createTableStr(tableName, headers);
connection.query(createsql, function (err) {
if (err) throw err;
});
const tableData = [];
for (let i = 1; i < lines.length;++i) {
const cells = lines[i].replace('\r', '').split(",");
if(cells.length === headers.length) tableData.push(cells);
};
const insertsql = insertStr(tableName, headers,tableData);
connection.query(insertsql, (err) => {
if (err) throw err;
});
const selectsql = 'select (FuelEconomy_km_L DIV 2) * 2 AS class,'
+ 'COUNT(*) AS counts from ' + tableName
+ ' group by FuelEconomy_km_L DIV 2 order by class;';
connection.query(selectsql, (err, rows, fields) => {
if (err) throw err;
rows.forEach( row => {
console.log('row : ', row );
});
});
});
});
console.log('row : ', row );の出力を確認すると、おおよそ期待通りです。
row : RowDataPacket { class: 2, counts: 1 }
row : RowDataPacket { class: 4, counts: 12 }
row : RowDataPacket { class: 6, counts: 9 }
row : RowDataPacket { class: 8, counts: 6 }
row : RowDataPacket { class: 10, counts: 1 }
row : RowDataPacket { class: 12, counts: 7 }
row : RowDataPacket { class: 14, counts: 4 }
row : RowDataPacket { class: 20, counts: 1 }
今回の走行では、16km/Lから20km/Lまでのデータがなかったので、その部分はclassも生成されていません。FuelEconomy_km_L DIV 2でclassを作っているので、元々データがないclassは作られないようで、そういう場合は別に欲しい階級(軸)のテーブルをあらかじめ作成しておき、そのテーブルと集約したデータをJOINするなどの対策が必要そうです。今回は全てSQLで処理する必要もなく、JavaScriptでグラフを書くページにデータを送信できればいいので、JavaScript側に予め欲しい階級の配列(連想配列)を作っておいて、SQL出力のclassをキーを使って値をコピーすることにしました。
0km/Lから48km/Lまで、先ほどと同じ2km/L刻みの連想配列を作り、SQLで求めた値(度数)を階級の値をキーに、該当する階級に入れます。
app.post('/upload', upload.single('filename'), (req, res) => {
fs.readFile(req.file.path,'utf8', function(err, data) {
const LF = String.fromCharCode(10); // 改行コード (LF)
const lines = data.split(LF); // 改行コード単位で分割
// タイトル行
const headers = lines[0].replace('\r', '').split(",");
const basename = req.file.originalname.split('.').slice(0, -1).join('.').replace(/-/g,'_');
const tableName = basename.replace(' ','_');
connection.query('DROP TABLE IF EXISTS ' + tableName + ';', function (err) {
if (err) throw err;
});
const createsql = createTableStr(tableName, headers);
connection.query(createsql, function (err) {
if (err) throw err;
});
const tableData = [];
for (let i = 1; i < lines.length;++i) {
const cells = lines[i].replace('\r', '').split(",");
if(cells.length === headers.length) tableData.push(cells);
};
const insertsql = insertStr(tableName, headers,tableData);
connection.query(insertsql, (err) => {
if (err) throw err;
});
const selectsql = 'select (FuelEconomy_km_L DIV 2) * 2 AS class,'
+ 'COUNT(*) AS counts from ' + tableName
+ ' group by FuelEconomy_km_L DIV 2 order by class;';
connection.query(selectsql, (err, rows, fields) => {
if (err) throw err;
let classData = {};
for(let i = 0; i < 50; i += 2){
classData[i] = 0
}
rows.forEach( row => {
classData[row.class] = row.counts;
});
});
});
});
console.logの結果は
{
'0': 0,
'2': 1,
'4': 12,
'6': 9,
'8': 6,
'10': 1,
'12': 7,
'14': 4,
'16': 0,
'18': 0,
'20': 1,
'22': 0,
'24': 0,
...(以下略)
となり、データがゼロの階級も含めた度数分布表のもとになるデータが出来ました。
pugを使って度数分布表を表示
度数分布表のもとになるデータが出来たので、これをブラウザで表形式で表示したいと思います。htmlだと<table>タグで表を作成できるようですが、pugではeach文が使用できるということでpugを使ってみます。
npm install pug
viewsディレクトリにhistogram.pugという名前のファイルを作りました。
html
head
title #{title}
link(rel="stylesheet" href="css/style.css")
meta(charset='utf-8')
title TEST
meta(name='viewport' content='width=device-width,initial-scale=1.0')
body
h1 #{table_title}
table
thead
tr
each element in column_names
th #{element}
tbody
each line in tableDatas
tr
each element in line
td #{element}
最後のeach element in lineでlineの行数分、表の行を追加してくれる、というところが便利なところだそうです。テーブルのタイトルをtable_title、項目名をcolumn_names、データをtableDatasという名前でapp.jsから渡します。
先ほど、表にしたいデータは連想配列を使いましたが、表にするときは[ [階級, 頻度],...]という形で渡す方が扱いやすそうなので、渡す前に２次元配列に変換しました。さらに、今回のデータは2km/Lごとに区切った燃費のヒストグラムなので、ヘッダー行に'燃費[km/L]'、'度数'という見出しを入れ、また、階級は整数除算×階級の幅、で作ったので、該当する階級の最小値が階級名になっています。そこで、〇〇km/Lの階級には、分かりやすく"〇〇km/L～"と表示するようにしました。
データを渡すときは、res.renderメソッドを使いました。histogram.pugに、ページのタイトルは「燃費ヒストグラム」、テーブルのタイトルとしてtableName、データはclassDataForHistgramという名前を付けた配列で渡しています。
ここまでの処理を、app.post('/upload', ...)に追加しました。
また、pugを使いたいので'view engine'に'pug'を指定しています。最終的なapp.jsを示しますが、ちょっと長くなりました。
const express = require('express')
const multer = require('multer')
const fs = require('fs');
const mysql = require('mysql');
const DB_NAME = 'my_db';
//mysqlクライアント作成
const connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : 'password',
});
//データベース作成作成
connection.query('CREATE DATABASE IF NOT EXISTS ' + DB_NAME, function(err) {
if (err) {
throw err;
}
});
//データベース選択
connection.query('USE ' + DB_NAME);
const app = express()
app.use(express.static('views'));
app.set('view engine', 'pug');
app.get('/', function (req, res) {
res.sendFile('index.html');
});
const upload = multer({dest: 'uploaded'})
app.post('/upload', upload.single('filename'), (req, res) => {
fs.readFile(req.file.path,'utf8', function(err, data) {
const LF = String.fromCharCode(10); // 改行コード (LF)
const lines = data.split(LF); // 改行コード単位で分割
// タイトル行
const headers = lines[0].replace('\r', '').split(",");
const basename = req.file.originalname.split('.').slice(0, -1).join('.').replace(/-/g,'_');
const tableName = basename.replace(' ','_');
connection.query('DROP TABLE IF EXISTS ' + tableName + ';', function (err) {
if (err) throw err;
});
const createsql = createTableStr(tableName, headers);
connection.query(createsql, function (err) {
if (err) throw err;
});
const tableData = [];
for (let i = 1; i < lines.length;++i) {
const cells = lines[i].replace('\r', '').split(",");
if(cells.length === headers.length) tableData.push(cells);
};
const insertsql = insertStr(tableName, headers,tableData);
connection.query(insertsql, (err) => {
if (err) throw err;
});
const selectsql = 'select (FuelEconomy_km_L DIV 2) * 2 AS class,'
+ 'COUNT(*) AS counts from ' + tableName
+ ' group by FuelEconomy_km_L DIV 2 order by class;';
connection.query(selectsql, (err, rows, fields) => {
if (err) throw err;
let classData = {};
for(let i = 0; i < 50; i += 2){
classData[i] = 0
}
rows.forEach( row => {
classData[row.class] = row.counts;
});
const columnNames = ['燃費[km/L]','度数'];
classDataForHistgram = []
const keys = Object.keys(classData)
keys.forEach(key => {
classDataForHistgram.push([key + '~',classData[key]])
})
res.render('histogram.pug', { title: '燃費ヒストグラム',
table_title : tableName,
column_names : columnNames,
tableDatas: classDataForHistgram
});
});
});
});
function createTableStr(tableName, columnNames){
let str = 'CREATE TABLE IF NOT EXISTS ' + tableName + '(';
for(let i = 0; i < columnNames.length; i++){
if(i > 0) str += ',';
const col = columnNames[i];
if (col == 'Start_date') {
str = str + col + ' datetime not null primary key';
} else if (col == 'End_date') {
str = str + col + ' datetime';
} else {
str = str + col + ' float';
}
}
str += ');';
return(str);
};
function insertStr(tableName, columnNames, tableData){
let str = 'insert ignore into ' + tableName + '(';
for(let i = 0; i < columnNames.length; i++){
if(i > 0) str += ',';
str += columnNames[i];
}
str += ')';
for (i = 0; i < tableData.length; i++) {
str = i == 0 ? str + " values (" : str + ",(";
const row = tableData[i];
for (let j = 0;j < row.length; j++){
if(j > 0) str += ',';
if (j == 0 || j == 1) // datetime型なので
str += ('\'' + row[j] + '\'');
else{
if(row[j].length == 0)
str += '0'; // float は 0. とか、0.0はダメらしい
else
str += row[j];
}
}
str += ')';
}
return(str);
};
app.listen(3000);
cssファイルも一応、簡単に作りました。viewsディレクトリにcssディレクトリを作り、そこにstyle.cssというファイルを作りました。
th,
td {
border: solid 1px;
padding-left: 10px;
padding-right: 10px;
width: 200px;
}
table {
border-collapse: collapse; /* セルの線を重ねる */
}
.no_border{
border: 0;
}
p {
margin: 0.1em 0px;
}
td {
text-align: center;
}
.row{
display:flex;
column-gap: 10px;
}
できた度数分布表はこんな感じです。
Chart.jsを使ってヒストグラムを表示
ヒストグラムを作成するのには Chart.js が良さそうだったので使ってみました。
「Getting Started | Chart.jsには、CDN(コンテンツ・デリバリー・ネットワーク)を使った方法が最初に記載されていましたので、その方法で試しました。
度数分布表の横に並べたかったので、枠線なしのテーブルタグを使いました。ヒストグラムを書く場所をcanvasで確保し、mychartというidを付けておきます。
html
head
title #{title}
link(rel="stylesheet" href="css/style.css")
meta(charset='utf-8')
title TEST
meta(name='viewport' content='width=device-width,initial-scale=1.0')
body
h1 #{table_title}
table.no_border
td.no_border
table
thead
tr
each element in column_names
th #{element}
tbody
each line in tableDatas
tr
each element in line
td #{element}
td.no_border
div(style='width:800px')
canvas#mychart
mychartにどんなグラフを書くかはscriptタグをつけてJavaScriptで指定します。テーブルのデータをscriptで括られた領域に持っていくのに、'カスタムデータ属性'というものを使いました。mydataと名付けた要素にdata-〇〇とつければ良いそうなので、data-tableとしてデータを入れた配列をセットしました。取り出すときは、script内で、mydata.dataset.〇〇と指定すればいいそうです。
div#mydata(data-table=tableDatas)
script.
const ctx = document.getElementById('mychart');
const mydata = document.getElementById('mydata');
// Javascript：カスタムデータ属性で配列（実は文字列）の受け渡し(https://imuza.com/regexp/)
const dataString = mydata.dataset.table;
alert("dataString:" + dataString);
「Javascript：カスタムデータ属性で配列（実は文字列）の受け渡し」にも書かれていましたが、script側では文字列として送られてくるそうなので、alertを追加して確認してみると、
dataString:[["0~",0],["2~",1],["4~",12],["6~",9],["8~",6],["10~",1],["12~",7],["14~",4],["16~",0],["18~",0],["20~",1],["22~",0],["24~",0],["26~",0],["28~",0],["30~",0],["32~",0],["34~",0],["36~",0],["38~",0],["40~",0],["42~",0],["44~",0],["46~",0],["48~",0]]
というように文字列として受け取っているようですので、ここから階級名と値を抽出します。単純に','で区切ると'0~'と0、'2~'、1もばらばらになってしまうので、],[で区切り、そうすると先頭と最後の要素に[[もしくは]]が残るのでそれも削除し、階級の文字列から""(ダブルクォーテーション)も削除しました。その後、階級の方をclassNameという配列に、頻度の方をclassValueに格納しました。この辺りはもう少し、きれいに書けるかもしれません。
classNameの方を、Chartインスタンスのlabelsに、頻度データをdatasetsのdataに設定すると、棒グラフが表示されます。ヒストグラムらしく隣り合ったバーが接するようにcategoryPercentage: 1.0,barPercentage: 1.0としました。
さらに、ヒストグラムのy軸は、「頻度」だと思いますが、x軸の方はラベル名を度数分布表の項目名にしたいので、カスタムデータ属性で渡すようにしました。
最終的にはhistogram.pugは以下のようになりました。
html
head
title #{title}
link(rel="stylesheet" href="css/style.css")
meta(charset='utf-8')
title TEST
meta(name='viewport' content='width=device-width,initial-scale=1.0')
//- Chart.jsライブラリの読み込み
script(src="https://cdn.jsdelivr.net/npm/chart.js")
body
h1 #{table_title}
table.no_border
td.no_border
table
thead
tr
each element in column_names
th #{element}
tbody
each line in tableDatas
tr
each element in line
td #{element}
td.no_border
div(style='width:800px')
canvas#mychart
div#mydata(data-table=tableDatas)
div#mycolumn_x_title(title=column_names[0])
script.
const ctx = document.getElementById('mychart');
const mydata = document.getElementById('mydata');
// Javascript：カスタムデータ属性で配列（実は文字列）の受け渡し(https://imuza.com/regexp/)
const dataString = mydata.dataset.table;
const dataSplits = dataString.split('],[');
const tableData = [];
dataSplits.map(element => {
tableData.push(element.replace(/(\[\[)|(\]\])/,'').replace(/['"]/g,'').split(','));
});
const className = [];
const classValue = [];
tableData.map(line => {
className.push(line[0]);
classValue.push(line[1]);
});
const x_title = document.getElementById('mycolumn_x_title').title;
const myChart = new Chart(ctx, {
type: 'bar',
data: {
labels: className,
datasets: [{
label: '頻度',
categoryPercentage: 1.0,
barPercentage: 1.0,
data: classValue,
backgroundColor: '#48f',
}],
},
options:{
plugins:{
legend:{
display: false // 凡例はいらない
}
},
scales: {
x:{
display: true,
title:{
display: true,
font:{
size: 16 // 軸タイトルのフォントサイズ
},
text: x_title
},
ticks:{
display: true,
font:{
size: 16 // 目盛りのフォントサイズ
}
}
},
y:{
display: true,
title:{
display: true,
font:{
size: 16
},
text: '頻度'
},
ticks:{
display: true,
font:{
size: 16
}
}
}
}
}
});
出来たヒストグラムはこんな感じです。Node.js、Express、MySQLとChart.jsの組み合わせでcsvファイルから度数分布表とヒストグラムを作成し、ブラウザで表示することが出来ました。
参考記事