前回まででBigQueryにデータを入れ、データポータル(Data Studio)での可視化を行いました。
基本的な使い方を一通りできるようになったので、今度は場数を踏むためにもっとデータ分析を細かくできるようにしていこうかと思います。
前回まで一応頑張った記事↓
BigQueryにオープンデータを入れてみた｜@kashii
オープンデータをGoogleデータポータルで可視化してみた｜@kashii
今回の流れ
今回は何をするのか記録するために順序だてて書いていきます。
- 目的の提示
- 仮説だて
- データ集め
- データの前処理
- データをStorageに入れる
- データをBigQueryと連携させる
- クエリを打ってビューを保存
- データポータルで可視化
- 壁にぶち当たる
- レポートを作ってみる
- 分析した結果をまとめてみる
- 次にできることはなにか
1.目的の提示
最近、同年代の友人と話しているなか、各都道府県の友人が集まっていたので「〇〇県は運転が荒いよね」「いや、〇〇県のほうが荒いんじゃないか？」という話になりました。
どこの都道府県が運転が荒いのか気になったので実際にどの都道府県が「運転が荒い」のかを分析してみようかなと思い立ち、データ分析をしてみることにしました。
2.仮説だて
「どの都道府県が運転が荒いのか」というのを知るのが目的でした。
しかし、「運転が荒い」だけだと何を基準とすればよいのかわからないため、
定義として一旦以下のようなことを考えてみました。
■運転が荒い
1.他の県と比較し交通事故発生件数が多い
2.他の県と比較し危険運転が多い
3.データ集め
オープンデータは警察庁のデータベースから。
統計表｜警察庁Webサイト
もともとは以下のようなデータでした。いままで使用したことがあるデータはkaggleなどだったため、すでに前処理がされている状態だったので何もすることはありませんでしたが、今回になって初めてデータの前処理をすることになりました。
■使用したデータ
2019年度を基準として使用しています。
・統計表｜警察庁Webサイト
・人口推計（2019年（令和元年）10月1日現在）
‐全国：年齢（各歳），男女別人口 ・ 都道府県：年齢（5歳階級），男女別人口‐｜総務省統計局
・車種別（詳細）保有台数表｜自動車検査登録情報協会
4.データの前処理
SQLに入れられるようデータを整理したあとはこのような形になりました。ほかにもあとでいじくったので参考程度になります…。
基本的に文字化けしてしまうので日本語ではなく、英語表記にしました。ただ、都道府県名は視認しやすくするために日本語にしようと頑張りました。正しい方法かはわからないのでマネをするのはおすすめしません。
■日本語でBigQueryに入れるための流れ
1.excelファイルで保存
2.csvファイルに変更して保存
3.逆に文字化けさせる
4.そのままBigQueryにぶち込む
5~8.データをBigQueryにいれて処理
5.データをStorageに入れる
6.データをBigQueryと連携させる
7.クエリを打ってビューを保存
8.データポータルで可視化
5~8は前回やったので省略してみました。流れはこんな感じ。
1.Storageに入れる
2.BigQueryと連携させる
3.クエリを打ってビューを保存
4.データポータルで可視化
9.壁にぶち当たる
データポータルで可視化…可視化ができない…
一回目、データを可視化しようと思ったけれどあまりできず…。
初心者過ぎてなぜできないかわからないのでとりあえず
1.データが変
2.データポータルの操作に慣れてない
の2つではないかと考えました。
そのため、自分でデータを持っていて連携しやすい、GAのデータとYouTubeStudioのデータを使ってデータポータルの操作に慣れる時間をつくりました。
【閑話】データポータルに慣れよう
その操作は→こちら
10.レポートを作ってみる
11.分析した結果をまとめてみる
データの前処理↔BigQuery↔データポータルで繰り返し処理する練習をしよう
都道府県別に交通事故発生件数とそれによる負傷者数、死者数をまとめて降順にしてみました。
prefectureは日本語にしたかったのですが上手い事行かずに数字で割り振っています。
わかりにくい部分は対応する日本語を以下で示しています。
| prefecture | 都道府県名 |
| accident | 交通事故発生件数 |
| injuriesi | 交通事故による負傷者数 |
| fatalieties | 交通事故による死者数 |
交通事故発生件数を中心に見てみるとランキングは以下のようになります。
| 順位 | 都道府県 |
|---|---|
| 1 | 大阪 |
| 2 | 愛知 |
| 3 | 東京 |
| 4 | 福岡 |
| 5 | 静岡 |
| 6 | 神奈川 |
| 7 | 兵庫 |
| 8 | 埼玉 |
| 9 | 千葉 |
この時点では「やはり大阪とか人が多いところは交通事故発生数も多いのか…？」と思っていました。しかし、10万人あたりでの事故発生件数を確認してみると一気に変化します。
| 順位 | 都道府県 |
|---|---|
| 1 | 静岡 |
| 2 | 佐賀 |
| 3 | 宮崎 |
| 4 | 群馬 |
| 5 | 福岡 |
| 6 | 香川 |
| 7 | 兵庫 |
| 8 | 愛知 |
| 9 | 山形 |
静岡はもともと5位でランクインしていたので理解はできますが、いきなりほかの県がランクインしました。静岡の次に佐賀が入り、3位は宮崎となりました。このことから、ほかの要因があるのではないかと考え、違うデータも探ってみることにしました。
乗用車の保有台数を見てみる
次に調査してみたのは都道府県別一人当たりの乗用車の保有台数です。一応、交通事故が車以外もカウントされるので、乗用車と自転車を含め、一人当たりの保有者のランキングをつくってみました。
※ただ単に出したかっただけなので棒グラフにしています。
一人当たりの保有乗用車のランキングは以下の通りになります。
| 順位 | 都道府県 |
|---|---|
| 1 | 和歌山 |
| 2 | 山梨 |
| 3 | 福井 |
| 4 | 富山 |
| 5 | 岐阜 |
| 6 | 群馬 |
| 7 | 茨城 |
| 8 | 栃木 |
| 9 | 福島 |
こうやって見てみると群馬はランクインしていますがほかの保有者の多い県がランクインはしていません。そのため、車の保有台数とは相関がないように思います。
ここまで見てみるとなにもないぞ…と手詰まりしてしまいました。
追加でデータを見つけてきて分析してみる予定です。
12.次にできることはなにか
1.今回できるようになったこと
・オープンデータからデータを抜き出し整理する
・日本語でBigQueryにデータを入れる
・データポータルでレポートを作成する
・データポータルで混合データを作成する
2.今回の学び
・クエリを打つ際にエラーになるのでデータセット、テーブル名を数字や日本語を使用しない。
・被りが発生しないように何を使用するのかを先に理解しておく。
・ピボットテーブルのように考えていたが、縦計算しかできないのでそれも踏まえてデータ整理を行う。
・データポータルでGoogleマップを使いたかったが、GAやGoogle広告で連携したほうが楽。
表でデータを追加すればできるが面倒なので最初からデータに加えておくとよい。
・データをクエリやデータポータルでどうにかしようとしてはいけない。データの前処理が勝負。
3.今回追加でやりたかったこと
・地域別や地形別で交通事故が発生する割合は変わるのか
・住んでいる人の性格などで交通事故が発生する割合は変化するのか
・年齢別での事故発生率の比較(若年層が多い、高齢者が多いための事故なのか？？)
4.次回でやろうと思っていること
・もっと大きいデータを使用してみる(データ量が最低1000以上など)
・効果的な可視化を本やサイト等で勉強し、アウトプット