CSV を SQL で扱える 'q' を使って数値データのランク分け芸
大量の CSV ファイルを処理する必要にかられて見つけた q が便利すぎたので SQL の CASE 文と合わせてランク分類をして遊んでみました。
q とは
CSV を SQL で処理できるコマンドラインツールです。 CSV だけでなくタブ区切りの TSV にも対応しているとのこと。
使い方
公式のヘルプに書いてあるとおりではありますが、引数に SQL とファイルを取れば結果が標準出力に出力できます。
q "SELECT * FROM file.csv"-H オプションでヘッダの有無を選べたり、 -d オプションで区切り文字を選べたり、割りと小回りがきく印象。
ヘルプも各オプションの説明が丁寧に書いてあって使いやすい。
今回やったこと
某プロジェクトにて、とあるところから出力された独自形式の （エクセルの）xlsx ファイルにあるデータを条件によってランク分けをしたいとのこと。
q は SQL の CASE 文にも対応しているので楽勝です。
xlsx を CSV に変換
エクセルで作業する用に出力されているデータなので、元データは独自形式の xlsx ファイルでした。
とりあえず xlsx を CSV に変換します。
いろいろ試してまともに動かせたのはこちらのツール。
dilshod/xlsx2csv: Convert xslx to csv, it is fast, and works for huge xlsx files
xlsx2csv $xlsx $csv元データは扱いやすいように見た目が成形され、8 行目から CSV のデータが貼り付けられている形式だったので、7 行削るなど。
sed -i "" -e '1,7d' $csvSQL どばー
q を使ってランク分け。 CASE が使えるの幸せですね。
q -H -d',' -O "select id, foo, bar, baz,
CASE
WHEN foo >= 9 AND bar >= 0.8 THEN 'A'
WHEN foo >= 3 AND bar >= 0.6 THEN 'B'
WHEN foo >= 1 AND bar >= 0.1 THEN 'C'
ELSE 'D'
END as rank
from $csv" > $dst$dst に出力される各行の末尾に rank が追加されます。
もちろん SUM も使えるのでランクごとの集計もこんな感じで。
q -H -d',' "select
SUM(CASE rank WHEN 'A' THEN 1 ELSE 0 END) AS A,
SUM(CASE rank WHEN 'B' THEN 1 ELSE 0 END) AS B,
SUM(CASE rank WHEN 'C' THEN 1 ELSE 0 END) AS C,
SUM(CASE rank WHEN 'D' THEN 1 ELSE 0 END) AS D
from $dst"シェル芸で大量ファイルを一括処理
各処理をコマンド化できたので、あとはスクリプト化してファイルをループ処理。
#!/bin/bash
set -e
set -u
mkdir -p csv
mkdir -p dst
result=result.csv
:> $result
ids=$(ls xlsx/*.xlsx | sed 's;^.*/;;' | sed 's;.xlsx;;')
for id in ${ids[@]}; do
echo $id
src=xlsx/$id.xlsx
csv=csv/$id.csv
dst=dst/$id.csv
xlsx2csv $src $csv
sed -i "" -e '1,5d' $csv
q -H -d',' -O "select id, foo, bar, baz,
CASE
WHEN foo >= 9 AND bar >= 0.8 THEN 'A'
WHEN foo >= 3 AND bar >= 0.6 THEN 'B'
WHEN foo >= 1 AND bar >= 0.1 THEN 'C'
ELSE 'D'
END as rank
from $csv" > $dst
sum=$(q -H -d',' "select
SUM(CASE rank WHEN 'A' THEN 1 ELSE 0 END) AS A,
SUM(CASE rank WHEN 'B' THEN 1 ELSE 0 END) AS B,
SUM(CASE rank WHEN 'C' THEN 1 ELSE 0 END) AS C,
SUM(CASE rank WHEN 'D' THEN 1 ELSE 0 END) AS D
from $dst")
echo $sum
echo -n ${id##*_} | tr -d '\n' >> $result
echo -n ',' >> $result
echo -n $sum >> $result
echo '' >> $result
done以上
元データを CSV で持っていなかったり、条件を指定したデータの出力が叶わなかったりするなど、社会は厳しいことが多いので雑シェル力は大事ですね。