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

发布时间：2020-12-20 12:00:23 所属栏目：Python 来源：网络整理

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

我有一个pandas数据帧,如下所示.

dataframee

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

format

我曾尝试使用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  expect
0      Pass       1
1      Fail       2
2      Fail       3

writer = pd.ExcelWriter('pandas_conditional.xlsx',engine='xlsxwriter')
df.to_excel(writer,sheet_name='Sheet1')
workbook  = writer.book
worksheet = 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 string

df = pd.DataFrame({'Pass/Fail':['Pass',3]})
print (df)
  Pass/Fail  expect
0      Pass       1
1      Fail       2
2      Fail       3

writer = pd.ExcelWriter('pandas_conditional.xlsx',sheet_name='Sheet1')
workbook  = writer.book
worksheet = 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 it
d = 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 formatting
col = 'Pass/Fail'
excel_header = str(d[df.columns.get_loc(col)])
#get length of df
len_df = str(len(df.index) + 1)
rng = excel_header + '2:' + excel_header + len_df
print (rng)
B2:B4

worksheet.conditional_format(rng,'format': red_format})

worksheet.conditional_format(rng,'format':  green_format})
writer.save()

EDIT1：

感谢jmcnamara的评论和XlsxWriter

col = 'Pass/Fail'
loc = df.columns.get_loc(col) + 1
len_df = len(df.index) + 1

worksheet.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 expect
0      Pass      d
1      Fail      f
2      Fail      g

def 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 y

styled = df.style.apply(f,axis=None)
styled.to_excel('styled.xlsx',engine='openpyxl')

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!