車両混雑データの構造化と簡易分析

# バスの混雑データを構造化し分析してみる
宇野バスがオープンデータとして公開されている[車両密データ](http://www3.unobus.co.jp/opendata/)を、蓄積からデータベースで分析するまでの道のりをここに記載します。

# 1. データ蓄積
今回分析対象１５秒おきに更新される仕様で、過去データの配布されていないようなので、一定間隔でダウンロードします.

```shell:15秒間隔で対象のURLにアクセスしファイル名をタイムスタンプで保存する
while true;do sleep 15s; curl アクセス先のURL -o ~/busdata/`date "+%Y%m%d_%H%M%S"`.txt ;done
```
 - 繰り返し実行: `while true;do 繰り返し実行したいコマンド; done` 
 - 15秒間隔: `sleep 15s;`
 - 特定のURLにアクセスしてダウンロード: `curl アクセス先のURL -o ファイル名`
 - 実行時の時刻をファイル名の一部に: `date "+%Y%m%d_%H%M%S"'.txt  `

上記はAmazon LightsailのLAMP環境で実行しています。


 
# 2. データの整形
車両密データは、先頭にだけタイムスタンプがあり、各バスの行にはタイムスタンプが付与されていません。
データベースで分析するに、ファイルの先頭行のタイムスタンプを各行に付与します。
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/53918/1d56b5b7-ee67-24c0-2bf3-88a29c95c0b9.png width=400px />
## 2.1. ダウンロードしたファイルを１つのファイルに結合

ファイルが多いとその後の処理で色々と面倒なので、1つのファイルに結合してしまいます。
ファイル数が少ない場合は、`cat *.txt >out.txt` と書けばディレクトリ内のすべてのtxtファイルをout.txtに出力することができますが、ファイル数が増えるとエラーがでてしまいます。

```shell:大量のファイルを結合する
find . -maxdepth 1 -name '*.txt'|xargs cat > ../out_uno_mitsu.txt
```

## 2.2. データベースに投入

未整形のデータを投入するテーブルにデータを一気に投入し、投入後に整形します。

```sql:
-- 未整形データ投入用テーブルの作成
CREATE TABLE uno_base(line text);

-- データ投入
COPY uno_base FROM 'out_uno_mitsu.txt';

-- データの整形
-- DROP TABLE IF EXISTS uno_occupancy ;
CREATE TABLE uno_occupancy AS -- 以下のSQL実行結果を uno_occupancyテーブルとして保存

WITH 
 -- テキストを配列に変換
 s1 AS (SELECT 
           row_number()OVER() AS id ,
           string_to_array(replace(line ,e'\ufeff','' ), ',') AS data , -- BOMの除去
           array_length(string_to_array(line , ','),1)   --先頭行の特定に配列長を計算
        FROM uno_base )
 -- タイムスタンプ行から次のタイムスタンプ行まで同一のIDを付与する　【このクエリの最大のポイント!】
 ,s2 AS (SELECT *, COUNT(CASE WHEN array_length = 1 THEN 1 END )OVER(ORDER BY id) FROM s1 )
 -- データ整形と各行にタイムスタンプを付与
 SELECT
           CASE WHEN s2.data[1] != '' THEN s2.data[1]::int      END AS car_number ,
           CASE WHEN s2.data[2] != '' THEN s2.data[2]::smallint END AS lever_position ,
           CASE WHEN s2.data[3] != '' THEN s2.data[3]::text     END AS occupancy_state_jp , 
           CASE WHEN s2.data[4] != '' THEN s2.data[4]::text     END AS text_color ,
           CASE WHEN s2.data[5] != '' THEN s2.data[5]::smallint END AS occupancy_status, ts 
        FROM s2 ,
         (SELECT
           to_timestamp(data[1],'yyyy/mm/dd hh24:mi:ss')::timestamp without time zone AS ts ,
           count 
          FROM s2 WHERE array_length = 1 ) AS t2
        WHERE s2.count = t2.count AND s2.array_length > 1 AND s2.data[1] != ''
;
```

### 実行結果

カンマ区切りで記載されていたデータ行をそれっぽい型にキャストし、行末にタイムスタンプを付与しました.

![スクリーンショット 2020-08-14 23.13.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/53918/fe7d27b7-47a5-e883-b5ee-a98269165fd6.png)



# 簡易分析

混雑状況の分布を観察してみます。

```sql:混雑状況別の統計.sql

SELECT 
  occupancy_status -- 密状態レベル（１〜４）
  , MIN(lever_position)  AS lever_position_min -- 密状態のうちのレバー位置最小値
  , MAX(lever_position)   AS lever_position_max -- 密状態のうちのレバー位置最大値
  , array_agg(DISTINCT occupancy_state_jp) AS state_list -- 密状態をリストアップ
  , COUNT(DISTINCT car_number) AS bus_count -- 該当バス数
  , COUNT(car_number) AS data_count -- 記録データ数
  , array_agg(DISTINCT date_part('hour',ts)) AS hour_list -- 発生時間帯をリストアップ
FROM uno_occupancy
WHERE ts::date = '2020-08-11'
GROUP BY 1
ORDER BY 1 ;
```
実行結果
![図1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/53918/bccb706c-f2a6-6811-92d3-947e219ebc6f.png)

## 2020年８月１１日の密状況の観察

### データ仕様
車両の混雑レベルは１〜４であり、対応するレバー位置は６４毎に階級が割り振られているようです。

### 混雑発生状況について： 密は発生している
混雑レベル４を記録した車両は４台、時間帯は7時台、17時台、18時台の３つの時間帯でした。
レベル３を見ると、発生時間帯は午前７時〜１０時、午後は１８時〜２０時。通勤通学の時間帯が車内が混雑する傾向があるようです。

## 時間帯別混雑度の分布

```sql
SELECT
  date_trunc('hour' , ts ) 
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 0 THEN car_number END  ) AS l1
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 1 THEN car_number END  ) AS l2
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 2 THEN car_number END  ) AS l3
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 3 THEN car_number END  ) AS l4
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 4 THEN car_number END  ) AS l5
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 5 THEN car_number END  ) AS l6
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 6 THEN car_number END  ) AS l7
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 7 THEN car_number END  ) AS l8
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 8 THEN car_number END  ) AS l9
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 9 THEN car_number END  ) AS l10
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 10 THEN car_number END  ) AS l11
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 11 THEN car_number END  ) AS l12
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 12 THEN car_number END  ) AS l13
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 13 THEN car_number END  ) AS l14
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 14 THEN car_number END  ) AS l15
 , COUNT(DISTINCT CASE WHEN lever_position/16 = 15 THEN car_number END  ) AS l16
 FROM uno_occupancy
 WHERE 
 ts::date = '2020-08-11'
 AND 
 text_color != ''
GROUP BY 1
ORDER BY 1;

```
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/53918/5168a72f-7953-c03f-e9ef-1d7335326764.png" width=500px />

レベル４の最小値は255と、立が多くなるとレバー位置がすぐに振り切っているようです。
立つ客が多くなると見通しが悪くなるので仕方がない事かもしれません。