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()')