Pearson 相関 エクセル編
エクセルのアドイン機能「相関」を使用してPearson相関係数を算出、ピボットグラフでビジュアル化する手順を解説しています。
分析ツールをアドインして計算
データについて
顧客コード,バスケット,sales,unit,グループコード,グループ名称 19014 ,20181103_00030296,20,1,1,青物 6349 ,20181107_00042474,38,1,1,青物 5744 ,20181101_00013161,39,1,1,青物 19326 ,20181101_00024108,39,1,1,青物 4169 ,20181101_00040497,39,1,1,青物 455 ,20181102_00013233,39,1,1,青物
総行数:93,760(ヘッダー行を含む)
バスケット数:20,653
顧客コード数:4,301
グループ名称数:20
sales合計:37,354,518
unit合計:201,912

最小集計粒度は「バスケット」
「顧客コード」でも集計できます。
テキストファイルをエクセルへ変換

・「区切り文字」は「コンマ」です。

・「顧客コード」は「文字列」です。(念のため)
ピボット

・「挿入」タブ
・「ピボットテーブル」を選択します。

・「行」=「バスケット」
・「列」=「グループ名称」
・「値」=「sales」

横計・縦計の総計を削除します。
分析ツールをアドイン

「ファイル」タブからホーム画面へすすみます。
・「その他」をクリックし「オプション」を選択します。

・左側のメニューから「アドイン」を選択
・「分析ツール」→「設定」の順にクリックします。

窓が開いたら
・「分析ツール」をチェックして「OK」
Pearson相関係数を計算

・「データ」タブ→「データ分析」
・「相関」を選択します。

・入力範囲を設定します。
ピボットテーブルの左端列(バスケット列)を除くすべてのデータ範囲です。総計列・総計行が残っているときは、入力範囲から除外してください。
・「データ方向」=「列」
・「先頭行をラベルとして使用する」へチェック
・「新規ワークシート」へ出力しましょう。

クロス集計表の下三角形へ数値が出力されれば完成です。
結果をビジュアル化する
列指向形式へ変換

表をコピーします。

あたらしいシートへ
・行列を入れ替えてペーストします。

空欄へコピーもとの値を入れこみます。
画像のように「値1」をはさんで対角線上に同一値が入ります。

すべてのセルを値で埋めます。

・対角線上の「値1」をデリートします。
・念のため、すべての値を値貼り付けします。

・A列名を「グループ名称」にします。
・「データ」タブ→「テーブルから」をクリックします。

Power Query エディターが開きます。
・「グループ名称」の列を除くすべての列を選択
・「変換」タブ→「列のピボット解除」をクリックします。

列名を変更します。

・「ファイル」タブ→「閉じて読み込む」
ピボットグラフ

列指向形式へ変換できたら
・「挿入」タブ→「ピボットグラフ」

・「行」=グループ名称
・「列」=相手方グループ名称
・「値」=Pearson

・「スライサー」=「相手方グループ名称」

・値(Pearson)を降順に並び替えます。

これで完成です。
検証
散布図作成

はじめに作成したピボットテーブルへ戻ります。バスケットごとにsalesを集計したピボットテーブルです。
・バスケットの列、縦総計・横総計を除く全体をコピーします。

・あらたしいシートへペーストします。

・2列を選択します。(例:「菓子」「アイスクリーム」)
・「挿入」タブ→「散布図」をクリックします。

・+マークをクリック
・「近似直線」へチェック
・「その他オプション」→「グラフに数式を表示する」「グラフにR-2乗値を表示する」をチェックします。

・縦軸のスケールを右クリック
・最大値を入力して見やすくします。

・R2の値を確認しましょう。

R2=Pearsonの2乗です。
顧客コードで集計すると結果は違う

もとデータです。

・「顧客コード」で集計します。

バスケットと同様の手順で棒グラフを作成します。

バスケットから計算したPearsonと顧客コードから計算したPearsonを比較すると違いがあります。
バスケットと顧客コードの違い

画像のようなデータがあるとします。
・バスケットのユニークカウント数=5
・顧客コードのユニークカウント数=2

バスケットを集計レベルとして散布図を作成すると、
「精肉」と「惣菜」の両方が入っているバスケット数は4です。
したがって散布図へプロットされるデータ点の数は4です。

顧客コードを集計レベルとして散布図を作成すると、
「精肉」と「惣菜」の両方が入っている顧客コード数は2です。
したがって散布図へプロットされるデータ点の数は2です。
計算のもとになる散布図(相関図)が違うから相関係数も違うということです。