全国の市区町村の財政

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

目次

言葉の定義

財政力指数

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

経常収支比率

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

実質公債費比率

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

地方債現在高

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

データのダウンロード

財政力指数

地域選択

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

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

表示項目選択

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

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

ダウンロード

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

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

ファイル名の変更

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

ダウンロードしたファイルを右クリックして「名前の変更」
ダウンロードしたファイルを右クリックして「名前の変更」

経常収支比率

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

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

ファイル名を CurrentAccountBalanceRatio に変更

実質公債費比率

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

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

ファイル名を RealDebtServiceRatio に変更

地方債現在高

ファイル名を CurrentAmountofLocalBonds に変更

データクレンジング

テキストエディタで開く

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

csvファイルをメモ帳から開く
csvファイルをメモ帳から開く

エラー文字をすべて置換

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

エラー文字を選択して「編集」から「置換」
エラー文字を選択して「編集」から「置換」

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

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

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

EXCEL で開く

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

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

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

「データの取り込み」で「インポート」
「データの取り込み」で「インポート」

Power Query エディターの起動

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

「データの変換」をクリックするとPower Queryエディターが起動する
「データの変換」をクリックするとPower Queryエディターが起動する

不要な列の削除

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

不要な列を選択して「列の削除」
不要な列を選択して「列の削除」

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

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

ヘッダー行より「上位の行の削除」
ヘッダー行より「上位の行の削除」

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

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

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

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

「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 でのテーブル名になる.

「ファイル」「名前を付けて保存」で拡張子をtxt
「ファイル」「名前を付けて保存」で拡張子をtxt

SQL Server へのインポート

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

ウィザードが起動する

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

データソースの選択

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

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

「データソースの選択」で「Flat File Source」
「データソースの選択」で「Flat File Source」
txtファイルを指定
txtファイルを指定

変換先の選択

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

「変換先の選択」で「SQL Server Native Client」
「変換先の選択」で「SQL Server Native Client」

マッピングの編集が重要

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

「マッピングの編集...」をクリック
「マッピングの編集…」をクリック

列マッピングの実際

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

「列マッピング」で詳細な設定を行う
「列マッピング」で詳細な設定を行う
列マッピング
列マッピング
列マッピング
列マッピング
列マッピング
列マッピング
データ型マッピングの確認
データ型マッピングの確認

インポートに成功

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

「操作は正常に実行されました」
「操作は正常に実行されました」

内部結合でデータ抽出

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

再び EXCEL へ

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

バブルチャートの作成

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

バブルチャートのデータ系列
バブルチャートのデータ系列
表示できるデータラベルの数は,最大で1000個です.これを超えているので,一部のラベルはグラフに表示されません.
表示できるデータラベルの数は,最大で1000個です.これを超えているので,一部のラベルはグラフに表示されません.

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

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

実質公債費比率

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

2016年度の北海道地方の財政
2016年度の北海道地方の財政

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

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

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

地方債現在高

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

2016年度北海道地方の地方債現在高
2016年度北海道地方の地方債現在高

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

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

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

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

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

2015年度北海道地方の人口,財政力指数,公債費比率
2015年度北海道地方の人口,財政力指数,公債費比率

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

2015年度北海道地方の人口,財政力指数,地方債現在高
2015年度北海道地方の人口,財政力指数,地方債現在高

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

まとめ

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

コメントを残す

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

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