[TOC]
# 创建DataFrame
~~~
import pandas as pd
# key,A,B是列,里面是值
left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3']})
right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']})
print(left)
~~~
输出
~~~
A B key
0 A0 B0 K0
1 A1 B1 K1
2 A2 B2 K2
3 A3 B3 K3
~~~
# on
~~~
# on表示以这个key为键
res = pd.merge(left, right, on='key')
print(res)
~~~
输出
~~~
A B key C D
0 A0 B0 K0 C0 D0
1 A1 B1 K1 C1 D1
2 A2 B2 K2 C2 D2
3 A3 B3 K3 C3 D3
~~~
**on也可以指定多个key**
如果有不一样的那行会被忽略
~~~
import pandas as pd
left = pd.DataFrame({'key1': ['K0', 'K1', 'K2', 'K3'],
'key2': ['K0', 'K1', 'K2', 'K3'],
'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3']})
right = pd.DataFrame({'key1': ['K0', 'K1', 'K2', 'K3'],
'key2': ['K0', 'K1', 'K2', 'K4'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']})
res = pd.merge(left, right, on=['key1', 'key2'])
print(res)
~~~
输出
~~~
A B key1 key2 C D
0 A0 B0 K0 K0 C0 D0
1 A1 B1 K1 K1 C1 D1
2 A2 B2 K2 K2 C2 D2
~~~
**如果指定key1的话,他会把key1的合并起来,其他的列名相同也不会合并**
~~~
res = pd.merge(left, right, on='key1')
print(res)
~~~
输出
~~~
A B key1 key2_x C D key2_y
0 A0 B0 K0 K0 C0 D0 K0
1 A1 B1 K1 K1 C1 D1 K1
2 A2 B2 K2 K2 C2 D2 K2
3 A3 B3 K3 K3 C3 D3 K4
~~~
**指定多个key,并集,没有的值显示NaN**
~~~
res = pd.merge(left, right, on=['key1', 'key2'], how='outer')
print(res)
~~~
输出
~~~
A B key1 key2 C D
0 A0 B0 K0 K0 C0 D0
1 A1 B1 K1 K1 C1 D1
2 A2 B2 K2 K2 C2 D2
3 A3 B3 K3 K3 NaN NaN
4 NaN NaN K3 K4 C3 D3
~~~
**查看并集的情况**
~~~
res = pd.merge(left, right, on=['key1', 'key2'], how='outer', indicator=True)
print(res)
~~~
输出
~~~
A B key1 key2 C D _merge
0 A0 B0 K0 K0 C0 D0 both
1 A1 B1 K1 K1 C1 D1 both
2 A2 B2 K2 K2 C2 D2 both
3 A3 B3 K3 K3 NaN NaN left_only
4 NaN NaN K3 K4 C3 D3 right_only
~~~
**左连接**
~~~
res = pd.merge(left, right, on=['key1', 'key2'], how='left')
print(res)
~~~
输出
~~~
A B key1 key2 C D
0 A0 B0 K0 K0 C0 D0
1 A1 B1 K1 K1 C1 D1
2 A2 B2 K2 K2 C2 D2
3 A3 B3 K3 K3 NaN NaN
~~~
**右连接**
~~~
res = pd.merge(left, right, on=['key1', 'key2'], how='right')
print(res)
~~~
输出
~~~
A B key1 key2 C D
0 A0 B0 K0 K0 C0 D0
1 A1 B1 K1 K1 C1 D1
2 A2 B2 K2 K2 C2 D2
3 NaN NaN K3 K4 C3 D3
~~~
