10年戦えるデータ分析入門　SQLを武器にデータ活用時代を生き抜く (Informatics ＆IDEA)

はじめに

手元に大きめのCSVファイルがあり、

そのデータを少し解析したい場合は、

Excelを使うか、PythonのPandasで解析することが多いのですが、

myenigma.hatenablog.com

ちょっとSQLを使いたいなぐらいですと、

Excelだと結構たいへんですし、

Pandasだと少し面倒に感じることがありました、

今回は、下記のTweetで知った、

組み込みのDBであるsqliteのCLIツールを使って、

シェル上で簡単にSQLを使って、CSVファイルのデータ解析をする

手法を紹介したいと思います。

手元のCSVファイルに対して、ワンライナーでsqliteとSQLで集計するのめっちゃ便利そう。 https://t.co/TdBAcnU12k
— Atsushi Sakai (@Atsushi_twi) 2022年6月22日

事前セットアップ

sqlite3のCLIは下記のリンクを参考にインストールしておきます。

www.sqlite.org

myenigma.hatenablog.com

また、手元に解析したいCSVを用意しておきます。

今回の記事では、有名なデータセットであるタイタニックの乗員のCSVファイルを使います。

github.com

加えて、今回のデータ解析手法を使うために、

bashrcに下記の関数を設定しておきます。

# sqlite3 based CSV analaysis command
sqlcsv() {
  if [ $# -lt 2 ]
  then
    echo "USAGE: sqlcsv [filename.csv] [SQL]"
    echo "In the SQL, refer to the data sourse as [filename]"
  else
    filename="$1"
    dataname=${filename%????}
    sqlite3 :memory: -cmd '.mode csv' -cmd ".import $filename $dataname" -cmd '.mode markdown' -cmd "$2"
  fi
}

こちらの関数は下記を参考にしました。

Wrapper for using SQLite on CSV files · GitHub

データ解析レシピ

5行表示する

とりあえず、sqliteに読み込んで5行表示する。ちょっと長いけど、シェルのヒストリーから編集すれば、許容範囲な気がする。 pic.twitter.com/9BOr8LFL8M
— Atsushi Sakai (@Atsushi_twi) 2022年7月2日

Desktop $ sqlite3 -cmd '.mode csv' -cmd '.import titanic.csv titanic' -cmd '.mode markdown' -cmd 'SELECT * FROM titanic LIMIT 5';
| PassengerId | Survived | Pclass |                        Name                         |  Sex   | Age | SibSp | Parch |      Ticket      |  Fare   | Cabin | Embarked |
|-------------|----------|--------|-----------------------------------------------------|--------|-----|-------|-------|------------------|---------|-------|----------|
| 1           | 0        | 3      | Braund, Mr. Owen Harris                             | male   | 22  | 1     | 0     | A/5 21171        | 7.25    |       | S        |
| 2           | 1        | 1      | Cumings, Mrs. John Bradley (Florence Briggs Thayer) | female | 38  | 1     | 0     | PC 17599         | 71.2833 | C85   | C        |
| 3           | 1        | 3      | Heikkinen, Miss. Laina                              | female | 26  | 0     | 0     | STON/O2. 3101282 | 7.925   |       | S        |
| 4           | 1        | 1      | Futrelle, Mrs. Jacques Heath (Lily May Peel)        | female | 35  | 1     | 0     | 113803           | 53.1    | C123  | S        |
| 5           | 0        | 3      | Allen, Mr. William Henry                            | male   | 35  | 0     | 0     | 373450           | 8.05    |       | S        |
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> ^D

表示する行を指定

行を指定して表示。やっぱりSQLはこのへんは便利だな。ただ、毎回sqliteのモードに入ってしまうのはつらい。表示したあと、exitして、自動的にシェルに戻ってくれば、すごく使いやすいんだけど。。。 pic.twitter.com/mYKNIFy08c
— Atsushi Sakai (@Atsushi_twi) 2022年7月2日

Desktop $ sqlite3 -cmd '.mode csv' -cmd '.import titanic.csv titanic' -cmd '.mode markdown' -cmd 'SELECT PassengerId, Survived, Sex, Age FROM titanic LIMIT 5';
| PassengerId | Survived |  Sex   | Age |
|-------------|----------|--------|-----|
| 1           | 0        | male   | 22  |
| 2           | 1        | female | 38  |
| 3           | 1        | female | 26  |
| 4           | 1        | female | 35  |
| 5           | 0        | male   | 35  |

htmlやjson, SQLとして出力

htmlで表示。 pic.twitter.com/pGwSSuJe7N
— Atsushi Sakai (@Atsushi_twi) 2022年7月2日

Desktop $ sqlite3 -cmd '.mode csv' -cmd '.import titanic.csv titanic' -cmd '.mode html' -cmd 'SELECT PassengerId, Survived, Sex, Age FROM titanic LIMIT 5';
<TR><TD>1</TD>
<TD>0</TD>
<TD>male</TD>
<TD>22</TD>
</TR>
<TR><TD>2</TD>
<TD>1</TD>
<TD>female</TD>
<TD>38</TD>
</TR>
<TR><TD>3</TD>
<TD>1</TD>
<TD>female</TD>
<TD>26</TD>
</TR>
<TR><TD>4</TD>
<TD>1</TD>
<TD>female</TD>
<TD>35</TD>
</TR>
<TR><TD>5</TD>
<TD>0</TD>
<TD>male</TD>
<TD>35</TD>
</TR>

JSONやSQLのinsert文として出力できるのも便利そう： pic.twitter.com/ee3XawXZ09
— Atsushi Sakai (@Atsushi_twi) 2022年7月2日

Desktop $ sqlite3 -cmd '.mode csv' -cmd '.import titanic.csv titanic' -cmd '.mode json' -cmd 'SELECT PassengerId, Survived, Sex, Age FROM titanic LIMIT 5';
[{"PassengerId":"1","Survived":"0","Sex":"male","Age":"22"},
{"PassengerId":"2","Survived":"1","Sex":"female","Age":"38"},
{"PassengerId":"3","Survived":"1","Sex":"female","Age":"26"},
{"PassengerId":"4","Survived":"1","Sex":"female","Age":"35"},
{"PassengerId":"5","Survived":"0","Sex":"male","Age":"35"}]

Desktop $ sqlite3 -cmd '.mode csv' -cmd '.import titanic.csv titanic' -cmd '.mode insert' -cmd 'SELECT PassengerId, Survived, Sex, Age FROM titanic LIMIT 5';
INSERT INTO "table" VALUES('1','0','male','22');
INSERT INTO "table" VALUES('2','1','female','38');
INSERT INTO "table" VALUES('3','1','female','26');
INSERT INTO "table" VALUES('4','1','female','35');
INSERT INTO "table" VALUES('5','0','male','35');

総行数を数える

Desktop $ sqlite3 -cmd '.mode csv' -cmd '.import titanic.csv titanic' -cmd 'SELECT COUNT(*) FROM titanic';
891

GROUP BYで同じデータの数を数えたり、平均を取る

ああ、やっぱりSQLはGROUP BYする場合は最高だ。。
CSVでちょっとGROUP BYしたい場合はこれが一番らくかもしれない。下記はタイタニックのデータで生存者と死亡者の数を出している。 pic.twitter.com/Ug9t8Nmeb4
— Atsushi Sakai (@Atsushi_twi) 2022年7月2日

なるほど、0が死亡で1が生存者なのだが、死亡者と生存者の平均年齢は変わらないが、平均料金が生存者の方が高いので、お金持ちの方が生き残ったのか。。。なんとなくそうだろうなと思ったけど。。こんな簡単な解析でもワンライナーでできる、そうSQLiteならね。 pic.twitter.com/ezubdj7bax
— Atsushi Sakai (@Atsushi_twi) 2022年7月2日

Desktop $ sqlite3 -cmd '.mode csv' -cmd '.import titanic.csv titanic' -cmd '.mode markdown' -cmd 'SELECT Survived, COUNT(Survived), AVG(Age) FROM titanic GROUP BY Survived';
| Survived | COUNT(Survived) |     AVG(Age)     |
|----------|-----------------|------------------|
| 0        | 549             | 23.6530054644809 |
| 1        | 342             | 24.0341228070175 |

複数列でのGroup byと統計計算

こうゆう複数条件でのGROUP BYって、Excelだと結構めんどくさいんだけど、SQLだと一撃だな。この解析結果も面白い。男性の死亡者数が多いが、そもそも料金の低い部屋に女性がそんなに居なかったのかもしれない。そして死亡した女性はみんな若いな。。。 pic.twitter.com/7Ow6Ydvq3b
— Atsushi Sakai (@Atsushi_twi) 2022年7月2日

Desktop $ sqlite3 -cmd '.mode csv' -cmd '.import titanic.csv titanic' -cmd '.mode markdown' -cmd 'SELECT Survived, Sex, COUNT(Survived), AVG(Age), AVG(Fare) FROM titanic GROUP BY Survived, Sex';
| Survived |  Sex   | COUNT(Survived) |     AVG(Age)     |    AVG(Fare)     |
|----------|--------|-----------------|------------------|------------------|
| 0        | female | 81              | 19.7901234567901 | 23.0243851851852 |
| 0        | male   | 468             | 24.3215811965812 | 21.9609929487179 |
| 1        | female | 233             | 24.3905579399142 | 51.9385733905579 |
| 1        | male   | 109             | 23.2722018348624 | 40.8214844036697 |

シェル関数を使って、コマンドの簡易化

記事冒頭のシェル関数の使うことでコマンドの簡易化が可能です。

おお、bashrcにちょっとしたコマンドを書いたら、最高に使いやすくなったぞ😃 これは今後常用できそう。 pic.twitter.com/rn3wwcQCv7
— Atsushi Sakai (@Atsushi_twi) 2022年7月3日

Desktop $ sqlcsv titanic.csv "SELECT Survived, Sex, Pclass, COUNT(Survived), AVG(Age), AVG(Fare) FROM titanic GROUP BY Survived, Sex, Pclass"
| Survived |  Sex   | Pclass | COUNT(Survived) |     AVG(Age)     |    AVG(Fare)     |
|----------|--------|--------|-----------------|------------------|------------------|
| 0        | female | 1      | 3               | 25.6666666666667 | 110.604166666667 |
| 0        | female | 2      | 6               | 36.0             | 18.25            |
| 0        | female | 3      | 72              | 18.1944444444444 | 19.7730930555555 |
| 0        | male   | 1      | 77              | 35.3181818181818 | 62.8949103896104 |
| 0        | male   | 2      | 91              | 30.8021978021978 | 19.4889648351648 |
| 0        | male   | 3      | 300             | 19.5333333333333 | 12.2044693333333 |
| 1        | female | 1      | 91              | 31.4835164835165 | 105.978159340659 |
| 1        | female | 2      | 70              | 27.2785714285714 | 22.2889885714286 |
| 1        | female | 3      | 72              | 12.6180555555556 | 12.4645263888889 |
| 1        | male   | 1      | 45              | 32.2204444444444 | 74.63732         |
| 1        | male   | 2      | 17              | 14.1370588235294 | 21.0951          |
| 1        | male   | 3      | 47              | 18.0089361702128 | 15.5796957446809 |