世界各国の人口推移およびGDP推移を取得したい.そんな場合は国連や世界銀行のデータを活用する.今回は国連から人口推移,世界銀行からGDP推移のデータをそれぞれ取得したので経緯を紹介する.
国名コードを取得する
国名コードは法務省,日本学術振興会がそれぞれ2文字,3文字のコードを紹介しているが,Wikipedia(ISO 3166-1)のものが両者を網羅していて使いやすい.今回はこちらを取得することにする.
EXCELのPowerQueryでデータを取得しテキスト形式で保存する
Wikipediaの該当ページのテーブルをEXCELのPowerQueryで取得する.ブラウザのURLをコピーしておく.データタブのWebからをクリックする.

コピーしておいたURLをここで貼り付ける.

Webコンテンツへのアクセスでは「接続」をクリックする.

ナビゲーターの初期画面では表示オプションにいくつかのテーブル名が表示されている.それぞれクリックしてみて当たりをつける.

提案されたテーブルが良さそうだ.これを選択して「データの変換」をクリックする.

PowerQueryエディタ画面に遷移する.

最も右側の列は不要なので削除する.

閉じて読み込むと「249行読み込まれました」と表示される.

「ファイル」メニューの「名前をつけて保存」をクリックする.

ここではM_COUNTRYとファイル名をつけ,ファイル形式をテキスト(タブ区切り)とする.これがSQL Serverでインポートする際のデフォルト形式だからである.

SQL Serverにインポートする
SQL Serverで該当するデータベースを右クリックし,「タスク」「データのインポート」と進む.

ウィザードが起動するので,データソースの選択で「Flat File Source」を選択する.

「参照」をクリックしてダイアログからファイルを指定したところである.

Nextをクリックして列の概観を表示している.ここではNextを押さずに「詳細設定」へ行く.

「詳細設定」で「国・地域名」「英語名」「フランス語名」のOutputColumnWidthを50から500に拡張しておく.予想以上に長い国名がある.

変換先の選択では「SQL Server Native Client 11.0」を選択する.

コピー元のテーブルおよびビューを選択では「マッピングの編集」へ行く.

列マッピングでは初期状態で下図のようになっている.

それぞれの列のデータ型,データ長およびNULLの可否を指定する.

成功すると下図のように「***行転送されました」と表示される.ここで失敗するとしたら,先の列の詳細設定でOutputColumnWidthを拡張していなかったために入力が切り捨てられた場合である.

国連の人口データを取得する
国連の各国の人口の推移は統計局のリンクから辿った.ブラウザの検索で「世界 国別人口 推移」などと検索した結果を示す.

統計局のページで「人口」と検索する.最初に出典のリンクがあるのでこれをクリックする.

主要出典資料名一覧のページが表示される.最初のWorld Population Prospectのリンクをクリックする.

国連のサイトに遷移するので,Download Data Filesをクリックする.

最初に目的のファイルがあるのでクリックするとEXCELのファイルがダウンロードされる.

EXCELのPowerQueryでデータを取得しテキスト形式で保存する
開くと膨大な表が表示される.意外なことにテーブル化されていない.後の工程のためにここでテーブル化しておく.A17から最後のセルまでを選択し,テーブル化する.


新規にEXCELを起動し,PowerQueryを起動する.

WPP2024_GEN_F01_DEMOGRAPHIC_INDICATORS_COMPACTが該当するファイルである.

ナビゲーターで先程作成しておいたテーブルが見えるのでこれを選択する.

このテーブルには集計行が含まれている.大陸,地域,先進国か途上国か,などの単位で集計行がある.見分けるポイントは国名コードの有無である.国名コードのない行は集計行である.それらを削除したい.例えば,ISO3 Alpha-codeは3文字で国名を示すコード列であり,この列を選択して「空の削除」しても良い.
ISO3 Alpha-codeの列を選択し「空の削除」

下図は最初の人口の列である.注意が必要だが,人口の値は1000で割ってある.これは整数に直しておきたい.

まずカスタム列を追加する.

最初の人工の列をダブルクリックし,*1000とタイプしてOKする.



ファイルメニューの名前をつけて保存をクリックする.

ファイル名をT_TotalPopulationとし,ファイル形式をテキスト(タブ区切り)とする.

SQL Serverにインポートする


詳細設定でCountry列のOutputColumnWidthを50から500に拡張する.

列マッピングは下図のように設定する.


世界銀行のGDP推移を取得する
データのダウンロードと前処理
世界銀行のDataBankからGDP(current US$)をクリックする.

World Development Indicatorsの初期画面.左側のペインで変数を設定できるようになっている.ここでは推移を取得したいのでTimeに注目する.

Availableをチェックしたときの初期状態である.

手動ですべての年にチェックを入れる.ここでVIEW RECENT YEARSの50にチェックを入れてしまうと1975年より前の年が選択できなくなってしまうので注意を要する.

右上のDownload optionsからExcelをクリックするとファイルがダウンロードされる.

ダウンロードしたファイルを開いてみると,データのないセルには..(ピリオド2つ)が入力されている.これは邪魔なので検索と置換で削除する.

コントロール+Hで検索と置換ダイアログを出し,検索文字列にピリオド2つ,置換後文字列は空白のまますべて置換する.

306737件を置換しましたと方向がある.
306737件置換しましたと報告

ここでファイルを保存して閉じる.
PowerQueryでの処理
EXCELを新規に開き,PowerQueryを起動する.Series Nameをプルダウンすると目的以外の行が多数あることがわかる.

目的のGDP(current US$)以外のチェックを外す.

Country Nameより左側の列を削除する.Country Name列とCountry Code列を選択し,「列のピボット解除」から「その他の列のピボット解除」を選択する.

「属性」列を選択し,「列の分割」から「文字数による分割」へと進む.

文字数を4,できるだけ左側で1回を選択する.

閉じて読み込む.

T_GDP.txtとして保存する.
SQL Serverへのインポート
最も重要な列のマッピングは下図のとおりにする.

成功すると下図のように報告される.

まとめ
世界各国の人口推移およびGDPの推移を取得した.データベースはそれぞれ国連,世界銀行である.世界経済の指標として有用であると考えられる.本稿では示していないが,一人当たりGDPも経済指標として有用であり,世界銀行のデータベースから抽出可能である.