LoginSignup
2
1

More than 1 year has passed since last update.

@nanbuwksinIoTLT

SQL を使って新型コロナのオープンデータを処理

Last updated at Posted at 2021-08-15

(夏休み企画 9/20)

先に
「LibreOffice Calc を使って新型コロナのオープンデータを処理」
https://qiita.com/nanbuwks/items/d13fb40138660015ef8a
を書きましたが、もう少し高度な処理をしてみます。

先の記事では最近の1週間のキッズの感染者数を出しました。今回は去年3月からのキッズの感染者の変動をグラフにしてみます。
しかしながらこれより高度な集計を表計算ソフトでがんばるのはどうかなと思います。社会人向けにはその方法でもいいかも知れませんが、小学生にセル集計テクニックを覚えてもらうのはバッドノウハウかなあということで、データ処理のところは SQL で行ってみましょう。

phpMyAdmin を使う

SQL 環境として、今回は phpMyAdmin を自分専用ツールとして使っていきます。
image.png

手軽な SQL 環境として LibreOffice Base も検討しましたが残念ながら調子が悪い感じでした。
cf.,「LibreOffice Base を使うテスト」
https://qiita.com/nanbuwks/items/3742c7ab4805d187326d

環境

  • Ubuntu 20.04 LTS Desktop
  • MariaDB 10.3.31-MariaDB-0ubuntu0.20.04.1
  • phpMyAdmin 4.9.5deb2
  • LibreOffice 6.4.7.2 Calc
  • 足立区感染状況のオープンデータ (2021/8/14朝取得)

phpMyAdmin を用意

以下の記事を元に、phpMyAdmin の画面が出るところまで進んでおきます。

「phpMyAdmin + AMP を Ubuntu パッケージからインストール」
https://qiita.com/nanbuwks/items/6768bc73661bdba43af9

オープンデータを用意

「LibreOffice Calc を使って新型コロナのオープンデータを処理」
https://qiita.com/nanbuwks/items/d13fb40138660015ef8a
の通り、足立区の
「区内での新型コロナウイルス感染者の発生状況」
https://www.city.adachi.tokyo.jp/juyo/covid19list.html

image.png

からのデータを持ってきます。

image.png

ここの CSV をクリックし、ファイルをダウンロードします

CSV データをデータベースに登録するために整理する

本来、SQLで使うためにはデータを正規化するという処理をしないといけません。
しかしながら 「統計表における機械判読可能なデータの表記方法の統一ルール」
https://www.soumu.go.jp/main_content/000723626.pdf
に基づいたデータである場合は、ほぼ正規化されているのでそのまま使います。
image.png

しかしながら、ファイルの中身を詳しく見てみると以下のように行の最後に「^M」というヘンテコなものがついています。これは改行コードと言われるもので、処理する機械によって改行コードが異なるのでLinuxで扱う場合はこれを除去しないといけません。

image.png
簡単に除去するために、LibreOffice Calcで一旦開きます。

ダウンロードしたデータを開くと、インポートの画面が出てくるので「OK」
image.png

以下の画面のように読み込まれます

image.png
そのまま保管します。「テキストCSV形式を使用」を押します。

image.png

そうすると、「^M」はうまく無くなりました。

image.png

phpMyAdmin で CSVデータをインポート

ログインします。

必要に応じ、データベースを作成します。
例では「夏休みのテスト」という名前のデータベースで作業します。もしまだデータベースを作成していない場合は以下のようにして作成します。

image.png

「夏休みのテスト」を押します。まだデータは空ですね。

image.png

もし以前に「コロナ」テーブルを作っていた場合は以下「テーブルを削除する」で消しておきます。
image.png

「インポート」で「ファイルを選択」してCSVファイルを指定します。

image.png

「ファイルの最初の行にテーブルのカラム名が含まれている。」にチェックを付けて「実行」

image.png

うまく読み込まれました。

image.png
「TABLE 1」をクリックすると読み込まれたデータが表示されます。

image.png

「TABLE 1」の名前を扱いやすいように「コロナ」に変えました。
image.png

10歳未満の最近の感染者を抽出

calc と同じようにデータ分析をしてみます。

10歳未満で検索してみます。

image.png

440人と出てきました。

image.png
日付を 2021-08-10 のみで検索すると、

image.png
7人になりました。

image.png

image.png

診断日をINにすると、複数日が検索できます。 2021-08-03,2021-08-04,2021-08-05,2021-08-06,2021-08-07,2021-08-08,2021-08-09,2021-08-10 と入れて検索します。

image.png

79と出てきました。

SQL を使う

先程の最後の検索は行0-24の表示のところにSQLが表示されています。


SELECT * FROM `コロナ` WHERE `診断日` IN ('2021-08-03','2021-08-04','2021-08-05','2021-08-06','2021-08-07','2021-08-08','2021-08-09','2021-08-10') AND `年代` LIKE '10歳未満'

こういったものを自分で書いてみましょう。「SQL」を押して、

image.png


SELECT * FROM `コロナ` WHERE 年代="10歳未満"

とします。元々表示しているものを書き換えます。
「コロナ」 は 「コロナ」 でもいいです。
「"10歳未満"」は文字列として扱っているので 「"」 で囲む必要があります。「"」 は 「'」 でもいいです。
実行すると440の一覧が出てきました。

更に「クエリボックスを表示」して、以下のように書き換えると


SELECT * FROM `コロナ` WHERE 年代="10歳未満" AND 診断日="2021-08-10"

で7名の一覧が出てきます。

SQL で集計する

これを一覧ではなく、集計してみるとどうなるでしょうか？


SELECT COUNT("No.") AS 合計 FROM `コロナ` WHERE 年代="10歳未満" AND 診断日="2021-08-10"

image.png


SELECT COUNT("No.") AS 合計 FROM `コロナ` WHERE 診断日="2021-08-10"

だと
image.png

SELECT 年代,COUNT("No.") AS 人数 FROM コロナ GROUP BY 年代

だと

image.png

並びがバラバラなので、直します。


SELECT 年代,COUNT("") AS 人数 FROM `コロナ` GROUP BY 年代 ORDER BY 年代

image.png

並び順がちょっと惜しいけど、さっきよりはずっといいですね！

8月10日だけの集計です


SELECT 年代,COUNT("") AS 人数 FROM `コロナ` WHERE 診断日="2021-08-10" GROUP BY 年代 ORDER BY 年代

image.png

日付ごとに集計する


SELECT 診断日,COUNT("No.") AS 人数 FROM `コロナ` WHERE 年代="10歳未満" GROUP BY 診断日 ORDER BY 診断日

image.png

166のデータが出ますが、24しか表示されていません。
「全て表示」として、一番下の「クリックボードにコピー」を押します。

グラフを作る

LibreOffice Calc を起動して、「編集」-「貼り付け」で貼り付けます。「区切りのオプション」を「タブ」にするといい感じになります。

image.png
image.png
余計な行を削除しましょう。
image.png
Ctrl+Aを押して、全体を選択します。
image.png

「挿入」-「グラフ」で、縦棒「」を選択して「完了」
image.png

1日ごとの感染者数が出ましたが、はじまりが2021-03-05となっていてちょっとわかりずらいですね。一度グラフを削除し、もう一度作り直しましょう。
image.png
2020-03-01 0というデータを挿入し、グラフを作り直すと
image.png

見やすい感じになりました。

データをいい感じに変換する

まずは、ユニークキーというものを作ってテーブルを編集できるようにします。

「構造」で No. の名前の . があると使いづらいので、 No に変更します。
image.png

「その他」の「ユニーク」を押します。
image.png

さて、日付のデータが、「2021-08-01」のようになってますが文字の形式なので、処理しやすい形式に変更します

「表示」で「診断日」を押すと、変なデータが1つあるのがわかりますね。
image.png

これを削除します。

次に「構造」で、「診断日」を「変更」して、データ型を「DATE」にします。
image.png

週の集計を簡単に



SELECT 年代,COUNT("") AS 人数 FROM `コロナ` WHERE 診断日 <= "2021-08-03" AND 診断日 <= "2021-08-10" GROUP BY 年代 ORDER BY 年代

image.png

月、週ごとの集計をする


SELECT DATE_FORMAT(診断日, "%Y %U週") as 診断週,COUNT("") AS 人数 FROM `コロナ` WHERE 年代="10歳未満" GROUP BY 診断週 ORDER BY 診断週

image.png


SELECT DATE_FORMAT(診断日, "%Y年%m月") as 診断月,COUNT("") AS 人数 FROM `コロナ` WHERE 年代="10歳未満" GROUP BY 診断月 ORDER BY 診断月

image.png

これを元に、以下のように足立区の10歳未満の感染者数グラフを作りました。

image.png
image.png

考えよう

  • 最後のグラフの、2021年8月が下がっているのはどういうわけかな？
  • この感染者数と、発症していないけれど感染力を持つ人の関係はどうなるかな？
  • 小学校の1校ごとに何人感染力を持つ生徒がいるか計算できるかな？
2
1
0

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
Sign upLogin
2
1