Pandas Styler is lost on push to Organiser Topic

[John Holland]

I have a SDL notebook that produces a pandas Styler object and uses IPython.core.display.display_html to display these.

When I run this in addon mode, it works perfectly.

However, I would prefer to share this via an organiser topic. I have seen other topics here where images are updated and I have tried similar methods. This pushes the tables fine but loses the styling.

Is there a way to provide this styling to the organiser topic or perhaps the best approach might be to try to create images from the html?

Thanks.

[Emilio Conde]

Hi John,

 

Have you tried something similar to below?

# Convert styled df to html
html = df.style.to_html()

# Find the topic you want
topic_search = spy.workbooks.search({'ID': organizer_id})

# Pull in the topic
topic = spy.workbooks.pull(topic_search, include_referenced_workbooks = False)[0]

# Create a new sheet
topic.document('Sheet Name')

# Modify the html of the new sheet with the styled df html
topic.worksheets[-1].html = html

# Push your changes
organizer_push = spy.workbooks.push(topic)

 

[John Holland]

Hi Emilio,

that is more or less exactly what I did.

Do be sure, I ran the following:

 

html = df_styled.to_html()
topic_search = spy.workbooks.search({'ID': organizer_id})
topic = spy.workbooks.pull(topic_search, include_referenced_workbooks = False)[0]
topic.document('Sheet2')
topic.worksheets[-1].html = html
organizer_push = spy.workbooks.push(topic)

where df_styled is a pandas styler object. The result was the same.

Running df_styled in the notebook prints the coloured table, but going to the new Sheet2 in the topic shows a plain table.

 

 

 

[Emilio Conde]

I see... What version of Seeq is your server on? And for my own testing, what's the styling you're applying to df? My simple test just created clickable hyperlinked text in df.

[John Holland]

Hi,

hopefully the attached shows you all you need to know.

Thanks.

Untitled.ipynb (auto-d) - JupyterLab.pdf

[Emilio Conde]

Thanks for the context, John.

 

Unfortunately, Organizer currently only supports basic html, and not CSS / HTML5 as implemented by the .highlight_max() or .highlight_null operators.

 

With that said, I recommend you send us a support ticket here requesting this functionality so that we can link it to an existing feature request and allow you to be automatically notified of this capability once it's implemented in a future version of Seeq.

 

Working with ChatGPT, I was able to get the basic HTML equivalent applied to the df in your example, and have verified it pushes to Organizer as expected. Of course, another approach could be to create an image out of your html and just push the image. There are other posts on this forum that discuss that functionality.

See working example below:

import numpy as np
import pandas as pd
from bs4 import BeautifulSoup


organizer_id = '7AC3EAA0-6429-46D5-88E3-57ADC6AA1ED7'

df = pd.DataFrame({
"A": [0, -5, 12, -4, 3],
"B": [12.24, 3.14, 2.71, -3.14, np.nan], 
"C": [0.5, 1.2, 0.3, 1.9, 2.2],
"D": [2000, np.nan, 1000, 7000, 5000]
})


# Basic HTML to highlight a cell
def highlight_cell_bg(val, color=None):
    # If there's a color specified, apply it as the background
    if color:
        return 'background-color: {}'.format(color)
    return ""

# Highlight the max value in a column yellow
def highlight_max_bg(series):
    # Remove non-numeric values and compute max
    numeric_vals = series[pd.to_numeric(series, errors='coerce').notnull()]
    if not numeric_vals.empty:
        max_val = numeric_vals.max()
        return [highlight_cell_bg(val, 'yellow') if val == max_val else "" for val in series]
    return [""] * len(series)

# Highlight Missing row values red
def highlight_missing_bg(val):
    if val == 'Missing':
        return highlight_cell_bg(val, 'red')
    return ""

# Replace NaN values with "Missing" and highlight them
df.replace({np.nan: 'Missing'}, inplace=True)
missing_bg = df.applymap(highlight_missing_bg)


# Highlight the maximum values in each column
max_bg = df.apply(highlight_max_bg)


# Merge the two background styles
final_bg = missing_bg.where(missing_bg != "", max_bg)

# Convert DataFrame to HTML without additional formatting
raw_html = df.to_html(escape=False, header=True, index=False)

# Parse the HTML using BeautifulSoup
soup = BeautifulSoup(raw_html, 'html.parser')

# Iterate through each cell in the table and apply styles
for row in soup.findAll("tr"):
    for col_name, cell in zip(df.columns, row.findAll("td")):
        if cell.text in df.columns:
            continue  # Skip headers
        
        # Convert dataframe values to string for comparison
        idx = df[col_name].astype(str).tolist().index(cell.text)
        style = final_bg[col_name].iloc[idx]
        if style:
            cell["style"] = style

# Convert the modified HTML back to a string
html = str(soup)


# Find the topic you want
topic_search = spy.workbooks.search({'ID': organizer_id})

# Pull in the topic
topic = spy.workbooks.pull(topic_search, include_referenced_workbooks = False)[0]

# Create a new sheet
topic.document('New Sheet')

# Modify the html of the new sheet with the styled df html
topic.worksheets[-1].html = html

# Push your changes
organizer_push = spy.workbooks.push(topic)

[John Holland]

Hi Emilio,

apologies for the delay. Your reply gave me enough pointers to get this to work (with the help of ChatGPT!).

An abridged version of the code is below:

import cssutils  # type: ignore[import-untyped]
import pandas as pd
from bs4 import BeautifulSoup
from pandas.io.formats.style import Styler

class StyleData
	def style_area_data_for_seeq_topic(
        self,
        area: str,
        data_df: pd.DataFrame,
        *,
        reverse_sort: bool = False,
    ) -> str:
        """Style the data for the Seeq topic."""
        styler = self._style_area_data(
            area,
            data_df,
            reverse_sort=reverse_sort,
        )
        if styler is None:
            msg = f"Styler for {area} is None."
            raise ValueError(msg)

        soup = BeautifulSoup(styler.to_html(), "html.parser")
        style_tag = soup.find("style")

        if style_tag is None:
            msg = f"Style tag for {area} is None."
            raise ValueError(msg)

        parser = cssutils.CSSParser()

        stylesheet = parser.parseString(getattr(style_tag, "string", None))

        for rule in stylesheet:
            if rule.type == rule.STYLE_RULE:
                selector = rule.selectorText
                style = rule.style.cssText
                elements = soup.select(selector)
                for el in elements:
                    existing_style = el.get("style", "")
                    new_style = existing_style + style
                    el["style"] = new_style

        style_tag.extract()

        return str(soup.prettify().strip())