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)
常用参数解释:
-
right : DataFrame or named Series:
- 当使用pandas.merge()时,right处实际填入两个待合并的结构;当使用dataframe.merge()时,right处仅填入一个待合并的结构,此处的right与dataframe分别作为右/左结构.
-
how : {‘left’, ‘right’, ‘outer’, ‘inner’}, default ‘inner’:
- how指定了结构的融合的类型,是一个关于key的重要参数
- 默认inner,即采用交叉部分的key作为列的内容
- left: 即选取左侧结构的key作为列的内容
- right: 即选取左侧结构的key作为列的内容
- outer: 选取所有的键作为列内容.
- 不存在的内容用NaN填充
-
on : label or list
Column or index level names to join on. These must be found in both DataFrames.- 参数on指定了用于合并的键key.
- 参数on指定的键必须是两个结构中共有的.
-
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 用于指示说明该行所用的键来自于哪一边结构.
-
left_index : bool, default False
Use the index from the left DataFrame as the join key(s).- left_index 设定为True, 即根据左侧结构的index进行merge. 而不再是根据某一columns.
-
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.
- 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
