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

2019-02-20Bash, CentOS, Cygwin, Linux, Ubuntu

以下のエントリの続き。

前回は q コマンドの導入とテストデータ作成( 年次別にみた出生数・率(人口千対)・出生性比及び合計特殊出生率 )まで。

今回は実際に q コマンドでデータを操作してみる。

検証環境

$ uname -moi
x86_64 MacBookPro10,1 Darwin

$ bash -version
GNU bash, バージョン 5.0.2(1)-release (x86_64-apple-darwin18.2.0)
Copyright (C) 2019 Free Software Foundation, Inc.

$ q -version
q version 1.8
Copyright (C) 2012-2017 Harel Ben-Attia (harelba@gmail.com, @harelba on twitter)
http://harelba.github.io/q/

単純なデータの出力

  • CSVファイルをDBのテーブルと見直したクエリを実行することができる。
    • from 句にCSVファイルを指定
    • 今回は各列がカンマ ( , ) で区切られているので、デリミタとして , を指定する。
$ 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,   ...
...(省略)...

ダウンロードしてきたデータの最終列がよくわからない。なぜドット。。。
もともとのデータの1〜9行目を見る限り、列情報の意味は左から以下のようになっている。

  1. 西暦
  2. 総数
  3. 出生率
  4. 性比
  5. ?

必要なカラム情報のみを出力

7列目がよくわからないので、除外したい。
列名は暗黙的に c1 , c2 , c3 … のように cN という名前がつけられている。

$ q -d , "select c1, c2, c3, c4, c5, c6 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
...(省略)...

見やすくなった。

10件を表示

100年間のデータが出力されるので、コンソールが見切れる。
10件だけを表示させてみる。

SQLの limit が使える。

$ q -d , "select c1, c2, c3, c4, c5, c6 from mb010000-utf8.csv limit 10"
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

出生数上位10件を表示

出生数( 2列目 )の10件だけを表示させてみる。

SQLの order by が使える。

$ q -d , "select c1, c2 from mb010000-utf8.csv order by c2 desc limit 10"
1949,2696638
1948,2681624
1947,2678792
1950,2337507
1941,2277283
1943,2253535
1942,2233660
1935,2190704
1932,2182742
1937,2180734

意外にも第二次世界大戦前後の出生率が高い。

逆に下位10件を表示してみる。

$ q -d , "select c1, c2 from mb010000-utf8.csv order by c2 limit 10"
2016,976978
2014,1003539
2015,1005677
2013,1029816
2012,1037231
2011,1050806
2005,1062530
2009,1070035
2010,1071304
2007,1089818

最近はめっきり少子化。

データの集計値を表示

データは結局何件あったっけ?データ件数を確認してみる。

$ q -d , "select count(*) from mb010000-utf8.csv"
115

出生率( 5列目 )の最大、最小を表示してみる。

$ q -d , "select max(c5), min(c5) from mb010000-utf8.csv"
36.2,7.8

全然違う。

10年ごとの出生数の合計を表示してみる。

# 西暦の先頭3文字を切り取り、 `x`と連結
$ q -d , "select substr(c1, 0, 4) || 'x', sum(c2) from mb010000-utf8.csv group by 1 order by 1"
189x,1386981
190x,15181349
191x,17751415
192x,20491682
193x,20838964
194x,16937399
195x,18360218
196x,17072283
197x,18936246
198x,14342192
199x,12046529
200x,11155608
201x,7175351

平均も出せる。

# 西暦の先頭3文字を切り取り、 `x`と連結
$ q -d , "select avg(c2) from mb010000-utf8.csv"
1666749.7130434783

わかりやすいカラム名を指定する

ヘッダー行があれば、ヘッダー行の情報をカラム名として指定できる。

# CSVにヘッダー業を追加
$ sed -i '1i西暦,出生数,男性,女性,出生率,性比,ゴミ' mb010000-utf8.csv

# -Hオプションを追加
$ q -H -d , "select substr(西暦, 0, 4) || 'x', sum(出生数) from mb010000-utf8.csv group by>
189x,1386981
190x,15181349
191x,17751415
192x,20491682
193x,20838964
194x,16937399
195x,18360218
196x,17072283
197x,18936246
198x,14342192
199x,12046529
200x,11155608
201x,7175351

(おまけ)標準入力を食わせる

ファイルではなく標準入力からデータを食わせることもできる。

$ cat mb010000-utf8.csv| q -d , "select * from -"
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,   ...
...(省略)...

※2019-02-13現在の問題か、python3系の僕の環境で試したところエラーが出た。

  • Error('iterator should return strings, not bytes (did you open the file in text mode?)')

(おまけ)表示データを整形して表示

カンマ区切りのままだと行の区切りが見にくい。
column -t コマンドを使って、きれいな表敬式で表示できる。

# -sオプションで区切り文字を指定
$ q -d , "select c1, c2, c3, c4, c5, c6 from mb010000-utf8.csv limit 10" | column -t -s,
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

-s オプションで区切り文字を指定する。カンマ以外( ex: | )にも対応できる。

2019-02-20Bash, CentOS, Cygwin, Linux, Ubuntu