パンダとアヒルの手を組ませて、オープンデータを楽に分析する
前置き
DuckDBというOLAPデータベースをご存じでしょうか。
去年くらいから、そこはかとなく流行り始めているようで、Python環境で簡単に動くのですが、処理が速いとか色々な形式に対応しているとか、様々な特徴があります。
と、DuckDBを昨日知ったばかりの私が、これ以上語っていくとボロがでそうなので、それは参考にさせていただいたページの諸先輩方にお任せします。
[参考]
- https://zenn.dev/notrogue/articles/1193d0ab8d8eda
- https://www.salesanalytics.co.jp/datascience/datascience150/
今回、実際に触ってみて、なんて便利なんだ！これを待っていた気がする！！と感じたことを、オープンデータを使った分析で実演していきたいと思います。
本題
DuckDBの紹介というよりも、DuckDBを用いたSQLでのデータ加工と、Pandasでのデータ加工を上手いこと組み合わせて、最強のデータ加工をやってみようというのが目的です。
（PandaとDuckを生成AIで合体させてみたの図）
国勢調査データの分析
東京都には「千代田区 丸の内１丁目」の様な、”町目・小字等”で区切られるエリアが約4900あります。
そんなにあると、果たして赤ん坊が一番多いエリアはどこなのか？シニア層が一番多いエリアはどこなのか？など、気になって眠れなくなることもあると思います。
<s>そんな時に、ダウンロードしたCSVデータをExcelに読み込ませて、チマチマ調べてもいいのですが、ちょっと面倒くさい。</s>
これまでであれば、PandasのDataFrameにして、その中で必要な加工・集計を行っていたのですが、この処理SQLだったらもっと楽に書けるのにな～と思ったことも多々あります。
そこで今回は、これらの処理をPandasとSQL(DUCK DB)の（個人的な）いい所取りで進めていきたいと思います。
参考：東京都の統計
Setup
まずはライブラリとデータの準備をしましょう。
動作はColab上で動かすことを想定しています。
%%bash
# 必要なライブラリをインストール
pip install duckdb-engine
# 必要なデータをダウンロード
wget https://www.toukei.metro.tokyo.lg.jp/kokusei/2020/kd20zv01100000.zip
unzip kd20zv01100000.zip
Cleaning
とりあえず、ダウンロードしたCSV形式のデータを、Pandasで開いてみます（SQLで直接開いても同じなので、そこはお好みで）
import pandas as pd
df = pd.read_csv("kd20zv01100000.csv")
df.head(3)
一見、綺麗に東京の各市区町村、町・大字ごとの年齢ごとの人数が入っていそうだが所々イケてないものが混ざるので、データをクレンジングします。（こういうことって、もう少しオープンデータを打ち出す際に気を付けてほしいところ。。。）
ここで、複数の列に対して同じような処理をする場合は、Pandasが有利です。
例えば、項目のデータ型変換をSQLでやろうとすると、対象の項目を指定する必要があります。
CAST(年齢（５歳階級）／0～4歳（人）AS INT64) AS 年齢（５歳階級）／0～4歳（人）,
CAST(年齢（５歳階級）／5～9歳（人）AS INT64) AS 年齢（５歳階級）／5～9歳（人）,
CAST(年齢（５歳階級）／10～14歳（人）AS INT64) AS 年齢（５歳階級）／10～14歳（人）,
・・・
この用に、対象の項目探すのも、SQL書くのも（後から何かあったときに修正するのも）面倒
しかし、Pandasを用いると次のように汎用的なプログラムで加工することが可能です。
（ちょいちょいコメントに、オープンデータに対する文句が入っているのはスルーしてくださいｗ）
# １行目の項目名の最後に半角スペースが入っている場合に、上手く項目を指定できないため、除外する処理 -> こういうの、本当にデータを提供している人たちは使う人のことをわかっていないんだろうなと実感する
df.columns = df.columns.map(lambda x: x.replace(" ",""))
# 年齢（５歳階級）という名称で始まる項目を抽出
# わざわざ、年齢（５歳階級）／10～14歳（人）, 年齢（各歳）／うち10歳（人）, 年齢（各歳）／うち11歳（人）, 年齢（各歳）／うち12歳（人）, 年齢（各歳）／うち13歳（人）, 年齢（各歳）／うち14歳（人）な順番で入れるのは、
# 普通の人は見やすいんだろうか。。。私としては、1歳刻みはそれでまとめて、5歳階級はそれでまとめておいてくれた方が探しやすいのだが。。。
age_cols = df.columns[df.columns.map(lambda x: x.startswith("年齢（５歳階級）"))]
_age_cols = ",".join(age_cols) # SQLに渡すために、STRING化したものも用意
import numpy as np
# 値が入っていない場合と、「-」「X」の場合があるので、全てゼロに置換
# ちなみに、「X」は秘匿処理でマスクされたデータ（皇居とか、永田町とか）、「-」は皆無又は定義上該当数値がないものらしい。
# 他にも定義が色々とあるけど、今回の目的的には使えないデータなので、全てゼロ
# https://www.stat.go.jp/data/nihon/index2-1.html
for _c in age_cols:
df[_c] = df[_c].apply(lambda x: 0 if x in (np.NaN,'-','X') else x)
# 「-」と「X」を除いたことで、年齢項目を全て、数値型(INT64)に変換できる
df[age_cols] = df[age_cols].astype('int64')
Transform
WHEREで項目を絞ったり、CONCATで項目同士をくっつけたり、JOINしたりは（好みもありそうだけど）SQLが便利です。
さすがにもう慣れたけど、最初はPandasの行絞り込みもちょっと苦手でした
df[(df["オリジナル地域階層コード／町・大字相当"] != 0.0) & (df["地域階層フラグ値"] == 50.0) & (df["表側／男女の別"] == "総数") & (~df["人口総数（人）"].isin(['X', '-']))]
同じDFの中の処理に何回、df書かせるんだ！、カッコが多すぎて条件があとから読みづらい！って
- queryメソッド使え！には、知ってるよ！とだけ答えておきます
- pandasqlは、個人的に好きだったんだけど、開発が止まっているので、使いのはちょっと怖い。他に似たライブラリありそうだけど、探してなかった
それがSQLだと、WHERE句に好きなだけ並べられて、可読性も高くなります。（個人の見解です）
WHERE
オリジナル地域階層コード／町・大字相当 != 0.0
AND 地域階層フラグ値 = 50.0 -- 地域は町目・小字等のレベル
AND 表側／男女の別 = '総数' -- 今回は総数で出す
AND 人口総数（人） not in ('X', '-')
なので、この辺りの処理はSQLに任せたいんですが、いちいちデータフレームをSQLサーバー（例えば業務で使っているBigQueryとか）にアップロードするのは手間。
そこで登場するのが、DuckDB。なんと、次のようにデータフレームをFROM句において、そのままSQLが書けてしまいます。
import duckdb
# 別にtable変数にする必要はないんだけど、SQLにそのまま書くと、最早PandasDataFrameがどこいったか分からなくなりそうなので、変数に切り出してます。
table = "df"
# 普段BigQueryを使っている身では、SQLでの諸々の指定はやりやすい。さらに、日本語がそのまま使えるのも良い（BigQueryでも最近、項目名に日本語が使えるようになりましたが、`項目名` とくくらないといけないのが、少々面倒）
# もちろん、全ての処理・関数が使えるわけではないと思うので、JOINとか、日付処理とか、Window関数とかは別途検証予定
df2 = duckdb.query(
f"""
-- #ではコメントアウトできないので、-- を使う
SELECT
地域ＩＤ, オリジナル地域階層コード／町・大字相当, 地域階層フラグ値,
表側表章地域（階層別）／区市町村階層以上の地域名称, 表側表章地域（階層別）／町・大字相当階層の地域名称 , 表側表章地域（階層別）／丁目・小字等階層の地域名称, 表側表章地域（階層なし）／地域名称,
CONCAT(表側表章地域（階層別）／区市町村階層以上の地域名称, 表側表章地域（階層別）／丁目・小字等階層の地域名称) AS 地域名,
人口総数（人）, {_age_cols},
面積（平方キロメートル）,
FROM {table}
WHERE
オリジナル地域階層コード／町・大字相当 != 0.0
AND 地域階層フラグ値 = 50.0 -- 地域は町目・小字等のレベル
AND 表側／男女の別 = '総数' -- 今回は総数で出す
AND 人口総数（人） not in ('X', '-')
"""
).to_df()
何がすごいって、特に何も設定していないのに、FROMで指定するテーブルに、Pandas DataFrameをそのまま指定できるところ。これはかなり違和感なく使えてすごい。
Analytics
ここまで来たら、最後は集計。各年齢階級が多いエリアを、一気に出しましょう。
SQLでもサブクエリで頑張ればできそうですが、そこはPandasの得意分野。２行（実質１行）で、はい出来上がり
# 各年齢層が多い地域を出すために、地域名をIndexに。argmaxの処理（idxmax）はPandasで一発
df2 = df2.set_index("地域名")
df2[age_cols].idxmax()
年齢（５歳階級）／0～4歳（人） 江東区東雲１丁目
年齢（５歳階級）／5～9歳（人） 江東区東雲１丁目
年齢（５歳階級）／10～14歳（人） 江東区東雲１丁目
年齢（５歳階級）／15～19歳（人） 小平市小川町１丁目
年齢（５歳階級）／20～24歳（人） 小平市小川町１丁目
年齢（５歳階級）／25～29歳（人） 小平市小川町１丁目
年齢（５歳階級）／30～34歳（人） 港区芝浦４丁目
年齢（５歳階級）／35～39歳（人） 江東区東雲１丁目
年齢（５歳階級）／40～44歳（人） 江東区東雲１丁目
年齢（５歳階級）／45～49歳（人） 江東区東雲１丁目
年齢（５歳階級）／50～54歳（人） 江東区東雲１丁目
年齢（５歳階級）／55～59歳（人） 小平市小川町１丁目
年齢（５歳階級）／60～64歳（人） 小平市小川町１丁目
年齢（５歳階級）／65～69歳（人） 板橋区高島平２丁目
年齢（５歳階級）／70～74歳（人） 板橋区高島平２丁目
年齢（５歳階級）／75～79歳（人） 板橋区高島平２丁目
年齢（５歳階級）／80～84歳（人） 板橋区高島平２丁目
年齢（５歳階級）／85～89歳（人） 板橋区高島平２丁目
年齢（５歳階級）／90～94歳（人） 杉並区高井戸西１丁目
年齢（５歳階級）／95～99歳（人） 青梅市大門１丁目
年齢（５歳階級）／100歳以上（人） 青梅市大門１丁目
年齢（５歳階級）／年齢不詳（人） 新宿区西新宿４丁目
ふむふむ、江東区の東雲には30代後半から50代前半の夫婦と、中学生までの子どもがいるファミリーが多いんだな。タワマン族かな。
20代が一番多いのが、小平市なのは意外！アクセスとか、生活費の関係で過ごしやすいのかな？
65歳以上になると、板橋区の高島平ね。マンモス団地で有名だもんね。
などなど、これでスッキリして眠れますね。
ちなみに、答え合わせのために、ExcelでCSVファイルを開いたら、項目が多いわ、「X」とか「-」のせいで、人数項目が全て文字列になるから、一番多い人数が中々探せないわで、手間取りました。
そういうところやぞ、オープンデータ。。。
Discussion