LoginSignup
38

More than 3 years have passed since last update.

COVID-19ダッシュボードを1時間で作る(Googleデータポータル) 〜お家で出来ること

Last updated at Posted at 2020-05-06

はじめに

新型コロナウイルス感染症対策で、アーティストや芸人さんのSNSを通じた活動に感動し、何か家で自分にできることはないか、と考えました。
サーバーレスノーコードで、自動更新の新型コロナウイルス(COVID-19)感染症対策ダッシュボードをGoogleデータポータルで作って、実際に作り方を公表したい!と考えたので、ここに紹介します。

※2020年8月12日から16日追加
この記事では紹介していませんが、MAXとLOOKUP関数等を組み合わせて、一つのデータベースから最新データのみを利用したり、都道府県別人口(別データ)を組み合わせて比率を可視化したりできないかテストしました。何かの参考になればと思いキャプチャだけ貼り付けます。
Screenshot 2020-08-16 at 13.55.48.png

マップの都道府県をクリックすると、フィルタできます。
Screenshot 2020-08-16 at 13.56.40.png

もしご興味あれば紹介いたしますので、コメントください。

ゴール

自動更新される、国内と世界各国の新型コロナウイルス感染症に関するダッシュボードを1時間で作成する。

Screenshot 2020-05-01 at 07.05.19.png

日本国内のダッシュボードのイメージ

Screenshot 2020-05-01 at 07.07.17.png
世界各国のダッシュボードのイメージ

Screenshot 2020-05-01 at 07.10.59.png

利用させていただいたStayHome,データソース(出典もと),政府の対策情報、注意事項等を表記したダッシュボードのイメージ

※諸注意
この記事は、2020年5月6日時点の作成手順です。データソースが変更になる場合がありますので、適宜対応ください。

BIツールの利点

Googleデータポータルのように、データを可視化したり分析するツールをBI(ビジネス・インテリジェンス)ツールと呼びます。
最大の利点は、「ゴールまでが爆速」です。
Excelなどの表計算ソフトなどでもデータ解析や可視化は可能ですが、データを集めたり編集したり、可視化することに特化しているので、作成時間がヤバいくらい早いです。
また、GoogleデータポータルのようなSaaSを利用した場合、html,css,JavaScript,SQLなどの知識が最低限(ほぼ不要)で完結でき、サーバーの管理する手間もありません。

必要なもの

作成時間:1時間程度。
アカウント:Googleアカウント。
以上。

手順イメージ

Googleスプレッドシートに関数でデータをインポート
Googleスプレッドシートの関数を利用して、データソースを整える
Googleデータポータルで可視化
以上。

それでは、早速作成します。

データソース集約

Googleスプレッドシートを開きます。

Googleスプレッドシートの説明

新しいスプレッドシートを空白で作成します。
スプレッドシートに任意で名前をつけます。
サンプルでは「covid19」にしました。

次にセルB1に=IMPORTDATA("https://www.stopcovid19.jp/data/covid19japan.csv")と入力します。

IMPORTDATAの説明

公開されているcsvファイルはCOVID-19 Japan 新型コロナウイルス対策ダッシュボードで公開されているファイルを利用させていただきました。

現在患者数(ベース): 厚生労働省「国内事例における都道府県別の患者報告数」 → PDF → CSV/JSON/APP

image.png

すると、csvの内容がGoogleスプレッドシートにインポートされます。
image.png

シート名もわかりやすいように名称変更しておきましょう。
サンプルでは「地域」にしました。
image.png

データソース加工

こちらのデータはGoogleデータポータルの地図グラフで利用したいので、少しトッピングをしたいと思います。
A1に「ISO3166-2」と入力し、A2からA48に向かってJP-01,JP-02,JP-03・・・JP-47と入力します。
ちなみに、A2に「JP-01」を入力したセルを選択すると、セルの右下に小さな四角のマークがでてくるので、それをダブルクリックすると、自動で連続して入力されます。
WHoHnesTi8zIwDdYY4Lu1588375776-1588376070.gif

余談ですが、データソース加工で便利な方法を紹介しておきます。
マスタを利用する方法です。
シートを追加します。
image.png

サンプルではシート名を「地域マスタ」にしました。
都道府県と地域コードの一覧を作成します。
image.png

「地域」シートに戻って、一旦A2からA48までの「ISO3166-2」データを消します。
A2に=ARRAYFORMULA(VLOOKUP("*" & C2:C &"*",'地域マスタ'!$A$2:$B$48,2,false))と入力します。
これでマスタの都道府県を照合し、地域コードのデータを引っ張ってきます。
image.png

ARRAYFORMULAについて説明します。
Googleスプレッドシートをデータソースとして利用するうえで最も重要な関数がARRAYFORMULAです。

ARRAYFORMULAの説明

ざっくりいうと、一発目に使用すれば、数式を配列形式で表示します。

今はエクセルに便利なXLOOKUP関数が実装されたのですが、VLOOKUPはエクセルでおなじみ、データベース関数です。

VLOOKUPの説明

地図グラフ

マップを利用するときのハマリポイントを説明します。
Googleデータポータルの地図グラフでは、数値によってマップを色分けして表示できるのですが、地域ディメンションといった地域を特定するための設定があります。

image.png
地域ディメンションのイメージ

image.png
地図グラフのイメージ

また、国、州など、エリアの規模を指定するズームエリアといった設定があり、日本を指定したい場合、都道府県のコードを事前に設定しておく必要があります。例えば、「徳島県」といった文字列はGoogleデータポータル側で位置情報として特定できないので、「JP-36」に変換する必要があります。

image.png
ズームエリアのイメージ

実際はtokyo,tokushimaなどの英語表記でも利用できるのですが、ズームレベルの設定がうまくいかない場合があったので、Googleの説明通り、ISO 3166-2のコードを利用した方法を紹介します。
サポートされる地理フィールド タイプ
ISO 3166-2:JP

データソース追加

どんどんデータソースを追加していきたいと思います。
シートを追加します。
demography,summary,世界各国,world,リンクと追加しました。
シート名は任意です。

image.png

demography

人口統計のデータソースをインポートします。
今回のデータソースは東洋経済オンライン編集部 荻原和樹さまよりGitHubにて公開されているcsvを利用させていただきました。

demography.csvをクリックします。
image.png

Rowをクリックします。
image.png

csvのソースが表示されるので、ブラウザのURLをコピーします。
https://raw.githubusercontent.com/kaz-ogiwara/covid19/master/data/demography.csv

Googleスプレッドシートにもどり、「demography」シートのA1に
=IMPORTDATA("https://raw.githubusercontent.com/kaz-ogiwara/covid19/master/data/demography.csv")
と入力すると、先程のデータがインポートされます。
image.png

summary

概要のデータソースを先程の手順でインポートします。
GitHubにもどり、summary.csv、Rowの順にクリックし、URLを取得します。
image.png

Googleスプレッドシートにもどり、「summary」シートのB2に
=IMPORTDATA("https://raw.githubusercontent.com/kaz-ogiwara/covid19/master/data/summary.csv")
と入力すると、先程のデータが同じようにインポートされます。
image.png

日付のデータが必要なので、関数を利用して作成します。
A1に「年月日」と入力し、A2に
=ARRAYFORMULA(TEXT(B2:B &"/"& C2:C &"/"& D2:D,"YYYYMMDD"))
と入力します。
TEXT関数を利用して、Googleデータポータルで読み取れるフォーマットにしておきます。

TEXT関数の説明

image.png

世界各国

世界各国のデータをウィキペディアからウェブスクレイピングしたいと思います。

ウェブスクレイピング

ウィキペディアの世界各国のデータを利用したいと思います。

方法は、やはりGoogleスプレッドシートのIMPORTHTML関数を利用します。

IMPORTHTML関数の説明

「世界各国」のシートに移動し、A1に
=IMPORTHTML("https://en.wikipedia.org/wiki/Template:2019%E2%80%9320_coronavirus_pandemic_data","table",1)
と入力します。
image.png

続いて、地図グラフに適切に表示されるように整えます。[]が国名の後ろについているとうまく表示されない場合があるので、[から後ろを関数で削除します。
G3に
=ARRAYFORMULA(iferror(LEFT(B3:B, FIND("[", B3:B)-1),B3:B))
と入力します。

IFERROR関数の説明
LEFT関数の説明
FIND関数の説明

image.png

world

世界中の概要のデータソース前回のIMPORTDATA関数を利用した手順でインポートします。
こちらのデータソースは三重大学名誉教授 奥村晴彦さまよりGitHubにて公開されているcsvを利用させていただきました。
RawよりURLを取得し、Googleスプレッドシート「world」シートのB1に
=IMPORTDATA("https://raw.githubusercontent.com/okumuralab/okumuralab.github.io/master/python/data/COVID-19.csv")
と入力します。
image.png

日付のデータを、関数を利用してGoogleデータポータルで読み取れるフォーマットで作成します。
A1に「年月日」と入力し、A2に
=ARRAYFORMULA(TEXT(B2:B,"YYYYMMDD"))
と入力します。
image.png

リンク

ダッシュボードに有用な情報や、データの出典元を表示させたいので、「リンク」シートに、情報を登録していきます。
A1にタイトル、B1にURL、C1にカテゴリーを入力し、必要なデータを入力していきます。
サンプルでは、関係省庁の支援策のホームページと、利用させていただいたデータの出典元にしました。
image.png

不要な行列削除

最後に、不要な行列を削除しておくことをおすすめします。
理由は、Googleスプレッドシートをデータベースとして利用するので、バグを減らすことと、セルの利用上限を意識する必要があるためです。

Google ドライブに保管可能なファイル

スプレッドシート
​Google スプレッドシートで作成したスプレッドシートまたは Google スプレッドシート形式に変換したスプレッドシートは 500 万セルまたは 18,278 列(列 ZZZ)まで。

列を削除する方法は、削除したい列を選択して、右クリック、列○-○を削除の順です。
JV0Bl44XGCh2UwJUIx5o1588467460-1588467486.gif

すべてのシートにおいて、行も同じように削除しておきましょう。
image.png
image.png

ダッシュボード作成

Googleデータポータルにアクセスします。

Googleデータポータルの説明

空白のレポートを開きます。
image.png

日本国内の情報

データのレポートへの追加でGoogleスプレッドシートを選択します。
先程作成したGoogleスプレッドシートを選択し、ワークシート、地域、追加の順に選択します。
image.png

このレポートにデータを追加しようとしています。のポップアップが表示されたら、「レポートを追加」をクリックします。
レポートが作成され、グラフが勝手に一つ作成されますが、今回はいったん右クリックから削除しておきます。
まず、名前をつけます。例では、「covid19」にしました。

グラフを追加していきます。
グラフを追加より、地図をクリックし、レポートに配置します。
image.png

地域ディメンションに「ISO3166-2」、指標に「ncurrentpatients」、オプションの指標をONにして、nexits、ndeaths、npatientsを追加します。
image.png

指標の名前を日本語変えます。
sumになっている部分をクリックして名前を感染者数に変更します。
image.png

ほかの指標の名前も同じ方法で、退院者数、死亡者数、感染者数(全)に変更しました。
image.png

ちなみにヒートマップも作成できます。
こちらは、自由に作成してみてください。サンプルには入れてません。
グラフの追加よりGoogleマップを追加し、ディメンション、バブルの位置をISO3166-2、ツールチップにname_jp、指標、バブルのサイズにncurrentpatientsにすると、それっぽくなります。
image.png

本編にもどります。
グラフの追加より円グラフを追加します。
image.png
ディメンションにname_jp、指標にncueerntpatients、並べ替えにncueerntpatientsを選択し、降順にしました。
image.png

グラフを追加よりスコアカードを追加します。
image.png

指標にncueerntpatientsを選択し、名前を感染者数に変更します。
先程の地図グラフと同じように、必要に応じてオプションの指標も作成してみてください。
image.png

同じ方法で、死亡者数もつくっておきます。
image.png

続いて、リソースを追加します。
リソース、追加済みのデータソースの管理とクリックします。
image.png

データソースの追加をクリックします。
image.png

Googleスプレッドシートを追加します。
covid19、demography、追加の順でクリックします。
image.png

閉じるで戻るのですが、データソースを追加より、先程と同じ手順でsummary、world、リンクも追加しておきます。
image.png

最後に、世界各国を接続します。
ここでは、範囲指定をB3:G229とし、「先頭行をヘッダーとして使用する」のチェックを外して追加します。
image.png

接続した世界各国のデータソースを編集します。
image.png

国名(修正前)、患者数、死亡者数、回復者数、Ref、国名の順に名前を編集し、右上にある完了ボタンをクリックします。
image.png

続いてデータソースのリンクを編集します。
image.png

まず、フィールドを追加します。
image.png

項目名を名称にしました。
計算式にHYPERLINK(URL,タイトル)と入力し、保存をクリックし、さらに完了をクリックします。

右上の「閉じる」をクリックしてレポートに戻ります。
image.png

続いて、年齢比の円グラフを作りたいので、円グラフを追加し、データソースを「covid19-demography」,ディメンションを「年代」,指標を「陽性者」,オプションの指標を「重症」,「死亡」,並べ替えを「陽性者」昇順に設定します。
image.png

さらに、概要の折れ線グラフを作りたいので、折れ線グラフを追加し、データベースを「covid19-summary」,期間のディメンションを「年月日」,ディメンションを「年月日」,指標を「PCR検査陽性者」,「死亡者」,並べ替えを「年月日」昇順に設定します。
image.png

次にスタイルをカスタマイズします。
系列番号2、軸を「右側」に設定します。
image.png

軸を「X軸を逆方向にする」をチェックします。
image.png

スコアカードを目立たせたいので、図形を追加します。
円形を追加します。
image.png

円形のプロパティで背景と枠線を好みに合わせてカスタマイズします。
image.png

世界各国

国内のダッシュボードの内容が構築王できたので、続いて世界各国のレポートを別のページに作成したいと思います。
左上の「ページを追加」をクリックして、新しいページを作成します。
image.png

早速、Googleマップを追加してみます。
設定は、データソースに「covid19-世界各国」、ディメンション、バブルの位置に「国名」、指標に「患者数」を選択しました。
image.png

日本国内の情報ページで行った作業と同じように、円グラフ、スコアボードを追加します。
image.png

世界全体の概要のグラフを追加したいので、まず折れ線グラフを追加します。
こちらも先程、日本国内のダッシュボード作成時に追加した折れ線グラフと同じように設定していきます。
まず、データソースは「covid19-world」、期間のディメンション、ディメンション、並べ替えは「年月日」とし、指標に「Global Confirmed」,「Global Deaths」を設定します。
スタイルの設定も忘れなく、系列番号2、軸を「右側」、軸を「X軸を逆方向にする」をチェックしておきます。
image.png
ヒートマップ付きデータ表も追加しておきます。
image.png
設定は先程の折れ線グラフと同等でOKです。
image.png

出典・対策情報リンク

新しいページを追加して、名前をつけます。
image.png
先程作ったページにも名前をつけておきましょう。
例では「全国感染者数」,「世界各国」,「出典・対策情報リンク」にしました。
image.png

グラフを追加から表を追加し、データソースを「covid19-リンク」,ディメンションを「名称」,「カテゴリ」,「URL」に設定します。
image.png

これで一通りグラフを追加できました。
ダッシュボードの表題や注意事項を表示しておきたいので、必要に応じてテキストを追加します。
image.png
表題は「出典・政府情報リンク集」にしました。
注意事項も追加しました。
<注意事項について>
当ページデータについて、上記リンク集に含まれるインターネット上に公表されたオープンデータ等に基づいて作成しています。
データの精度の問題や、情報に誤りがあるかもしれないため、利用により何らかの問題が生じても責任は取れません。よって、本サイトに掲載されている資料及び情報は、いかなる種類の保証も行なわないものとします。
また、いかなる内容の損害について、貴台又は何者に対しても責任を負いません。本サイト上の情報に置く信頼は完全に貴台自身の責任によることをご承知下さい。

私は注意事項を作成しましたが、法務に詳しくないため、ここらへんは適切な表記方法を確認してください。

デザイン

最後に、デザインを自分好み(本当はウェブアクセシビリティを学ぶべき)にカスタマイズします。
レポートの空白の部分を選択すると、全体のテーマを設定できます。
image.png

あとは、各レポート内のグラフの位置や大きさ、デザインを調整してみてください。

Control+Zで一つ前に戻るので、とにかく触ってみて、色々試すのが最短で覚えるコツです。
必要に応じて操作方法は逆引き的な感じで学びましょう。
なぜなら、Googleのサービスは更新が早いので、熟読した情報が1ヶ月後に使えない場合があるため、感覚で覚えて、行き詰まった部分を調べる方式が性質上あっているからです。

完成

Screenshot 2020-05-06 at 11.56.56.png
Screenshot 2020-05-06 at 11.54.42.png
Screenshot 2020-05-06 at 11.54.58.png

おまけ

可視化をする方法はたくさんありますが、「早く簡単に作る」方法として、Googleデータポータルを紹介させていただきました。
「早く作れる」のいいところは、修正やソースコードの追加が簡単なため、利用実態に応じてすぐに変更できます。
例えば、この記事作成中(2020年5月5日)に、大阪府新型コロナウイルス対策本部会議において、緊急事態宣言の規制を早期に緩和させるための独自基準「大阪モデル」を決定されましたが、この基準に関しても可視化したい場合でもオープンデータ等があればすぐに追加することも可能です。

  • 新たに発見された感染経路(リンク)不明陽性者の増加比(前週比1未満)
  • 新規陽性者におけるリンク不明数(10人未満)
  • 新規PCR検査での陽性率(7%未満)
  • 重症の患者を受け入れる病床使用率(60%未満)

作り込んだり、細かい要件がある場合は当然開発により作り込んだものを利用するべきですが、簡単な可視化や一時的なデータ集計などでも活用できます。
最後まで見ていただき、ありがとうございました。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
38