この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは。Alteryx勉強中のスズです。
社内のAlteryxチームでは、1つのお題に対して各自でワークフローを持ち寄ることがあり、その様子をプチハッカソンとしてブログで紹介しています。今回のお題は「Alteryx Designerで偏差値を求める」です。
プチハッカソン 偏差値を求める編
偏差値の計算って？
はじめに偏差値の計算方法についてご紹介します。
- 平均を計算します。これは全部の値を足して件数で割ります。
- 分散を求めます。これは各値から平均値を引いたものを2乗したものの合計値を計算してデータ件数で割る(標本分散)またはデータ件数-1で割る(不偏分散)します。
- 標準偏差を求めます。これは√分散で計算します。
- 各値に対して平均を引いてから標準偏差で割ると、平均0, 標準偏差1になるように変換されます。 これで難しいテストと簡単なテストや国語と数学のテストのように平均点が異なるもの同士でも比較できるようになります。
- この値に10をかけて50を足すことで、平均50, 標準偏差10になるように調整できます。
- これが偏差値です。
お題
以下の画像にある「score_a」「score_b」「score_c」の偏差値を求めます。
解答の形式として、元の値、平均値、標準偏差（標本標準偏差）、偏差値を出力します。
Designerでは集計ツールで標準偏差を求めることができますが、これはExcelのSTDEV.S関数相当となり、不偏標準偏差となります。ExcelのSTDEV.P関数相当の標本標準偏差を求める処理を行いたい場合、計算ロジックそのものをワークフローで組み立てることになります。
また、集計ツールでは分散を求めることができますが、不偏分散になります。標本分散で計算する場合は、集計ツールの分散を使うのではなく、計算する必要があります。
解答1
まずは私の解答、偏差値の計算方法を調べながらこつこつと作ったワークフローになります。データを縦持ちに変換したのち、計算を行ってデータを結合という処理を繰り返しています。初めにデータを縦持ちに変更した分、クロスタブツールでそれぞれの計算結果を横持ちに変換して結合する、という手間がかかっています。
それぞれのパートに分けて簡単に紹介していきます。
平均値の出力
- 転置ツールで縦持ちに変換
- 集計ツールで平均値を出力
- 結合ツールで集計ツールの前のデータと結合
標準偏差の出力
- フォーミュラツールで平均値との差の平方数を出力
- 集計ツールで平均との差の平方数の平均（標本分散）を出力
- フォーミュラツールでSQRT関数を用いて、分散から標準偏差を出力
- 結合ツールで集計ツールの前のデータと結合
偏差値の出力
- フォーミュラツールで偏差値を出力
- クロスタブツールで元の値、平均値、標準偏差、偏差値に分けて横持ちに変換
- 複数結合ツールで4つのデータを結合して列名を修正
解答2
続いてじょんすみすの回答。こちらも計算ロジックそのものをワークフローで組み立てたものになります。先ほどの解答1と比べると、各列に対してそれぞれ計算処理を設定しているため、転置ツールやクロスタブツールが不要になっています。
平均値の出力
- 集計ツールで各列の平均を出力
- フィールド付加ツールで元のデータと結合
標準偏差の出力
- フォーミュラツールでPOW関数を使用して、「元の値-平均値」の2乗を出力
- 集計ツールでそれぞれの値の合計とレコード数を出力
- フォーミュラツールでSQRT関数を使用して標本分散から標準偏差を出力
- フィールド付加ツールで元のデータと結合
偏差値の出力
- フォーミュラツールで偏差値を出力
解答3
最後に兼本の回答。集計ツールとフォーミュラツールで計算を行うすっきりとしたワークフローになっています。
集計ツールを使用して、平均、標準偏差（ExcelのSTDEV.S関数相当）、分散、非Nullのカウントを出力します。なお、ここで作成した標準偏差（ExcelのSTDEV.S関数相当）は、このあとの処理では不要となります。
フィールド付加ツールで元のデータと結合します。結合した結果は以下のようになっています。
フォーミュラツールを使用して、標準偏差と偏差値を出力します。標準偏差を出力するためSQRT関数で標本分散を使用しますが、前の集計ツールでは不偏分散を出力しています。不偏分散を標本分散に直すため、不偏分散にデータ件数-1をかけてデータ件数を割る（[分散_score_a] * ([非Nullのカウント_score_a] - 1) / [非Nullのカウント_score_a]）という計算を行っています。
あとは選択ツールで不要な列の削除と列名の修正を行って完成です。
最後に
今回は「Alteryx Designerで偏差値を求める」のプチハッカソンの様子をご紹介しました。
Alteryxの導入なら、クラスメソッドにおまかせください
日本初のAlteryxビジネスパートナーであるクラスメソッドが、Alteryxの導入から活用方法までサポートします。14日間の無料トライアルも実施中ですので、お気軽にご相談ください。
5
