【Python in Excel】DataFrameを結合する方法(join)

サンプルデータ DataFrame
サンプルデータ
この記事は約12分で読めます。

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、リストも可
onstr
or list
呼出元の列またはインデックスレベル名を引数otherのインデックスに結合する
未指定の場合は、インデックスとインデックスで結合する
howstr選択肢:
・”left”(左外部結合)
・”right”(右外部結合)
・”outer”(完全外部結合)
・”inner”(内部結合)
・”cross”(交差結合)
デフォルト:”left”
lsuffixstr左側と右側のDataFrameに重複する列名がある場合、
左側の列名に追加する接尾辞
デフォルト:””
rsuffixstr左側と右側のDataFrameに重複する列名がある場合、
右側の列名に追加する接尾辞
デフォルト:””
sortboolTrue … 結果のDataFrameで結合キーを辞書順に並べ替える
False … 結合キーの順序は結合タイプ (how引数) によって決まる
デフォルト:False
validatestrマージキーが一意であるかチェックする
(チェックに引っ掛かった場合はエラーが返る)
選択肢:
・「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」…チェックは行われない
joinメソッドの引数一覧(「引数」列に「必須」の記載がないものはすべて任意引数)

サンプルコード

前準備

サンプルデータのブックにシートを追加し、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件はありません。(売れてないってことです。)

売上データのdataframe
売上データのdataframe
製品マスタのdataframe
製品マスタのdataframe

結合の種類(how)

結合の種類とは、SQL文でいうところの join 句の種類と同じです。
以降の説明では、「左」は join メソッドを呼び出すオブジェクトを意味し、「右」は join メソッドの第1引数 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件

joinメソッド how="inner" の例
how=”inner” の例

結果 左外部結合(左の全てのデータと、左のインデックスと一致した右のデータ)60398件(=54970+5428)

joinメソッド how="left" の例
how=”left” の例

結果 右外部結合(右の全てのデータと、右のインデックスと一致した左のデータ)55065件(=54970+95)

joinメソッド how="right" の例
how=”right” の例

結果 完全外部結合(左右の全てのデータ)60493件(=5428+54970+95)

joinメソッド how="outer" の例
how=”outer” の例

結果 クロス結合 11,898,406件(=60398*197)

joinメソッド how="cross" の例
how=”cross” の例

結合キーの指定(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") # 結合される

結果

売上データ(インデックス無)
売上データ(インデックス無)
joinメソッド onを指定しない場合の例
onを指定しない場合の例 →結合されない
joinメソッド onを指定した例
onを指定した例 →結合される

複数のDataFrameを指定

複数のDataFrameを結合する場合は、第1引数にリストで指定する。

# 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") # 第1引数にリストを指定

結果 products、categories が sales に結合される

カテゴリーマスタ
カテゴリーマスタ風
joinメソッド 第1引数にリストを渡した例
第1引数にリストを渡した例

並び順(sort)

# A19セル
df10 = sales2.join(products, how="left", on="ProductKey", sort=True)
# A13セル
df7  = sales2.join(products, how="left", on="ProductKey") # 比較対象

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

joinメソッド sort=True の例
sort=True の例
joinメソッド sortを指定しない例
sortを指定しない例

重複する列名の接尾辞(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」列を追加
製品マスタに「OrderQuantity」列を追加

結果 「OrderQuantity」列が左右のDataFrameにあるため、lsuffix, rsuffixを指定しない場合、エラーとなる。

joinメソッド lsuffix, rsuffix を指定しない例
lsuffix, rsuffix を指定しない例
joinメソッド lsuffix, rsuffix を指定しない例(エラーメッセージ)
lsuffix, rsuffix を指定しない例(エラーメッセージ)

結果 lsuffix, rsuffixを指定した場合、列名に指定のサフィックスがつく。

joinメソッド 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セルはエラーになる

joinメソッド validate の例(チェックNG)
validate の例(チェックNG)
joinメソッド validate の例(チェックNGのメッセージ)
validate の例(チェックNGのメッセージ)

結果 A32、A33セルはエラーにならず結合結果が返る

joinメソッド validate="m:1"の例
validate=”m:1″の例

join メソッドの注意点

列の結合キーを使えるのは左側DataFrameのみ

左側のDataFrameのみ結合キーに列を使用できます。この場合引数 on は必須です(指定しないとおかしな結果になる)。
右側のDataFrameの結合キーは常にインデックスである必要があります(列は不可)。
参照:結合キーの指定(on)

第1引数にリストを指定した時の制限

第1引数にリストを指定した時は、引数 on, lsuffix, rsuffix を指定するとエラーになります。

# 第1引数にリスト、onを指定 ⇒ エラーになる
df9 = sales.join([products, categories], how="inner", on="ProductKey")
joinメソッド 第1引数がリストの場合onを指定するとエラーになる
第1引数がリストの場合onを指定するとエラーになる

join メソッドと merge メソッドの違い

merge メソッドの結合キーには列とインデックスを指定できるのに対して、join メソッドは結合キーに列を使用できるのは左DataFrameのみです。join メソッドはインデックス同士で結合する時に適しています。
SQL文のように列同士を結合したいケースでは merge メソッド、インデックスがセットされているDataFrameを結合するケースでは join メソッド、が使いやすいかと思います。

引数 how は両メソッドにありますが、交差結合(how=”cross”)で戻り値の件数が1000万件を超えるケースで、join メソッドでは正しい結果が返ってきましたが、merge メソッドでは結果が返ってこずでエラーになりました。件数が多い時は join メソッドの方が効率が良さそうです

参考

コメント

タイトルとURLをコピーしました