Python – 使用pandas格式化excel单元格

概述我有一个pandas数据帧,如下所示. 我想格式化“通过/失败”列,就好像失败 – >红色背景,否则绿色背景,如： 我曾尝试使用Pandas进行格式化,但无法为excel添加颜色.以下是代码： writer = pandas.ExcelWriter(destination,engine = 'xlsxwriter')color = Answer.style.applymap(lambda x: 我有一个pandas数据帧,如下所示.

我想格式化“通过/失败”列,就好像失败 – >红色背景,否则绿色背景,如：

我曾尝试使用Pandas进行格式化,但无法为excel添加颜色.以下是代码：

writer = pandas.ExcelWriter(destination,engine = 'xlsxwriter')color = Answer.style.applymap(lambda x: 'color: red' if x == "Fail" else 'color: green',subset= pandas.IndexSlice[:,['Pass/Fail']])color.to_excel(writer,'sheet1')

我尝试了无法安装的StyleFrame.似乎StyleFrame不符合我的python版本3.6.

如何根据需要格式化excel？

解决方法 你可以使用 conditional_format：

df = pd.DataFrame({'Pass/Fail':['Pass','Fail','Fail'],'expect':[1,2,3]})print (df)  Pass/Fail  expect0      Pass       11      Fail       22      Fail       3writer = pd.ExcelWriter('pandas_conditional.xlsx',engine='xlsxwriter')df.to_excel(writer,sheet_name='Sheet1')workbook  = writer.bookworksheet = writer.sheets['Sheet1']red_format = workbook.add_format({'bg_color':'red'})green_format = workbook.add_format({'bg_color':'green'})worksheet.conditional_format('B2:B4',{'type': 'text','criteria': 'containing','value':     'Fail','format': red_format})worksheet.conditional_format('B2:B4','value':   'Pass','format':  green_format})writer.save()

更多动态解决方案,get_loc用于列位置和字典映射：

import stringdf = pd.DataFrame({'Pass/Fail':['Pass',3]})print (df)  Pass/Fail  expect0      Pass       11      Fail       22      Fail       3

writer = pd.ExcelWriter('pandas_conditional.xlsx',sheet_name='Sheet1')workbook  = writer.bookworksheet = writer.sheets['Sheet1']red_format = workbook.add_format({'bg_color':'red'})green_format = workbook.add_format({'bg_color':'green'})#dict for map excel header,first A is index,so omit itd = dict(zip(range(25),List(string.ascii_uppercase)[1:]))print (d){0: 'B',1: 'C',2: 'D',3: 'E',4: 'F',5: 'G',6: 'H',7: 'I',8: 'J',9: 'K',10: 'L',11: 'M',12: 'N',13: 'O',14: 'P',15: 'Q',16: 'R',17: 'S',18: 'T',19: 'U',20: 'V',21: 'W',22: 'X',23: 'Y',24: 'Z'}#set column for formattingcol = 'Pass/Fail'excel_header = str(d[df.columns.get_loc(col)])#get length of dflen_df = str(len(df.index) + 1)rng = excel_header + '2:' + excel_header + len_dfprint (rng)B2:B4worksheet.conditional_format(rng,'format': red_format})worksheet.conditional_format(rng,'format':  green_format})writer.save()

EDIT1：

感谢@L_404_4@的评论和XlsxWriter

col = 'Pass/Fail'loc = df.columns.get_loc(col) + 1len_df = len(df.index) + 1worksheet.conditional_format(1,loc,len_df,'format': red_format})worksheet.conditional_format(1,'format':  green_format})writer.save()

编辑：

最后一个版本的pandas(0.20.1)和styles的另一个解决方案：

df = pd.DataFrame({'Pass/Fail':['Pass','expect':['d','f','g']})print (df)  Pass/Fail expect0      Pass      d1      Fail      f2      Fail      gdef f(x):    col = 'Pass/Fail'    r = 'background-color: red'    g = 'background-color: green'    c = np.where(x[col] == 'Pass',g,r)    y = pd.DataFrame('',index=x.index,columns=x.columns)    y[col] = c    return ystyled = df.style.apply(f,axis=None)styled.to_excel('styled.xlsx',engine='openpyxl')

总结

以上是内存溢出为你收集整理的Python – 使用pandas格式化excel单元格全部内容，希望文章能够帮你解决Python – 使用pandas格式化excel单元格所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。