はじめに

PandasでDataFrameを結合(Merge)するときに、マスタだと思っているデータ側が実は一意でなかったら、どうやって結合すればよいかについて考えてみます。

実際にやってみる

import numpy as np
import pandas as pd

トランザクションデータの作成

まずはトランザクションデータとして、注文データtransactionを作成します。

transaction = pd.DataFrame({
    '注文番号': ['C1201-0001', 'C1201-0002','C1201-0003' , 'C1201-0004'],
    '注文数': [4, 5, 6, 1],
    '商品コード': ['S0000001', 'S0000002', 'S0000003', 'S0000005']
})
transaction

実行すると、4件の注文データがあることがわかります。このあと作成する商品マスタを見てもらうとわかりますが、注文番号C1201-0004の商品コードS0000005は商品マスタにないコードです。

マスタデータの作成

次にマスタデータとして、商品マスタを作成します。

master = pd.DataFrame({
    '商品コード': ['S0000001', 'S0000002', 'S0000002', 'S0000003', 'S0000004'],
    '商品名': ['商品１', '商品２(旧価格)', '商品２(新価格)', '商品３', '商品４'],
    '単価': [10000, 20000, 30000, 40000, 50000]
})
master

商品コードS0000002が2行あることがわかります。商品名を見る限り、これらは同じ商品で一つは旧価格でもう一つは新価格のようです。
ですが、これによりこの商品マスタは商品コードでは一意になりません。

マージしてみる

how='inner'で結合すると、存在しない商品コードを持つC1201-0004が消えてしまうので、how='left'で結合します。

pd.merge(transaction, master, how='left', on='商品コード')

注文番号C1201-0004は消えませんでしたが、注文番号C1201-0002が2レコードに増幅してしまっています。これでは正しく計算できません。

他の結合方法も試してみる

結論からいうと、いずれも期待した結果にならないので駄目ですが、念のため、他の方法も試してみます。

pd.merge(transaction, master, how='inner', on='商品コード')

pd.merge(transaction, master, how='right', on='商品コード')

pd.merge(transaction, master, how='outer', on='商品コード')

ではどうするか

いくつかやり方はあると思いますが、ここでは商品マスタから重複するデータを除外してからマージすることにします。duplicatedメソッドを使って重複している商品コードをあぶり出して除外してみます。
入門書ではあまり見かけないメソッドなので、1つずつ順番に確認していきます。

まずは、商品マスタのキーである商品コードを取り出します。

master['商品コード']

この時点でインデックス1と2の商品コードが同じであることがわかります。
ちなみに、キー(PK)が複数あるときは、

master[['キー１', 'キー２']]

のようにリストで複数並べてください。

次に、このSeriesのどのインデックスが重複しているかをduplicatedメソッドを用いて表示してみます。

master['商品コード'].duplicated()

これをみると、重複しているインデックス1と2のうち、2の方がTrueとなりました。
TrueやFalseが返ってくるということは、これを使って商品マスタをフィルタすることができます。ただし、Trueとなっているインデックスが残り、Falseとなっているインデックスが消えるので、~を使って反転させます。

~master['商品コード'].duplicated()

なんとなく、これで重複除外できそうなので、商品マスタをフィルタしてみます。

master[~master['商品コード'].duplicated()]

商品コードS0000002が行になり、重複が除外されましたが、ここで1つ気になる点があります。
商品コードS0000002の商品名が商品２(旧価格)になっている点です。
これはduplicatedメソッドの引数keepのデフォルト値がfirstになっているためで、重複した複数のインデックスのうち、最初のインデックスをFalse(キープ)と判定し、それ以外をTrue(重複)と判定したものを~で反転したさせたためです。
ここでは最新価格の方を使いたいということにして、keep='last'(最後をキープする)を指定します。

少し周りくどいですが、もう一度TrueとFalseのどちらに判定されるか見てみます。

master['商品コード'].duplicated(keep='last')

重複しているインデックス1と2のうち、最初のインデックス1がTrue(重複)となり、最後のインデックス2がFalse(キープ)されていることがわかります。
では、再度反転してフィルタしてみます。

master[~master['商品コード'].duplicated(keep='last')]

これで、重複した商品コードS0000002の重複が除外され、最新価格の方だけが残りました。

最後に、これを使って結合(Merge)してみます。

pd.merge(transaction, master[~master['商品コード'].duplicated(keep='last')], how='left', on='商品コード')

期待通りに結合ができました。

最後に

やり方の説明のために注文データと商品マスタを使いましたが、実務的にはこれでは駄目です。
なぜかと言うと、注文番号C1201-0002で販売した商品がS0000002であることは正しいと思いますが、どちらの価格で販売したのかがわかりません。ここでは新価格を適用しましたが、旧価格で販売していたら売上金額にズレが生じます。
普通は価格が変わるごとに商品コードを変えたり、商品コード＋価格コード等の複合キーの形で一意になるようにするなどするので、この例のようなマスタの持ち方でDBに格納されていることはありえません。

あくまでもduplicatedメソッドを使った重複除外の例としてみていただけたら幸いです。