第13回大都市交通センサスの調査データの解析に DuckDB を使ってみる
「政府統計の総合窓口（e-Stat）」で公開されている「第13回大都市交通センサス」の調査データを DuckDB の Python API を使って調査データを解析してみた。
調査データ
https://www.e-stat.go.jp/stat-search/files?page=1&toukei=00600020&tstat=000001103355
「第13回大都市交通センサス」の調査データとして公開されているのは、次のデータである。
定期券調査
定期券発売実績調査データ
一件明細調査
0次ODデータ
1次ODデータ
2次ODデータ
3次ODデータ
いずれも秘匿化処理が行われたものが CSV ファイルとして公開されている。
各調査データの詳細については割愛するが、これらのうち今回は「定期券発売実績調査データ」と「1次ODデータ」を見てみることにする。
DuckDB
DuckDB はオンライン分析処理（OLAP）向けに設計された軽量 RDBMS である。次のページがわかりやすい。
今回は Python API を使って次の環境で操作した。
定期券発売実績調査データ
2021年12月末時点で有効な定期券の発売実績を調査した結果である。調査データに付随している「第13回大都市交通センサス定期券発売実績調査報告書」には
令和3年12月に有効な調査区域内の定期券の発売枚数
と記載されているが、「第13回 大都市交通センサス 集計・分析業務報告書」の方には
12月末日時点で有効な定期券の発売枚数
と記載されている。年末年始期間は定期券を購入しない利用者もいると思われるが、影響がどのくらいあるかはわからない。
また、利用者数が極端に少ない駅は公開データからは除かれているため、発売枚数の合計は報告書に記載の件数とは一致しない。
その他にも注意事項が報告書に記載してあり、調査データを用いて分析する際には留意する必要がある。
調査データの読み込み
e-Stat から調査データをダウンロードし、下記のコードで DuckDB へインポートする。
from pathlib import Path
import duckdb
DB_PATH = Path(r"transport-census.db")
COMMUTER_PASS_DATA_PATH = Path(r"第13回大都市交通センサス定期券発売実績調査.csv")
COMMUTER_PASS_RESULTS_SQL = """\
DROP TABLE IF EXISTS commuter_pass_results;
CREATE TEMPORARY TABLE tmp (
area VARCHAR, -- 圏域
start_prefecture1 VARCHAR, -- 区間開始駅都道府県
start_city1 VARCHAR, -- 区間開始駅市区町村
start_company_name1 VARCHAR, -- 区間開始駅事業者名
start_station1 VARCHAR, -- 区間開始駅名
via1 VARCHAR, -- 経由駅
goal_prefecture1 VARCHAR, -- 区間終了駅都道府県
goal_city1 VARCHAR, -- 区間終了駅市区町村
goal_company_name1 VARCHAR, -- 区間終了駅事業者名
goal_station1 VARCHAR, -- 区間終了駅名
start_prefecture2 VARCHAR, -- 区間開始駅都道府県
start_city2 VARCHAR, -- 区間開始駅市区町村
start_company_name2 VARCHAR, -- 区間開始駅事業者名
start_station2 VARCHAR, -- 区間開始駅名
via2 VARCHAR, -- 経由駅
goal_prefecture2 VARCHAR, -- 区間終了駅都道府県
goal_city2 VARCHAR, -- 区間終了駅市区町村
goal_company_name2 VARCHAR, -- 区間終了駅事業者名
goal_station2 VARCHAR, -- 区間終了駅名
num_commuters VARCHAR, -- 通勤枚数
num_schools VARCHAR, -- 通学枚数
total VARCHAR, -- 合計枚数
data_type VARCHAR, -- データ種別
);
COPY tmp FROM '{}';
CREATE TABLE commuter_pass_results AS
SELECT
area,
start_prefecture1,
start_city1,
start_company_name1,
start_station1,
via1,
goal_prefecture1,
goal_city1,
goal_company_name1,
goal_station1,
start_prefecture2,
start_city2,
start_company_name2,
start_station2,
via2,
goal_prefecture2,
goal_city2,
goal_company_name2,
goal_station2,
CAST(replace(num_commuters, ',', '') AS INTEGER) AS num_commuters,
CAST(replace(num_schools, ',', '') AS INTEGER) AS num_schools,
CAST(replace(total, ',', '') AS INTEGER) AS total,
data_type,
FROM
tmp
;
""".format(COMMUTER_PASS_DATA_PATH)
with duckdb.connect(DB_PATH) as db:
db.execute(COMMUTER_PASS_RESULTS_SQL)「通勤枚数」「通学枚数」「合計枚数」は3桁ごとにカンマが入れられており、そのままだと INTEGER 型としてインポートができなかった。そのため、一時テーブルを作成し VARCHAR 型で読み込んだ後、改めて INTEGER 型へ変換している。
拡大推計
注意事項にも記載がある通り、回収率の異なるデータが混在している。下記のコードで事業者・データ種別ごとに枚数の合計を求めて推定した回収率のテーブルを作成し、これを回収率の差異の補正に利用する。
from itertools import groupby
from operator import attrgetter
from typing import NamedTuple
class CommuterPassResultsTotalRecord(NamedTuple):
"""事業者・データ種別ごとの合計枚数
"""
area: str
company_name: str
data_type: str
num_commuters: int
num_schools: int
COMMUTER_PASS_RESULTS_TOTAL_SQL = """\
SELECT
area,
start_company_name1,
data_type,
sum(num_commuters) AS num_commuters,
sum(num_schools) AS num_schools,
FROM
commuter_pass_results
GROUP BY
area, start_company_name1, data_type
ORDER BY
area, start_company_name1, data_type
;
"""
COLLECTION_RATES_DDL = """\
DROP TABLE IF EXISTS collection_rates;
CREATE TABLE collection_rates (
area VARCHAR NOT NULL, -- 圏域
company_name VARCHAR NOT NULL, -- 事業者名
data_type VARCHAR NOT NULL, -- データ種別
num_commuters INTEGER NOT NULL, -- 発売件数（通勤）
num_schools INTEGER NOT NULL, -- 発売件数（通学）
collection_rate_commuters FLOAT NOT NULL, -- データ種別ごとの回収率（通勤）
collection_rates_schools FLOAT NOT NULL, -- データ種別ごとの回収率（通学）
);
"""
with duckdb.connect(DB_PATH) as db:
with db.cursor() as cur1:
cur1.begin()
cur1.execute(COLLECTION_RATES_DDL)
with db.cursor() as cur2:
cur2.execute(COMMUTER_PASS_RESULTS_TOTAL_SQL)
for key, records_iter in groupby(
map(CommuterPassResultsTotalRecord._make, cur2.fetchall()),
attrgetter("area", "company_name")):
area, company_name = key
# データ種別ごとの通勤通学それぞれの枚数
counts = {}
for record in records_iter:
counts[record.data_type] = {
"commuters": record.num_commuters,
"schools": record.num_schools,
}
# データ種別ごとの通勤通学それぞれの回収率
rates = {"A": {}, "b1": {}, "c1": {}}
for attr in ["commuters", "schools"]:
if "A" in counts:
rates["A"][attr] = 1.0
if "b1" in counts and "b2" in counts:
b1 = counts["b1"][attr]
b2 = counts["b2"][attr]
rates["b1"][attr] = b1 / (b1 + b2)
if "c1" in counts and "c2" in counts:
c1 = counts["c1"][attr]
c2 = counts["c2"][attr]
rates["c1"][attr] = c1 / (c1 + c2)
# テーブルにレコードを追加
for data_type, values in rates.items():
if not values:
continue
num_commuters = counts.get(data_type, {}).get("commuters", 0)
num_schools = counts.get(data_type, {}).get("schools", 0)
collection_rate_commuters = values["commuters"]
collection_rates_schools = values["schools"]
cur1.execute(
"INSERT INTO collection_rates VALUES (?, ?, ?, ?, ?, ?, ?)",
[
area,
company_name,
data_type,
num_commuters,
num_schools,
collection_rate_commuters,
collection_rates_schools,
],
)
cur1.commit()調査データからは提出元の事業者がわからないため、1区間目の「区間開始駅事業者名」を発売事業者と仮定して集計している。共同使用駅等で他社発の定期券を発売した場合など正しくない場合もあるが、おおむね問題ないと考える。
作成したテーブルは次のようになっている。JR東日本（東日本旅客鉄道）の回収率が3割程度と低いのが目立つ。
調査データの確認
京急線金沢文庫駅から新宿までの駅までの定期券発売実績を次の SQL で確認してみる。
sql = f"""\
SELECT
t1.start_company_name1,
t1.start_station1,
t1.goal_company_name1,
t1.goal_station1,
t1.via1,
t1.num_commuters,
t1.num_schools,
t1.total,
t1.data_type,
CAST(t1.num_commuters * (1 / t2.collection_rate_commuters) AS INTEGER) AS num_commuters_estimated,
CAST(t1.num_schools * (1 / t2.collection_rates_schools) AS INTEGER) AS num_schools_estimated,
num_commuters_estimated + num_schools_estimated AS total_estimated,
FROM
commuter_pass_results AS t1
INNER JOIN collection_rates AS t2
ON t1.area = t2.area AND t1.start_company_name1 = t2.company_name AND t1.data_type = t2.data_type
WHERE
(
(t1.start_station1 = '金沢文庫' AND t1.goal_station1 = '新宿')
OR (t1.start_station1 = '新宿' AND t1.goal_station1 = '金沢文庫')
)
ORDER BY
total_estimated DESC
;
"""
with duckdb.connect(DB_PATH, read_only=True) as db:
print(db.sql(sql))結果は次のようになる。
品川乗換の方が圧倒的に多いが、横浜乗換の利用者もそれなりに存在することがわかる。JR線の経由はわからないが、品川乗換の場合は山手線、横浜乗換の場合は湘南新宿ラインを利用するのが一般的と思われる。品川乗換東京経由や、大門乗換大江戸線経由の利用者もいて興味深い。
1次ODデータ
2021年12月のとある2日間におけるICカード乗車券の利用履歴を調査した結果である。調査データに付随している「第13回大都市交通センサス一件明細調査報告書」には
調査日は、2021 年 12 月の平日であり、日付は非公開としている。
と記載されており、ある仮想的な1日のものとして作成した統計値であるとされる。
定期券発売実績調査データと同様に利用者数が極端に少ない駅は公開データからは除かれている。その他にも注意事項が報告書に記載してあり、調査データを用いて分析する際には留意する必要がある。
調査データの読み込み
e-Stat から調査データ（2日分、計12ファイル）をダウンロードして「1次OD表」ディレクトリへ保存し、下記のコードで DuckDB へインポートする。
IC_CARD_OD_1ST_DATA_PATH = Path(r"1次OD表")
IC_CARD_RESULTS_TABLE_DDL = """\
DROP TABLE IF EXISTS {table_name};
CREATE TABLE {table_name} (
area VARCHAR NOT NULL, -- 圏域
ic_card_type VARCHAR NOT NULL, -- カード種別
area_ingress VARCHAR NOT NULL, -- 【入場】圏域
company_ingress VARCHAR NOT NULL, -- 【入場】事業者名
line_ingress VARCHAR NOT NULL, -- 【入場】路線名
station_ingress VARCHAR NOT NULL, -- 【入場】駅名
prefecture_ingress VARCHAR NOT NULL, -- 【入場】都道府県
city_ingress VARCHAR NOT NULL, -- 【入場】市町村区
hour_ingress INTEGER NOT NULL, -- 【入場】時間帯
area_egress VARCHAR NOT NULL, -- 【出場】圏域
company_egress VARCHAR NOT NULL, -- 【出場】事業者名
line_egress VARCHAR NOT NULL, -- 【出場】路線名
station_egress VARCHAR NOT NULL, -- 【出場】駅名
prefecture_egress VARCHAR NOT NULL, -- 【出場】都道府県
city_egress VARCHAR NOT NULL, -- 【出場】市町村区
required_time INTEGER NOT NULL, -- 所要時間（５分単位）
count INTEGER NOT NULL, -- 人数
);
"""
IC_CARD_RESULTS_ALL_SQL = """\
DROP VIEW IF EXISTS ic_card_results_all;
CREATE VIEW ic_card_results_all AS
SELECT 1 AS days, * FROM ic_card_results1
UNION ALL
SELECT 2 AS days, * FROM ic_card_results2
;
"""
sqls = []
for table_name, target in [("ic_card_results1", "1ji_1_*.csv"), ("ic_card_results2", "1ji_2_*.csv")]:
sql = IC_CARD_RESULTS_TABLE_DDL.format(table_name=table_name)
for path in IC_CARD_OD_1ST_DATA_PATH.glob(target):
with path.open(encoding="utf-8") as f:
first_row = next(f)
if "【入場】事業者名" in first_row:
header = "true"
else:
header = "false"
sql += f"COPY {table_name} FROM '{path}' (HEADER {header});\n"
sqls.append(sql)
sqls.append(IC_CARD_RESULTS_ALL_SQL)
with duckdb.connect(DB_PATH) as db:
for sql in sqls:
db.execute(sql)1日目と2日目の最初のファイル、すなわち「1ji_1_1.csv」および「1ji_2_1.csv」にのみヘッダー行が存在し、それ以外のファイルは1行目からデータが始まっている。そのためヘッダー行の有無を考慮してデータを投入している。
また、1日目と2日目をそれぞれ別のテーブルへインポートし、両方をあわせた View も作成した。
調査データの確認
京急線金沢文庫駅から入場した利用者の出場駅を次の SQL で確認してみる。
sql = """\
SELECT
line_egress,
station_egress,
sum(count) AS total,
FROM
ic_card_results_all
WHERE
station_ingress = '金沢文庫'
GROUP BY
station_ingress, line_egress, station_egress
ORDER BY
total DESC
LIMIT 20
;
"""
with duckdb.connect(DB_PATH, read_only=True) as db:
print(db.sql(sql))結果は次のようになる。
利用者が最も多い出場駅は横浜で、上大岡が続き、品川は3番目である。隣駅の金沢八景と能見台までの利用者も多い。
1次ODデータはIC乗車券の入場と出場のペアを1レコードとしているため、基本的にノーラッチ（改札を通過しない）で行ける駅しか現れない。しかしながら、元データにはIC乗車券のタッチ不良等で窓口処理を行った記録も含まれており、改札を通過しないとたどり着けないJR線の品川駅や、入場と同じ金沢文庫駅で出場処理したデータも含まれている。
ワンラッチ・ツーラッチの乗換も含めたトリップを扱いたい場合は2次ODデータや3次ODデータを使用すると良い。
調査データの活用
これらの調査データを集計して、新京成線の旅客流動を可視化してみた。
なんとなく「新京成は短距離利用者が多い」印象があるので大都市交通センサスの調査データを集計してみたところ、初乗り区間の利用者は定期が約4割、定期外だと半数を占めていてイメージ通りだった。一方で京成線への乗継ぎが1割ほどあったり、津田沼〜松戸の乗り通しがそれなりにあったりと興味深い。 pic.twitter.com/EM4ObkSAz9
— てつろう（sokutou-metsu） (@tetsurou624) November 18, 2024
なお旅客流動を調べたのは、新京成線の運賃が京成線と通算された場合にどのくらいの利用者に影響があるのかが気になったからである。
新京成は2025年4月に京成へ合併されるが、運賃は通算されず現行のまま据置きとなる。「もし京成本線と通算したらどうなるのか？」が気になったので運賃表を作ってみた。新京成線内相互発着の場合だと、通学定期は全区間で値下げとなるものの、通勤定期と普通運賃は大半の区間で値上げとなってしまう。 pic.twitter.com/Wb9VYK3C5M
— てつろう（sokutou-metsu） (@tetsurou624) November 21, 2024
参考
大都市交通センサスの調査データを扱うに当たり、次の資料が参考になる。