Easiest way to create a color gradient on excel using python/pandas?

Question

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?

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.

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.

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

Please let me know if you can think of a solution or point me in the right direction.

Answer 1

Here is an example of how to apply a conditional format to the XlsxWriter Excel file created by Pandas:

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 Formatting to see how you can change the colours or other properties.

See also Working with Python Pandas and XlsxWriter.