Conditional formatting data bars in plotly subplot tables

Question

Hi guys i have been struggling with this for days. I have set up a subplot with 4 tables and the columns that are coloured, I actually want them to be data bars based off the min and max numbers in the column.

I have tried multiple different ways but cant get it to work. My current code is below.

import plotly.graph_objects as go
from plotly.subplots import make_subplots
from plotly.colors import n_colors


fig = make_subplots(
    rows=4, cols=1,
    subplot_titles=("Backs", "Edges", "Middles", "Halves, Hookers"),
    shared_xaxes=True,
    vertical_spacing=0.02,
    specs=[[{"type": "table"}],
           [{"type": "table"}],
           [{"type": "table"}],
           [{"type": "table"}]]
)


fig.add_trace(
    go.Table(
        header=dict(
            values=list(Backs_cleaned.columns),
            fill_color='maroon',
            font=dict(size=10),
            font_color='white',
            line_color='lightgrey',
            align="center"
        ),
        cells=dict(
            values=[Backs_cleaned.Round_x, Backs_cleaned.Name, Backs_cleaned.Minutes_x, Backs_cleaned.Game_x, Backs_cleaned.Con_x,
                   Backs_cleaned.Round_y,Backs_cleaned.Minutes_y, Backs_cleaned.Game_y, Backs_cleaned.Con_y, Backs_cleaned.Sum],
            font=dict(size=10),
            fill=dict(color=['white', 'white', 'lightgreen', 'salmon', 'lightgoldenrodyellow', 'white','lightgreen', 'salmon', 'lightgoldenrodyellow', 'white']),
            font_color='black',
            line_color='lightgrey',
            align = "center"),
            
    ),
    row=1, col=1
)


fig.add_trace(
    go.Table(
        header=dict(
            values=list(Edges_cleaned.columns),
            fill_color='maroon',
            font=dict(size=10),
            font_color='white',
            line_color='lightgrey',
            align="center"
        ),
        cells=dict(
            values=[Edges_cleaned.Round_x, Edges_cleaned.Name, Edges_cleaned.Minutes_x, Edges_cleaned.Game_x, Edges_cleaned.Con_x,
                   Edges_cleaned.Round_y,Edges_cleaned.Minutes_y, Edges_cleaned.Game_y, Edges_cleaned.Con_y, Edges_cleaned.Sum],
            fill=dict(color=['white', 'white', 'lightgreen', 'salmon', 'lightgoldenrodyellow', 'white','lightgreen', 'salmon', 'lightgoldenrodyellow', 'white']),
            font=dict(size=10),
            font_color='black',
            line_color='lightgrey',
            align = "center")
    ),
    row=2, col=1
)



fig.add_trace(
    go.Table(
        header=dict(
            values=list(Middles_cleaned.columns),
            fill_color='maroon',
            font=dict(size=10),
            font_color='white',
            line_color='lightgrey',
            align="center"
        ),
        cells=dict(
            values=[Middles_cleaned.Round_x, Middles_cleaned.Name, Middles_cleaned.Minutes_x, Middles_cleaned.Game_x, Middles_cleaned.Con_x,
                   Middles_cleaned.Round_y,Middles_cleaned.Minutes_y, Middles_cleaned.Game_y, Middles_cleaned.Con_y, Middles_cleaned.Sum],
            fill=dict(color=['white', 'white', 'lightgreen', 'salmon', 'lightgoldenrodyellow', 'white','lightgreen', 'salmon', 'lightgoldenrodyellow', 'white']),
            font=dict(size=10),
            font_color='black',
            line_color='lightgrey',
            align = "center")
    ),
    row=3, col=1
)

fig.add_trace(
    go.Table(
        header=dict(
            values=list(HH_cleaned.columns),
            fill_color='maroon',
            font=dict(size=10),
            font_color='white',
            line_color='lightgrey',
            align="center"
        ),
        cells=dict(
            values=[HH_cleaned.Round_x, HH_cleaned.Name, HH_cleaned.Minutes_x, HH_cleaned.Game_x, HH_cleaned.Con_x,
                   HH_cleaned.Round_y,HH_cleaned.Minutes_y, HH_cleaned.Game_y, HH_cleaned.Con_y, HH_cleaned.Sum],
            fill=dict(color=['white', 'white', 'lightgreen', 'salmon', 'lightgoldenrodyellow', 'white','lightgreen', 'salmon', 'lightgoldenrodyellow', 'white']),
            font=dict(size=10),
            font_color='black',
            line_color='lightgrey',
            align = "center")
    ),
    row=4, col=1
)

fig.update_layout(
    height=1600,

    showlegend=False,
    title_text="Longitudinal Conditioning Minutes",
)


fig.show()

And here is a copy of the figure output where I want to change the coloured columns to data bars using the same colours. Thanks for your help in advance!

pd.DataFrame({'Round_x': {0: 0.1,
  1: 0.1,
  2: 0.1,
  3: 0.1,
  4: 0.1,
  5: 0.1,
  6: 0.1,
  7: 0.1,
  8: 0.1,
  9: 0.1,
  10: 0.1,
  11: ''},
 'Name': {0: 'Jesse Arthars',
  1: 'Corey Oates',
  2: 'Herbie Farnworth',
  3: 'Tristan Sailor',
  4: 'Selwyn Cobbo',
  5: 'Ethan Quai-Ward',
  6: 'Jordan Pereira',
  7: 'Kotoni Staggs',
  8: 'Delouise Hoeter',
  9: 'Deine Mariner',
  10: 'Reece Walsh',
  11: 'Average'},
 'Minutes_x': {0: 31.0,
  1: 30.0,
  2: 37.0,
  3: 33.0,
  4: 30.0,
  5: 26.0,
  6: 37.0,
  7: 26.0,
  8: 0.0,
  9: 15.0,
  10: 11.0,
  11: 25.0},
 'Game_x': {0: 0,
  1: 0,
  2: 0,
  3: 27,
  4: 0,
  5: 27,
  6: 0,
  7: 0,
  8: 40,
  9: 0,
  10: 0,
  11: ''},
 'Con_x': {0: 6,
  1: 6,
  2: 6,
  3: 0,
  4: 6,
  5: 0,
  6: 12,
  7: 6,
  8: 17,
  9: 15,
  10: 16,
  11: ''},
 'Round_y': {0: 1.0,
  1: 1.0,
  2: 1.0,
  3: 1.0,
  4: 1.0,
  5: 1.0,
  6: 1.0,
  7: 1.0,
  8: 1.0,
  9: 1.0,
  10: 1.0,
  11: ''},
 'Minutes_y': {0: 24.0,
  1: 23.0,
  2: 14.0,
  3: 18.0,
  4: 13.0,
  5: 16.0,
  6: 9.0,
  7: 18.0,
  8: 7.0,
  9: 18.0,
  10: 24.0,
  11: 17.0},
 'Game_y': {0: 63.0,
  1: 63.0,
  2: 63.0,
  3: 0.0,
  4: 63.0,
  5: 0.0,
  6: 0.0,
  7: 39.0,
  8: 0.0,
  9: 0.0,
  10: 0.0,
  11: ''},
 'Con_y': {0: 0.0,
  1: 0.0,
  2: 0.0,
  3: 35.0,
  4: 0.0,
  5: 29.0,
  6: 35.0,
  7: 0.0,
  8: 20.0,
  9: 31.0,
  10: 19.0,
  11: ''},
 'Sum': {0: 124.0,
  1: 122.0,
  2: 120.0,
  3: 113.0,
  4: 112.0,
  5: 98.0,
  6: 93.0,
  7: 89.0,
  8: 84.0,
  9: 79.0,
  10: 70.0,
  11: 100.0}})

Answer 1

If you're willing to unleash Dash, there are a few examples in the docs on Diverging Data Bars that should fit your needs perfectly:

Apply the code in the snippet below to produce the following plot based on your dataset:

Plot

Just add or remove column references in this part in the data_bars() function to add or remove formatted columns:

style_data_conditional=(
    data_bars(df, 'Minutes_x') +
    data_bars(df, 'Minutes_y')
)

If you'd like diverging colors, use data_bars_diverging() instead.

Complete code

# %%
from dash import Dash, dash_table
import pandas as pd

df_gapminder = pd.read_csv(
    'https://raw.githubusercontent.com/plotly/datasets/master/gapminderDataFiveYear.csv')
df = df_gapminder[:500]


df = pd.DataFrame({'Round_x': {0: 0.1,
                               1: 0.1,
                               2: 0.1,
                               3: 0.1,
                               4: 0.1,
                               5: 0.1,
                               6: 0.1,
                               7: 0.1,
                               8: 0.1,
                               9: 0.1,
                               10: 0.1,
                               11: ''},
                   'Name': {0: 'Jesse Arthars',
                            1: 'Corey Oates',
                            2: 'Herbie Farnworth',
                            3: 'Tristan Sailor',
                            4: 'Selwyn Cobbo',
                            5: 'Ethan Quai-Ward',
                            6: 'Jordan Pereira',
                            7: 'Kotoni Staggs',
                            8: 'Delouise Hoeter',
                            9: 'Deine Mariner',
                            10: 'Reece Walsh',
                            11: 'Average'},
                   'Minutes_x': {0: 31.0,
                                 1: 30.0,
                                 2: 37.0,
                                 3: 33.0,
                                 4: 30.0,
                                 5: 26.0,
                                 6: 37.0,
                                 7: 26.0,
                                 8: 0.0,
                                 9: 15.0,
                                 10: 11.0,
                                 11: 25.0},
                   'Game_x': {0: 0,
                              1: 0,
                              2: 0,
                              3: 27,
                              4: 0,
                              5: 27,
                              6: 0,
                              7: 0,
                              8: 40,
                              9: 0,
                              10: 0,
                              11: ''},
                   'Con_x': {0: 6,
                             1: 6,
                             2: 6,
                             3: 0,
                             4: 6,
                             5: 0,
                             6: 12,
                             7: 6,
                             8: 17,
                             9: 15,
                             10: 16,
                             11: ''},
                   'Round_y': {0: 1.0,
                               1: 1.0,
                               2: 1.0,
                               3: 1.0,
                               4: 1.0,
                               5: 1.0,
                               6: 1.0,
                               7: 1.0,
                               8: 1.0,
                               9: 1.0,
                               10: 1.0,
                               11: ''},
                   'Minutes_y': {0: 24.0,
                                 1: 23.0,
                                 2: 14.0,
                                 3: 18.0,
                                 4: 13.0,
                                 5: 16.0,
                                 6: 9.0,
                                 7: 18.0,
                                 8: 7.0,
                                 9: 18.0,
                                 10: 24.0,
                                 11: 17.0},
                   'Game_y': {0: 63.0,
                              1: 63.0,
                              2: 63.0,
                              3: 0.0,
                              4: 63.0,
                              5: 0.0,
                              6: 0.0,
                              7: 39.0,
                              8: 0.0,
                              9: 0.0,
                              10: 0.0,
                              11: ''},
                   'Con_y': {0: 0.0,
                             1: 0.0,
                             2: 0.0,
                             3: 35.0,
                             4: 0.0,
                             5: 29.0,
                             6: 35.0,
                             7: 0.0,
                             8: 20.0,
                             9: 31.0,
                             10: 19.0,
                             11: ''},
                   'Sum': {0: 124.0,
                           1: 122.0,
                           2: 120.0,
                           3: 113.0,
                           4: 112.0,
                           5: 98.0,
                           6: 93.0,
                           7: 89.0,
                           8: 84.0,
                           9: 79.0,
                           10: 70.0,
                           11: 100.0}})
# %%
app = Dash(__name__)


def data_bars(df, column):
    n_bins = 100
    bounds = [i * (1.0 / n_bins) for i in range(n_bins + 1)]
    ranges = [
        ((df[column].max() - df[column].min()) * i) + df[column].min()
        for i in bounds
    ]
    styles = []
    for i in range(1, len(bounds)):
        min_bound = ranges[i - 1]
        max_bound = ranges[i]
        max_bound_percentage = bounds[i] * 100
        styles.append({
            'if': {
                'filter_query': (
                    '{{{column}}} >= {min_bound}' +
                    (' && {{{column}}} < {max_bound}' if (
                        i < len(bounds) - 1) else '')
                ).format(column=column, min_bound=min_bound, max_bound=max_bound),
                'column_id': column
            },
            'background': (
                """
                    linear-gradient(90deg,
                    #0074D9 0%,
                    #0074D9 {max_bound_percentage}%,
                    white {max_bound_percentage}%,
                    white 100%)
                """.format(max_bound_percentage=max_bound_percentage)
            ),
            'paddingBottom': 2,
            'paddingTop': 2
        })

    return styles


def data_bars(df, column):
    n_bins = 100
    bounds = [i * (1.0 / n_bins) for i in range(n_bins + 1)]
    ranges = [
        ((df[column].max() - df[column].min()) * i) + df[column].min()
        for i in bounds
    ]
    styles = []
    for i in range(1, len(bounds)):
        min_bound = ranges[i - 1]
        max_bound = ranges[i]
        max_bound_percentage = bounds[i] * 100
        styles.append({
            'if': {
                'filter_query': (
                    '{{{column}}} >= {min_bound}' +
                    (' && {{{column}}} < {max_bound}' if (
                        i < len(bounds) - 1) else '')
                ).format(column=column, min_bound=min_bound, max_bound=max_bound),
                'column_id': column
            },
            'background': (
                """
                    linear-gradient(90deg,
                    #0074D9 0%,
                    #0074D9 {max_bound_percentage}%,
                    white {max_bound_percentage}%,
                    white 100%)
                """.format(max_bound_percentage=max_bound_percentage)
            ),
            'paddingBottom': 2,
            'paddingTop': 2
        })

    return styles


def data_bars_diverging(df, column, color_above='#3D9970', color_below='#FF4136'):
    n_bins = 100
    bounds = [i * (1.0 / n_bins) for i in range(n_bins + 1)]
    col_max = df[column].max()
    col_min = df[column].min()
    ranges = [
        ((col_max - col_min) * i) + col_min
        for i in bounds
    ]
    midpoint = (col_max + col_min) / 2.

    styles = []
    for i in range(1, len(bounds)):
        min_bound = ranges[i - 1]
        max_bound = ranges[i]
        min_bound_percentage = bounds[i - 1] * 100
        max_bound_percentage = bounds[i] * 100

        style = {
            'if': {
                'filter_query': (
                    '{{{column}}} >= {min_bound}' +
                    (' && {{{column}}} < {max_bound}' if (
                        i < len(bounds) - 1) else '')
                ).format(column=column, min_bound=min_bound, max_bound=max_bound),
                'column_id': column
            },
            'paddingBottom': 2,
            'paddingTop': 2
        }
        if max_bound > midpoint:
            background = (
                """
                    linear-gradient(90deg,
                    white 0%,
                    white 50%,
                    {color_above} 50%,
                    {color_above} {max_bound_percentage}%,
                    white {max_bound_percentage}%,
                    white 100%)
                """.format(
                    max_bound_percentage=max_bound_percentage,
                    color_above=color_above
                )
            )
        else:
            background = (
                """
                    linear-gradient(90deg,
                    white 0%,
                    white {min_bound_percentage}%,
                    {color_below} {min_bound_percentage}%,
                    {color_below} 50%,
                    white 50%,
                    white 100%)
                """.format(
                    min_bound_percentage=min_bound_percentage,
                    color_below=color_below
                )
            )
        style['background'] = background
        styles.append(style)

    return styles


app.layout = dash_table.DataTable(
    data=df.to_dict('records'),
    sort_action='native',
    columns=[{'name': i, 'id': i} for i in df.columns],
    style_data_conditional=(
        data_bars(df, 'Minutes_x') +
        data_bars(df, 'Minutes_y')
    ),
    style_cell={
        'width': '100px',
        'minWidth': '100px',
        'maxWidth': '100px',
        'overflow': 'hidden',
        'textOverflow': 'ellipsis',
    },
    page_size=20
)


app.layout = dash_table.DataTable(
    data=df.to_dict('records'),
    sort_action='native',
    columns=[{'name': i, 'id': i} for i in df.columns],
    style_data_conditional=(
        data_bars(df, 'Minutes_x') +
        data_bars(df, 'Minutes_y')
    ),
    style_cell={
        'width': '100px',
        'minWidth': '100px',
        'maxWidth': '100px',
        'overflow': 'hidden',
        'textOverflow': 'ellipsis',
    },
    page_size=20
)


if __name__ == '__main__':
    app.run_server(debug=False)