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]