Conditional formatting for 2- or 3-scale coloring of cells of a table

matplotlib, numpy, pandas, python

Solution

Ok, I did a little more research and this pretty much covers what i wanted to do. using matplotlib colormaps did the trick. here is a link to the options of map colours. matplotlib color maps

I went with RdYlGn colouring.

import pandas as pd
import numpy as np
from matplotlib import pyplot as plt

df = pd.DataFrame(np.random.randn(10, 8), columns=list('abcdefgh'))

print df

#Round to two digits to print nicely
vals = np.around(df.values, 2)
#Normalize data to [0, 1] range for color mapping below
normal = (df - df.min()) / (df.max() - df.min())

fig = plt.figure()
ax = fig.add_subplot(111)
ax.axis('off')
the_table=ax.table(cellText=vals, rowLabels=df.index, colLabels=df.columns, 
                   loc='center', cellColours=plt.cm.RdYlGn(normal),animated=True)
fig.savefig("table.png")

Output:

Problem

I would like to output a simple table to a PDF file with some conditional formatting of 2- or 3-scale coloring of cells dependent on the value. Like the red-white-green color scaling in Microsoft Excel conditional formatting option. ``` import pandas import numpy as np df = pandas.DataFrame(np.random.randn(10, 2), columns=list('ab')) print df #Output: a b 0 -1.625192 -0.949186 1 -0.089884 0.825922 2 2.117651 -0.046258 3 -0.921751 -0.144447 4 -0.294095 -1.774725 5 -0.780523 -0.435909 6 0.544958 0.303268 7 0.014335 0.036182 8 -0.756565 0.120711 9 1.145055 0.542755 ``` Now, I would like to output this to a PDF in a table with a 3-scale conditional format for column `a` and independently for column `b` so my output would look like the below example from Excel. Kind of like this, but by column:

Original source