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

※2021/04/06追記

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

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

※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
・・・	・・・	・・・

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

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

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

これまで、県別の陽性者数のデータは、公的機関からは見つかりませんでしたが、厚生労働省が公開を始めました。

地域ごとの感染状況等の公表について

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

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

https://www.mhlw.go.jp/content/10900000/000756250.pdf

今回は、Power BI Desktopで取り込むことにしました。
デスクトップアプリでは、Power Queryを使って様々なデータソースに接続し、データ整形もできます。

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

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

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

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

不要な列を削除したり、

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ピボット解除します

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

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

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

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

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

ようやくマージします

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

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

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

これでマージ完了です。

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

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

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

グラフを作成します

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

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

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

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

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

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

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日」**という記述があります。

    変更された型2 = Table.TransformColumnTypes(#"名前が変更された列 ",{{"3月12日", 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日"}),

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

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

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

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

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

了