Power Query による分析データの再分析 (Excel & HTML 入力編)


現場社員が自らデータ分析を行うための新しいサービスである Power BI for Office 365 の Power Query を利用して、公開されているデータを利用してみましょう。前回に引き続いての内容ですが、今回は、様々な府省が管理している統計データが公開されている「政府統計の総合窓口 (e-Stat)」のデータを利用します。

政府統計の総合窓口(e-Stat)
http://www.e-stat.go.jp/

今回、調査するのは、都道府県別の大卒者の正規就職率と教育費の関係性です。
利用するデータは、それぞれ以下のページから Excel ファイルHTML で取得可能であり、後者の Web サイト「都道府県別統計とランキングで見る県民性」では e-Stat からも取得可能な家計調査のデータを分析して掲載しています。

平成 25 年度の都道府県別 状況別 卒業者数
http://www.e-stat.go.jp/SG1/estat/List.do?bid=000001051739&cycode=0

学習塾・予備校費用 [ 2008年第一位 埼玉県 ]|新・都道府県別統計とランキングで見る県民性 [とどラン]
http://todo-ran.com/t/kiji/11604

はい、そうです。いずれも、既にある角度から分析されたデータです。ここでは、データ間の関係性を見るために、複数の分析されたデータを元に再度分析を実施します。

何かのデータ分析の結果を得るには、データ間の関係性を確認することは重要ですが、これを行うには、「それぞれのデータが何処かに存在することを知っている、あるいは知ることが出来る」「試行錯誤でデータ間の関係性を確認する」ことが重要であり、これを行えるのはデータ分析の専門家か、データの意味をよく知っている人であることが必要です。Power BI for Office 365 は、データの意味をよく知っている人 (= データ分析の専門家ではない人) が、データの在り処を知り、試行錯誤でデータ分析を行うためのサービスとなっています。

完成すれば、以下のような都道府県名、教育費や正規就職率でフィルターを掛けることが可能な分布図が完成します。なお、今回は Power Query の利用手順をかなり簡素化して記載していますので、必要な方はこちらから完成した Excel ファイルをダウンロードして確認してください。

image

事前準備

Power Query で拡張子が .xls の Excel ファイルを取得するために、事前に ACE (Microsoft Access データベース エンジン) 2010 SP1 以降のプロバイダを以下より取得してインストールしておきます。

Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント
http://www.microsoft.com/ja-jp/download/details.aspx?id=13255

正規就職率の取得

  1. [POWER QUERY] タブの [外部データの取り込み] – [ファイルから] – [Excel から] を選択し、[ファイル名] に以下の URL を入力し、[OK] をクリック
    http://www.e-stat.go.jp/SG1/estat/Xlsdl.do?sinfid=000023610209
    image
  2. クエリー エディターが開くので、[バイナリ] タブの [開く] – [形式を指定してファイルを開く] から [Excel ブック] を選択します
    image
  3. シートが表示されるので、1 行目 (Name 列の値が「73(2-1)」の行) の Data 列に書かれている「Table」をクリックします
    image
  4. シートの内容が表示されるので、順に以下の操作を行います
    image
    1) 不要な列 (Column2, 4, 5, 6, 8, 9 以外の列) を削除
    2) 列名の変更
    3) [上位の行の削除] から最初の 10 行を削除
    4) 「元都道府県」列から値が「null」と「都道府県別は…」の行を対象外に
    image
    5) 列のデータ型の変更
    6) 正規就職率の列を追加
    image
    7) [挿入] タブの [カスタム列の挿入] から、「都道府県」列を以下の式で追加 (元の都道府県名を正規化しています)
    Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Remove([元都道府県名], “ ”) & “県”, “北海道県”, “北海道”), “大阪県”, “大阪府”), “京都県”, “京都府”), “東京県”, “東京都”)
    8) [名前] を「都道府県別正規就職率」、[ワークシートへの読み込み] のチェックを外し、[データ モデルへの読み込み] にチェックを入れたら [ファイル] タブの [適用して閉じる] を選択します
    image

都道府県別の教育費の取得

  1. [POWER QUERY] タブの [外部データの取り込み] – [Web から] を選択し、[URL] に以下の URL を入力し、[OK] をクリック
    http://todo-ran.com/t/kiji/11604
    image
  2. ナビゲーター ペインに HTML を解析した結果の構造が表示されるので、[Table 0] を選択します
    image
  3. シートの内容が表示されるので、順に以下の操作を行います
    image
    1) 余分な列 (1 列目と 4 列目) を削除
    2) 列名の変更
    3) 最初の 2 行を削除
    4) 「教育費」列を以下の数式で追加し、データ型を数値型に
    Text.Remove([単位付き教育費], “円”)
    5) 「都道府県」列から値が「全国」の行を対象外に
    6) [名前] を「都道府県別教育費」、[ワークシートへの読み込み] のチェックを外し、[データ モデルへの読み込み] にチェックを入れたら [ファイル] タブの [適用して閉じる] を選択します
    image

2 つのクエリの結合

ここまでで作成した 2 つのクエリを結合して、一つのデータ モデルを作成します。結合したデータ モデルを作成しておくと、Power View や Power Map によってデータをグラフや地図上に表示する際に操作が行い易くなります。
RDBMS のリレーションの作成と似ていますが、実際には RDBMS の 2 つの表を結合して、新しい表を作成しているイメージになります。

  1. [POWER QUERY] タブの [結合] – [マージ] を選択し、作成した 2 つのモデルと「都道府県」列を選択し、[OK] をクリック
    image
  2. シートの内容が表示されるので、順に以下の操作を行います
    image
    1) 「NewColumn」列右側のアイコンをクリックし、「教育費」のみを選択します
    image
    2) 列名を「教育費」に変更します
    3) 余分な列 (「正規就職率」「都道府県」「教育費」以外の列) を削除します
    4) 「都道府県」列をドラッグして一番左へ移動し、ドロップし、[名前] を「正規就職率と教育費」、[ワークシートへの読み込み] のチェックを外し、[データ モデルへの読み込み] にチェックを入れたら [ファイル] タブの [適用して閉じる] を選択します
    image

レポートの作成

レポートの作成には、いつものように Power View を利用します。

  1. [挿入] – [レポート] – [パワー ビュー] でパワー ビューのシートを追加します。
  2. [正規就職率と教育費] モデルから、以下の設定の散布図視覚エフェクトを追加します。
    image
  3. 最後に、以下の設定の集合縦棒グラフ視覚エフェクト (レポート下) を追加すれば完成です。
    image

ここまでの手順で、以下のようなレポート画面が表示されているはずです。
このグラフによって、実は、教育費を掛けたからといっても正規就職率が上がるわけではないこと (教育費と正規就職率には明確な相関関係がないこと) がわかります。
image

教育費の大小ではなく、どちらかと言えば、地方に住む人の方が正規就職率は高く、
image

沖縄県の正規就職率は他と比較しても非常に低いことや、image

北海道と沖縄県を比較すると、真逆に位置していることなども見えてきます。
image

データ分析は続くよ

容易に思いつく分析の視点として、正規就職率が高くても給与が低いのではないか、男女の差はどうなのか、複数年分のデータを見て過去からの推移を確認するなどが挙げられます。今回のサンプルではここまでですが、同じようにデータを見つけて手順を繰り返せば、きっと手に入れたいデータ分析の結果が得られることでしょう。

また、Power BI for Office 365 の機能をフルに使えば、Power BI データ カタログ機能で会社の中で誰かが作成した Power Query によるクエリを再利用したり、Power BI Q&A 機能で誰かが作成した Excel ファイル内のデータ モデルから簡単にグラフを作成することが可能です。ひとりで頑張るのは時間が掛かってしまい効率的ではありませんので、データ分析はみんなで行うことをお薦めします。

広告