Excelを利用した7市町オープンデータの分析②・相関分析

相関分析とは

相関分析とは，2つの変数XとYの関係性を分析する方法です． 例えば，暑い日にはアイスクリームの売上数が増加すると考えられます． このとき，「X:気温」と「Y:アイスクリームの売上数」には関係がありそうです． 一般に，相関分析には相関係数と呼ばれる評価指標が用いられます（正確にはピアソンの積率相関係数と呼ばれる）． 相関係数は-1から1の範囲で与えられ，1に近いほど正の相関，-1に近いほど負の相関を示します．

相関係数	相関の強さ
0.7～1	強い正の相関
0.4～0.7	正の相関
0.2～0.4	弱い正の相関
-0.2～0.2	相関なし
-0.4～0.2	弱い負の相関
-0.7～-0.4	負の相関
-1～-0.7	強い負の相関

正の相関は，Xが増加するとYも増加する関係を表し，負の相関は，Xが増加するとYが減少する関係性を表します． 先の「気温」と「アイスクリームの売上数」の例では，気温が増加すると，売上数も増加するので正の相関となります． 相関係数はアンケートの分析などでも用いられるメジャーな分析手法の一つです． ここでは，日進市のオープンデータを対象に相関分析を適用してみましょう．

データの準備

前回の授業で日進市の人口ピラミッドを作成しました． グラフから，日進市には子育て世帯が多く，幼稚園や保育園などが充実した地区が人気であることが予想されます． そこで，町別人口・世帯数と子育て支援施設のデータを対象に，相関関係があるかを調べてみましょう．

それでは，日進市のオープンデータミュージアムから 上記の2種類のデータをダウンロードしましょう． まずは，オープンデータミュージアムで公開されている行政活動情報の町別人口・世帯数のデータをダウンロードします． 行政活動情報のページにアクセスし， 平成30年1月1日のCSV形式のデータをダウンロードしてください． このデータには，日進市内の各町（赤池町，浅田町など）の人口や世帯数が含まれています．

次に7市町オープンデータのフォーマットに従った子育て支援施設のデータをダウンロードします． 7市町オープンデータ共通整備項目にアクセスし， CSV形式のデータをダウンロードしてください． このデータには，日進市内にある幼稚園や保育園などの子育て支援施設の名称や住所が含まれています．

相関係数の算出

それでは，ダウンロードしたデータをエクセルで読み込み相関係数を算出してみましょう． まずはエクセルを起動して，空白のブックを選択します． セルA1をアクティブにした状態で，データ・タブのテキストまたはCSVからをクリックします． ダイアログが表示されたら，ダウンロードした町別人口・世帯数のファイルを選択し，インポートをクリックします． プレビュー画面で，編集をクリックし，クエリエディタを開きます． ここでは，1行目から3行目の不要なデータを削除してしまいます． 削除後に，1行目をヘッダーとして使用をクリックして，最後に，閉じて読み込むをクリックします． 読み込み後に，下記のように表示されていることを確認してください．

同様の手順で子育て支援施設のデータを読み込みます． プレビュー画面で，編集をクリックし，クエリエディタを開きます． ここでは，名称と住所表記の列だけを残し，その他の列は削除してしまいましょう． 削除後に，1行目をヘッダーとして使用をクリックして，最後に，閉じて読み込むをクリックします． 読み込み後に，下記のように表示されていることを確認してください．

次に，各町に存在する子育て支援施設の数を数えます． 町別人口・世帯数のシートのF列に，countif関数を用いて施設数をカウントします． セルF2をアクティブにした状態で，関数の挿入をクリックし，countif関数を選択します．

countif関数は条件を満たすセルの数をカウントするために用いられます． ここでは，「町名が住所表記に含まれる」という条件を満たすセルをカウントします． 下図を参考に範囲，検索条件を入力してください． ここで，範囲は子育て支援施設のシートのB2:B73を指定しています． また，検索条件の「*(アスタリスク)」はワイルドカードと呼ばれ， 任意の文字列を表しています（赤池は赤池町を含むため，二重でカウントされてしまうが，簡単化のため，ここでは考慮しない）．

カウントした結果が下図と一致することを確認してください． 赤池町は3，浅田町は5となっています．

まずは，散布図を作成して，全体の傾向を可視化しましょう． E列の世帯数とF列の子育て支援施設数を選択して，挿入タブから散布図を選択してください． このとき，合計の行を含めないように注意してください． 作成した散布図が下記と一致することを確認してください．

ここで，近似曲線を追加します． データ系列を選択して，右クリックし，近似曲線の追加（線形近似）を選択します． 近似直線が右上がりであることから，世帯数と子育て支援施設数には，正の相関があることが読み取れます．

最後に分析ツールを用いて相関係数を算出しましょう． データ・タブのデータ分析をクリックし，相関を選択します． データ分析が表示されていない場合は， 【ファイル】-【オプション】-【アドイン】-【Excelアドイン】の設定をクリックし， 分析ツールのチェックボックスにチェックを入れてください． 入力範囲はE1:F28，先頭行をラベルとして使用にチェックを入れ， 出力先をH2に設定して，OKをクリックします．

この結果，世帯数と子育て支援施設数の相関係数は0.43と算出されました． この値は正の相関であることを意味しています． つまり，子育て支援施設が多い地区は，その地区に住む世帯数も多いということが示されました． しかし，このルールに該当しない例外的な地区もあるようです． 例えば，折戸町は1073世帯と決して多くはありませんが，12の子育て支援施設が存在しています． このように統計において他の値から大きく外れた値は外れ値と呼ばれます． 外れ値が大きな意味合いを持つケースも存在するので，この理由を調査してみるのも面白いと思います．