RESASのデータをSQLを利用して取得する
はじめに
本記事ではTellusのJupyter Labを使って地域経済分析システム（RESAS）の統計データを取得する方法を紹介します。
地域経済分析システム（RESAS）の統計データを取得する
地域経済分析システム（RESAS）とは、経済産業省と内閣官房（まち・ひと・しごと創生本部事務局）が提供する、官民ビッグデータを集約し可視化するシステムです。
TellusではRESASが整備する統計データをデータベースとして保持しており、開発環境からアクセスすることができます。データベース接続情報に関しては、こちらをご覧ください。
それでは実際に、Tellus IDE（開発環境）からSQLクエリ（データベースに対する問い合わせ文のこと）を用いてデータを取得してみましょう。
import psycopg2
def connect():
return psycopg2.connect('postgresql://jovyan:password@postgres:5432/resas')
with connect() as conn:
with conn.cursor() as cur:
cur.execute('SELECT year, worker_total_15_24, worker_total_25_34, worker_total_35_44, worker_total_45_54, worker_total_55_64, worker_total_65_74, worker_total_over_74 FROM agriculture_related_country')
print('年齢別従事者数')
print('集計年 | 15～24歳 | 25～34歳 | 35～44歳 | 45～54歳 | 55～64歳 | 65～74歳 | 74歳以上')
for row in cur:
print(row)
データベースに接続するのに必要な情報は、データリファレンスの「データベース接続情報」を参照してください。
サンプルでは、agriculture_related_country（全国農業関連事業）テーブルから、年齢別従事者数_男女計を取得しています。全国農業関連事業のデータは5年毎に調査・集計されており、RESASでは2005年からのデータを参照することができます。
参考：農林業センサス
クエリの書き方次第で、条件を絞った検索も可能です。
with connect() as conn:
with conn.cursor() as cur:
cur.execute('SELECT year,city_name,agriculture_land_total,agriculture_land_ricefield,agriculture_land_field,agriculture_land_treefield FROM agriculture_land_city WHERE city_code=02201')
print('集計年 | 市町村名 | 耕作面積合計 | 田 | 畑 | 樹園地')
for row in cur:
print(row)
print('単位はアール')
サンプルでは、agriculture_land_city（市町村別耕作地）テーブルから、経営耕作面積（総面積、田、畑、樹園地）を青森市の市町村コードで絞り込んで取得しています。 RESASでは農業以外にも多数のデータを扱っており、観光系のデータベースもその一つです。農業系のデータを取得した方法と同様にクエリ文を作成し、データの問い合わせをします。
# 京都府の宿泊施設分析データを表示
with connect() as conn:
with conn.cursor() as cur:
cur.execute('SELECT year,month,prefecture_name\
display_class_name, facilities_num, guests_total, japanese_num, foreigners_num\
FROM hotel_analysis_hotel WHERE prefecture_code = 26 AND month != 0')
print('集計年 | 月 | 都道府県名 | 表示分類名 | 施設数 | 延べ宿泊者数 | 延べ日本人宿泊者数 | 延べ外国人宿泊者数')
for row in cur:
print(row)
print('*延べ宿泊者数は人数と宿泊日数の積（person_day)')
2011年から2017年までの各月のデータを取得することができます。月が0のデータは合計値を表すため、除いてあります。
RESASのデータからグラフを作る
取得したデータの年齢別従事者数を棒グラフにまとめてみましょう。
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
with connect() as conn:
with conn.cursor() as cur:
cur.execute('SELECT year, worker_total_15_24, worker_total_25_34, worker_total_35_44, worker_total_45_54, worker_total_55_64, worker_total_65_74, worker_total_over_74 FROM agriculture_related_country')
years = []
data = []
for row in cur:
years.append(row[0])
data.append(row[1:])
data = np.array(data)
bar_width = 0.3
bar_gutter = 0.1
set_width = bar_width * data.shape[0] + bar_gutter
x_points = np.arange(data.shape[1]) * set_width
xlabels = ['15-24', '25-34', '35-44', '45-54', '55-64', '65-74', 'over74']
xlocations = x_points + bar_width*(-1 + data.shape[0]) / 2
fig, ax= plt.subplots(figsize=(8, 4))
for i in range(len(data)):
plt.bar(x_points + bar_width*i, data[i], bar_width,label=years[i], align='center')
plt.xticks(xlocations, xlabels )
plt.xlim(-3*bar_gutter, x_points[-1] + set_width - bar_gutter)
ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda y, pos: "{:,}".format(int(y))))
ax.set_xlabel('age')
ax.set_ylabel('num')
plt.legend()
plt.show()
グラフから全ての年代において2005年の農業従事者数がもっとも多く、特に65歳以上の世代では100万人を超えているのがわかります。しかし、その後、どの年齢層で農業従事者は減少しており、農業離れと高齢化による農業従事者引退の影響が重なっていることが読み取れます。
次に、青森の2016年の農業販売額を市町村別に地図上で表示してみます。市町村のポリゴンは、値に応じた色を持ち、このような図をコロプレス図(choropleth map)と言います。
青森の市町村別農業販売額（2016年）は以下のクエリで取得可能です。
with connect() as conn:
with conn.cursor() as cur:
cur.execute('SELECT city_code,agri_output_total FROM agriculture_sale_city WHERE prefecture_code=2 AND year=2016')
agri_output_total = {}
for row in cur:
agri_output_total[str(row[0]).zfill(5)] = row[1]
print(agri_output_total)
サンプルコードでは、取得した値を市区町村コードをキーとする辞書オブジェクトにまとめなおしています。これにより、取得した値と市町村が一対一の関係になります（市区町村の形状はGeoJSONとして「国土数値情報 ダウンロードサービス」から入手できます）。 ※詳しいダウンロード方法はこちらの記事をご覧ください。 ダウンロードした青森県の行政区域データをJupyter Lab上にアップロードします。 
ファイルは「work」ディレクトリの下であればどこにアップロードして問題ありません。本チュートリアルでは、work下に新しいフォルダを作成し、そこにファイルをドラッグ・アンド・ドロップしました。アップロードしたファイルをgeopandasを使って呼び出します（geopandasはpandasを地理情報を扱いやすいよう拡張したライブラリです）。
import os
import geopandas as gpd
DIR_PATH = os.path.expanduser("~/work/data/") #ファイルを置いた場所に各自修正する
df = gpd.read_file(DIR_PATH + "N03-19_02_190101.geojson") #保存したファイル名に各自修正する
df[:3]
読み込んだデータを先頭から3件表示しています。このデータを地図上へプロットしてみましょう。
data = []
for code in df['N03_007']:
data.append(agri_output_total[code])
df['v'] = data
fig, ax = plt.subplots(figsize = (12, 6))
df.plot(column="v", edgecolor='black', figsize=(12, 8), cmap='Wistia', legend=True, ax=ax)
ax.set_xlabel('longitude')
ax.set_ylabel('latitude')
ax.set_title('Aomori\'s total output amount of 2016', fontsize='small')
plt.show()
青森県の各市区町村において、農業販売額が大きいほどオレンジに、小さいほど黄色に塗られた地図を作成することができました。
以上が、TellusのJupyterLabを使って地域経済分析システム（RESAS）の統計データを取得する方法でした。
RESASのデータは一次データです。本チュートリアルのように記述データとして表現したり、時系列分析により、今後のデータの変化を予測したり、様々なデータを組み合わせてある因果関係を統計的処理により発見したり、目的に合わせた利用をしてみてください。
今回使用したスクリプトです。
import psycopg2
def connect():
return psycopg2.connect('postgresql://jovyan:password@postgres:5432/resas')
with connect() as conn:
with conn.cursor() as cur:
cur.execute('SELECT year, worker_total_15_24, worker_total_25_34, worker_total_35_44, worker_total_45_54, worker_total_55_64, worker_total_65_74, worker_total_over_74 FROM agriculture_related_country')
print('年齢別従事者数')
print('集計年 | 15～24歳 | 25～34歳 | 35～44歳 | 45～54歳 | 55～64歳 | 65～74歳 | 74歳以上')
for row in cur:
print(row)
# 京都府の宿泊施設分析データを表示
with connect() as conn:
with conn.cursor() as cur:
cur.execute('SELECT year,month,prefecture_name\
display_class_name, facilities_num, guests_total, japanese_num, foreigners_num\
FROM hotel_analysis_hotel WHERE prefecture_code = 26 AND month != 0')
print('集計年 | 月 | 都道府県名 | 表示分類名 | 施設数 | 延べ宿泊者数 | 延べ日本人宿泊者数 | 延べ外国人宿泊者数')
for row in cur:
print(row)
print('*延べ宿泊者数は人数と宿泊日数の積（person_day)')
with connect() as conn:
with conn.cursor() as cur:
cur.execute('SELECT year,city_name,agriculture_land_total,agriculture_land_ricefield,agriculture_land_field,agriculture_land_treefield FROM agriculture_land_city WHERE city_code=02201')
print('集計年 | 市町村名 | 耕作面積合計 | 田 | 畑 | 樹園地')
for row in cur:
print(row)
print('単位はアール')
import numpy as np
import matplotlib.pyplot as plt
get_ipython().run_line_magic('matplotlib', 'inline')
with connect() as conn:
with conn.cursor() as cur:
cur.execute('SELECT year, worker_total_15_24, worker_total_25_34, worker_total_35_44, worker_total_45_54, worker_total_55_64, worker_total_65_74, worker_total_over_74 FROM agriculture_related_country')
years = []
data = []
for row in cur:
years.append(row[0])
data.append(row[1:])
data = np.array(data)
bar_width = 0.3
bar_gutter = 0.1
set_width = bar_width * data.shape[0] + bar_gutter
x_points = np.arange(data.shape[1]) * set_width
xlabels = ['15-24', '25-34', '35-44', '45-54', '55-64', '65-74', 'over74']
xlocations = x_points + bar_width*(-1 + data.shape[0]) / 2
fig, ax= plt.subplots(figsize=(8, 4))
for i in range(len(data)):
plt.bar(x_points + bar_width*i, data[i], bar_width,label=years[i], align='center')
plt.xticks(xlocations, xlabels )
plt.xlim(-3*bar_gutter, x_points[-1] + set_width - bar_gutter)
ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda y, pos: "{:,}".format(int(y))))
ax.set_xlabel('age')
ax.set_ylabel('num')
plt.legend()
plt.show()
with connect() as conn:
with conn.cursor() as cur:
cur.execute('SELECT city_code,agri_output_total FROM agriculture_sale_city WHERE prefecture_code=2 AND year=2016')
agri_output_total = {}
for row in cur:
agri_output_total[str(row[0]).zfill(5)] = row[1]
print(agri_output_total)
import os
import geopandas as gpd
DIR_PATH = os.path.expanduser("~/work/Aomori_district/") #ファイルを置いた場所に各自修正する
df = gpd.read_file(DIR_PATH + "N03-19_02_190101.geojson") #保存したファイル名に各自修正する
df[:3]
data = []
for code in df['N03_007']:
data.append(agri_output_total[code])
df['v'] = data
fig, ax = plt.subplots(figsize = (12, 6))
df.plot(column="v", edgecolor='black', figsize=(12, 8), cmap='Wistia', legend=True, ax=ax)
ax.set_xlabel('longitude')
ax.set_ylabel('latitude')
ax.set_title('Aomori\'s total output amount of 2016', fontsize='small')
plt.show()