Linux PostgreSQL Tool

csvkit で CSV ファイルを自在に操る

CSV ファイルを自在に操れるツール csvkit

CSV ファイルは EXCEL で開いてフィルター機能を使ったりしてデータを確認されていると思いますが、 csvkit というツールを使えば自在に CSV ファイルを操ることが出来ます。また CSV ファイルをデータベースに登録したりすることも可能です。

csvkit で出来ること

csvkit 開発元によると、csvkit を使えば以下のようなことが出来ます。

EXCEL 形式のファイルを CSV ファイルに変換
JSON 形式のファイルを CSV ファイルに変換
CSV ファイルの列名だけを抜き出す
CSV ファイルの特定の列データだけを抜き出す
CSV ファイルの列の順番を入れ替える
CSV ファイルの中で条件に一致するデータを抜き出す(正規表現利用可)
圧縮された CSV ファイルを解凍せずに CSV ファイルの中身を操作可能
CSV ファイルに対して SQL を実行
PostgreSQL や MySQL に CSV データをインポート
PostgreSQL や MySQL のデータを CSV ファイルにエクスポート

csvkit をインストール

ここでは Ubuntu で csvkit をインストールする方法を紹介します。Python 言語で書かれたツールですので、Python のパッケージとしてインストールします。

$ sudo apt-get install python-dev python-pip python-setuptools build-essential
$ pip install csvkit

テスト用の EXCEL ファイルを入手

まず csvkit のウェブサイトで EXCEL ファイルを入手することが出来ますので、以下のようにして EXCEL ファイルをダウンロードします。

手順としては、まず csvkit_tutorial というディレクトリを作って、そこに移動して EXCEL ファイルをダウンロードします。

$ mkdir csvkit_tutorial
$ cd csvkit_tutorial

$ curl -L -O https://raw.githubusercontent.com/wireservice/csvkit/master/examples/realdata/ne_1033_data.xlsx

ダウンロードした EXCEL ファイルを、EXCEL で開くと次のようなデータが含まれています。

EXCEL ファイルを CSV 形式に変換する (in2csv)

csvkit にはいくつかサブコマンドが含まれていますが、in2csv というコマンドを使うと以下のように EXCEL ファイルを CSV 形式に変換してくれます。

$ in2csv ne_1033_data.xlsx
state,county,fips,nsn,item_name,quantity,ui,acquisition_cost,total_cost,ship_date,federal_supply_category,federal_supply_category_name,federal_supply_class,federal_supply_class_name
NE,ADAMS,31001.0,1005-00-589-1271,"RIFLE,7.62 MILLIMETER",1.0,Each,138.0,138.0,2008-07-11,10.0,WEAPONS,1005.0,"Guns, through 30 mm"
NE,ADAMS,31001.0,1005-00-589-1271,"RIFLE,7.62 MILLIMETER",1.0,Each,138.0,138.0,2008-07-11,10.0,WEAPONS,1005.0,"Guns, through 30 mm"
NE,ADAMS,31001.0,1005-00-589-1271,"RIFLE,7.62 MILLIMETER",1.0,Each,138.0,138.0,2008-07-11,10.0,WEAPONS,1005.0,"Guns, through 30 mm"
NE,ADAMS,31001.0,1005-00-589-1271,"RIFLE,7.62 MILLIMETER",1.0,Each,138.0,138.0,2008-07-11,10.0,WEAPONS,1005.0,"Guns, through 30 mm"
NE,ADAMS,31001.0,1005-00-589-1271,"RIFLE,7.62 MILLIMETER",1.0,Each,138.0,138.0,2008-07-11,10.0,WEAPONS,1005.0,"Guns, through 30 mm"
NE,ADAMS,31001.0,1005-00-589-1271,"RIFLE,7.62 MILLIMETER",1.0,Each,138.0,138.0,2008-07-11,10.0,WEAPONS,1005.0,"Guns, through 30 mm"
NE,BUFFALO,31019.0,1005-00-073-9421,"RIFLE,5.56 MILLIMETER",1.0,Each,499.0,499.0,2008-09-24,10.0,WEAPONS,1005.0,"Guns, through 30 mm"
NE,BUFFALO,31019.0,1005-00-073-9421,"RIFLE,5.56 MILLIMETER",1.0,Each,499.0,499.0,2008-09-24,10.0,WEAPONS,1005.0,"Guns, through 30 mm"
NE,BUFFALO,31019.0,1005-00-073-9421,"RIFLE,5.56 MILLIMETER",1.0,Each,499.0,499.0,2008-09-24,10.0,WEAPONS,1005.0,"Guns, through 30 mm"
(以降は省略)

もちろん次のようにすれば EXCEL ファイルを CSV 形式に変換したものをファイルに保存することも出来ます。

in2csv ne_1033_data.xlsx > data.csv

なお in2csv コマンドを使えば、入力ファイルとして ".xls" ファイル、".xlsx" ファイル、そして JSON 形式のファイル、固定長のファイル等も処理できます。

CSV ファイルを閲覧する (csvlook)

今度は csvlook というコマンドを使って CSV ファイルを閲覧してみましょう。先のファイルは非常に大きいファイルですので、ここでは下記のような CSV ファイルを用意しました。

$ cat sample.csv
id,name,age
1,foo,99
2,bar,17
3,hoge,29
4,pikachu,65
5,tarou,24

このファイルを csvlook コマンドを使って中身を見てみます。次のようにヘッダ行とデータを区別してデータを整形して表示してくれています。

$ csvlook sample.csv
| id | name    | age |
| -- | ------- | --- |
|  1 | foo     |  99 |
|  2 | bar     |  17 |
|  3 | hoge    |  29 |
|  4 | pikachu |  65 |
|  5 | tarou   |  24 |
|    |         |     |

もし行数の多い CSV ファイルを閲覧する場合には、次のようにするとスクロールが可能になります。

$ csvlook sample.csv | less -S

CSV データを加工する (csvcut)

csvcut というコマンドを使うことによって、CSV ファイル内のデータを加工することが可能です。

まず最初に CSV ファイル内の列名だけを取得してみます。オプションとして "-n" を使います。

$ csvcut -n sample.csv
  1: id
  2: name
  3: age

次に指定した列のみのデータを取得してみます。"-c" オプションを付けて、必要な列番号をカンマ(,)区切りで指定します。下記の例では２列目と３列目のみを指定しています。

$ csvcut -c 2,3 sample.csv
name,age
foo,99
bar,17
hoge,29
pikachu,65
tarou,24

もちろん列番号ではなく、列名を使って必要な列を指定することも以下のように可能です。

$ csvcut -c name,age sample.csv
name,age
foo,99
bar,17
hoge,29
pikachu,65
tarou,24

in2csv と csvcut、csvlook コマンドを組み合わせる

先に説明した in2csv とcsvcut、csvlook コマンドを組み合わせることによって、in2csv コマンドで EXCEL ファイルを読み込み、csvcut コマンドで列を絞り込み、さらに csvlook コマンドで内容を確認し、head コマンドで先頭の１０行のみを表示する、という組み合わせが可能です。

$ in2csv ne_1033_data.xlsx | csvcut -c county,item_name,quantity | csvlook | head
| county     | item_name                | quantity |
| ---------- | ------------------------ | -------- |
| ADAMS      | RIFLE,7.62 MILLIMETER    |        1 |
| ADAMS      | RIFLE,7.62 MILLIMETER    |        1 |
| ADAMS      | RIFLE,7.62 MILLIMETER    |        1 |
| ADAMS      | RIFLE,7.62 MILLIMETER    |        1 |
| ADAMS      | RIFLE,7.62 MILLIMETER    |        1 |
| ADAMS      | RIFLE,7.62 MILLIMETER    |        1 |
| BUFFALO    | RIFLE,5.56 MILLIMETER    |        1 |
| BUFFALO    | RIFLE,5.56 MILLIMETER    |        1 |

csvstat コマンドで CSV ファイルの統計情報を取得する

csvstat コマンドを使うことによって CSV ファイル内のデータの統計情報を取得してくれます。これを使えば敢えてファイルを開いてデータを分析・集計しなくても、頻出値や中央値、標準偏差、合計値等、データ分布が把握できます。また各列のデータ型を推測し数値型なのか日付型なのか、それともテキスト形式なのか等、さらには各列の最小値と最大値も調べてくれます。

ここでは csvcut コマンドを使って county、acquisition_cost、ship_date という３列を CSV ファイルから抜き出した結果を csv stat コマンドに投入してデータを解析してもらっています。

$ csvcut -c county,acquisition_cost,ship_date data.csv | csvstat
  1. "county"

        Type of data:          Text
        Contains null values:  False
        Unique values:         35
        Longest value:         10 characters
        Most common values:    DOUGLAS (760x)
                               DAKOTA (42x)
                               CASS (37x)
                               HALL (23x)
                               LANCASTER (18x)

  2. "acquisition_cost"

        Type of data:          Number
        Contains null values:  False
        Unique values:         75
        Smallest value:        0
        Largest value:         412,000
        Sum:                   5,430,787.55
        Mean:                  5,242.073
        Median:                6,000
        StDev:                 13,368.078
        Most common values:    6,800 (304x)
                               10,747 (195x)
                               6,000 (105x)
                               499 (98x)
                               0 (81x)

  3. "ship_date"

        Type of data:          Date
        Contains null values:  False
        Unique values:         84
        Smallest value:        2006-03-07
        Largest value:         2014-01-30
        Most common values:    2013-04-25 (495x)
                               2013-04-26 (160x)
                               2008-05-20 (28x)
                               2012-04-16 (26x)
                               2006-11-17 (20x)

Row count: 1036

csvgrep コマンドで必要なレコードのみ抜き出す

csvgrep コマンドを使うと、ある条件を満たした列の値を含むレコードのみを取得することが出来ます。

ここでは csvcut コマンドで county、item_name、total_cost という３つの列を CSV ファイルから抜き出し、csvgrep コマンドで country 列に "LANCASTER" という値が含まれるレコードのみを抽出するようにしています。EXCEL のフィルタ機能のようなものです。

$ csvcut -c county,item_name,total_cost data.csv | csvgrep -c county -m LANCASTER
county,item_name,total_cost
LANCASTER,"RIFLE,5.56 MILLIMETER",120.0
LANCASTER,"RIFLE,5.56 MILLIMETER",120.0
LANCASTER,"RIFLE,5.56 MILLIMETER",120.0
LANCASTER,"RIFLE,5.56 MILLIMETER",120.0
LANCASTER,"RIFLE,5.56 MILLIMETER",120.0
LANCASTER,"RIFLE,5.56 MILLIMETER",120.0
LANCASTER,"RIFLE,5.56 MILLIMETER",120.0
LANCASTER,"RIFLE,5.56 MILLIMETER",120.0
LANCASTER,"RIFLE,5.56 MILLIMETER",120.0
LANCASTER,"RIFLE,5.56 MILLIMETER",120.0
LANCASTER,LIGHT ARMORED VEHICLE,0.0
LANCASTER,LIGHT ARMORED VEHICLE,0.0
LANCASTER,LIGHT ARMORED VEHICLE,0.0
LANCASTER,MINE RESISTANT VEHICLE,412000.0
LANCASTER,"IMAGE INTENSIFIER,NIGHT VISION",6800.0
LANCASTER,"IMAGE INTENSIFIER,NIGHT VISION",6800.0
LANCASTER,"IMAGE INTENSIFIER,NIGHT VISION",6800.0
LANCASTER,"IMAGE INTENSIFIER,NIGHT VISION",6800.0

csvsort コマンドで値を並べ替える

csvsort コマンドを使えば CSV ファイルのレコードを並べ替えることが出来ます。

ここでは country、item_name、total_cost 列を CSV ファイルから抜き出し、csvgrep コマンドで country 列に LANCASTER という値が含まれる行を取得します。そして csvsort コマンドで total_cost の降順で並べ替えています。オプションに付けた "-r" コマンドが降順を指示しています。

$ csvcut -c county,item_name,total_cost data.csv | csvgrep -c county -m LANCASTER | csvsort -c total_cost -r | csvlook
| county    | item_name                      | total_cost |
| --------- | ------------------------------ | ---------- |
| LANCASTER | MINE RESISTANT VEHICLE         |    412,000 |
| LANCASTER | IMAGE INTENSIFIER,NIGHT VISION |      6,800 |
| LANCASTER | IMAGE INTENSIFIER,NIGHT VISION |      6,800 |
| LANCASTER | IMAGE INTENSIFIER,NIGHT VISION |      6,800 |
| LANCASTER | IMAGE INTENSIFIER,NIGHT VISION |      6,800 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | LIGHT ARMORED VEHICLE          |          0 |
| LANCASTER | LIGHT ARMORED VEHICLE          |          0 |
| LANCASTER | LIGHT ARMORED VEHICLE          |          0 |

csvjoin コマンドで２つの CSV ファイルを結合する

csvjoin コマンドを使えば SQL で言う JOIN を使えます。これは２つのファイルで共通する列名を指定して、双方で一致するレコードを結合してくれます。

ここでは sample.csv ファイルと sample_address.csv という２つのファイルを結合してみます。共通する列は id となっており、id が一致するレコードを結合して表示してみます。

$ cat /tmp/sample.csv
id,name,age
1,foo,99
2,bar,17
3,hoge,29
4,pikachu,65
5,tarou,24

$ cat sample_address.csv
id,address
1,Tokyo
2,Osaka
3,Kyoto
4,Kanagawa
5,Saitama

$ csvjoin -c id sample.csv sample_address.csv | csvlook
| id | name    | age | address  |
| -- | ------- | --- | -------- |
|  1 | foo     |  99 | Tokyo    |
|  2 | bar     |  17 | Osaka    |
|  3 | hoge    |  29 | Kyoto    |
|  4 | pikachu |  65 | Kanagawa |
|  5 | tarou   |  24 | Saitama  |

圧縮された CSV ファイルを扱う

また csvkit の各コマンドは gzip コマンドや bz2 コマンドで圧縮された CSV ファイルも処理することが出来ます。

例えば以下のように sample.csv ファイルを gzip コマンドで圧縮して sample.gz ファイルを作成し、圧縮されたファイルに対して csvlook コマンドを実行してみます。

$ gzip sample.csv

$ csvlook sample.csv.gz
| id | name    | age |
| -- | ------- | --- |
|  1 | foo     |  99 |
|  2 | bar     |  17 |
|  3 | hoge    |  29 |
|  4 | pikachu |  65 |
|  5 | tarou   |  24 |

圧縮されたファイルでも問題なく CSV ファイルの中身を抽出して表示する際に整形してくれました。

CSV ファイルに対して SQL を実行する

ここまでは csvkit の様々なコマンドを駆使してデータを加工してきましたが、CSV ファイルの対して SQL を実行することも出来ます。

ここでは年齢 (age) が 30 を超えているレコードを SQL の SELECT で抽出してみましょう。問題なく SQL で問い合わせできることがわかります。

$ csvsql --query "SELECT name, age FROM sample WHERE age > 30" sample.csv
name,age
foo,99
pikachu,65

csvkit で PostgreSQL にデータを登録する

CSV データを PostgreSQL に登録したい場合には INSERT コマンドを自力で作成したり、COPY コマンドを使ったり、あるいは PgAdmin3 のようなツールを使っていましたが、csvkit で CSV ファイルを PostgreSQL に簡単に登録できるようになって以来、csvkit しか使わなくなってしました。それほど便利なのです。

では早速 sample.csv というファイルを PostgreSQL に登録してみましょう。なお予めテーブルを作成しておく必要はなく、csvkit が自動的に CSV ファイルに合わせてテーブルを作成してデータを登録 (INSERT) してくれます。

$ csvsql --db postgresql://username:password@127.0.0.1:5432/dbname --insert sample.csv

何も画面に表示されていませんが、PostgreSQL を見ると、自動的に CSV ファイル名と同じテーブル名が作成され、またデータに合わせた列定義になっています。また CSV ファイルのレコードも全て登録されています。

=> \d public.sample
            Table "public.sample"
+--------+----------------------+-----------+
| Column |         Type         | Modifiers |
+--------+----------------------+-----------+
| id     | numeric              | not null  |
| name   | character varying(7) | not null  |
| age    | numeric              | not null  |
+--------+----------------------+-----------+

=> select * from public.sample ;
+----+---------+-----+
| id |  name   | age |
+----+---------+-----+
|  1 | foo     |  99 |
|  2 | bar     |  17 |
|  3 | hoge    |  29 |
|  4 | pikachu |  65 |
|  5 | tarou   |  24 |
+----+---------+-----+
(5 rows)

csvkit で PostgreSQL のテーブルデータを CSV に出力する

今度は逆に PostgreSQL データベースのテーブルのデータを CSV ファイルに出力してみます。問題なくデータが CSV 形式で取得できました。このファイルをリダイレクトしてファイルに出力すれば CSV ファイルを作成できます。

$ sql2csv --db postgresql://username:password@127.0.0.1:5432/dbname --query "SELECT id, name, age FROM public.sample;"
id,name,age
1,foo,99
2,bar,17
3,hoge,29
4,pikachu,65
5,tarou,24

csvkit のさらなる情報源

csvkit の各コマンドを使うことである程度の処理を行うだけでしたら、敢えて MySQL や PostgreSQL 等のデータベースを使う必要はないかと思います。

なお各コマンドには多くのオプションがありますので、さらなる詳しい情報は下記公式サイトでご確認頂ければと思います。

https://csvkit.readthedocs.io/

Everything you do is practice

csvkit で CSV ファイルを自在に操る