Pandas 合并操作

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

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

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) 在單個(gè)鍵上進(jìn)行合并操作

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

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) 在多個(gè)鍵上進(jìn)行合并操作

下面示例，指定多個(gè)鍵來合并上述兩個(gè) DataFrame 對(duì)象：

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 中要包含哪些鍵，如果在左表、右表都不存的鍵，那么合并后該鍵對(duì)應(yīng)的值為 NaN。為了便于大家學(xué)習(xí)，我們將 how 參數(shù)和與其等價(jià)的 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']}) 
#求出兩個(gè)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']}) 
#求出兩個(gè)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

注意：當(dāng) a 與 b 進(jìn)行內(nèi)連操作時(shí) a.join(b) 不等于 b.join(a)。