pandas在多表查询并输出行数据到不同sheet页

import pandas as pd
import pathlib
from openpyxl import load_workbook

original_file = r'school_and_country_table.csv'

originaltable = pd.read_csv(original_file)
originadata = originaltable.loc[:, 'school_name'].values

home_path = pathlib.Path(r'世界大学排名').glob("*.csv")


# p = pathlib.Path(r'D:\data').rglob("*.py")  # 递归
def data(file):
    data = pd.read_csv(str(file))
    tempdata = []
    for od in range(len(originadata)):
        try:
            # print(pd.loc[pd['university_name'] == originadata[od]].values[0])
            tempdata.append(data.loc[data['university_name'] == originadata[od]].values[0])
        except IndexError:
            continue
    return tempdata


writer = pd.ExcelWriter(r'D:/test.xlsx')
for x, y in enumerate(home_path, 1):
    if str(y) == 'school_and_country_table.csv':
        continue
    elif x % 2 == 0:
        pddata = pd.DataFrame(data(y))
        pddata.to_excel(writer, 'sheet' + str(x))
    else:
        pddata1 = pd.DataFrame(data(y))
        pddata1.to_excel(writer, 'sheet' + str(x))
writer.save()
writer.close()