pythonpandasgoogle-chromemicrosoft-edgepandas-styles
Output of
pandas styling doesn't display for all rows in large dataframes in Chrome or Edge
Update:
The issue seems to be with displaying the HTML with styling rendered by pandas in Google Chrome and Microsoft Edge.
JupyterLab in Firefox correctly displays all of the styled rows and correctly renders an output HTML file.
The updated questions are
- Why doesn't the HTML rendered by pandas completely display all the styling in Chrome or Edge?
- Is there a more efficient way to apply the styling done by pandas so the HTML also works in Chrome and Edge?
Versions:
pandas v1.2.4Chrome v90.0.4430.93 (Official Build) (64-bit)Edge v90.0.818.56 (Official build) (64-bit)
Original:
- Questions - they are interrelated:
- Why aren't all of the rows displaying the background styling in Jupyter or writing to HTML?
- All rows should have green styling, but the last 5 do not display the styling.
- How can all of the rows be made to display the background styling?
- Why aren't all of the rows displaying the background styling in Jupyter or writing to HTML?
- Given a large dataframe, in this case
474 rows x 35 columns, the applied styling stops displaying. - If the number of rows or columns increases beyond this size, then more rows aren't displayed.
- We can see from the styling map, that the rows are correctly mapped with a background color, but it isn't displayed.
- If the number of rows or columns is reduced, then all of the rows display the correct styling.
- Tested in
jupyterlab v3.0.11 - Tested in
PyCharm 2021.1 (Professional Edition) Build #PY-211.6693.115, built on April 6, 2021saving the redendered styler to a file has the same result, so this isn't just an issue withjupyter. - Tested in the console
- This issue is reproducible on two different systems, that I have tried.
- If the shape is reduced to
471 rows × 35 columnsor474 rows × 34 columns, then all rows correctly display the highlighting. - Associated pandas bug report: 40913
Reproducible DataFrame
import pandas as pd
import numpy as np
from faker import Faker # conda install -c conda-forge faker or pip install Faker
# for fake names
fake = Faker()
# test data
np.random.seed(365)
rows = 11000
# change 36 or 158 to test where the rows stop appearing
vals = {f'val{i}': np.random.randint(1, 11, size=(rows)) for i in range(1, 36)}
data = {'name': np.random.choice([fake.unique.name() for i in range(158)], size=rows),
'cat': np.random.randint(1, 4, size=(rows))}
data.update(vals)
df = pd.DataFrame(data)
# used to create the mask for the background color
mean = df.groupby('cat').mean().round(2)
# calculate the mean for each name and cat
cat_mean = df.groupby(['name', 'cat']).mean()
def color(x):
"""Function to apply background color"""
c1 = 'background-color: green'
c = ''
# compare columns
mask1 = x.gt(mean)
# DataFrame with same index and columns names as original filled empty strings
df1 = pd.DataFrame(c, index=x.index, columns=x.columns)
# modify values of df1 column by boolean mask
df1[mask1] = c1
display(df1)
return df1
# Last line in notebook displays the styled dataframe
cat_mean.style.apply(color, axis=None)
# Last line in PyCharm saving rendered styler to file - comment out when in Jupyter
cm = cat_mean.style.apply(color, axis=None).set_precision(3).render()
# save the output to an html file
with open('cm_test.html', 'w') as f:
f.write(cm)
Reference
- This answer was referenced for the function to apply the background color.
- This question is similar, but has no answer.
Output of pd.show_versions()
- All packages that were
None, aren't shown to conserve space
INSTALLED VERSIONS
------------------
commit : f2c8480af2f25efdbd803218b9d87980f416563e
python : 3.8.8.final.0 or 3.9.2.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19041
machine : AMD64
processor : Intel64 Family 6 Model 60 Stepping 3, GenuineIntel
byteorder : little
LOCALE : English_United States.1252
pandas : 1.2.3 or 1.2.4
numpy : 1.19.2
pytz : 2021.1
dateutil : 2.8.1
pip : 21.0.1
setuptools : 52.0.0.post20210125
Cython : 0.29.22
pytest : 6.2.3
sphinx : 3.5.3
xlsxwriter : 1.3.8
lxml.etree : 4.6.3
html5lib : 1.1
jinja2 : 2.11.3
IPython : 7.22.0
pandas_datareader: 0.9.0
bs4 : 4.9.3
bottleneck : 1.3.2
fsspec : 0.9.0
matplotlib : 3.3.4
numexpr : 2.7.3
openpyxl : 3.0.7
scipy : 1.6.2
sqlalchemy : 1.4.5
tables : 3.6.1
tabulate : 0.8.9
xlrd : 2.0.1
xlwt : 1.3.0
numba : 0.53.1
Workaround
Split DataFrame
- It's dissatisfying, but splitting the DataFrame and applying the style will work, since if reduces the overall size.
cat_mean.iloc[:237, :].style.apply(color, axis=None)
cat_mean.iloc[237:, :].style.apply(color, axis=None)
Save to Excel
- All rows are displayed correctly with the highlight color when saving to Excel
test = cat_mean.style.apply(color, axis=None)
test.to_excel('test.xlsx', engine='openpyxl')
Solution
Update
- As of
pandas 1.3.0, the following implementation correctly highlights all of the expected columns and rows for the(474, 35)test data in the OP.- It was also successfully tested with a dataframe of shape
(7760, 35).
- It was also successfully tested with a dataframe of shape
- This implementation is from pandas Table Visualization User Guide: Set classes instead of using Styler functions
# also use code previous to list line for the test data
# calculate the mean for each name and cat
cat_mean = df.groupby(['name', 'cat']).mean()
def test(s, props=''):
t = np.where(s.gt(mean[s.name]), props, '')
return t
build = lambda x: pd.DataFrame(x, index=cat_mean.index, columns=cat_mean.columns)
cls1 = build(cat_mean.apply(test, props='cls-1 ', axis=0))
test = cat_mean.style.set_table_styles([{'selector': '.cls-1', 'props': [('color', 'white'), ('background-color', 'darkblue')]}]).set_td_classes(cls1)
# save the output to an html file
with open('cm_test.html', 'w') as f:
f.write(test.render())
- Output of working implementation
- Output of implementation in OP
Answering: Why doesn't this work in Chrome or Edge?
- Per bug #39400, the issue occurs for large DataFrames because Styler puts all CSS ids on a single attribute, which are not resolved be all browsers.
- In the following small snippet, see that all the ids are at the top.
- The snippet ids are for 5 rows and 35 columns, though only data for 1 table row is included.
<style type="text/css" >
#T__row0_col0,#T__row0_col1,#T__row0_col4,#T__row0_col5,#T__row0_col6,#T__row0_col11,#T__row0_col12,#T__row0_col13,#T__row0_col16,#T__row0_col19,#T__row0_col20,#T__row0_col22,#T__row0_col23,#T__row0_col24,#T__row0_col26,#T__row0_col27,#T__row0_col28,#T__row0_col30,#T__row0_col33,#T__row0_col34,#T__row1_col0,#T__row1_col1,#T__row1_col4,#T__row1_col8,#T__row1_col9,#T__row1_col11,#T__row1_col13,#T__row1_col15,#T__row1_col17,#T__row1_col21,#T__row1_col22,#T__row1_col26,#T__row1_col31,#T__row1_col34,#T__row2_col0,#T__row2_col1,#T__row2_col3,#T__row2_col7,#T__row2_col8,#T__row2_col9,#T__row2_col10,#T__row2_col12,#T__row2_col13,#T__row2_col16,#T__row2_col19,#T__row2_col20,#T__row2_col24,#T__row2_col26,#T__row2_col27,#T__row2_col28,#T__row2_col30,#T__row2_col32,#T__row3_col0,#T__row3_col3,#T__row3_col4,#T__row3_col6,#T__row3_col7,#T__row3_col8,#T__row3_col9,#T__row3_col15,#T__row3_col17,#T__row3_col20,#T__row3_col22,#T__row3_col27,#T__row3_col28,#T__row3_col29,#T__row3_col31,#T__row3_col34,#T__row4_col0,#T__row4_col1,#T__row4_col2,#T__row4_col3,#T__row4_col4,#T__row4_col6,#T__row4_col8,#T__row4_col14,#T__row4_col16,#T__row4_col17,#T__row4_col18,#T__row4_col19,#T__row4_col22,#T__row4_col23,#T__row4_col25,#T__row4_col27,#T__row4_col29,#T__row4_col32,#T__row4_col33{
text-align: center;
}#T__row0_col2,#T__row0_col3,#T__row0_col7,#T__row0_col8,#T__row0_col9,#T__row0_col10,#T__row0_col14,#T__row0_col15,#T__row0_col17,#T__row0_col18,#T__row0_col21,#T__row0_col25,#T__row0_col29,#T__row0_col31,#T__row0_col32,#T__row1_col2,#T__row1_col3,#T__row1_col5,#T__row1_col6,#T__row1_col7,#T__row1_col10,#T__row1_col12,#T__row1_col14,#T__row1_col16,#T__row1_col18,#T__row1_col19,#T__row1_col20,#T__row1_col23,#T__row1_col24,#T__row1_col25,#T__row1_col27,#T__row1_col28,#T__row1_col29,#T__row1_col30,#T__row1_col32,#T__row1_col33,#T__row2_col2,#T__row2_col4,#T__row2_col5,#T__row2_col6,#T__row2_col11,#T__row2_col14,#T__row2_col15,#T__row2_col17,#T__row2_col18,#T__row2_col21,#T__row2_col22,#T__row2_col23,#T__row2_col25,#T__row2_col29,#T__row2_col31,#T__row2_col33,#T__row2_col34,#T__row3_col1,#T__row3_col2,#T__row3_col5,#T__row3_col10,#T__row3_col11,#T__row3_col12,#T__row3_col13,#T__row3_col14,#T__row3_col16,#T__row3_col18,#T__row3_col19,#T__row3_col21,#T__row3_col23,#T__row3_col24,#T__row3_col25,#T__row3_col26,#T__row3_col30,#T__row3_col32,#T__row3_col33,#T__row4_col5,#T__row4_col7,#T__row4_col9,#T__row4_col10,#T__row4_col11,#T__row4_col12,#T__row4_col13,#T__row4_col15,#T__row4_col20,#T__row4_col21,#T__row4_col24,#T__row4_col26,#T__row4_col28,#T__row4_col30,#T__row4_col31,#T__row4_col34{
background-color: green;
text-align: center;
}</style><table id="T__" ><thead> <tr> <th class="blank" ></th> <th class="blank level0" ></th> <th class="col_heading level0 col0" >val1</th> <th class="col_heading level0 col1" >val2</th> <th class="col_heading level0 col2" >val3</th> <th class="col_heading level0 col3" >val4</th> <th class="col_heading level0 col4" >val5</th> <th class="col_heading level0 col5" >val6</th> <th class="col_heading level0 col6" >val7</th> <th class="col_heading level0 col7" >val8</th> <th class="col_heading level0 col8" >val9</th> <th class="col_heading level0 col9" >val10</th> <th class="col_heading level0 col10" >val11</th> <th class="col_heading level0 col11" >val12</th> <th class="col_heading level0 col12" >val13</th> <th class="col_heading level0 col13" >val14</th> <th class="col_heading level0 col14" >val15</th> <th class="col_heading level0 col15" >val16</th> <th class="col_heading level0 col16" >val17</th> <th class="col_heading level0 col17" >val18</th> <th class="col_heading level0 col18" >val19</th> <th class="col_heading level0 col19" >val20</th> <th class="col_heading level0 col20" >val21</th> <th class="col_heading level0 col21" >val22</th> <th class="col_heading level0 col22" >val23</th> <th class="col_heading level0 col23" >val24</th> <th class="col_heading level0 col24" >val25</th> <th class="col_heading level0 col25" >val26</th> <th class="col_heading level0 col26" >val27</th> <th class="col_heading level0 col27" >val28</th> <th class="col_heading level0 col28" >val29</th> <th class="col_heading level0 col29" >val30</th> <th class="col_heading level0 col30" >val31</th> <th class="col_heading level0 col31" >val32</th> <th class="col_heading level0 col32" >val33</th> <th class="col_heading level0 col33" >val34</th> <th class="col_heading level0 col34" >val35</th> </tr> <tr> <th class="index_name level0" >name</th> <th class="index_name level1" >cat</th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> </tr></thead><tbody>
<tr>
<th id="T__level0_row0" class="row_heading level0 row0" rowspan="3">Alisha Ortiz</th>
<th id="T__level1_row0" class="row_heading level1 row0" >1</th>
<td id="T__row0_col0" class="data row0 col0" >4.46</td>
<td id="T__row0_col1" class="data row0 col1" >4.62</td>
<td id="T__row0_col2" class="data row0 col2" >5.73</td>
<td id="T__row0_col3" class="data row0 col3" >6.12</td>
<td id="T__row0_col4" class="data row0 col4" >4.77</td>
<td id="T__row0_col5" class="data row0 col5" >4.73</td>
<td id="T__row0_col6" class="data row0 col6" >4.50</td>
<td id="T__row0_col7" class="data row0 col7" >6.12</td>
<td id="T__row0_col8" class="data row0 col8" >5.50</td>
<td id="T__row0_col9" class="data row0 col9" >5.92</td>
<td id="T__row0_col10" class="data row0 col10" >6.08</td>
<td id="T__row0_col11" class="data row0 col11" >4.92</td>
<td id="T__row0_col12" class="data row0 col12" >5.42</td>
<td id="T__row0_col13" class="data row0 col13" >5.38</td>
<td id="T__row0_col14" class="data row0 col14" >6.08</td>
<td id="T__row0_col15" class="data row0 col15" >5.77</td>
<td id="T__row0_col16" class="data row0 col16" >5.31</td>
<td id="T__row0_col17" class="data row0 col17" >5.58</td>
<td id="T__row0_col18" class="data row0 col18" >6.12</td>
<td id="T__row0_col19" class="data row0 col19" >4.77</td>
<td id="T__row0_col20" class="data row0 col20" >5.19</td>
<td id="T__row0_col21" class="data row0 col21" >5.96</td>
<td id="T__row0_col22" class="data row0 col22" >4.88</td>
<td id="T__row0_col23" class="data row0 col23" >5.31</td>
<td id="T__row0_col24" class="data row0 col24" >4.65</td>
<td id="T__row0_col25" class="data row0 col25" >5.88</td>
<td id="T__row0_col26" class="data row0 col26" >5.38</td>
<td id="T__row0_col27" class="data row0 col27" >5.27</td>
<td id="T__row0_col28" class="data row0 col28" >4.88</td>
<td id="T__row0_col29" class="data row0 col29" >6.35</td>
<td id="T__row0_col30" class="data row0 col30" >5.19</td>
<td id="T__row0_col31" class="data row0 col31" >5.81</td>
<td id="T__row0_col32" class="data row0 col32" >5.85</td>
<td id="T__row0_col33" class="data row0 col33" >5.46</td>
<td id="T__row0_col34" class="data row0 col34" >4.50</td>
</tr>Answering: Is there a more efficient way to apply the styling?
- The OP already suggests splitting the DataFrame into sections.
- The Pandas: Table Visualization User Guide contains possible Optimizations
- Setting
uuid_len=0,cell_ids=Falsemakes the file size slightly smaller, but does not resolve this issue.s4 = Styler(cat_mean, uuid_len=0, cell_ids=False).apply(color, axis=None)- Normally the uuid would look like
id="T_5409d_level0_row0", but withuuid_len=0, it looks likeid="T__level0_row0"
- Use
table_styleswhere you can: these add classes to rows or columns (not individual cells) so if you have those groupings its best to use them.- In the case of the OP, where there are many rows / columns, setting row or column
table_stylesmay not be a valid option
- In the case of the OP, where there are many rows / columns, setting row or column
- Setting
- There is method
set_td_classes, which allows you to refer to external css classes. (a bugfix is in 1.3.0)- The following code using a
classshould work, but is affected by bug #39317
- The following code using a
def test(s, props=''):
t = np.where(s.gt(mean[s.name]), props, '')
return t
build = lambda x: pd.DataFrame(x, index=cat_mean.index, columns=cat_mean.columns)
cls1 = build(cat_mean.apply(test, props='cls-1 ', axis=0))
test = cat_mean.style.set_table_styles([{'selector': '.cls-1', 'props': [('color', 'white'), ('background-color', 'darkblue')]}]).set_td_classes(cls1)
- Which should generate HTML more like the following, which uses the
class=for each value, instead of placing all the styled rows ids at the top of the HTML. - However, this also has a bug, as mentioned, and doesn't work correctly
<style type="text/css" >
#T_b3f37_ .cls-1 {
color: white;
background-color: darkblue;
}</style><table id="T_b3f37_" ><thead> <tr> <th class="blank" ></th> <th class="blank level0" ></th> <th class="col_heading level0 col0" >val1</th> <th class="col_heading level0 col1" >val2</th> <th class="col_heading level0 col2" >val3</th> <th class="col_heading level0 col3" >val4</th> <th class="col_heading level0 col4" >val5</th> <th class="col_heading level0 col5" >val6</th> <th class="col_heading level0 col6" >val7</th> <th class="col_heading level0 col7" >val8</th> <th class="col_heading level0 col8" >val9</th> <th class="col_heading level0 col9" >val10</th> <th class="col_heading level0 col10" >val11</th> <th class="col_heading level0 col11" >val12</th> <th class="col_heading level0 col12" >val13</th> <th class="col_heading level0 col13" >val14</th> <th class="col_heading level0 col14" >val15</th> <th class="col_heading level0 col15" >val16</th> <th class="col_heading level0 col16" >val17</th> <th class="col_heading level0 col17" >val18</th> <th class="col_heading level0 col18" >val19</th> <th class="col_heading level0 col19" >val20</th> <th class="col_heading level0 col20" >val21</th> <th class="col_heading level0 col21" >val22</th> <th class="col_heading level0 col22" >val23</th> <th class="col_heading level0 col23" >val24</th> <th class="col_heading level0 col24" >val25</th> <th class="col_heading level0 col25" >val26</th> <th class="col_heading level0 col26" >val27</th> <th class="col_heading level0 col27" >val28</th> <th class="col_heading level0 col28" >val29</th> <th class="col_heading level0 col29" >val30</th> <th class="col_heading level0 col30" >val31</th> <th class="col_heading level0 col31" >val32</th> <th class="col_heading level0 col32" >val33</th> <th class="col_heading level0 col33" >val34</th> <th class="col_heading level0 col34" >val35</th> </tr> <tr> <th class="index_name level0" >name</th> <th class="index_name level1" >cat</th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> <th class="blank" ></th> </tr></thead><tbody>
<tr>
<th id="T_b3f37_level0_row0" class="row_heading level0 row0" rowspan="3">Adriana Mcknight</th>
<th id="T_b3f37_level1_row0" class="row_heading level1 row0" >1</th>
<td id="T_b3f37_row0_col0" class="data row0 col0 cls-1 " >5.782609</td>
<td id="T_b3f37_row0_col1" class="data row0 col1 cls-1 " >5.652174</td>
<td id="T_b3f37_row0_col2" class="data row0 col2 cls-1 " >6.130435</td>
<td id="T_b3f37_row0_col3" class="data row0 col3 cls-1 " >6.086957</td>
<td id="T_b3f37_row0_col4" class="data row0 col4" >4.478261</td>
<td id="T_b3f37_row0_col5" class="data row0 col5" >4.565217</td>
<td id="T_b3f37_row0_col6" class="data row0 col6" >5.826087</td>
<td id="T_b3f37_row0_col7" class="data row0 col7" >5.956522</td>
<td id="T_b3f37_row0_col8" class="data row0 col8" >4.782609</td>
<td id="T_b3f37_row0_col9" class="data row0 col9" >5.347826</td>
<td id="T_b3f37_row0_col10" class="data row0 col10" >5.260870</td>
<td id="T_b3f37_row0_col11" class="data row0 col11" >5.130435</td>
<td id="T_b3f37_row0_col12" class="data row0 col12" >5.217391</td>
<td id="T_b3f37_row0_col13" class="data row0 col13" >6.173913</td>
<td id="T_b3f37_row0_col14" class="data row0 col14" >5.043478</td>
<td id="T_b3f37_row0_col15" class="data row0 col15" >6.391304</td>
<td id="T_b3f37_row0_col16" class="data row0 col16" >5.217391</td>
<td id="T_b3f37_row0_col17" class="data row0 col17" >5.913043</td>
<td id="T_b3f37_row0_col18" class="data row0 col18" >5.608696</td>
<td id="T_b3f37_row0_col19" class="data row0 col19" >5.869565</td>
<td id="T_b3f37_row0_col20" class="data row0 col20" >6.086957</td>
<td id="T_b3f37_row0_col21" class="data row0 col21" >4.826087</td>
<td id="T_b3f37_row0_col22" class="data row0 col22" >5.739130</td>
<td id="T_b3f37_row0_col23" class="data row0 col23" >6.304348</td>
<td id="T_b3f37_row0_col24" class="data row0 col24" >5.347826</td>
<td id="T_b3f37_row0_col25" class="data row0 col25" >5.173913</td>
<td id="T_b3f37_row0_col26" class="data row0 col26" >4.608696</td>
<td id="T_b3f37_row0_col27" class="data row0 col27" >5.391304</td>
<td id="T_b3f37_row0_col28" class="data row0 col28" >5.652174</td>
<td id="T_b3f37_row0_col29" class="data row0 col29" >5.434783</td>
<td id="T_b3f37_row0_col30" class="data row0 col30" >5.565217</td>
<td id="T_b3f37_row0_col31" class="data row0 col31" >5.956522</td>
<td id="T_b3f37_row0_col32" class="data row0 col32" >6.043478</td>
<td id="T_b3f37_row0_col33" class="data row0 col33" >5.217391</td>
<td id="T_b3f37_row0_col34" class="data row0 col34 cls-1 " >5.521739</td>
</tr>