Pandas 处理excel

import  xlrd
import pandas as pd
import numpy as np
import os
from pandas import DataFrame,Series
import re

# data = xlrd.open_workbook(r'C:\Users\Admin\Desktop\test.xls')
# table = data.sheets()[2]
# names = data.sheet_names()

df1 = pd.read_excel(r'C:\Users\Admin\Desktop\主承销商核对.xlsx',sheet_name=0)
df1 =df1['联席全球协调人']
df1=df1.fillna('--')
#obligor_ticker_sky
df2 =pd.read_excel(r'C:\Users\Admin\Desktop\CE.xlsx',sheet_name=0)
df2 = df2[['英文名','中文名']]
df2 =df2.reset_index(drop=True)
df2 = df2.to_dict('list')
engname = df2['英文名']
cnname = df2['中文名']
final = []
for row in df1:
    list = row.split(';')
    for i in range(0,len(list)):

        if list[i] in engname:
            index_eng = engname.index(list[i])
            list[i] = cnname[index_eng]
    final.append(list)



df = pd.read_excel(r'C:\Users\Admin\Desktop\主承销商核对.xlsx',sheet_name=0)
df['联席全球协调人'] = final
df.to_excel(r'C:\Users\Admin\Desktop\主承销商核对.xlsx')