CSVデータに対してSQLのwhere、order by、group by、joinができる `q` コマンド（導入編）

はじめに

年度末が近づいてきて、仕事でも CSV データをこねくり回す機会が増えてきました。（ログデータ集計、デバッグだけでなく、確定申告の金額計算など）

CUI 好きの自分も grep / sed / sort / uniq / join / paste のコマンドを毎回毎回パイプラインでつないでいるのは多少面倒になる。

CSV データを SQL で操作できればいいのに、と思う人も少なくないはずです。僕も SQL でデータ操作するほうがなれている人間のひとりです。

そんな方におすすめなのが、 q コマンド。名前のせいでググラビリティが低いのが難点だがとっても便利。

検証環境

1
2
3
4
5
6
7
$ bash -version | head -n 2
GNU bash, version 5.0.17(1)-release (x86_64-pc-linux-gnu)
Copyright (C) 2019 Free Software Foundation, Inc.

$ cat /etc/os-release | head -n 2
NAME="Ubuntu"
VERSION="20.04.3 LTS (Focal Fossa)"

インストール方法

Github の以下のページを参照。OS ごとにインストール方法が記載されています。

Download and Install

RedHat 系 OS、Debian 系 OS 向けにそれぞれのインストールパッケージも用意されています。

1
2
3
4
5
6
7
$ export VERSION=3.1.6
# curlではなく、wgetでダウンロードしてもいいですし、普通にリンクからダウンロードしてもよい。
$ curl -O -L https://github.com/harelba/q/releases/download/v${VERSION}/q-text-as-data-${VERSION}-1.x86_64.deb

$ apt update

$ apt install -y ./q-text-as-data-${VERSION}-1.x86_64.deb

正しくインストールされたか確認してみます。

1
2
3
4
5
$ q --version
q version 3.1.6
Python: 3.8.11 (default, Jul 24 2021, 23:08:48) // [Clang 12.0.1 ]
Copyright (C) 2012-2021 Harel Ben-Attia (harelba@gmail.com, @harelba on twitter)
http://harelba.github.io/q/

使ってみる

テストデータ

テストデータを用意。以下のページの csv データを借用。

人口動態調査人口動態統計確定数出生年次 2016 年 | ファイルから探す | 統計データを探す | 政府統計の総合窓口

先頭の数行を出力。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
$ head mb010000.csv
28N,lԒ,,
㊪@o@@SDP\@Nʂɂ݂oEiljEoyэvo

@FPjaPX`QPN͎ŝߏȗBaQQ`SVN͉ꌧ܂ȂB
@@@QjaNETNEPON̏ȏɂ́AjsڂeP܂܂B
@@@RjZoɗpl͓{llłB
FЉElulvWvAJȁulԓvv
, o,,, o, o@,v
, @,@ j,@ ,, @,o
1899,1386981,713442,673539,32.0,105.9,@@@...

文字コードが CP932 の csv ファイル？

UTF-8に変換し、少し覗いてみましょう。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$ cat mb010000.csv | iconv -f cp932 -t utf8 | head -n 15

平成28年,人口動態調査,,
上巻　出生　　第４．１表　年次別にみた出生数・率（人口千対）・出生性比及び合計特殊出生率

注　：１）昭和１９～２１年は資料不備のため省略した。昭和２２～４７年は沖縄県を含まない。
　　　２）昭和元年・５年・１０年の出生数の総数には、男女不詳が各１が含まれている。
　　　３）率算出に用いた分母人口は日本人人口である。
資料：国立社会保障・人口問題研究所「人口統計資料集」、厚生労働省「人口動態統計」
※本表の数値は、2004・2006・2009～2017年（平成16・18・21～29年）の都道府県からの報告漏れ（2019年３月29日公表）による再集計後の数値である。
, 出生数,,, 出生率, 出　生,合計特殊
, 総　数,　 男,　 女,, 性　比,出 生 率
root@76dcc633e4ee:/workdir# cat mb010000.csv | iconv -f cp932 -t utf8 | head -n 15
平成28年,人口動態調査,,
上巻　出生　　第４．１表　年次別にみた出生数・率（人口千対）・出生性比及び合計特殊出生率

注　：１）昭和１９～２１年は資料不備のため省略した。昭和２２～４７年は沖縄県を含まない。
　　　２）昭和元年・５年・１０年の出生数の総数には、男女不詳が各１が含まれている。
　　　３）率算出に用いた分母人口は日本人人口である。
資料：国立社会保障・人口問題研究所「人口統計資料集」、厚生労働省「人口動態統計」
※本表の数値は、2004・2006・2009～2017年（平成16・18・21～29年）の都道府県からの報告漏れ（2019年３月29日公表）による再集計後の数値である。
, 出生数,,, 出生率, 出　生,合計特殊
, 総　数,　 男,　 女,, 性　比,出 生 率
1899,1386981,713442,673539,32.0,105.9,　　　...
1900,1420534,727916,692618,32.4,105.1,　　　...
1901,1501591,769494,732097,33.9,105.1,　　　...
1902,1510835,773296,737539,33.6,104.8,　　　...
1903,1489816,763806,726010,32.7,105.2,　　　...

上部の行によくわからないものがたくさん入っています。

データ行はcsv形式となっており、 一番左が西暦を示している ようです。先頭が西暦となっているデータのみを残すように絞り込みます。

同時に「数字」「,」「.」以外は除去しておきます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# sedでゴミ掃除
$ cat mb010000.csv | sed -n "/^[0-9]\{4\},/s/[^0-9,.]//gp" > mb010000-utf8.csv

$ head mb010000-utf8.csv
1899,1386981,713442,673539,32.0,105.9,...
1900,1420534,727916,692618,32.4,105.1,...
1901,1501591,769494,732097,33.9,105.1,...
1902,1510835,773296,737539,33.6,104.8,...
1903,1489816,763806,726010,32.7,105.2,...
1904,1440371,738230,702141,31.2,105.1,...
1905,1452770,735948,716822,31.2,102.7,...
1906,1394295,726155,668140,29.6,108.7,...
1907,1614472,818114,796358,34.0,102.7,...
1908,1662815,850209,812606,34.7,104.6,...

普通にデータ閲覧

早速 SQL を使ってデータを操作してみます。

-d オプションに区切り文字を指定。
- 今回はカンマ( , )を指定。
from 句には csv ファイル名を指定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
$ q -d , "select * from mb010000-utf8.csv"
1899,1386981,713442,673539,32.0,105.9,...
1900,1420534,727916,692618,32.4,105.1,...
1901,1501591,769494,732097,33.9,105.1,...
1902,1510835,773296,737539,33.6,104.8,...
1903,1489816,763806,726010,32.7,105.2,...
1904,1440371,738230,702141,31.2,105.1,...
1905,1452770,735948,716822,31.2,102.7,...
1906,1394295,726155,668140,29.6,108.7,...
1907,1614472,818114,796358,34.0,102.7,...
1908,1662815,850209,812606,34.7,104.6,...
1909,1693850,863855,829995,34.9,104.1,...
1910,1712857,872779,840078,34.8,103.9,...
1911,1747803,891049,856754,35.1,104.0,...
1912,1737674,886449,851225,34.4,104.1,...
1913,1757441,897824,859617,34.3,104.4,...
...

ひとこと

続きは次のエントリでまとめます。

CSV データに対して SQL の where、order by、group by、join ができる q コマンド（活用編）