8

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

    1. Why doesn't the HTML rendered by pandas completely display all the styling in Chrome or Edge?
    2. 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.4
    • Chrome v90.0.4430.93 (Official Build) (64-bit)
    • Edge v90.0.818.56 (Official build) (64-bit)

Original:

  • Questions - they are interrelated:
    1. 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.
    2. How can all of the rows be made to display the background styling?
  • Given a large dataframe, in this case 474 rows x 35 columns, the applied styling stops displaying.
    • enter image description here
  • 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.
    • enter image description here
  • 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, 2021 saving the redendered styler to a file has the same result, so this isn't just an issue with jupyter.
  • 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 columns or 474 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')
Improve this question
0

1 Answer 1

Reset to default
4

Update

# 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 enter image description here
  • Output of implementation in OP enter image description here

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=False makes 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 with uuid_len=0, it looks like id="T__level0_row0"
    • Use table_styles where 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_styles may not be a valid option
  • 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 class should work, but is affected by bug #39317
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>

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.