用颜色pd.ExcelWriter格式化Excel文件 [英] Format excel file with colors pd.ExcelWriter
本文介绍了用颜色pd.ExcelWriter格式化Excel文件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个数据框df
df
Out[15]:
Subject ID
StartDate
2017-11-06 ID1
2017-11-07 ID1
2017-11-08 ID2
2017-11-10 ID3
然后将其保存在excel文件中
and I save it in an excel file
writer = pd.ExcelWriter(os.path.join(folders_path,'summary.xls'))
df.to_excel(writer,'Sheet1')
writer.save()
但是我想根据Subject ID
设置行的颜色,即我希望每个ID都使用不同的颜色
However I would like to format the color of the rows according to the Subject ID
, i.e. I would like a different colour for each ID
推荐答案
您必须使用"xlsxwriter"引擎并定义格式.
You have to use the 'xlsxwriter' engine and define your formats.
以下是可能的代码段:
#!/usr/bin/env python3
from datetime import date
import pandas as pd
data = [['2017-11-06', 'ID1'],
['2017-11-07', 'ID1'],
['2017-11-08', 'ID2'],
['2017-11-10', 'ID3']]
df = pd.DataFrame(data, columns=['StartDate', 'Subject ID'])
print(df)
writer = pd.ExcelWriter('/home/spomared/tmp/summary.xls', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']
my_formats = {'"ID1"': '#FF0000',
'"ID2"': '#00FF00',
'"ID3"': '#0000FF'}
for val, color in my_formats.items():
fmt = workbook.add_format({'font_color': color})
worksheet.conditional_format('B2:B5', {'type': 'cell',
'criteria': '=',
'value': val,
'format': fmt})
writer.save()
这是输出的Excel文件:
Here is the output Excel file:
您可以在 http://xlsxwriter.readthedocs.io/working_with_conditional_formats上找到一些条件格式的引用. html
这篇关于用颜色pd.ExcelWriter格式化Excel文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文