はじめに
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'],
'商品名': ['商品1', '商品2(旧価格)', '商品2(新価格)', '商品3', '商品4'],
'単価': [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[['キー1', 'キー2']]
のようにリストで複数並べてください。
次に、このSeriesのどのインデックスが重複しているかをduplicated
メソッドを用いて表示してみます。
master['商品コード'].duplicated()
これをみると、重複しているインデックス1
と2
のうち、2
の方がTrue
となりました。
True
やFalse
が返ってくるということは、これを使って商品マスタをフィルタすることができます。ただし、True
となっているインデックスが残り、False
となっているインデックスが消えるので、~
を使って反転させます。
~master['商品コード'].duplicated()
なんとなく、これで重複除外できそうなので、商品マスタをフィルタしてみます。
master[~master['商品コード'].duplicated()]
商品コードS0000002
が行になり、重複が除外されましたが、ここで1つ気になる点があります。
商品コードS0000002
の商品名が商品2(旧価格)
になっている点です。
これは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
メソッドを使った重複除外の例としてみていただけたら幸いです。