016_多表联合

 

 

import pandas as pd

if __name__ == '__main__':
    pd.options.display.max_columns = 777
    students = pd.read_excel("C:/Users/123/Desktop/pandas/016_多表联合/Student_Score.xlsx", sheet_name="Students")
    scores = pd.read_excel("C:/Users/123/Desktop/pandas/016_多表联合/Student_Score.xlsx", sheet_name="Scores")

    # 方法一 : merge;
    # 1 全部匹配
    table_v1 = students.merge(scores, on = "ID")
    print(table_v1)

    # 2 保留匹配
    table_v2 = students.merge(scores, how = "left", on = "ID").fillna("")    # 无法匹配处理
    # table_v2 = students.merge(scores, how = "left", left_on = "ID", right_on = "ID").fillna("")  # 无法匹配处理
    # table_v2.Score = table_v2.Score.astype(int)    # 类型转换
    print(table_v2)

    table_v2 = table_v2.set_index("ID")
    table_v2.to_excel("C:/Users/123/Desktop/Student_Score.xlsx")


    # 方法二 : join;
    students = pd.read_excel("C:/Users/123/Desktop/pandas/016_多表联合/Student_Score.xlsx", sheet_name="Students", index_col="ID")
    scores = pd.read_excel("C:/Users/123/Desktop/pandas/016_多表联合/Student_Score.xlsx", sheet_name="Scores", index_col="ID")
    table_v2 = students.join(scores, how="left").fillna("")  # 默认index
    print(table_v2)