【GAWK】でGROUP BY、カウント／合計、PIVOT【CSV】

2022年4月25日 03:13

GAWKで PIVOT

グーグルスプレッドシートの SQLライクな QUERY関数をいじくり回していたらなんとな～く GROUP BYとか PIVOTとかがイメージできてきた。
GROUP BYは uniqなのね。
PIVOT部はそこに辞書（JSONで言うオブジェクト）が入ってるようなものなのね。
{PIVOT1:集計値1, PIVOT2:集計値2, ...}
なら GAWKの連想配列でできそうな気がしてきた。

サンプルデータとして
東京都新型コロナウイルス陽性患者発表詳細 - データセット - 東京都オープンデータカタログサイト

↑から
東京都_新型コロナウイルス陽性患者発表詳細（全期間一括版）
130001_tokyo_covid19_patients.csv
の先頭23行を抜き出したものを用意する。

sample.csv↓

No,全国地方公共団体コード,都道府県名,市区町村名,公表_年月日,発症_年月日,確定_年月日,患者_居住地,患者_年代,患者_性別,患者_職業,患者_状態,患者_症状,患者_渡航歴の有無フラグ,患者_接触歴の有無フラグ,備考,退院済フラグ
1,130001,東京都,,2020-01-24,,,湖北省武漢市,40代,男性,,,,,,,1
2,130001,東京都,,2020-01-25,,,湖北省武漢市,30代,女性,,,,,,,1
3,130001,東京都,,2020-01-30,,,湖南省長沙市,30代,女性,,,,,,,1
4,130001,東京都,,2020-02-13,,,都内,70代,男性,,,,,,,1
5,130001,東京都,,2020-02-14,,,都内,50代,女性,,,,,,,1
6,130001,東京都,,2020-02-14,,,都内,70代,男性,,,,,,,1
7,130001,東京都,,2020-02-15,,,都内,80代,男性,,,,,,,1
8,130001,東京都,,2020-02-15,,,都内,50代,女性,,,,,,,1
9,130001,東京都,,2020-02-15,,,都内,50代,男性,,,,,,,1
10,130001,東京都,,2020-02-15,,,都内,70代,男性,,,,,,,1
11,130001,東京都,,2020-02-15,,,都内,70代,男性,,,,,,,1
12,130001,東京都,,2020-02-15,,,都内,40代,男性,,,,,,,1
13,130001,東京都,,2020-02-15,,,都内,60代,女性,,,,,,,1
14,130001,東京都,,2020-02-15,,,都内,40代,男性,,,,,,,1
15,130001,東京都,,2020-02-16,,,都内,60代,男性,,,,,,,1
16,130001,東京都,,2020-02-16,,,都内,30代,男性,,,,,,,1
17,130001,東京都,,2020-02-16,,,都内,60代,男性,,,,,,,1
18,130001,東京都,,2020-02-16,,,都外,60代,男性,,,,,,,1
19,130001,東京都,,2020-02-16,,,都内,30代,男性,,,,,,,1
20,130001,東京都,,2020-02-18,,,都内,80代,男性,,,,,,,1
21,130001,東京都,,2020-02-18,,,都内,20代,男性,,,,,,,1
22,130001,東京都,,2020-02-18,,,都内,50代,男性,,,,,,,1

この CSVに
=QUERY(A1:Q23, "SELECT C, E, COUNT(A) GROUP BY C, E PIVOT J, I", 1)
相当の操作をする GAWKスクリプトを考える。
まず $3 $5 $10 $9 の組み合わせを数えるために group_by[$3, $5][$10 "_" $9]++ していき、
最後に一行毎、group_by[$3, $5]毎に group_by[$3, $5][$10 "_" $9]の値を並べていく。

cat sample.csv | gawk -e '
BEGIN {
  FS = ","
  OFS = ","
  SUBSEP = OFS
  ORS = ""
  #PROCINFO["sorted_in"] = "@ind_num_asc"
}
        #MAIN.
NR==1{print $3, $5}  #ヘッダ前半
NR>1{group_by[$3, $5][$10 "_" $9]++}
NR>1{pivot_headers_dic[$10 "_" $9] = 1}

END{
          #ピボット列名を「配列」に.
  asorti(pivot_headers_dic, pivot_headers, "@ind_str_asc")

          #ヘッダ後半.
  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 sprintf("%3s", group_by[group][pivot])
    }       #→一行完成
    print line "\n" | "sort"
  }
}
' #>outfile.csv

出力は以下のとおり↓

都道府県名,公表_年月日,女性_30代,女性_50代,女性_60代,男性_20代,男性_30代, 男性_40代,男性_50代,男性_60代,男性_70代,男性_80代
東京都,2020-01-24,   ,   ,   ,   ,   ,  1,   ,   ,   ,
東京都,2020-01-25,  1,   ,   ,   ,   ,   ,   ,   ,   ,
東京都,2020-01-30,  1,   ,   ,   ,   ,   ,   ,   ,   ,
東京都,2020-02-13,   ,   ,   ,   ,   ,   ,   ,   ,  1,
東京都,2020-02-14,   ,  1,   ,   ,   ,   ,   ,   ,  1,
東京都,2020-02-15,   ,  1,  1,   ,   ,  2,  1,   ,  2,  1
東京都,2020-02-16,   ,   ,   ,   ,  2,   ,   ,  3,   ,
東京都,2020-02-18,   ,   ,   ,  1,   ,   ,  1,   ,   ,  1

うまくいった（もちろんここまで大変だった^^）のでもう少し汎用性を高めてテンプレートにして作り置き。

CSVデータを GROUP BYしてカウントか合計を PIVOTするテンプレート

        #csv, group and pivot by gawk.

        #パラメタ設定始め.
CSVFILE='sample.csv'
GROUP='$3, $5'  #グループ化するフィールドをカンマ区切りで
#GROUP='""'  #1行になる
PIVOT='$10 "_" $9'  #ピボットしてヘッダになるフィールド（を文字列結合する式）
#PIVOT='""'  #グループ化のみ
CALCULATOR='++'  #カウント
#CALCULATOR='+=$1'  #$1の合計
FORMAT='%3s'  #値の書式
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__
BEGIN {
  FS = ","
  OFS = ","
  SUBSEP = OFS
  ORS = ""
  #PROCINFO["sorted_in"] = "@ind_num_asc"
}
        #MAIN.
NR==1{print $GROUP}  #ヘッダ前半
NR>1{group_by[$GROUP][$PIVOT]$CALCULATOR}
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 sprintf("$FORMAT", group_by[group][pivot])
    }       #→一行完成
    print line "\n" | "sort $ROWSORT"
  }
}
__HERE__
) >$OUTFILE

上記のパラメタで実行すると出力はもちろん上と同じになる。
では元サンプルからグループ化列は同じでピボット列を患者_年代＝第9列にして
PIVOT = '$9'
として実行すると出力は以下のようになる。
=QUERY(A1:Q23, "SELECT C, E, COUNT(A) GROUP BY C, E PIVOT I", 1) に相当↓

都道府県名,公表_年月日,20代,30代,40代,50代,60代,70代,80代
東京都,2020-01-24,   ,   ,  1,   ,   ,   ,
東京都,2020-01-25,   ,  1,   ,   ,   ,   ,
東京都,2020-01-30,   ,  1,   ,   ,   ,   ,
東京都,2020-02-13,   ,   ,   ,   ,   ,  1,
東京都,2020-02-14,   ,   ,   ,  1,   ,  1,
東京都,2020-02-15,   ,   ,  2,  2,  1,  2,  1
東京都,2020-02-16,   ,  2,   ,   ,  3,   ,
東京都,2020-02-18,  1,   ,   ,  1,   ,   ,  1

ではグループ化列を都道府県名、公表_年月日、患者_性別＝第3, 5, 10列にして、ピボット列を患者_年代＝第9列にして
GROUP = '$3, $5, $10'
PIVOT = '$9'
として実行すると出力は以下のようになる。
=QUERY(A1:Q23, "SELECT C, E, J, COUNT(A) GROUP BY C, E, J PIVOT I", 1) に相当↓

都道府県名,公表_年月日,患者_性別,20代,30代,40代,50代,60代,70代,80代
東京都,2020-01-24,男性,   ,   ,  1,   ,   ,   ,
東京都,2020-01-25,女性,   ,  1,   ,   ,   ,   ,
東京都,2020-01-30,女性,   ,  1,   ,   ,   ,   ,
東京都,2020-02-13,男性,   ,   ,   ,   ,   ,  1,
東京都,2020-02-14,女性,   ,   ,   ,  1,   ,   ,
東京都,2020-02-14,男性,   ,   ,   ,   ,   ,  1,
東京都,2020-02-15,女性,   ,   ,   ,  1,  1,   ,
東京都,2020-02-15,男性,   ,   ,  2,  1,   ,  2,  1
東京都,2020-02-16,男性,   ,  2,   ,   ,  3,   ,
東京都,2020-02-18,男性,  1,   ,   ,  1,   ,   ,  1

では全く無意味だが No＝第1列を加算してみよう。
GROUP = '$5'
PIVOT = '$10'
CALCULATOR = '+=$1'
として実行すると出力は以下のようになる。
=QUERY(A1:Q23, "SELECT E, SUM(A) GROUP BY E PIVOT J", 1) に相当↓

公表_年月日,女性,男性
2020-01-24,   ,  1
2020-01-25,  2,
2020-01-30,  3,
2020-02-13,   ,  4
2020-02-14,  5,  6
2020-02-15, 21, 63
2020-02-16,   , 85
2020-02-18,   , 63

う～ん、無意味だ(笑
では
PIVOT = '""'
CALCULATOR='++'
だと単純に毎日の人数が分かる。
=QUERY(A1:Q23, "SELECT E, COUNT(A) GROUP BY E", 1) に相当↓

公表_年月日,
2020-01-24,  1
2020-01-25,  1
2020-01-30,  1
2020-02-13,  1
2020-02-14,  2
2020-02-15,  8
2020-02-16,  5
2020-02-18,  3

そして
GROUP='$3'
PIVOT='$10 "_" $9'
だと都道府県別小計になる…ってそもそも東京都しかないけど^^
=QUERY(A1:Q23, "SELECT C, COUNT(A) GROUP BY C PIVOT J, I", 1) に相当↓

都道府県名,女性_30代,女性_50代,女性_60代,男性_20代,男性_30代,男性_40代,男 性_50代,男性_60代,男性_70代,男性_80代
東京都,  2,  2,  1,  1,  2,  3,  2,  3,  4,  2

ならば年別月別小計ができそうなものだが惜しいかな、年や月が単独のフィールドになっていない。
もちろん事前に AWKで、

awk '
BEGIN{FS=",";OFS=","}
NR==1{sub("年月日", "年,月,日", $5);print}
NR>1{gsub("-", ",", $5);print}
' sample.csv

ハイフンをカンマにしておけば GROUP='$5' で年別、GROUP='$5, $6' で月別小計が出せるのだが
（後ろのフィールド番号がずれるので注意）。

おわりに

「QUERY関数でできるならスプレッドシートでいいのでは？」

いやあ、17列×100万レコード超え（千万セル超え）で元の東京都の CSVは開けないんですよね。
GAWKなら一瞬…いや、3拍くらいかな^^
まあ、何でも入力が一番たいへんなんですが。
コロナ対応のみな様ご苦労様です。

（おしまい）

主な修正履歴。
2022-04-26: 最後のコードブロックを修正。

この記事が気に入ったらサポートをしてみませんか？