Coloring Cells in Pandas
Pandas has a relatively new Styler
feature where you can apply conditional formatting type manipulations to dataframes.
http://pandas.pydata.org/pandas-docs/stable/style.html
You can use some of their built-in functions like background_gradient
or bar
to replicate excel-like features like conditional formatting and data bars. You can also format cells to display percentages, floats, ints, etc. without changing the original dataframe.
Here's an example of the type of chart you can make using Styler
(this is a nonsense chart but just meant to demonstrate features):
To harness the full functionality of Styler
you should get comfortable with the Styler.apply()
and Styler.applymap()
APIs. These allow you to create custom functions and apply them to the table's columns, rows or elements. For example, if I wanted to color a +ive cell green and a -ive cell red, I'd create a function
def _color_red_or_green(val):
color = 'red' if val < 0 else 'green'
return 'color: %s' % color
and call it on my Styler
object, i.e., df.style.applymap(_color_red_or_green)
.
With respect to exporting back to Excel, as far as I'm aware this is not supported in Styler
yet so I'd probably go the xlsxwriter route if you NEED Excel for some reason. However, in my experience this is a great pure Python alternative, for example along with matplotlib charts and in emails/reports.
The most simple way is to use applymap and lambda if you only want to highlight certain values:
df.style.applymap(lambda x: "background-color: red" if x>0 else "background-color: white")
There are quite a few ideas about styling the cells on the Pandas website. However it ist mentioned: This is a new feature and still under development. We'll be adding features and possibly making breaking changes in future releases