Styling

This document is written as a Jupyter Notebook, and can be viewed or downloaded here.

You can apply conditional formatting, the visual styling of a DataFrame depending on the data within, by using the DataFrame.style property. This is a property that returns a Styler object, which has useful methods for formatting and displaying DataFrames.

The styling is accomplished using CSS. You write “style functions” that take scalars, DataFrames or Series, and return like-indexed DataFrames or Series with CSS "attribute: value" pairs for the values. These functions can be incrementally passed to the Styler which collects the styles before rendering.

CSS is a flexible language and as such there may be multiple ways of achieving the same result, with potential advantages or disadvantages, which we try to illustrate.

Styler Object

The DataFrame.style attribute is a property that returns a Styler object. Styler has a _repr_html_ method defined on it so they are rendered automatically. If you want the actual HTML back for further processing or for writing to file call the .render() method which returns a string.

[2]:
import pandas as pd
import numpy as np

np.random.seed(24)
df = pd.DataFrame({'A': np.linspace(1, 10, 10)})
df = pd.concat([df, pd.DataFrame(np.random.randn(10, 4), columns=list('BCDE'))],
               axis=1)
df.iloc[3, 3] = np.nan
df.iloc[0, 2] = np.nan
df.style
[2]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

The above output looks very similar to the standard DataFrame HTML representation. But we’ve done some work behind the scenes to attach CSS classes to each cell. We can view these by calling the .render method.

[3]:
df.style.render().split('\n')[:10]
[3]:
['<style type="text/css">',
 '</style>',
 '<table id="T_2c3b2_">',
 '  <thead>',
 '    <tr>',
 '      <th class="blank level0" ></th>',
 '      <th class="col_heading level0 col0" >A</th>',
 '      <th class="col_heading level0 col1" >B</th>',
 '      <th class="col_heading level0 col2" >C</th>',
 '      <th class="col_heading level0 col3" >D</th>']

The row0_col2 is the identifier for that particular cell. We’ve also prepended each row/column identifier with a UUID unique to each DataFrame so that the style from one doesn’t collide with the styling from another within the same notebook or page (you can set the uuid if you’d like to tie together the styling of two DataFrames, or remove it if you want to optimise HTML transfer for larger tables).

Building styles

There are 3 primary methods of adding custom styles to DataFrames using CSS and matching it to cells:

  • Directly linking external CSS classes to your individual cells using Styler.set_td_classes.

  • Using table_styles to control broader areas of the DataFrame with internal CSS.

  • Using the Styler.apply and Styler.applymap functions for more specific control with internal CSS.

Linking External CSS

New in version 1.2.0

If you have designed a website then it is likely you will already have an external CSS file that controls the styling of table and cell objects within your website.

For example, suppose we have an external CSS which controls table properties and has some additional classes to style individual elements (here we manually add one to this notebook):

[4]:
from IPython.display import HTML
style = \
"<style>"\
".table-cls {color: grey;}"\
".cls1 {background-color: red; color: white;}"\
".cls2 {background-color: blue; color: white;}"\
".cls3 {font-weight: bold; font-style: italic; font-size:1.8em}"\
"</style>"
HTML(style)
[4]:

Now we can manually link these to our DataFrame using the Styler.set_table_attributes and Styler.set_td_classes methods (note that table level ‘table-cls’ is overwritten here by Jupyters own CSS, but in HTML the default text color will be grey).

[5]:
css_classes = pd.DataFrame(data=[['cls1', None], ['cls3', 'cls2 cls3']], index=[0,2], columns=['A', 'C'])
df.style.\
    set_table_attributes('class="table-cls"').\
    set_td_classes(css_classes)
[5]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

The advantage of linking to external CSS is that it can be applied very easily. One can build a DataFrame of (multiple) CSS classes to add to each cell dynamically using traditional DataFrame.apply and DataFrame.applymap methods, or otherwise, and then add those to the Styler. It will integrate with your website’s existing CSS styling.

The disadvantage of this approach is that it is not easy to transmit files standalone. For example the external CSS must be included or the styling will simply be lost. It is also, as this example shows, not well suited (at a table level) for Jupyter Notebooks. Also this method cannot be used for exporting to Excel, for example, since the external CSS cannot be referenced either by the exporters or by Excel itself.

Using Table Styles

Table styles allow you to control broader areas of the DataFrame, i.e. the whole table or specific columns or rows, with minimal HTML transfer. Much of the functionality of Styler uses individual HTML id tags to manipulate the output, which may be inefficient for very large tables. Using table_styles and otherwise avoiding using id tags in data cells can greatly reduce the rendered HTML.

Table styles are also used to control features which can apply to the whole table at once such as greating a generic hover functionality. This :hover pseudo-selectors, as well as others, can only be used this way.

table_styles are extremely flexible, but not as fun to type out by hand. We hope to collect some useful ones either in pandas, or preferable in a new package that builds on top the tools here.

[6]:
def hover(hover_color="#ffff99"):
    return {'selector': "tr:hover",
            'props': [("background-color", "%s" % hover_color)]}

styles = [
    hover(),
    {'selector': "th", 'props': [("font-size", "150%"), ("text-align", "center")]}
]

df.style.set_table_styles(styles)
[6]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

If table_styles is given as a dictionary each key should be a specified column or index value and this will map to specific class CSS selectors of the given column or row.

[7]:
df.style.set_table_styles({
    'A': [{'selector': '',
           'props': [('color', 'red')]}],
    'B': [{'selector': 'td',
           'props': [('color', 'blue')]}]
}, axis=0)
[7]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720
[8]:
df.style.set_table_styles({
    3: [{'selector': 'td',
           'props': [('color', 'green')]}]
}, axis=1)
[8]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

We can also chain all of the above by setting the overwrite argument to False so that it preserves previous settings. We also show the CSS string input rather than the list of tuples.

[9]:
from pandas.io.formats.style import Styler
s = Styler(df, cell_ids=False, uuid_len=0).\
    set_table_styles(styles).\
    set_table_styles({
        'A': [{'selector': '',
               'props': 'color:red;'}],
        'B': [{'selector': 'td',
               'props': 'color:blue;'}]
    }, axis=0, overwrite=False).\
    set_table_styles({
        3: [{'selector': 'td',
             'props': 'color:green;font-weight:bold;'}]
    }, axis=1, overwrite=False)
s
[9]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

By using these table_styles and the additional Styler arguments to optimize the HTML we have compressed these styles to only a few lines withing the <style> tags and none of the <td> cells require any id attributes.

[10]:
s.render().split('\n')[:16]
[10]:
['<style type="text/css">',
 '#T__ tr:hover {',
 '  background-color: #ffff99;',
 '}',
 '#T__ th {',
 '  font-size: 150%;',
 '  text-align: center;',
 '}',
 '#T__ .col0 {',
 '  color: red;',
 '}',
 '#T__ td.col1 {',
 '  color: blue;',
 '}',
 '#T__ td.row3 {',
 '  color: green;']

The advantage of table styles is obviously the reduced HTML that it can create and the relative ease with which more general parts of the table can be quickly styled, e.g. by applying a generic hover, rather than having to apply a hover to each cell individually. Rows and columns as individual objects can only be styled in this way.

The disadvantage of being restricted solely to table styles is that you have very limited ability to target and style individual cells based on dynamic criteria. For this, one must use either of the other two methods. Also table level styles cannot be exported to Excel: to format cells for Excel output you must use the Styler Functions method below.

Styler Functions

Thirdly we can use the method to pass your style functions into one of the following methods:

  • Styler.applymap: elementwise

  • Styler.apply: column-/row-/table-wise

Both of those methods take a function (and some other keyword arguments) and applies your function to the DataFrame in a certain way. Styler.applymap works through the DataFrame elementwise. Styler.apply passes each column or row into your DataFrame one-at-a-time or the entire table at once, depending on the axis keyword argument. For columnwise use axis=0, rowwise use axis=1, and for the entire table at once use axis=None.

For Styler.applymap your function should take a scalar and return a single string with the CSS attribute-value pair.

For Styler.apply your function should take a Series or DataFrame (depending on the axis parameter), and return a Series or DataFrame with an identical shape where each value is a string with a CSS attribute-value pair.

The advantage of this method is that there is full granular control and the output is isolated and easily transferrable, especially in Jupyter Notebooks.

The disadvantage is that the HTML/CSS required to produce this needs to be directly generated from the Python code and it can lead to inefficient data transfer for large tables.

Let’s see some examples.

Let’s write a simple style function that will color negative numbers red and positive numbers black.

[11]:
def color_negative_red(val):
    """Color negative scalars red."""
    css = 'color: red;'
    if val < 0: return css
    return None

In this case, the cell’s style depends only on its own value. That means we should use the Styler.applymap method which works elementwise.

[12]:
s = df.style.applymap(color_negative_red)
s
[12]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Notice the similarity with the standard df.applymap, which operates on DataFrames elementwise. We want you to be able to reuse your existing knowledge of how to interact with DataFrames.

Notice also that our function returned a string containing the CSS attribute and value, separated by a colon just like in a <style> tag. This will be a common theme.

Finally, the input shapes matched. Styler.applymap calls the function on each scalar input, and the function returns a scalar output.

Now suppose you wanted to highlight the maximum value in each column. We can’t use .applymap anymore since that operated elementwise. Instead, we’ll turn to .apply which operates columnwise (or rowwise using the axis keyword). Later on we’ll see that something like highlight_max is already defined on Styler so you wouldn’t need to write this yourself.

[13]:
def highlight_max(s):
    """Highlight the maximum in a Series bold-orange."""
    css = 'background-color: orange; font-weight: bold;'
    return np.where(s == np.nanmax(s.values), css, None)
[14]:
df.style.apply(highlight_max)
[14]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720
[15]:
df.style.apply(highlight_max, axis=1)
[15]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

In this case the input is a Series, one column (or row) at a time. Notice that the output shape of highlight_max matches the input shape, an array with len(s) items.

A common use case is also to highlight values based on comparison between columns. Suppose we wish to highlight those cells in columns ‘B’ and ‘C’ which are lower than respective values in ‘E’ then we can write a comparator function. (You can read a little more below in ‘Finer Control: Slicing’)

[16]:
def compare_col(s, comparator=None):
    css = 'background-color: #00BFFF;'
    return np.where(s < comparator, css, None)
[17]:
df.style.apply(compare_col, subset=['B', 'C'], comparator=df['E'])
[17]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

We encourage you to use method chains to build up a style piecewise, before finally rending at the end of the chain. Note the ordering of application will affect styles that overlap.

[18]:
df.style.\
    apply(compare_col, subset=['B', 'C'], comparator=df['E']).\
    applymap(color_negative_red).\
    apply(highlight_max)
[18]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Above we used Styler.apply to pass in each column (or row) one at a time.

Debugging Tip: If you’re having trouble writing your style function, try just passing it into DataFrame.apply. Internally, Styler.apply uses DataFrame.apply so the result should be the same.

What if you wanted to highlight just the maximum value in the entire table? Use .apply(function, axis=None) to indicate that your function wants the entire table, not one column or row at a time. In this case the return must be a DataFrame or ndarray of the same shape as the input. Let’s try that next.

[19]:
s = df.style.apply(highlight_max, axis=None)
s
[19]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Building Styles Summary

Style functions should return strings with one or more CSS attribute: value delimited by semicolons. Use

  • Styler.applymap(func) for elementwise styles

  • Styler.apply(func, axis=0) for columnwise styles

  • Styler.apply(func, axis=1) for rowwise styles

  • Styler.apply(func, axis=None) for tablewise styles

And crucially the input and output shapes of func must match. If x is the input then func(x).shape == x.shape.

Tooltips

New in version 1.3.0

You can now add tooltips in the same way you can add external CSS classes to datacells by providing a string based DataFrame with intersecting indices and columns.

[20]:
tt = pd.DataFrame(data=[[None, 'No Data', None],
                     [None, None, 'Missing Data'],
                     ['Maximum value across entire DataFrame', None, None]],
                  index=[0, 3, 9],
                  columns=['A', 'C', 'D'])
s.set_tooltips(tt)
[20]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

The tooltips are added with a default CSS styling, however, you have full control of the tooltips in the following way. The name of the class can be integrated with your existing website’s CSS so you do not need to set any properties within Python if you have the external CSS files.

[21]:
s.set_tooltips_class(name='pd-tt', properties=[
    ('visibility', 'hidden'),
    ('position', 'absolute'),
    ('z-index', '1'),
    ('background-color', 'blue'),
    ('color', 'white'),
    ('font-size', '1.5em'),
    ('transform', 'translate(3px, -11px)'),
    ('padding', '0.5em'),
    ('border', '1px solid red'),
    ('border-radius', '0.5em')
])
[21]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Finer control: slicing

Both Styler.apply, and Styler.applymap accept a subset keyword. This allows you to apply styles to specific rows or columns, without having to code that logic into your style function.

The value passed to subset behaves similar to slicing a DataFrame.

  • A scalar is treated as a column label

  • A list (or series or numpy array)

  • A tuple is treated as (row_indexer, column_indexer)

Consider using pd.IndexSlice to construct the tuple for the last one.

[22]:
df.style.apply(highlight_max, subset=['B', 'C', 'D'])
[22]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

For row and column slicing, any valid indexer to .loc will work.

[23]:
df.style.applymap(color_negative_red,
                  subset=pd.IndexSlice[2:5, ['B', 'D']])
[23]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Only label-based slicing is supported right now, not positional.

If your style function uses a subset or axis keyword argument, consider wrapping your function in a functools.partial, partialing out that keyword.

my_func2 = functools.partial(my_func, subset=42)

Finer Control: Display Values

We distinguish the display value from the actual value in Styler. To control the display value, the text is printed in each cell, use Styler.format. Cells can be formatted according to a format spec string or a callable that takes a single value and returns a string.

[24]:
df.style.format("{:.2%}")
[24]:
A B C D E
0 100.00% 132.92% nan% -31.63% -99.08%
1 200.00% -107.08% -143.87% 56.44% 29.57%
2 300.00% -162.64% 21.96% 67.88% 188.93%
3 400.00% 96.15% 10.40% nan% 85.02%
4 500.00% 145.34% 105.77% 16.56% 51.50%
5 600.00% -133.69% 56.29% 139.29% -6.33%
6 700.00% 12.17% 120.76% -0.20% 162.78%
7 800.00% 35.45% 103.75% -38.57% 51.98%
8 900.00% 168.66% -132.60% 142.90% -208.94%
9 1000.00% -12.98% 63.15% -58.65% 29.07%

Use a dictionary to format specific columns.

[25]:
df.style.format({'B': "{:0<4.0f}", 'D': '{:+.2f}'})
[25]:
A B C D E
0 1.000000 1000 nan -0.32 -0.990810
1 2.000000 -100 -1.438713 +0.56 0.295722
2 3.000000 -200 0.219565 +0.68 1.889273
3 4.000000 1000 0.104011 +nan 0.850229
4 5.000000 1000 1.057737 +0.17 0.515018
5 6.000000 -100 0.562861 +1.39 -0.063328
6 7.000000 0000 1.207603 -0.00 1.627796
7 8.000000 0000 1.037528 -0.39 0.519818
8 9.000000 2000 -1.325963 +1.43 -2.089354
9 10.000000 -000 0.631523 -0.59 0.290720

Or pass in a callable (or dictionary of callables) for more flexible handling.

[26]:
df.style.format({"B": lambda x: {:.2f}".format(abs(x))})
[26]:
A B C D E
0 1.000000 ±1.33 nan -0.316280 -0.990810
1 2.000000 ±1.07 -1.438713 0.564417 0.295722
2 3.000000 ±1.63 0.219565 0.678805 1.889273
3 4.000000 ±0.96 0.104011 nan 0.850229
4 5.000000 ±1.45 1.057737 0.165562 0.515018
5 6.000000 ±1.34 0.562861 1.392855 -0.063328
6 7.000000 ±0.12 1.207603 -0.002040 1.627796
7 8.000000 ±0.35 1.037528 -0.385684 0.519818
8 9.000000 ±1.69 -1.325963 1.428984 -2.089354
9 10.000000 ±0.13 0.631523 -0.586538 0.290720

You can format the text displayed for missing values by na_rep.

[27]:
df.style.format("{:.2%}", na_rep="-")
[27]:
A B C D E
0 100.00% 132.92% - -31.63% -99.08%
1 200.00% -107.08% -143.87% 56.44% 29.57%
2 300.00% -162.64% 21.96% 67.88% 188.93%
3 400.00% 96.15% 10.40% - 85.02%
4 500.00% 145.34% 105.77% 16.56% 51.50%
5 600.00% -133.69% 56.29% 139.29% -6.33%
6 700.00% 12.17% 120.76% -0.20% 162.78%
7 800.00% 35.45% 103.75% -38.57% 51.98%
8 900.00% 168.66% -132.60% 142.90% -208.94%
9 1000.00% -12.98% 63.15% -58.65% 29.07%

These formatting techniques can be used in combination with styling.

[28]:
df.style.highlight_max().format(None, na_rep="-")
[28]:
A B C D E
0 1.000000 1.329212 - -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 - 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Builtin styles

Finally, we expect certain styling functions to be common enough that we’ve included a few “built-in” to the Styler, so you don’t have to write them yourself.

[29]:
df.style.highlight_null(null_color='red')
[29]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

You can create “heatmaps” with the background_gradient method. These require matplotlib, and we’ll use Seaborn to get a nice colormap.

[30]:
import seaborn as sns

cm = sns.light_palette("green", as_cmap=True)

s = df.style.background_gradient(cmap=cm)
s
[30]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Styler.background_gradient takes the keyword arguments low and high. Roughly speaking these extend the range of your data by low and high percent so that when we convert the colors, the colormap’s entire range isn’t used. This is useful so that you can actually read the text still.

[31]:
# Uses the full color range
df.loc[:4].style.background_gradient(cmap='viridis')
[31]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
[32]:
# Compress the color range
(df.loc[:4]
    .style
    .background_gradient(cmap='viridis', low=.5, high=0)
    .highlight_null('red'))
[32]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018

There’s also .highlight_min and .highlight_max.

[33]:
df.style.highlight_max(axis=0)
[33]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Use Styler.set_properties when the style doesn’t actually depend on the values.

[34]:
df.style.set_properties(**{'background-color': 'black',
                           'color': 'lawngreen',
                           'border-color': 'white'})
[34]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Bar charts

You can include “bar charts” in your DataFrame.

[35]:
df.style.bar(subset=['A', 'B'], color='#d65f5f')
[35]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

New in version 0.20.0 is the ability to customize further the bar chart: You can now have the df.style.bar be centered on zero or midpoint value (in addition to the already existing way of having the min value at the left side of the cell), and you can pass a list of [color_negative, color_positive].

Here’s how you can change the above with the new align='mid' option:

[36]:
df.style.bar(subset=['A', 'B'], align='mid', color=['#d65f5f', '#5fba7d'])
[36]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

The following example aims to give a highlight of the behavior of the new align options:

[37]:
import pandas as pd
from IPython.display import HTML

# Test series
test1 = pd.Series([-100,-60,-30,-20], name='All Negative')
test2 = pd.Series([10,20,50,100], name='All Positive')
test3 = pd.Series([-10,-5,0,90], name='Both Pos and Neg')

head = """
<table>
    <thead>
        <th>Align</th>
        <th>All Negative</th>
        <th>All Positive</th>
        <th>Both Neg and Pos</th>
    </thead>
    </tbody>

"""

aligns = ['left','zero','mid']
for align in aligns:
    row = "<tr><th>{}</th>".format(align)
    for series in [test1,test2,test3]:
        s = series.copy()
        s.name=''
        row += "<td>{}</td>".format(s.to_frame().style.bar(align=align,
                                                           color=['#d65f5f', '#5fba7d'],
                                                           width=100).render()) #testn['width']
    row += '</tr>'
    head += row

head+= """
</tbody>
</table>"""


HTML(head)
[37]:
Align All Negative All Positive Both Neg and Pos
left
0 -100
1 -60
2 -30
3 -20
0 10
1 20
2 50
3 100
0 -10
1 -5
2 0
3 90
zero
0 -100
1 -60
2 -30
3 -20
0 10
1 20
2 50
3 100
0 -10
1 -5
2 0
3 90
mid
0 -100
1 -60
2 -30
3 -20
0 10
1 20
2 50
3 100
0 -10
1 -5
2 0
3 90

Sharing styles

Say you have a lovely style built up for a DataFrame, and now you want to apply the same style to a second DataFrame. Export the style with df1.style.export, and import it on the second DataFrame with df1.style.set

[38]:
df2 = -df
style1 = df.style.applymap(color_negative_red)
style1
[38]:
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720
[39]:
style2 = df2.style
style2.use(style1.export())
style2
[39]:
A B C D E
0 -1.000000 -1.329212 nan 0.316280 0.990810
1 -2.000000 1.070816 1.438713 -0.564417 -0.295722
2 -3.000000 1.626404 -0.219565 -0.678805 -1.889273
3 -4.000000 -0.961538 -0.104011 nan -0.850229
4 -5.000000 -1.453425 -1.057737 -0.165562 -0.515018
5 -6.000000 1.336936 -0.562861 -1.392855 0.063328
6 -7.000000 -0.121668 -1.207603 0.002040 -1.627796
7 -8.000000 -0.354493 -1.037528 0.385684 -0.519818
8 -9.000000 -1.686583 1.325963 -1.428984 2.089354
9 -10.000000 0.129820 -0.631523 0.586538 -0.290720

Notice that you’re able to share the styles even though they’re data aware. The styles are re-evaluated on the new DataFrame they’ve been used upon.

Other Options

You’ve seen a few methods for data-driven styling. Styler also provides a few other options for styles that don’t depend on the data.

  • precision

  • captions

  • table-wide styles

  • missing values representation

  • hiding the index or columns

Each of these can be specified in two ways:

  • A keyword argument to Styler.__init__

  • A call to one of the .set_ or .hide_ methods, e.g. .set_caption or .hide_columns

The best method to use depends on the context. Use the Styler constructor when building many styled DataFrames that should all share the same properties. For interactive use, the.set_ and .hide_ methods are more convenient.

Precision

You can control the precision of floats using pandas’ regular display.precision option.

[40]:
with pd.option_context('display.precision', 2):
    html = (df.style
              .applymap(color_negative_red)
              .apply(highlight_max))
html
[40]:
A B C D E
0 1.00 1.33 nan -0.32 -0.99
1 2.00 -1.07 -1.44 0.56 0.30
2 3.00 -1.63 0.22 0.68 1.89
3 4.00 0.96 0.10 nan 0.85
4 5.00 1.45 1.06 0.17 0.52
5 6.00 -1.34 0.56 1.39 -0.06
6 7.00 0.12 1.21 -0.00 1.63
7 8.00 0.35 1.04 -0.39 0.52
8 9.00 1.69 -1.33 1.43 -2.09
9 10.00 -0.13 0.63 -0.59 0.29

Or through a set_precision method.

[41]:
df.style\
  .applymap(color_negative_red)\
  .apply(highlight_max)\
  .set_precision(2)
[41]:
A B C D E
0 1.00 1.33 nan -0.32 -0.99
1 2.00 -1.07 -1.44 0.56 0.30
2 3.00 -1.63 0.22 0.68 1.89
3 4.00 0.96 0.10 nan 0.85
4 5.00 1.45 1.06 0.17 0.52
5 6.00 -1.34 0.56 1.39 -0.06
6 7.00 0.12 1.21 -0.00 1.63
7 8.00 0.35 1.04 -0.39 0.52
8 9.00 1.69 -1.33 1.43 -2.09
9 10.00 -0.13 0.63 -0.59 0.29

Setting the precision only affects the printed number; the full-precision values are always passed to your style functions. You can always use df.round(2).style if you’d prefer to round from the start.

Captions

Regular table captions can be added and, if necessary, controlled with CSS.

[42]:
df.style.set_caption('Colormaps, with a caption.')\
    .set_table_styles([{
        'selector': "caption", 'props': [("caption-side", "bottom")]
    }])\
    .background_gradient(cmap=cm)
[42]:
Colormaps, with a caption.
A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Missing values

You can control the default missing values representation for the entire table through set_na_rep method.

[43]:
(df.style
   .set_na_rep("FAIL")
   .format(None, na_rep="PASS", subset=["D"])
   .highlight_null("yellow"))
[43]:
A B C D E
0 1.000000 1.329212 FAIL -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 PASS 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Hiding the Index or Columns

The index can be hidden from rendering by calling Styler.hide_index. Columns can be hidden from rendering by calling Styler.hide_columns and passing in the name of a column, or a slice of columns.

[44]:
df.style.hide_index()
[44]:
A B C D E
1.000000 1.329212 nan -0.316280 -0.990810
2.000000 -1.070816 -1.438713 0.564417 0.295722
3.000000 -1.626404 0.219565 0.678805 1.889273
4.000000 0.961538 0.104011 nan 0.850229
5.000000 1.453425 1.057737 0.165562 0.515018
6.000000 -1.336936 0.562861 1.392855 -0.063328
7.000000 0.121668 1.207603 -0.002040 1.627796
8.000000 0.354493 1.037528 -0.385684 0.519818
9.000000 1.686583 -1.325963 1.428984 -2.089354
10.000000 -0.129820 0.631523 -0.586538 0.290720
[45]:
df.style.hide_columns(['C','D'])
[45]:
A B E
0 1.000000 1.329212 -0.990810
1 2.000000 -1.070816 0.295722
2 3.000000 -1.626404 1.889273
3 4.000000 0.961538 0.850229
4 5.000000 1.453425 0.515018
5 6.000000 -1.336936 -0.063328
6 7.000000 0.121668 1.627796
7 8.000000 0.354493 0.519818
8 9.000000 1.686583 -2.089354
9 10.000000 -0.129820 0.290720

CSS classes

Certain CSS classes are attached to cells.

  • Index and Column names include index_name and level<k> where k is its level in a MultiIndex

  • Index label cells include

    • row_heading

    • row<n> where n is the numeric position of the row

    • level<k> where k is the level in a MultiIndex

  • Column label cells include

    • col_heading

    • col<n> where n is the numeric position of the column

    • level<k> where k is the level in a MultiIndex

  • Blank cells include blank

  • Data cells include data

Limitations

  • DataFrame only (use Series.to_frame().style)

  • The index and columns must be unique

  • No large repr, and performance isn’t great; this is intended for summary DataFrames

  • You can only style the values, not the index or columns (except with table_styles above)

  • You can only apply styles, you can’t insert new HTML entities

Some of these will be addressed in the future. Performance can suffer when adding styles to each cell in a large DataFrame. It is recommended to apply table or column based styles where possible to limit overall HTML length, as well as setting a shorter UUID to avoid unnecessary repeated data transmission.

Terms

  • Style function: a function that’s passed into Styler.apply or Styler.applymap and returns values like 'css attribute: value'

  • Builtin style functions: style functions that are methods on Styler

  • table style: a dictionary with the two keys selector and props. selector is the CSS selector that props will apply to. props is a list of (attribute, value) tuples. A list of table styles passed into Styler.

Fun stuff

Here are a few interesting examples.

Styler interacts pretty well with widgets. If you’re viewing this online instead of running the notebook yourself, you’re missing out on interactively adjusting the color palette.

[46]:
from ipywidgets import widgets
@widgets.interact
def f(h_neg=(0, 359, 1), h_pos=(0, 359), s=(0., 99.9), l=(0., 99.9)):
    return df.style.background_gradient(
        cmap=sns.palettes.diverging_palette(h_neg=h_neg, h_pos=h_pos, s=s, l=l,
                                            as_cmap=True)
    )
[47]:
def magnify():
    return [dict(selector="th",
                 props=[("font-size", "4pt")]),
            dict(selector="td",
                 props=[('padding', "0em 0em")]),
            dict(selector="th:hover",
                 props=[("font-size", "12pt")]),
            dict(selector="tr:hover td:hover",
                 props=[('max-width', '200px'),
                        ('font-size', '12pt')])
]
[48]:
np.random.seed(25)
cmap = cmap=sns.diverging_palette(5, 250, as_cmap=True)
bigdf = pd.DataFrame(np.random.randn(20, 25)).cumsum()

bigdf.style.background_gradient(cmap, axis=1)\
    .set_properties(**{'max-width': '80px', 'font-size': '1pt'})\
    .set_caption("Hover to magnify")\
    .set_precision(2)\
    .set_table_styles(magnify())
[48]:
Hover to magnify
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
0 0.23 1.03 -0.84 -0.59 -0.96 -0.22 -0.62 1.84 -2.05 0.87 -0.92 -0.23 2.15 -1.33 0.08 -1.25 1.20 -1.05 1.06 -0.42 2.29 -2.59 2.82 0.68 -1.58
1 -1.75 1.56 -1.13 -1.10 1.03 0.00 -2.46 3.45 -1.66 1.27 -0.52 -0.02 1.52 -1.09 -1.86 -1.13 -0.68 -0.81 0.35 -0.06 1.79 -2.82 2.26 0.78 0.44
2 -0.65 3.22 -1.76 0.52 2.20 -0.37 -3.00 3.73 -1.87 2.46 0.21 -0.24 -0.10 -0.78 -3.02 -0.82 -0.21 -0.23 0.86 -0.68 1.45 -4.89 3.03 1.91 0.61
3 -1.62 3.71 -2.31 0.43 4.17 -0.43 -3.86 4.16 -2.15 1.08 0.12 0.60 -0.89 0.27 -3.67 -2.71 -0.31 -1.59 1.35 -1.83 0.91 -5.80 2.81 2.11 0.28
4 -3.35 4.48 -1.86 -1.70 5.19 -1.02 -3.81 4.72 -0.72 1.08 -0.18 0.83 -0.22 -1.08 -4.27 -2.88 -0.97 -1.78 1.53 -1.80 2.21 -6.34 3.34 2.49 2.09
5 -0.84 4.23 -1.65 -2.00 5.34 -0.99 -4.13 3.94 -1.06 -0.94 1.24 0.09 -1.78 -0.11 -4.45 -0.85 -2.06 -1.35 0.80 -1.63 1.54 -6.51 2.80 2.14 3.77
6 -0.74 5.35 -2.11 -1.13 4.20 -1.85 -3.20 3.76 -3.22 -1.23 0.34 0.57 -1.82 0.54 -4.43 -1.83 -4.03 -2.62 -0.20 -4.68 1.93 -8.46 3.34 2.52 5.81
7 -0.44 4.69 -2.30 -0.21 5.93 -2.63 -1.83 5.46 -4.50 -3.16 -1.73 0.18 0.11 0.04 -5.99 -0.45 -6.20 -3.89 0.71 -3.95 0.67 -7.26 2.97 3.39 6.66
8 0.92 5.80 -3.33 -0.65 5.99 -3.19 -1.83 5.63 -3.53 -1.30 -1.61 0.82 -2.45 -0.40 -6.06 -0.52 -6.60 -3.48 -0.04 -4.60 0.51 -5.85 3.23 2.40 5.08
9 0.38 5.54 -4.49 -0.80 7.05 -2.64 -0.44 5.35 -1.96 -0.33 -0.80 0.26 -3.37 -0.82 -6.05 -2.61 -8.45 -4.45 0.41 -4.71 1.89 -6.93 2.14 3.00 5.16
10 2.06 5.84 -3.90 -0.98 7.78 -2.49 -0.59 5.59 -2.22 -0.71 -0.46 1.80 -2.79 0.48 -5.97 -3.44 -7.77 -5.49 -0.70 -4.61 -0.52 -7.72 1.54 5.02 5.81
11 1.86 4.47 -2.17 -1.38 5.90 -0.49 0.02 5.78 -1.04 -0.60 0.49 1.96 -1.47 1.88 -5.92 -4.55 -8.15 -3.42 -2.24 -4.33 -1.17 -7.90 1.36 5.31 5.83
12 3.19 4.22 -3.06 -2.27 5.93 -2.64 0.33 6.72 -2.84 -0.20 1.89 2.63 -1.53 0.75 -5.27 -4.53 -7.57 -2.85 -2.17 -4.78 -1.13 -8.99 2.11 6.42 5.60
13 2.31 4.45 -3.87 -2.05 6.76 -3.25 -2.17 7.99 -2.56 -0.80 0.71 2.33 -0.16 -0.46 -5.10 -3.79 -7.58 -4.00 0.33 -3.67 -1.05 -8.71 2.47 5.87 6.71
14 3.78 4.33 -3.88 -1.58 6.22 -3.23 -1.46 5.57 -2.93 -0.33 -0.97 1.72 3.61 0.29 -4.21 -4.10 -6.68 -4.50 -2.19 -2.43 -1.64 -9.36 3.36 6.11 7.53
15 5.64 5.31 -3.98 -2.26 5.91 -3.30 -1.03 5.68 -3.06 -0.33 -1.16 2.19 4.20 1.01 -3.22 -4.31 -5.74 -4.44 -2.30 -1.36 -1.20 -11.27 2.59 6.69 5.91
16 4.08 4.34 -2.44 -3.30 6.04 -2.52 -0.47 5.28 -4.84 1.58 0.23 0.10 5.79 1.80 -3.13 -3.85 -5.53 -2.97 -2.13 -1.15 -0.56 -13.13 2.07 6.16 4.94
17 5.64 4.57 -3.53 -3.76 6.58 -2.58 -0.75 6.58 -4.78 3.63 -0.29 0.56 5.76 2.05 -2.27 -2.31 -4.95 -3.16 -3.06 -2.43 0.84 -12.57 3.56 7.36 4.70
18 5.99 5.82 -2.85 -4.15 7.12 -3.32 -1.21 7.93 -4.85 1.44 -0.63 0.35 7.47 0.87 -1.52 -2.09 -4.23 -2.55 -2.46 -2.89 1.90 -9.74 3.43 7.07 4.39
19 4.03 6.23 -4.10 -4.11 7.19 -4.10 -1.52 6.53 -5.21 -0.24 0.01 1.16 6.43 -1.97 -2.64 -1.66 -5.20 -3.25 -2.87 -1.65 1.64 -10.66 2.83 7.48 3.94

Export to Excel

New in version 0.20.0

Experimental: This is a new feature and still under development. We’ll be adding features and possibly making breaking changes in future releases. We’d love to hear your feedback.

Some support is available for exporting styled DataFrames to Excel worksheets using the OpenPyXL or XlsxWriter engines. CSS2.2 properties handled include:

  • background-color

  • border-style, border-width, border-color and their {top, right, bottom, left variants}

  • color

  • font-family

  • font-style

  • font-weight

  • text-align

  • text-decoration

  • vertical-align

  • white-space: nowrap

  • Only CSS2 named colors and hex colors of the form #rgb or #rrggbb are currently supported.

  • The following pseudo CSS properties are also available to set excel specific style properties:

    • number-format

Table level styles are not included in the export to Excel: individual cells must have their properties mapped by the Styler.apply and/or Styler.applymap methods.

[49]:
df.style.\
    applymap(color_negative_red).\
    apply(highlight_max).\
    to_excel('styled.xlsx', engine='openpyxl')

A screenshot of the output:

Excel spreadsheet with styled DataFrame

Extensibility

The core of pandas is, and will remain, its “high-performance, easy-to-use data structures”. With that in mind, we hope that DataFrame.style accomplishes two goals

  • Provide an API that is pleasing to use interactively and is “good enough” for many tasks

  • Provide the foundations for dedicated libraries to build on

If you build a great library on top of this, let us know and we’ll link to it.

Subclassing

If the default template doesn’t quite suit your needs, you can subclass Styler and extend or override the template. We’ll show an example of extending the default template to insert a custom header before each table.

[50]:
from jinja2 import Environment, ChoiceLoader, FileSystemLoader
from IPython.display import HTML
from pandas.io.formats.style import Styler

We’ll use the following template:

[51]:
with open("templates/myhtml.tpl") as f:
    print(f.read())
{% extends "html.tpl" %}
{% block table %}
<h1>{{ table_title|default("My Table") }}</h1>
{{ super() }}
{% endblock table %}

Now that we’ve created a template, we need to set up a subclass of Styler that knows about it.

[52]:
class MyStyler(Styler):
    env = Environment(
        loader=ChoiceLoader([
            FileSystemLoader("templates"),  # contains ours
            Styler.loader,  # the default
        ])
    )
    template = env.get_template("myhtml.tpl")

Notice that we include the original loader in our environment’s loader. That’s because we extend the original template, so the Jinja environment needs to be able to find it.

Now we can use that custom styler. It’s __init__ takes a DataFrame.

[53]:
MyStyler(df)
[53]:

My Table

A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Our custom template accepts a table_title keyword. We can provide the value in the .render method.

[54]:
HTML(MyStyler(df).render(table_title="Extending Example"))
[54]:

Extending Example

A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

For convenience, we provide the Styler.from_custom_template method that does the same as the custom subclass.

[55]:
EasyStyler = Styler.from_custom_template("templates", "myhtml.tpl")
EasyStyler(df)
[55]:

My Table

A B C D E
0 1.000000 1.329212 nan -0.316280 -0.990810
1 2.000000 -1.070816 -1.438713 0.564417 0.295722
2 3.000000 -1.626404 0.219565 0.678805 1.889273
3 4.000000 0.961538 0.104011 nan 0.850229
4 5.000000 1.453425 1.057737 0.165562 0.515018
5 6.000000 -1.336936 0.562861 1.392855 -0.063328
6 7.000000 0.121668 1.207603 -0.002040 1.627796
7 8.000000 0.354493 1.037528 -0.385684 0.519818
8 9.000000 1.686583 -1.325963 1.428984 -2.089354
9 10.000000 -0.129820 0.631523 -0.586538 0.290720

Here’s the template structure:

[56]:
with open("templates/template_structure.html") as f:
    structure = f.read()

HTML(structure)
[56]:
before_style
style
<style type="text/css">
table_styles
before_cellstyle
cellstyle
</style>
before_table
table
<table ...>
caption
thead
before_head_rows
head_tr (loop over headers)
after_head_rows
tbody
before_rows
tr (loop over data rows)
after_rows
</table>
after_table

See the template in the GitHub repo for more details.