【GAWK】でグループ平均を求めたり、PIVOT、UNPIVOTするテンプレート【CSV】
はじめに
前回からもう少し応用がきくように…
・グループ化と集計だけ（縦持ちのまま）
・ピボットするだけ。縦持ちから横持ち(wider)に
・アンピボットするだけ。横持ちから縦持ち(longer)に
…の三つのテンプレートに分けとく
（でもカウントと合計に限れば前回の方が便利なのよね^^）。
まず、以下の CSVデータをサンプルとする。
long_sample.csv↓
氏名,年次,教科,点数
佐藤一郎,1,国,30
佐藤一郎,1,数,20
佐藤一郎,1,英,10
佐藤一郎,2,国,60
佐藤一郎,2,数,50
佐藤一郎,2,英,40
佐藤一郎,3,国,90
佐藤一郎,3,数,80
佐藤一郎,3,英,70
山田花子,1,国,40
山田花子,1,数,30
山田花子,1,英,20
山田花子,2,国,70
山田花子,2,数,60
山田花子,2,英,50
山田花子,3,国,100
山田花子,3,数,90
山田花子,3,英,80
鈴木太郎,1,国,50
鈴木太郎,1,数,40
鈴木太郎,1,英,30
鈴木太郎,2,国,80
鈴木太郎,2,数,70
鈴木太郎,2,英,60
鈴木太郎,3,国,100
鈴木太郎,3,数,100
鈴木太郎,3,英,90分岐は面倒なので一度にカウント、合計、最大、最小、平均を計算して縦持ち（long）にしておく。
最後に必要な行だけ取り出してね^^
今回は 3年間、各人、教科毎の最大、最小、平均を出してみる。
グループ化と集計だけ（縦持ちのまま）するテンプレート
#csv, group and aggregate by gawk on bash.
#パラメタ設定始め.
CSVFile='long_sample.csv'
Group='$1, $3' #グループ化するフィールドをカンマ区切りで
#Group='""' #全行で 1グループにする場合
Calc='$4' #集計に用いる数値型フィールドを一つ
#Calc='""' #数値列が無くカウントだけの場合
AVGFormat='%5.1f' #平均の書式
Format='%3s' #それ以外の書式
Sorted_In='@ind_str_asc' #グループ部のソート順（文字順）
#Sorted_In='@ind_num_asc' #グループ部のソート順（数字順）
#Sorted_In='@ind_num_desc' #グループ部のソート順（数字逆順）
#Filter='cat'
Filter='sed -r -e /,(CNT|SUM),/d' #最大、最小、平均を出力
#Filter='sed -n -r -e 1p;/,(MAX|MIN|AVG),/p' #最大、最小、平均を出力
OutFile='/dev/stdout'
#OutFile='temporary.csv'
#パラメタ設定終わり.
cat $CSVFile | gawk -f <(cat <<__HERE__ #| tee temporary.awk
BEGIN {
FS = ","
OFS = ","
SUBSEP=OFS
PROCINFO["sorted_in"] = "$Sorted_In"
}
#MAIN.
NR==1{print $Group, "集計法", "集計値"} #ヘッダ
NR>1{
group_by[$Group]["CNT"]++
group_by[$Group]["SUM"] += $Calc
if($Calc > group_by[$Group]["MAX"] || group_by[$Group]["MAX"] == ""){
group_by[$Group]["MAX"] = $Calc
}
if($Calc < group_by[$Group]["MIN"] || group_by[$Group]["MIN"] == ""){
group_by[$Group]["MIN"] = $Calc
}
}
END{
for (group in group_by){
print group, "CNT", sprintf("$Format", group_by[group]["CNT"])
print group, "SUM", sprintf("$Format", group_by[group]["SUM"])
print group, "MAX", sprintf("$Format", group_by[group]["MAX"])
print group, "MIN", sprintf("$Format", group_by[group]["MIN"])
print group, "AVG", sprintf("$AVGFormat", group_by[group]["SUM"] / group_by[group]["CNT"])
} #→（ソート済み）1グループあたり 5行出力.
}
__HERE__
) | ${Filter} >$OutFile
3年集計なので
Group='$1, $3'
で第1,3列をグループ化し、
最大、最小、平均が欲しいので
Filter='sed -r -e /,(CNT|SUM),/d'
でカウントと合計を除外して出力すると以下のとおり
（上記スクリプトは標準出力だが、ファイルに保存したものとして）。
temporary.csv↓
氏名,教科,集計法,集計値
佐藤一郎,国,MAX, 90
佐藤一郎,国,MIN, 30
佐藤一郎,国,AVG, 60.0
佐藤一郎,数,MAX, 80
佐藤一郎,数,MIN, 20
佐藤一郎,数,AVG, 50.0
佐藤一郎,英,MAX, 70
佐藤一郎,英,MIN, 10
佐藤一郎,英,AVG, 40.0
山田花子,国,MAX,100
山田花子,国,MIN, 40
山田花子,国,AVG, 70.0
山田花子,数,MAX, 90
山田花子,数,MIN, 30
山田花子,数,AVG, 60.0
山田花子,英,MAX, 80
山田花子,英,MIN, 20
山田花子,英,AVG, 50.0
鈴木太郎,国,MAX,100
鈴木太郎,国,MIN, 50
鈴木太郎,国,AVG, 76.7
鈴木太郎,数,MAX,100
鈴木太郎,数,MIN, 40
鈴木太郎,数,AVG, 70.0
鈴木太郎,英,MAX, 90
鈴木太郎,英,MIN, 30
鈴木太郎,英,AVG, 60.0次にこれを集計列でピボットする
（つまりグループはそのまま）。
ピボットするだけ。縦持ちから横持ちに(wider)するテンプレート
#csv, pivot by gawk on bash.
#パラメタ設定始め.
CSVFile='temporary.csv'
Group='$1, $2' #ピボットと値以外のフィールドをカンマ区切りで
Pivot='$3' #ピボットしてヘッダになるフィールド（を文字列結合する式）
Value='$4' #値フィールド（を値とする式）
RowSort='' #行ソート、文字順
#RowSort='-n' #行ソート、数字順
#RowSort='-nr' #行ソート、数字逆順
ColSort='@ind_str_asc' #ピボット列ソート、文字順
#ColSort='@ind_str_desc' #ピボット列ソート、文字逆順
#ColSort='@ind_num_asc' #ピボット列ソート、数字順
OutFile='/dev/stdout'
#OutFile='outfile.csv'
#パラメタ設定終わり.
cat $CSVFile | gawk -f <(cat <<__HERE__ #| tee temporary.awk
BEGIN {
FS = ","
OFS = ","
SUBSEP = OFS
ORS = ""
#PROCINFO["sorted_in"] = "@ind_num_asc"
}
#MAIN.
NR==1{print $Group} #ヘッダ前半
NR>1{group_by[$Group][$Pivot] = $Value}
NR>1{pivot_headers_dic[$Pivot] = 1}
END{
#ピボット列名を「配列」に.
asorti(pivot_headers_dic, pivot_headers, "$ColSort")
#ヘッダ後半.
for (idx in pivot_headers){
pivot = pivot_headers[idx]
print OFS pivot
}
print "\n"
#グループ部を一行ずつ.
for (group in group_by){
line = group
#その一行にピボット列の値を追加していく.
for (idx in pivot_headers){
pivot = pivot_headers[idx]
line = line OFS group_by[group][pivot]
} #→一行完成
print line "\n" | "sort $RowSort"
}
}
__HERE__
) >$OutFileグループ化列は氏名、教科、
ピボット列は集計法、
値は集計値なので…
Group='$1, $2'
Pivot='$3'
Value='$4'
…とすると、出力は以下のとおり↓
氏名,教科,AVG,MAX,MIN
佐藤一郎,国, 60.0, 90, 30
佐藤一郎,数, 50.0, 80, 20
佐藤一郎,英, 40.0, 70, 10
山田花子,国, 70.0,100, 40
山田花子,数, 60.0, 90, 30
山田花子,英, 50.0, 80, 20
鈴木太郎,国, 76.7,100, 50
鈴木太郎,数, 70.0,100, 40
鈴木太郎,英, 60.0, 90, 30また、
Group='$1'
Pivot='$3 "_" $2'
Value='$4'
で、一人一行にすると以下のとおり↓
氏名,AVG_国,AVG_数,AVG_英,MAX_国,MAX_数,MAX_英,MIN_国,MIN_数,MIN_英
佐藤一郎, 60.0, 50.0, 40.0, 90, 80, 70, 30, 20, 10
山田花子, 70.0, 60.0, 50.0,100, 90, 80, 40, 30, 20
鈴木太郎, 76.7, 70.0, 60.0,100,100, 90, 50, 40, 30ところで、ここまで元データが縦持ち(long)前提でやってきたけれども、実際のところ常に縦持ちでデータが提供されるとは限らず、以下のような横持ち(wide)データをもらうことも多いだろう。
wide_sample.csv↓
氏名,年次,国,数,英
佐藤一郎,1,30,20,10
佐藤一郎,2,60,50,40
佐藤一郎,3,90,80,70
山田花子,1,40,30,20
山田花子,2,70,60,50
山田花子,3,100,90,80
鈴木太郎,1,50,40,30
鈴木太郎,2,80,70,60
鈴木太郎,3,100,100,90なのでこれを加工しやすいように…
アンピボットするだけ。横持ちから縦持ち(longer)にするテンプレート
#csv, unpivot by awk.
awk '
BEGIN {
#パラメタ設定（入出力以外）始め.
start_unpivot = 3 #アンピボット開始フィールド番号
new_col_name = "教科" #エクセルで言う属性 (Attribute) 名
new_val_name = "点数" #エクセルで言う値 (Value) の名
#パラメタ設定（入出力以外）終わり.
FS=","
OFS=","
}
#MAIN.
NR==1{
end_unpivot = NF
#For pivot = start_unpivot To end_unpivot
for (pivot=start_unpivot; pivot<=end_unpivot; pivot++){
pivot_headers[pivot] = $pivot
} #→アンピボット列名の配列
#For group = 1 To start_unpivot-1
for (group=1; group<=start_unpivot-1; group++){
header = header $group OFS
} #→元グループ部のヘッダ
print header new_col_name, new_val_name #元グループ名+属性名+値の名
} #→ヘッダ行おわり
NR>1{
#アンピボット列の数だけくりかえし.
#For pivot = start_unpivot To end_unpivot
for (pivot=start_unpivot; pivot<=end_unpivot; pivot++){
line = ""
#For group = 1 To start_unpivot-1
for (group=1; group<=start_unpivot-1; group++){
line = line $group OFS
} #→元グループ部
print line pivot_headers[pivot], $pivot #元グループ部+属性+値
} #→元の各1行が縦に広がる
}
' wide_sample.csv #>outfile.csv #入出力設定この出力結果は…もちろん最初の long_sample.csvと等しい。
と、一周したところで以上、GROUP BYに伴う集計、 PIVOT、UNPIVOTでした。
（おしまい）
↓（いつの間にか増補新装版が出てたので推す。AWKは出てこないけど^^）
ITエンジニア1年生のための まんがでわかるLinux
コマンド＆シェルスクリプト基礎編