python – 通过pandas xlsxwriter表循环
发布时间:2020-12-20 11:03:40 所属栏目:Python 来源:网络整理
导读:我想使用pandas在excel表中创建一个条件格式.如果列A和B小于C列中的相应值,则列A和B应为红色.如何正确完成,以及为什么我的循环提前停止2行? import numpy as npimport pandas as pdd = pd.DataFrame(np.random.randint(1,4,(15,3)),columns = ['A','B','C']
我想使用pandas在excel表中创建一个条件格式.如果列A和B小于C列中的相应值,则列A和B应为红色.如何正确完成,以及为什么我的循环提前停止2行?
import numpy as np import pandas as pd d = pd.DataFrame(np.random.randint(1,4,(15,3)),columns = ['A','B','C']) writer = pd.ExcelWriter('c:usersMYNAMEdesktopxl_loop.xlsx') # please change! d.to_excel(writer,'Sheet1',index = False) worksheet = writer.sheets['Sheet1'] for i in (range(d.shape[0])): worksheet.conditional_format( 'A{}:B{}'.format(i,i),{'type': 'icon_set','icon_style': '3_traffic_lights','icons': [{'criteria': '>','type': 'number','value': d.iloc[i,2]},{'criteria': '=',{'criteria': '<',2]} ]} ) writer.save() 解决方法
在范围计算和定位中有几个一对一的错误,这就是为什么你的范围是2行短.
但是,没有必要为每一行创建条件格式.您可以为整个范围创建一个,如下所示: import numpy as np import pandas as pd d = pd.DataFrame(np.random.randint(1,'C']) writer = pd.ExcelWriter('xl_loop.xlsx') d.to_excel(writer,index = False) workbook = writer.book worksheet = writer.sheets['Sheet1'] red_format = workbook.add_format({'bg_color': '#FFC7CE','font_color': '#9C0006'}) start_row = 1 end_row = start_row + d.shape[0] -1 start_col = 0 end_col = 1 worksheet.conditional_format(start_row,start_col,end_row,end_col,{'type': 'cell','criteria': '<','value': '$C2','format': red_format}) writer.save() 输出: (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |