【Python in Excel】DataFrameを結合する方法（merge）

Excelの vlookup関数やSQL文の join句のように、Python in Excel でも pandas のオブジェクト DataFrame を使用して、表・テーブルの結合が可能です。この記事では merge メソッドを使った結合方法について紹介します。

2023年10月現在、Python in Excel は Microsoft 365 Insider Program のベータチャネルを選択することによって使用できるベータ版のExcelでのみ利用可能です。
ベータ版のExcelのインストール・初期設定方法は以下で詳しく紹介しています。

Python in Excel が使えるExcelをインストール・初期設定する方法（画像付き）

［Python in Excel］が使えるベータ版Excelをインストールする方法、Microsoft 365 Insider プログラムの参加方法、Python in Excel で Hello World! を出力する方法をご紹介します

サンプルデータ
merge メソッドの構文
サンプルコード
参考

サンプルデータ

Anaconda の公式サイトにあるサンプルデータを使用して説明します。以下のページ内の「download here」のリンクからダウンロードできます。

Python for Excel Analysts: Combining Tables | Anaconda

This is the final blog post in a series that teaches you how to work with tables of data using Python code. The subject of this post is a common operation used ...

以下のテーブルが定義されています。

「InternetSales」テーブル… 売上データ 60,398件
「ProductInfo」テーブル… 製品マスタ 197件

両テーブルに「ProductKey」列があり、結合のキーに使えそうです。但し「ProductInfo」テーブルには「InternetSales」テーブル内の一部の「ProductKey」は含まれていません。

merge メソッドの構文

DataFrame.merge(right [, 任意引数])

戻り値

DataFrame

引数	型	説明
right (必須)	object	マージするDataFrameまたはSeries
how	string	選択肢：・”left”（左外部結合）・”right”（右外部結合）・”outer”（完全外部結合）・”inner”（内部結合）・”cross”（交差結合）デフォルト：”inner”
on	str or list	結合する列またはインデックスのレベル名指定しない場合は交差結合になる
left_on	str or list	左側のDataFrameで結合する列またはインデックスレベルの名前
right_on	str or list	右側のDataFrameで結合する列またはインデックスレベルの名前
left_index	bool	左側のDataFrameのインデックスを結合キーとして使用するか否かデフォルト：False
right_index	bool	右側のDataFrameのインデックスを結合キーとして使用するか否かデフォルト：False
sort	bool	True … 結果のDataFrameで結合キーを辞書順に並べ替える False … 結合キーの順序は結合タイプ (how引数) によって決まるデフォルト：False
suffixes	list	左側と右側のDataFrameに重複する列名がある場合に列名に追加する接尾辞長さが２のシーケンス（タプルやリスト）を指定するデフォルト：(“_x”, “_y”)
copy	bool	Falseの場合、可能であればコピーを避けるデフォルト：True
indicator	bool or str	各行の結合結果を表示する「_merge」列を出力するか否か出力内容は以下の通り・「left_only」…左のDataFrameのみに存在・「right_only」…右のDataFrameのみに存在・「both」…左右のDataFrameに存在デフォルト：False ※文字列を渡した場合はその値が列名となる
validate	str	マージキーが一意であるかチェックする（チェックに引っ掛かった場合はエラーが返る）選択肢：・「one_to_one」or「1:1」…マージキーが左右両方で一意・「one_to_many」or「1:m」…マージキーが左DataFrameで一意・「many_to_one」or「m:1」…マージキーが右DataFrameで一意・「many_to_many」or「m:m」…チェックは行われない

mergeメソッドの引数一覧（「引数」列に「必須」の記載がないものはすべて任意引数）

pandas.DataFrame.merge — pandas 2.1.3 documentation

サンプルコード

前準備

サンプルデータのブックにシートを追加し、A1セルに以下のコードを入力します。

# A1セル　トランザクションテーブル（一部の列のみ取得）
sales = xl("InternetSales[#すべて]", headers = True)
sales = sales.reindex(columns=["SalesOrderNumber","SalesOrderLineNumber","OrderDate","ProductKey","OrderQuantity"])

# A2セル　マスタ（一部の列のみ取得）
products = xl("ProductInfo[#すべて]", headers = True)
products = products.reindex(columns=["ProductKey", "ProductName"])

売上データ sales は 60398件、製品マスタ products は 197件です。列は適当に絞り込みました。
製品マスタは全ての ProductKey は網羅していません。また、製品マスタの197件中102件（製品）は売上データがありますが、残りの95件はありません。（売れてないってことです。）

Pythonの入力方法や出力種類、DataFrameの定義方法については、以下の記事で詳しく紹介しています。

【超基本】Python in Excel の使い方とルール

Python in Excel の入力方法は難しくはありませんが、Excelのワークシート関数の入力の仕方とは少し勝手が違います。この記事では、Python in Excel の入力の仕方と出力の種類、計算順序やパフォーマンスを向上させるた...

[DataFrame] xl関数の指定方法いろいろ

この記事では、DataFrameの定義、xl関数の使い方や注意点について説明します。DataFrameを使うと、Excelのセル範囲や名前、テーブル、Power Queryのデータを Python in Excel で扱えるようになります。...

結合の種類（how, on）

結合の種類とは、SQL文でいうところの join 句の種類と同じです。
以降の説明では、「左」はmergeメソッドを呼び出すオブジェクトを意味し、「右」はmergeメソッドの第１引数 right に指定するオブジェクトを意味します。

【SQL】テーブル結合の全パターンを総まとめ！初心者向けに解説

SQLでSELECT文を書いていると、複数のテーブルをがっちゃんこしたい！と思う時があるでしょう。これをSQL…

サンプルデータは以下のイメージです。

inner, left, right, outer

# A3セル
df1 = sales.merge(products, on="ProductKey", how="inner") # innerはデフォルト
# A4セル
df2 = sales.merge(products, on="ProductKey", how="left")
# A5セル
df3 = sales.merge(products, on="ProductKey", how="right")
# A6セル
df4 = sales.merge(products, on="ProductKey", how="outer")

結果　内部結合（左右のdataframeで結合キーが一致したデータ）54970件

mergeメソッド how="inner" の処理結果 — how=”inner” の処理結果

結果　左外部結合（左の全てのデータと、左の結合キーと一致した右のデータ）60398件（＝54970+5428）

mergeメソッド how="left" の処理結果 — how=”left” の処理結果

結果　右外部結合（右の全てのデータと、右の結合キーと一致した左のデータ）55065件（＝54970+95）

mergeメソッド how="right" の処理結果 — how=”right” の処理結果

結果　完全外部結合（左右の全てのデータ）60493件（＝5428+54970+95）

mergeメソッド how="outer" の処理結果 — how=”outer” の処理結果

cross

前準備セクションで定義したDataFrame（60398件）を使用すると、結果が返って来ずの「#BUSY!」のままで変わらず。この場合リセットすると良いらしいのですが、リセットしても変わらず。（エラーコードの説明ページ参照）
11,898,406件（＝60398＊197）の結果が返るはずですが、最終的には「#CALC!」エラーになったので件数が多すぎのようです。

#BUSY! って出ちゃう

#CALC! エラー

なので、cross集計だけデータ量を減らしてサンプルを作成しました。

# A7セル　トランザクションテーブル（3397件）
sales2012 = xl("InternetSales[#すべて]", headers = True)
sales2012["OrderYear"] = sales2012["OrderDate"].dt.year
sales2012 = sales2012.query("OrderYear == 2012") # 2012年のデータのみ抽出
sales2012 = sales2012.reindex(columns=["SalesOrderNumber","SalesOrderLineNumber","OrderDate","ProductKey","OrderQuantity"])

# B7セル　マスタ（197件）
products2012 = xl("ProductInfo[#すべて]", headers = True)
products2012 = products2012.reindex(columns=["ProductKey", "ProductName"])

# C7セル
df5 = sales2012.merge(products2012, how="cross")

結果　クロス結合 669,209件（＝3397＊197）

mergeメソッド how="cross" の処理結果 — how=”cross” の処理結果

結合キーの指定

前セクションでは、引数 on に結合キーの列名を指定しました。左右のDataFrameで結合キーの名称が同じ場合は on を使用します。左右のDataFrameで結合キーの名称が異なる場合は、left_on, right_on, left_index, right_index を使用します。

left_on, right_on

# A9セル（製品マスタの「ProductKey」列を「ProductId」列にリネーム）
products2 = products.rename(columns={"ProductKey": "ProductId"})
# A10セル
df6 = sales.merge(products2, left_on="ProductKey", right_on="ProductId")

結果

mergeメソッド left_on, right_on 使用例 — left_on, right_on 使用例

left_index, right_index

# A12セル（ProductId列をインデックスに変更する）
products3 = products2.set_index("ProductId")
# A13セル
# right_index=True を指定すると、右側のdataframeのインデックスと結合される
df7 = sales.merge(products3, left_on="ProductKey", right_index=True)
# A14セル
# right_onにインデックス名を指定してもよい（結果は↑と同じ）
df8 = sales.merge(products3, left_on="ProductKey", right_on="ProductId")

結果

mergeメソッド left_on, right_on, right_index 使用例 — left_on, right_on, right_index 使用例

結合キーが複数ある場合

# A16セル
products4 = products3.copy() 
products4.insert(1, "OrderQuantity", 1) # 列を追加
products4
# A17セル
# 結合キーが複数ある時はリストで渡す
df9 = sales.merge(products4, left_on=["ProductKey", "OrderQuantity"], right_on=["ProductId", "OrderQuantity"])

結果

mergeメソッド left_on, right_on にリストを渡した例 — left_on, right_on にリストを渡した例

並び順（sort）

# A19セル
df10 = sales.merge(products, on="ProductKey", sort=True)

結果　結合キーで並び変わる

mergeメソッド sort=True の処理結果 — sort=True の処理結果

重複する列名の接尾辞（suffixes）

# A21セル
products5 = products.reindex(columns=["ProductKey", "OrderQuantity", "ProductName"], fill_value=1)
# A22セル
# 引数suffixes未指定　重複する列名に「_x」「_y」のサフィックスが付く
df11 = sales.merge(products5, on="ProductKey")
# A23セル
# 引数suffixesを指定　重複する列名に「_left」「_right」のサフィックスが付く
df12 = sales.merge(products5, on="ProductKey", suffixes=['_left', '_right'])

結果

mergeメソッド suffixes を指定しない例 — suffixes を指定しない例

mergeメソッド suffixes を指定した例 — suffixes を指定した例

結合結果の表示（indicator）

# A27セル
df14 = sales.merge(products, on="ProductKey", how="outer", indicator=True)
# A28セル
df19 = sales.merge(products, on="ProductKey", how="outer", indicator="インジケータ")

結果　indicator を指定すると結合結果の列が表示される。True を指定すると「_merge」、文字列を指定するとその文字列が列名になる。

mergeメソッド indicator=True の処理結果 — indicator=True の処理結果

mergeメソッド indicator に文字列を指定した例 — indicator に文字列を指定した例

一意チェック（validate）

引数 validate を指定すると、結合キーが一意であるかをチェックし、チェックに引っ掛かった場合はエラーが返ってきます。

# A30セル（sales, productsの一意チェック　エラーになるはず）
df15 = sales.merge(products, on="ProductKey", validate="1:1") # "one_to_one"でもOK
# A31セル（salesの一意チェック　エラーになるはず）
df16 = sales.merge(products, on="ProductKey", validate="1:m") # "one_to_many"でもOK
# A32セル（productsの一意チェック　エラーにならない）
df17 = sales.merge(products, on="ProductKey", validate="m:1") # "many_to_one"でもOK
# A33セル（左右どちらもチェックしない）
df18 = sales.merge(products, on="ProductKey", validate="m:m") # "many_to_many"でもOK

結果　A30、A31セルはエラーになる