Excelの vlookup関数やSQL文の join句のように、Python in Excel でも pandas のオブジェクト DataFrame を使用して、表・テーブルの結合が可能です。この記事では join メソッドを使った結合方法と注意点、merge メソッドとの違いについて紹介します。
サンプルデータ
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 メソッドの第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件
結果 左外部結合(左の全てのデータと、左のインデックスと一致した右のデータ)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を結合する場合は、第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 に結合される
並び順(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)
第1引数にリストを指定した時の制限
第1引数にリストを指定した時は、引数 on, lsuffix, rsuffix を指定するとエラーになります。
# 第1引数にリスト、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 メソッドの方が効率が良さそうです。
コメント