PowerShell でアンケートの集計をするワンライナーを書きました。
Google フォームでも Microsoft Forms でもマークシートでも、結果を CSV ファイルに出来れば OK です。
Excel で開いて COUNTIF？ 文字化けがめんどうだからね。
2021/09/24 「後日談」と「教訓」を追加しました。
作業環境
- Windows 10 20H2 (Build 19042.1237)
- Windows PowerShell 5.1.19041.1237
- PowerShell 7.1.4
前提
アンケート結果の CSV ファイルがあったとします。Q1 から Q5 まで、それぞれ A か B で答えています。999 人分のデータがあるとしましょう。
"Name","Q1","Q2","Q3","Q4","Q5" "たいだ","A","B","A","B","B" "まじめ","B","A","A","B","A" ...
このアンケート結果を、全ての回答の組み合わせごとに集計することになりました。つまり、
- AAAAA
- AAAAB
- AAABA
- AAABB
- ...
- BBBAA
- BBBAB
- BBBBA
- BBBBB
という全 32 通りについて人数を数えるわけです。ひえ。
ごく普通に Where-Object で AAAAA の人数を数えるならこうですね。
# AAAAA の人数を数える @(Import-Csv -LiteralPath .\survey_result.csv -Encoding UTF8 | Where-Object {$_.Q1 -eq 'A' -and $_.Q2 -eq 'A' -and $_.Q3 -eq 'A' -and $_.Q4 -eq 'A' -and $_.Q5 -eq 'A'}).Length
これを 32 通りなんて無理無理無理のかたつむり！
Group-Object で楽をしましょう。
完成形
# 全ての回答の組み合わせごとに集計する Import-Csv -LiteralPath .\survey_result.csv -Encoding UTF8 | Group-Object -Property {"$($_.Q1)$($_.Q2)$($_.Q3)$($_.Q4)$($_.Q5)"} | Sort-Object -Property Name
Count Name Group
----- ---- -----
1 {@{Name=981; Q1=; Q2=; Q3=; Q4=; Q5=}}
3 AAAA {@{Name=530; Q1=A; Q2=; Q3=A; Q4=A; Q5=A}, ...
790 AAAAA {@{Name=003; Q1=A; Q2=A; Q3=A; Q4=A; Q5=A},...
37 AAAAB {@{Name=040; Q1=A; Q2=A; Q3=A; Q4=A; Q5=B},...
28 AAABA {@{Name=053; Q1=A; Q2=A; Q3=A; Q4=B; Q5=A},...
4 AAABB {@{Name=175; Q1=A; Q2=A; Q3=A; Q4=B; Q5=B},...
40 AABAA {@{Name=005; Q1=A; Q2=A; Q3=B; Q4=A; Q5=A},...
1 AABAB {@{Name=840; Q1=A; Q2=A; Q3=B; Q4=A; Q5=B}}
2 AABBA {@{Name=174; Q1=A; Q2=A; Q3=B; Q4=B; Q5=A},...
43 ABAAA {@{Name=015; Q1=A; Q2=B; Q3=A; Q4=A; Q5=A},...
2 ABABA {@{Name=070; Q1=A; Q2=B; Q3=A; Q4=B; Q5=A},...
1 ABABB {@{Name=たいだ; Q1=A; Q2=B; Q3=A; Q4=B; Q5=...
34 BAAAA {@{Name=011; Q1=B; Q2=A; Q3=A; Q4=A; Q5=A},...
3 BAAAB {@{Name=365; Q1=B; Q2=A; Q3=A; Q4=A; Q5=B},...
3 BAABA {@{Name=まじめ; Q1=B; Q2=A; Q3=A; Q4=B; Q5=...
3 BABAA {@{Name=238; Q1=B; Q2=A; Q3=B; Q4=A; Q5=A},...
2 BBAAA {@{Name=139; Q1=B; Q2=B; Q3=A; Q4=A; Q5=A},...
2 BBBAA {@{Name=540; Q1=B; Q2=B; Q3=B; Q4=A; Q5=A},...
いずれかの質問に答えていない場合、どれに答えていないかは分かりません。未回答を除くならこうです。
# 全ての回答の組み合わせごとに集計する（未回答を除く） Import-Csv -LiteralPath .\survey_result.csv -Encoding UTF8 | Group-Object -Property {"$($_.Q1)$($_.Q2)$($_.Q3)$($_.Q4)$($_.Q5)"} | Sort-Object -Property Name | Where-Object {$_.Name.Length -eq 5}
Count Name Group
----- ---- -----
790 AAAAA {@{Name=003; Q1=A; Q2=A; Q3=A; Q4=A; Q5=A},...
37 AAAAB {@{Name=040; Q1=A; Q2=A; Q3=A; Q4=A; Q5=B},...
28 AAABA {@{Name=053; Q1=A; Q2=A; Q3=A; Q4=B; Q5=A},...
4 AAABB {@{Name=175; Q1=A; Q2=A; Q3=A; Q4=B; Q5=B},...
40 AABAA {@{Name=005; Q1=A; Q2=A; Q3=B; Q4=A; Q5=A},...
1 AABAB {@{Name=840; Q1=A; Q2=A; Q3=B; Q4=A; Q5=B}}
2 AABBA {@{Name=174; Q1=A; Q2=A; Q3=B; Q4=B; Q5=A},...
43 ABAAA {@{Name=015; Q1=A; Q2=B; Q3=A; Q4=A; Q5=A},...
2 ABABA {@{Name=070; Q1=A; Q2=B; Q3=A; Q4=B; Q5=A},...
1 ABABB {@{Name=たいだ; Q1=A; Q2=B; Q3=A; Q4=B; Q5=...
34 BAAAA {@{Name=011; Q1=B; Q2=A; Q3=A; Q4=A; Q5=A},...
3 BAAAB {@{Name=365; Q1=B; Q2=A; Q3=A; Q4=A; Q5=B},...
3 BAABA {@{Name=まじめ; Q1=B; Q2=A; Q3=A; Q4=B; Q5=...
3 BABAA {@{Name=238; Q1=B; Q2=A; Q3=B; Q4=A; Q5=A},...
2 BBAAA {@{Name=139; Q1=B; Q2=B; Q3=A; Q4=A; Q5=A},...
2 BBBAA {@{Name=540; Q1=B; Q2=B; Q3=B; Q4=A; Q5=A},...
-join で結合するのもいいか。お好みでどうぞ。
# 全ての回答の組み合わせごとに集計する（-join で結合する） Import-Csv -LiteralPath .\survey_result.csv -Encoding UTF8 | Group-Object -Property {$(($_.Q1, $_.Q2, $_.Q3, $_.Q4, $_.Q5) -join '')} | Sort-Object -Property Name | Where-Object {$_.Name.Length -eq 5}
回答の組み合わせが何通りになっても、{"$($_.Q1)$($_.Q2)$($_.Q3)$($_.Q4)$($_.Q5)"} の部分を書き換えればワンライナーで全組み合わせを集計できます。素晴らしき汎用性。
一応こういうのも書きました。汎用性。ちょっと遅いですね。
# 'Name' 以外の全てのプロパティの組み合わせごとに集計する # プロパティの並びは五十音順になる Import-Csv -LiteralPath .\survey_result.csv -Encoding UTF8 | Group-Object -Property {ForEach-Object {$target = $_; ($_ | Get-Member -MemberType NoteProperty | Where-Object {$_.Name -ne 'Name'} | ForEach-Object {$_.Name | ForEach-Object {$target.$_}}) -join ''}} | Sort-Object -Property Name
ポイントは、Group-Object コマンドレットの Property パラメータでスクリプトブロックを使うことです。こうすることで、「Q1 から Q5 の回答を結合したもの」というプロパティを新たに作成してグループ化しています。
計算されたプロパティ
Group-Object コマンドレットの Property パラメータでスクリプトブロックを使いました。これを 'calculated properties' （計算されたプロパティ）と呼ぶそうです。そのオブジェクトが元々持っているプロパティを使って、新しいプロパティを作ることができます。
Group-Object における使い方は以下のページをご覧ください。
- Group-Object (Microsoft.PowerShell.Utility) - PowerShell | Microsoft Docs
- Group-Object (Microsoft.PowerShell.Utility) - PowerShell | Microsoft Docs の -Property パラメータの箇所
そうか、「PowerShell でフォルダサイズの一覧を取得する:3.0+1.0 - タイダログ」や「【総選挙】高校教師が最もよく使うExcel関数は？【プログラム編】 - タイダログ」で使った Select-Object @{label="..."; expression={...}} も calculated properties だったんだ。ようやくわかりましたよ。
Group-Object の場合 expression はなくてもいいようですね。
サンプル
このようなファイルがあるディレクトリで、以下のコードを試してください。
apple.png caramel.png cookie.png mushroom.png pear.png persimmon.png potato.png pumpkin.png
Get-ChildItem -File | Group-Object -Property {$_.Name[0]}
-Property {$_.Name[0]} で、「ファイル名の1文字目」というプロパティを作成し、それでグループ化します。$_ を使えるのがいいですね。
結果はこのようになるはずです。
Count Name Group
----- ---- -----
1 a {apple.png}
2 c {caramel.png, cookie.png}
1 m {mushroom.png}
4 p {pear.png, persimmon.png, potato.png, pumpkin.png}
お次はこれを試してください。
Get-ChildItem -File | Select-Object -Property Name, @{label="Initial"; expression={$_.Name[0]}}
こうなるはずです。
Name Initial ---- ------- apple.png a caramel.png c cookie.png c mushroom.png m pear.png p persimmon.png p potato.png p pumpkin.png p
Select-Object の場合、作成したプロパティは次のコマンドレットに渡して処理できます。
Get-ChildItem -File | Select-Object -Property Name, @{label="Initial"; expression={$_.Name[0]}} | Where-Object {$_.Initial -eq 'p'}
Name Initial ---- ------- pear.png p persimmon.png p potato.png p pumpkin.png p
まとめ
Group-Object で calculated properties を使うことで、全ての回答の組み合わせを一度で集計できる、汎用性のあるワンライナーが書けました。Calculated properties を使えるコマンドレットや、引数としてスクリプトブロックを渡せるコマンドレットは他にもあります。使い方がわかると便利ですよ。
Excel で開いて COUNTIF？ PowerShell 使いたいからね。
後日談
もしかしてと思って Group-Object の Docs を読み返しました。そしたらね、笑っちゃいましたよ。-Property パラメータはもともと複数プロパティを指定できるんですって。ちゃんと Type: Object[] って書いてありますね。まさに車輪の再発明。
Group-Object (Microsoft.PowerShell.Utility) - PowerShell | Microsoft Docs の -Property パラメータの箇所
つまり、私はこんなことをしていましたが、
Import-Csv -LiteralPath .\survey_result.csv -Encoding UTF8 | Group-Object -Property {"$($_.Q1)$($_.Q2)$($_.Q3)$($_.Q4)$($_.Q5)"} | Sort-Object -Property Name
これでいいようです。
Import-Csv -LiteralPath .\survey_result.csv -Encoding UTF8 | Group-Object -Property Q1, Q2, Q3, Q4, Q5 | Sort-Object -Property Name
出力は少し変わります。
Count Name Group
----- ---- -----
1 , , , , {@{Name=981; Q1=; Q2=; Q3=; Q4=; Q5=}}
1 , A, A, A, A {@{Name=816; Q1=; Q2=A; Q3=A; Q4=A; Q5=A}}
1 A, , A, A, A {@{Name=530; Q1=A; Q2=; Q3=A; Q4=A; Q5=A}}
1 A, A, A, A, {@{Name=623; Q1=A; Q2=A; Q3=A; Q4=A; Q5=}}
790 A, A, A, A, A {@{Name=003; Q1=A; Q2=A; Q3=A; Q4=A; Q5=A},...
37 A, A, A, A, B {@{Name=040; Q1=A; Q2=A; Q3=A; Q4=A; Q5=B},...
28 A, A, A, B, A {@{Name=053; Q1=A; Q2=A; Q3=A; Q4=B; Q5=A},...
4 A, A, A, B, B {@{Name=175; Q1=A; Q2=A; Q3=A; Q4=B; Q5=B},...
40 A, A, B, A, A {@{Name=005; Q1=A; Q2=A; Q3=B; Q4=A; Q5=A},...
1 A, A, B, A, B {@{Name=840; Q1=A; Q2=A; Q3=B; Q4=A; Q5=B}}
2 A, A, B, B, A {@{Name=174; Q1=A; Q2=A; Q3=B; Q4=B; Q5=A},...
43 A, B, A, A, A {@{Name=015; Q1=A; Q2=B; Q3=A; Q4=A; Q5=A},...
2 A, B, A, B, A {@{Name=070; Q1=A; Q2=B; Q3=A; Q4=B; Q5=A},...
1 A, B, A, B, B {@{Name=たいだ; Q1=A; Q2=B; Q3=A; Q4=B; Q5=...
34 B, A, A, A, A {@{Name=011; Q1=B; Q2=A; Q3=A; Q4=A; Q5=A},...
3 B, A, A, A, B {@{Name=365; Q1=B; Q2=A; Q3=A; Q4=A; Q5=B},...
3 B, A, B, A, A {@{Name=238; Q1=B; Q2=A; Q3=B; Q4=A; Q5=A},...
2 B, B, A, A, A {@{Name=139; Q1=B; Q2=B; Q3=A; Q4=A; Q5=A},...
2 B, B, B, A, A {@{Name=540; Q1=B; Q2=B; Q3=B; Q4=A; Q5=A},...
どっちの書き方でもあんまり変わんないじゃんという気もしますが、早とちりで回り道していることが問題なのですよ。私の悪い癖です。Group-Object の使い方について正確ではない情報を掲載してしまいました。すみません。
とはいえ、複数プロパティを指定するやり方だと未回答を除くのがややこしくなるので、その場合は上で書いた方法を使うといいかもしれません。ちょっといい車輪ができました。また、-Property パラメータで calculated properties が使えることが分かりました。ひとつの早とちりでふたつを得たのです。素晴らしい早とちりじゃないか。
そういうことにさせてください。
教訓
- ことあるごとに「もっといい方法があるのでは」と考えよう。
- ちゃんとドキュメントを読もう。前にも同じことをしてるよ。
