JAPAN SYSTEMS Driving for NEXT NEW with Comfort and Convenience

木も見て森も見たい(5)~自治体財務データから見えるもの

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

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

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

執行伝票
所管課名称執行額(千円)
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
・・・ ・・・  

前回までのコラムで、施設データに執行伝票を紐付けるところまで来ました。すぐにでも2つのデータを紐付けしたいのですが、その処理は、出来るだけ簡便に済ませたい所です。このような処理を行う場合、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. 施設コストとして、施設単位に、按分された伝票金額を集計して完成。

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

表3

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

表4

Excelは万能だが…

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

図1

Rのスクリプト例

コラムニスト
淑徳大学コミュニティ政策学部 専任講師 松村 俊英

参考

※1
統計数理研究所:https://cran.ism.ac.jp/
※2
RStudio:https://rstudio.com/products/rstudio/download/
※3
総務省ICTスキル総合習得プログラム>[コース4]オープンデータ・ビッグデータ利活用事例
4-3 プログラミングによるビッグデータの分析(R):
https://www.soumu.go.jp/ict_skill/pdf/ict_skill_4_3.pdf

関連コラム

Adobe® Reader®

PDFファイルの閲覧には、Adobe® Reader®が必要です。

Adobe® Reader®のダウンロード

お問い合わせ