pandas で SQL を使う

直感 #

SQL に慣れたメンバーに「loc と groupby で書いて」と頼むより、同じデータを SQLite に載せて SELECT ... WHERE ... GROUP BY を書いてもらうほうが早いことがあります。SQLite は Python に標準で付いてくるファイル 1 つ（またはメモリ上）のデータベースで、サーバーの用意がいりません。SQL の既存知識を活かしたい場合、pandas で整形した DataFrame を SQLite に載せ替えて SELECT や JOIN を実行するのが手っ取り早いです。ここでは in-memory DB を使った最小構成を紹介します。

次のコードは整形済み CSV を読み込み、列名から「年度」を取って SQL で書きやすい短い名前にします。

1
2
3
4
5

import pandas as pd
from sqlite3 import connect

df = pd.read_csv("../data/processed/soumu-gdp-clean.csv")
df.columns = df.columns.str.replace("年度", "")

1. DataFrame → SQLite #

次のコードは、メモリ上に SQLite を作り、df を gdp という名前のテーブルとして登録します。

1
2

conn = connect(":memory:")  # メモリ上の SQLite
df.to_sql("gdp", conn, index=False, if_exists="replace")

2. SQL を実行 (read_sql) #

次のコードは、令和 1 年と平成 1 年の差が 5 を超える費目を SQL で絞り込み、差分の大きい順に DataFrame として受け取ります。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

query = """
SELECT
  カテゴリ,
  R1 - H1 AS diff_recent,
  AVG(H1) AS avg_h1
FROM gdp
WHERE (R1 - H1) > 5
ORDER BY diff_recent DESC
"""

result = pd.read_sql(query, conn)
result.head()

read_sql に文字列を渡すだけで DataFrame が返ってくるため、SQL と pandas を行き来しながら探索できます。

3. pandas + SQL ハイブリッド #

サブクエリで Year 列を UNPIVOT #

次のコードは、年度列の名前を集めて、SUM(H1) AS H1 + SUM(H2) AS H2 ... のような SELECT 句の断片を文字列として組み立てます。

1
2

years = [col for col in df.columns if col.startswith(("H", "R"))]
year_select = " + ".join([f"SUM({y}) AS {y}" for y in years])

SQLite は標準の UNPIVOT を持たないため、以下のように pandas の melt を使った結果を SQL に登録すると便利です。次のコードは long 形式の表を gdp_tidy として登録し、年度ごとの平均を SQL で求めます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

tidy = df.melt(id_vars="カテゴリ", var_name="year", value_name="value")
tidy.to_sql("gdp_tidy", conn, index=False, if_exists="replace")

pd.read_sql(
    """
    SELECT year, AVG(value) AS avg_value
    FROM gdp_tidy
    GROUP BY year
    ORDER BY year
    """,
    conn
)

4. 逆方向：SQL → pandas #

データベースにあるテーブルを pandas に持ってくるときは read_sql_table / read_sql_query を使います。次のコードは SQLAlchemy でファイル DB に接続し、fact_sales テーブルをまるごと DataFrame に読み込みます。

1
2
3
4

import sqlalchemy as sa

engine = sa.create_engine("sqlite:///../data/warehouse.db")
df_from_db = pd.read_sql_table("fact_sales", con=engine)

SQLAlchemy を用いると PostgreSQL や BigQuery など他エンジンにも同じ要領で接続できます。

運用 Tips #

一つ目は SQL を資産として残すことです。Notebook のセルに貼り付けるだけでなく、queries/ ディレクトリに .sql ファイルとして保存しておくと差分レビューがしやすく、open("queries/growth.sql").read() で read_sql に渡せます。二つ目はビューの代わりに pandas パイプラインを使うことで、df.pipe(...) でフィルタや集計を関数化しておき、SQL で取得した直後に適用します。三つ目は大規模データへの拡張で、測定目的なら SQLite で十分ですが、数 GB を超える実運用では DuckDB や Polars など列指向のエンジンに to_sql の先を切り替えることを検討します。

チェックリスト #

最初に確認したいのは、DataFrame を to_sql で DB 化し、read_sql で往復するテンプレを持ったかです。二つ目は列名・型を SQL 向けに正規化してから投入したか、三つ目は Notebook とは別に .sql ファイルを保管し、再利用できるようにしたかです。四つ目は pandas の melt / pivot と SQL の GROUP BY を組み合わせ、得意な言語からアプローチできるようにしたか、最後に将来のデータ量増加に備えて SQLite → DuckDB / DWH への移行経路を検討したかを見ます。

これで「既存の SQL クエリを pandas 上でも即座に再現する」ための土台が整いました。