Pandas 提供的 merge() 函數(shù)能夠進行高效的合并操作，這與 SQL 關系型數(shù)據(jù)庫的 MERGE 用法非常相似。從字面意思上不難理解，merge 翻譯為“合并”，指的是將兩個 DataFrame 數(shù)據(jù)表按照指定的規(guī)則進行連接，最后拼接成一個新的 DataFrame 數(shù)據(jù)表。

 merge() 函數(shù)的法格式如下：

pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,left_index=False, right_index=False, sort=True,suffixes=('_x', '_y'), copy=True)

參數(shù)說明，如下表所示：

注意：Pandas 庫的 merge() 支持各種內(nèi)外連接，與其相似的還有 join() 函數(shù)（默認為左連接）。

下面創(chuàng)建兩個不同的 DataFrame，然后對它們進行合并操作：

import pandas as pd 
left = pd.DataFrame({ 
   'id':[1,2,3,4], 
   'Name': ['Smith', 'Maiki', 'Hunter', 'Hilen'], 
   'subject_id':['sub1','sub2','sub4','sub6']}) 
right = pd.DataFrame({ 
    'id':[1,2,3,4], 
   'Name': ['William', 'Albert', 'Tony', 'Allen'], 
   'subject_id':['sub2','sub4','sub3','sub6']}) 
print (left) 
print (right)  

輸出如下：

   id    Name subject_id
0   1   Smith       sub1
1   2   Maiki       sub2
2   3  Hunter       sub4
3   4   Hilen       sub6

   id     Name subject_id
0   1  William       sub2
1   2   Albert       sub4
2   3     Tony       sub3
3   4    Allen       sub6

1) 在單個鍵上進行合并操作

通過 on 參數(shù)指定一個連接鍵，然后對上述 DataFrame 進行合并操作：

import pandas as pd 
left = pd.DataFrame({ 
   'id':[1,2,3,4], 
   'Name': ['Smith', 'Maiki', 'Hunter', 'Hilen'], 
   'subject_id':['sub1','sub2','sub4','sub6']}) 
right = pd.DataFrame({ 
    'id':[1,2,3,4], 
   'Name': ['William', 'Albert', 'Tony', 'Allen'], 
   'subject_id':['sub2','sub4','sub3','sub6']})
#通過on參數(shù)指定合并的鍵
print(pd.merge(left,right,on='id'))

輸出結(jié)果：

   id  Name_x subject_id_x   Name_y subject_id_y
0   1   Smith         sub1  William         sub2
1   2   Maiki         sub2   Albert         sub4
2   3  Hunter         sub4     Tony         sub3
3   4   Hilen         sub6    Allen         sub6

2) 在多個鍵上進行合并操作

下面示例，指定多個鍵來合并上述兩個 DataFrame 對象：

import pandas as pd 
left = pd.DataFrame({ 
   'id':[1,2,3,4], 
   'Name': ['Smith', 'Maiki', 'Hunter', 'Hilen'], 
   'subject_id':['sub1','sub2','sub4','sub6']}) 
right = pd.DataFrame({ 
    'id':[1,2,3,4], 
   'Name': ['Bill', 'Lucy', 'Jack', 'Mike'], 
   'subject_id':['sub2','sub4','sub3','sub6']}) 
print(pd.merge(left,right,on=['id','subject_id']))

輸出結(jié)果：

   id Name_x subject_id Name_y
0   4  Hilen       sub6   Mike

使用how參數(shù)合并

通過how參數(shù)可以確定 DataFrame 中要包含哪些鍵，如果在左表、右表都不存的鍵，那么合并后該鍵對應的值為 NaN。為了便于大家學習，我們將 how 參數(shù)和與其等價的 SQL 語句做了總結(jié)：

1) left join

import pandas as pd 
left = pd.DataFrame({ 
   'id':[1,2,3,4], 
   'Name': ['Smith', 'Maiki', 'Hunter', 'Hilen'], 
   'subject_id':['sub1','sub2','sub4','sub6']}) 
right = pd.DataFrame({ 
    'id':[1,2,3,4], 
   'Name': ['Bill', 'Lucy', 'Jack', 'Mike'], 
   'subject_id':['sub2','sub4','sub3','sub6']}) 
#以left側(cè)的subject_id為鍵
print(pd.merge(left,right,on='subject_id',how="left"))

輸出結(jié)果：

   id_x  Name_x subject_id  id_y Name_y
0     1   Smith       sub1   NaN    NaN
1     2   Maiki       sub2   1.0   Bill
2     3  Hunter       sub4   2.0   Lucy
3     4   Hilen       sub6   4.0   Mike

2) right join

import pandas as pd 
left = pd.DataFrame({ 
   'id':[1,2,3,4], 
   'Name': ['Smith', 'Maiki', 'Hunter', 'Hilen'], 
   'subject_id':['sub1','sub2','sub4','sub6']}) 
right = pd.DataFrame({ 
    'id':[1,2,3,4], 
   'Name': ['Bill', 'Lucy', 'Jack', 'Mike'], 
   'subject_id':['sub2','sub4','sub3','sub6']}) 
#以right側(cè)的subject_id為鍵
print(pd.merge(left,right,on='subject_id',how="right"))

輸出結(jié)果：

   id_x  Name_x subject_id  id_y Name_y
0   2.0   Maiki       sub2     1   Bill
1   3.0  Hunter       sub4     2   Lucy
2   4.0   Hilen       sub6     4   Mike
3   NaN     NaN       sub3     3   Jack

3) outer join(并集)

import pandas as pd 
left = pd.DataFrame({ 
   'id':[1,2,3,4], 
   'Name': ['Smith', 'Maiki', 'Hunter', 'Hilen'], 
   'subject_id':['sub1','sub2','sub4','sub6']}) 
right = pd.DataFrame({ 
    'id':[1,2,3,4], 
   'Name': ['Bill', 'Lucy', 'Jack', 'Mike'], 
   'subject_id':['sub2','sub4','sub3','sub6']}) 
#求出兩個subject_id的并集，并作為鍵
print(pd.merge(left,right,on='subject_id',how="outer"))

輸出結(jié)果：

   id_x  Name_x subject_id  id_y Name_y
0   1.0   Smith       sub1   NaN    NaN
1   2.0   Maiki       sub2   1.0   Bill
2   3.0  Hunter       sub4   2.0   Lucy
3   4.0   Hilen       sub6   4.0   Mike
4   NaN     NaN       sub3   3.0   Jack

4) inner join(交集)

import pandas as pd 
left = pd.DataFrame({ 
   'id':[1,2,3,4], 
   'Name': ['Smith', 'Maiki', 'Hunter', 'Hilen'], 
   'subject_id':['sub1','sub2','sub4','sub6']}) 
right = pd.DataFrame({ 
    'id':[1,2,3,4], 
   'Name': ['Bill', 'Lucy', 'Jack', 'Mike'], 
   'subject_id':['sub2','sub4','sub3','sub6']}) 
#求出兩個subject_id的交集，并將結(jié)果作為鍵
print(pd.merge(left,right,on='subject_id',how="inner"))

輸出結(jié)果：

   id_x  Name_x subject_id  id_y Name_y
0     2   Maiki       sub2     1   Bill
1     3  Hunter       sub4     2   Lucy
2     4   Hilen       sub6     4   Mike

注意：當 a 與 b 進行內(nèi)連操作時 a.join(b) 不等于 b.join(a)。