使用 python/pandas 在 excel 上创建颜色渐变的最简单方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26265403/
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
Easiest way to create a color gradient on excel using python/pandas?
提问by jenny
So I have data that I am outputting to an excel file using pandas' ExcelWriter. After the entire data is outputted to the Excel file, what is the easiest way to apply conditional formatting to it programmatically using Python?
所以我有使用Pandas的 ExcelWriter 输出到 excel 文件的数据。将整个数据输出到 Excel 文件后，使用 Python 以编程方式对其应用条件格式的最简单方法是什么？
I want to be able to do the equivalent (through Python) of selecting (in Excel) all the filled cells in the Excel sheet and clicking on "Conditional Formatting" > Color Scales. The end result is a gradient of colors based on the values, a "heat map" if you will.
我希望能够（通过 Python）选择（在 Excel 中）Excel 工作表中所有填充的单元格并单击“条件格式”>“色阶”。最终结果是基于这些值的颜色渐变，如果您愿意，则是“热图”。
This is what I am doing to generate the data:
这就是我为生成数据所做的工作：
writer = ExcelWriter('Data' + today +'.xls')
... processing data ...
df.to_excel(writer, sheet_name = 'Models', startrow = start_row, index=False)
After the data is written, I need a way to apply the conditional formatting using python. To make it simple, I want the colors to be darker shades of blue the more positive (>0) the values are and to be darker shades of red the more negative the values are (<0) and the cell to be white if the value is 0.
数据写入后，我需要一种使用python应用条件格式的方法。为简单起见，我希望颜色为更深的蓝色阴影，值越正 (>0)，红色的阴影越深，值越负 (<0) 并且单元格为白色，如果值为 0。
I tried looking into xlsxwriter (in hopes of being able to modify the excel file after creating it) but in the documentation it says that "It [XLSXwriter] cannot read or modify existing Excel XLSX files."
我尝试查看 xlsxwriter（希望能够在创建后修改 excel 文件）但在文档中它说“它 [XLSXwriter] 无法读取或修改现有的 Excel XLSX 文件。”
Please let me know if you can think of a solution or point me in the right direction.
如果您能想到解决方案或为我指明正确的方向，请告诉我。
回答by jmcnamara
Here is an example of how to apply a conditional format to the XlsxWriter Excel file created by Pandas:
以下是如何将条件格式应用于 Pandas 创建的 XlsxWriter Excel 文件的示例：
import pandas as pd
# Some sample data to plot.
list_data = [30, 40, 50, 40, 20, 10, 5]
# Create a Pandas dataframe from the data.
df = pd.DataFrame(list_data)
# Create a Pandas Excel writer using XlsxWriter as the engine.
excel_file = 'testfile.xlsx'
sheet_name = 'Sheet1'
writer = pd.ExcelWriter(excel_file, engine='xlsxwriter')
df.to_excel(writer, sheet_name=sheet_name)
# Access the XlsxWriter workbook and worksheet objects from the dataframe.
# This is equivalent to the following using XlsxWriter on its own:
#
# workbook = xlsxwriter.Workbook('filename.xlsx')
# worksheet = workbook.add_worksheet()
#
workbook = writer.book
worksheet = writer.sheets[sheet_name]
# Apply a conditional format to the cell range.
worksheet.conditional_format('B2:B8', {'type': '3_color_scale'})
# Close the Pandas Excel writer and output the Excel file.
writer.save()
The output looks like this:
输出如下所示：
See the XlsxWriter docs on Conditional Formattingto see how you can change the colours or other properties.
请参阅有关条件格式的 XlsxWriter 文档，了解如何更改颜色或其他属性。
