内閣府の「平均寿命の推移」の CSV が扱いやすくなるようにワンライナーで整形してみた。
はじめに
内閣府の公開している「平均寿命の推移」、データとして最悪なCSVすぎる pic.twitter.com/DVsvvwigTz
— ひな (@hnle0) 2023年3月2日
もうだいぶ前のことだけど、Twitter で「内閣府の公開している『平均寿命の推移』がデータとして最悪な CSV」と話題になっていたので、最悪な CSV が扱いやすくなるようにワンライナーで整形してみた。
ワンライナー
先に書いてしまうと、平均寿命の推移 CSV を整形するワンライナーは次のようになる。
$ cat h0304080.csv | \ nkf -Luw | \ sed '1,3d' | \ sed '/^$/,$d' | \ tr -d ' ' | \ awk -F, '{a[NR]=$4" "$5" "$6; $4=$5=$6=""; print}END{for(i=0;i<NR;i++){print a[i]}}' | \ grep -v '^ *$' | \ sed 's/(.*)//' | \ awk '$1 ~ /^[0-9]/{y=substr($1,0,2)}; $1 ~ /^ /{gsub(" ","",$1); $1=y $1}; {print}' | \ awk '{print $1,$1,$2,$3}' | \ teip -f2 -- wareki -K - | \ awk '{$2="(" $2 ")"; print}' | \ sed 's/ //' | \ sed 's/ /,/g' 1947(昭和22),50.06,53.96 1948(昭和23),55.60,59.40 1949(昭和24),56.20,59.80 # ... 省略 ... 2002(平成14),78.32,85.23 2003(平成15),78.36,85.33 2004(平成16),78.64,85.59
このあとは、平均寿命の推移の CSV が扱いにくい原因と、その原因を解消する方法をワンライナーを分解しつつ書いていく。
平均寿命の推移の CSV が扱いにくい原因
平均寿命の推移の CSV が「データとして最悪」とまで言われるのは、CSV データとして扱いにくい内容になっているためだろう。
CSV データとして扱いにくい原因としては、次のものがある。
- 余計なヘッダーとフッターが含まれている
- データ中に意味のないスペースが含まれている
- 1行あたり2レコードが含まれている
- 西暦の上2桁が省略されているレコードがある
- 元号が省略されているレコードがある
- 和暦のフォーマットが異なるレコードがある
余計なヘッダーとフッターが含まれている
平均寿命の推移の CSV は、Excel で human readable (?) なドキュメントを作り、CSV フォーマットでエクスポートした感じで、次のような余計なヘッダー / フッターが含まれている (ヘッダー、フッターといえるものではないが)
↓ヘッダー
図8 平均寿命の推移,,,,, ,,,,,(歳) 年,男,女,年,男,女
↓フッター
注1:厚生労働省「生命表」による。平均寿命は、各年の0歳児の平均余命を,,,,, いう。,,,,, 2:1947、55、60、65、70、75、80、85、90、95、2000年は「完全生命表」、,,,,, その他の年は「簡易生命表」。,,,,,
データ中に意味のないスペースが含まれている
50.06 ,53.96 ,
のように、カンマの前に意味のないスペースが含まれている。
1行あたり2レコードが含まれている
平均寿命の推移 CSV を扱いにくい最大の原因は、1行あたり2レコードが含まれていることだろう。
1行あたり2レコードが含まれていても、含まれるレコードがデータ的に連続しているものであれば整形しやすいことが多いが、平均寿命の推移 CSV はデータ的に連続していないレコードが1行に含まれており、扱いにくさを増す原因になっている。
1947(昭和22),50.06 ,53.96 , 77(昭和52),72.69 ,77.95 48( 23),55.60 ,59.40 , 78( 53),72.97 ,78.33
西暦の上2桁が省略されているレコードがある
任意の行について、西暦の上2桁が上の行と同じ場合、西暦の上2桁がスペースに置き換えられる形で省略されている。
データとして欠落しているものになっており、これもまたこの CSV を扱いにくくする原因になっている。
1947(昭和22),50.06 ,53.96 , 77(昭和52),72.69 ,77.95 48( 23),55.60 ,59.40 , 78( 53),72.97 ,78.33
元号が省略されているレコードがある
西暦の上2桁の省略とだいたい同じ *1
和暦のフォーマットが異なるレコードがある
和暦は <元号>年
というフォーマットになっているが、64・平成元
のように2つの和暦が含まれ、フォーマットが異なるレコードがある。
59( 34),65.21 ,69.88 , 89(64・平成元),75.91 ,81.77
ワンライナーの解説
文字コードと改行コードを変換する
平均寿命の推移 CSV は文字コードが Shift_JIS に、改行コードが CRLF になっていて、少々扱いにくい。 まずは文字コードを UTF-8 に、改行コードを LF に変換しておく。
$ cat h0304080.csv | \ nkf -Luw
余計なヘッダーとフッターを削除する
ヘッダーの行数は1〜3行目に決まっているので、sed で削除する。
sed '1,3d' | \
フッターは、実データのあとの空行以降がフッターとなるので、空行以降を sed で削除する。
sed '/^$/,$d' | \
余分なスペースを削除する
50.06 ,53.96 ,
に含まれるような余分なスペースを削除する。
tr -d ' ' | \
1行あたり1レコードにする
awk で次のように処理し、1行あたり1レコードにする。
- 1行に含まれる2レコードのうち、1レコード目を出力する
- 2レコード目は配列に保存しておく
- 1, 2 をすべての行で行う
- すべての行を処理し終わったら、配列に保存しておいた2レコード目を全て出力する
awk -F, '{a[NR]=$4" "$5" "$6; $4=$5=$6=""; print}END{for(i=0;i<NR;i++){print a[i]}}' | \
awk の OFS
変数は指定せず、各フィールドはデフォルトの区切り文字のスペースで区切らせる。
空行を削除する
1行あたり1レコードにする際、空行 / スペースだけを含む行ができてしまうので、削除する。
grep -v '^ *$' | \
元号・和暦を削除する
元号・和暦は一旦削除し、あとで西暦から求める。
元号・和暦は (
と )
で囲まれているので、その部分を sed で削除する。
sed 's/(.*)//' | \
西暦の上2桁を補完する
西暦の上2桁が欠損しているデータについて、次のようにして西暦の上2桁を補完する。
- 1つめのフィールドが
1947
のように数字で始まっていたら、先頭の2桁を記憶しておく - 1つめのフィールドが全角スペースで始まっていたら、全角スペースを記憶しておいた2桁に置き換える
awk '$1 ~ /^[0-9]/{y=substr($1,0,2)}; $1 ~ /^ /{gsub(" ","",$1); $1=y $1}; {print}' | \
元号・和暦を追加する
西暦,男性の平均寿命,女性の平均寿命
のようになっているデータを、西暦,西暦,男性の平均寿命,女性の平均寿命
のように西暦のフィールドを1つ増やす。
awk '{print $1,$1,$2,$3}' | \
2つめのフィールドの西暦を元号・和暦に変換する。
teip を使って2つめのフィールドだけを処理対象にし、wareki を使って西暦を元号・和暦に変換する。
teip -f2 -- wareki -K - | \
元号・和暦を (
, )
で囲む。
awk '{$2="(" $2 ")"; print}' | \
西暦と元号・和暦を1つのフィールドにまとめる
西暦と元号・和暦を区切っているスペースを sed で削除し、1つのフィールドにまとめる。
sed 's/ //' | \ sed 's/ /,/g'
区切り文字をスペースからカンマに戻す
sed でスペースをカンマに変換する。
sed 's/ /,/g'
以上。