Python-使用 pandas 格式化Excel单元格 [英] Python - Using pandas to format excel cell

问题描述

我有一个熊猫数据框，如下图所示.

I have a pandas dataframe, which is something like shown below.

我想将通过/失败"列的格式设置为if Fail --> red background, else green background，例如:

I would like to format the column "Pass/Fail" as if Fail --> red background, else green background, like:

我尝试使用Pandas进行格式化，但是无法为Excel添加颜色.以下是代码:

I have tried to use Pandas to do the formatting, but it fails to add color to the excel. Following is the code:

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版.

I tried StyleFrame which failed to install. Seems that StyleFrame does not comply with my python version 3.6.

如何根据需要格式化Excel?

How can I format the excel as I want?

推荐答案

您可以使用 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', {'type': 'text',
                                      'criteria': 'containing',
                                       'value':   'Pass',
                                       'format':  green_format})
writer.save()

使用 get_loc 用于column的位置并使用dictionary进行映射:

More dynamic solution with get_loc for position of column and mapping with dictionary:

import string

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'})

#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, {'type': 'text',
                                      'criteria': 'containing',
                                       'value':     'Fail',
                                       'format': red_format})

worksheet.conditional_format(rng, {'type': 'text',
                                      'criteria': 'containing',
                                       'value':   'Pass',
                                       'format':  green_format})
writer.save()

谢谢您 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,loc, {'type': 'text',
                                      'criteria': 'containing',
                                       'value':     'Fail',
                                       'format': red_format})

worksheet.conditional_format(1,loc,len_df,loc, {'type': 'text',
                                      'criteria': 'containing',
                                       'value':   'Pass',
                                       'format':  green_format})
writer.save()

另一种解决方案，使用最新版本的pandas(0.20.1)和

Another solution with last version of pandas (0.20.1) and styles:

df = pd.DataFrame({'Pass/Fail':['Pass','Fail','Fail'],
                   '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')

这篇关于Python-使用 pandas 格式化Excel单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！