Python

Pythonで複数のデータを結合する方法【pandasのmerge・concat・VLOOKUPの代わり】

酒井 裕麻株式会社Bistro 代表取締役社長

「注文データに、顧客マスタの名前や会員ランクを付けたい」——ExcelならVLOOKUPを使う場面です。Pythonでは、pandasの merge() を使うと、何万行のデータでも一瞬で結合できます。

この記事では、Google ColabのPython(pandas)で、2つの表を結合する merge() と、表を縦に積み重ねる concat() の使い方を、実行結果付きで解説します。

同じことをRで行う方法は、Rで複数のデータを結合する方法で紹介しています。

この記事で使うデータ

注文データと顧客マスタの、2つの表を作ります。

import pandas as pd

orders = pd.DataFrame({
    "order_id": range(101, 107),
    "customer_id": ["C01", "C02", "C01", "C03", "C05", "C02"],
    "amount": [5200, 3100, 4800, 9900, 2500, 6100],
})
customers = pd.DataFrame({
    "customer_id": ["C01", "C02", "C03", "C04"],
    "name": ["佐藤", "鈴木", "高橋", "田中"],
    "rank": ["ゴールド", "一般", "シルバー", "一般"],
})
orders

実行結果

   order_id customer_id  amount
0       101         C01    5200
1       102         C02    3100
2       103         C01    4800
3       104         C03    9900
4       105         C05    2500
5       106         C02    6100
customers

実行結果

  customer_id name  rank
0         C01   佐藤  ゴールド
1         C02   鈴木    一般
2         C03   高橋  シルバー
3         C04   田中    一般

2つの表には、共通の列 customer_id(顧客ID)があります。この列をキーにして結合します。

注文には、顧客マスタにない「C05」があり、顧客マスタには、一度も注文していない「C04(田中)」がいます。この違いが、結合の種類によってどう扱われるかを見ていきます。

merge()の結合の種類

merge() は、how で結合の種類を指定します。

how 残る行 SQL・Rでいうと
"left" 左の表のすべての行 LEFT JOIN / left_join()
"inner"(初期設定) 両方の表にキーがある行だけ INNER JOIN / inner_join()
"outer" どちらかの表にキーがある行すべて FULL OUTER JOIN / full_join()
"right" 右の表のすべての行 RIGHT JOIN / right_join()

how=“left”:左の表に情報を足す

いちばんよく使うのが、how="left" です。左の表(注文)の行はすべて残し、右の表(顧客マスタ)の情報を足します。ExcelのVLOOKUPと同じ考え方です。

orders.merge(customers, on="customer_id", how="left")

実行結果

   order_id customer_id  amount name  rank
0       101         C01    5200   佐藤  ゴールド
1       102         C02    3100   鈴木    一般
2       103         C01    4800   佐藤  ゴールド
3       104         C03    9900   高橋  シルバー
4       105         C05    2500  NaN   NaN
5       106         C02    6100   鈴木    一般

6件の注文がすべて残り、名前と会員ランクが付きました。顧客マスタにない「C05」の注文は、名前とランクが NaN(欠損値)になっています。

how=“inner”:両方にあるものだけ

how="inner" は、両方の表にキーがある行だけを残します。how を省略すると、この結合になります。

orders.merge(customers, on="customer_id", how="inner")

実行結果

   order_id customer_id  amount name  rank
0       101         C01    5200   佐藤  ゴールド
1       102         C02    3100   鈴木    一般
2       103         C01    4800   佐藤  ゴールド
3       104         C03    9900   高橋  シルバー
4       106         C02    6100   鈴木    一般

「C05」の注文がなくなり、5件になりました。inner結合では、相手にないデータが黙って消えるので、件数が減っていないかを必ず確認しましょう。

how=“outer”:どちらかにあればすべて残す

how="outer" は、どちらかの表にキーがあれば、すべての行を残します。

orders.merge(customers, on="customer_id", how="outer")

実行結果

   order_id customer_id  amount name  rank
0     101.0         C01  5200.0   佐藤  ゴールド
1     103.0         C01  4800.0   佐藤  ゴールド
2     102.0         C02  3100.0   鈴木    一般
3     106.0         C02  6100.0   鈴木    一般
4     104.0         C03  9900.0   高橋  シルバー
5       NaN         C04     NaN   田中    一般
6     105.0         C05  2500.0  NaN   NaN

注文のない「C04(田中)」も、注文IDや金額が NaN の行として残りました。outer結合では行がキーの順に並び替わり、NaN が入った数値の列は、101.0のような小数の表示になります。

相手にないものを探す(indicator)

indicator=True を付けると、それぞれの行が、どちらの表にあったかを表す _merge という列が追加されます。

merged = orders.merge(customers, on="customer_id", how="outer", indicator=True)
merged[["order_id", "customer_id", "name", "_merge"]]

実行結果

   order_id customer_id name      _merge
0     101.0         C01   佐藤        both
1     103.0         C01   佐藤        both
2     102.0         C02   鈴木        both
3     106.0         C02   鈴木        both
4     104.0         C03   高橋        both
5       NaN         C04   田中  right_only
6     105.0         C05  NaN   left_only
_merge の値 意味
both 両方の表にある
left_only 左の表(注文)にだけある
right_only 右の表(顧客マスタ)にだけある

これを使うと、「相手にないもの」を探せます。Rの anti_join() にあたる使い方です。

一度も注文していない顧客

merged.query("_merge == 'right_only'")[["customer_id", "name", "rank"]]

実行結果

  customer_id name rank
5         C04   田中   一般

マスタに登録されていない顧客の注文

merged.query("_merge == 'left_only'")[["order_id", "customer_id", "amount"]]

実行結果

   order_id customer_id  amount
6     105.0         C05  2500.0

「マスタに登録漏れがないか」「一度も購入していない会員は誰か」といった確認に役立ちます。isin() を使って、orders[~orders["customer_id"].isin(customers["customer_id"])] と書くこともできます(~ は「〜ではない」という意味です)。

キーの列名が違うとき

2つの表で、キーの列名が違うことがあります。そのときは、left_on と right_on で、それぞれの列名を指定します。

master = pd.DataFrame({"cid": ["C01", "C02"], "area": ["東京", "神奈川"]})
orders.merge(master, left_on="customer_id", right_on="cid", how="left")

実行結果

   order_id customer_id  amount  cid area
0       101         C01    5200  C01   東京
1       102         C02    3100  C02  神奈川
2       103         C01    4800  C01   東京
3       104         C03    9900  NaN  NaN
4       105         C05    2500  NaN  NaN
5       106         C02    6100  C02  神奈川

右の表のキーの列(cid)も残るので、不要なら .drop(columns="cid") で削除します。

concat():表を縦に積み重ねる

月ごとに分かれたファイルをまとめるなど、同じ列を持つ表を縦に積み重ねるときは、pd.concat() を使います。

jan = pd.DataFrame({"month": "1月", "store": ["渋谷店", "横浜店"], "sales": [950, 720]})
feb = pd.DataFrame({"month": "2月", "store": ["渋谷店", "横浜店"], "sales": [880, 650]})
pd.concat([jan, feb], ignore_index=True)

実行結果

  month store  sales
0    1月   渋谷店    950
1    1月   横浜店    720
2    2月   渋谷店    880
3    2月   横浜店    650

ignore_index=True を付けると、行番号が0から振り直されます。付けないと、0, 1, 0, 1 のように、元の行番号が重複します。

フォルダにある複数のCSVをまとめて読み込んで積み重ねる、といった使い方もよくします。

import glob
files = sorted(glob.glob("/content/drive/MyDrive/売上/*.csv"))
all_sales = pd.concat([pd.read_csv(f) for f in files], ignore_index=True)

結合でよくある落とし穴

キーが重複していて、行が増える

顧客マスタに同じ顧客IDが2行あると、結合したときに注文が2倍に増えてしまいます。結合の前に、キーが重複していないかを確認しましょう。

customers["customer_id"].duplicated().sum()

実行結果

np.int64(0)

0なら重複はありません。validate="many_to_one" を指定すると、右の表のキーが重複しているときにエラーで知らせてくれます。

orders.merge(customers, on="customer_id", how="left", validate="many_to_one")

キーの型や表記が違う

「001」と「1」、「C01」と「C01 」(末尾にスペース)は、別のキーとして扱われ、結合されません。結合する前に、型をそろえる(astype(str))、余分なスペースを取る(str.strip())などで整えておきましょう。

結合の前後で件数を確認する

結合したら、件数が想定どおりかを必ず確認する習慣をつけましょう。

result = orders.merge(customers, on="customer_id", how="left")
print("結合前:", len(orders), "件 → 結合後:", len(result), "件")

実行結果

結合前: 6 件 → 結合後: 6 件

まとめ

  • 2つの表の結合は merge()。VLOOKUPの代わりには how="left"
  • how="inner" では、相手にないデータが消えるので注意
  • 相手にないものを探すには indicator=True と _merge の列
  • キーの列名が違うときは left_on と right_on
  • 表を縦に積み重ねるときは pd.concat()
  • 結合の前にキーの重複を、結合の後に件数を確認する

CONTACT

データ活用のお悩み、
直接相談しませんか。

「何から始めればいいかわからない」段階でも大丈夫です。課題の整理からお手伝いします。