Python-dataframe合并(merge函数)
import pandas as pd import numpy as np
df1=pd.DataFrame({'key':['b','b','a','c','a','a','b'],'data1':range(7)}) df2=pd.DataFrame({'key':['a','b','d'], 'data2':range(3)})
pd.merge(df1,df2) #没有指明联结的建,会将重叠列的列名当作键。
pd.merge(df1,df2,on='key').sort_values(by='key')
df3=pd.DataFrame({'lkey':['b','b','a','c','a','a','b'],'data1':range(7)})
df4=pd.DataFrame({'rkey':['a','b','d'],'data2':range(3)})
pd.merge(df3,df4,left_on='lkey',right_on='rkey')
#若两列所要联结的键不同可以分别制定,且默认也是inner
pd.merge(df1,df2,how='outer') #两个数据框不都有的key值会出现NaN
多对多
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)})
pd.merge(df1,df2,how='left') #left保证df1的data1全部出现
#多对多联结产生的结果是笛卡儿积,左边df1有3个b,右边df2有2两个b,共得6个b
pd.merge(df1,df2,how='inner')
根据多个列合并
left=pd.DataFrame({'key1':['foo','foo','bar'], 'key2':['one','two','one'], 'lval':[1,2,3]}) right=pd.DataFrame({'key1':['foo','foo','bar','bar'], 'key2':['one','one','one','two'], 'rval':[4,5,6,7]})
pd.merge(left,right,on=['key1','key2'],how='outer')
pd.merge(left,right,on=['key1','key2'])
重复列名处理
pd.merge(left,right,on='key1')
#对于key2来说其是重复的列名
pd.merge(left,right,on='key1',suffixes=('_left','_right'))
索引上的合并 (列名上无重复,index上有重复)
left1=pd.DataFrame({'key':['a','b','a','a','b','c'], 'values':range(6)}) right1=pd.DataFrame({'group_val':[3.5,7]},index=['a','b'])
pd.merge(left1,right1,left_on='key',right_index=True)
#将left1的key列与left2的index列联结
left2=pd.DataFrame([[1.,2.],[3.,4.],[5.,6.]],index=['a','c','e'],
columns=['ohio','nevada'])
right2=pd.DataFrame([[7.,8.],[9.,10.],[11.,12.],[13.,14]],
index=['b','c','d','e'],columns=['missouri','alabama'])
pd.merge(left2,right2,how='outer',left_index=True,right_index=True)
join方法
left2.join(right2,how='outer') #直接实现按索引合并
left1.join(right1,on='key') #是merge里的outer联结