Power BI Desktopを使い新型コロナ県別陽性者数推移をPDFから取得

※2021/04/06追記

取得するPDFのファイル名が変わったので、取得元の設定変更しました。

<img width="500" alt="PDFから取得01 MtoM1" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/85e76841-d001-443b-63b0-f2f891d24b4a.png">

更新したところ、うまく更新されました。
元データの数値で気になる箇所がありましたが、体裁が変わらなければ、ボタン1つで更新できそうです。

<img width="700" alt="PDFから取得02 MtoM1" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/8ab3bd9b-1da6-1983-afeb-54e69e6726e6.png">

※2021/04/06追記ここまで

##表体裁を設計します

今回の要点は、

**●PDFからの表データの取得
●扱いやすい表にするために、ピボット解除を利用したマージ
※「累計」と「前日差」の表に分け、マージ列を追加して結合
●詳細エディター（スクリプト）を利用して更新可能なクエリに変更**

となります。
元のデータの形式は、

| エリア | 3/12 |  | 3/13 |  | 3/14 | ・・・ |
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
|   |   | 前日差  |   | 前日差  |   | ・・・  |
| 全国  | num  | num  | num  | num  | num  | num  |
| 北海道  | num  | num  | num  | num  | num  | num  |
| 青森  | num  | num  | num  | num  | num  | num  |
| 岩手  | num  | num  | num  | num  | num | num  |
|  ・・・ | ・・・  | ・・・  | ・・・  | ・・・  | ・・・  | ・・・  |

となっているので、以下のようなスタイルに変えることにしました。

| エリア | 日付 | カウント |
|:-:|:-:|:-:|
| 全国  | 3/12  | num  |
| 全国  | 3/13  | num  |
| ・・・ |・・・|・・・|
| 北海層  | 3/12  | num  |
| 北海道 | 3/13  | num  |
| ・・・ |・・・|・・・|
| 青森  | 3/12  | num  |
| 青森 | 3/13  | num  |
| ・・・ |・・・|・・・|

上記以外にも「累計」と「前日差」を識別するフラグ列を入れたりしています。

※最後にも記載していますが、
　出典元のデータ形式が変わることは珍しくありません。
　その場合、以下の手順は見直すことになります。
　その点、ご了承ください。

##厚労省が県別数値を公開しました

これまで、県別の陽性者数のデータは、公的機関からは見つかりませんでしたが、厚生労働省が[公開を始めました](https://www.mhlw.go.jp/stf/seisakunitsuite/newpage_00016.html)。

>**地域ごとの感染状況等の公表について**
>>　新型コロナウイルス感染症対策専門家会議の状況分析・提言（令和２年４月１日）を踏まえ、新型コロナウイルス感染症の地域ごとの状況について、厚生労働省のホームページ上においても、都道府県等から報告いただいているデータをとりまとめ、可能な範囲で公表することとしました。（厚生労働省のサイトより）

ただ、公開されたデータはPDFでした。

[https://www.mhlw.go.jp/content/10900000/000756250.pdf](https://www.mhlw.go.jp/content/10900000/000756250.pdf)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/b13fd124-66cb-3b8c-40d4-0e2af7b4f614.png)

今回は、**Power BI Desktop**で取り込むことにしました。
[デスクトップアプリでは](https://docs.microsoft.com/ja-jp/power-query/power-query-quickstart-using-power-bi)、**Power Query**を使って様々なデータソースに接続し、データ整形もできます。

##Power BI DesktopでPDFを取り込みます

**「データを取得」**設定画面でPDFのURLを記入します。
※この時点で取得したデータは2021年3月21日からのものでした。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/7edc422c-5289-670a-2884-52b7e1c1b97d.png)

プレビュー画面で確認します。
まず左側で一番上のテーブル（表）を取り込みました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/d6c0cdc3-0976-04bf-ac86-268b7e8ee491.png)

**「データの変換」**から**Power Queryエディター**を開き、データの整形を行います。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/83ee96a6-9c45-df32-bc74-f3c8627c727d.png)

不要な列を削除したり、

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/25fdf5bc-9e56-09c0-0762-da843df3e328.png)

「全」という文字だけ残ったので「全国」と置換したりしましたが、

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/9790a089-e31f-9c0b-6ba6-7e31ff7cd266.png)

足りないデータがあることに気づきました。
日付です。
日付を使わず1日目、2日目・・・とする手も考えましたが、PDF上でもう1つ認識されたのテーブルから日付データを取得できることがわかり、そちらを使うことにしました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/48784ea8-b911-e931-98f8-fefeacff313f.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/b7992838-b1ab-c29e-0c0f-bc3500dde877.png)

再度PDFの取り込み画面に戻ります。
先ほどチェックしなかった方のテーブルを選びます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/2daea7fc-2c3a-69cf-a878-fd1a4b7f9a87.png)

数値フィールドが「null」となっている列を削除します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/84a015ee-fb3e-40a6-d489-6963c814bc3e.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/fbff2e11-17c0-ffcd-2332-b8910b0489ee.png)

一番上の行も使わないので、**「行の削除」**で削除する行を指定します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/5eb150c2-9ca6-e51b-ae15-e5f36feada46.png)

まだ不完全ですが、この時点で**「1行目をヘッダーとして使用」**としてみます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/4371bffa-9b1b-51cf-3df1-b02bd5f12e0e.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/5f2c96ff-6409-692a-e2aa-079927879ef2.png)

##クエリを「累計」と「前日との差」に分けることにしました

ヘッダに日付が配置されている列はいいのですが、**「前日との差」**の文字列がヘッダーに配置できません。
※Excelと違い、セルをひょいっと移動できないのです。
解決できる機能がわからなかったので、クエリ（表）を「累計」と「前日との差」に分けることにしました。
まず、これまで操作してきたクエリをコピーし、コピー元を「累計」表、コピーした方を「前日との差」表とすることにしました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/40eb6263-003b-2f64-92f6-bd1ba8c49714.png)

コピー元から「前日との差」の列を削除します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/ec92976b-d103-2699-0f44-fe32b5ac2d75.png)

1行目に「null」が並んでいるので削除します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/16d5a79b-f052-d916-9c8b-527d1be01256.png)

エリア名に「全国」がうまく取り込まれてないので、該当列を右クリックし、**「置換」**設定画面で**「null」→「全国」**と置き換えます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/6a6622bc-569f-1f12-c456-082d38f4878c.png)

列名は「エリア」と変更しておきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/14eda26b-d72e-7bf4-eb71-5a8375733e66.png)

**3月12日**のデータが**文字列型**になっているので、**「型の変更」**で**整数型**に変更しました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/ce628043-09fe-0a7f-3c8d-84fec06ae13b.png)

ここで、**3月12日列**の部分にストライプ上の表示が出ました。
型の変更に沿わないデータがあったようです。
一番下行に**Error**が出ていたので、その行を削除します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/d6ba58ab-8c85-636a-e2b4-183ea2f2c784.png)

**「ホーム」→「閉じて適用」→「適用」**でPower BI Desktopの可視化画面に戻ります。
きちんと適用されるかの確認の意味もあります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/de3a0fd6-28f3-49dd-c304-7000073b1499.png)


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/dce9c3ed-a587-c9d7-c664-2df4296b10b6.png)

このままではまだ使いにくいので、後ほど整形していきます。

##「前日との差」クエリーを用意しマージ準備

同様に、今度は**「前日との差」**の列のみ残します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/cb4342f0-6158-a644-a26e-028d33f5a47d.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/0491ccbe-e893-27b9-a8a4-218cf0f5d912.png)

**「変換」→「1行目をヘッダーとして使用」**で列の先頭を設定します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/44a834ae-f03e-5a4f-283f-ffd8d2f348a1.png)

ここで、例えば**(1,284)**が**-1284**と自動的に変換さされていることに気づきました。
すべてが変換されているわけではないので、後で同じルールで一括変更するために、元に戻すことにします。
右画面の**「適用したステップ」**の中の**「変更された型2」**を削除します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/e82bb298-f9a1-edb6-7739-0e9f71883a39.png)

**(1,284)**に戻ります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/81469c5a-ed2f-8cf9-2435-dcd14b379ca4.png)

**「(」→「」（ブランク）**、**「)」→「」**と変換し、括弧を外します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/fea27ea2-0a05-ea5f-e5fc-bd6524e59795.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/21604243-8ebd-0245-4e09-45473d96f284.png)

数字が**文字型**になっているので、**整数型**に変換します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/c7caf801-167b-729b-c2ab-a34168b7d43f.png)

##マージ用の列を準備します

**「累計」**のクエリ（表）と**前日差**のクエリを横にマージ（右側Join）するために、マージ用の列を作ります。
**「列の追加」→「インデックス列」→「1から」**と選び、「1、2、3・・・」と連番の列を追加します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/eec21ce4-ec1b-f134-29d4-ed987fa49fc6.png)

以下画面では、「前日差」のクエリに連番列を追加しました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/6947d30a-038a-b9ac-1b42-0323a5556efb.png)

なお、事後説明になりますが、**「列の追加」→「インデックス列」→「カスタム」**で**「開始インデックス」「1」、「増分」「0」**とすることで、**「1」**のみの列を追加しています。
これの列、**1=累計**という意味を持つフラグ（印）で、グラフを作成するときに**「1を選べば累計グラフ」**、**「2を選べば前日差グラフ」**と切り替えられるようにするための列になります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/57afa186-b1b7-c6e0-3682-b0cbbf5959de.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/7682e7e6-b1fb-9d01-5f18-924530133248.png)

前日比のクエリには**「2」**が並ぶ列を追加します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/db6261e7-ae38-2cb7-8b64-739d06343c72.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/0c509b61-f361-ae33-933c-88be28acbdb0.png)

##ピボット解除します

現状、列を時系列に並べた状態です。
BIで使いやすくするためには、列数はできるだけ少なくしたいと思います。
列数を削減してから、マージを行います。
時系列にならんだ列以外を選択し、**「列のピボット解除」→「その他の列のピボット解除」**を選びます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/5475c443-7951-0719-07e2-c709f1af35f2.png)

ピボット解除することで、**「エリア」「日付」「カウント（陽性者数）」**を列名とする表（クエリ）に変わります。
※今回、間に「インデックス」（累計か前日差かを識別する数字）が入っています。

| エリア | インデックス | 日付 | カウント |
|:-:|:-:|:-:|:-:|
| 全国  | x  | 3/12  | nnn  |
| 全国  | x  | 3/13  | nnn  |
| ・・・ | ・・・ |・・・|・・・|
| 北海層  | x  | 3/12  | nnn  |
| 北海道 | x  | 3/13  | nnn  |
| ・・・ | ・・・ |・・・|・・・|
| 青森  | x  | 3/12  | nnn  |
| 青森 | x  | 3/13  | nnn  |
| ・・・ | ・・・ |・・・|・・・|

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/6d8c1363-fd73-6d83-ab08-8bd2aa7e0388.png)

**前日差**のクエリでも、時系列にならんだ列以外を選択し、**「列のピボット解除」→「その他の列のピボット解除」**を選びます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/fa693797-5150-b7c6-3508-72cb5330b25a.png)

同様に**「エリア」「日付」「カウント（陽性者数）」**という列構成になります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/fe4284ec-be0b-ea6b-ad44-6ddf8e3fd99d.png)

##ようやくマージします

**「ホーム」→「クエリのマージ」→「新規としてクエリをマージ」**を選びます。
マージするクエ入りをそのままの状態で残しておきたかったので**「新規」**としました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/ddf997f7-a1d3-3a5f-9302-bc81640ea472.png)

**「結合の種類」**は何パターンかあり、使い分けまでできてませんが、試した結果**「内部（一致する行のみ）」**が適当であることがわかりました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/d9486524-0ffd-a958-0a18-d7d5a9322293.png)

マージ後テーブルを展開することになります。
**「カウント2」**にのみチェックを入れます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/8b1ba47c-ff00-c594-1681-ea37e9d4a5de.png)

これでマージ完了です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/7cc2ba0f-52da-0ebd-bd07-b47ab03de7ea.png)

日付型が「文字列型」になっているので、「日付型」に変更します。
※これまでの作業途中で「日付型」に変更したのですが、ある理由で変更ステップを解除しています。この点については最後に述べます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/fc11d23b-7981-582c-9447-0b6b3eedd0e8.png)

**「ホーム」→「閉じて適用」→「適用」**でクエリの変更内容をPower BI Desktop本画面に反映し、グラフ作成に入ります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/7f6a4353-5134-8a63-4718-c861a7d280e4.png)

反映後など、作業の区切り区切りで保存します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/9ace0564-dbd7-ac6e-43c8-269e396bab51.png)

##グラフを作成します

左パネルで**「折れ線グラフおよび集合棒グラフ」**を選び、その隣のパネルには**「共有の軸」に「日付」**、**「各棒の値」に「カウント」（累計）**、**「線の値」に「カウント2」（前日差）**をドラッグします。
**「フィルター」**パネルに**「エリア」**をドラッグして、その画面上で**「全国」**にのみチェックを入れることで、全国の推移の可視化としました。
軸目盛りの大きさやデータラベルを追加などもしています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/f5728762-2b7f-e46e-69ba-44d7dc10435f.png)

設定パネルは以下のようになっています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/288299ee-afa9-0635-ae76-f24d50489b77.png)

エリア別（県別）折れ線グラフも作ってみました。
**「凡例」**に**「エリア」**をドラッグし、今度は**「フィルター」**パネルで**「エリア」→全国以外チェックする**ことで、県別推移としました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/e60772f6-2146-6b22-68f3-3eb1a434beb5.png)

##PDFのデータが更新されたときに対処したこと

今回の作業、数日かかったのこともあり、途中で取得元のPDFが更新され、**「3月12日から」のデータが「3月15日から」**に変わり、クエリエディター上で**「テーブルに3月12日列がありません」**というエラーメッセージが出るようになりました。
その対処をどうしたかと言いますと、色々した結果、**「詳細エディター」**を使うことにしました。
クエリエディターの左パネルのクエリを右クリックして選びます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/651d19fb-164f-3179-0a40-498b81957935.png)

これまでの処理が記述されたスクリプトが表示されます。

```javascript
umn15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}}),
    削除された列 = Table.RemoveColumns(変更された型,{"Column1", "Column2", "Column4", "Column6", "Column9", "Column12", "Column15", "Column18", "Column21", "Column24", "Column27", "Column30", "Column33", "Column36", "Column39", "Column42"}),
    削除された最初の行 = Table.Skip(削除された列,1),
    昇格されたヘッダー数 = Table.PromoteHeaders(削除された最初の行, [PromoteAllScalars=true]),
    変更された型1 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"Column1", type text}, {"3月12日", type text}, {"Column3", type text}, {"3月13日", Int64.Type}, {"Column5", type text}, {"3月14日", Int64.Type}, {"Column7", type text}, {"3月15日", Int64.Type}, {"Column9", type text}, {"3月16日", Int64.Type}, {"Column11", type text}, {"3月17日", Int64.Type}, {"Column13", type text}, {"3月18日", Int64.Type}, {"Column15", type text}, {"3月19日", Int64.Type}, {"Column17", type text}, {"3月20日", Int64.Type}, {"Column19", type text}, {"3月21日", Int64.Type}, {"Column21", type text}, {"3月22日", Int64.Type}, {"Column23", type text}, {"3月23日", Int64.Type}, {"Column25", type text}, {"3月24日", Int64.Type}, {"Column27", type text}, {"3月25日", Int64.Type}, {"24時時点", type text}}),
    削除された列1 = Table.RemoveColumns(変更された型1,{"Column3", "Column5", "Column7", "Column9", "Column11", "Column13", "Column15", "Column17", "Column19", "Column21", "Column23", "Column25", "Column27", "24時時点"}),
    削除された最初の行1 = Table.Skip(削除された列1,1),
    置き換えられた値 = Table.ReplaceValue(削除された最初の行1,null,"全国",Replacer.ReplaceValue,{"Column1"}),
    #"名前が変更された列 " = Table.RenameColumns(置き換えられた値,{{"Column1", "エリア"}}),
    変更された型2 = Table.TransformColumnTypes(#"名前が変更された列 ",{{"3月12日", Int64.Type}}),
    削除された下の行 = Table.RemoveLastN(変更された型2,1),
    追加されたインデックス = Table.AddIndexColumn(削除された下の行, "インデックス", 1, 0, Int64.Type),
    並べ替えられた列 = Table.ReorderColumns(追加されたインデックス,{"エリア", "インデックス", "3月12日", "3月13日", "3月14日", "3月15日", "3月16日", "3月17日", "3月18日", "3月19日", "3月20日", "3月21日", "3月22日", "3月23日", "3月24日", "3月25日"}),
    ピボット解除された他の列 = Table.UnpivotOtherColumns(並べ替えられた列, {"インデックス", "エリア"}, "属性", "値"),
    #"名前が変更された列 1" = Table.RenameColumns(ピボット解除された他の列,{{"属性", "日付"}, {"値", "カウント"}}),
    追加されたインデックス1 = Table.AddIndexColumn(#"名前が変更された列 1", "インデックス.1", 1, 1, Int64.Type)
in
    追加されたインデックス1
```

このスクリプトを見てみると、**「3月12日」**という記述があります。

```javascript
    変更された型2 = Table.TransformColumnTypes(#"名前が変更された列 ",{{"3月12日", Int64.Type}}),
```
```javascript
    並べ替えられた列 = Table.ReorderColumns(追加されたインデックス,{"エリア", "インデックス", "3月12日", "3月13日", "3月14日", "3月15日", "3月16日", "3月17日", "3月18日", "3月19日", "3月20日", "3月21日", "3月22日", "3月23日", "3月24日", "3月25日"}),
```

このため、PDFが更新され**「3月12日」という列**がなくなると、**「3月12日がない」**とエラーが出るわけです。
エラー解消のため、**「適用したステップ」**で該当するステップを削除します（「×」印をクリック）。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/27a24331-7259-ab9c-efe8-eb2b22f2f7ca.png)

しかし、このままでは「型の変換」などが行われず日付が「文字列型」として残ってしまいます。
このため、例えば、上述の**「ピボット解除」**することで、列名を**「日付」**に変え、日付の名称がスクリプトに残らないようにしました。
例えばデータ型の変換は以下のような記述に変わっています。

```javascript
 変更された型1 = Table.TransformColumnTypes(追加されたインデックス1,{{"日付", type date}, {"カウント", Int64.Type}})
```

PDF内の表の体裁が変わると、これまで作ったクエリは使えなくなり、再設定が必要なので、「完成」とは言えませんが、よい練習になりました。

作業の区切り区切りで保存します。

**了**