365連休

にわかのandroidとかの開発メモ。

Excelの"取得と変換"(Power Query)でオープンデータを読み込む😎

 

前書き

以前書いた取得と変換の記事に思ったよりもアクセスがあるため、活用方法を模索する観点からオープンデータを読み込む実験をします。

厚生労働省からコロナに関連するオープンデータが公開されているので、そちらを使用してみます。

www.mhlw.go.jp

 

なお、今回使用するExcelMicrosoft Office Personal 2016です。

 

 

免責

この記事を読み、読者が何かをした結果、何らかの損害が発生しても筆者は一切の責任を負いません。

自己の責任において実行してください。

 

 

手順

  1. 新規ブック
  2. データを取り込む
  3. データを加工する
  4. シートへ読み込み、グラフを作る

 

実践

 

新規ブック

Excel新規ブックと厚生労働省-コロナオープンデータ
https://www.mhlw.go.jp/stf/covid-19/open-data.html

 

データを取り込む

 

[データ]-[新しいクエリ]-[その他のデータソースから]-[Webから]

 

URL入力
https://covid19.mhlw.go.jp/public/opendata/newly_confirmed_cases_daily.csv

 

認証方式を選択。匿名とは、ログインなどの認証を行うことなくアクセスすること。

 

データプレビュー画面。[データの変換]をクリック。
ここで[読み込み]をクリックすると直ちにシートへ展開されるが、
今回は引き続きデータを加工したいため[データの変換]からPower Query エディタを起動する。

 

Power Query エディタが起動する。Webから取得したcsvを元にクエリが作成された。
"ソース"(Webデータ取得)、"昇格されたヘッダー数"(先頭行ヘッダー設定)、"変更された型"(データ型設定)の3ステップで読み込まれている事がわかる。これらはステップ名で、中身はデータビュー上部の数式バーのような入力欄にPower Queryという専用言語で表示されている。

 

クエリ名としてCSVファイル名が設定されているが、分かりづらいため「新規陽性者数の推移(日別)」へ変更する。

 

名称変更完了。

 

他の3データも読み込み、名前を変更する。

入院治療等を要する者等推移 https://covid19.mhlw.go.jp/public/opendata/requiring_inpatient_care_etc_daily.csv

死亡者数(累積)https://covid19.mhlw.go.jp/public/opendata/deaths_cumulative_daily.csv

重症者数の推移 https://covid19.mhlw.go.jp/public/opendata/severe_cases_daily.csv

クエリ一覧の何もない場所で右クリック-[新しいクエリ]-[その他のソース]-[Web]

 

読み込み完了。

 

データを加工する

新規陽性者数のデータには、日付と全国値と都道府県値があるが、
都道府県値は今回使わないため列を削除する。
ヘッダDateとヘッダAllを[Shiftキー]や[Ctrlキー]などで同時選択し、[他の列の削除]を実行する。

 

削除完了。2列のみ表示され、クエリステップに"削除された他の列"が追加されたことを確認する。

 

All列の名称を変更する。All列ヘッダで右クリック か 列名横[▼]をクリック し、[名前の変更]を実行。"新規陽性患者数"に変更する。

 

列名変更完了。

 

入院治療等を要する者等推移についても不要な列の削除と列名を変更する。
これも全国値(3種)と都道府県値(3種)があるため、都道府県値の列を削除する。
列名を次の通り変更する。
(ALL) Requiring inpatient care -> 入院治療等を要する者
(ALL) Discharged from hospital or released from treatment -> 退院又は療養解除者数
(ALL) To be confirmed -> 確認中

 

死亡者数についても不要な列の削除と列名を変更する。
全国値と都道府県値があるため、都道府県値を削除する。
列名はAll -> 死亡者数(累積)とする。

 

重傷者数の推移についても不要な列の削除と列名を変更する。
全国値と都道府県値があるため、都道府県値を削除する。
列名はAll -> 重症者数の推移とする。

 

4データを結合するクエリを作成する。
クエリ一覧の何もない場所で右クリック-[新しいクエリ]-[結合]-[新規としてクエリをマージ]

 

①メインとなるテーブルとして、"新規陽性者数"を選択し、
②結合キーとしてDate列を選択し、
③結合テーブルとして"入院治療等を・・・"を選択し、
④結合キーとしてDate列を選択する。
⑤[OK]をクリックする。
※結合の種類は初期選択の"左外部・・・"で良い。SQLのLeft Outer Join相当。
※※新規陽性患者数のDate列に欠けがない前提である。実際にはテーブル間で当日データの有無に差があったが大勢に影響はない。詳細は実データを確認されたし。



列を選択した時点で認証方法のダイアログが出る場合がある。最初と同じように、匿名接続を実行する。
※この手順より前に"アクセス許可の削除"を実行した場合に、再現性のある接続失敗が発生した。その場合、いったんExcelファイルとして保存して、開きなおすと治った。

 

マージ1というクエリが作成され、新規陽性者データと入院データが結合された。
引き続き死亡者データと重症者データを結合するため、クエリ[マージ1]を選択した状態で、画面右上の[クエリのマージ]をクリックする。

 

同様に死亡者データを結合する。

 

同様に重症者データを結合する。

 

入院データを展開する。
"入院治療等・・・"列ヘッダの "┐┌"をクリックすると展開メニューが開く。
Date列はベースとなった新規陽性者数データの列を使うため、チェックを外すこと。

 

展開完了。もともとデータがTableとなっていたが3列の数値データになった。
※null(ヌル)は結合時に、対象日付のデータ見つからなかったレコード。空っぽの意。
※※後でシートへ読み込んだ際にはnullは未入力セルになる。

 

死亡者数データも同様に展開する。Date列は不要。

 

重症者データも同様に展開する。Date列は不要。

 

データをよく見るとわかるが、加工したデータは並び順がめちゃくちゃになるため、昇順で並べ替える。
シートへ展開する前には、ソートを掛けた方がよさそう。

 

ここまでで加工は全て完了。
クエリ"マージ1"のステップが同じになっていればOK。

 

 

シートへ読み込みグラフを作る

 

[閉じて読み込む]を実行する。
クエリを保存して、Excelシートへ読み込む。

 

Power Query エディタが閉じられ、クエリがシートへ展開される。
回線速度やマシンスペックによるが10秒程度かかる。
シート名はデフォルトで、各シートへ各クエリが読み込まれ、範囲にはクエリ名が設定される。
右側のブック クエリ一覧の各項目の右には更新ボタンがついており、ここから最新データを読み込みなおすことができる。つまり、たった一度クエリを設定すれば、更新ボタンを押すだけでデータの再取得が行われシート上の数値やグラフを更新できる。

 

"マージ1"のセルにカーソルが当たっている状態で、折れ線グラフを実行する。

 

グラフを引き伸ばすとこんな感じ。
読み込んだデータの利活用については各ユーザで工夫して欲しい。
コロナのオープンデータだけを使用しているが、他のジャンルのオープンデータ や 自社の売り上げデータ を結合して、複合チャートを作り分析するなど可能性は無限大。



今回の成果物。
対数目盛にプロットしたコロナデータは見かけないので作ってみた。
2022年から重症者数の傾向が変わっているように見える。

 

 

後書き

Excel上で更新ボタンを押すだけで、オリジナルチャートが更新されると思ったら結構便利なのでは?

 

つなげるだけで、何かを生み出すことができるのはノーコードっぽくていいですね。

 

デジタル庁さんにはもっと積極的にオープンデータを提供して欲しいです。

 

 

 

以上、最後までお付き合いいただきありがとうございました。