awkでvlookupみたいな表結合させる
はじめに
特にawkは使い始めたばかりなので、awkを使ってワンラインでexcelのvlookup的なことをやりたいときにどうすればよいのかわからなくて、excelで頑張ってたりしてました。
でもエンジニアならば、excelなんて使わなくてもコマンドラインでぱぱっと操作できたほうが格好いいじゃない?と思ったので挑戦してみます。
目標
以下の2つの表を想定する。
ユーザ一覧を格納した表(users.csv)
id name -- ---- 1 一郎 2 二郎 3 三郎 4 四郎
何かしらの条件をみたしたIDの表(condition.csv)
id -- 2 4
この２つの表から以下を得たい。
ゴール
id name condition -- ---- ---- 1 一郎 F 2 二郎 T 3 三郎 F 4 四郎 T
conditionにデータがあれば、usersの3列名にTを立て、なければFを立てたいわけです。
sqlでいえば、
SELECT id , name , CASE WHEN condition.id IS NULL THEN 'F' ELSE 'T' END FROM users LEFT JOIN CONDITION ON users.id = CONDITION.id
みたいな操作を実行したいのだ。
回答
awk 'FNR==NR{a[$1]++; next} {print $0, (a[$1]) ? "T" : "F"}' condition.csv users.csv
ね、簡単だね!
説明
なぜ上記コマンドになるのかを説明しようと思う。
FNR==NR{a[$1]; next}
FNR==NRは条件を表現しており、{}内はその条件がマッチしたときの処理を意味する。
よって、FNR==NR, {}内に分けて説明する。
1. FNR==NRとについて
2つのファイルを読み込んで、それぞれNR,FNRと両者の行を出力すると、以下となる。
awk '{print NR, FNR, $0}' condition.csv users.csv
NR FNR $0 -- --- -- 1 1 2 2 2 4 3 1 1 一郎 4 2 2 二郎 5 3 3 三郎 6 4 4 四郎
NRは2つのファイルをまとめて何行目を処理しているかを FNRは各ファイルの何行目を処理しているかを表している。
よって、はじめのファイル(condition.csv)が処理中のときは、FNR==NRが真となる。
2. {a[$1]++; next;}
突然登場した a は変数である。var, constなど使わずともいきなり変数を定義できるのである。さらに[$1]とつづくので、この変数は$1をkey名にもつ連想配列になる。
++は、1値を追加せよ(初期値0)という意味になる。
したがって、aという連想配列を宣言して、$1をkeyとして1増加せよという意味である。
next は、以降つづく処理をストップして次の行に進みなさいという意味である。{a[$1]++; next;} {…1} {…2} {…n}と続いたときに{…1}から{…n}の操作は実行されない。
3. {print $0, (a[$1]) ? "T" : "F"}' condition.csv users.csv
今までのを整理すると、 FNR==NR{a[$1]++; next} {...} は、
- はじめに読み込んだファイルに対してのみ真となり、
- 読み込んだ1列目をkeyとして1追加した連想配列を生成し、
- 次に続く処理{…}を実行しない
という意味になる。
残った {print $0, ....} は、FN==FNRが未達成ときに実行される。つまり、NR >= 3のとき、常に処理される。
$0で、users.csvの処理中の行を表示し、 a[$1]は、users.csvの1行目の値を、連想配列aのkeyに指定したときに真となるかどうかを確認して、TまたはFを出力している。
注意
users.csvとcondition.csvの順番を間違えると意味がなくなるので、注意が必要である。
awk 'FNR==NR{a[$1]++; next} {print $0, (a[$1]) ? "T" : "F"}' users.csv condition.csv
結果は、こちらの通り。理由はもうわかりますよね。
id Flag -- -- 2 T 4 T
このやり方を使うときは、必ずSQLでいうRihgt joinになってしまうわけである。users.csvが4行あるのに対して、conditionは2行しかないのでおかしな結果になってしまう。
もっとつぎへ
先程の使用したconditionで該当するユーザが、2,4だけであることが記載されていた。 しかし、そうでないときもあると思います。
たとえば、bmi.csvが、全ユーザのBMI情報を保存しており、25以上ユーザの名前とBMI情報が知りたいということもあります。
全ユーザとBMI情報が記載された表(bmi.csv)id = 2, 4は
id BMI -- -- 1 22 2 40 3 21 4 30
awk 'FNR==NR{a[$1]=$2; next} {if (a[$1] > 25) print $0, a[$1]}' bmi.csv users.csv
今回のように、bmi.csv, users.csvの行数が同じならば、LEFT JOIN的に記述することも可能となります。ちょっと冗長かな？
awk 'FNR==NR{a[$1]=$2; next} {if ($2 > 25) print $1, a[$1], $2}' users.csv bmi.csv
まとめ
awkをつかって、特定の列をキーに２つの表を結合する方法を整理してみました。
ファイル内容によっては、ファイルの指定順序に気をつけて使ってみてください。
とはいうものの、csvが用意されていたらつかえるわけで、 結局excelやスプレッドシートで渡されるようであれば使えないわけですね。
そのあたりはなんとかできないんかな汗
