全国の市区町村の財政

　総務省の e-Stat から全国の市区町村の財政状況を垣間見る．衰退する都市には財政破綻の徴候が見られるはずである．今回は財政力指数，経常収支比率，実質公債費比率，地方債現在高に注目する．

言葉の定義

財政力指数

　基準財政収入額を基準財政需要額で除して得た数値．この数値が 1 に近い，または 1 を超えるほど自主財源の割合が高く，財政力が強い．

経常収支比率

　一般財源（地方税，普通交付税）などのうち，経常的に支出される経費（人件費，公債費）に充当されたものの占める割合．比率が低いほど財政運営に弾力性があり，政策的に使える財源がある．

実質公債費比率

　自治体財政の健全度を判断するための指標で，自治体の標準的な財政規模に占める借金返済の割合．この比率が 18 % 以上の自治体は起債に許可が必要となり，25 % 以上では起債が制限される．

地方債現在高

　定義は総務省による地方債現在高の状況によると，前年度末現在高(A) + 当年度発行額(B) – 当年度元利償還額_元金(C) という計算になる．

データのダウンロード

財政力指数

地域選択

　e-Statの「地域選択」で「すべて選択」する．

表示項目選択

　「表示項目選択」で「D行政基盤」「D2財政力」「D22市町村財政」と進み「D2201財政力指数」「D2205基準財政収入額」「D2206基準財政需要額」を選択する．

ダウンロード

　「ダウンロード設定」で「注釈を表示する」をオフ，「データがない行を表示しない」「データがない列を表示しない」をオン，「桁区切りを使用しない」にチェックしてダウンロードする．

ファイル名の変更

　ダウンロードしたファイルを右クリックして「名前の変更」を選ぶ．ここでは FiscalPowerIndex としておく．

経常収支比率

　D2 から「D2203 経常収支比率」「D2209 一般財源」を選択する．D3 から「D320312 公債費」「D320401 人件費」を選択する．

ファイル名を CurrentAccountBalanceRatio に変更

実質公債費比率

　D2から「D2211 実質公債費比率」を選択する．

ファイル名を RealDebtServiceRatio に変更

地方債現在高

ファイル名を CurrentAmountofLocalBonds に変更

データクレンジング

テキストエディタで開く

　ダウンロードした 4 つの csv ファイルを右クリックして「プログラムから開く」「メモ帳」と進む．

エラー文字をすべて置換

　エラー文字を選択して「編集」から「置換」と進む．「すべて置換」をクリックする．

　同様に他のエラー文字も「すべて置換」する．

　先頭の数行は削除して保存する．

EXCEL で開く

　「データ」「データの取得」「ファイルから」「テキストまたはCSVから」と進む．

　「データの取り込み」で該当ファイルを選択して「インポート」をクリックする．

Power Query エディターの起動

　「読み込み」をクリックすると Power Query を経由せずにインポートされる．「データの変換」をクリックすると Power Query エディターが起動する．

不要な列の削除

　不要な列を選択して「列の削除」する．ここでは「調査年コード」「/項目」が該当する．

ヘッダー行より上の行を削除

　ヘッダー行を選択して「上位の行の削除」をクリックする．

　「上位の行の削除」で削除する行数を指定する．

1行目をヘッダーとして使用

　「1行目をヘッダーとして使用」をクリックする．

閉じて読み込む

　最後に「閉じて読み込む」．

クエリと接続

　「クエリと接続」でテーブルに読み込んだ件数が表示される．

　上記の作業を csv ファイル 4 つすべてで繰り返す．結果は下図のようになる．

テーブルのフィルターで空白行を削除

　ここまでの作業でワークシートが 4 枚増えているはずだ．ファイル名を City Finance として保存しよう．

　次に行うのは空白行の削除である．ご承知の通り，優先すべき列は D2203 経常収支比率, D2201 財政力指数, D2211 実質公債費比率, D3205 地方債現在高である．これらをフィルターにかけて空白行を抽出し，削除する．削除した行数を示しておこう．

項目コード	項目	インポート行数	削除行数
D2203	経常収支比率	32,572	5,852
D2201	財政力指数	70,807	21,979
D2211	実質公債費比率	17,244	1,624
D3205	地方債現在高	13,412	1,225

数値を実数に変換

　パーセンテージ表記は小数に，千円単位の表記は円単位に変換する．列を挿入してそれぞれ 0.01 や 1,000 を掛け算する．財政力指数はそのままにしておく．

TEXT関数で地域コードの桁数を揃える

　TEXT 関数は数値で表現された固定長のコードを作成するのに極めて有用である．先頭がゼロでも切れず，データ型を文字列に変換してくれる．

値の貼り付けで数式の参照関係を解消

　数式の参照関係を解消するためにコピーして値の貼り付けを行う．

市区町村マスターの作成

ワークシートをコピー

　財政力指数のワークシートをコピーして市区町村マスターを作成する．財政力指数のワークシートを選んだのは，単にレコード数が最大だからである．

　B 列の「地域コード」と C 列の「地域」を残し，他の列は削除する．「テーブルデザイン」タブの「ツール」から「重複の削除」をクリックする．

　キーとなる項目のみチェックする．ここでは「地域コード」である．

　重複が削除され一意の値が残る．一意であることはキーの重要な要件である．

都道府県コードを抽出

　列を一つ挿入し，数式を使って都道府県コードを作出する．LEFT 関数で左から 2 文字を抽出するだけである．

都道府県名を抽出

　列を一つ挿入し，FIND 関数と LEFT 関数を組み合わせて「地域」から都道府県名を抽出する．「地域」に半角スペースがあるのがここで効いている．

地方を作出

　これは少々手間がかかる．全国を 8 つの地方に分類するのだが，都道府県コードでフィルターをかけ，手作業でコピペすることになる．都道府県コードが北から順番に振られていて，助かる．

値の貼り付けで参照関係を解消

　数式の参照関係を解消するために値の貼り付けを行う．半角スペースを削除するのはその後である．

ワークシート名を変更する

　ブック内のワークシート名を下記のように変更する．

M_CITY
D2203
D2201
D2211
D3205

　変更しなくても良いのだが，次に SQL Server にインポートする際の目印とするためでもある．

SQL Server

txtファイルにエクスポート

　「ファイル」「名前を付けて保存」で拡張子を .txt として保存する．ファイル名をワークシート名に変更しておく．ここで命名したファイル名が SQL Server でのテーブル名になる．

SQL Server へのインポート

　SQL Server Management Studio を起動する．先に作成しておいた日本の都市人口データベースがあるので，そのデータベースを右クリックして「タスク」「データのインポート…」と進む．

ウィザードが起動する

SQL Server Management Studioのオブジェクトエクスプローラーでデータベースを右クリックして「タスク」「データのインポート...」 — SQL Server Management Studioのオブジェクトエクスプローラーでデータベースを右クリックして「タスク」「データのインポート…」

データソースの選択

　「データソースの選択」で「Flat File Source」を選択する．「参照…」をクリックして txt ファイルを選択する．

　ここでは D2201 を例に挙げているが，D2203, D2211, D3205 いずれも操作は共通である．

変換先の選択

　「変換先の選択」ではリスト最後の SQL Server Native Client を選択する．

マッピングの編集が重要

　最も重要な作業である．「マッピングの編集…」をクリックする．

列マッピングの実際

　ここで変換先の列名を変更できる．データ型，データ長を指定する．空白行は予め削除してあるので，「NULLの許可」はチェックを外す．

インポートに成功

　Finish をクリックし，インポートに成功すると下図のようになる．ここで失敗する原因にはいくつかあるが，最も多い原因として，空白行の存在による NULL のチェックに引っかかる，データ型の間違い，設定したデータ長よりも大きなデータの存在，などがある．それぞれその手順に戻って操作をやり直す．

内部結合でデータ抽出

　クエリは以下の通りである．

SELECT	M_City.CityCode			AS '市区町村コード'
	,	M_City.City				AS '市区町村'
	,	M_City.PrefectureCode	AS '都道府県コード'
	,	M_City.Prefecture		AS '都道府県'
	,	M_City.Region			AS '地方'
	,	Balance.Year			AS '年度'
	,	Balance.D2201			AS '財政力指数'
	,	FiscalPower.D2203		AS '経常収支比率'
	,	DeptRatio.D2211			AS '実質公債費比率'
	,	LocalBonds.D3205		AS '地方債現在高'
FROM	M_City
INNER	JOIN dbo.D2201	AS Balance
ON	M_City.CityCode =	Balance.CityCode

INNER	JOIN dbo.D2203	AS FiscalPower
ON	Balance.CityCode = FiscalPower.CityCode
AND	Balance.Year = FiscalPower.Year

INNER	JOIN dbo.D2211	AS DeptRatio
ON	Balance.CityCode = DeptRatio.CityCode
AND Balance.Year = DeptRatio.Year

INNER	JOIN dbo.D3205	AS LocalBonds
ON	Balance.CityCode=LocalBonds.CityCode
AND Balance.Year = LocalBonds.Year;

(12017 行処理されました)

再び EXCEL へ

　抽出された結果を「ヘッダー付きでコピー」してエクセルに貼り付け，テーブルに変換する．

バブルチャートの作成

　下図を参考にバブルチャートのデータ系列を作成する．実際には 1,000 を超えるラベルは扱えないため，エラーとなり EXCEL はフリーズする．

表示できるデータラベルの数は，最大で1000個です．これを超えているので，一部のラベルはグラフに表示されません．

やむを得ず地方ごとに表示

　テーブルにあらかじめフィルターをかけて件数を絞り込む．地方がよいだろう．総数 12 万件である．1,000 で割ると 12 だが，さらに年度で 16 等分されるので何とかなりそうだ，と見当をつける．

実質公債費比率

　下図は 2016 年度の北海道地方の財政である．横軸は財政力指数，縦軸は経常収支比率，バブルサイズが実質公債費比率である．

　定義上，財政力指数は高いほど良好，逆に経常収支比率は低いほど良好であった．つまり，このチャートでは右下ほど良いということで，左上ほど悪いということになる．

　二つの例外があることが分かる．一つは右下の孤立した小さなバブル，もう一つが左上の巨大なバブルである．右下のバブルはテーブルの財政力指数に数値フィルターをかけると出てくるが，北海道泊村である．左上のバブルは経常収支比率に数値フィルターをかけると出てくるが，北海道夕張市である．かたや原子力政策に乗った都市，かたや財政再建団体．対照的であるが，どちらが良いとは一概には言えない．

　札幌市はどうなのだろう？財政力指数は 0.73 とまずまずだが，経常収支比率が 0.947 とカツカツであることが分かる．つまり，投資に回す余裕がないということだ．

地方債現在高

　今のデータ系列のバブルサイズだけを実質公債費比率から地方債現在高に変更すると，また違った風景が見えてくる．

　ひときわ大きなサイズのバブルが目立つ．これはテーブルを地方債現在高で降順ソートすると分かるが，札幌市である．

　これらのチャートから，財政力指数は良好だが，借金が積み上がり，その返済に追われて投資的な財政の余裕を失っている，という札幌市の姿が見えてくる．

人口テーブルを加えてみる

　リレーショナルデータベースがその真価を発揮するのは，テキストファイルから SQL Server に気象データをインポートするで示したように，共通するキー（ディメンション）を媒介にしてテーブルを結合することにより，異なる軸のデータを同じ俎上に載せる時である．今度は先のデータベースから人口を加えたデータを抽出してみよう．

SELECT	M_City.CityCode			AS '市区町村コード'
	,	M_City.City				AS '市区町村'
	,	M_City.PrefectureCode	AS '都道府県コード'
	,	M_City.Prefecture		AS '都道府県'
	,	M_City.Region			AS '地方'
	,	Balance.Year			AS '年度'
	,	P.A1101_TotalPopulation	AS '総人口'
	,	P.A05101_PopulationChangeRate	AS '人口増減率'
	,	Balance.D2201			AS '財政力指数'
	,	FiscalPower.D2203		AS '経常収支比率'
	,	DeptRatio.D2211			AS '実質公債費比率'
	,	LocalBonds.D3205		AS '地方債現在高'
FROM	M_City
INNER	JOIN dbo.D2201	AS Balance
ON	M_City.CityCode =	Balance.CityCode

INNER	JOIN dbo.D2203	AS FiscalPower
ON	Balance.CityCode = FiscalPower.CityCode
AND	Balance.Year = FiscalPower.Year

INNER	JOIN dbo.D2211	AS DeptRatio
ON	Balance.CityCode = DeptRatio.CityCode
AND Balance.Year = DeptRatio.Year

INNER	JOIN dbo.D3205	AS LocalBonds
ON	Balance.CityCode=LocalBonds.CityCode
AND Balance.Year = LocalBonds.Year

INNER	JOIN dbo.T_Population	AS P
ON	Balance.CityCode = P.CityCode
AND Balance.Year = P.Year;

(3430 行処理されました)

　同様に抽出結果を「ヘッダー付きでコピー」して EXCEL に貼り付ける．バブルチャートを挿入し，データ系列の系列Xの値に総人口，系列Yの値に財政力指数，バブルサイズに公債費比率を投入したのが下図である．

　さらにバブルサイズだけを地方債現在高に入れ替えたのが下図である．

　こうして眺めると，札幌市だけが巨大な例外なのではなく，人口に比例して他の中小都市とも地続きであることが理解できる．それにしても，北海道泊村の孤高ぶりは異常である．

まとめ

　都市の人口データベースに財政の指標を加えて考察を試みた．北海道地方においては，人口と財政規模を同じチャートに載せると，巨大な都市も中小都市も地続きであることが分かった．同様の傾向が全国で見られるか，検証してみたい．

言葉の定義

財政力指数

経常収支比率

実質公債費比率

地方債現在高

データのダウンロード

財政力指数

地域選択

表示項目選択

ダウンロード

ファイル名の変更

経常収支比率

ファイル名を CurrentAccountBalanceRatio に変更

実質公債費比率

ファイル名を RealDebtServiceRatio に変更

地方債現在高

ファイル名を CurrentAmountofLocalBonds に変更

データクレンジング

テキストエディタで開く

エラー文字をすべて置換

EXCEL で開く

Power Query エディターの起動

不要な列の削除

ヘッダー行より上の行を削除

1行目をヘッダーとして使用

閉じて読み込む

クエリと接続

テーブルのフィルターで空白行を削除

数値を実数に変換

TEXT関数で地域コードの桁数を揃える

値の貼り付けで数式の参照関係を解消

市区町村マスターの作成

ワークシートをコピー

都道府県コードを抽出

都道府県名を抽出

地方を作出

値の貼り付けで参照関係を解消

ワークシート名を変更する

SQL Server

txtファイルにエクスポート

SQL Server へのインポート

ウィザードが起動する

データソースの選択

変換先の選択

マッピングの編集が重要

列マッピングの実際

インポートに成功

内部結合でデータ抽出

再び EXCEL へ

バブルチャートの作成

やむを得ず地方ごとに表示

実質公債費比率

地方債現在高

人口テーブルを加えてみる

まとめ

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル