厚労省「地域ごとのまん延の状況に関する指標等」の PDF から Power BI Desktop でデータを抽出し EXCEL のグラフに表現する

 新型コロナウイルスのパンデミック宣言以降,Twitter でフォローしているアカウントに自然と相互協調の動きがみられる.

 このツイートから始まった一連のやりとりで,厚労省の発表した PDF からテーブルを抽出するくだりに注目した.

 今回はここを画像つきで実施してみた.

PDF への接続

 データソースは地域ごとのまん延の状況に関する指標等の公表についてである.ファイル名は 2020 年 4 月 24 日時点で 000624135 となっている.

EXCEL の Power Query では PDF に接続できない

 PDF ファイルからデータを抽出したくても,EXCEL の Power Query からではできない.そのため Power BI Desktop を使う必要がある.

 Power BI Desktop のインストールについてはこちらを参照されたい.

Power BI Desktopを起動

Power BI Desktop起動画面
Power BI Desktop起動画面

「ホーム」「データを取得」「詳細」

 ホームタブの「データを取得」を押下し最下部の「詳細…」を選ぶ.

「ホーム」「データを取得」「詳細」
「ホーム」「データを取得」「詳細」

「ファイル」「PDF」

 「データを取得」ウィンドウで「ファイル」から「PDF」を選ぶとファイルパスを指定するよう促される.指定して OK をクリックする.

「データを取得」「ファイル」「PDF」
「データを取得」「ファイル」「PDF」

Table001(Page 1)のチェックを入れ、「データの変換」

 下図のようにナビゲーターウィンドウが開く.チェックボックスが 2 つあるので Table001(Page 1) にチェックを入れ,「データの変換」をクリックする.

Table001(Page 1)のチェックを入れ、「データの変換」
Table001(Page 1)のチェックを入れ、「データの変換」

Power Query による加工

「変換」「1行目をヘッダーとして使用」

 ここからデータの整形に入る.主な作業はピボット解除である.その前にヘッダー行を指定する.「1行目をヘッダーとして使用」を選ぶ.

 
「変換」「1行目をヘッダーとして使用」
「変換」「1行目をヘッダーとして使用」

「確定日」列を選択したまま右クリックし「その他の列のピボット解除」

 ピボット解除すべき列の数が不定の場合には,解除しない列を指定しておいて「その他の列のピボット解除」を使う.

「確定日」列を選択したまま右クリック-「その他の列のピボット解除」
「確定日」列を選択したまま右クリック-「その他の列のピボット解除」

 結果はこうなる.「属性」「値」という名前の列ができる.「属性」列のフィールド内に「前日との差」という値が見える.今回,これはグラフ化の邪魔になるので削除したい.

ピボット解除後「属性」「値」という列ができる
ピボット解除後「属性」「値」という列ができる

「属性」列のフィルター▼をクリックし「テキスト フィルター」「指定の値を含まない」

 そこで列のフィルターを使って除外する.「テキストフィルター」のうち「指定の値を含まない」である.

「テキストフィルター」で「指定の値を含まない」
「テキストフィルター」で「指定の値を含まない」

「前日との差」とタイプ入力し「OK」

 「行のフィルター」ウィンドウが開くので『前日との差』とキーボードからタイプ入力し,OK をクリックする.

「行のフィルター」に『前日との差』とタイプ入力
「行のフィルター」に『前日との差』とタイプ入力

 結果はこうなる.

テキストフィルターの結果
テキストフィルターの結果

「ホーム」「閉じて適用」

 「閉じて適用」する.

「閉じて適用」
「閉じて適用」

クエリの詳細

 上記操作の記録を記述したクエリである.

Excel での作業

左側の三つのアイコンのうち真ん中の表をクリック

 ウィンドウ左側にあるアイコンは上から順にレポート,データ,モデルという.アイコンの形状から想像できるが,それぞれデータの構造を象徴している.レポートは EXCEL ではグラフに該当するが,表現力は正直 Power BI の方が上である.

 今回は EXCEL へのデータ移行を目的とするため,真ん中のデータアイコンをクリックし,テーブルを表示する.

左側の三つのアイコンのうち真ん中の表をクリック
左側の三つのアイコンのうち真ん中の表をクリック

右側フィールドの中の『Table001 (Page1 )』を右クリックし「テーブルのコピー」

 右クリックしても,右側の縦 3 つの点をクリックしても同じメニューが出現するが,「テーブルのコピー」を選ぶ.クリップボードにデータがコピーされる.

右側フィールドの中の「Table001 (Page1 )」を右クリック
右側フィールドの中の「Table001 (Page1 )」を右クリック

Excel へ貼り付け

 EXCEL のワークシートに貼り付け,テーブルに変換する.確定日が月と日しかないため,右隣のセルに

とタイプし,さらに右隣のセルに DATEVALUE 関数を適用して確実に年月日として確定する.

 人数が文字列として認識されている場合は

などと数式を使って数値と認識させる.共に「値のみ貼り付け」してセルの参照関係を解消しておく.

折れ線グラフの追加

 新規ワークシートを追加し,空白の折れ線グラフを追加作成する.

データ系列の追加

 グラフ内部を右クリックして「データの選択…」を選び,系列を 47 個追加して,テーブルの各都道府県名を「系列名」に指定し,確定患者数のセル範囲を「値」に指定する.横軸ラベルの「編集」をクリックし,全国の「確定日」のセル範囲を指定する.

データ系列の追加
データ系列の追加

 縦軸の書式は初期の「線形」のままではなく,「対数」にしておく.これには疫学的理由があるが,詳細には踏み込まないでおく.

書式を設定するコード

 下記コードを標準モジュールに記述し,F5 を押下して実行する.

結果

新型コロナウイルス確定患者数の推移

 結果を示す.筆者は疫学が専門ではないため,考察には踏み込まず,専門家に委ねる.

各都道府県の新型コロナウイルス確定患者数の推移
各都道府県の新型コロナウイルス確定患者数の推移

まとめ

 厚労省の公開した PDF ファイルを元に各都道府県のコロナウイルス確定患者数の推移を折れ線グラフに表現した.

 PDF ファイルからのデータ抽出には EXCEL の Power Query は未対応で,Power BI Desktop が必要であった.厚労省には csv ファイルまたは EXCEL でファイル形式でのデータ公開を求めたい.

 方法はすでに Akira Takao (@modernexcel7) さんが公開してくれている.筆者はその方法を画像つきで解説した.

追加情報:空のクエリ

 さらに Akira Takao さんから追加情報が寄せられたので紹介しておく.

「データの取得」「その他のデータソースから」「空のクエリ」

空のクエリ
空のクエリ

ファイルパスの指定

 ファイルパスを指定するよう促されるので入力する.

ファイルパスの指定
ファイルパスの指定

 ファイルパスが正しければ下図のようにファイルのアイコンが示される.

正しいファイルパスを指定した結果
正しいファイルパスを指定した結果

ファイルをダブルクリックして開く

 ダブルクリックして開くと見慣れた Power Query 画面へと遷移する.

ファイルアイコンをダブルクリックして開く
ファイルアイコンをダブルクリックして開く

読み込まれるレコード

 読み込まれたのは 2 行のみだが,Id 列をよく見ると Page001 および Table001 とある.ここで必要なのはテーブルの方である.

2行が読み込まれる
2行が読み込まれる

上位1行を削除

 「上位の行の削除」で Page001 を削除していく.

上位の行の削除
上位の行の削除
先頭から削除する行数を指定
先頭から削除する行数を指定

 結果として Table001 の行が残る.

2行目だけが残る
2行目だけが残る

Data 列を展開する

 Data という名前の列が展開できそうなので,アイコンをクリックしてみる.

展開する列がチェックされているのでこのままOK
展開する列がチェックされているのでこのままOK

 展開された結果である.ここからは定型的な作業になってくる.

展開された状態.ここから加工していく
展開された状態.ここから加工していく

データの加工

 ID, Name, Kind の3列は不要なので削除する.

不要列を削除
不要列を削除

 「前日との差」という列も不要なので削除する.

不要列の削除
不要列の削除

 このあたりまで来ると見慣れた画面になってくる.「その他の列のピボット解除」を行う.

その他の列のピボット解除
その他の列のピボット解除

 ピボット解除後の状態だが,列名がおかしいので修正しておく.

ピボット解除直後.列名が変だ
ピボット解除直後.列名が変だ

 最後に「閉じて読み込む」と結果はこうなる.

クエリと接続
クエリと接続

詳細クエリ

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください