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