このページは解答編です。
↓ネ
↓タ
↓バ
↓レ
↓防
↓止
答えと解説
設問はこちらでした。
P-030: レシート明細データ(receipt.csv)に対し、店舗コード(store_cd)ごとに売上金額(amount)の分散を計算し、降順で5件表示せよ。
※不偏分散(与えられたデータが母集団の一部である分散、n-1で割る)をまず出力しましょう。余裕があれば標本分散(与えられたデータを集合全体とした分散、nで割る)にもチャレンジしてください
解答ワークフローは以下のようになります。
分散(Variance)とは
今回は分散を出す問題ですが、ちょっと分散について考えてみたいと思います。
分散とは、標準偏差と同様、値のちらばり具合を示すものです。例えば、今回の問題の標本分散が最大、最小の店舗をヒストグラムで比較すると以下のようになります。右側のS14050の方が値が全体的に左側にあり、左側のS13052は値が若干広がり気味になっていることがわかるかと思います。
この値の散らばり具合を数値にしたものが分散です。
標本分散を求めるための数式はこちらです。
不偏分散の場合は、シグマの結果に対してn(レコード数)で割っていますが、n-1で割れば良いだけです。
なお、標本分散は今計算しているデータに対しての分散になりますが、不偏分散は何かしら元となる集団、つまり母集団から一部抜き取ったデータ(サンプリングされたデータ)と考えた時の母集団の分散となります。
ということは、今回のデータは特に母集団などがあるわけではないので、標本分散を取ればよい、ということになります(100本ノックの解答も標本分散となっています)。
とはいえ、なかなかそこまでわかりにくいので、今回は両方の分散を出してみましょう。
実際にワークフローを作っていきましょう
まず、型の変更からになります。今回は売上金額(amount)を計算していくので、数値型(Int16)にしておきます。不要な項目も削除します。
不偏分散(与えられたデータが母集団の一部である分散、n-1で割る)
不偏分散はAlteryxでは簡単に計算することができます。集計ツールを使うだけです。
以下のような設定となります。store_cdでグループ化し、amountの「分散 – 0を無視する」を選択します。
あとはamount(分散の結果です)で降順にソートし、先頭から5件取得すれば完了です。
実際のワークフローは以下のようになります。
標本分散(与えられたデータを集合全体とした分散、nで割る)
次に、標本分散を出力してみましょう。なぜかAlteryxには標本分散を一発で出す機能がありません。ということで、元の定義からワークフロー化したいと思います。
まず、平均を計算しましょう。集計ツールで各店舗コードごとにグループ化し、平均を取ります。
得られた平均値は、計算に用いるために元のデータに結合する必要があります。以下のように結合ツールで結合します。
結合ツールの設定は以下の通りです。キー列はstore_cdとし、不要な項目も削除します。
これで準備は整いましたので計算を進めていきましょう。
まず、定義の計算式のうち、赤い部分を計算していきましょう。
これは、各amountの値[amount]と平均値[Avg_amount]を引き算し、二乗すればよいです。二乗はPOW関数で可能です。つまり以下の計算式となります。
POW([amount]-[Avg_amount], 2)
なお、POW関数の前側は計算をする値で、後ろの数字はべき乗の乗数を記載します。今回は二乗なので2となります。
フォーミュラツールには以下のようにセットします。フィールド名は「xi-x」、データ型はDoubleとしています。
さて、ここから以下の赤い部分、シグマを計算していきます。シグマは基本的に合計しろ、ということなので、今回の場合は1からNまで、つまり先ほど求めた「xi-x」のすべての合計を行うということなので、集計ツールで合計を求めるだけです。また、ここでnも求めておきましょう。nはレコード数なので集計ツールでカウントを取ればオッケーです。つまり、集計ツールは以下のように設定します。
ここまで求められれば、あとは単純な割り算です。
[Sum_xi-x]/[Count]
フォーミュラツールは以下のように設定しています。
あとは、amountの昇順で並べ替えて先頭から5レコード抽出すれば完了です。実際のワークフローは以下の通りです。
まとめ
今回も、集計の問題でしたが、単なる集計に終わりませんでした。分散を計算したいときに標本分散か不偏分散なのかは理解しておいた方が良いかと思います。Alteryxの集計ツールですぐに出せるのは「不偏分散」であることに注意下さい。
なお、Excelで考えると、VAR.Pが標本分散、VAR.Sが不偏分散です。ちなみに、Excel2007以前はVAR関数というものがあり、VAR.S相当だったようです。何も知らずにVAR使うと不偏分散だった、ということになるかと思います。
解答ワークフローダウンロード
※2022/09/08更新
コメント