Datatable - Apply conditional formatting to all columns

Dash Python
1

This should be very simple, but I am completely new to Dash, so I am not sure of the syntax yet.

I’m trying to use conditional style formatting on a datatable to make all negative numbers appear in red. Below is an example of what I want to do, but only done on one column. I would like to do this for all columns. Also the column headers (in this case years) might change over time, so I would like to avoid hard coding any column names.

import dash
import dash_table
import dash_html_components as html
import pandas as pd
import numpy as np
from datetime import date

app = dash.Dash(__name__)

sColNames = [str(x) for x in range(date.today().year-6, date.today().year+1)]
df =  pd.DataFrame(data = np.array([[1.5,0.0,-1.5]]*7).T, index=range(3), columns=sColNames) # Example dataframe

dtTest = dash_table.DataTable(
        id='TestTable',
        columns=[{"name": i, "id": i} for i in df.columns],
        data=df.to_dict("rows"),
        style_data_conditional=[
        {
            'if': {
                'column_id': '2015',
                'filter': '2015 < num(0.0)' # Here I would like the same condition applied to all collumns not just "2015"
            }, 'color': 'red',
        },],)
app.layout = html.Div(children=[dtTest])

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

I looked at https://dash.plot.ly/datatable/style but couldn’t make the examples work in this case.

Any suggestions are much appreciated

3 Likes
2

@dhove There’s currently no way to express this as a single style expression.

You could generate the condition for each column (no explicit column id dependency) and apply the result like so:

styles = [{
    'if': {
        'column_id': str(x),
        'filter': '{} < num(0.0)'.format(x)
    },
    'color': 'red',
} for x in range(date.today().year-6, date.today().year+1)]

dash_table.DataTable(..., style_data_conditional=styles)

image
image.png1106×134 2.5 KB

2 Likes
3

I think that is a neat solution, and it works perfectly! So that is great, many thanks :slight_smile:

Using format like this also seems nice and flexible. I’ll remember this for future formatting.

4

I tried Marc-Andre’s suggestion from Feb 11, but it seems to be formatting the entire column data red, instead of just the negative ones.

Any idea what I could be doing wrong?

Thanks

5

@threemonks Do you have a code example and a screenshot of the result and the difference vs. the expected behavior? Will have a look, thanks.

6

Here is the code:

   return dash_table.DataTable(
        columns=[{"name": x, "id": x, 'type': 'numeric', 'format': FormatTemplate.money(0),} for x in dataframe.columns.tolist()],
        data=dataframe.to_dict('records'),
        filter_action="native",
        sort_action="native",
        page_action='native',
        page_current= 0,
        page_size=page_size,
        style_cell={'whiteSpace': 'normal', 'textOverflow': 'ellipsis'},
        style_data_conditional=[ {
                'if': {'column_id': str(x), 'filter': '{} < num(0.0)'.format(x)},
                'color': 'red',
            } for x in dataframe.columns.tolist()
        ],
    )

See attached screenshot of the table display, all values in the columns product_cost and revenue are shown in red, though I expect only cells with negative values in red, others in black.

Untitled

7

@threemonks Thanks for the example. The table’s props have changed a bit since February, especially during the 4.0 migration. The example code below uses dash==1.0.2 (dash-table==4.0.2) and while not an exact match for what you had above, should be equivalent.

import dash
from dash_table import DataTable, FormatTemplate

app = dash.Dash(__name__)

app.layout = DataTable(
        columns=[{"name": x, "id": x, 'type': 'numeric', 'format': FormatTemplate.money(0),} for x in ['a', 'b', 'c', 'd']],
        data=[
            {'a': '123', 'b': '234', 'c': 0, 'd': 456},
            {'a': '124', 'b': '235', 'c': 346, 'd': 457},
            {'a': '125', 'b': '236', 'c': 347, 'd': 458},
            {'a': '126', 'b': '237', 'c': 348, 'd': 459},
            {'a': '127', 'b': '238', 'c': 349, 'd': 450},
            {'a': '128', 'b': '239', 'c': 340, 'd': 451},
        ],
        filter_action="native",
        sort_action="native",
        page_action='native',
        style_cell={'whiteSpace': 'normal', 'textOverflow': 'ellipsis'},
        style_data_conditional=[ {
                'if': {'column_id': str(x), 'filter_query': '{{{0}}} > 125 && {{{0}}} < 458'.format(x)},
                'color': 'red',
            } for x in ['a', 'b', 'c', 'd']
        ],
    )
if __name__ == '__main__':
    app.run_server(debug=True)

Gives me:

image
image.png1317×442 22.6 KB

Few things to note:

  • conditionals nested filter is now filter_query for consistency
  • columns were referenced as columnId, it’s now {columnId}
  • num(value) no longer exists and some common conversions are attempted automatically instead
8

That works. Thank a lot!

9

Hi. If I have to add multiple if statements, what should I do? I tried to add a comma after

for x in [‘a’, ‘b’, ‘c’, ‘d’]

like in the following code:

style_data_conditional=[ {
                'if': {'column_id': str(x), 'filter_query': '{{{0}}} > 125 && {{{0}}} < 458'.format(x)},
                'color': 'red',
            } for x in ['a', 'b', 'c', 'd'], 

         // add new code block
       'if': {'column_id': str(x), 'filter_query': '{{{0}}} > condition'.format(x)},
                'color': 'blue',
            } for x in ['a', 'b', 'c', 'd']



        ],

But it does not work! Also, I changed { } at many places to put two conditional blocks, but the code does not work!

10

Instead of comma, it should be +.

[ 
{'if': {'column_id': str(x), 'filter_query': '{{{0}}} > 125 && {{{0}}} < 458'.format(x)},
                  'color': 'red',
            } for x in ['a', 'b', 'c', 'd'] 
] + [
{'if': {'column_id': str(x), 'filter_query': '{{{0}}} > condition'.format(x)},
                'color': 'blue',
            } for x in ['a', 'b', 'c', 'd']
]

This is like combining 2 lists.

1 Like
11

Thank you very much! Let me see if it works.

12

I signed up for a forum account just to thank you for the help with this, was facing the same problem and couldn’t find documentation anywhere. Marlon, you are a lifesaver!!! :pray:t2: :pray:t2:

13

Hi. How would i use this code to format a negative float inside parentheses?

I tried this:

style_data_conditional=[ {
'if': {'column_id': str(x), 'filter_query': '{{{0}}} < 0'.format(x)},
                        'format': Format(sign=Sign.parantheses),
                } for x in ['Quantity']]

Do you know how to do this without converting to string? If i need to convert to str, what is best way to do so?

It should like this

df = {'Quantity': '-50;)
Out: (50.0)
14

Hi All,

Im having the same issue where this is formatting every value in the column rather than individual values. dash-table ver 4.6.2 code as follows

p.layout = html.Div([
    html.Div([
    dash_table.DataTable(id='xx',
             columns=[{'name': i, 'id': i, 'type': 'numeric'} for i in list(dict[0].keys())],
             data=dct_asia,
             editable = False,
             # virtualization= True,
             # style_cell={'maxWidth': 100}
             style_data_conditional=[
                 {'if': {'column_id': 'delta_m'}, 'filter_query': '{delta_m}<t 0', 'color': 'red', }],

any help much appreciated

15

Not sure if this will work but give a try:

style_data_conditional=[
    {'if': { 'filter_query': '{delta_m}<t 0'},
    'color': 'red' }]
16

Thanks for the feedback everyone! Our documentation was definitely lacking here. I’ve rewritten this documentation in a new conditional formatting chapter here: https://dash.plotly.com/datatable/conditional-formatting

I hope this clears things up! If you have a new question and have consulted the documentation, please create a new thread :clinking_glasses:

image
image850×225 10.5 KB