データの変換をコマンドラインで!csvkit

概要

コマンドライン使ってますか!
シェル芸人の皆さんは日常茶飯事でしょうけど、そこまで極めることなしに手軽に身近なデータ分析に組み込むことのできるツールをご紹介します。

その名も! csvkit!

github.com

すぐに特定の変換方法が知りたいせっかちなあなたは下記の目次から飛んでください。

テストデータは公式のものを使って試しています。
内容は公式のチュートリアルから抜粋しているだけなので、もっと機能を知りたい方はぜひ公式ドキュメントを参考にして下さい。

インストール

v1.0.2では警告メッセージが大量に出力される不具合が観測されたため、pipからダウンロードすることのできるv1.0.5を使用するようにしましょう。

$ pip install csvkit

データの変換

データの変換にとっても便利なcsvkit
もちろんRやPython、Excelなんかを開いてデータの加工、変換を行ってもよいですが、コマンドラインでできるととっても楽。
データ分析が捗りますね。

Excel → CSV

Excel のデータをCSVに変換するには、in2csvの引数にエクセルファイルを渡します。

$ in2csv ne_1033_data.xlsx > data.csv

出力結果

$ head data.csv
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"

CSV → SQL文

csvsqlでは、-i にデータベースの種類を指定すると、
指定したデータベースにおいてCSVと同様のテーブルを作成するためのコマンドを作成してくれます。

$ csvsql -i sqlite data.csv
CREATE TABLE data (
        state VARCHAR NOT NULL,
        county VARCHAR NOT NULL,
        fips FLOAT NOT NULL,
        nsn VARCHAR NOT NULL,
        item_name VARCHAR,
        quantity FLOAT NOT NULL,
        ui VARCHAR NOT NULL,
        acquisition_cost FLOAT NOT NULL,
        total_cost FLOAT NOT NULL,
        ship_date DATE NOT NULL,
        federal_supply_category FLOAT NOT NULL,
        federal_supply_category_name VARCHAR NOT NULL,
        federal_supply_class FLOAT NOT NULL,
        federal_supply_class_name VARCHAR NOT NULL
);

CSV → RDB

同じく csvsql--insertを使用すると、 CSVファイルを指定してDBにテーブルを追加することができます。

$ csvsql --db sqlite:///leso.db --insert data.csv

RDB → CSV

sql2csvでは、SQLのqueryを使ってRDBからCSVを抽出することができます。

$ sql2csv --db sqlite:///leso.db --query "select * from joined where county='DOUGLAS';" > douglas.csv

JSON → CSV

in2csvを用いると、標準出力で受け取った値をCSVに変換することができます。
下記はgithubのAPIからJSON形式で取得したissue内容をCSVに変換する例です。

$ curl https://api.github.com/repos/wireservice/csvkit/issues?state=open | in2csv -f json -v

データの閲覧

CSVを見やすくわかりやすく表示するためには、csvlookが有用です。
下記のような視認性の高い可視化を行ってくれます。

$ csvlook data.csv | head
| 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      | 31,001 | 1005-00-589-1271 | RIFLE,7.62 MILLIMETER                                          |        1 | Each    |           138.00 |     138.00 | 2008-07-11 |                      10 | WEAPONS        |                1,005 | Guns, through 30 mm                                             |
| NE    | ADAMS      | 31,001 | 1005-00-589-1271 | RIFLE,7.62 MILLIMETER                                          |        1 | Each    |           138.00 |     138.00 | 2008-07-11 |                      10 | WEAPONS        |                1,005 | Guns, through 30 mm                                             |
| NE    | ADAMS      | 31,001 | 1005-00-589-1271 | RIFLE,7.62 MILLIMETER                                          |        1 | Each    |           138.00 |     138.00 | 2008-07-11 |                      10 | WEAPONS        |                1,005 | Guns, through 30 mm                                             |
| NE    | ADAMS      | 31,001 | 1005-00-589-1271 | RIFLE,7.62 MILLIMETER                                          |        1 | Each    |           138.00 |     138.00 | 2008-07-11 |                      10 | WEAPONS        |                1,005 | Guns, through 30 mm                                             |
| NE    | ADAMS      | 31,001 | 1005-00-589-1271 | RIFLE,7.62 MILLIMETER                                          |        1 | Each    |           138.00 |     138.00 | 2008-07-11 |                      10 | WEAPONS        |                1,005 | Guns, through 30 mm                                             |
| NE    | ADAMS      | 31,001 | 1005-00-589-1271 | RIFLE,7.62 MILLIMETER                                          |        1 | Each    |           138.00 |     138.00 | 2008-07-11 |                      10 | WEAPONS        |                1,005 | Guns, through 30 mm                                             |
| NE    | BUFFALO    | 31,019 | 1005-00-073-9421 | RIFLE,5.56 MILLIMETER                                          |        1 | Each    |           499.00 |     499.00 | 2008-09-24 |                      10 | WEAPONS        |                1,005 | Guns, through 30 mm                                             |
| NE    | BUFFALO    | 31,019 | 1005-00-073-9421 | RIFLE,5.56 MILLIMETER                                          |        1 | Each    |           499.00 |     499.00 | 2008-09-24 |                      10 | WEAPONS        |                1,005 | Guns, through 30 mm              

画面サイズによっては折り返しが生じてしまうため、
less -Sにパイプでつなげてあげるのがよいです。

$ csvlook data.csv | less -S

データの抽出

csvcutコマンドを使用すると、データのトリミングを行うことができます。
-nで対象のCSVを指定すると、各列のインデックスが表示されます。

$ csvcut -n data.csv
  1: state
  2: county
  3: fips
  4: nsn
  5: item_name
  6: quantity
  7: ui
  8: acquisition_cost
  9: total_cost
 10: ship_date
 11: federal_supply_category
 12: federal_supply_category_name
 13: federal_supply_class
 14: federal_supply_class_name

-c でインデックスを指定すると、指定したインデックスのカラムだけを抽出することができます。

$ csvcut -c 2,5,6 data.csv | head
county,item_name,quantity
ADAMS,"RIFLE,7.62 MILLIMETER",1.0
ADAMS,"RIFLE,7.62 MILLIMETER",1.0
ADAMS,"RIFLE,7.62 MILLIMETER",1.0
ADAMS,"RIFLE,7.62 MILLIMETER",1.0
ADAMS,"RIFLE,7.62 MILLIMETER",1.0
ADAMS,"RIFLE,7.62 MILLIMETER",1.0
BUFFALO,"RIFLE,5.56 MILLIMETER",1.0
BUFFALO,"RIFLE,5.56 MILLIMETER",1.0
BUFFALO,"RIFLE,5.56 MILLIMETER",1.0

カラム名を指定して抽出することも可能です。

$ csvcut -c county,item_name,quantity data.csv | 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 |

また、csvcut を使用すると、カラムを指定した文字列の検索を行うことができます。 下記は county列において LANCASTERという文字列で検索を行っている例です。

$ csvcut -c county,item_name,total_cost data.csv | csvgrep -c county -m LANCASTER | csvlook
| county    | item_name                      | total_cost |
| --------- | ------------------------------ | ---------- |
| 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 |
| 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 |

データの並び替え

csvsort を使用すると、指定した列の値を使用して行の並び替えを行うことができます。
下記は上記の結果からtotal_cost列の値を使用して並び替えを行っている例です。

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