Power QueryをもちいたNoCodeで行う貿易統計の自動ダウンロード
数か月かけてpythonを学んで、e-Statという政府の統計サイトのAPI機能を使って、データを自動ダウンロードしてExcelで解析するプログラムを書いた。
やっと完成して、さあ運用だと思った矢先、それって似たようなことをExcelのpower queryの機能でもできることに気が付いてしまった。
自分がやりたかったことがコードを書くことなくここまで簡単にできることに驚いたので、そのやり方を備忘録がてら残す。
今回は、政府が公開している普通貿易統計という統計データを用いて、コーヒーがどの港を経由してどの国へ輸出されているかをExcelのPower Query機能を用いて調べてみよう。
0）はじめに
日本政府は各々の府庁で様々な統計データを公開している。国剤調査や雇用動向調査、景気予測調査などの調査や統計は聞いたことがあるかもしれない。
その統計データは各府庁のHPから閲覧することもできるのが、e-Statはその統計データを府庁を横断して検索できるようになっているポータルサイトだ。
また、ログインすると横断的な検索だけでなく、APIという機能を用いて機械的に統計データをダウンロードすることもできる。今回、このAPI機能を用いて機械的にe-StatからデータをダウンロードしてExcelで解析できるようにする。
通常、このAPIの利用にはプログラムを組んだりする必要があるが、ExcelのPowerQuery機能を使うとコードを書く必要なくこれらのAPI機能からデータを自動でダウンロードしてくれるようになる。
1)e-Stat のアプリケーションIDの発行
まずは、e-Statに登録してアプリケーションIDを発行する。ログインとアプリケーションIDの発行は利用ガイドを参照したり、下記サイトの紹介などを参考にしてほしい。
ここでアプリケーションIDを発行すると20桁程度のアプリケーションIDをもらうことができる。ここまででe-Statを用いる準備ができた。
※アプリケーションIDは悪用される恐れがあるため、公開してはいけない。
2)統計表IDの取得
はじめに行うのは、どの統計表からデータをダウンロードするかを決めることだ。
今回のコーヒーの国別、税関別の輸出量が知りたい場合は、必要な統計データは貿易統計である。特に国別、税関別分かれているものが必要となる。この統計データは「貿易統計_税関別 税関別概況品別国別表 輸出」が該当する。この統計データの正式名称はe-Statの検索などから調べることができる。
▲普通貿易統計の中にも統計の種類がいくつかある。目的にあった統計データの正式名称を控えておく。
e-StatのAPI機能を用いたダウンロードでは、統計表名ではなく、統計表にふられた統計表IDが必要になる。IDを指定すれば「貿易統計_税関別 税関別概況品別国別表 輸出」をダウンロードできるので、これに対応する統計表IDを調べる必要がある。
統計表IDの一覧は次のページから取得できる(※アプリケーションIDが必要)。
アプリケーションIDと取得したい「貿易統計_税関別 税関別概況品別国別表 輸出」を検索キーワードに入れている。この部分は各自自分の知りたいデータを入れていけばよい。
「表示」を押すと、次のような文字の一覧が現れるはずだ。
一覧はXML形式でわかりずらいが、<RESULT_INF>と書かれたタグ以降に▽<TABLE_INF id=>と書かれたデータがぶら下っている。
<TITLE>のタグをみると「確定 税関別概況品別国別表 (輸出 1-12月：確定) 2001年～2002年」となっていて、貿易統計_税関別 税関別概況品別国別表 輸出の場合は2年ごとにデータが区切られていることが分かる。今回の場合、もっとも古いデータが1991年－1992年で、最新は2021年までだ。
最終的に使いたい統計表IDは<TABLE_INF id=>と書かれている部分に該当するので、<TITLE>のタグがある中から、知りたい年度の<TABLE_INF id=>を参照する。
今回は2019年-2020年のデータを参照してみよう。
<TABLE_INF id="0003334000">となる。
3)HSコードの取得
このままでもデータのダウンロードに進めるのだが、普通貿易統計の場合、このままでは2019-2020年のすべての貿易データをダウンロードすることになってしまい膨大なデータになってしまう。そこで、調べたいコーヒーに限ってデータをダウンロードするようにしてダウンロードするデータを極力小さくしよう。
コーヒーに関する貿易データを取得するには、HSコードと呼ばれる貿易品の品目の分類に使用されるコードを使用する。
HSコードは次のページから検索できる。
コーヒーで調べてみると次のコードが該当しそうだ。
ここによるとコーヒーのHSコードは0901.21とある。ダウンロードの際に必要なHSコードは9桁固定でカンマやハイフンは不要なので、カンマを除いて0で右埋めして、”090121000”が必要なHSコードとなる。
3)Excel power queryの準備
ここまでで、ダウンロードに必要なデータの準備ができた。
以下、メモ帳などに次のデータを記録してExcelを立ち上げて、PowerQuery機能を使ってExcelにデータを取り込んでいこう。
アプリケーションID：<自分のアプリケーションID>
統計表ID：0003334000
HSコード：090121000
(以下Excel2016で説明しています)
データ>新しいクエリ>その他のデータソースから>Webから、を選択する。
http://api.e-stat.go.jp/rest/2.1/app/getSimpleStatsData?
appId=<アプリケーションID>&
statsDataId=<統計表ID>&
cdCat01=<HSコード>
ポップアップウインドウから詳細設定を選択して、「URL部分」に上記のように入力する。ボックスが足りない場合は「パーツの追加」を適宜押して、ボックスを追加する。
＜より詳しい解説＞
http://api.e-stat.go.jp/rest/2.1/app/getSimpleStatsData?部分はダウンロードするデータの形式などを指定している。今回はExcelと相性の良いcsv形式をして指定している。
以降、statDataIdで統計表IDを指定して、cdCat01でHSコードを指定している。その他、例えば貿易国を中国に限定したい場合は、このURLの後ろに&area_code=50105(50105は中国の国コード)と入れることで、中国に限定したデータを入手できる。
詳しい仕様は公式のAPI仕様を参照する必要がある。
なお、このURLをchromeのアドレスバーにベタ打ちしても同様のデータを得ることができる。はじめはなかなかコツをつかむのが難しいので、chromeで色々とURLを試してみることをお勧めする。
OKボタンを押すとしばらくして次のような一覧が出てくる。「区切り記号」が「コンマ」になっていることを確認して「データの変換」を押す。
次のようなウインドウが現れる。
はじめの数行はメタデータと言って、このデータがどのようなデータなのかを説明する情報が入っている。今回は不要なのでメタデータは消す。普通貿易統計の場合は、43行目までがメタデータである。
適当な行を選択したのち、リボンの「行の削除>上位行の削除」を選択して、43行目までを削除する。
このままだと、一行目が分類名を指すはずが、データとして扱われているので、最後にリボンの「1行目をヘッダーとして使用」を有効にすると、1行目がヘッダーに変換されてきれいな一覧が得られる。
リボンの「閉じて読み込む」を押すと、以上のデータがExcel上にテーブルとして読み込まれる。
あとは、これをピボットテーブルなどで好きに解析すればいいのだが、実はe-Statからダウンロードできるデータの形式がExcelのピボット解析には不向きなデータ構造をしているので、Excelで解析するにはひと工夫がいる。