CSV を SQL で扱える 'q' を使って数値データのランク分け芸

大量の CSV ファイルを処理する必要にかられて見つけた q が便利すぎたので SQL の CASE 文と合わせてランク分類をして遊んでみました。

q とは

CSV を SQL で処理できるコマンドラインツールです。 CSV だけでなくタブ区切りの TSV にも対応しているとのこと。

Run SQL directly on CSV files | Text as Data | q

使い方

公式のヘルプに書いてあるとおりではありますが、引数に 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' $csv

SQL どばー

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 で持っていなかったり、条件を指定したデータの出力が叶わなかったりするなど、社会は厳しいことが多いので雑シェル力は大事ですね。

Share Comments
comments powered by Disqus