DataTable formulaic backgroundColor setting

[marketemp]

I have created a DataTable and want to set the background color of a column by a simple
equation that sets the index of a list of colors I have defined. Something like:

           style_cell_conditional=[
                   'if': {
                       'column_id': 'myCol',
                   },
                   'backgroundColor': my_color[int(my_color_formula(cell_value))],
               },

Is this possible?

The Highlighting cells by value with a colorscale like a heatmap example seems absolutely bonkers to accomplish this.

If this is not available, please make it a high priority as this seems like the most intuitive way to set variable colors versus everything being conditionalized to fixed values. Conditionals are great if you have 2-5 colors, but not if you have 255.

[AnnMarieW]

Hi @marketemp

You could try something like this:

import dash
import dash_table
import pandas as pd
import random

data = dict(
    [
        ("Date", ["2015-01-01", "2015-10-24", "2016-05-10", "2017-01-10", "2018-05-10", "2018-08-15"]),
        ("Region", ["Montreal", "Toronto", "New York City", "Miami", "San Francisco", "London"]),
        ("Temperature", [1, -20, 3.512, 4, 10423, -441.2]),
        ("Humidity", [10, 20, 30, 40, 50, 60]),
        ("Pressure", [2, 10924, 3912, -10, 3591.2, 15]),
    ]
)

df = pd.DataFrame(data)

my_color = ["#c54040", "#c58d36", "#65c336", "#32c1be", "#483dbe", "#000000"]
def my_color_formula(cell_value):
    # cell_color = your formula based on cell value,
    cell_color = random.randint(0, 5)
    return cell_color


app = dash.Dash(__name__)


df["id"] = df.index
app.layout = dash_table.DataTable(
    data=df.to_dict("records"),
    sort_action="native",
    columns=[
        {"name": "Date", "id": "Date", "type": "datetime", "editable": False},        
        {"name": "Region", "id": "Region", "type": "text"},
        {"name": "Temperature", "id": "Temperature", "type": "numeric"},
        {"name": "Humidity", "id": "Humidity", "type": "numeric"},
        {"name": "Pressure", "id": "Pressure", "type": "any"},
    ],
    editable=True,
    style_data_conditional=[
        {
            "if": {"column_id": "Humidity", "filter_query": "{{id}} = {}".format(i)},
            "backgroundColor": "{}".format(my_color[int(my_color_formula(j))]),
            "color": "white",
        }
        for i, j in enumerate(df["Humidity"])
    ],
)

if __name__ == "__main__":
    app.run_server(debug=True)

[marketemp]

can you explain what “filter_query”: “{{id}} = {}”.format(i) is meant to accomplish?

When I include it, I get no color and when I comment it out, all my cells are the final queried color, despite a print statement indicating that a unique color is returned with each self.temp_color(j) call

this is great. The ome enumerate example in https://dash.plotly.com/datatable/conditional-formatting doesn’t properly construe that a range of results is possible since it is only setting the max.

[AnnMarieW]

The "filter_query": "{{id}} = {}".format(i) is the magic that applies the color to each row.

The “if” statement is selecting the column “Humidity” and the filter_query is selecting the row.

It’s necessary to have a column called “id” which contains a row id. The example adds the “id” column like this:

df["id"] = df.index

If you add this to your dataframe, it will probably work. If it doesn’t, check that the index is like the default index (0,1,2,3…) otherwise the enumerate() wont’ work right. If you do other filtering and sorting, reset the index first like this:

# df sort  or filter....
df=df.reset_index(drop=True)
df["id"] = df.index

More info on row id’s here: https://dash.plotly.com/datatable/interactivity

BTW, here’s a simpler way that might work for you…
Since it looks like you are assigning a discrete color to cell value, you could make a dictionary. No need to mess with row id’s:

import dash
import dash_table
import pandas as pd

data = dict(
    [
        ("Date", ["2015-01-01", "2015-10-24", "2016-05-10", "2017-01-10", "2018-05-10", "2018-08-15"]),
        ("Region", ["Montreal", "Toronto", "New York City", "Miami", "San Francisco", "London"]),
        ("Temperature", [1, -20, 3.512, 4, 10423, -441.2]),
        ("Humidity", [10, 20, 30, 40, 50, 60]),
        ("Pressure", [2, 10924, 3912, -10, 3591.2, 15]),
    ]
)

df = pd.DataFrame(data)

# keys are the "Humidity" data
colors = {
    10: "#c54040",
    20: "#c58d36",
    30: "#65c336",
    40: "#32c1be",
    50: "#483dbe",
    60: "#000000",
}

app = dash.Dash(__name__)

app.layout = dash_table.DataTable(
    data=df.to_dict("records"),    
    columns=[{"name": i, "id": i} for i in df.columns],
    style_data_conditional=[
        {
            "if": {
                "column_id": "Humidity",
                "filter_query": "{{Humidity}} = {}".format(i),
            },
            "backgroundColor": colors[i],
            "color": "white",
        }
        for i in colors
    ],
)

if __name__ == "__main__":
    app.run_server(debug=True)

[marketemp]

Got it! Not sure what I had wrong trying to replicate the first example but defining

colors = {t: self.temp_color(t) for t in df["Temp"]}

and style_data_conditional like the second example worked.

It seemed like the for i in colors impeded defining more than one of these but referencing https://dash.plotly.com/datatable/conditional-formatting is saw how to do it as [ if_block_1] + [ if_block_2 ]

With your help I now have numerical sorting + string formatting working and formulaic color setting. If you are part of the Plotly organization, I would definitely advise getting an example like this on https://dash.plotly.com/datatable/conditional-formatting. Here you can see my results:

While I have drastically improved my DataTable presentation with your help, I still have linking, a title, and much more CSS control using html.Table(). I will have to deliberate whether my customers prefer the cleaner look or column sorting capacity.

[AnnMarieW]

Hi @marketemp

The table looks nice!

• You can include the links to the stocks by using “presentation”: “markdown” more info here Links within a DataTable cell

• A title can be added by using an html.Div and matching the formatting of the table header.

• Also - you can use style_as_list_view=True, to match the list style of the html.Table.
  https://dash.plotly.com/datatable/style