Excel や Power BI Desktop のデータ取得の機能である Power Query （データの取得と変換） の話題です。
地震データを取得する
最近、また地震が多いのが気になりますよね。地震データは気象庁のホームページで随時公開されています。しかしながら、この公開データは、良い意味でデータ クレンジングのサンプルになり、違う意味では「集計」や「レポート作成」などの後工程をあまり考慮していないデータかもしれない、と愕然とします。
今回のお題とは離れた話題なのですが、ちょっとだけご紹介します。
http://www.jma.go.jp/jp/quake/quake_local_index.html
すべての数字が「全角」です。大きい、小さいの判断をするためには数値にしないと比較できないので、まずはこれを半角に変換する必要があります。同じく日付に使われている数字も全角です。良いか悪か別にして、Excel は全角数字をセルに入力すると、自動的に全角数字を半角の数字に変換してくれます。また、関数も用意されていて、ASC関数が全角のアルファベット、数字を半角に変えてくれます。（反対は JSC関数）
残念ながら Power Query エディターには全角→半角にしてくれる関数はありません。自分でカスタム関数を作って対応します。やることはものすごくベタで、全角のＡは半角の A に、全角の１は半角の 1 に、というようにすべてを羅列するようなものです。
一方、Yahoo さんの地震情報は、数字は半角になっています。元ネタは気象庁のデータを使っていると思うので、後工程・再利用を考えて変換されているのでしょう。
Yahoo! JAPAN 天気・災害 地震情報
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/
全角・半角変換の手間を考えると Yahooさんのページからデータを取得したいところですが、地震の回数が多くなる、ちょっと長い期間でみてみたい、となると最初の１ページのデータだけでは足りなくなります。
Yahooさんのこの情報ぺージは、１ページから300ページまであり、かなり前の情報も Web ページから取得することが可能です。
３００ページ目を開くと、今だと 2004年の２月後半からデータを取得することができます。１ページあたりに１００件のデータをテーブル内で表示してくれます。
URL は以下です。
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=101
?から後ろがページを指定するパラメーターですが、一番最後のパラメーターの b=101 が、このページで表示する「開始番号」の扱いです。なので
１ページ目 b=1
２ページ目 b=101
３ページ目 b=201
４ページ目 b=301
・・・・・
１０ページ目 b=901
といったルールになっていることがわかります。
複数の Web ページからデータを取得する
Power Query で最新100件（＝１ページ目）を超える過去のデータを取得したい場合は、２ページ目、３ページ目、、、と複数のページ上のデータを取得する必要があります。
毎回、たとえば３ページ分のデータを取得して分析しよう、とした場合、すぐに思いつくのは URL 固定で ３ 個のクエリを作成し、そのクエリを Append (追加) する方法でしょうか。
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=1
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=101
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=201
この３つのWebページのクエリ結果のテーブルを追加(Append)する方法です。
これでもいいんですが、URL固定、数固定にできない場合などを考慮すると、違う方法も覚えておくと便利です。それが「カスタム関数」を使う方法です。上記の URL であれば、b=1 や b=101 の部分の数値を変数、引数として扱い、結果をもらう、という方法です。
Excel / Power BI Desktop で「繰り返しの処理」のような方法でデータをとりたいな、と感じたときは、上述のように変数と関数を使うことを検討してみます。
データの読み込み処理を関数にする
Excel でも Power BI Desktop でも方法と手順は同じです。Excel の画面で手順を追ってみましょう。Excel 2016 のデータ タブにある「取得と変換」または「データの取得と変換」を使います。繰り返しになりますが、取得と変換はこれまでの外部データの取り込みに代わる新しい機能で Power Query と呼ばれていたものであり、Power BI Desktop のデータの取り込み・変換の機能（ＥＴＬ機能）と同じものです。製品の更新タイミングによって若干の表現の差がありますが、ほぼ同じ位置や言い回しなので、脳内補完してください。あえて画面は少し前の Excel のスナップショットです。
データ タブの「(データの)取得と変換」と「新しいクエリ」や「データの取得」などのメニューから「Webから」を選びます。
データを取得する Web ページの URL を入れるダイアログが表示されます。
Yahoo!さんの地震情報ページの 1 行目からを表示する URL を入れて [OK] を押します。
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=1
今回は、Yahoo! さんの一般向けのページなので [匿名] のまま [接続] を押します。
指定したページにあるデータ情報からどのデータを取得するか指定する「ナビゲーターウィンドウ」が表示されます。テーブル形式でデータがまとまっている [Table 0] を選んで
[編集] を押します。[読み込み] を押すと、このまま取り込んでしまうので、いったん [編集] を押し、クエリ エディターにデータを読み込んで、変換・編集作業をします。
クエリ エディターが立ち上がります。取り込んだデータをもとにして、データの型が最初から列の属性として設定されています。気象庁のデータと違って、元データの数字が半角になっていること、マグニチュードの”Ｍ”が無いこと、などでマグニチュードは数値データとして、情報発表時刻は日付データとして認識されています。発生時刻が「ABC」の文字になっているのは 2017年7月8日 15時42分 ごろ という「ごろ」がデータに含まれているからです。最大震度が同じく「ABC」の文字になっているのは、半角数字の後に「弱」や「強」の文字がついているものがあったからです。
今回は発生時刻の「ごろ」や最大震度のお「強・弱」を扱う「データ クレンジング」処理は割愛しますが、このクエリ エディターでクレンジング処理をすると、クエリ エディターで「ステップ」として記録され、再利用することができます。
なお、マグニチュードでも文字列が入るデータがあるため、クレンジングの作業は必要です。
このクエリ エディタは基本 GUI で操作しますが、それらの操作手順は VBA のマクロ記録のように Power Query の M 言語で記録されます。M 言語で記述されたソースを編集するために詳細エディターを立ち上げます。[ホーム] タブの [クエリ] グループの [詳細エディター] をクリックしてください。詳細エディターを立ち上げるコマンドは他にも多く配置されています。
詳細エディターが立ち上がりました。詳細といっても、機能的には「簡易」です。
何度かお伝えしていますが、現段階で M 言語を深く習得し、詳細エディターでガリガリと操作を記述するような使い方をマイクロソフトは想定していないと思います。このエディターには本当に最低限の機能しかありません。そういうものだ、と思ってあまり深堀りしないほうがいいと感じています。
この簡易エディターで「引数」の指定をします。let の上に (page as number) => を挿入します。page という変数で、数値(number)型を引数として指定します、という意味です。
これで、page という変数(数値）が let 以下で使えるようになりました。これで 1 から始まる、101 から始まる、201 から始まる、という指定が可能になります。
URL の一番最後の値として置き換えたいのですが、注意点は page は「数値」であり、URL は「文字」であること。よって、数値を文字に変換してあげます。
Number.ToText(page)
この関数で変数 page の中の数値データが、文字のデータに代わります。URL の最後の b=1 の部分を以下のように書き換えます。
[変更前] /?sort=1&key=1&b=1")),
[変更後] /?sort=1&key=1&b="& Number.ToText(page))),
ウィンドウの右下の [完了] を押すと、エラーがなければ、パラメーターの入力を促す画面が表示されます。
画面左のナビゲーションの [クエリ] の上にある [>] 展開マークを押すと、このクエリ エディターで扱えるクエリが表示されます。（Excel の場合は、ブックに含まれるクエリであり、Power BI Desktop の場合は pbix ファイルに含まれるクエリを含みます）
引数（パラメーター）を準備する
ナビゲーションの Table 0 の前に [fx] アイコンからわかるように、この引数を設定する操作によって、Tablel 0 クエリは関数に代わりました。page パラメータに 1 を入れて [呼び出し] を押すと、1 からのデータの表を関数の「クエリの結果として」表示します。101 をいれると、101 からのデータの結果を表示してくれます。このクエリの結果はナビゲーションから削除することができます。削除するまでの操作手順のアニメーション GIF が以下です。
パラメータに、1、101、と入力し関数 Table 0 に渡し、その結果を表にすることができました。この 1、101、、、を手入力ではなくしたいのです。これが Power Query の繰り返しの処理です。
たとえば、常に10ページ分がほしいのであれば、1、101、201、、、901 までパラメータとして渡します。そのため、そのパラメータをリストとして用意します。
手順は、10個の連続した数値を用意して、1から901を作ります。いろいろなやり方があると思いますが、0 から 9 までの連続した数値から以下のように作ってみます。
0 -> 0*100+1 = 1
1 -> 1*100+1 = 101
2 -> 2*100+1 = 201
・・・
8 -> 8*100+1 = 801
9 -> 9*100+1 = 901
クエリ エディターで 0 から 9 までの連続した数値を作る方法は、数式で ={0..9} を使います。この数式の結果は {0,1,2,3,4,5,6,7,8,9} の連続した数値のリストを作成します。
クエリ エディターで、このリストをテーブルに変換して、新しい列を追加して、0*100+1 の数式を使って 1 から 901 までの数字を作ります。そこまでは以下のアニメーションGIFです。
パラメーターを使って関数を利用する
この操作で関数へ渡すパラメーターが用意できたので、このパラメーター列の横に新しく列を追加し、そこに作成した関数 Table 0 を指定して、1列目にある数値をパラメーターとして指定します。
関数 Table 0 はパラメーターで指定されたデータから始まる「表」をクエリの結果として返してくれます。個々の [Table] のリンクをクリックすると、1ページ100件分のデータのみですが、列名の横にある [展開] ボタンを押すと、すべての結果の表(テーブル)を結合して一気にデータ 10ページ 1000件を作成します。
0~9のパラメーターの最大値の 9 をさらにパラメーター化したり、多めの引数を用意して、取り込んだ後でエラー行の削除を行う、クレンジングをする、という方法を組み合わせることができます。
まとめ
今回は複数の Web ページのデータをまとめる、というお題でしたが、ポイントは「関数化」と「パラメーターの準備」による「繰り返し処理」と「クエリ結果の追加統合」の処理だと言えます。この方法を知っていると、Web ページだけではなく、他のところでも応用が利くテクニックです。
以上、少しでも参考になれば幸いです。
地震データを取得する
最近、また地震が多いのが気になりますよね。地震データは気象庁のホームページで随時公開されています。しかしながら、この公開データは、良い意味でデータ クレンジングのサンプルになり、違う意味では「集計」や「レポート作成」などの後工程をあまり考慮していないデータかもしれない、と愕然とします。
今回のお題とは離れた話題なのですが、ちょっとだけご紹介します。
http://www.jma.go.jp/jp/quake/quake_local_index.html
すべての数字が「全角」です。大きい、小さいの判断をするためには数値にしないと比較できないので、まずはこれを半角に変換する必要があります。同じく日付に使われている数字も全角です。良いか悪か別にして、Excel は全角数字をセルに入力すると、自動的に全角数字を半角の数字に変換してくれます。また、関数も用意されていて、ASC関数が全角のアルファベット、数字を半角に変えてくれます。（反対は JSC関数）
残念ながら Power Query エディターには全角→半角にしてくれる関数はありません。自分でカスタム関数を作って対応します。やることはものすごくベタで、全角のＡは半角の A に、全角の１は半角の 1 に、というようにすべてを羅列するようなものです。
一方、Yahoo さんの地震情報は、数字は半角になっています。元ネタは気象庁のデータを使っていると思うので、後工程・再利用を考えて変換されているのでしょう。
Yahoo! JAPAN 天気・災害 地震情報
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/
全角・半角変換の手間を考えると Yahooさんのページからデータを取得したいところですが、地震の回数が多くなる、ちょっと長い期間でみてみたい、となると最初の１ページのデータだけでは足りなくなります。
Yahooさんのこの情報ぺージは、１ページから300ページまであり、かなり前の情報も Web ページから取得することが可能です。
３００ページ目を開くと、今だと 2004年の２月後半からデータを取得することができます。１ページあたりに１００件のデータをテーブル内で表示してくれます。
URL は以下です。
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=101
?から後ろがページを指定するパラメーターですが、一番最後のパラメーターの b=101 が、このページで表示する「開始番号」の扱いです。なので
１ページ目 b=1
２ページ目 b=101
３ページ目 b=201
４ページ目 b=301
・・・・・
１０ページ目 b=901
といったルールになっていることがわかります。
複数の Web ページからデータを取得する
Power Query で最新100件（＝１ページ目）を超える過去のデータを取得したい場合は、２ページ目、３ページ目、、、と複数のページ上のデータを取得する必要があります。
毎回、たとえば３ページ分のデータを取得して分析しよう、とした場合、すぐに思いつくのは URL 固定で ３ 個のクエリを作成し、そのクエリを Append (追加) する方法でしょうか。
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=1
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=101
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=201
この３つのWebページのクエリ結果のテーブルを追加(Append)する方法です。
これでもいいんですが、URL固定、数固定にできない場合などを考慮すると、違う方法も覚えておくと便利です。それが「カスタム関数」を使う方法です。上記の URL であれば、b=1 や b=101 の部分の数値を変数、引数として扱い、結果をもらう、という方法です。
Excel / Power BI Desktop で「繰り返しの処理」のような方法でデータをとりたいな、と感じたときは、上述のように変数と関数を使うことを検討してみます。
データの読み込み処理を関数にする
Excel でも Power BI Desktop でも方法と手順は同じです。Excel の画面で手順を追ってみましょう。Excel 2016 のデータ タブにある「取得と変換」または「データの取得と変換」を使います。繰り返しになりますが、取得と変換はこれまでの外部データの取り込みに代わる新しい機能で Power Query と呼ばれていたものであり、Power BI Desktop のデータの取り込み・変換の機能（ＥＴＬ機能）と同じものです。製品の更新タイミングによって若干の表現の差がありますが、ほぼ同じ位置や言い回しなので、脳内補完してください。あえて画面は少し前の Excel のスナップショットです。
データ タブの「(データの)取得と変換」と「新しいクエリ」や「データの取得」などのメニューから「Webから」を選びます。
データを取得する Web ページの URL を入れるダイアログが表示されます。
Yahoo!さんの地震情報ページの 1 行目からを表示する URL を入れて [OK] を押します。
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=1
今回は、Yahoo! さんの一般向けのページなので [匿名] のまま [接続] を押します。
指定したページにあるデータ情報からどのデータを取得するか指定する「ナビゲーターウィンドウ」が表示されます。テーブル形式でデータがまとまっている [Table 0] を選んで
[編集] を押します。[読み込み] を押すと、このまま取り込んでしまうので、いったん [編集] を押し、クエリ エディターにデータを読み込んで、変換・編集作業をします。
クエリ エディターが立ち上がります。取り込んだデータをもとにして、データの型が最初から列の属性として設定されています。気象庁のデータと違って、元データの数字が半角になっていること、マグニチュードの”Ｍ”が無いこと、などでマグニチュードは数値データとして、情報発表時刻は日付データとして認識されています。発生時刻が「ABC」の文字になっているのは 2017年7月8日 15時42分 ごろ という「ごろ」がデータに含まれているからです。最大震度が同じく「ABC」の文字になっているのは、半角数字の後に「弱」や「強」の文字がついているものがあったからです。
今回は発生時刻の「ごろ」や最大震度のお「強・弱」を扱う「データ クレンジング」処理は割愛しますが、このクエリ エディターでクレンジング処理をすると、クエリ エディターで「ステップ」として記録され、再利用することができます。
なお、マグニチュードでも文字列が入るデータがあるため、クレンジングの作業は必要です。
このクエリ エディタは基本 GUI で操作しますが、それらの操作手順は VBA のマクロ記録のように Power Query の M 言語で記録されます。M 言語で記述されたソースを編集するために詳細エディターを立ち上げます。[ホーム] タブの [クエリ] グループの [詳細エディター] をクリックしてください。詳細エディターを立ち上げるコマンドは他にも多く配置されています。
詳細エディターが立ち上がりました。詳細といっても、機能的には「簡易」です。
何度かお伝えしていますが、現段階で M 言語を深く習得し、詳細エディターでガリガリと操作を記述するような使い方をマイクロソフトは想定していないと思います。このエディターには本当に最低限の機能しかありません。そういうものだ、と思ってあまり深堀りしないほうがいいと感じています。
この簡易エディターで「引数」の指定をします。let の上に (page as number) => を挿入します。page という変数で、数値(number)型を引数として指定します、という意味です。
これで、page という変数(数値）が let 以下で使えるようになりました。これで 1 から始まる、101 から始まる、201 から始まる、という指定が可能になります。
URL の一番最後の値として置き換えたいのですが、注意点は page は「数値」であり、URL は「文字」であること。よって、数値を文字に変換してあげます。
Number.ToText(page)
この関数で変数 page の中の数値データが、文字のデータに代わります。URL の最後の b=1 の部分を以下のように書き換えます。
[変更前] /?sort=1&key=1&b=1")),
[変更後] /?sort=1&key=1&b="& Number.ToText(page))),
ウィンドウの右下の [完了] を押すと、エラーがなければ、パラメーターの入力を促す画面が表示されます。
画面左のナビゲーションの [クエリ] の上にある [>] 展開マークを押すと、このクエリ エディターで扱えるクエリが表示されます。（Excel の場合は、ブックに含まれるクエリであり、Power BI Desktop の場合は pbix ファイルに含まれるクエリを含みます）
引数（パラメーター）を準備する
ナビゲーションの Table 0 の前に [fx] アイコンからわかるように、この引数を設定する操作によって、Tablel 0 クエリは関数に代わりました。page パラメータに 1 を入れて [呼び出し] を押すと、1 からのデータの表を関数の「クエリの結果として」表示します。101 をいれると、101 からのデータの結果を表示してくれます。このクエリの結果はナビゲーションから削除することができます。削除するまでの操作手順のアニメーション GIF が以下です。
パラメータに、1、101、と入力し関数 Table 0 に渡し、その結果を表にすることができました。この 1、101、、、を手入力ではなくしたいのです。これが Power Query の繰り返しの処理です。
たとえば、常に10ページ分がほしいのであれば、1、101、201、、、901 までパラメータとして渡します。そのため、そのパラメータをリストとして用意します。
手順は、10個の連続した数値を用意して、1から901を作ります。いろいろなやり方があると思いますが、0 から 9 までの連続した数値から以下のように作ってみます。
0 -> 0*100+1 = 1
1 -> 1*100+1 = 101
2 -> 2*100+1 = 201
・・・
8 -> 8*100+1 = 801
9 -> 9*100+1 = 901
クエリ エディターで 0 から 9 までの連続した数値を作る方法は、数式で ={0..9} を使います。この数式の結果は {0,1,2,3,4,5,6,7,8,9} の連続した数値のリストを作成します。
クエリ エディターで、このリストをテーブルに変換して、新しい列を追加して、0*100+1 の数式を使って 1 から 901 までの数字を作ります。そこまでは以下のアニメーションGIFです。
パラメーターを使って関数を利用する
この操作で関数へ渡すパラメーターが用意できたので、このパラメーター列の横に新しく列を追加し、そこに作成した関数 Table 0 を指定して、1列目にある数値をパラメーターとして指定します。
関数 Table 0 はパラメーターで指定されたデータから始まる「表」をクエリの結果として返してくれます。個々の [Table] のリンクをクリックすると、1ページ100件分のデータのみですが、列名の横にある [展開] ボタンを押すと、すべての結果の表(テーブル)を結合して一気にデータ 10ページ 1000件を作成します。
0~9のパラメーターの最大値の 9 をさらにパラメーター化したり、多めの引数を用意して、取り込んだ後でエラー行の削除を行う、クレンジングをする、という方法を組み合わせることができます。
まとめ
今回は複数の Web ページのデータをまとめる、というお題でしたが、ポイントは「関数化」と「パラメーターの準備」による「繰り返し処理」と「クエリ結果の追加統合」の処理だと言えます。この方法を知っていると、Web ページだけではなく、他のところでも応用が利くテクニックです。
以上、少しでも参考になれば幸いです。
