bashのワンライナーでクロス集計(※awkとsedを使わないプレイ)
使用コマンド:cat tr cut sort xargs bash grep bc echo Cross tabulation with bash, without sed and awk.
using:cat tr cut sort xargs bash grep bc echo
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
# 入力ファイルをTSVからCSV形式に変換する。ついでにヘッダの除去も行う | |
cat "$1" | tr -s ' ' | tr '\n' ',' | cut -d, -f2- | tr , '\n' | tr ' ' , | grep -v "^$" > tmp.input | |
# 1列目の項目を抜出し、キー1とする | |
cat tmp.input | cut -d, -f1 | sort -u > tmp.key1 | |
# 2列目の項目を抽出し、キー2とする | |
cat tmp.input | cut -d, -f2 | sort -u > tmp.key2 | |
# キー1とキー2をCORSS JOINし、インデックスとする | |
cat tmp.key1 | xargs -I% bash -c 'cat tmp.key2 | xargs -I@ echo %,@' > tmp.index | |
# インデックスから、集計用の初期値レコード(Money = 0)の行を生成する | |
cat tmp.index | xargs -I% echo %,0 > tmp.init | |
# 入力ファイルと初期値レコードを縦に連結 | |
sort tmp.init tmp.input > tmp.trans | |
# 縦集計しサマリを作成 | |
cat tmp.index | xargs -I% bash -c 'echo %,`grep % tmp.trans | cut -d, -f3 | xargs | tr " " + | bc`' > tmp.sumy | |
# キー1とキー2とサマリからクロス表を出力 | |
echo " `cat tmp.key2 | xargs`" | tr ' ' , | |
cat tmp.key1 | xargs -I% bash -c 'echo % `grep "^%," tmp.sumy | cut -d, -f3 | xargs `' | tr ' ' , |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
tmp=tmp.$$_ \ | |
&& cat sample.txt | tr -s ' ' | tr '\n' ',' | cut -d, -f2- | tr , '\n' | tr ' ' , | grep -v "^$" > ${tmp}1 \ | |
&& key1="`cat ${tmp}1 | cut -d, -f1 | sort -u`" \ | |
&& key2="`cat ${tmp}1 | cut -d, -f2 | sort -u`" \ | |
&& index=`echo "$key1" | xargs -I% bash -c "echo \"$key2\" | xargs -I@ echo %,@"` \ | |
&& (cat ${tmp}1 && echo "$index" | xargs -I% echo %,0) > ${tmp}2 \ | |
&& (echo "$index" | xargs -I% bash -c "echo %,\`grep % ${tmp}2 | cut -d, -f3 | xargs | tr ' ' + | bc\`") > ${tmp}3 \ | |
&& (echo " `echo $key2 | xargs`" && echo "$key1" | xargs -I% bash -c "echo % \`grep '^%,' ${tmp}3 | cut -d, -f3 | xargs \`") | tr ' ' , \ | |
&& rm ${tmp}1 ${tmp}2 ${tmp}3 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
User Item Money | |
A Ice 130 | |
A Ice 180 | |
B Juice 120 | |
B Ice 130 | |
I OREO 210 | |
I OREO 210 | |
I OREO 210 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment