关于pandas merge 合并操作的讲解

pandas 中的merge是一种功能比较强大的用于两个DataFrame或者Series进行合并的方法.

合并时会将所有的列进行合并,但是指定键值不存在行列会填充NaN.

---

直接复制官方文档 :
DataFrame.merge(right, how='inner', on=None, left_on=None, right_on=None, left_index=False, right_index=False, sort=False, suffixes=('_x', '_y'), copy=True, indicator=False, validate=None)

常用参数解释:

1. right : DataFrame or named Series:

   • 当使用pandas.merge()时,right处实际填入两个待合并的结构;当使用dataframe.merge()时,right处仅填入一个待合并的结构,此处的right与dataframe分别作为右/左结构.

2. how : {‘left’, ‘right’, ‘outer’, ‘inner’}, default ‘inner’:

   • how指定了结构的融合的类型,是一个关于key的重要参数
   • 默认inner,即采用交叉部分的key作为列的内容
   • left: 即选取左侧结构的key作为列的内容
   • right: 即选取左侧结构的key作为列的内容
   • outer: 选取所有的键作为列内容.
   • 不存在的内容用NaN填充

3. on : label or list
   Column or index level names to join on. These must be found in both DataFrames.

   • 参数on指定了用于合并的键key.
   • 参数on指定的键必须是两个结构*有的.

4. indicator : bool or str, default False
   If True, adds a column to output DataFrame called “_merge” with information on the source of each row.

   • indicator 用于指示说明该行所用的键来自于哪一边结构.

5. left_index : bool, default False
   Use the index from the left DataFrame as the join key(s).

   • left_index 设定为True, 即根据左侧结构的index进行merge. 而不再是根据某一columns.

6. right_index : bool, default False
   Use the index from the right DataFrame as the join key. Same caveats as left_index.

   • right_index 与 left_index同时使用.即根据两个结构的index进行merge.

7. suffixes : tuple of (str, str), default (‘_x’, ‘_y’)
   Suffix to apply to overlapping column names in the left and right side, respectively. To raise an exception on overlapping columns use (False, False).
   • suffixes 主要用于解决两个合并结构的列存在交叉的情况.
   • 通过suffixes 的指定,名字相同可以在merge后使用不同的列名,并同时存在.

代码:

In:df1 = pd.DataFrame({'A':['A0','A1','A2'],'B':['B0','B1','B2']},index=['KO','K1','K2'])
   df2 = pd.DataFrame({'C':['C0','C2','C3'],'D':['D0','D2','D3']},index=['KO','K1','K2'])

In: df1
Out:    
        A    B
  KO    A0  B0
  K1    A1  B1
  K2    A2  B2

In: df1
Out: 
     C   D
KO  C0  D0
K1  C2  D2
K2  C3  D3
# 打开left_index 和 right_index ,how='inner'即根据行进行merge, 合并的类型是采用交叉部分(index部分的交叉)进行合并.
In: res = pd.merge(df1,df2,left_index=True,right_index=True,how='inner')
In: res
Out: 
     A  B   C   D
KO  A0  B0  C0  D0
K1  A1  B1  C2  D2
K2  A2  B2  C3  D3

In: df1 = pd.DataFrame({'lkey1':['foo','bar','baz','foo'],'value':[1,2,3,4],'rkey':['ab','bc','cd','ef']})
    df2 = pd.DataFrame({'rkey': ['foo', 'bar', 'baz', 'foooo'],'value': [4, 6, 7, 8],'xxx':[1,23,4,5]})
In: df1
Out: 
    lkey1   value   rkey
0   foo       1     ab
1   bar       2     bc
2   baz       3     cd
3   foo       4     ef

In: df2
Out: 
    rkey    value   xxx
0   foo       4     1
1   bar       6     23
2   baz       7     4
3   foooo     8     5

# 基于value键,使用left类型进行合并.合并结果中的value只采用df1中value值,对于df2中不存在value值对应行的情况直接填充NaN(例如value1/2/3)
In: pd.merge(df1,df2,how='left',on = 'value')
Out: 
    lkey1   value   rkey_x  rkey_y  xxx
0   foo        1    ab       NaN    NaN
1   bar        2    bc       NaN    NaN
2   baz        3    cd       NaN    NaN
3   foo        4    ef       foo    1.0

注意到在上一段代码的运行结果中,重叠部分即rkey列,在融合后自动添加了x和y后缀,避免了重叠.这里也可以利用suffixes对后缀进行指定

In:  boys = pd.DataFrame({'k':['K0','K1','K2'],'age':['1','2','3'],'name':['b1','b2','b3']})
     girls = pd.DataFrame({'k':['K0','K0','K3'],'age':['4','5','6'],'name':['g1','g2','g3']})
In: boys
Out: 
    k   age name
0   K0  1   b1
1   K1  2   b2
2   K2  3   b3

In: girls
Out: 

    k   age name
0   K0  4   g1
1   K0  5   g2
2   K3  6   g3

# 针对name和age列存在重叠情况. 使用suffixes指明了后缀
In: pd.merge(boys,girls,suffixes=['_boys','_girls'],on = 'k',how=  'inner')
Out:

     k    age_boys  name_boys   age_girls   name_girls
0   K0        1       b1          4         g1
1   K0        1       b1          5         g2

In: pd.merge(boys,girls,suffixes=['_boys','_girls'],on = ['k','age'] ,how=  'left')
Out:

      k         age  name_boys  name_girls
0     K0          1   b1        NaN
1     K1          2   b2        NaN
2     K2          3   b3        NaN