Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
7
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

Organization

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

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

成果物:
image.png

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

BigQueryのコンソールを開く

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

image.png

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

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

image.png

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

image.png

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

image.png

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

image.png

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

covid19_open_dataテーブルの中身

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

  • 地域ごと日付ごとに1レコード
  • new_confirmedに新規感染者数、new_deceasedに死亡者数が入っている
  • country_codeJPのレコードが日本
  • 日本の場合location_keyが都道府県を表す。JP_13のようにJP_の後ろに都道府県の番号が付いている
  • 東京の location_keyJP_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倍とか)にずれているわけでもないので、だいたいこれでよしとします。

image.png

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の画面に移動すると次のようになります。

image.png

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

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

image.png

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

image.png

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

線の色も変えました。

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

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

image.png

まとめ

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

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

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

Google Data Studioの名称

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

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

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

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
7
Help us understand the problem. What are the problem?