CODEWITHSUNDEEP
pythonpandasipython
Rahul Wadhwani
Rahul Wadhwani

Reputation: 443

Coloring Cells in Pandas

I am able to import data from an Excel file with Pandas by using:

xl = read_excel('path_to_file.xls', 'Sheet1', index_col=None, na_values=['NA'])

Now that I have all the data in xl as a DataFrame, I would like to colour some cells in that data based on conditions defined in another function before exporting the same data (with colour coding) to an Excel file.

How can I color specific cells in a Pandas DataFrame?

Upvotes: 31

Views: 144700

Answers (5)

davidvandebunte
davidvandebunte

Reputation: 1488

The accepted and alternative answers seems to focus on applymap, which is deprecated in favor of map.

Many users will also be able to jump straight to one of the Table Visualization § Builtin Styles and avoid reading that whole page or doing all the work required by map. Notice in particular the excellent examples at the bottom of background_gradient.

Upvotes: 0

Yuchao Jiang
Yuchao Jiang

Reputation: 3838

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

Upvotes: 14

wkzhu
wkzhu

Reputation: 1660

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):

enter image description here

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.

Upvotes: 63

pyano
pyano

Reputation: 1988

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

Upvotes: 2

Skorpeo
Skorpeo

Reputation: 2572

try something like this:

with pandas.io.excel.ExcelWriter(path=Path, engine="xlsxwriter") as writer:
   sheet = writer.book.worksheets()[0]
   sheet.write(x, y, value, format) #format is what determines the color etc.

More info here: https://xlsxwriter.readthedocs.org/format.html

Upvotes: 1

Related Questions