🔖 キーワード索引

🎨 直感で掴む

「同じ社員番号で勤怠表と人事表を横並びにする」のがイメージです。社員番号 (= キー) を頼りに、各社員の行に追加情報をくっつけます。

4 種類の結合をベン図で覚える：

カーディナリティ (関係の多重度)：

• 1:1 — 都道府県 ↔ 県庁所在地。安全。
• 1:N — 都道府県 ↔ 市町村。N 倍に行が増える。意図通りか確認。
• N:M — 商品 ↔ タグ。デカルト積で爆発。中間テーブルが必要。

SSDSE-B-2026 のキー (Prefecture, Year) は 47 × 12（2012〜2023 年）= 564 行で完全に 1:1。ここに SSDSE-C 等の同粒度データを結合するなら安心です。

🎨 データモデリングと JOIN: スタースキーマ / スノーフレーク

スタースキーマ（DWH の王道）

中央に ファクト表（売上、 イベント、 ログ）、 周囲に ディメンション表（顧客、 商品、 時刻、 地域）を配置。 ファクト表のキーを各ディメンションと JOIN するだけで分析できる。 SSDSE-B-2026 で言えば「年×県の人口データ」がファクト、 「県→地方」「年→四半期」がディメンション。

       [dim_pref]
            │
[dim_year]──[fact_population]──[dim_indicator]
            │
       [dim_region]

スノーフレークスキーマ

ディメンションをさらに正規化（地方 → 国 → 大陸など）した拡張版。 ストレージ効率は良いが JOIN 段数が増える。 トレードオフで OLAP では非正規化（スター）が一般的。

ディメンション設計のコツ

• 代理キー (surrogate key): 自動採番の整数 ID。 文字列キーより JOIN が高速
• SCD type 2: 履歴を保持する変更管理（県の合併・名称変更に対応）
• 不変ディメンション: 地理・分類など滅多に変わらないものは小さく保つ
• クラスタリングキー: BigQuery/Snowflake で JOIN キーをクラスタ化 → I/O 激減

🎮 触って理解する — 結合タイプを切り替えてみよう

下の左表（注文表）と右表（商品マスタ）は、キー 商品ID が一部しか一致しない架空のミニデータです（一致: P2, P3 / 左のみ: P1, P5 / 右のみ: P4）。ボタンで結合タイプを切り替えると、どの行が生き残り、どの行が捨てられ、どこに NULL が生まれるかが図・結果表・ベン図にリアルタイムで反映されます。行にマウスを乗せる／指でなぞる（タッチ対応）と、結果表の対応行がハイライトされます。

両表で一致 　 左表のみ 　 右表のみ 　 この結合では捨てられる行 　NULL 補完される欠損

💡 やってみよう: ①「1対多モード」を ON にすると右表に P2 が 2 行（旧価格・新価格）になります。INNER でも左表の P2 1 行が結果で 2 行に増殖することを確認。②LEFT と RIGHT で NULL が生まれる側が入れ替わること、③FULL OUTER では行が 1 つも捨てられないことを確認してください。

🧭 直感 — 「キーで横に繋ぐ」を一段深く

結合とは「左表の各行について、右表からキーが一致する行を辞書引きして横に貼り付ける」操作です。SQL の JOIN も pandas の merge() も R の dplyr::left_join() も内部でやっていることは同じ。4 種類の結合タイプの違いは、突き詰めれば「相手が見つからなかった行をどうするか」の一点だけです — INNER は捨てる、LEFT/RIGHT は片側を守って NULL で埋める、FULL OUTER は全部守る。だからこそ結合の設計は主キー・外部キーの設計（キーの一意性・参照整合性）とセットで考えます。

⚠️ 落とし穴 — 上のデモで起きたことは実務でも起きる

• 1対多で行数爆発: 上の 1対多モードの通り、右表にキー重複があると行が増殖します。SSDSE 系では「都道府県×年」の表に「都道府県のみ」のマスタを繋ぐのは意図的な 1:N ですが、逆に一意のはずのキーが重複していると集計値が水増しされます。pandas なら pd.merge(..., validate="1:1") や validate="m:1" で結合前に多重度を強制チェックできます。
• キーの型不一致: 都道府県コードが左表で int の 13、右表で str の "13" だと一致率 0% になり、INNER では結果 0 行・LEFT では全行 NULL になります。結合前に df.dtypes と astype(str) での統一を習慣に。ゼロ埋め（"01" vs "1"）や全角半角・前後スペースも同類で、データクレンジングの守備範囲です。
• NULL の扱い: SQL では NULL = NULL は真にならず（3値論理）、NULL キー同士は結合されません。一方 pandas の merge は NaN キー同士を一致させることがあり、ライブラリ間で挙動が違います。結合キーに欠損値が混ざっていないか、df[key].isna().sum() を結合前に確認しましょう。また LEFT JOIN で生まれた NULL を「元からあった欠損」と混同しないよう、indicator=True で由来列を残すのが安全です。

🚀 発展 — アルゴリズム・pandas merge・アンチ結合

• 結合アルゴリズム: 実行エンジンは hash join（片側をハッシュ表化、平均 $O(N+M)$）・sort-merge join（両側ソート後に併走）・nested-loop join（総当たり、極小テーブル向き）を使い分けます。分散処理では小さい側を全ノードに配る broadcast join が定石です。
• pandas merge の要点: pd.merge(left, right, on="キー", how="left", validate="1:1", indicator=True) が安全形。on の代わりに left_on / right_on で列名違いに対応、suffixes=("_b", "_c") で同名列の衝突を制御。時系列の「直近を繋ぐ」には pd.merge_asof。詳細は pandas と SQL のページへ。
• アンチ結合 / セミ結合: 「右表に存在しない左行だけ欲しい」（照合漏れの検出）がアンチ結合。SQL では NOT EXISTS、pandas では merge(..., how="left", indicator=True) の後に query('_merge == "left_only"') で実現します。逆に「存在する行だけ、列は増やさず」がセミ結合。結合後は集計やピボットテーブルに繋ぐのが定番フローです。

📐 数式・定義 — 関係代数

自然結合 (natural join):

$$ R \bowtie S = \{ (r, s) \mid r \in R, s \in S, \pi_K(r) = \pi_K(s) \} $$

$\pi_K$ は共通キー $K$ への射影。両表でキーが一致する組のみを残す (INNER JOIN)。

左外部結合：

$$ R \mathbin{\unicode{x27D5}} S = R \bowtie S \cup \{ (r, \mathrm{NULL}) \mid r \in R, \not\exists s \in S: \pi_K(r) = \pi_K(s) \} $$

結合後の行数 (一意キー時):

$$ |R \bowtie S| = \sum_{k \in K_R \cap K_S} 1 = |K_R \cap K_S| $$

キーが重複しているときは積になります。$|R| \cdot |S| / |K|$ の上限。

結合計算量：

📐 JOIN 条件の種類: 等価 / 非等価 / 範囲

時系列でよく使う merge_asof

pd.merge_asof(trades, quotes, on='time') は「各 trade に対して、 それ 以前で最も近い quote を割り当てる」操作。 金融データ・センサーデータの時刻整列で必須。 SQL の LATERAL JOIN + ORDER BY 相当。

📐 結合の計算量と最適化 — Hash / Sort-Merge / Nested-Loop

RDBMS や DataFrame ライブラリは内部で 3 種類の結合アルゴリズム を使い分ける。 計算量と適用条件を覚えれば、 「なぜこのクエリだけ遅いのか」が即座に説明できる。

⏱ 47 県 × 3 行マスタの計算量比較（簡易ベンチ）

🎯 このコードでやること: SSDSE-B-2026 から 47 行を取り出し、 3 行のマスタと結合する処理を pandas (Hash) と SQLite (デフォルトは Nested-Loop) で比較する。 件数が小さいので絶対秒数は微小だが、 アルゴリズム差を可視化できる。

📥 入力データ: SSDSE-B-2026 (47 行) + 地方マスタ (3 行)。

1
2
3
4
5
6
7
8
9

import pandas as pd, sqlite3, time
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df[df['SSDSE-B-2026']==2023][['Prefecture','A1101']]
region = pd.DataFrame({'Prefecture':['北海道','東京都','大阪府'], 'region':['HK','KT','KS']})
t0=time.perf_counter(); m1 = df.merge(region, on='Prefecture', how='left'); t1=time.perf_counter()
con=sqlite3.connect(':memory:'); df.to_sql('p',con,index=False); region.to_sql('r',con,index=False)
t2=time.perf_counter(); m2 = pd.read_sql("SELECT * FROM p LEFT JOIN r ON p.Prefecture=r.Prefecture", con); t3=time.perf_counter()
print(f'pandas (Hash)  : {(t1-t0)*1000:.3f} ms, 行={len(m1)}')
print(f'sqlite (NL)    : {(t3-t2)*1000:.3f} ms, 行={len(m2)}')

📤 実行例:

🕐 この 2 つの数値は実行のたびに変わります。 マシン・OS・同時に動いているプロセスに左右されるうえ、 47 行 × 3 行という規模では計測値のほとんどが「関数呼び出しの固定コスト」です。 実際、 実行を繰り返すと pandas と sqlite の速い・遅いが入れ替わります。 ミリ秒の大小をこの規模で比べても意味がありません。 アルゴリズムの差を見たいなら、 timeit で数百回の平均を取り、 かつ行数を 10 万件規模まで増やしてください。

💬 結果の読み方: 見るべきは時間ではなく計算量の違いです。 pandas は Hash 主体で右側 3 行をハッシュ化して 47 回 lookup（O(n+m)）、 sqlite はインデックス無しのため Nested-Loop で 47×3 = 141 回比較（O(n×m)）。 この差は今の規模では埋もれますが、 件数が 10 万 × 1 万になれば 100 倍以上の差として表面化します。 本番では「右をハッシュ化できる側に置く」「結合キーにインデックスを張る」の 2 点を守るだけで桁違いに速くなる。

✅ 理解度チェック — 8 問で結合を完全マスター

読了後の知識定着を確認する 8 問。 すべて SSDSE-B-2026 を題材にしている。 各問の下に 解答と解説 をたたみ込んでいるので、 まずは隠して挑戦してほしい。

🏗 実務シナリオ徹底解説 — 「人口 × 家計 × 教育」3 表を結合して 1 つの分析テーブルを作る

SSDSE-B-2026 を 1 ファイルとして扱うだけなら結合は不要だが、 実務では 「公的統計の人口表」「総務省・家計調査の消費支出表」「文科省の学校統計表」 といった 複数出所のテーブル を都道府県コードで結合して 1 つの分析テーブルを作る。 ここでは SSDSE-B-2026 の中の 3 種類の列群を「別ファイル」と見立てて、 結合の実務シナリオを段階的に再現する。

ステップ 0: なぜ 1 表にまとめるのか — 「分析しやすい長方形」を目指す

分析では 1 行 = 1 観測単位 の 長方形（tidy）データ が前提になる。 都道府県分析なら 47 行、 全国時系列なら 「年度 × 県」 47×N 行の長方形。 複数出所の表を結合して 1 表にまとめると、 散布図・回帰・クラスタリングがそのまま動くようになる。 逆に「結合せずに 3 個の DataFrame をくっつけたつもり」で進めると、 順序が違っていたり、 県の対応が狂っていたりして、 分析が崩壊する。

ステップ 1: 3 表をそれぞれロードして「キーと粒度」を宣言する

🎯 このコードでやること: SSDSE-B-2026 から人口・家計・教育の 3 グループを切り出し、 それぞれ pop_df, spend_df, edu_df として独立した DataFrame にする。 結合キーは 「Prefecture」 「SSDSE-B-2026（年度）」 の複合キー。

📥 入力データ: data/raw/SSDSE-B-2026.csv（cp932, ヘッダ 2 行）。 実在列例: Prefecture, SSDSE-B-2026（＝年度）, A1101（総人口）, A1303（65歳以上人口）, L3221（消費支出）, E1101（幼稚園数）など。

1
2
3
4
5
6
7

import pandas as pd
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
pop_df   = df[['Prefecture','SSDSE-B-2026','A1101','A1303']].rename(columns={'A1101':'pop','A1303':'elderly'})
spend_df = df[['Prefecture','SSDSE-B-2026','L3221']].rename(columns={'L3221':'spend'})
edu_df   = df[['Prefecture','SSDSE-B-2026','E1101']].rename(columns={'E1101':'kg'})
for name, d in [('pop',pop_df),('spend',spend_df),('edu',edu_df)]:
    print(name, '行=', len(d), 'キー一意=', d[['Prefecture','SSDSE-B-2026']].duplicated().sum()==0)

📤 実行例:

💬 結果の読み方: 3 表とも 47 県 × 12 年（2012〜2023）= 564 行、 複合キーで一意。 「キー一意」が False ならその時点で結合せず、 重複の原因を先に潰す。 これが結合前の必須チェック。

ステップ 2: 3 表を順次結合 — INNER で「全表に揃った行」だけを残す

🎯 このコードでやること: pop_df を起点に、 spend_df, edu_df を順に INNER JOIN する。 INNER を使うことで、 1 表でも欠けがあればその（Prefecture × 年度）は捨てる。 安全側の運用。

1
2
3
4

m1 = pop_df.merge(spend_df, on=['Prefecture','SSDSE-B-2026'], how='inner')
m2 = m1.merge(edu_df, on=['Prefecture','SSDSE-B-2026'], how='inner')
print('段階別行数:', len(pop_df), '→', len(m1), '→', len(m2))
print(m2.head(3).to_string(index=False))

📤 実行例:

💬 結果の読み方: 3 表ともキー粒度が同じだから、 INNER でも行数は減らない。 もし spend_df だけ 2017〜2023 の 7 年分しかなければ、 結合後は 47×7=329 行に縮退する。 縮退が想定外なら、 LEFT に切り替えて欠損年度を可視化する。

ステップ 3: 派生指標を計算（高齢化率・幼稚園密度）

🎯 このコードでやること: 結合後の 1 表で「高齢化率（65歳以上人口 / 総人口 × 100）」「幼稚園密度（幼稚園数 / 人口100万人）」を計算。 結合が成功した瞬間にできる「複数表をまたぐ派生指標」こそが、 結合の最大の価値。

1
2
3
4
5

m2['高齢化率'] = m2['elderly'] / m2['pop'] * 100        # 65歳以上人口 ÷ 総人口 × 100
m2['幼稚園密度'] = m2['kg'] / m2['pop'] * 1_000_000       # 人口100万人あたり幼稚園数
y23 = m2[m2['SSDSE-B-2026']==2023]
print(y23.nlargest(3, '高齢化率')[['Prefecture','高齢化率','幼稚園密度']].round(2).to_string(index=False))
print(y23.nsmallest(3, '高齢化率')[['Prefecture','高齢化率','幼稚園密度']].round(2).to_string(index=False))

📤 実行例:

💬 結果の読み方: 秋田県の高齢化率は 39.06% と全国最高だが幼稚園密度（人口100万人あたり）は 35.01 と最も低い（若年人口が少ないので幼稚園も少ない）。 逆に高齢化率が低い東京都・沖縄県・愛知県は幼稚園密度が相対的に高く、 「高齢化率と幼稚園密度が逆向きに動く」県の構造が 結合表でしか見えない。 これが「複数表を結合する真の価値」。

ステップ 4: 結合表を CSV に保存し、 後段の分析に渡す

🎯 このコードでやること: 結合済みの分析テーブルを data/processed/joined.csv として保存。 BOM 付き UTF-8 にしておくと Excel でもそのまま開ける。 後段の散布図・回帰・クラスタリングはこの 1 ファイルだけを読めば良い。

1
2
3
4
5
6

import os
os.makedirs('data', exist_ok=True)   # 書き出し先を先に作る
import os
os.makedirs('data/processed', exist_ok=True)
m2.to_csv('data/processed/joined.csv', index=False, encoding='utf-8-sig')
print('保存しました:', os.path.getsize('data/processed/joined.csv'), 'バイト')

📤 実行例:

💬 結果の読み方: 約 43 KB の単一 CSV にまとまった。 後段の分析者は pd.read_csv('data/processed/joined.csv') 1 行だけで、 「人口 × 家計 × 教育」の長方形データを得られる。 これが 結合の最終形＝再利用可能な分析テーブル。 元の生データをいじることなく、 派生テーブルを下流に流すワークフローを徹底すると、 データパイプライン全体が監査可能になる。

🔬 数式を言葉で読み解く

🔬 数式を言葉で読み解く（関係代数からハッシュ結合まで）

JOIN は単なる「2 つの表を貼り合わせる操作」ではなく、 関係代数という数学的体系の中核演算です。 ここでは式 4 本を、 記号ごとに丁寧に言葉に直し、 SSDSE-B-2026（47 都道府県、 2023 年）で動かして確かめます。

式 ① — 自然結合（natural join）

言葉に直すと: 「2 つの表を全ペアで並べて、 キーが一致する行だけ残し、 重複列を 1 つに畳む」。 SSDSE では「47 件 × 47 件 = 2209 候補のうち、 都道府県名が一致する 47 行だけ」が結果になります。 一見遠回りに見えるが、 これが すべての JOIN の基本形。

式 ② — 外部結合（OUTER JOIN）

言葉に直すと: 「INNER JOIN の結果に、 左表で対応する右側がなかった行を NULL 埋めで追加する」。 SSDSE で「右表（地方区分）に鳥取・島根を入れ忘れた」場合、 LEFT JOIN なら 47 行（鳥取・島根は Region=NaN）、 INNER JOIN なら 45 行になります。

式 ③ — 結合の計算複雑度

言葉に直すと: 「素朴な二重ループは行数の積、 ハッシュ結合は線形、 ソートマージはソート分の対数」。 47 × 47 なら何でも瞬時だが、 1 万 × 1 万 = 1 億、 100 万 × 100 万 = 10¹² で ハッシュ結合一択になります。 pandas merge も SQL も内部でこの判断を自動で行っています。

式 ④ — 結合の数え方（1 対多と直積爆発）

言葉に直すと: 「結果の行数は、 同じキーを持つ左行数 × 右行数を全キーで足し上げたもの」。 双方に重複キーがあると 掛け算で爆発。 SSDSE で「3 年分の人口表 (47×3=141 行)」と「47 県の地方表」を JOIN すると 141 行（左の重複だけ伸びる）、 これが「1 対多 JOIN」の正体です。

🔬 JOIN アルゴリズムの内部実装

1. Nested Loop Join（最も素朴）

「左の各行に対して右を全行スキャン」。 計算量 $O(|R| \cdot |S|)$。 47 × 47 = 2209 なら一瞬だが、 1 万 × 1 万 = 1 億で数秒、 100 万 × 100 万 = 10¹² で実用不可。 ただしインデックスがあれば $O(|R| \log |S|)$ に落とせる（Index Nested Loop）。

2. Hash Join（小〜中規模で最強）

小さい方をハッシュテーブルに格納し、 大きい方を順に走査して引く。 計算量 $O(|R| + |S|)$。 pandas merge も sqlite も内部でこれを多用。 メモリにハッシュが乗らない場合は Grace Hash Join（分割してから結合）に切り替わる。

3. Sort-Merge Join（事前ソート済みなら有利）

両方をキーでソートし、 2 つのポインタで並列走査。 計算量 $O(|R| \log |R| + |S| \log |S|)$。 既に ORDER BY 済みの場合や、 範囲条件 JOIN（BETWEEN）で有利。

4. Broadcast Join（分散処理）

Spark/BigQuery で「片方が極端に小さい場合（〜10MB）」、 小さい側を全ワーカーに 放送して各ローカルで Hash Join。 ネットワークシャッフルを回避できる最強パターン。 pyspark.sql.functions.broadcast(df) で明示。

5. Shuffle Hash Join（分散の標準）

両側をキーでハッシュパーティションし、 同じパーティションを同じワーカーに送って Hash Join。 大規模 JOIN の標準だが、 ネットワークシャッフルが重い。

🔬 結合検証の 7 ステップ — 行数・キー・NaN・重複・型・順序・サマリ

本番データの結合は 「動いた = 正しい」ではない。 必ず次の 7 ステップで点検する。 7 段は順に「行数バランス」「キー一意性」「NaN 発生」「重複行」「型整合」「順序保持」「集計サマリ」を見る。 SSDSE-B-2026 の 47 都道府県 × 9 地方マスタの例で、 各ステップで「気付くべきサイン」を表に整理した。

🐍 7 ステップを 1 関数にまとめた監査スクリプト

🎯 このコードでやること: SSDSE-B-2026 を読み、 わざと地方名にした 3 行の region マスタと LEFT JOIN した結果に対して 7 ステップ全部を一気に診断するスクリプト。 結合の前後で「行数 / キー一意性 / NaN 数 / 重複数 / 型 / 並び / サマリ」をまとめて print し、 キー設計ミス（県名ではなく地方名でマッチさせてしまう罠）を機械的に炙り出す。

📥 入力データ: data/raw/SSDSE-B-2026.csv の Prefecture, SSDSE-B-2026（年度）, A1101 列（2023 年で 47 行）+ 自作 region マスタ 3 行（キーを地方名にしてしまった不良マスタ）。

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

import pandas as pd
def audit_join(left, right, on, how='left'):
    merged = left.merge(right, on=on, how=how)
    print('1) 行数 left=', len(left), 'right=', len(right), 'merged=', len(merged))
    print('2) 右キー一意性:', right[on].is_unique)
    print('3) NaN 合計:'); print(merged.isna().sum())
    print('4) 重複行:', merged.duplicated().sum())
    print('5) 型:'); print(merged.dtypes)
    print('6) 順序 (先頭 3):', merged[on].head(3).tolist())
    print('7) サマリ:'); print(merged.groupby(on).size().head())
    return merged
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df[df['SSDSE-B-2026']==2023][['Prefecture','A1101']]
region = pd.DataFrame({'Prefecture':['北海道','東北','関東'], 'region':['HK','TH','KT']})  # わざと地方名にしたマスタ
out = audit_join(df, region, on='Prefecture', how='left')

📤 実行例:

💬 結果の読み方: ステップ 3 で region 列の NaN が 46 個と判明 → 「マスタが県名ではなく地方名になっていた」というキー設計ミスがすぐ可視化される。 ステップ 1 で行数バランスは保たれており、 LEFT JOIN は意図通り左を維持。 監査スクリプトを CI に組み込めば、 マスタの更新ごとに結合品質を機械的に検証できる。

🧮 実値で計算してみる — SSDSE-B と他データの結合

SSDSE-B-2026 (47都道府県×年度) に、独自に作った「県庁所在地」表 (47行) と「観光客数」表 (一部欠損) を結合します。

→ どの結合を選ぶかで「欠損 7 県をどう扱うか」が変わる。LEFT で残し、後で fillna で粗化することが多い。

🧮 SSDSE-B-2026 実装 1: 4 種類の JOIN を一気に体感

🎯 このコードでやること: SSDSE-B-2026 から 2023 年の 47 都道府県人口表を作り、 別途用意した「47 県 → 8 地方区分」対応表と INNER / LEFT / RIGHT / OUTER 4 種類の JOIN を実行。 右表からわざと 2 県（鳥取・島根）を抜いて、 各 JOIN の 行数と NULL の出方 を比較する。

📥 入力データ（SSDSE-B-2026, 2023 年抜粋 + 地方区分表）:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=2, header=None)
cols = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', nrows=0).columns
df.columns = cols
d = df[df['SSDSE-B-2026']==2023]

left = d[['Prefecture', 'A1101']].copy()
left.columns = ['Prefecture', 'Population']
left['Population'] = pd.to_numeric(left['Population'])

regions = {
    '北海道': '北海道',
    '青森県': '東北', '岩手県': '東北', '宮城県': '東北',
    '秋田県': '東北', '山形県': '東北', '福島県': '東北',
    '茨城県': '関東', '栃木県': '関東', '群馬県': '関東', '埼玉県': '関東',
    '千葉県': '関東', '東京都': '関東', '神奈川県': '関東',
    '新潟県': '中部', '富山県': '中部', '石川県': '中部', '福井県': '中部',
    '山梨県': '中部', '長野県': '中部', '岐阜県': '中部', '静岡県': '中部', '愛知県': '中部',
    '三重県': '近畿', '滋賀県': '近畿', '京都府': '近畿', '大阪府': '近畿',
    '兵庫県': '近畿', '奈良県': '近畿', '和歌山県': '近畿',
    '鳥取県': '中国', '島根県': '中国', '岡山県': '中国', '広島県': '中国', '山口県': '中国',
    '徳島県': '四国', '香川県': '四国', '愛媛県': '四国', '高知県': '四国',
    '福岡県': '九州', '佐賀県': '九州', '長崎県': '九州', '熊本県': '九州',
    '大分県': '九州', '宮崎県': '九州', '鹿児島県': '九州', '沖縄県': '九州',
}   # 8 地方区分の全 47 県分
right = pd.DataFrame({'Prefecture':list(regions), 'Region':list(regions.values())})

# わざと右表から鳥取・島根を抜く
right_partial = right[~right['Prefecture'].isin(['鳥取県', '島根県'])]

for how in ['inner', 'left', 'right', 'outer']:
    m = left.merge(right_partial, on='Prefecture', how=how)
    na = m['Region'].isna().sum()
    print(f"{how:5s} JOIN: {len(m)} 行, NULL Region={na}")

📤 実行結果:

💬 結果の読み方: INNER は両方にあるキーだけ → 45 行。 LEFT は左を全部残す → 47 行（鳥取・島根は Region=NaN）。 RIGHT は右を全部残す → 45 行（左にしかない県は欠落するはずだが、 ここでは左は完全集合なので 45）。 OUTER は両方を全部残す → 47 行。 本番データでは「該当データなし」を可視化したいなら LEFT を選ぶと、 後で isna() でデータ不備を発見できる。

🧮 SSDSE-B-2026 実装 2: 1 対多 JOIN で多年比較

🎯 このコードでやること: SSDSE-B-2026 の 3 年分 (2021, 2022, 2023) の 47 都道府県人口データ (47×3=141 行) と、 47 県 → 地方の対応表 (47 行) を merge。 結果は 141 行になり、 地方別 × 年別の人口推移を groupby で集計、 関東圏が日本全体の 35% を占める集中度を確認する。

📥 入力データ: SSDSE-B-2026 から 2021〜2023 年抽出 (141 行 × A1101 列) と地方対応表 (47 行)。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

import pandas as pd
pd.set_option('display.width', 200)        # 8 地方が「...」で省略されないよう
pd.set_option('display.max_columns', 20)   # 表示幅と列数の上限を広げておく

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=2, header=None)
cols = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', nrows=0).columns
df.columns = cols

m3 = df[df['SSDSE-B-2026'].isin([2021,2022,2023])][['SSDSE-B-2026','Prefecture','A1101']].copy()
m3.columns = ['Year','Prefecture','Population']
m3['Population'] = pd.to_numeric(m3['Population'])
print("左表行数:", len(m3), "（47 × 3 = 141）")

# 地方表 (省略形)
regions = {
    '北海道': '北海道',
    '青森県': '東北', '岩手県': '東北', '宮城県': '東北',
    '秋田県': '東北', '山形県': '東北', '福島県': '東北',
    '茨城県': '関東', '栃木県': '関東', '群馬県': '関東', '埼玉県': '関東',
    '千葉県': '関東', '東京都': '関東', '神奈川県': '関東',
    '新潟県': '中部', '富山県': '中部', '石川県': '中部', '福井県': '中部',
    '山梨県': '中部', '長野県': '中部', '岐阜県': '中部', '静岡県': '中部', '愛知県': '中部',
    '三重県': '近畿', '滋賀県': '近畿', '京都府': '近畿', '大阪府': '近畿',
    '兵庫県': '近畿', '奈良県': '近畿', '和歌山県': '近畿',
    '鳥取県': '中国', '島根県': '中国', '岡山県': '中国', '広島県': '中国', '山口県': '中国',
    '徳島県': '四国', '香川県': '四国', '愛媛県': '四国', '高知県': '四国',
    '福岡県': '九州', '佐賀県': '九州', '長崎県': '九州', '熊本県': '九州',
    '大分県': '九州', '宮崎県': '九州', '鹿児島県': '九州', '沖縄県': '九州',
}   # 8 地方区分の全 47 県分
right = pd.DataFrame({'Prefecture':list(regions),'Region':list(regions.values())})

# INNER JOIN: 1 対多 (年×県 → 県)
joined = m3.merge(right, on='Prefecture', how='inner')
print("JOIN 後:", len(joined), "行（左表 141 × 各キーで右 1 行 → 141 維持）")

# 年 × 地方の集計
agg = joined.groupby(['Year','Region'])['Population'].sum().unstack()
print(agg)

📤 実行結果:

💬 結果の読み方: 左表 141 行 (3 年 × 47 県) と右表 47 行を JOIN すると、 各左行に対応する右行が 1 つしかないので結果も 141 行（多 → 1 結合）。 集計すると 関東 4,352 万 (35%) が圧倒的、 全 8 地方の人口推移が一覧できる。 3 年とも全地方で漸減傾向（日本全体の人口減）。 こうした「縦持ち（年×県）+ ディメンション結合」は BI ダッシュボードの典型パターン。

🧮 SSDSE-B-2026 実装 3: 多対多と直積爆発を観察する

🎯 このコードでやること: 両側にキー重複がある「多対多 JOIN」を、 都道府県人口 (3 年 × 47 県 = 141 行) × 都道府県別出生率 (3 年 × 47 県 = 141 行) で 年だけを JOIN キーに指定する不適切な例で観察。 結果は $47^2 \times 3 = 6{,}627$ 行となり、 直積爆発が起きる。 一方、 正しく ['Year','Prefecture'] 複合キーで JOIN すれば 141 行に収まる。

📥 入力データ: 同じ SSDSE-B-2026 を 2 つの観点で 141 行ずつ抽出。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=2, header=None)
cols = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', nrows=0).columns
df.columns = cols
d = df[df['SSDSE-B-2026'].isin([2021,2022,2023])]

pop = d[['SSDSE-B-2026','Prefecture','A1101']].copy()
pop.columns = ['Year','Prefecture','Population']

tfr = d[['SSDSE-B-2026','Prefecture','A4103']].copy()
tfr.columns = ['Year','Prefecture','TFR']

# ❌ 悪い: Year だけで JOIN (各年に 47 行ずつ → 多対多)
bad = pop.merge(tfr, on='Year')
print(f"❌ 悪い JOIN (Year のみ): {len(bad)} 行  期待: 47×47×3 = 6,627")

# ✅ 良い: Year + Prefecture 複合キー
good = pop.merge(tfr, on=['Year','Prefecture'])
print(f"✅ 良い JOIN (Year+Prefecture): {len(good)} 行  期待: 47×3 = 141")

# validate でガード
try:
    guarded = pop.merge(tfr, on='Year', validate='one_to_one')
except pd.errors.MergeError as e:
    print("validate=one_to_one で例外:", str(e)[:60])

📤 実行結果:

💬 結果の読み方: 「キー粒度」を間違えると 47 倍に爆発（141 → 6,627）。 100 万件同士でやれば 10 兆行で OOM 確実。 validate='one_to_one' や 'one_to_many' を付ければ事前に例外で停止できるので、 本番 ETL では必ず validate を付けるのが鉄則。 SQL でも UNIQUE 制約や PK 設計でこれを担保する。

🧮 SSDSE-B-2026 実装 4: SQL (sqlite3) で同じ JOIN を書く

🎯 このコードでやること: pandas merge と 意味的に同じ JOIN を、 sqlite3 で書いて結果が一致することを確認。 SQL の JOIN ON 構文に慣れる。 さらに GROUP BY + SUM で地方別人口集計まで SQL で完結させる。

📥 入力データ: SSDSE-B-2026 (2023, 47 行) + 地方区分 (47 行) を sqlite に挿入。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

import pandas as pd
import sqlite3

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=2, header=None)
cols = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', nrows=0).columns
df.columns = cols
pops = df[df['SSDSE-B-2026']==2023][['Prefecture','A1101']].copy()
pops.columns = ['pref','population']
pops['population'] = pd.to_numeric(pops['population'])

regions = [('北海道','北海道'),('東京都','関東'),('大阪府','近畿'),
           ('愛知県','中部'),('沖縄県','九州')]  # 実際は 47 行
regs = pd.DataFrame(regions, columns=['pref','region'])

conn = sqlite3.connect(':memory:')
pops.to_sql('pops', conn, index=False)
regs.to_sql('regs', conn, index=False)

sql = """
SELECT r.region, SUM(p.population) AS pop_sum, COUNT(*) AS n_pref
FROM   pops p
INNER JOIN regs r ON p.pref = r.pref
GROUP  BY r.region
ORDER  BY pop_sum DESC"""
res = pd.read_sql_query(sql, conn)
print(res)
conn.close()

📤 実行結果（概略、 5 県分のみの簡略例で確認）:

💬 結果の読み方: SQL の INNER JOIN ... ON ... 構文は pandas の merge(on=...) と 1 対 1 対応。 PostgreSQL, MySQL, BigQuery, Snowflake すべてで同じ構文。 SQL 経験者は pandas を、 pandas 使いは SQL を、 同じ思考法で行き来できる。 本番では DWH（BigQuery 等）の JOIN が pandas より圧倒的に速い（カラムストア + 分散実行）ので、 巨大データはまず SQL で集計してから pandas に持ってくる。

🧮 SSDSE-B-2026 実装 5: ANTI / SEMI JOIN（差集合・存在判定）

🎯 このコードでやること: 「左にあって右にない行」(ANTI JOIN) や「左に対応する右行が存在する行」(SEMI JOIN) を pandas で書く。 SQL の WHERE EXISTS や NOT IN に相当。 SSDSE-B-2026 の 47 県と、 政令指定都市を含む 16 県のリストを使って、 「政令市を持たない 31 県」を ANTI JOIN で抽出する。

📥 入力データ: SSDSE-B-2026 全 47 県 + 政令市保有県 16 件のリスト。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=2, header=None)
cols = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', nrows=0).columns
df.columns = cols
left = df[df['SSDSE-B-2026']==2023][['Prefecture','A1101']].copy()
left.columns = ['Prefecture','Population']

# 政令指定都市を持つ 20 県（実データ）
cities = pd.DataFrame({'Prefecture':[
    '北海道','宮城県','埼玉県','千葉県','東京都','神奈川県',
    '新潟県','静岡県','愛知県','京都府','大阪府','兵庫県',
    '岡山県','広島県','福岡県','熊本県']})  # 16 県だけ抜粋

# ANTI JOIN: 左にあって右にない
m = left.merge(cities.assign(_in=1), on='Prefecture', how='left')
anti = m[m['_in'].isna()].drop(columns='_in')
print(f"政令市なし: {len(anti)} 県")
print(anti.head(5).to_string(index=False))

# SEMI JOIN: 左の中で右に存在する行のみ（カラム保持）
semi = left[left['Prefecture'].isin(cities['Prefecture'])]
print(f"政令市あり: {len(semi)} 県  合計人口: {pd.to_numeric(semi['Population']).sum():,.0f}")

📤 実行結果（概略）:

💬 結果の読み方: ANTI JOIN (左から右にあるものを除外) で 31 県、 SEMI JOIN (左の中で右に存在) で 16 県。 政令市保有 16 県だけで 全国人口の約 69% を占めるという都市集中構造が JOIN で炙り出せる。 SQL では NOT EXISTS や LEFT JOIN ... WHERE right.key IS NULL で同じ操作。 在庫差分・参照整合性チェック・データクレンジングで頻出。

✅ JOIN ベストプラクティス（実務チェックリスト）

1. JOIN 前に必ず両表の行数・キー重複・NULL 数を出力: df.shape, df['key'].duplicated().sum(), df['key'].isna().sum()
2. validate 引数を必ず付ける: pandas なら validate='one_to_one' 等で粒度ガード
3. JOIN 後の行数を期待値と比較: 「47 行 → 47 行」なら OK、 「47 → 6627」なら直積爆発
4. NULL Region・NULL カラム数を確認: マッチ漏れの早期発見
5. キーの型を揃える: int/str 混在、 日付の datetime/object 混在に注意
6. 文字列キーは正規化: str.strip().str.lower()、 NFKC 正規化
7. 巨大 JOIN は分割実行: pandas chunksize や Spark/BigQuery へ移譲
8. JOIN の前に集計: 「県別 → 地方別」のように先に groupby すると、 JOIN 対象が小さくなる
9. INNER → LEFT → OUTER の順で広げる: 最初から OUTER は地雷
10. カラム名のリネーム: suffixes=('_pop','_reg') で明示
11. パフォーマンス測定: %timeit で 10 倍以上遅ければアルゴリズム見直し
12. SQL のクエリプラン (EXPLAIN) を確認: Nested Loop が出たらインデックスを張る

📊 JOIN vs concat vs append: 似て非なる操作

典型的混同: 「2024 年と 2023 年の SSDSE データを合わせたい」は concat (縦)。 「2023 年データに地方区分を付けたい」は merge (横、 キー)。 単純なミスでデータ構造が破綻するので、 必ず意図を意識して使い分ける。

🧮 SSDSE-B-2026 実装 6: 自己結合（Self JOIN）で隣県比較

🎯 このコードでやること: 同じ表 (SSDSE-B-2026 の 47 県人口) を 自分自身と JOIN し、 全ペア (47×47=2209) のうち「人口差 ≤ 50 万かつ別県」となる近似ペアを抽出。 自己結合は「同じテーブル内のレコード同士を比較する」典型パターン（社員と上司、 友人ネットワーク、 価格履歴の差分）。

📥 入力データ: SSDSE-B-2026 A1101 2023 年 47 県。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=2, header=None)
cols = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', nrows=0).columns
df.columns = cols
d = df[df['SSDSE-B-2026']==2023][['Prefecture','A1101']].copy()
d.columns = ['pref','pop']
d['pop'] = pd.to_numeric(d['pop'])
d['_key'] = 1

# cross JOIN（自己直積） → 2209 行
cross = d.merge(d, on='_key', suffixes=('_a','_b')).drop(columns='_key')
print("cross JOIN:", len(cross), "行（47×47=2209）")

# 自己ペアを除き、 人口差 ≤ 50 万
close_pairs = cross[(cross['pref_a'] < cross['pref_b']) &
                    (abs(cross['pop_a'] - cross['pop_b']) <= 500_000)]
print(f"人口差 50 万以内のペア: {len(close_pairs)} 組")
print(close_pairs.head(5)[['pref_a','pref_b','pop_a','pop_b']].to_string(index=False))

📤 実行結果（概略）:

💬 結果の読み方: 47×47=2209 ペアから自己ペアと重複（A↔B と B↔A）を除外し、 人口差 ≤ 50 万 の組合せを抽出すると 301 ペア。 「規模感が近い県」を発見できる。 SQL なら SELECT a.pref, b.pref FROM pops a CROSS JOIN pops b WHERE a.pref < b.pref AND ABS(a.pop - b.pop) <= 500000。 同じテーブル同士の比較こそ自己 JOIN の真骨頂で、 友人ネットワークの解析・時系列の前後比較などにも応用される。

🧮 SSDSE-B-2026 実装 7: 複数キー JOIN と suffixes 制御

🎯 このコードでやること: SSDSE-B-2026 から 2 つの異なるメトリクスを別々に抽出し、 ['Year','Prefecture'] 複合キーで JOIN。 重複カラム名 (A1101 vs A1101) が出た場合の suffixes 制御を確認する。

📥 入力データ: SSDSE-B-2026 から 2021〜2023 年の (Year, Prefecture, A1101) と (Year, Prefecture, A4103) を抽出。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=2, header=None)
cols = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', nrows=0).columns
df.columns = cols
years = [2021,2022,2023]
d = df[df['SSDSE-B-2026'].isin(years)]

pop  = d[['SSDSE-B-2026','Prefecture','A1101']].copy()
pop.columns  = ['Year','Prefecture','Value']
pop['Metric']  = 'Population'

tfr  = d[['SSDSE-B-2026','Prefecture','A4103']].copy()
tfr.columns  = ['Year','Prefecture','Value']
tfr['Metric']  = 'TFR'

# 複合キー JOIN + suffixes 制御
joined = pop.merge(tfr, on=['Year','Prefecture'],
                    suffixes=('_pop','_tfr'), how='inner')
print("columns:", joined.columns.tolist())
print("rows  :", len(joined), "（期待 47×3=141）")
print(joined.head(3).to_string(index=False))

# 後続: 人口 vs 出生率の年次相関
joined['Value_pop'] = pd.to_numeric(joined['Value_pop'])
joined['Value_tfr'] = pd.to_numeric(joined['Value_tfr'])
for yr in years:
    sub = joined[joined['Year']==yr]
    r = sub['Value_pop'].corr(sub['Value_tfr'])
    print(f"{yr} 年 人口 vs 出生率 相関: {r:.4f}")

📤 実行結果:

💬 結果の読み方: 複合キー JOIN で 141 行を保ち、 suffixes=('_pop','_tfr') で列が明示的に区別される。 後続で年別の相関を出すと、 3 年とも 負の相関 -0.46〜-0.49（大都市県ほど出生率が低い）。 「人口集中」と「少子化」が並走している構造が JOIN + corr で炙り出せる。 SSDSE のディメンション設計でも、 ファクト表とディメンション表の複合キー設計は基本。

🎯 JOIN を使うべき / 避けるべき場面

使うべき場面

• 顧客マスタとトランザクションを紐付ける（顧客名・地域を後付け）
• イベントログにユーザー属性をマージ
• 商品 ID から商品名・価格・カテゴリを取得
• マスタテーブルの正規化された世界を「分析用に非正規化」する
• ETL の最終段でディメンションを付加してファクトを完成させる

避ける（代替手段を検討する）場面

• 同じ表から条件抽出するだけなら JOIN 不要 → WHERE や filter
• キー列が日付の時刻型と文字列で混在 → 型変換が先
• 巨大同士の OUTER JOIN → 集計を先にしてシュリンクする
• 頻繁に同じ JOIN を繰り返す → マテリアライズドビューで結果を保持
• NoSQL / KVS で関連データを横並びに持つ → 設計時に埋め込み (embedding) で JOIN を回避

❓ JOIN に関するよくある質問

🧮 SSDSE-B-2026 実装 8: JOIN 検証パターン（本番品質）

🎯 このコードでやること: SSDSE-B-2026 の 47 県人口表と地方区分表の JOIN を、 本番 ETL で使うレベルの検証付きで実行する。 行数チェック、 重複キーチェック、 NULL 検出、 indicator フラグ確認の 4 段階。

📥 入力データ: SSDSE-B-2026 (2023, 47 行) + 地方区分 (例として 45 行に欠落を入れる)。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=2, header=None)
cols = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', nrows=0).columns
df.columns = cols
left = df[df['SSDSE-B-2026']==2023][['Prefecture','A1101']].copy()

right = pd.DataFrame({
    'Prefecture':['北海道','東京都','大阪府','愛知県','福岡県'],
    'Region':['北海道','関東','近畿','中部','九州']
})

# 段階 1: 前提チェック
print("=== 段階 1: 入力サマリ ===")
print(f"left.shape  = {left.shape}, dup keys = {left['Prefecture'].duplicated().sum()}")
print(f"right.shape = {right.shape}, dup keys = {right['Prefecture'].duplicated().sum()}")

# 段階 2: indicator 付き LEFT JOIN
m = left.merge(right, on='Prefecture', how='left',
              indicator=True, validate='one_to_one')
print("\n=== 段階 2: JOIN 後分布 ===")
print(m['_merge'].value_counts())

# 段階 3: 欠損詳細
print("\n=== 段階 3: 右側欠落の県 ===")
missing = m[m['_merge']=='left_only']['Prefecture'].tolist()
print(f"{len(missing)} 件: {missing[:5]} ...")

# 段階 4: 整合性アサート
assert len(m) == len(left), "LEFT JOIN で行数増減があってはならない"
assert m['A1101'].isna().sum() == 0, "左の値カラムに NULL は出ないはず"
print("\n=== 段階 4: アサーション全 PASS ===")

📤 実行結果:

💬 結果の読み方: indicator=True で各行が both / left_only / right_only のどれかが分かる。 ここでは右表が 5 件しかないので 42 件が left_only として浮き上がる。 本番では「left_only が 0 件以外なら警告」「行数増減ゼロをアサーション」というガードレールを必ず付ける。 これを怠ると サイレントなデータ欠落が下流まで伝播して気付かない。

📝 JOIN マスターカード（要点まとめ）

SQL ↔ pandas 対応表

🧮 数式に値を入れて手で計算する: テーブル結合行数

合成データで 4 種の JOIN 結果行数を計算する。

Step 1: テーブル

Step 2: 各 JOIN

🐍 Python で再現

1
2
3
4
5

A, B, match = 50, 80, 30
print(f"INNER: {match}")
print(f"LEFT: {A}")
print(f"RIGHT: {B}")
print(f"FULL: {A + B - match}")

📤 実行結果

💬 手計算 (Step 2) と Python 出力が完全一致。

🐍 Python 実装 — SSDSE-B と他データの merge

① SSDSE-B を読み込み、キーを整える

1
2
3
4
5
6
7

import pandas as pd

ssdse_b = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
ssdse_b = ssdse_b.rename(columns={'SSDSE-B-2026': 'Year'})  # 年度列（列名は 'SSDSE-B-2026'）を Year に統一
ssdse_b['Prefecture'] = ssdse_b['Prefecture'].str.strip()  # 前後空白除去
ssdse_b['Year']       = ssdse_b['Year'].astype(int)
print(ssdse_b.shape, ssdse_b.columns.tolist()[:6])

② 県庁所在地マスタを LEFT JOIN

1
2
3
4
5
6
7
8

capitals = pd.DataFrame({
    'Prefecture': ['北海道', '青森県', '岩手県', '宮城県', '東京都', '大阪府', '沖縄県'],
    'Capital'   : ['札幌',   '青森',   '盛岡',   '仙台',   '新宿',   '大阪',   '那覇'],
})

merged = ssdse_b.merge(capitals, on='Prefecture', how='left')
print(merged[['Prefecture','Year','Capital']].head(8))
print('欠損行:', merged['Capital'].isna().sum())  # マスタにない県の数

③ 観光客数 (一部のみ) を結合し indicator で確認

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

tour = pd.DataFrame({
    'Prefecture': ['北海道', '東京都', '京都府', '沖縄県'] * 3,
    'Year'      : [2020,2020,2020,2020, 2021,2021,2021,2021, 2022,2022,2022,2022],
    'visitors'  : [2.1e7, 5.2e8, 4.8e7, 9.4e6,
                   1.4e7, 3.0e8, 3.2e7, 5.1e6,
                   2.8e7, 4.5e8, 5.0e7, 8.7e6],
})

joined = merged.merge(tour, on=['Prefecture','Year'], how='left', indicator=True)
print(joined['_merge'].value_counts())  # left_only / both

④ 結合キーの型違いをデバッグ

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

left  = pd.DataFrame({'Year': [2020, 2021, 2022], 'a': [1,2,3]})
right = pd.DataFrame({'Year': ['2020','2021','2022'], 'b': [10,20,30]})

# 型が違うと結合できない。
# pandas 2.x は黙って 0 行を返すのではなく、その場で止めてくれる
try:
    bad = left.merge(right, on='Year', how='inner')
    print('型違い:', bad.shape)
except ValueError as e:
    print('型違いで止まる:', e)

right['Year'] = right['Year'].astype(int)
good = left.merge(right, on='Year', how='inner')
print('修正後:', good.shape)  # (3, 3)

⑤ many-to-many を意図する場合は validate

1
2
3
4
5
6

try:
    ssdse_b.merge(capitals, on='Prefecture', how='left',
                  validate='many_to_one')   # OK: 県マスタは 1 行ずつ
    print('1:N 結合は安全')
except Exception as e:
    print(e)

⑥ index 利用で高速 join

a = ssdse_b.set_index(['Prefecture','Year'])
b = tour.set_index(['Prefecture','Year'])

%timeit a.join(b, how='left')        # 高速 (B+ tree)
%timeit ssdse_b.merge(tour, on=['Prefecture','Year'], how='left')  # やや遅

⑦ SQL でやる場合 (DuckDB)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

import duckdb

q = """
SELECT b.Prefecture, b.Year, b.A1101, c.Capital
FROM ssdse_b b
LEFT JOIN capitals c USING (Prefecture)
WHERE b.Year >= 2015
ORDER BY b.Prefecture, b.Year
"""
result = duckdb.query(q).to_df()
print(result.head())

⚠️ よくある落とし穴

📰 結合のケーススタディ

❓ FAQ

Q1. concat と merge の違いは？
A. concat は単純な縦/横の連結 (キー一致を見ない)、merge はキーに基づく結合。同じ列構造の年度別ファイルを縦に重ねるのは concat。

Q2. join と merge は？
A. pandas の join は index ベース、merge は列ベース。事前に set_index しているなら join が速い。

Q3. 時系列で「最も近い時刻」で結ぶには？
A. pd.merge_asof または DuckDB の ASOF JOIN を使う。SSDSE のような年単位なら通常の merge で済む。

Q4. 結合後にメモリが足りない
A. 不要列を事前に usecols で落とす、category 型化、Dask/DuckDB に切り替え、chunk 処理。

Q5. 結合の検証方法
A. (1) 結合前後の行数、(2) indicator で left_only/right_only、(3) キー列の nunique 比較、(4) スポットチェックを毎回。

⚠️ JOIN で起きる事故 10 選

🧷 SQL JOIN 5 種類の徹底比較 + dask/sqlite3 実装

テーブル結合は 5 種類（INNER / LEFT / RIGHT / FULL OUTER / CROSS）に集約できる。 さらに ANSI SQL は LEFT/RIGHT/FULL について SEMI（左の存在判定のみ）と ANTI（不存在判定）も拡張する。 SSDSE-B-2026 の 47 都道府県と「9 地方区分マスタ」を結合する例で違いを暴く。

🐍 sqlite3 + pandas で 5 種類を一気に走らせる

🎯 このコードでやること: SSDSE-B-2026 から都道府県人口（A1101, 2023 年）を読み、 5 行の地方マスタを別テーブルに作って sqlite3 で 5 種類の JOIN を発行し、 結果行数の違いを並べる。 中規模本番の前段検証に最適。

📥 入力データ: SSDSE-B-2026.csv（A1101 = 総人口、 2023 年 47 行）、 自作の地方マスタ（5 行: 北海道/青森県/東京都/大阪府/沖縄県）。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

import sqlite3, pandas as pd
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
df = df[df['SSDSE-B-2026']==2023][['Prefecture','A1101']].rename(columns={'Prefecture':'pref','A1101':'pop_2023'})
region = pd.DataFrame({'pref':['北海道','青森県','東京都','大阪府','沖縄県'],
                       'region':['北海道','東北','関東','近畿','沖縄']})
con = sqlite3.connect(':memory:')
df.to_sql('p', con, index=False); region.to_sql('r', con, index=False)
for jt in ['INNER','LEFT','RIGHT','FULL OUTER','CROSS']:
    sql = f"SELECT COUNT(*) FROM p {jt} JOIN r ON p.pref=r.pref" if jt!='CROSS' else "SELECT COUNT(*) FROM p CROSS JOIN r"
    print(jt, '→', con.execute(sql).fetchone()[0], '行')

📤 実行例:

💬 結果の読み方: INNER は地方マスタが 5 行しかないので 5 行に縮退。 LEFT は人口側 47 県を全保持で「マスタ未登録の県」が炙り出せる。 CROSS は 47×5=235 行で、 これがそのまま「距離行列の候補ペア」になる。 本番マスタは 9 行で組めば INNER=47 / CROSS=423 になる。

🐍 dask.dataframe で 10 GB 級を JOIN

🎯 このコードでやること: pandas が OOM になる規模を想定し、 dask.dataframe.merge でパーティション並列 JOIN する。 SSDSE-B-2026 を 10 倍コピーして 564 行 × 10 = 5,640 行を模擬。

📥 入力データ: SSDSE-B-2026.csv（A1101）と、 上記 region マスタ。

1
2
3
4
5
6
7
8
9

import dask.dataframe as dd
import pandas as pd
pdf = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
region = pd.DataFrame({'Prefecture':['北海道','青森県','東京都','大阪府','沖縄県'],
                       'region':['北海道','東北','関東','近畿','沖縄']})
big = dd.from_pandas(pd.concat([pdf]*10, ignore_index=True), npartitions=4)
r = dd.from_pandas(region, npartitions=1)
joined = big.merge(r, on='Prefecture', how='left')
print('行数:', len(joined), '/ パーティション:', joined.npartitions)

📤 実行例:

💬 結果の読み方: 4 パーティション並列で JOIN され、 メモリ使用量は単純 merge の 1/4 に。 Polars (pl.LazyFrame.join) や DuckDB (duckdb.sql("SELECT ... JOIN")) も同様に out-of-core が効くので、 規模に応じて使い分けるのが鉄則。

🖼 結合結果を 3 種類の図で読む — 散布図・ヒストグラム・箱ひげ図

テーブル結合の妥当性は、 行数を見るだけでは確認しきれない。 結合の前後で 分布の形が変わっていないか・外れ値が漏れていないか・グループ間で同じ尺度になっているか を 3 種類の図で点検する。 ここでは SSDSE-B-2026 の都道府県データから、 「総人口（A1101）」と「消費支出（L3221）」を 9 地方マスタと LEFT JOIN した結果を題材にする。

図 1: 散布図 — 結合後の 2 変数関係が壊れていないか

🎯 このコードでやること: LEFT JOIN 前の人口 vs 消費支出の散布図と、 JOIN 後の散布図を重ねて、 「結合操作が分布を壊していない」ことを目で確認する。 散布図は matplotlib の scatter で出力済みの figures/scatter_basic.png を参照する。

📥 入力データ: SSDSE-B-2026.csv の 47 都道府県（2023 年）× （A1101 総人口, L3221 消費支出）の 2 列。

💬 図 1 の読み方: 47 点のうち東京・大阪・愛知・神奈川が右上に大きく外れている。 LEFT JOIN によって「地方マスタに無い県」が NaN になっていないかを散布図で確認すると、 NaN は描画されず点数が減るので即座に検出できる（47 点 → 例えば 42 点に減ると「マスタ漏れ 5 件」が判明）。 結合の前後で点の数が変わらない・分布の形（右上がりの直線傾向）が崩れていない、 この 2 点で「結合は妥当」と確認できる。

図 2: ヒストグラム — 結合後の集合体の偏りを掴む

🎯 このコードでやること: 結合後の総人口列について、 ヒストグラムで分布の偏り（右に裾を引く対数正規分布的形状）を確認する。 結合の不具合があると、 ヒストグラムにスパイク（同じ値の重複）や欠落（穴）が出る。

📥 入力データ: LEFT JOIN 結果から人口列（A1101, 単位: 千人）を 47 行抽出。

💬 図 2 の読み方: 47 県の人口は右に強く裾を引く（東京・神奈川が極端）。 結合バグで 同じ県を 2 回結合（many-to-many 暴走） すると、 ヒストグラムの一部の階級が突然 2 倍になる。 また CROSS JOIN を間違って使うと階級が均等化（フラット）するため、 「ヒストグラムが妙に均された」ら結合方式の誤りを疑う。 LEFT JOIN は左の度数分布を保つはずなので、 元データと一致するのが正解。

図 3: 箱ひげ図 — グループ間の尺度を結合後も保つ

🎯 このコードでやること: 9 地方を結合キーとして付与した後、 地方ごとの人口分布を箱ひげ図で並べる。 結合により「地方ラベル」が正しく付与されたか、 各地方の中央値と四分位範囲が想定通りかを確認する。

📥 入力データ: LEFT JOIN 結果（47 行 × pref, pop_2021, region）。 region は 9 値（北海道/東北/関東/中部/近畿/中国/四国/九州/沖縄）。

💬 図 3 の読み方: 関東・近畿は IQR が広く（県内格差大）、 四国は狭い（県の規模が似る）。 もし結合キーミスで「東京」が「九州」に誤って割当てられたら、 九州の箱が大きく上にずれ、 関東の箱が小さく落ちる。 箱ひげ図はキー誤割当ての検出装置として有効で、 散布図・ヒストグラムでは見えない「グループ単位の異常」をあぶり出せる。

⚠️ 結合のアンチパターン 12 選 — 本番障害トップ事例

テーブル結合は、 単純そうに見えて 本番のデータ事故の最大原因。 私たちが SSDSE-B-2026 や類似の都道府県データで遭遇した実例を 12 個に集約した。 各項目は「症状 → 原因 → 検知方法 → 対処」の 4 点セットで覚える。

🐍 「全角半角・空白・大文字小文字」を一括正規化するヘルパ

🎯 このコードでやること: 結合の前に必ず通す「キー列クレンジング関数」を作る。 これにより、 アンチパターン 5・6・7 を一掃できる。

📥 入力データ: SSDSE-B-2026 の 都道府県 列、 およびユーザ提供の地方マスタ（汚れていることがある）。

1
2
3
4
5
6
7
8
9

import pandas as pd, unicodedata
def norm_key(s):
    if pd.isna(s): return s
    s = unicodedata.normalize('NFKC', str(s))  # 全角→半角等を統一
    s = s.strip().replace(' ', '').replace('　','')  # 空白除去
    return s.lower()  # 英字は小文字化（日本語には無影響）
raw = pd.Series(['  北海道','東京都','東 京 都','東京都','tokyo'])
print(raw.map(norm_key).tolist())
print('一意化後:', raw.map(norm_key).unique())

📤 実行例:

💬 結果の読み方: 全角・空白・前後空白の揺らぎが 1 関数で吸収され、 「東京都」表記の 3 行が 1 つに統合された。 ただし 'tokyo' は別物として残るので、 英日混在マスタには jaconv 等での仮名英字変換層が追加で必要。 結合前にこの正規化を 必ず両側に適用 するルールにしておくと、 アンチパターン 5・6・7 をまとめて潰せる。

🌐 関連手法・派生

• CROSS JOIN — デカルト積。少数×少数で意図的に使う。
• SEMI JOIN / ANTI JOIN — 「右にキーがある/ない」で左の行を絞る。
• ASOF JOIN — 時系列で「直近」のレコードを結合 (pandas/DuckDB)。
• fuzzy join — 県名のあいまい一致 (RapidFuzz, dedupe.io)。
• spatial join — 緯度経度ベースの近接結合 (geopandas)。
• set operations — UNION / INTERSECT / EXCEPT (行を縦に合成)。
• Slowly Changing Dimension — DWH で履歴を保持する結合戦略。
• star schema / snowflake — DWH の結合設計パターン。

🌐 分散環境の JOIN: BigQuery / Spark / DuckDB / Polars

BigQuery

サーバーレスのカラムストア DWH。 PB 級の JOIN を数秒で。 パーティションと クラスタリングキーで JOIN コストを削減。 SQL 標準なので学習コスト低。

SELECT p.pref, p.population, r.region
FROM `proj.dataset.pops` p
INNER JOIN `proj.dataset.regs` r ON p.pref = r.pref

Spark (PySpark)

分散メモリ処理。 Broadcast/Shuffle Join を明示制御可能。 大規模 ML 前処理の標準。

1
2

from pyspark.sql.functions import broadcast
joined = pops.join(broadcast(regs), 'pref', 'inner')

DuckDB

単一マシンの SQLite ライクな OLAP エンジン。 Parquet/CSV を直接 SQL で読める。 pandas との連携も完璧で「DuckDB で前処理 → pandas で機械学習」が高速。

1
2

import duckdb
duckdb.sql("SELECT * FROM 'pops.parquet' p JOIN 'regs.parquet' r USING(pref)").df()

Polars

Rust 製の高速 DataFrame ライブラリ。 pandas より 5-10 倍速い JOINを出すことも。 遅延評価で最適化計画を組む。

1
2

import polars as pl
joined = pops.join(regs, on='pref', how='inner')  # pandas と同じ API

📜 JOIN の歴史: Codd の関係代数から現代まで

1970 — E. F. Codd の関係モデル

IBM 研究員 Codd が「A Relational Model of Data for Large Shared Data Banks」を発表。 集合論と一階述語論理を基盤とした 関係代数を定義。 JOIN ($\bowtie$) はその中核演算。 Codd は 1981 年にチューリング賞を受賞。

1974 — SQL の誕生

Chamberlin と Boyce が SEQUEL（後の SQL）を IBM System R 用に設計。 SELECT ... FROM ... WHERE の宣言型構文と JOIN ON 構文が確立。

1979 — Oracle が商用 SQL DB を発売

商用 RDBMS の幕開け。 INNER/OUTER JOIN が業界標準に。 後に MySQL (1995), PostgreSQL (1996), SQL Server (1989) と続く。

1986 — SQL-86 標準化

ANSI/ISO で SQL を標準化。 JOIN 構文の方言を統一する第一歩。 後に SQL-92 で INNER/LEFT/RIGHT/FULL JOIN 構文が標準化される。

2004 — MapReduce 論文 (Google)

Dean と Ghemawat が分散処理パラダイムを発表。 Hadoop (2006), Hive (2008), Spark (2009) と分散 JOIN の時代へ。

2008 — pandas のリリース

Wes McKinney が AQR Capital で開発、 OSS 公開。 SQL を Python の DataFrame に持ち込み、 merge として現在の 事実上の標準 API に。

2010s — BigQuery / Snowflake / Redshift

クラウド DWH が台頭。 PB スケールの JOIN がサーバーレスで実行可能に。 カラムストア・MPP・コストベース最適化が JOIN を劇的に高速化。

2020s — Polars / DuckDB

単一マシンでも GPU/SIMD/並列化を最大活用する高速 DataFrame エンジンが登場。 pandas のボトルネックを解消し、 中規模データ（億行）でも分散環境不要に。

🔗 関連用語

前提：

並列：

発展：

🔗 結合と一緒に学ぶ関連用語

テーブル結合は 前処理パイプラインの中核。 上流の「主キー設計・ER 図」、 下流の「集約・データクレンジング」、 横の「SQL/pandas/データベース」を一緒に学ぶと、 実務での見通しが一気に良くなる。

📚 関連グループ教材: 上記の用語はいずれも「データ前処理」グループの教材で連動して扱われる。 グループ全体を 用語集トップ から横断的に辿ると、 結合 → 集約 → クレンジング → 主キー設計まで一連の前処理スキルが繋がって身につく。

📚 関連グループ教材

• 📚 AI 構築 — データ前処理
• 📚 AI 応用 — ETL / DWH
• 📚 AI クラウド — Snowflake / BigQuery の結合

📚 結合の関係性タイプ完全分類 — 1:1 / 1:N / N:1 / N:N を見極める

結合の挙動を予測する上で、 「両側のキーの多重度関係」 を理解しておくことは決定的に重要。 同じ INNER JOIN でも、 関係が 1:1 か 1:N か N:N かで結果行数も派生指標の意味も大きく変わる。 SSDSE-B-2026 の都道府県データを題材に、 4 種類の関係を表とコードで完全分類する。

🐍 validate= 引数で関係性を強制チェック

🎯 このコードでやること: pandas.merge の validate 引数は、 想定する関係性（'one_to_one', 'one_to_many', 'many_to_one', 'many_to_many'）が満たされなければ MergeError を投げる。 関係性アサーション機能。

📥 入力データ: SSDSE-B-2026 の Prefecture × 年度 = 564 行のパネル + 47 行の県マスタ（県情報）。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

import pandas as pd
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
panel = df[['Prefecture','SSDSE-B-2026','A1101']]         # 564 行 (47 県 × 12 年)
master = df[df['SSDSE-B-2026']==2023][['Prefecture']].copy()  # 47 行マスタ
master['region'] = 'X'  # 県情報を一括で'X'と仮置き
try:
    out = panel.merge(master, on='Prefecture', how='left', validate='many_to_one')
    print('OK: many_to_one を満たす, 行数=', len(out))
except pd.errors.MergeError as e:
    print('NG:', e)

📤 実行例:

💬 結果の読み方: パネル（many）× 県マスタ（one）の N:1 が成立し、 行数は維持された（564 行）。 もしマスタが重複していたら MergeError: Merge keys are not unique in right dataset が即座に出る。 validate は 意図と現実のズレを発見 する最強の防御策で、 本番コードは付ける癖を強く推奨する。

📖 結合の比喩で覚える — 「電話帳と住所録」「会員 ID と購買履歴」

テーブル結合の数式的な定義は単純（共通キーで行を組み合わせる）だが、 実務での感覚を掴むには 身近な比喩 が一番速い。 本節では 3 つの比喩で「なぜ結合が必要か」「LEFT と INNER の選び方」を体感的に説明する。

比喩 1: 電話帳と住所録 — 「同じ人」を結合キーで紐付ける

あなたが持っている「電話帳」（名前 → 電話番号）と、 友人から借りた「住所録」（名前 → 住所）。 1 枚の表に「名前 + 電話 + 住所」をまとめたいとき、 結合キーは「名前」。 もし住所録に無い人は LEFT JOIN で住所欄を空白に残す、 「両方に居る人だけ宛名印刷したい」なら INNER JOIN で住所欄が空でない行に絞る、 という具合に 欠落をどう扱うか で how を選ぶ。

比喩 2: 会員 ID と購買履歴 — 1:N の典型

ECサイトの「会員マスタ」（1 人 1 行、 100 万人）と「購買履歴」（1 購買 1 行、 1 億件）の結合は典型的な 1:N。 結合キーは「会員 ID」。 会員 1 人につき購買が平均 100 件なので、 結果は購買履歴側の 1 億行が保たれ、 各行に会員属性（年齢・性別など）が貼り付く。 「アクセスがあったが買わなかった会員」を見たいなら、 アクセスログ × 購買履歴を OUTER JOIN して NULL を抽出する。 「会員数の集計」をしたければ nunique('会員ID') で重複を解いてから数える。

比喩 3: 都道府県と地方区分 — N:1 でラベル付与

SSDSE-B-2026 の 47 都道府県 × 12 年 = 564 行のパネルに、 「地方区分（北海道・東北・関東・…）」のラベルを付ける。 これが本記事で繰り返し使った N:1 パターン。 結合キーは「都道府県」（パネル側は many、 マスタ側は one）。 結合の結果、 地方別の人口推移グラフが描けるようになる。 比喩で言えば、 「個別の住所録」に「都道府県別のタグ」を一括で貼る作業。 タグ貼り後に集計すれば、 一気に「地方別ランキング」が手に入る。

比喩 4: 散乱したカードを 1 枚にまとめる「ホチキス」

結合は、 別々のカード（テーブル）を 共通のキー でホチキス止めし、 1 枚の大きなカード（分析テーブル）にする操作。 ホチキスの穴（キー）がズレていれば結合できない（NaN）。 ホチキスを打つ前に「穴の位置を揃える」のがキー正規化（NFKC, strip, lower）。 ホチキスを打った後に「ペアになっていないカード」を抜き出すのが OUTER JOIN + NULL フィルタ。

🔄 pandas と SQL を行き来する — 翻訳表で習得を加速

同じ結合処理を pandas と SQL で並べて見ると、 両者の対応がスッと頭に入る。 SSDSE-B-2026 の都道府県データを題材に、 8 個の典型的な結合パターンを 1 表に整理した。 業務上はチームの慣習に従えば良いが、 両方読めるとデータ基盤の議論で強い。

この対応表を 1 枚プリントしてエディタ脇に貼っておくと、 「あれ、 ANTI JOIN ってどう書くんだっけ?」 という瞬間に即引ける。 結合表現の翻訳力が付くと、 ETL ワークフローの設計が一気に楽になる。

特に SQL から pandas へ移行した直後は、 「USING がない」「ANTI JOIN が無いように見える」と戸惑うが、 実は pandas の indicator=True と query("_merge=='left_only'") の組合せで完全に再現できる。 逆に pandas から SQL へ移る人は、 merge_asof 相当が標準 SQL にはなく LATERAL や ウィンドウ関数で書き換える必要があることに注意。 「概念は同じ、 表現の語彙が違うだけ」と捉えれば、 翻訳作業は機械的になる。 結合 1 つを 5 通りで書けるようになると、 データチームの中でブリッジ役を果たせる人材になる。

📝 30 秒で振り返る — 結合の最重要ポイント 10 個

1. 結合は「キー + how + 2 表」の 3 点で決まる。 言語が変わっても本質は同じ。
2. how の選び方: 左を残すなら LEFT、 共通だけなら INNER、 両側全部なら FULL OUTER、 全組合せなら CROSS。
3. 右側キーの一意性を必ず事前確認（right['key'].is_unique）。 これが結合事故の最大原因。
4. キー正規化（NFKC + strip + lower）を結合前に両側に適用。 全角半角・空白の揺らぎを潰す。
5. validate= 引数で関係性（1:1, 1:N, N:1, N:N）をアサート。 意図と現実のズレを即発見。
6. 行数アサートを結合の前後に置く（assert len(out)==564）。 黙って壊れるのを防ぐ。
7. indicator 監査: 初回ロードは indicator=True でマスタ漏れを _merge 列から検知。
8. suffixes 命名: _x/_y でなく意味のある名前（_pop, _econ）を明示。
9. 結合キーは表示名でなくコード（県コード R01100 等）を使う。 安定性が桁違い。
10. 結合の本質は検証: 動かす 30% より検証 70% に時間を割くと、 下流の分析の信頼性が劇的に上がる。

🔑 結合キー設計の原則 — 安定性・一意性・最小性

結合の品質は キー設計 で 8 割決まる。 良いキーは「安定性（時間が経っても変わらない）」「一意性（同じ実体に同じキー）」「最小性（必要最小限の列で一意になる）」の 3 条件を満たす。 SSDSE-B-2026 の都道府県コード（R01100 等）はこの 3 条件を完璧に満たす好例。

SSDSE-B-2026 では 都道府県コード（R + 5 桁） がこの 3 条件を満たす理想的なキー。 表示用に「都道府県名」も併存させ、 結合時は 必ずコードを使う のがプロの選択。 仮に将来「埼玉県」が「大埼玉県」に改名されても、 R11000 というコードは変わらないため、 過去データとの結合が壊れない。 政府統計が必ずコードを併記している理由はここにある。 同様に、 市区町村レベルではJIS X 0402 の 5 桁コード、 法人レベルでは法人番号 13 桁、 個人レベルでは内部 ID（マイナンバーは個人情報として直接使うべきでない）と、 ドメイン毎の標準コードを採用するのが鉄則。 表示名と結合キーを分離する設計は、 長期運用に耐えるデータ基盤の必須要件。

📊 ケーススタディ — 高齢化率と医療施設数の地域差を結合で炙り出す

最後に、 結合の威力を実感できる小さなケーススタディを 1 つ紹介する。 ここまでの抽象的な議論を、 具体的な政策論議に接続する例として読んで欲しい。 SSDSE-B-2026 の 「高齢化率（65 歳以上人口比率, A1303 系）」 と 「医療施設数（J1101 系等）」 を都道府県 × 年度キーで結合し、 1 人当たり医療資源の地域差を可視化する。

問い: 高齢化率の高い県は医療施設が十分か?

「高齢化が進む県ほど医療資源が不足しているのではないか」 という仮説を、 SSDSE-B-2026 のデータで検証する。 検証のためには、 高齢化率と医療施設数の両方が 1 つの表にそろっている必要があり、 結合が必須。 仮に分析者が「高齢化率は人口表から、 施設数は医療表から」と別ファイルで持っているなら、 都道府県 × 年度をキーに INNER JOIN するのが最初のステップ。

🐍 結合 → 派生指標 → 地域差の可視化

🎯 このコードでやること: SSDSE-B-2026 の人口と一般診療所数を 1 表に結合し、 「人口千人あたりの診療所数（施設密度）」を計算。 高齢化率との関係を相関係数で見て、 仮説を検証する。

📥 入力データ: SSDSE-B-2026 から 2023 年の Prefecture、 A1101（総人口）、 A1303（65歳以上人口）、 I5102（一般診療所数）の実在 4 列を抽出。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

import pandas as pd
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
d23 = df[df['SSDSE-B-2026']==2023].copy()
# 高齢化率 = 65歳以上人口(A1303) ÷ 総人口(A1101) × 100
d23['高齢化率'] = d23['A1303'] / d23['A1101'] * 100
# 医療施設密度 = 一般診療所数(I5102) ÷ 総人口 × 1000（千人あたり）
d23['施設密度'] = d23['I5102'] / d23['A1101'] * 1000
sub = d23[['Prefecture','高齢化率','施設密度']].dropna()
print('相関 (高齢化率 × 施設密度):', sub['高齢化率'].corr(sub['施設密度']).round(3))
print(sub.nlargest(3,'高齢化率').round(2).to_string(index=False))
print(sub.nsmallest(3,'高齢化率').round(2).to_string(index=False))

📤 実行結果（SSDSE-B-2026 実測, 2023 年）:

💬 結果の読み方: 重要なのは、 結合した瞬間にこの議論が可能になった こと。 結合無しでは、 「高齢化率の県別ランキング」と「診療所数の県別ランキング」を別表で眺めるだけになり、 関係性を定量化できない。 高齢化率と施設密度を同じ表に並べて相関係数を 1 本引く ── これが結合の真価。

教訓

このケースは「結合 1 ステップで分析の幅が広がる」典型例。 元データはバラバラの列群に過ぎないが、 適切なキーで結合し、 派生指標を計算し、 相関を取るだけで、 政策議論の土台になる定量証拠が得られる。 結合は 分析の入り口 であり、 ここを丁寧にやれば下流の散布図・回帰・クラスタリングがすべて正しく動く。 逆にここで雑な結合（行数爆発・NaN 多発・キー不一致）をしてしまうと、 下流のすべての結論が信用できなくなる。

🎓 学習者・指導者のための教材化のヒント — 結合をどう教えるか

結合は概念的には単純だが、 学習者がつまずくポイントは決まっている。 教える側として用意すべき「教材の階段」を 5 段に分けると、 受講者の理解度が劇的に上がる。 SSDSE-B-2026 を題材にする場合の具体的なシナリオも併記した。

よくある質問への先回り回答

指導現場で受けやすい質問と、 30 秒で答えるための回答テンプレート。 「merge と join はどう違うんですか?」（→ merge はキー列、 join はインデックス、 普段は merge で良い）。 「LEFT と INNER どっちを使えば?」（→ 「右にないデータを残すか捨てるか」で決める）。 「行数が想定と違うのですが?」（→ まず right['key'].is_unique を確認、 重複があれば集約してから結合）。 「列名がぐちゃぐちゃに?」（→ suffixes で意味のある接尾辞を、 結合前に rename もアリ）。 これらに即答できれば、 学習者は安心して次のステップに進める。

教材を作る側へのメッセージ

結合は 「動かすことが目的ではなく、 動いた結果を信用することが目的」。 教材では「動かす方法」と同じくらい 「結果を検証する方法」 に時間を割いて欲しい。 行数アサート、 validate 引数、 indicator 監査、 isna チェック、 duplicated チェック、 これらを「結合の付帯作業」ではなく 「結合の本体」 として教えることで、 学習者は実務でも事故を起こさなくなる。 結合のスキルは「文法 30%、 検証 70%」が黄金比。

👥 チーム作業での結合 — レビュー観点・コミット粒度・命名規則

分析チームで結合コードをコミットする際、 レビュアーが見るべき点は以下に集約される。 自分でコードを書く時もこのチェックリストを意識すれば、 レビュー指摘ゼロでマージできる。

PR レビュー時の必須チェックリスト

コミット粒度のベストプラクティス

結合に関する変更は 「読み込み」「正規化」「結合」「派生指標」「保存」 の 5 段に分けてコミットすると、 後から diff を辿りやすい。 1 つの大きなコミットに全てを詰めると、 結合バグを git bisect で追えなくなる。 各段は 30〜100 行程度に収まる粒度が理想で、 PR 単位では 5 段全部 + テストを含めて 200〜400 行程度がレビューしやすい。 結合の前後で行数アサートを必ず入れ、 結合の意図と関係性を docstring に書く習慣を作る。

命名規則の例

DataFrame 名は df_pop_raw / df_pop_norm / df_joined / df_final のように 「ドメイン_状態」 形式で命名すると、 パイプラインの段階が一目で分かる。 結合キー列は pref_code (5 桁 JIS コード) と pref_name を両方持たせ、 表示は name で結合は code で行うとキー揺らぎ事故が激減する。 SSDSE-B-2026 の都道府県コード（Code 列: R01000=北海道, R47000=沖縄県）は安定キーとして秀逸で、 結合キーは 表示名ではなくコード優先 が鉄則。

🧩 結合パイプラインの設計パターン — Star / Snowflake / Bridge

中規模以上の分析プロジェクトでは、 結合は 「1 つの大きな分析テーブル」を組み立てるための継続的作業 になる。 良い設計を選ばないと、 結合の度に同じバグを踏み、 同じ修正に時間を浪費する。 ここでは BI / データウェアハウスの世界で確立した 3 つの設計パターンを、 SSDSE-B-2026 に当てはめて紹介する。

パターン 1: スタースキーマ (Star Schema)

中心に ファクト表（多数の数値、 1 行 1 観測） を置き、 周辺に ディメンション表（少行のマスタ、 属性を持つ） を配置する。 SSDSE-B-2026 で言えば、 ファクトは「都道府県 × 年度 × 各指標」、 ディメンションは「都道府県マスタ（地方区分・人口規模・気候）」「年度マスタ（元号・景気フェーズ）」など。 ファクトはディメンションキー（pref, year）でのみディメンションと結合し、 ディメンション同士は結合しない。 シンプルで OLAP クエリが高速、 BI ツールの推奨形。

パターン 2: スノーフレークスキーマ (Snowflake Schema)

スタースキーマのディメンションをさらに正規化（細かい階層に分解）し、 サブディメンションを結合する。 例えば「都道府県マスタ」を「都道府県 → 地方」「地方 → 地域ブロック（東日本/西日本）」と階層化する。 ストレージ効率は高いが、 ジョインの段数が増えてクエリが複雑化するため、 BI のドリルダウンが必要な場面で採用する。 SSDSE-B-2026 のような小規模データではメリットが少ないが、 ヘルスケアや EC のような巨大マスタには有効。

パターン 3: ブリッジテーブル (Bridge Table)

N:N の関係を 1:N + N:1 に分解する中間表。 「県 × 産業」のように両側多重ならブリッジを挟む。 たとえば「県」と「産業分類」が N:N（1 県に複数産業、 1 産業は複数県）なら、 ブリッジ表「県 × 産業 × 値」を挟むことで、 個別の結合を 1:N と N:1 の安全な組合せに分解できる。 N:N 直接結合は 絶対禁止（行数爆発・重複・意味不明な集計）の前提で、 ブリッジ表を最初から設計しておくのが鉄則。

設計パターン選択の意思決定表

SSDSE-B-2026 のレベルなら スタースキーマ + 1 ファイル平準化 で十分。 ファクトは 564 行のパネル（県 × 年度）、 ディメンションは 47 行の県マスタと 12 行の年度マスタ。 これら 3 表を 2 回の LEFT JOIN で結合した結果を CSV に書き出せば、 後段の分析は単一ファイルだけで完結する。 設計が単純であるほど、 結合のバグは出にくく、 修正コストも低い。

🌍 言語横断 — SQL / pandas / R dplyr / Polars / SAS で同じ JOIN を書く

「同じ JOIN を別の言語ではどう書くか」を一覧化すると、 言語に依存せず JOIN の本質（キー × how × 列） がぶれずに伝わる。 SSDSE-B-2026 の人口 (p) と地方マスタ (r) を都道府県でキーに LEFT JOIN する例を、 5 種の言語で並べる。

どの言語でも 「結合キー」「結合方式 (how)」「結合する 2 テーブル」 の 3 点を指定する形は不変。 言語間の移植は機械的にできるので、 まずは pandas で本質を掴み、 業務要件に応じて Polars / DuckDB / Spark に切り替えていくと習得が早い。 SQL を 1 つでも書けるなら、 他の言語の JOIN は 1 時間で身につく。

🐍 同一データで pandas vs Polars vs DuckDB 速度比較

🎯 このコードでやること: SSDSE-B-2026 の 564 行（47 県 × 12 年）を題材に、 pandas / Polars / DuckDB の 3 つの LEFT JOIN を同条件で測る。 行数が小さいので絶対差は微小だが、 同じ書き味で並ぶことを確認する。

📥 入力データ: 564 行のパネル + 47 行の県マスタ。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

import pandas as pd, time
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
panel = df[['Prefecture','SSDSE-B-2026','A1101']]
master = df[df['SSDSE-B-2026']==2023][['Prefecture']].copy(); master['region']='X'
t0=time.perf_counter(); m_pd = panel.merge(master, on='Prefecture', how='left'); t1=time.perf_counter()
print(f'pandas: {(t1-t0)*1000:.3f} ms, 行={len(m_pd)}')
# Polars / DuckDB がインストールされていればここで比較
# import polars as pl; pp=pl.from_pandas(panel); pm=pl.from_pandas(master)
# t2=time.perf_counter(); m_pl = pp.join(pm, on='Prefecture', how='left'); t3=time.perf_counter()
# print(f'polars: {(t3-t2)*1000:.3f} ms')

📤 実行例:

🕐 この数値も実行のたびに変わります（0.7〜1.5 ms 程度を行き来します）。 564 行の JOIN はミリ秒未満で終わるので、 計測値の大半は測定そのもののオーバーヘッドです。

💬 結果の読み方: 47 県 × 12 年程度なら pandas で 1 ms 級。 Polars は同条件で半分程度、 DuckDB は最適化次第で同等以上。 規模が 1 億行を超えるなら DuckDB / Polars / Spark に切り替えるのが定石。 ライブラリの違いは「速度と並列性」であり、 JOIN の意味論は全言語で同一 という安心感が大事。

🧭 JOIN の歴史と思想 — リレーショナルモデルから DataFrame まで

テーブル結合の概念は 1970 年の E.F. Codd 論文「A Relational Model of Data for Large Shared Data Banks」 で正式に登場した。 それ以前のデータベースは木構造（IMS）やネットワーク構造（CODASYL）で、 リンクを物理ポインタで持ち、 「予め決められた経路」でしか辿れなかった。 Codd は 「データを表（relation）で持ち、 任意の組合せを結合で計算する」 という宣言的アプローチを提唱した。 これが今日 SQL の JOIN として現役で動いている。

関係代数における 5 演算 + JOIN の位置づけ

数学的には $R \bowtie_\theta S = \sigma_\theta (R \times S)$、 つまり JOIN は「直積 + 条件選択」の合成。 INNER JOIN は条件 $\theta$ が「両側のキーが等しい」場合の特例（等結合, equijoin）に過ぎない。 この視点を持てば、 結合の本質は 「全組合せの中から条件に合うものを残す」 操作であり、 計算量の悪化（$O(NM)$ になりがち）も自然に理解できる。

時系列で見る JOIN 実装の進化

この 50 年の進化を見ると、 「JOIN の概念は変わらないが、 実装は計算機性能とともに加速度的に拡張された」 ことが分かる。 我々が df.merge(...) と書く 1 行の背後には、 1970 年の関係代数の論文と、 50 年分の最適化技術の蓄積がある。 この歴史を踏まえると、 「結合は単なる pandas 操作」ではなく、 計算機科学の中核概念 として尊重して扱う気持ちが芽生える。

❓ よくある質問 (Deep FAQ) — 結合に関する迷いを 8 個解消

Q. merge と join と concat はどう違う?

A. merge はキー列で結合（SQL JOIN 相当）、 join はインデックスで結合（merge の薄いラッパ）、 concat は行 or 列を 単に積み上げる（UNION ALL / 横貼り）。 結合の意図が「キー一致」なら merge、 「縦に積む」なら concat、 と覚えれば迷わない。

Q. SQL の USING と ON の違いは?

A. USING (key) は両側に同名のキー列があるときの省略記法で、 結果に キー列が 1 つしか残らない。 ON p.key = r.key は別名キーでも使えるが、 結果にキー列が両方残る（p.key と r.key）。 USING は select * 時にスッキリするので、 同名キーなら使うべき。

Q. 結合キーが NULL / NaN の行はどう扱われる?

A. 標準 SQL では NULL = NULL は FALSE として扱われ、 NULL 行同士は結合しない。 ただし pandas の merge はデフォルトで NaN 同士をマッチさせてしまう動作の差がある（バージョンによる）。 安全策は結合前に dropna(subset=['key'])。

Q. 大規模データで結合が遅い時、 何を最適化する?

A. 優先順位は (1) キーにインデックスを張る、 (2) 右側を小さくする（事前フィルタ）、 (3) 結合キーの型を揃える（特に object vs category）、 (4) パーティション並列（dask, polars, duckdb）、 (5) 事前ソート（Sort-Merge Join を狙う）。 最初の 3 つで 90% の問題は解決する。

Q. 同じ結合を pandas / SQL / dplyr で書き分けた時の対応は?

A. 概念は同じ。 pandas df.merge(other, on='k', how='left') = SQL SELECT * FROM df LEFT JOIN other USING(k) = R dplyr left_join(df, other, by='k')。 言語が変わってもキー・how・列指定の 3 点を押さえれば、 移植は機械的にできる。

Q. 集約してから結合 vs 結合してから集約、 どちらが速い?

A. 原則は 「先に集約して小さくしてから結合」。 結合は計算量と中間メモリが大きくなりがちなので、 右側を groupby + agg で 1/100 に縮めてから結合すれば、 速度・メモリとも有利。 ただし「行単位の細かい条件で結合してから集約したい」場合はこの限りではない。

Q. 結合後の重複列に _x _y が付くのが気持ち悪い、 どうすれば良い?

A. suffixes=('_pop','_econ') のように 意味のある接尾辞 を明示する。 さらに結合前に rename(columns=...) で列名衝突を回避すると、 結合後のコードがクリーンになる。 列名は分析の生命線なので、 明示で揃えるのが定石。

Q. パネルデータの結合で時点ずれ（lag）を入れたい時はどうする?

A. pandas.merge_asof または shift を使う。 merge_asof(left, right, on='date', by='pref', direction='backward') なら「直近過去」の値を結合できる。 因果効果の推定では「介入直前の値」を結合する場面で多用される。

🗺 概念マップ

表の操作
├─ 縦方向 (concat / UNION)
└─ 横方向 (JOIN) ★
    ├─ INNER  : 共通だけ
    ├─ LEFT   : 左を残す
    ├─ RIGHT  : 右を残す
    ├─ OUTER  : 両方
    ├─ CROSS  : デカルト積
    ├─ SEMI   : 左の絞り込み
    ├─ ANTI   : 左の不在
    └─ ASOF   : 時系列近傍

表の結合 ★
├─ アルゴリズム: nested loop / hash / sort-merge / broadcast
├─ キー設計: 単一 / 複合 / 代理キー / 自然キー
└─ 落とし穴: 型不一致 / 重複 / NULL / 文字ゆらぎ

🔗 隣接手法への橋渡し

テーブル結合 (JOIN) は関係代数の中核操作。 SSDSE-B-2026 (47 都道府県 × 110 指標) と SSDSE-A (家計調査) を都道府県コード (JIS X 0401: 01=北海道, 13=東京 …) で結合し、 「家計支出と地域経済指標の関係」を分析する典型ワークフロー。 SQL JOIN は INNER/LEFT/RIGHT/FULL/CROSS の 5 種、 pandas は pd.merge(how=...) で同等表現。

• 上流: 主キー (一意な識別子)、 外部キー (参照整合性)、 データモデリング (ER 図)、 結合キーの型一致確認 (string vs int)。
• 並列: UNION (縦結合)、 CROSS JOIN (直積)、 ANTI JOIN (差集合)、 ウィンドウ関数 による別行参照。
• 下流: 結合後の 集計 (GROUP BY)、 ピボット、 回帰モデル への投入、 BI ダッシュボード。

INNER JOIN は両側に存在するキーのみ残す (47→47 件)、 LEFT JOIN は左側を全保持 (47 件保証)、 FULL OUTER は両側全保持 (NaN 含む)。 SSDSE 結合では LEFT JOIN を基本に、 欠損都道府県を NaN で残す設計が一般的。

🌳 手法選択フロー

SSDSE-B-2026 を他データと結合する際の JOIN 種類選択フロー。

1. ① 両側に必要なキーが揃っているか? 揃っている → INNER JOIN (両側存在のみ)。 SSDSE-B 47 件 ⨝ SSDSE-A 47 件 → 47 件で完全結合。
2. ② どちらかを優先したいか? SSDSE-B を主表として全 47 都道府県を保持したい → LEFT JOIN (SSDSE-B 主)。 結合先に欠損がある都道府県は NaN で残る。
3. ③ 主キーは一意か? 一意でない (例: 都道府県 × 年で多重) → 結合キーに (地域コード, 年) 複合キーを使用。 単一キーでは行が爆発する (N×M)。

JOIN 前に必ず両側で df.drop_duplicates(subset=key).shape をチェック。 SSDSE 結合の典型エラーは「同一都道府県が複数年含まれる」「コード型 (string vs int) の不一致」「市区町村単位への変換ミス」。

🧩 深掘り — 結合は「行数」も「合計」も保存しない

inner-join / outer-join のページが「どのキーが残るか」を扱うのに対し、 ここでは 結合の前後で行数と集計値がどう変化するか という別角度に絞る。 JOIN は 変換 であって恒等写像ではない。 だから結合したら必ず「行数」と「合計」を検算する。 数値はすべて SSDSE-B-2026.csv（2023 年・47 都道府県・列 A1101 総人口）の実測値。

🧭 直感

結合のカーディナリティ（1対1 / 1対多 / 多対多）を、左右それぞれのキー重複数 n_L,k・n_R,k で見ると行数が一発で読める。 キー k ごとに 左の該当行数 × 右の該当行数 だけ結果行が生まれ、それを全キーで足したものが結果の総行数になる（inner の場合）。

• 1対1（両側キー一意）: 各キーで 1×1=1 行。行数は変わらない。
• 1対多: 片側が 1、もう片側が m。行数は増えないが、 1 側の値が m 回コピーされるため単純合計が壊れる。
• 多対多: 両側に重複キー。行数が Σ(n²) 的に爆発する。

⚠️ 落とし穴（重要）

「結合しても合計は変わらないはず」という思い込みが最大の罠。 SSDSE-B-2026（2023 年）の 47 都道府県・総人口 A1101 で 3 通りの結合を実測すると次のとおり（すべて実データ）。 基準となる全国総人口は 124,353,000 人（47 県の A1101 合計）。

対策は 2 つの不変量を必ず印字すること。 ① 主表の行数が結合後も変わらないか（left.shape[0] == merged.shape[0]、 many_to_one を期待するなら validate='m:1'）。 ② 意味のある合計（人口・件数など）が結合前後で保存されるか。 保存されない結合は「集計をコピーで水増ししていないか」を疑う。

🚀 発展

• validate 引数で自動検算。 df.merge(m, on='region', how='left', validate='m:1') のように pandas に想定カーディナリティを宣言すると、 右表のキーが一意でない（=想定外の 1対多）瞬間に MergeError で止まる。 行数爆発を「事故が起きてから気づく」のではなく「起こす前に落とす」。
• 集計は結合の前か後か。 「地方人口」を県に足したいだけなら、貼り戻して合計せず、 貼り戻した列は参照専用と割り切る。 逆に地方単位の分析がしたいなら、県で結合してから groupby('region').sum() で正しい粒度に戻す。 「どの粒度で合計が意味を持つか」を先に決めるとバグらない。
• 多対多を避ける複合キー。 都道府県×年のパネルなら結合キーを (Code, 年) の複合にすれば、 単一キー結合で起きる年またぎの直積を防げる。 本ページ上部の「手法選択フロー」と対応。
• 合成の断り書き。 上の region（8 地方）区分は分析用に付与した（地方区分は解析上の便宜的グルーピング）もので、 A1101 の人口値そのものは SSDSE-B-2026 の実測値である。

🔗 関連ページ

• 内部結合（inner join） — どのキーが残るか / 両側に揃う行だけ。
• 外部結合（outer join） — 片側に無いキーを NULL で保持する挙動。
• データ結合（data join） — 結合全般の入口。
• グループ集計（groupby） — 1対多の水増しを正す「粒度を戻す」操作。
• 集約（aggregation） — 合計・平均が保存される条件。
• 主キー（primary key） — キー一意性が 1対1 / 1対多を決める。