MySQLやPostgreSQLなどのRDB(リレーショナルデータベース)では、複数のデータセットを、あるキーに基づいてリンクさせることで結合することができる。

これはRDBの中核となる操作だが、pandasのmerge関数を使うことで似たような操作を実現することができる。

まずは簡単な例から見てみよう。

サンプルとなる二つのデータフレームの用意

まずは多対一の結合例を見るため、下記のような二つのデータフレームを用意しよう。

import pandas as pd

df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],
                   'data1': range(7)})
#   key  data1
# 0   b      0
# 1   b      1
# 2   a      2
# 3   c      3
# 4   a      4
# 5   a      5
# 6   b      6

df2 = pd.DataFrame({'key': ['a', 'b', 'd'],
                   'data2': range(3)})
#   key  data2
# 0   a      0
# 1   b      1
# 2   d      2

merge関数による多対一の結合例

二つのデータフレームが用意できたら、まずは何も考えずにmerge関数の引数にそれぞれのデータフレームを与えてみよう。

df_merge = pd.merge(df1, df2)
#   key  data1  data2
# 0   b      0      1
# 1   b      1      1
# 2   b      6      1
# 3   a      2      0
# 4   a      4      0
# 5   a      5      0

この例のように、どの列をキーとするか指定していない場合、merge関数ではどちらのデータフレームにも含まれる同じ名前の列をキーとして扱う。
今回は「key」という列が共通しているので、「key」がキーとして扱われた。

ただし、キーを指定しないと分かりづらいので、以下のように明示的に指定したあげる方が良いだろう。

df_merge = pd.merge(df1, df2, on='key')
#   key  data1  data2
# 0   b      0      1
# 1   b      1      1
# 2   b      6      1
# 3   a      2      0
# 4   a      4      0
# 5   a      5      0

キーを個別に使用する

キーとしたい列名がそれぞれのデータフレームで異なる場合は、個別にする必要がある。

df3 = pd.DataFrame({'lkey': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],
                   'data1': range(7)})

df4 = pd.DataFrame({'rkey': ['a', 'b', 'd'],
                   'data2': range(3)})

df_merge2 = pd.merge(df3, df4, left_on='lkey', right_on='rkey')
#   lkey  data1 rkey  data2
# 0    b      0    b      1
# 1    b      1    b      1
# 2    b      6    b      1
# 3    a      2    a      0
# 4    a      4    a      0
# 5    a      5    a      0

内部結合と外部結合

さて、ここまで見てきた例で結合した結果を見ると、キーのうち「c」や「d」のデータが除外されていることが分かるだろう。

RDBの結合には内部結合や外部結合という結合方法があるが、merge関数はデフォルトだとRDBで言うところの内部結合(inner join)と同じ働きをする。

内部結合は双方のデータセットで共通したキーのみを結果として含めるようになっているため、今回は共通しない「c」や「d」が除外されたというわけだ。

外部結合した結果を得たい場合は、以下のようにhowオプションで指定する。

df_merge = pd.merge(df1, df2, on='key', how='outer')
#   key  data1  data2
# 0   b    0.0    1.0
# 1   b    1.0    1.0
# 2   b    6.0    1.0
# 3   a    2.0    0.0
# 4   a    4.0    0.0
# 5   a    5.0    0.0
# 6   c    3.0    NaN
# 7   d    NaN    2.0

df_merge = pd.merge(df1, df2, on='key', how='left')
#   key  data1  data2
# 0   b      0    1.0
# 1   b      1    1.0
# 2   a      2    0.0
# 3   c      3    NaN
# 4   a      4    0.0
# 5   a      5    0.0
# 6   b      6    1.0

df_merge = pd.merge(df1, df2, on='key', how='right')
#   key  data1  data2
# 0   b    0.0      1
# 1   b    1.0      1
# 2   b    6.0      1
# 3   a    2.0      0
# 4   a    4.0      0
# 5   a    5.0      0
# 6   d    NaN      2

# how='inner'と指定するとデフォルト挙動と同様、内部結合された結果が返される。

多対多の結合例

多対多のデータセットを結合すると、同じ名前のキーでも左右のデータセット値の組み合わせが多数存在するので、それら全ての組み合わせが結果として返る。

import pandas as pd

df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'b'],
             'data1': range(6)})

df2 = pd.DataFrame({'key': ['a', 'b', 'a', 'b', 'd'],
                   'data2': range(5)})

df_merge = pd.merge(df1, df2, on='key', how='left')
#    key  data1  data2
# 0    b      0    1.0
# 1    b      0    3.0
# 2    b      1    1.0
# 3    b      1    3.0
# 4    a      2    0.0
# 5    a      2    2.0
# 6    c      3    NaN
# 7    a      4    0.0
# 8    a      4    2.0
# 9    b      5    1.0
# 10   b      5    3.0

複数のキーを使って結合をおこなう場合は、列名のリストを渡す。

import pandas as pd

df1 = pd.DataFrame({'key1': ['foo', 'foo', 'bar'],
                   'key2': ['one', 'two', 'one'],
                   'lval': [1, 2, 3]})
#   key1 key2  lval
# 0  foo  one     1
# 1  foo  two     2
# 2  bar  one     3

df2 = pd.DataFrame({'key1': ['foo', 'foo', 'bar', 'bar'],
                   'key2': ['one', 'one', 'one', 'two'],
                   'rval': [4, 5, 6, 7]})
#   key1 key2  rval
# 0  foo  one     4
# 1  foo  one     5
# 2  bar  one     6
# 3  bar  two     7

df_merge = pd.merge(df1, df2, on=['key1', 'key2'], how='outer')
#   key1 key2  lval  rval
# 0  foo  one   1.0   4.0
# 1  foo  one   1.0   5.0
# 2  foo  two   2.0   NaN
# 3  bar  one   3.0   6.0
# 4  bar  two   NaN   7.0

重複する列名の取り扱い

データセットの結合において、重複する列名があるケースがしばしばある。

merge関数にはsuffixesというオプションがあり、列名が重複した場合にそれぞれのデータフレームの列名の末尾に指定の文字列を加えることができる。

df_merge = pd.merge(df1, df2, on='key1', suffixes=('_left', 'right'))
#   key1 key2_left  lval key2right  rval
# 0  foo       one     1       one     4
# 1  foo       one     1       one     5
# 2  foo       two     2       one     4
# 3  foo       two     2       one     5
# 4  bar       one     3       one     6
# 5  bar       one     3       two     7