# DataTable formulaic backgroundColor setting

**URL:** <https://community.plotly.com/t/datatable-formulaic-backgroundcolor-setting/47351>\
**Category:** Dash Python\
**Created:** [November 20, 2020, 1:17am UTC](https://community.plotly.com/t/datatable-formulaic-backgroundcolor-setting/47351 "2020-11-20T01:17:39Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![marketemp](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/marketemp/32/11523_2.png) [@marketemp](https://community.plotly.com/u/marketemp)\
**Post date:** [November 20, 2020, 1:17am UTC](https://community.plotly.com/t/datatable-formulaic-backgroundcolor-setting/47351/1 "2020-11-20T01:17:39Z")

</div>

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:

```auto
           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.

---

<div class="post-metadata">

**Author:** ![AnnMarieW](https://avatars.discourse-cdn.com/v4/letter/a/ec9cab/32.png) [@AnnMarieW](https://community.plotly.com/u/AnnMarieW)\
**Post date:** [November 21, 2020, 5:26pm UTC](https://community.plotly.com/t/datatable-formulaic-backgroundcolor-setting/47351/2 "2020-11-21T17:26:13Z")

</div>

Hi @marketemp

You could try something like this:

```auto

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)

```

---

<div class="post-metadata">

**Author:** ![marketemp](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/marketemp/32/11523_2.png) [@marketemp](https://community.plotly.com/u/marketemp)\
**Post date:** [November 21, 2020, 8:23pm UTC](https://community.plotly.com/t/datatable-formulaic-backgroundcolor-setting/47351/3 "2020-11-21T20:23:36Z")

</div>

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](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.

---

<div class="post-metadata">

**Author:** ![AnnMarieW](https://avatars.discourse-cdn.com/v4/letter/a/ec9cab/32.png) [@AnnMarieW](https://community.plotly.com/u/AnnMarieW)\
**Post date:** [November 22, 2020, 3:43pm UTC](https://community.plotly.com/t/datatable-formulaic-backgroundcolor-setting/47351/4 "2020-11-22T15:43:45Z")

</div>

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:

```auto
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:

```auto
# 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](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:

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![marketemp](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/marketemp/32/11523_2.png) [@marketemp](https://community.plotly.com/u/marketemp)\
**Post date:** [November 22, 2020, 5:05pm UTC](https://community.plotly.com/t/datatable-formulaic-backgroundcolor-setting/47351/5 "2020-11-22T17:05:34Z")

</div>

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

```auto
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](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](https://dash.plotly.com/datatable/conditional-formatting). Here you can see my results:

 ![Screen Shot 2020-11-22 at 12.27.18 PM](https://us1.discourse-cdn.com/flex024/uploads/plot/original/2X/8/845bb1f7e6c08800f07db537d8f52138e395a045.png)

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.

---

<div class="post-metadata">

**Author:** ![AnnMarieW](https://avatars.discourse-cdn.com/v4/letter/a/ec9cab/32.png) [@AnnMarieW](https://community.plotly.com/u/AnnMarieW)\
**Post date:** [November 22, 2020, 6:01pm UTC](https://community.plotly.com/t/datatable-formulaic-backgroundcolor-setting/47351/6 "2020-11-22T18:01:20Z")

</div>

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](https://community.plotly.com/t/links-within-a-datatable-cell/47353/2)

- 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](https://dash.plotly.com/datatable/style)
