国連の人口データおよび世界銀行のGDPデータをダウンロードする

 世界各国の人口推移およびGDP推移を取得したい.そんな場合は国連世界銀行のデータを活用する.今回は国連から人口推移,世界銀行からGDP推移のデータをそれぞれ取得したので経緯を紹介する.

国名コードを取得する

 国名コードは法務省日本学術振興会がそれぞれ2文字,3文字のコードを紹介しているが,Wikipedia(ISO 3166-1)のものが両者を網羅していて使いやすい.今回はこちらを取得することにする.

EXCELのPowerQueryでデータを取得しテキスト形式で保存する

 Wikipediaの該当ページのテーブルをEXCELのPowerQueryで取得する.ブラウザのURLをコピーしておく.データタブのWebからをクリックする.

「データ」「Webから」
「データ」「Webから」

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

URLをペースト
URLをペースト

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

Webコンテンツへのアクセスで「接続」
Webコンテンツへのアクセスで「接続」

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

ナビゲーター
ナビゲーター

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

「提案されたテーブル」をクリック
「提案されたテーブル」をクリック

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

PowerQueryエディタ画面
PowerQueryエディタ画面

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

最後の列は削除しておく
最後の列は削除しておく

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

閉じて読み込む
閉じて読み込む

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

ファイルメニューから名前をつけて保存
ファイルメニューから名前をつけて保存

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

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

SQL Serverにインポートする

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

データベースを右クリックして「タスク」「データのインポート」
データベースを右クリックして「タスク」「データのインポート」

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

データソースの選択で「Flat File Source」
データソースの選択で「Flat File Source」

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

ファイルを設定したところ
ファイルを設定したところ

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

「列」の概観.ここでNextを押さずに「詳細設定」へ
「列」の概観.ここでNextを押さずに「詳細設定」へ

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

「詳細設定」で「国・地域名」「英語名」「フランス語名」のOutputColumnWidthを50から500に拡張しておく
「詳細設定」で「国・地域名」「英語名」「フランス語名」のOutputColumnWidthを50から500に拡張しておく

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

変換先の選択では「SQL Server Native Client11.0」を選択
変換先の選択では「SQL Server Native Client11.0」を選択

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

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

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

列マッピングではデータ型およびデータ長を指定
列マッピングではデータ型およびデータ長を指定

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

上図のように列のデータ型,データ長,NULLの可否を指定する
上図のように列のデータ型,データ長,NULLの可否を指定する

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

成功すると「***行転送されました」と表示される
成功すると「***行転送されました」と表示される

国連の人口データを取得する

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

Googleで「世界 区別人口 推移」と検索した結果
Googleで「世界 区別人口 推移」と検索した結果

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

統計局のページで「人口」を検索
統計局のページで「人口」を検索

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

主要出典資料名一覧の最初のWorld Population Prospectをクリックする
主要出典資料名一覧の最初のWorld Population Prospectをクリックする

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

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

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

最初に目的のファイルがあるのでクリックする
最初に目的のファイルがあるのでクリックする

EXCELのPowerQueryでデータを取得しテキスト形式で保存する

 開くと膨大な表が表示される.意外なことにテーブル化されていない.後の工程のためにここでテーブル化しておく.A17から最後のセルまでを選択し,テーブル化する.

開くと膨大な表が表示される
開くと膨大な表が表示される
A17から最後のセルまでを選択しテーブル化する
A17から最後のセルまでを選択しテーブル化する

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

データタブの「データの取得」「EXCELブックから」
データタブの「データの取得」「EXCELブックから」

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

WPP2024_GEN_F01_DEMOGRAPHIC...のファイル名が該当する
WPP2024_GEN_F01_DEMOGRAPHIC…のファイル名が該当する

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

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

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

ISO3 Alpha-codeの列を選択し「空の削除」

ISO3 Alpha-codeの列を選択し「空の削除」
ISO3 Alpha-codeの列を選択し「空の削除」

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

人口の列は1000で割ってあることに注意
人口の列は1000で割ってあることに注意

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

カスタム列を追加
カスタム列を追加

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

最初の人口の列をダブルクリックし,*1000とタイプする
最初の人口の列をダブルクリックし,*1000とタイプする
閉じて読み込む
閉じて読み込む
17538行読み込まれましたと表示される
17538行読み込まれましたと表示される

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

ファイルメニューの名前をつけて保存
ファイルメニューの名前をつけて保存

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

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

SQL Serverにインポートする

ウィザードのダイアログで先ほど作成したT_TotalPopulation.txtを選択する
ウィザードのダイアログで先ほど作成したT_TotalPopulation.txtを選択する
データソースの選択でファイルを指定したところ
データソースの選択でファイルを指定したところ

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

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

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

列マッピングは図のように設定する
列マッピングは図のように設定する
正常に終了する
正常に終了する

世界銀行のGDP推移を取得する

データのダウンロードと前処理

 世界銀行のDataBankからGDP(current US$)をクリックする.

世界銀行のDataBank
世界銀行のDataBank

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

World Development Indicatorsの初期画面
World Development Indicatorsの初期画面

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

Availableをチェックしたときの初期状態
Availableをチェックしたときの初期状態

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

手動ですべての年にチェックを入れる
手動ですべての年にチェックを入れる

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

Download optionsからEXCELをクリック
Download optionsからEXCELをクリック

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

データのないセルには..が入力されている
データのないセルには..が入力されている

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

Ctrl:Hで置換ダイアログを出し,検索文字列に..を入力し,置換文字列は空白のまますべて置換
Ctrl+Hで置換ダイアログを出し,検索文字列に..を入力し,置換文字列は空白のまますべて置換

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

306737件置換しましたと報告

306737行を置換しましたと報告
306737件を置換しましたと報告

 ここでファイルを保存して閉じる.

PowerQueryでの処理

 EXCELを新規に開き,PowerQueryを起動する.Series Nameをプルダウンすると目的以外の行が多数あることがわかる.

Series Nameを表示したところ
Series Nameを表示したところ

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

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

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

Country NameとCountry Code以外の列をピボット解除
Country NameとCountry Code以外の列をピボット解除

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

「属性」列を選択し「列の分割」から「文字数による分割」
「属性」列を選択し「列の分割」から「文字数による分割」

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

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

 閉じて読み込む.

閉じて読み込むと11146行読み込まれたと報告
閉じて読み込むと11146行読み込まれたと報告

 T_GDP.txtとして保存する.

SQL Serverへのインポート

 最も重要な列のマッピングは下図のとおりにする.

列マッピングでは図のようにデータ型,データ長,NULLの可否を選択
列マッピングでは図のようにデータ型,データ長,NULLの可否を選択

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

11146行転送されました
11146行転送されました

まとめ

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

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください