pandas merge连接

import pandas as pd

df1 = pd.DataFrame({'Key1':['a','a','b','c','a'],
                    'v1':['1','2','3','4','13']})

df1

df2 = pd.DataFrame({'Key2':['b','b','a','a','e'],
                    'v2':['10','11','12','13','14']})

 df2

inner 内连接

pd.merge(left=df1,
                 right=df2,
                 left_on='Key1',
                 right_on='Key2',
                 how='inner'    
                )

 

 left 左连接

pd.merge(left=df1,
                 right=df2,
                 left_on='Key1',
                 right_on='Key2',
                 how='left'    
                )

right 右连接

pd.merge(left=df1,
                 right=df2,
                 left_on='Key1',
                 right_on='Key2',
                 how='right'    
                )

 

out 全连接

pd.merge(left=df1,
                 right=df2,
                 left_on='Key1',
                 right_on='Key2',
                 how='outer'    
                )

 

cross 笛卡尔积

pd.merge(left=df1,
         right=df2,
         how='cross'
        ) 

 

	Key1	v1	Key2	v2
0	a	1	b	10
1	a	1	b	11
2	a	1	a	12
3	a	1	a	13
4	a	1	e	14
5	a	2	b	10
6	a	2	b	11
7	a	2	a	12
8	a	2	a	13
9	a	2	e	14
10	b	3	b	10
11	b	3	b	11
12	b	3	a	12
13	b	3	a	13
14	b	3	e	14
15	c	4	b	10
16	c	4	b	11
17	c	4	a	12
18	c	4	a	13
19	c	4	e	14
20	a	13	b	10
21	a	13	b	11
22	a	13	a	12
23	a	13	a	13
24	a	13	e	14

左反连接    从左表找到没能在右表中有匹配的行

pd.merge(left=df1,
                 right=df2,
                 left_on='Key1',
                 right_on='Key2',
                 how='left'    
                ).query('Key2.isna()')

右反连接   从右表中找到没能在左表中有匹配的行

pd.merge(left=df1,
                 right=df2,
                 left_on='Key1',
                 right_on='Key2',
                 how='right'  
                 ).query('Key1.isna()')