joinコマンドは使えるか

csvデータなどテキストベースのデータの塊の処理を必要とする仕事が時々まわってくる。 大抵は、検索系なので、grepしたあと、少し手作業で加工するだけ。しかし、たまに複数のデータファイルを特定のキーで結びつけたりしなければならなくなる。 今までは、リレーショナル・データベースにぶち込んでSQLで処理していた。が、先日なんでもUnixコマンドでやりましょう的な記事を見つけて、大変可能性を感じたので、やってみることにした。

用意したのは、以下の２つのCSVファイル。

やりたいことは、sample-地域.csvの県名とsample-ナンバー.csvの出身地を結びつけ、両方の情報を表示すること。SQLで言うjoin

$ cat sample-地域.csv 
ID,地域,県名,
1001,九州,宮崎,
1002,九州,鹿児島,
1003,九州,福岡,
1004,中国,山口,
,,,
,,,
,,,

$ cat sample-メンバー.csv
ID,名前,性別,歳,出身地,,,,,,
1001,山田,男,50,福岡,,,,,,
1002,田中,女,32,宮崎,,,,,,
1003,中山,女,18,福岡,,,,,,
1004,佐藤,女,19,大分,,,,,,
1005,佐藤,男,48,山口,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
(続く)
,,,,,,,,,,
,,,,,,,,,,

sort , awk, sed そして join

joinコマンドを使うことが今回の目的だ。joinコマンドを使う上で重要なことは、対象ファイルがキーでソートされていること。その下準備のために、sort, awk, sedのコマンドを利用するのだ。

実行したコマンド行は、3行。 実行内容だけ解説します。各コマンドの詳細は helpやmanを見てください。

$ sed -e "/^[,I]/d" sample-メンバー.csv | sort -t , -k 5 | awk -F , '{print $1,$5,$2,$3,$4}' > member.txt
$ sed -e "/^[,I]/d" sample-地域.csv | sort -t , -k 3 | awk -F "," '{print  $1,$3,$2}' > area.txt 
$ join -j 2 area.txt member.txt 

1行目のコマンドライン

sedは、stream editorの略。-eは、"コマンド"を指定するオプション、/^[,I]/d のdは削除(delete)のd、正規表現/^[,I]/に一致する行を削除することを意味する。

sortは、名前の通り並び替え。-t ,でデータが ","で仕切られていることを指定し、-k 5で5番目の項目が並べ替えの基準どすることを指示する。

awkは、万能レコード加工ツールかな。色々出来るらしいので、もはや言語と言って良いかも。{}で囲まれたコマンドを実行する。-F ,はデータのデリミタ(仕切り)が","であることを指定し、{}の中で、項目の並べ替えを行っている。$nが項目のIDだ。 以上のパイプ "|"でつなぎ、仕事をするのが、Unix流だ（そうだ）。

3行目のコマンドライン

3行目のjoinコマンドは、今回のポイント。-j 2は、項目の2番目がジョインするためのキーであることを指定している。キーの指定は、各ファイル個別に出来るが、やはり細かいところはmanを見てください。

要約

1行目で、sample-メンバー.csvファイルを 1) 不要な行を削除し、2) キーでソートし、3) 行毎に整形しspace区切りのファイル member.txtを作る。

2行目で、同様に sample-地域.csvから、area.txtをつくる。

3行目で、2つのファイル area.txt member.txt をジョインする。

作成結果、出力結果は、以下の通り。

CodeRider:sample baker$ cat area.txt 
1001 宮崎 九州
1004 山口 中国
1003 福岡 九州
1002 鹿児島 九州

CodeRider:sample baker$ cat member.txt 
1004 大分 佐藤 女 19
1002 宮崎 田中 女 32
1005 山口 佐藤 男 48
1001 福岡 山田 男 50
1003 福岡 中山 女 18

CodeRider:sample baker$ join -j 2 area.txt member.txt 
宮崎 1001 九州 1002 田中 女 32
山口 1004 中国 1005 佐藤 男 48
福岡 1003 九州 1001 山田 男 50
福岡 1003 九州 1003 中山 女 18

rubyでやってみる

これと、ほぼ同様なことをrubyでやってみた。

総当たりでレコードを調べ、キー値が一致していたら、標準出力に出力すると言う結構乱暴なプログラム。

require 'ostruct'
require 'csv'

area = OpenStruct.new
area.filename = "sample-地域.csv"
area.key = 3 - 1

member = OpenStruct.new
member.filename = "sample-メンバー.csv"
member.key = 5 - 1

CSV::open(area.filename,"r") do |a|
    line_a = a.compact
    next if line_a.size == 0
    CSV::open(member.filename,"r") do |b|
        line_b = b.compact
        next if line_b.size == 0
        $stdout.print [line_a + line_b].join(","), "\n" if a[area.key] === b[member.key]
    end
end

実行結果は、

$ ruby another_one.rb 
1001,九州,宮崎,1002,田中,女,32,宮崎
1003,九州,福岡,1001,山田,男,50,福岡
1003,九州,福岡,1003,中山,女,18,福岡
1004,中国,山口,1005,佐藤,男,48,山口

OpenStructを使ったのは、JavaScriptのように自由にプロパティが設定出来て、整理しやすかったから。(使ってみたかったとかではない :-p)

OpenStructマニュアル(英語)

もっと短くできるかもしれないけど、まあサッとつくるプログラムとしてはこんなもんでよいのでは。

Unixコマンドとrubyとどちらが便利だろうか? また、リレーショナル・データベースにぶち込んでSQLで処理すると言う手もあるが。

(余談) Numbersはすごい

余談だが、サンプルは Appleの表計算ソフト Numbersで作成した。Excelと違って、一寸面白い。表計算ソフトではなく、表プレゼンテーションソフトと言ったほうが良い。 あまり使っていなかったのでビックリした。気づいたことは

1. シートと表は異なる
2. シートは、ExcelのWorksheetとは全く違う。むしろBookにちかい
3. 表には、一つの表を描くのが正しい
4. 表は、シート内で複数配置することが出来、それぞれがExcelのworkseetのような機能をもつ
5. その構造の御陰で、他の表のレイアウト（例えば幅とか）に影響されず、別の表を作成できる
6. シートは、表(オブジェクト)を配置する台紙の役割。これがレポート用紙になる

この構造は、慣れると大変便利だ。Excelで文書を書くのは愚の骨頂だと常々思うが、Numbersならば、ページレイアウトを自然に出来るだろう。

ただ、Excelのように使うと問題ある。今回の場合、CSVへの出力だ。

• 表毎にCSVを出力出来るが、無駄な行や列を作成しておくと、その分 ,, が生成される。(取り除かなければならない)
• 行末の処理が、ちょっとおかしい(行末項目のソートが出来ないなど)。CSVを作成する場合は、行末に,を付けるように出力するか、後で補完したほうが良いようだ。