コラム

  1. HOME
  2. コラム
  3. データ分析
  4. 木も見て森も見たい（５）～自治体財務データから見えるもの

木も見て森も見たい（５）～自治体財務データから見えるもの

2020.08.31

自治体財務データを眺めていくと何がみえるのか。連載テーマ「木も見て森も見たい」第五回をお送りします。

執行伝票・施設データを容易に紐付けるには？

【前回コラムでの作業結果より】

執行伝票
所管課名称 執行額（千円）
A課 \1,000
B課 \500
B課 \300
C課 \2,000
D課 \1,500
・・・

 

施設データ
施設名 所管課名称 延床面積（m²）
あ公民館 A課 300
い公民館 A課 500
う小学校 B課 1,000
え小学校 B課 1,500
おスポーツセンター C課 3,000
・・・ ・・・

前回までのコラムで、施設データに執行伝票を紐付けるところまで来ました。すぐにでも２つのデータを紐付けしたいのですが、その処理は、出来るだけ簡便に済ませたい所です。このような処理を行う場合、Excelに精通している方ならば、「VLOOKUP（関数）でしょ？」、SQLに精通している方ならば、「JOIN（結合）でしょ？」と即答されるかもしれません。
確かにその通りです。ただ、ExcelのVLOOKUPで処理を行うのは難しくないでしょうか？考え方というより、引数の順番をどうしても覚えられないのです。仮に覚えられても、すぐに忘れてしまうのです。
著者の考えでは、Excelの関数群はどうしても覚え難いのです。最新のExcelは入力支援機能も充実しており、「次はこれを入れなさい」と指示を出してくれるため、全てを覚える必要は無い、と言われるかもしれませんが、検索値が一致する行が複数あった場合、最初に出てくるデータしか参照できないなど、思わぬ相違が出てしまうこともあります。またSQLで処理しようとした場合、どうしても敷居が高くなってしまいます。
そこで便利なのが、これまでご紹介したRやPythonなどの言語でプログラミングされたツールを使う方法です。Excelの統一性の無い操作をパラパラと覚えるのが大変。とは言え、SQLに挑むのも大変…。そういう方にぴったりではないでしょうか？馴染みのない方も多いかと思いますが、今回用いる「R」（※1）は無料で利用できる統計分析用ソフトウェア（プログラミング言語）、「R studio」(※2)は「R」を快適に利用するための統合開発環境です。実行したい処理のスクリプトを保存・実行することでデータを素早く処理することができます。（※3）

さて、ここでのデータ紐付けのイメージは、「全ての施設データに対して、所属課名称を頼りにして、金額データを紐付ける。但し、面積按分を用いる。」ということでした。この場合、左に置いた施設データに、右から執行伝票を結合します。Rなどを用いると「Left Join」というコマンドで一発処理することが出来ます。但し「面積按分を用いる」という条件が付いておりますので、この部分をどう考えれば良いか？様々な手法があると思いますが、ここは最もシンプルな方法で進めていきます。

方法は、以下の手順になります。

  1. 「所管課が管理する面積」を計算して、新しい列として施設データに挿入。
  2. 各施設の床面積を、「所管課が管理する面積」で除した結果を「按分比率」として挿入。
  3. 執行伝票データを紐付ける。
  4. 執行金額に按分面積を乗じた結果を新しい列として挿入。
  5. 施設コストとして、施設単位に、按分された伝票金額を集計して完成。

手順１が分かりにくいかもしれません。表2の施設データのとおり、A課は「あ公民館；床面積300」と「い公民館；床面積500」の２つの施設に責任を持っておりますので、A課の管理する面積は800ということになります。
手順３では、加工した施設データに執行伝票を紐付けましたが、その際、「D課 \1,500」の伝票は、管理する施設が無いため、紐付けられることは有りません。結果として結果的に、施設コストの合計は、\3,800ということになりました。
手順４が終わったところまでの処理結果は、表3をご覧ください。

表3

手順１～5までの最終結果は、表4をご覧ください。

表4

Excelは万能だが…

ここまでコラムを読んでいただいて、おそらく次のような感想をお持ちでは無いかと思います。「あの…、これくらいならば簡単にExcelで出来るのでは？」と。
上記の表4は、Excelを熟知された方であれば、簡単に作成することが出来ます。しかしながら、表を作成する過程で、コピー＆ペーストを多用することになりませんでしょうか？セルの選択範囲を間違ったりしていませんでしょうか？それでも、今回の分量程度であれば、間違うことは無いでしょう。
では、仮に10,000行の件数を処理する場合は如何でしょうか？実際に、1年分の執行伝票を施設データに紐付けようとした場合、縦横巨大なExcelシートに苦戦すること、計算式を列に埋め込む過程において、引数の指定や範囲を間違えてしまうことが予想されますが、何よりもExcelシートだからこそ陥り易いのが、「可読性」の問題です。
Excelに精通された方ですと、ExcelシートにマクロやVBAを活用する事は多々あるかと思います。Accessについても同じ事が言えますが、Excelに精通されている方が作成、維持管理している時には問題は生じませんが、そうではない方に、シートの作成、維持管理を引き継いでしまうと大変です。Excelでマクロを使用していた場合、シートに含まれるマクロやVBAを理解できない。また、マクロやVBAコードについての「コメント」が記載されていても、コメント同士が重なっていてわかりづらい。最悪の結果として、これまでの努力が水の泡（マクロやVBAが使われなくなる）になりかねません。
RやPythonなどを用いた場合はどうなるのか？参考に、表３を作成するに際して、著者が作成したRのスクリプトを、図1に記載させていただきます。図1をご覧になって「Rのスクリプトの方が、分かりづらいのでは？」と思われる方もいらっしゃるかもれませんが、下記のスクリプトは、上から下へ処理が流れて行くだけです。”#”の記号は、コメント行(実行されない)ことを、”%>%”の記号は、処理結果を次に引き渡しているという意味です。
一例として、下記　図1>結合データ　#手順3に対応する行をご覧下さい。“left_join”というコマンドの右側に、紐付けたいデータを２つ並べて、媒介となるキー項目(この場合は「所管課名称」)を指定するだけで、膨大な量のデータも瞬時に処理する事が可能になります。
簡単とは言い切れませんが、Rに一旦慣れてしまえば、「意外と使えるかも！」と思われるのではないでしょうか？
（次回に続く）

図1

Rのスクリプト例

コラムニスト
公共事業本部　ソリューションストラテジスト　松村　俊英

参考

関連コラム

カテゴリー一覧へ戻る