Databricks Community

DavidKxx · ‎09-26-2024

I am displaying a table in a notebook dashboard. One column of the data is conceptually a list of strings. I can originate or convert the list as whatever format would be useful (as a string representing a JSON array, as an ARRAY struct, etc.).

I want to display this list in the dashboard in a multiline format that would be conceptually equivalent to showing someone a list of bullet points.

To be clear, I'm ok with a new solution if you have one, rather than a fix to any of my partial solutions below.

My two solutions that are almost good enough are:

Method 1) Display the column as a string where the list items have been joined/concatenated with '\n' as a separator. This results in something like the second column of:

Row 2 shows this as a good solution. However, when you open the dashboard, the cells in that column all display at first as unexpanded, like row 1. You have to click each dropdown arrow to expand the list vertically. Is there a way to manipulate the data column or the dashboard conditions so that the default display mode would show the cells in their vertically expanded condition?

Method 2) Turn the data column in to a struct ARRAY of text strings, i.e., an actual list of the individual bullet points. Then it displays as follows:
x
x

This is less satisfying visually than Method 1, because of showing all that punctuation. But it's still an admissible solution, except that it has the same defect as Method 1 -- it defaults to the lists being unexpanded, and you have to click each list to expand it. (That problem is actually worse here, because the unexpanded list on row 1 doesn't make any of the text visible at all.) Even so, is there a way to default the lists to expanded?

Method 3) I haven't tried this yet ... something involving EXPLODE applied to the struct ARRAY. I anticipate that this would be unsatisfactory, because the other columns not being exploded would be repeated on each of the rows created by the EXPLODE operation, and that is not what I want visually.

filipniziol · ‎09-26-2024

Hi @DavidKxx ,

What you can do is convert your array to into an HTML formatted string with bullet points.

Here is the code:

# Sample data with an array column
data = [
    (1, ['Apple', 'Banana', 'Cherry']),
    (2, ['Dug', 'Elephant']),
    (3, ['Fish', 'Giraffe', 'Hippo', 'Iguana']),
    (4, []),  # Empty list example
    (5, ['Jellyfish'])
]

# Create DataFrame with columns 'id' and 'items'
df = spark.createDataFrame(data, ['id', 'items'])

# Collect the data to the driver
rows = df.collect()

# Build the HTML table
table_html = '<table style="width:100%; border-collapse: collapse;">'

# Add table headers
table_html += '<tr>'
for col_name in df.columns:
    table_html += f'<th style="border: 1px solid black; text-align: left; padding: 8px;">{col_name}</th>'
table_html += '</tr>'

# Add table rows
for row in rows:
    table_html += '<tr>'
    for col_name in df.columns:
        cell_value = row[col_name]
        if isinstance(cell_value, list):
            # Format list as bullet points
            if cell_value:  # Check if the list is not empty
                list_items = ''.join(f'<li>{item}</li>' for item in cell_value)
                cell_html = f'<ul>{list_items}</ul>'
            else:
                cell_html = '<i>No items</i>'  # Placeholder for empty lists
        else:
            cell_html = f'{cell_value}'
        # Add the cell to the table
        table_html += f'<td style="border: 1px solid black; text-align: left; padding: 8px; vertical-align: top;">{cell_html}</td>'
    table_html += '</tr>'
table_html += '</table>'

# Display the HTML table
displayHTML(table_html)

Here is the result:

Hope it helps