检索替换excel

# -*- coding: UTF-8 -*-

__time__ = '2019/3/19 21:40'
import numpy as np
import pandas as pd
import xlwt
def trans_to(data):
    train_data = np.array(data)
    return train_data.tolist()#list
io=r'C:\Users\hp\Desktop\替换(1).xlsx'
data=pd.read_excel(io, sheet_name='两列',usecols = [0,1],header=None)#usecols = [0, 1, 3]
data_x=data.loc[:,0]
data_y=data.loc[:,1]

train_data = np.array(data_x)
train_x_list=train_data.tolist()#list
train_data = np.array(data_y)
train_y_list=train_data.tolist()#list
print(train_x_list)
print(train_y_list)
#11列
#io=r'C:\Users\hp\Desktop\替换2.xlsx'
r_data=pd.read_excel(io,sheet_name='替换', usecols = [10],header=None)#usecols = [0, 1, 3]
train_data = np.array(r_data)
r_data=train_data.tolist()#list
print(r_data)
result=[]
print(len(train_x_list))
for i in r_data:
    s = ';'.join(i)
    #s=i[0]
    #print(s)
    l=s.split(';')
    temp=''
    del (l[-1])
    #print(l)
    #list = list[0:-1]
    #list.pop()
    for k in l:#每一个单词都进行检索，如果成功就进行下一个
        for j in range(len(train_x_list)):
            #print(s)
            if train_x_list[j] == k:
                #print(s+'##'+train_x_list[j]+train_y_list[j]+'\n')
                k=k.replace(train_x_list[j],train_y_list[j])
                print(k,train_x_list[j],train_y_list[j])
                print('替换一个')
                break

        temp=temp+k+';'
    result.append(temp.replace(';;',';'))
print(result)

# loc：通过行标签索引数据
# iloc：通过行号索引行数据
# ix：通过行标签或行号索引数据（基于loc和iloc的混合）
# for indexs in data.index:
#     print(data.loc[indexs].values[0:-1])#从第一个到最后一个
#######################3---------------------------------################
# data = pd.read_excel("example.xlsx", sheet_name="Sheet1")
# # 找到gender这一列，再在这一列中进行比较
# data['gender'][data['gender'] == 'male'] = 0
# data['gender'][data['gender'] == 'female'] = 1
# import xlrd
# import xlwt
# from xlutils.copy import copy
# def write_excel_xls_append(path, value):
#     index = len(value)  # 获取需要写入数据的行数
#     workbook = xlrd.open_workbook(path)  # 打开工作簿
#     sheets = workbook.sheet_names()  # 获取工作簿中的所有表格
#     worksheet = workbook.sheet_by_name('替换')  # 获取工作簿中所有表格中的的第一个表格
#     rows_old = worksheet.nrows  # 获取表格中已存在的数据的行数
#     new_workbook = copy(workbook)  # 将xlrd对象拷贝转化为xlwt对象
#     new_worksheet = new_workbook.get_sheet('替换')  # 获取转化后工作簿中的第一个表格
#     for i in range(0, index):
#         for j in range(0, len(value[i])):
#             new_worksheet.write(i+rows_old, j, value[i][j])  # 追加写入数据，注意是从i+rows_old行开始写入
#     new_workbook.save(path)  # 保存工作簿
#     print("xls格式表格【追加】写入数据成功！")
#

# for i in result:
#     print(i+'\t')

excel_writer=r'C:\Users\hp\Desktop\替换888.xlsx'
df1=pd.DataFrame(result)#字典格式{'Data1':[1,2,3,4,5,6,7]}  startcol=8
df1.to_excel(excel_writer, sheet_name='替换',header=None,index=None,startcol=2,index_label=None)#,columns=['K']好像是写入的dtaframe内容