28

I am able to import data from an excel file using 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 DataFrame. I would like to colour some cells in that data based on conditions defined in another function and export the same (with colour coding) to an Excel file.

Can someone tell me how should I go about this?

Thank you.

Rahul Wadhwani
  • 413
  • 1
  • 4
  • 13
  • Which library should I be using for it then? – Rahul Wadhwani Jan 21 '15 at 20:04
  • [xlwt](http://www.python-excel.org) or [xlutils](http://pythonhosted.org/xlutils/index.html). May be also http://xlwings.org (free) or https://datanitro.com (paid) for controlling excel directly. Pandas is not designed to manipulate excel files, it just reads from them and makes easy to make calculations. – elyase Jan 21 '15 at 20:12
  • openpyxl is well maintained and has seen many recent update – nitin Jan 21 '15 at 20:15
  • Are you looking to generate hexadecimal colors based on the values pulled from the spreadsheet, or **actually color the cells in the Excel Workbook**? Sorry, I wasn't clear on that point from your question. – benjaminmgross Jan 21 '15 at 22:42
  • I would like to colour the cells in the Excel Workbook. – Rahul Wadhwani Jan 22 '15 at 04:32
  • 1
    You can use the `xlsxwriter` engine from Pandas to apply a conditional format to data in an Excel worksheet. See this answer to [Easiest way to create a color gradient on excel using python/pandas?](http://stackoverflow.com/questions/26265403/easiest-way-to-create-a-color-gradient-on-excel-using-python-pandas/26268303#26268303). That may be close to what you want to do. – jmcnamara Jan 22 '15 at 12:43

4 Answers4

60

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.

wkzhu
  • 1,616
  • 13
  • 23
  • Exporting Styler objects back to Excel is supported as of Pandas 0.20.0 --https://pandas.pydata.org/pandas-docs/stable/generated/pandas.io.formats.style.Styler.to_excel.html – p_sutherland Apr 06 '18 at 16:51
  • @wkzhu when I attempt to run your code I get " instead of the table displaying, any ideas? – trock2000 Sep 25 '19 at 13:27
  • @trock2000 https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html#Building-styles have you tried with this example? Are you running on Jupyter notebook? – wkzhu Sep 26 '19 at 14:14
  • @wkzhu as you inferred, the issue was I was running this in Spyder - have it working in Jupyter now, thx – trock2000 Sep 26 '19 at 16:23
13

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")
Yuchao Jiang
  • 3,522
  • 30
  • 23
  • is there a library of color data available for discrete and continuous mappings for python, similar to [Wolfram's ColorData](https://reference.wolfram.com/language/ref/ColorData.html)? – alancalvitti Oct 07 '21 at 16:27
  • @alancalvitti I am not sure, but you can pass hex values in as options. – robertlayton Oct 28 '21 at 05:31
2

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

pyano
  • 1,885
  • 10
  • 28
1

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

Skorpeo
  • 2,362
  • 2
  • 15
  • 20