数据连接 merge

import pandas as pd
import numpy as np

df_obj1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],
                        'data1' : np.random.randint(0,10,7)})
df_obj2 = pd.DataFrame({'key': ['a', 'b', 'd'],
                        'data2' : np.random.randint(0,10,3)})

print(df_obj1)
print(df_obj2)

   data1 key
0      5   b
1      3   b
2      7   a
3      3   c
4      7   a
5      7   a
6      3   b
   data2 key
0      9   a
1      3   b
2      8   d

默认将重叠列的列名作为“外键”进行连接

pd.merge(df_obj1, df_obj2)

Paste_Image.png

on显示指定“外键”

pd.merge(df_obj1, df_obj2, on='key')

Paste_Image.png

left_on，right_on分别指定左侧数据和右侧数据的“外键”

# 更改列名
df_obj1 = df_obj1.rename(columns={'key':'key1'})
df_obj2 = df_obj2.rename(columns={'key':'key2'})

pd.merge(df_obj1, df_obj2, left_on='key1', right_on='key2')

Paste_Image.png

“外连接”

pd.merge(df_obj1, df_obj2, left_on='key1', right_on='key2', how='outer')

Paste_Image.png

左连接

pd.merge(df_obj1, df_obj2, left_on='key1', right_on='key2', how='left')

Paste_Image.png

右连接

pd.merge(df_obj1, df_obj2, left_on='key1', right_on='key2', how='right')

Paste_Image.png

处理重复列名

df_obj1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],
                        'data' : np.random.randint(0,10,7)})
df_obj2 = pd.DataFrame({'key': ['a', 'b', 'd'],
                        'data' : np.random.randint(0,10,3)})

pd.merge(df_obj1, df_obj2, on='key', suffixes=('_left', '_right'))

Paste_Image.png

按索引连接

df_obj1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],
                        'data1' : np.random.randint(0,10,7)})
df_obj2 = pd.DataFrame({'data2' : np.random.randint(0,10,3)}, index=['a', 'b', 'd'])

pd.merge(df_obj1, df_obj2, left_on='key', right_index=True)

Paste_Image.png