環境省の捕獲鳥獣数の統計を折れ線グラフにする

 また面倒な統計を見つけてしまった.Power Query に食わせれば早いのかも知れないが,どうにも埒が明かないので手動でデータを整形することになった.頼むから第一正規形で公開してくれ…

データのダウンロード

環境省の鳥獣関係統計

 データソースは鳥獣関係統計である.ディレクトリを開くとうんざりする.目的はおそらく 11 番の「狩猟者登録を受けた者による捕獲鳥獣数」という EXCEL のファイルである.これを平成 10 年から平成 28 年までダウンロードする.

環境省の鳥獣関係統計の狩猟者登録を受けたものによる捕獲鳥獣数
環境省の鳥獣関係統計の狩猟者登録を受けたものによる捕獲鳥獣数

EXCEL でのデータクレンジング

時代遅れのレイアウト,ワークシート保護

 ファイルを開いてみると,印刷を前提としたレイアウトになっていることが分かる.ご丁寧にセル内スペースが挿入してあるため,検索が効かない.置換しようとするとパスワード付きでワークシートに保護がかかっている.もういい加減,こういうの辞めてほしい.

ワークシートに保護がかかっている
ワークシートに保護がかかっている
シート保護を解除しようとするとパスワードで保護されていて解除不能
シート保護を解除しようとするとパスワードで保護されていて解除不能

該当部位をコピペ

 セルのコピペは可能だったので新規ワークシートに値貼り付けを行う.

スペース,ゼロ,マイナス記号の削除

 検索と置換でそれぞれ削除.スペースがあると検索に引っかからなくなるためである.ゼロとマイナス記号は左外部結合により発生した NULL である.

テキストを手動で整形

 犬の道だと分かっている.二度と同じ作業はしないと誓いつつ,SQL Server にインポートするためのテキストづくりと割り切って作業を続行する.修行である.途中からミスが頻発し始め,その日の作業はここまでとした.続きは翌日だ.

 ここで記しておくが,今回取り扱った「捕獲数」とは全部が殺処分となった訳ではない.罠,網などで生け捕りにした数も含む「合計」である.元データを確認してほしいが,これには捕獲年月日や捕獲場所の情報がない.あくまでも年間の集計値である.

 例外はクマで,ほぼ全頭が射殺されている.動物倫理とか言うつもりはない.市民の生命に直結する事態であり,やむを得ない措置である.

新規ファイルから「クエリと接続」でインポート

ワークシート 1 枚ごとにインポート

ワークシート1枚ごとにクエリを新規作成
ワークシート1枚ごとにクエリを新規作成

Power Query での作業

「データ」「データの取得」「ファイルから」
「データ」「データの取得」「ファイルから」
「ナビゲーター」「データの変換」
「ナビゲーター」「データの変換」
「列の分割」「文字数による分割」
「列の分割」「文字数による分割」
「文字数による列の分割」
「文字数による列の分割」
タイトル名の変更
タイトル名の変更
「列のピボット解除」「選択した列のみをピボット解除」
「列のピボット解除」「選択した列のみをピボット解除」
列名を変更
列名を変更

ブック内のテーブルを「範囲に変換」

 ここまで来てようやく VBA の出番となる.下記コードを標準モジュールから実行してすべてのテーブルをセル範囲に変換する.これは使い捨てのコードなので保存しなくて良い.

「範囲に変換」
「範囲に変換」

全データを 1 枚のワークシートに集め,テーブルに変換

 コピペでデータを 1 枚のワークシートに集める.コピー元のワークシートは削除する.それをテーブルに変換する.

「挿入」「テーブル」「テーブル」
「挿入」「テーブル」「テーブル」

年度を年末日に変換

 A 列の「年度」は後々何かと使いにくいため,年末日に変更しておく.下記の数式を挿入する.

 これでピボットテーブルや各種グラフなどに応用しやすくなる.該当列をコピーして値貼り付けで参照関係を削除しておくのを忘れないように.

テキストファイルに出力

 SQL Server にインポートするためにテキストファイルに出力する.

 なぜデータベースが必要になるのか?これまで蓄積してきたデータと結合したいからである.リレーショナルデータベースの醍醐味は異なる軸のデータを同じ俎上に載せることにある.

SQL Server での作業

ウィザードを用いてインポート

 SQL Server でのインポートのうち最も重要なのが「列マッピング」である.下図を参考に変換先の列名,データ型,NULL 値の使用,サイズを変更しておく.

列マッピング
列マッピング
「操作は正常に実行されました」
「操作は正常に実行されました」

クエリ

 ここではクマ,イノシシ,シカ,サルを抽出したい.代表的な獣害がこの野生動物だからである.ワイルドカードを使って対象を絞り込んでいく.

クマを抽出

 北海道はヒグマ,本州以南ではツキノワグマである.両者を抽出したい.まず下記のクエリを書いた.

 これだとその他にアライグマやアナグマも引っかかってくる.

 ざっと見たところ,クマはこれで抽出できているようだ.

イノシシを抽出

シカを抽出

サルを抽出

 不思議なことに,サルが抽出されてこない.

 テーブルにも元の EXCEL ファイルにもサルのデータが存在しない.どうやらサルは駆除の対象から外れているらしい.

 環境省特定鳥獣保護・管理計画作成のためのガイドライン(ニホンザル編・平成27年度)によると,駆除ではなく「防除」と呼ぶらしく,ガイドライン中に 88 回出現する.「捕獲」は 274 回出現し,「殺」という文字は 4 回(殺処分 3 回,捕殺 1 回)出現する.

 殺処分に至るにはフローチャートがある.加害レベルが 0 から 5 まで 6 段階あり,捕獲は加害レベル 2 からである.加害レベルが上がるにつれて群れのサイズと配置が大きくなり,選択捕獲,部分捕獲,群れ捕獲と対応が強化されていく.

サルの加害レベルに応じた捕獲オプションの選択手順
サルの加害レベルに応じた捕獲オプションの選択手順

クマ,イノシシ,シカを抽出

 サルを除いた上記 3 種の動物を抽出するクエリは以下の通りである.

おまけ

 一部のデータで PrefCode の先頭のゼロが欠損していた.それを修正するクエリである.

ヘッダー付きでコピーし EXCEL に貼り付け

 クエリの結果を右クリックから「ヘッダー付きでコッピー」し EXCEL の新規ワークシートに貼り付ける.EXCEL のおせっかい機能により PrefCode の先頭のゼロが再び消える.今回は次に述べるコードでループカウンタと照合するため,このままにしておく.

EXCEL でチャートの作成

捕獲場所のジオデータを公開せよ

 現地の市民にしてみれば「あそこの地域だな」と見当がつくだろう.

 しかし,環境省が年間の都道府県別の集計値しか公表していないので,市区町村レベルでの捕獲数が不明であり,これは粒度が荒すぎると思う.日時も不明だ.

 もっと言えば,捕獲場所のジオデータが欲しい.経度と緯度,可能なら高度も必要だ.このデータがないと正確なマッピングができない.猟友会の協力が不可欠と思われるが,環境省には捕獲場所の経度と緯度の報告を義務づけてもらいたい.

チャート作成の実際

 文句を言っていても仕方がないので手元のデータからチャートを作っていく.都道府県別,動物別の捕獲数の推移を示したい.

時系列で推移を示すのは折れ線グラフ

 テーブルを見れば見当がつくと思うが,1 つのチャートにデータ系列は 3 つの折れ線グラフとなる.ある年だけを取り出すなら地図上に立体の棒グラフを立てるのも視覚効果として有効だ.

 今回は折れ線グラフで行くことにする.

VBA での記述

 細かいことはいい.下記コード参照のこと.

チャート

 結果の折れ線グラフを示す.クマは青,シカはオレンジ,イノシシは灰色で示されている.

都道府県別のシカ,イノシシ,クマの捕獲数
都道府県別のシカ,イノシシ,クマの捕獲数

 北海道ではシカの捕獲数が圧倒的だが,クマも相当なものである.全国でイノシシの被害の報告が増えてきたことをデータも支持している.

 四国,九州沖縄地方ではクマはほぼ絶滅したと考えられ,捕獲報告はない.

まとめと考察

 環境省の捕獲鳥獣数の統計からクマ,イノシシ,シカの捕獲数に限定して都道府県別の折れ線グラフにした.地域によって様相が異なる.

 さらに精度の高い野生動物の行動を補足するには,捕獲場所のジオデータ(経度と緯度)と日時のデータが必要と考えられ,猟友会の協力が必要である.

One Reply to “環境省の捕獲鳥獣数の統計を折れ線グラフにする”

コメントを残す

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

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