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

2023-02-15Bash,CentOS,Linux,Ubuntu

はじめに

以下のエントリの続き。

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

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

検証環境

$ 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)"

$ 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 ファイルを 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. 出生率
  6. 性比
  7. ?

必要なカラム情報のみを出力 ( select cN )

7 列目がよくわからないので、除外したい。
q コマンドでは、各列名は暗黙的に 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 件を表示 ( limit )

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 件を表示 ( order by )

出生数（ 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,977242
2014,1003609
2015,1005721
2013,1029817
2012,1037232
2011,1050807
2005,1062530
2009,1070036
2010,1071305
2007,1089818

最近はめっきり少子化。

データの集計値を表示 ( count / sum / avg )

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

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

出生率（ 5 列目 ）の最大、最小を表示してみます。

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

最大と最小の値が大きく異なりますね。

平均も出せる。

$ q -d , "select avg(c2) from mb010000-utf8.csv"
1666749.7130434783

データの加工（ substr ）

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,9984947
201x,7175733

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

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

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

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

データの絞り込み（ where ）

2005 年以降のデータのみを確認することができます。

$ q -d , "select * from mb010000-utf8.csv where c1 >= 2005"
2005,1062530,545032,517498,8.4,105.3,1.26
2006,1092674,560439,532235,8.7,105.3,1.32
2007,1089818,559847,529971,8.6,105.6,1.34
2008,1091156,559513,531643,8.7,105.2,1.37
2009,1070036,548994,521042,8.5,105.4,1.37
2010,1071305,550743,520562,8.5,105.8,1.39
2011,1050807,538271,512536,8.3,105.0,1.39
2012,1037232,531781,505451,8.2,105.2,1.41
2013,1029817,527657,502160,8.2,105.1,1.43
2014,1003609,515572,488037,8.0,105.6,1.42
2015,1005721,515468,490253,8.0,105.1,1.45
2016,977242,502012,475230,7.8,105.6,1.44

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

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

$ 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,...
...(省略)...

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

カンマ区切りのままだと数値の桁数によって、データが不揃いになり見にくいときがあります。

-b オプションを付与すると、多少きれいに調整し出力してくれます。

# -sオプションで区切り文字を指定
$ q -d , -b "select * from mb010000-utf8.csv where c1 >= 2005"
2005,1062530,545032,517498,8.4,105.3,1.26
2006,1092674,560439,532235,8.7,105.3,1.32
2007,1089818,559847,529971,8.6,105.6,1.34
2008,1091156,559513,531643,8.7,105.2,1.37
2009,1070036,548994,521042,8.5,105.4,1.37
2010,1071305,550743,520562,8.5,105.8,1.39
2011,1050807,538271,512536,8.3,105.0,1.39
2012,1037232,531781,505451,8.2,105.2,1.41
2013,1029817,527657,502160,8.2,105.1,1.43
2014,1003609,515572,488037,8.0,105.6,1.42
2015,1005721,515468,490253,8.0,105.1,1.45
2016,977242 ,502012,475230,7.8,105.6,1.44    ### **気持ち、揃っているのがわかりますか？

出力形式をタブ区切りに変更すると更に見やすくなります。

-T オプションを付与します。

# q -d , -T "select * from mb010000-utf8.csv where c1 >= 2005"
2005    1062530 545032  517498  8.4     105.3   1.26
2006    1092674 560439  532235  8.7     105.3   1.32
2007    1089818 559847  529971  8.6     105.6   1.34
2008    1091156 559513  531643  8.7     105.2   1.37
2009    1070036 548994  521042  8.5     105.4   1.37
2010    1071305 550743  520562  8.5     105.8   1.39
2011    1050807 538271  512536  8.3     105.0   1.39
2012    1037232 531781  505451  8.2     105.2   1.41
2013    1029817 527657  502160  8.2     105.1   1.43
2014    1003609 515572  488037  8.0     105.6   1.42
2015    1005721 515468  490253  8.0     105.1   1.45
2016    977242  502012  475230  7.8     105.6   1.44

(おまけ)カンマを含むデータ、ダブルクォートを含むデータの取扱い

カンマを含んだデータの取り扱いにも触れておきましょう。

CSV フォーマットでは、カンマ (,) やダブルクォーテーション(")を含むデータを扱うときには注意が必要です。

  • カンマ (,)を含んだ列はダブルクォート(")で囲む必要があります
  • ダブルクォーテーション(")を含んだ列はダブルクォートを連続で2つ("")記述する必要があります

上記のようなデータを q コマンドでは正しく扱うことができるでしょうか？
試してみます。

# test.csv というファイルを作成し、データにはカンマやダブルクォートを含めておきます
$ cat test.csv
1,2
3,4
5,"6,666"
7,"\"8\",8"
9,"10""10""10"

# 2列目を出力してみる
$ q -d , "select c2 from test.csv"
2
4
"6,666"
"""8"",8"
"10""10""10"

カンマを含んだデータも正しく取り扱うことができました。

不要なダブルクォートを除外し、実際のデータを確認したい場合には -W none オプションを付与します。

$ q -d , -W none "select c2 from test.csv"
2
4
6,666
"8",8
10"10"10

ひとこと

CSV操作機能だけでも便利ですが、SQLで操作できるという素晴らしさ。

2023-02-15Bash,CentOS,Linux,Ubuntu