【CSV】ヘッダ列の無いPIVOT、多段/複行ヘッダを一行にして UNPIVOT、セル結合の空セルを埋める【AWK 】

ななっしー

1. ヘッダになる列の無いピボット

場合によってはヘッダになる列の無いピボット、属性列の無い値だけのピボットが必要なときもある。
専用ピボットを作ってもいいけど、汎用ピボットでも処理できるようにヘッダに利用する列を追加する方向で行く。

まず、サンプルの縦持ちCSV。
file 1.a: long_sample.csv↓

ID,世帯主,子,年齢
1,鈴木太郎,一郎,12
1,鈴木太郎,二郎,11
1,鈴木太郎,三郎,10
2,山田花子,松子,13
2,山田花子,梅子,12
2,山田花子,桜子,11
2,山田花子,藤子,10
3,井上成美,馨,11
3,井上成美,純,10

上記のような表をピボットするために各グループをカウントアップしていく列＝ピボット時にヘッダになる列を追加する AWKスクリプト。
script 1.b:↓

        #count up duplication.

awk '
BEGIN{
          #パラメタ設定（入出力以外）始め.
  end_group = 2  #グループ部最後のフィールド番号
  dupl_format = "%02d"  #重複カウントの書式
          #パラメタ設定（入出力以外）終わり.
  FS=","
  OFS=","
}

NR==1{
  print "DUPL", $0  #ヘッダ
}

NR>1{
  current = ""

          #For group = 1 To end_group
  for (group=1; group<=end_group; group++){
    current = current OFS $group
  }       #→現在行のグループ部

          #比較.
  if(previous==current){
    dupl++
  }else{
    dupl = 1
    previous = current
  }

  print sprintf(dupl_format, dupl), $0
}
' long_sample.csv #>temporary.csv  #入出力設定

出力は以下のとおり。
result 1.c:↓

DUPL,ID,世帯主,子,年齢
01,1,鈴木太郎,一郎,12
02,1,鈴木太郎,二郎,11
03,1,鈴木太郎,三郎,10
01,2,山田花子,松子,13
02,2,山田花子,梅子,12
03,2,山田花子,桜子,11
04,2,山田花子,藤子,10
01,3,井上成美,馨,11
02,3,井上成美,純,10

これを例えばカウントアップ列を新ヘッダ、子列+年齢列を値としてピボットするには
以前の「csv, pivot by gawk on bash」を利用してパラメタを…

Group='$2, $3' #ピボットと値以外のフィールドをカンマ区切りで
Pivot='"子" $1' #ピボットしてヘッダになるフィールド（を文字列結合する式）
Value='$4 "(" $5 ")"' #値フィールド（を値とする式）

…で実行する、と出力は以下のとおり。
result 1.d:↓

ID,世帯主,子01,子02,子03,子04
1,鈴木太郎,一郎(12),二郎(11),三郎(10),
2,山田花子,松子(13),梅子(12),桜子(11),藤子(10)
3,井上成美,馨(11),純(10),,

なお、条件として元データ（今回の long_sample.csv）はあらかじめソートされている必要があります。同じグループが飛び飛びになっていてはカウントできません。

2. 多段/複数行ヘッダを一行にしてアンピボット

ヘッダが多段/複数行になっている場合、アンピボットその他いろいろ不便なのでまず列名重複なしの一行にする。

例えば以下のような見かけの表があったとする↓

図表 2.a:

この中には以下のようなデータが入っている↓

file 2.b1: wide_sample1.csv

,国語,,,数学,,,英語,,
氏名,１年,２年,３年,１年,２年,３年,１年,２年,３年
佐藤一郎,  30,  60,  90,  20,  50,  80,  10,  40,  70
山田花子,  40,  70, 100,  30,  60,  90,  20,  50,  80
鈴木太郎,  50,  80, 100,  40,  70, 100,  30,  60,  90

file 2.b2: wide_sample2.csv

,2020,,,,,,,
,01,,,02,,,,
性別,24,25,30,13,14,15,16,18
女性,  , 1, 1,  , 1, 2,  ,
男性, 1,  ,  , 1, 1, 6, 5, 3

これを見出しのツリー構造をたどって一行ヘッダにする AWKスクリプト。
script 2.c:↓

        #to linear header.

awk '
BEGIN{
          #パラメタ設定（入出力以外）始め.
  header_line = 2  #ヘッダ段数/行数
  connector = "_"  #上下段列名結合文字
          #パラメタ設定（入出力以外）終わり.
  FS=","
  OFS=","
}

NR<header_line{
          #For idx = 1 To NF  #フィールドなめる.
  for (idx=1; idx<=NF; idx++){
    if($idx==""){
      headers[idx] = headers[idx-1]    #セル結合継続
    }else if($idx~/^　+$/){
      headers[idx] = headers[idx] ""    #セル結合終わる(だけ)
    }else{
      headers[idx] = headers[idx] connector $idx  #新しいセル結合始め
    }
  }       #Forおわり
}

NR==header_line{
          #For idx = 1 To NF  #フィールドなめる.
  for (idx=1; idx<=NF; idx++){

    if($idx==""){
      $idx = headers[idx]    #ヘッダ最下段に値なし
    }else{
      $idx = headers[idx] connector $idx  #ヘッダ最下段に値あり
    }

    sub(connector, "", $idx)  #余分な結合文字削除
  }       #Forおわり
  print
}

NR>header_line{print}

' wide_sample1.csv #>temporary.csv  #入出力設定

このスクリプトをそれぞれ
　wide_sample1.csvに対しては↓
header_line = 2 #ヘッダ段数/行数
connector = "_" #上下段列名結合文字

wide_sample2.csvに対しては↓
header_line = 3
connector = "-"
　…のパラメタで実行すると

国語,
１年,２年,　→　国語_１年,国語_２年,

2020,
01,
24,25,　→　2020_01_24,2020_01_25,

…等となって結果は以下の通り↓

result 2.d1:

氏名,国語_１年,国語_２年,国語_３年,数学_１年,数学_２年,数学_３年,英語_１年,英語_２年,英語_３年
佐藤一郎,  30,  60,  90,  20,  50,  80,  10,  40,  70
山田花子,  40,  70, 100,  30,  60,  90,  20,  50,  80
鈴木太郎,  50,  80, 100,  40,  70, 100,  30,  60,  90

result 2.d2:

性別,2020-01-24,2020-01-25,2020-01-30,2020-02-13,2020-02-14,2020-02-15,2020-02-16,2020-02-18
女性,  , 1, 1,  , 1, 2,  ,
男性, 1,  ,  , 1, 1, 6, 5, 3

これをアンピボットするに
以前の「csv, unpivot by awk」を利用して入力を temporary.csvとし、パラメタを…
　前者に対しては↓
start_unpivot = 2 #アンピボット開始フィールド番号
new_col_name = "教科_年次" #エクセルで言う属性 (Attribute) 名
new_val_name = "点数" #エクセルで言う値 (Value) の名

後者に対しては↓
start_unpivot = 2
new_col_name = "年月日"
new_val_name = "人数"
　…で実行すると結果は以下の通り↓

result 2.e1:

氏名,教科_年次,点数
佐藤一郎,国語_１年,  30
佐藤一郎,国語_２年,  60
佐藤一郎,国語_３年,  90
佐藤一郎,数学_１年,  20
佐藤一郎,数学_２年,  50
佐藤一郎,数学_３年,  80
佐藤一郎,英語_１年,  10
佐藤一郎,英語_２年,  40
佐藤一郎,英語_３年,  70
山田花子,国語_１年,  40
山田花子,国語_２年,  70
山田花子,国語_３年, 100
山田花子,数学_１年,  30
山田花子,数学_２年,  60
山田花子,数学_３年,  90
山田花子,英語_１年,  20
山田花子,英語_２年,  50
山田花子,英語_３年,  80
鈴木太郎,国語_１年,  50
鈴木太郎,国語_２年,  80
鈴木太郎,国語_３年, 100
鈴木太郎,数学_１年,  40
鈴木太郎,数学_２年,  70
鈴木太郎,数学_３年, 100
鈴木太郎,英語_１年,  30
鈴木太郎,英語_２年,  60
鈴木太郎,英語_３年,  90

result 2.e2:

性別,年月日,人数
女性,2020-01-24,
女性,2020-01-25, 1
女性,2020-01-30, 1
女性,2020-02-13,
女性,2020-02-14, 1
女性,2020-02-15, 2
女性,2020-02-16,
女性,2020-02-18,
男性,2020-01-24, 1
男性,2020-01-25,
男性,2020-01-30,
男性,2020-02-13, 1
男性,2020-02-14, 1
男性,2020-02-15, 6
男性,2020-02-16, 5
男性,2020-02-18, 3

ただし対応可能な多段/複数行ヘッダの条件が二つ。
・セル結合内の値は一番左のセルにあるものとする。
・ヘッダ段数/行数が増えたらその列以降、減ってはいけない（セル結合の終わりが分からないため）。

…とはいえど～しても減らしたい場合、その段のセル結合が終了して同段次の空列に全角スペースを入れてください。
例えば↓

　　　　↓表計算シート上での見かけが
＿＿＿＿＿＿＿＿＿＿
|　　|　住所 　|　　|
|氏名|町名|番地|備考|
──────────
　　　　↑こんなふうになっている場合、中身は↓
,住所,,
氏名,町名,番地,備考
　　　　↑このようになっているのだが、このままスクリプトを通すと↓
氏名,住所_町名,住所_番地,住所_備考
　　　　　　　　　　　　　↑こうなってしまう。ので、
　　　　↓上段第4列に全角スペースを入れておく。
,住所,,　
氏名,町名,番地,備考
　　　　↑こうしておいてからスクリプトを通すと↓
氏名,住所_町名,住所_番地,備考
　　　　　　　　　　　　　↑ツリー構造を反映した(一行)ヘッダになる。

なお全角スペース挿入は完全にここでのローカルルールなのでおぼえても他では全く役に立ちません。
すなおにヘッダの段数はそろえましょう^^
Pandas DataFrameに読み込むにもそろっていたほうがいいし。

おっと、Pandasに限らずアプリ連携には段数合わせるだけじゃダメだった。
セル結合を埋めて(fillして)おかなきゃ。
これも作り置きしておこう。

3. セル結合の空セルを埋める(fillする) AWK スクリプト。

script 3.a:↓

        #fill cells.

awk '
BEGIN{
          #パラメタ設定（入出力以外）始め.
  header_line = 3  #ヘッダ段数/行数
  index_col = 2  #行見出しの列数
          #パラメタ設定（入出力以外）終わり.
  FS=","
  OFS=","
}

NR<=header_line{
  previous_col = ""
          #For idx = index_col+1 To NF  #フィールドなめる.
  for (idx=index_col+1; idx<=NF; idx++){
    if($idx==""){
      $idx = previous_col  #セル結合継続
    }else if($idx~/^　+$/){
      $idx = ""
      previous_col = ""    #セル結合終わる(だけ)
    }else{
      previous_col = $idx  #新しいセル結合始め
    }
  }       #Forおわり
  print
}

NR>header_line{
          #For idx = 1 To index_col  #フィールドなめる.
  for (idx=1; idx<=index_col; idx++){
    if($idx==""){
      $idx = previous_row[idx]  #縦にセル結合継続
    }else{
      previous_row[idx] = $idx  #縦に新しいセル結合始め
    }
  }       #Forおわり
  print
}
' wide_sample3.csv #>outfile.csv  #入出力設定

これを例えば
file 3.b: wide_sample3.csv↓

,,2020,,,,,,,
,,01,,,02,,,,
府県名,性別,24,25,30,13,14,15,16,18
東京都,女性,,1,1,,1,2,,
,男性,1,,,1,1,6,5,3

↑このファイルに対して実行すると
result 3.d:↓

,,2020,2020,2020,2020,2020,2020,2020,2020
,,01,01,01,02,02,02,02,02
府県名,性別,24,25,30,13,14,15,16,18
東京都,女性,,1,1,,1,2,,
東京都,男性,1,,,1,1,6,5,3

↑空セルが埋められてこうなる。

え？ Pandas使うなら一行ヘッダにする必要無いって？
（モジュール追加すれば）.xlsxファイルも読み込めるから fillする必要も無いって？
…それはそれ、これはこれｗ

4. おまけ：Pandasでピボット

(file 1.a) long_sample.csvを
(script 1.b) count up duplicationした
その出力 (result 1.c) を Pandasでピボットするなら
Google Colabで以下のとおり。
script 4.a:↓

import pandas
            #読み込み.
imported_df = pandas.read_csv(
    '/content/drive/MyDrive/temporary.csv',
    dtype = str
)
display(
                #加工.
    imported_df.pivot(
        index = ['ID', '世帯主'],
        columns = 'DUPL',
        values = ['子', '年齢']
    ).swaplevel(
        0,
        1,
        axis = 1    #列ラベルの上下(level)交換
    ).sort_index(
        axis = 1
    ).reset_index()
)
"""
.to_csv(
    '/content/drive/MyDrive/outfile.csv',
    index = None
)
"""

こうすれば
図表 4.b↓

↑で、GAWK版 (result 1.d) とだいたい同じ。
というより自作スクリプトの結果が Pandasとだいたい同じでむしろ自分のピボットに対する解釈が合っていたようで一安心^^

（おしまい）

Google Colaboratory↓


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