How do I apply css formatting to a pd.DataFrame and not display index column

Question

Put simply, my goal is to display a table in my html page that looks 'normal' (aka all headers are on a single line, there are no line numbers) and each cell is color-coded according to a rule lookup (need flexibility to look up a different rule for each cell.) My data is being stored in a pd.DataFrame object. Sometimes my data is better expressed as a pivot table (multiple indices.) The formatting is a end-user requirement. File system access is very limited and all of this is being multi-threaded, so I don't really have the option to write the html then read-modify-write.

Some info on operating environment:

Python 3.5.2
Pandas version 0.19.2
needs to run on both x86 and x64
needs to run on both windows and linux

Sample setup:

import pandas as pd
import numpy as np
df = pd.DataFrame()
df['special_col_1'] = pd.Series([0,0,0,1,1,1])
df['special_col_2'] = pd.Series([0,1,2,0,1,2])
df['a'] = pd.Series(np.random.randn(6)).apply(lambda x: int(x*100))
df['b'] = pd.Series(np.random.randn(6)).apply(lambda x: int(x*100))
df['c'] = pd.Series(np.random.randn(6)).apply(lambda x: int(x*100))

basic_limits = {'a': lambda x: 'background-color: red' if x < 0 else 'background-color: green',\
                'b': lambda x: 'background-color: red' if x < 10 else 'background-color: green', \
                'c': lambda x: 'background-color: red' if x > 20 else 'background-color: green', \
                'special_col_1': lambda x: 'background-color: white', \
                'special_col_2': lambda x: 'background-color: white'}

Examples of what I've tried followed by why I don't like them:

df.to_html(index=False)

everything is laid out correctly
can't find any way to apply css for each cell

pd.pivot_table(data=df, columns=['special_col_1', 'special_col_2']).to_html(index=False)

incorrect layout, first row should contain all labels
can't find any way to apply css for each cell

dfs = df.style
for col in df.columns:
    dfs.applymap(basic_limits[col], subset=col)

incorrect layout - shouldn't have row numbers (index) but can't find any way to drop the index once you've gone from pd.DataFrame to pd.DataFrame.Styler
can correctly apply css styling using a dict of lambda functions
can be modified to iterate over both row and column and applymap() will still work (requires a more complicated multi-level dict of lambda functions)

df.pivot_table(data=df, columns=???).style(...)

specifying ['special_col_1', 'special_col_2'] breaks the formatting of the top row again
not specifying any columns is not acceptable ('ValueError: No group keys passed!')
has all the benefits and drawbacks of being a pd.DataFrame.Styler object

Any help would be greatly appreciated! Sorry I can't include images of the tables, this is my first post.

Confirmed Working Answer (piRSquared) gives the flexibility I was looking for:

df = pd.DataFrame()
dfs = df.style

#iterate through dataframe's columns, applying custom function to each cell in the column
for col in df.columns: 
    dfs.applymap(cell_based_function_dict[col], subset=col)

#apply a function to each column
col_based_function = lambda c: pd.Series(['background-color: white' if c.name in ['special_col_1', 'special_col_2'] else 'background-color: red' if x == c.max() else 'background-color: orange' if x > 0 else 'background-color: green' for x in c])
dfs.apply(col_based_function)

#define css specifics for this particular table
styler.set_table_styles([\
    {'selector': 'tr :first-child', 'props': [('display', 'none')]}, \
    {'selector': 'tr:hover td', 'props': [('background-color',  'yellow')]}, \
    {'selector': 'th, td', 'props': [('border', '1px solid black'), \
                                     ('padding', '4px'), \
                                     ('text-align', 'center')]}, \
    {'selector': 'th', 'props': [('font-weight', 'bold')]}, \
    {'selector': '', 'props': [('border-collapse', 'collapse'),\
                               ('border', '1px solid black')]} \
    ])

#all the styling appears to be tied only to this particular dataframe table, so you can easily call render() on different tables of different styles
dfs.render()

Please try to be more concise and make the question more readable, otherwise people will skip it ;) — phaberest
– phaberest, Commented Aug 1, 2017 at 22:17
To reword - I have 2 requirements and can't find a way to have both: (1) all columns must have a label in the first row; (2) must be able to modify css for each cell in the table. — Jigl
– Jigl, Commented Aug 1, 2017 at 22:59

piRSquared · Accepted Answer · 2017-08-01 23:25:00Z

2

You can use pd.DataFrame.style.set_table_styles

We can keep using the same styler object and updating it.

dfs = df.style
for col in df.columns:
    dfs.applymap(basic_limits[col], subset=col)

dfs.set_table_styles([
    {'selector': 'tr :first-child',
     'props': [('display', 'none')]}
])

You can suppress the output with a ; and still get the html

dfs = df.style
for col in df.columns:
    dfs.applymap(basic_limits[col], subset=col)

dfs.set_table_styles([
    {'selector': 'tr :first-child',
     'props': [('display', 'none')]}
]);

my_html = dfs.render()

edited Aug 1, 2017 at 23:25

answered Aug 1, 2017 at 22:52

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jigl Over a year ago

This is similar to MedAli's answer in that it's the general css style of the table ... how can I modify each cell's style using this method?

piRSquared Over a year ago

Updated my post

Mohamed Ali JAMAOUI · Accepted Answer · 2017-08-01 22:36:01Z

You can add CSS classes to the html generated from df.to_html as explained in the documentation here:

https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_html.html

Here's the part of the documentation explaining the use of classes

classes : str or list or tuple, default None CSS class(es) to apply to the resulting html table

So, for this sample code:

import pandas as pd 

df = pd.DataFrame() 
df['special_col_1'] = pd.Series([0,0,0,1,1,1])
df['special_col_2'] = pd.Series([0,1,2,0,1,2])

print(df.head())

df.to_html("my_html_file.html", index=False)

You would get the following html:

<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>special_col_1</th>
      <th>special_col_2</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <td>0</td>
      <td>1</td>
    </tr>
    <tr>
      <td>0</td>
      <td>2</td>
    </tr>
    <tr>
      <td>1</td>
      <td>0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>1</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
    </tr>
  </tbody>
</table>

Now, If you would like to style this further, you would need to wrap all the generated html with appropriate css classes into an html page and define the styling you want in CSS.

Here's a styling example using the materializecss library from here http://materializecss.com/table.html

In you code you would add the css classes table and striped as follows:

import pandas as pd 

df = pd.DataFrame() 
df['special_col_1'] = pd.Series([0,0,0,1,1,1])
df['special_col_2'] = pd.Series([0,1,2,0,1,2])

print(df.head())

# using classes table and striped from materializecss library 
df.to_html("my_html_file.html", classes="table striped", index=False)

The output html need to be wrapped into a html page structure, in which we call the materializecss library that holds the CSS code:

<!DOCTYPE html>
<html>
<head>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/0.100.1/css/materialize.min.css">
</head>
<body>
<table border="1" class="dataframe table striped">
  <thead>
    <tr style="text-align: right;">
      <th>special_col_1</th>
      <th>special_col_2</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <td>0</td>
      <td>1</td>
    </tr>
    <tr>
      <td>0</td>
      <td>2</td>
    </tr>
    <tr>
      <td>1</td>
      <td>0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>1</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
    </tr>
  </tbody>
</table>
</body>
</html>

Run snippet to see how the table is styled.

I don't see how using the 'classes' variable in to_html() answers my question. That's changing the look of the table as a whole.. I am able to color each cell correctly by sacrificing the requirement that the first row contain all the labels OR I can use the to_html() function to display the table properly but then I'm unable to access each individual cell in the table.
I'm having trouble from your example seeing how to import the css classes so that Pandas utilizes them when using the "classes" option. Can you please provide a little detail on the process to make the classes available to Pandas from materializecss?

Collectives™ on Stack Overflow

How do I apply css formatting to a pd.DataFrame and not display index column

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related