Google BigQueryとGoogleデータポータル(Data Studio)を使い始めてCOVID-19感染者数のグラフを作るまで

BigQueryのサンプルデータにCOVID-19のオープンデータがありましたので、このデータでBigQueryを初めて触ってみました。さらにGoogle Data Studioで可視化してみました。

成果物：

2020/07/24 追記
Metabaseでも可視化してみました。
Google BigQueryとMetabaseでCOVID-19感染者数のグラフを作るまで

BigQueryのコンソールを開く

GCPのコンソールの左のグローバルメニューからBigQueryをクリックします。初めてBigQueryのコンソールを開くときには次のようになっているかもしれません。

「ENABLE」をクリックして、クルクルしているのを待つとBigQueryのコンソールにアクセスできるようになります。

BigQueryのコンソールでCOVID-19のオープンデータにアクセス

BigQueryのコンソールの左のメニューのResourcesの「ADD DATA」をクリックして、「Explore public datasets」をクリックすると、さまざまなオープンデータを探すことができます。その画面で COVID-19 Open Data を探します。

「VIEW DATASET」をクリックすると、BigQueryのコンソールの画面に戻って、COVID-19のデータを選択された状態になります。

左のツリー部分の「covid19_open_data」を開くと同名のテーブルが中にあることを確認できます。そのテーブル名をクリックすると、テーブルのカラムの構成(Schema)が表示されます。

「Preview」をクリックすると、このテーブルの中の生データを見られます。「QUERY TABLE」をクリックすると、上半分にSQLの雛形が入ります。ここでSQLを自由にいじってBigQueryのクエリを実行することができます。

covid19_open_dataテーブルの中身

Schemaを見て、クエリをいろいろ実行してみてデータを見てみると、テーブルの構成はこんな感じのようです。

• 地域ごと日付ごとに1レコード
• new_confirmedに新規感染者数、new_deceasedに死亡者数が入っている
• country_codeがJPのレコードが日本
• 日本の場合location_keyが都道府県を表す。JP_13のようにJP_の後ろに都道府県の番号が付いている
• 東京の location_key は JP_13

最終的に以下のようなSQLを書きました。

SELECT t1.date, t1.new_confirmed_count, t1.new_deceased_count, t2.new_confirmed_count as tokyo_new_confirmed_count
FROM
  (
    SELECT date, SUM(new_confirmed) AS new_confirmed_count, SUM(new_deceased) AS new_deceased_count
    FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
    WHERE country_code="JP" GROUP BY date
  ) AS t1 JOIN (
    SELECT date, SUM(new_confirmed) AS new_confirmed_count, SUM(new_deceased) AS new_deceased_count
    FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
    WHERE country_code="JP" AND location_key="JP_13" GROUP BY date
  ) AS t2 ON t1.date = t2.date
WHERE t1.date <= DATE_ADD(CURRENT_DATE, INTERVAL -1 DAY)
ORDER BY date

2020/07/24 追記
SQLの6行目の
WHERE country_code="JP" GROUP BY date
は
WHERE country_code="JP" AND location_key="JP" GROUP BY date
のほうがよさそうです。

SQLの結果は、日付、全国の感染者数、全国の死亡者数、東京の感染者数の順に並びます。

結果の数値が報道の数値と少しずれているのですが、大幅（2倍とか）にずれているわけでもないので、だいたいこれでよしとします。

Google Data Studioでグラフを作成

SQL実行結果の上にある「EXPLORE DATA」をクリックして「Explore with Data Studio」をクリックします。すると、Data Studioの画面に移動します。

※Google Data Studioを初めて使うアカウントの場合には

Not able to connect to your data

というエラーになるかもしれません。私はこのエラーになりました。Google Data Studioにアクセスして最初の利用規約の承認だとかをしたら、エラーは解消しました。

Data Studioの画面に移動すると次のようになります。

右上のチャートの種類を選ぶエリアで6個目の折れ線グラフ(Time series chart)を選択します。

次、右下のエリアでデータのMetricsのところに右のAvailable Fieldsからドラッグしてきて次のようにします。日付ごとに1レコードになっているので、SUMである必要はないのですが。

すると、グラフができました。

右のSTYLEをクリックすると、可視化の細かい設定ができます。今回は、死亡者数（青緑の線）だけ値の範囲が大きく異なるので、死亡者数の目盛りを右側に移し、左右の縦軸の範囲をそれぞれ指定しました。

線の色も変えました。

冒頭のグラフができました。

• 青: 全国の感染者数
• 赤: 全国の死亡者数（右の軸）
• 紫: 東京の感染者数

まとめ

この程度のグラフは自分で作らなくてもネットを検索すれば出てきますが、BigQueryとData Studioを使ってみる練習として。

最近の感染者数増加に関して、このグラフを見て気が付く点：

• 死亡者数の変化は感染者数の変化よりも時間差で遅れてくるのでまだわかりませんが、感染者数増加の割には死亡者数は少い状態を維持しているように見えます
• 全国の感染者数に比べて、東京の感染者数の最近の増加は大きいように見えます

Google Data Studioの名称

コンソール上では英語インターフェースだと「Data Studio」、日本語インターフェースだと「データポータル」になってました。

ググると検索結果に以下の記述はありました。

日本でのデータスタジオの名称は「Google データポータル」に変わりました。お客様のレポートや商品およびサービスには影響はありません。

公式ドキュメントには名称変更の記述を見つけられなかったけど、「データスタジオ」よりも「データポータル」と書いてある場所のほうが多い。英語は「Data Studio」が多いけど「Data Portal」もあった。。。