Excelの vlookup関数やSQL文の join句のように、Python in Excel でも pandas のオブジェクト DataFrame を使用して、表・テーブルの結合が可能です。この記事では join メソッドを使った結合方法と注意点、merge メソッドとの違いについて紹介します。
2023年10月現在、Python in Excel は Microsoft 365 Insider Program のベータチャネルを選択することによって使用できるベータ版のExcelでのみ利用可能です。
ベータ版のExcelのインストール・初期設定方法は以下で詳しく紹介しています。
サンプルデータ
Anaconda の公式サイトにあるサンプルデータを使用して説明します。以下のページ内の「download here」のリンクからダウンロードできます。
以下のテーブルが定義されています。
- 「InternetSales」テーブル… 売上データ 60,398件
- 「ProductInfo」テーブル… 製品マスタ 197件
両テーブルに「ProductKey」列があり、結合のキーに使えそうです。但し「ProductInfo」テーブルには「InternetSales」テーブル内の一部の「ProductKey」は含まれていません。
join メソッドの構文
DataFrame.join(other [, 任意引数])| 戻り値 | DataFrame |
| 引数 | 型 | 説明 |
|---|---|---|
| other (必須) | object or list | マージするDataFrameまたはSeries、リストも可 |
| on | str or list | 呼出元の列またはインデックスレベル名を引数otherのインデックスに結合する 未指定の場合は、インデックスとインデックスで結合する |
| how | str | 選択肢： ・”left”（左外部結合） ・”right”（右外部結合） ・”outer”（完全外部結合） ・”inner”（内部結合） ・”cross”（交差結合） デフォルト：”left” |
| lsuffix | str | 左側と右側のDataFrameに重複する列名がある場合、 左側の列名に追加する接尾辞 デフォルト：”” |
| rsuffix | str | 左側と右側のDataFrameに重複する列名がある場合、 右側の列名に追加する接尾辞 デフォルト：”” |
| sort | bool | True … 結果のDataFrameで結合キーを辞書順に並べ替える False … 結合キーの順序は結合タイプ (how引数) によって決まる デフォルト：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」…チェックは行われない |
サンプルコード
前準備
サンプルデータのブックにシートを追加し、A1セルに以下のコードを入力します。
# A1セル トランザクションテーブル（一部の列のみ取得）
sales = xl("InternetSales[#すべて]", headers = True)
sales = sales.reindex(columns=["SalesOrderNumber","SalesOrderLineNumber","OrderDate","ProductKey","OrderQuantity"])
sales = sales.set_index("ProductKey") # インデックスをセット
# A2セル マスタ（一部の列のみ取得）
products = xl("ProductInfo[#すべて]", headers = True)
products = products.reindex(columns=["ProductKey", "ProductName"])
products = products.set_index("ProductKey") # インデックスをセット売上データ sales は 60398件、製品マスタ products は 197件です。列は適当に絞り込み、「ProductKey」列をインデックスに変換しています。製品マスタは全ての ProductKey は網羅していません。また、製品マスタの197件中102件（製品）は売上データがありますが、残りの95件はありません。（売れてないってことです。）
Pythonの入力方法や出力種類、DataFrameの定義方法については、以下の記事で詳しく紹介しています。
結合の種類（how）
結合の種類とは、SQL文でいうところの join 句の種類と同じです。
以降の説明では、「左」は join メソッドを呼び出すオブジェクトを意味し、「右」は join メソッドの第１引数 other に指定するオブジェクトを意味します。
サンプルデータは以下のイメージです。
# A3セル
df1 = sales.join(products, how="inner")
# A4セル
df2 = sales.join(products, how="left") # leftはデフォルト
# A5セル
df3 = sales.join(products, how="right")
# A6セル
df4 = sales.join(products, how="outer")
# A7セル
df5 = sales.join(products, how="cross")結果 内部結合（左右のdataframeでインデックスが一致したデータ）54970件
結果 左外部結合（左の全てのデータと、左のインデックスと一致した右のデータ）60398件（＝54970+5428）
結果 右外部結合（右の全てのデータと、右のインデックスと一致した左のデータ）55065件（＝54970+95）
結果 完全外部結合（左右の全てのデータ）60493件（＝5428+54970+95）
結果 クロス結合 11,898,406件（＝60398＊197）
結合キーの指定（on）
左右のDataFrameをインデックスで結合する場合は引数 on を省略できますが、左DataFrameの結合キーがインデックスでない場合は、引数 on に結合キーの列名を指定する必要があります。
# A11セル（インデックスを使わない売上データ）
sales2 = xl("InternetSales[#すべて]", headers = True)
sales2 = sales2.reindex(columns=["SalesOrderNumber","SalesOrderLineNumber","OrderDate","ProductKey"])
# A12セル
df6 = sales2.join(products, how="left") # onがないので結合されない
# A13セル
df7 = sales2.join(products, how="left", on="ProductKey") # 結合される結果
複数のDataFrameを指定
複数のDataFrameを結合する場合は、第１引数にリストで指定する。
# A16セル（カテゴリーマスタ（ちょっと雑な））
products0 = xl("ProductInfo[#すべて]", headers = True)
categories= products0.reindex(columns=["ProductKey", "ProductCategoryName"])
categories = categories.set_index("ProductKey")
# A17セル
df9 = sales.join([products, categories], how="inner") # 第１引数にリストを指定結果 products、categories が sales に結合される
並び順（sort）
# A19セル
df10 = sales2.join(products, how="left", on="ProductKey", sort=True)
# A13セル
df7 = sales2.join(products, how="left", on="ProductKey") # 比較対象結果 結合キーで並び変わる
重複する列名の接尾辞（lsuffix, rsuffix）
# A21セル
products5 = products.reindex(columns=["OrderQuantity", "ProductName"], fill_value=1)
# A22セル（引数 lsuffix, rsuffix 未指定）
df11 = sales.join(products5)
# A23セル（引数 lsuffix, rsuffix を指定）
df12 = sales.join(products5, lsuffix="_l", rsuffix="_r")
結果 「OrderQuantity」列が左右のDataFrameにあるため、lsuffix, rsuffixを指定しない場合、エラーとなる。
結果 lsuffix, rsuffixを指定した場合、列名に指定のサフィックスがつく。
一意チェック（validate）
引数 validate を指定すると、結合キーが一意であるかをチェックし、チェックに引っ掛かった場合はエラーが返ってきます。
# A30セル（sales, productsの一意チェック エラーになるはず）
df15 = sales.join(products, validate="1:1") # "one_to_one"でもOK
# A31セル（salesの一意チェック エラーになるはず）
df16 = sales.join(products, validate="1:m") # "one_to_many"でもOK
# A32セル（productsの一意チェック エラーにならない）
df17 = sales.join(products, validate="m:1") # "many_to_one"でもOK
# A33セル（左右どちらもチェックしない）
df18 = sales.join(products, validate="m:m") # "many_to_many"でもOK結果 A30、A31セルはエラーになる
結果 A32、A33セルはエラーにならず結合結果が返る
join メソッドの注意点
列の結合キーを使えるのは左側DataFrameのみ
左側のDataFrameのみ結合キーに列を使用できます。この場合引数 on は必須です（指定しないとおかしな結果になる）。
右側のDataFrameの結合キーは常にインデックスである必要があります（列は不可）。
参照：結合キーの指定（on）
第１引数にリストを指定した時の制限
第１引数にリストを指定した時は、引数 on, lsuffix, rsuffix を指定するとエラーになります。
# 第１引数にリスト、onを指定 ⇒ エラーになる
df9 = sales.join([products, categories], how="inner", on="ProductKey")
join メソッドと merge メソッドの違い
merge メソッドの結合キーには列とインデックスを指定できるのに対して、join メソッドは結合キーに列を使用できるのは左DataFrameのみです。join メソッドはインデックス同士で結合する時に適しています。
SQL文のように列同士を結合したいケースでは merge メソッド、インデックスがセットされているDataFrameを結合するケースでは join メソッド、が使いやすいかと思います。
引数 how は両メソッドにありますが、交差結合（how=”cross”）で戻り値の件数が1000万件を超えるケースで、join メソッドでは正しい結果が返ってきましたが、merge メソッドでは結果が返ってこずでエラーになりました。件数が多い時は join メソッドの方が効率が良さそうです。
参考
コメント