Python：利用pandas对两个dataframe数据进行左联接/左关联(以左边为主体)、右联接/右关联(以右边为主体)、内联接(取交集)、外联接(取并集)操作案例实现代码
join、left join和right join之间区别
Database之SQLSever：SQL命令实现查询之多表查询、嵌套查询、分页复杂查询，删除表内重复记录数据、连接(join、left join和right join简介及其区别)等案例之详细攻略
https://yunyaniu.blog.csdn.net/article/details/107620041
左联接/左关联(以左边为主体)、右联接/右关联(以右边为主体)、内联接(取交集)、外联接(取并集)操作案例
实现代码
# Python：利用pandas对两个dataframe数据进行左联接/左关联(以左边为主体)、右联接/右关联(以右边为主体)、内联接(取交集)、外联接(取并集)操作案例实现代码
import pandas as pd
import numpy as np
students_info={"student_num": ['1001', '1002', '1003', '1004'],
"name": ['Bob', 'LiSa', 'Mary', 'Alan'],
"major": ['mechanical', 'philosophy', 'mathematics', 'computer'],
"grade": ['freshman', 'sophomore', 'junior', 'senior'],
"sex": ['男', '女', '女', None,], # 输出 None
"age": [np.nan, 28, 38 , '' ], # 输出
"born": [pd.NaT, pd.Timestamp("1990-01-01"), pd.Timestamp("1980-01-01"), ''], # 输出 NaT
}
students_score={"name": ['Bob', 'LiSa', 'Mary', 'Tom'],
"chinese": [85, 80, 95, 98],
"math": [90, 95, 85, 90],
}
s_info_df = pd.DataFrame(students_info)
s_score_df = pd.DataFrame(students_score)
s_df_left = s_info_df.merge(s_score_df, left_on='name',right_on='name',how='left')
print('------------left：以左边为主体--------------')
print(s_df_left)
s_df_right = s_info_df.merge(s_score_df, left_on='name',right_on='name',how='right')
print('------------right：以右边为主体--------------')
print(s_df_right)
s_df_inner = s_info_df.merge(s_score_df, left_on='name',right_on='name',how='inner')
print('------------inner：取交集--------------')
print(s_df_inner)
s_df_outer = s_info_df.merge(s_score_df, left_on='name',right_on='name',how='outer')
print('------------outer：取并集--------------')
print(s_df_outer)
------------left：以左边为主体--------------
student_num name major grade ... age born chinese math
0 1001 Bob mechanical freshman ... NaN NaT 85.0 90.0
1 1002 LiSa philosophy sophomore ... 28 1990-01-01 80.0 95.0
2 1003 Mary mathematics junior ... 38 1980-01-01 95.0 85.0
3 1004 Alan computer senior ... NaT NaN NaN
[4 rows x 9 columns]
------------right：以右边为主体--------------
student_num name major grade sex age born chinese math
0 1001 Bob mechanical freshman 男 NaN NaT 85 90
1 1002 LiSa philosophy sophomore 女 28 1990-01-01 80 95
2 1003 Mary mathematics junior 女 38 1980-01-01 95 85
3 NaN Tom NaN NaN NaN NaN NaT 98 90
------------inner：取交集--------------
student_num name major grade sex age born chinese math
0 1001 Bob mechanical freshman 男 NaN NaT 85 90
1 1002 LiSa philosophy sophomore 女 28 1990-01-01 80 95
2 1003 Mary mathematics junior 女 38 1980-01-01 95 85
------------outer：取并集--------------
student_num name major grade ... age born chinese math
0 1001 Bob mechanical freshman ... NaN NaT 85.0 90.0
1 1002 LiSa philosophy sophomore ... 28 1990-01-01 80.0 95.0
2 1003 Mary mathematics junior ... 38 1980-01-01 95.0 85.0
3 1004 Alan computer senior ... NaT NaN NaN
4 NaN Tom NaN NaN ... NaN NaT 98.0 90.0
[5 rows x 9 columns]
