Python - 使用 Pandas 格式化 Excel 单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44150078/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python - Using pandas to format excel cell
提问by user3843433
I have a pandas dataframe, which is something like shown below.
我有一个Pandas数据框，如下所示。
I would like to format the column "Pass/Fail" as if Fail --> red background, else green background, like:
我想将“通过/失败”列格式化为if Fail --> red background, else green background，例如：
I have tried to use Pandas to do the formatting, but it fails to add color to the excel. Following is the code:
我曾尝试使用 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')
I tried StyleFrame which failed to install. Seems that StyleFrame does not comply with my python version 3.6.
我尝试过安装失败的 StyleFrame。似乎 StyleFrame 不符合我的 python 3.6 版。
How can I format the excel as I want?
如何根据需要格式化excel？
回答by jezrael
You can use conditional_format:
您可以使用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()
More dynamic solution with get_locfor position of columnand mapping with dictionary:
更动态的解决方案，get_loc用于位置column和映射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()
EDIT1:
编辑1：
Thank you jmcnamarafor comment and for XlsxWriter
感谢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()
EDIT:
编辑：
Another solution with last version of pandas (0.20.1) and styles:
使用最新版本的Pandas ( 0.20.1) 和样式的另一种解决方案：
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')
回答by DeepSpace
Disclaimer: I wrote the following library
免责声明：我编写了以下库
I'd like to suggest using StyleFrame:
我想建议使用StyleFrame：
import pandas as pd
from StyleFrame import StyleFrame, Styler
df = pd.DataFrame({'Pass/Fail':['Pass','Fail','Fail'],
'expect':[1,2,3]})
sf = StyleFrame(df)
sf.apply_style_by_indexes(sf[sf['Pass/Fail'] == 'Pass'], cols_to_style='Pass/Fail',
styler_obj=Styler(bg_color='green'))
sf.apply_style_by_indexes(sf[sf['Pass/Fail'] == 'Fail'], cols_to_style='Pass/Fail',
styler_obj=Styler(bg_color='red'))
sf.to_excel('test.xlsx').save()
Since it bridges the gap between pandas and openpyxl, the styling is done on the dataframe level instead of the worksheet level (so for example you don't need to know the relevant cell range is B2:B4or mess with indexes.
由于它弥合了 pandas 和 openpyxl 之间的差距，因此样式是在数据帧级别而不是工作表级别完成的（例如，您不需要知道相关的单元格范围是B2:B4或与索引混淆。
The code above outputs the following:
上面的代码输出如下：
EDIT: Just saw you mentioned you've tried to install but got an error. Can you edit your question and include the error?
编辑：刚刚看到您提到您已尝试安装但出现错误。您可以编辑您的问题并包含错误吗？
回答by toto_tico
If have one or more columnsand more than two values to format, and want to apply multiple format rules at oncethen you can do the following:
如果有一个或多个列和两个以上的值来格式化，并且想要一次应用多个格式规则，那么您可以执行以下操作：
def fmt(data, fmt_dict):
return data.replace(fmt_dict)
styled = df.style.apply(fmt, fmt_dict=fmt_dict, subset=['Test_1', 'Test_2' ])
styled.to_excel('styled.xlsx', engine='openpyxl')
Above, fm_dictis a dictionary with the values mapped to the corresponding format:
上面fm_dict是一个字典，其中的值映射到相应的格式：
fmt_dict = {
'Pass': 'background-color: green',
'Fail': 'background-color: red',
'Pending': 'background-color: yellow; border-style: solid; border-color: blue'; color: red,
}
Notice that for the 'Pending'value, you can also specify multiple format rules(e.g. border, background color, foreground color)
请注意，对于'Pending'值，您还可以指定多种格式规则（例如边框、背景色、前景色）
(Requires: openpyxland jinja2)
（需要：openpyxl和jinja2）
Here is a full running example:
这是一个完整的运行示例：
import pandas as pd
df = pd.DataFrame({'Test_1':['Pass','Fail', 'Pending', 'Fail'],
'expect':['d','f','g', 'h'],
'Test_2':['Pass','Pending', 'Pass', 'Fail'],
})
fmt_dict = {
'Pass': 'background-color: green',
'Fail': 'background-color: red',
'Pending': 'background-color: yellow; border-style: solid; border-color: blue; color:red',
}
def fmt(data, fmt_dict):
return data.replace(fmt_dict)
styled = df.style.apply(fmt, fmt_dict=fmt_dict, subset=['Test_1', 'Test_2' ])
styled.to_excel('styled.xlsx', engine='openpyxl')
