python 合并函数_python 数据合并函数merge( )

python中的merge函数与sql中的 join 用法非常类似，以下是merge( )函数中的参数：

merge(left, 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)

一、左右连接键名一样

import pandas as pd

df1=pd.DataFrame({'key':['a','b','a','b','b'],'value1':range(5)})

df2=pd.DataFrame({'key':['a','c','c','c','c'],'value2':range(5)})

display(df1,df2,pd.merge(df1,df2))

df1

key value1

0a0

1b1

2a2

3b3

4b4

df2

key value2

0a0

1c1

2c2

3c3

4c4

pd.merge(df1,df2) ##以df1、df2中相同的列名key进行连接,默认how='inner', pd.merge(df1,df2,on='key',how='inner')

key value1 value2

0a00

1a20

pd.merge(df1,df2,how='outer') ##  全连接，取并集

keyvalue1value2

0a0.00.0

1a2.00.0

2b1.0NaN

3b3.0NaN

4b4.0NaN

5cNaN1.0

6cNaN2.0

7cNaN3.0

8cNaN4.0

pd.merge(df1,df2,how='left')  ### 左连接，左边取全部，右边取部分，没有值则用NaN填充

key value1 value2

0a00.0

1b1NaN

2a20.0

3b3NaN

4b4NaN

pd.merge(df1,df2,how='right') ###  右连接，右边取全部，左边取部分，没有值则用NaN填充

key value1 value2

0a0.00

1a2.00

2cNaN1

3cNaN2

4cNaN3

5cNaN4

二、左右连接键名不一样

如果两个DataFrame的左右连接键的列名不一样，可以用left_on，right_on来进行指定

df3=pd.DataFrame({'lkey':['a','b','a','b','b'],'data1':range(5)})

df4=pd.DataFrame({'rkey':['a','c','c','c','c'],'data2':range(5)})

df3

lkey data1

0 a 0

1 b 1

2 a 2

3 b 3

4 b 4

df4

rkey data2

0 a 0

1 c 1

2 c 2

3 c 3

4 c 4

pd.merge(df3,df4,left_on='lkey',right_on='rkey')   ### 内连接，默认how='inner'

lkey data1 rkey data2

0 a 0 a 0

1 a 2 a 0

pd.merge(df3,df4,left_on='lkey',right_on='lkey',how='outer')  ### 全连接

lkey data1 rkey data2

0 a 0.0 a 0.0

1 a 2.0 a 0.0

2 b 1.0 NaN NaN

3 b 3.0 NaN NaN

4 b 4.0 NaN NaN

5 NaN NaN c 1.0

6 NaN NaN c 2.0

7 NaN NaN c 3.0

8 NaN NaN c 4.0

pd.merge(df3,df4,left_on='lkey',right_on='rkey',how='left')  ### 左连接

lkey data1 rkey data2

0 a 0 a 0.0

1 b 1 NaN NaN

2 a 2 a 0.0

3 b 3 NaN NaN

4 b 4 NaN NaN

pd.merge(df3,df4,left_on='lkey',right_on='rkey',how='right')  ### 右连接

lkey data1 rkey data2

0 a 0.0 a 0

1 a 2.0 a 0

2 NaN NaN c 1

3 NaN NaN c 2

4 NaN NaN c 3

5 NaN NaN c 4

三、索引作为连接键

df5=pd.DataFrame(np.arange(12).reshape(3,4),index=list('abc'),columns=['v1','v2','v3','v4'])

df6=pd.DataFrame(np.arange(12,24,1).reshape(3,4),index=list('abd'),columns=['v5','v6','v7','v8'])

df5

v1 v2 v3 v4

a 0 1 2 3

b 4 5 6 7

c 8 9 10 11

df6

v5 v6 v7 v8

a 12 13 14 15

b 16 17 18 19

d 20 21 22 23

pd.merge(df5,df6,left_index=True,right_index=True)

v1v2v3v4v5v6v7v8

a012312131415

b456716171819