Need help with advanced Conditional Formatting in Dash with Mean and special selection of columns

[Davelikedash]

Hi, everyone,

I’m relatively new to the Dash world, but really excited about the possibilities.

However, I am currently facing a problem with the conditional formatting of my table in Dash and hope you can help me, as I get stuck after several days of trying.

The situation is as follows:

• I created a dashboard to match my cluster algorithm … that worked perfectly. However, I would now like to format the table with the values ​​a little more “complex”.

• Since my data and column names change frequently, it has to be the whole variable and I cannot refer to a specific column except for a single column, which should be yours as an orientation

Within the column: Number of rows, the row with the largest value should be taken as the reference value (i.e. largest cluster)

Then all values ​​of the remaining columns should be colored accordingly based on the mean value of this row:

(Simplified form)

Value is not the same = red
Value is the same = green
(desired shape)

The further the value is from the mean value of the “target line”, the red the field within the table should be … the closer to the mean value the greener it should be

Here is an example of how I imagine the whole thing (simple version)

(And my table is created using a callback from 2 dropdowns and a button)

I know that this is just one of the templates from the How to Guide from Plotly, but I am not able to adapt this to my liking.

I tried an if / else condition but didn’t succeed …

I have to say that I’ve only been programming for 5 months and therefore I probably don’t understand the syntax …

Summarized:

Did I need a conditional formatting, which can be found in the column:

Anzahl der Zeilen

Oriented and recognizes the largest number of them. Then, using this, everything that applies to this line turns green and everything that deviates from it turns red (optional: the further away from the mean value of the reference column, the color should change from green to red)

But I would be very happy about help from you for the solution or an approach to make my problem feasible …

Or at least the statement whether this is even possible.

[Eduardo]

Hallo @Davelikedash und Willkommen in der Community

First of all, to share a code use the </> icon of the message menu and insert there the code.

I can’t see your code, so I will give you some tips that perhaps you just used it.

For doing what that, first apply a style to all the columns with a red background and use the conditional format only for the numbers that match the mean condition.

For duing that see in the following link that has the same example as your needs in the secction " Highlighting values above average and below average":
https://dash.plotly.com/datatable/conditional-formatting

[Davelikedash]

Hello @Eduardo,

thank you for the quick response.

I saw the example, but here are the columns are defined and not variable and in my case i dont know the name and amount of rows.

I think im Know what you mean but i cant handle the flxible in there there

… with a single column where im knowing the name i got the example running…

Im looking forward to your help and hope you can help me.

[Eduardo]

Hey @Davelikedash

See that in the documentation’s example do not have the name of the columns in the conditional formating, it use:

for (col, value) in df.

[Davelikedash]

yes i know what you mean. I already tried it but i cant get reference of the row with the highste number from the column “Anzahl der Zeilen” so my issue is, that i cant get a referenz during the conditional formatting…

where i say :

This Row is my “Good Row” and any field in the table who is different from this row with the highest value in the column “Anzahl der Zeilen” is Red… all fields which are close are Orange and all who matches with the referenz row are green.

so i cant get the connection to my reference row.
i already tried every example.

there are very good and well explained… but didnt matched my needs…

i hope i descriped my issue a lil bit more.

but thanks about the trick with the backround, im sure as soon i can get my connection with the reference line it will help me alot.

[Eduardo]

Ok.

I will look it later and get my conclusion late today.

[Eduardo]

Hey @Davelikedash

Finally I get the goal. Here is the code:

import dash
import dash_table
import dash_core_components as dcc
import dash_html_components as html
import pandas as pd
import numpy as np

# import table as a DataFrame
df = pd.read_csv('table.csv')

# adds a column named "id" with the index (this column will be used in the conditional format)
df['id'] = df.index

# find the index of the max number in "anzahl der zailen"
idx_max = df["anzahl der zailen"].idxmax()

# take the elements of the row with the max value (except the last column)
ref_row = np.array(df.iloc[idx_max].values[:-1])

# take the names of the columns in the DataFrame
col_names = pd.DataFrame(columns=df.columns[:-1])


# variable styles will get the conditional format starting with the two columns that will have white backgrouund
styles = [{'if': {'column_id': 'anzahl der zailen'}, 'backgroundColor': 'white'},{'if': {'column_id': 'cluster'}, 'backgroundColor':'white'}]

# for each row of the DataFRame will compare the row with the "ref_row" values
for idx, row in df.iterrows():
    
    # convert the row in numpy array (except last column value)
    line = row.to_numpy()[:-1]
    
    # make a comparison between the line and the ref_row, will produce a variable with True and False elements
    result = np.equal(ref_row, line)
    
    # get the columns names of the "True" elements
    columns = [x for x, y in zip(col_names, result) if y == True]
    
    # get the style for this row
    style = {'if': {'filter_query': '{{id}} = {}'.format(idx),'column_id': columns}, 'backgroundColor': 'green','color': 'white'}
    
    # append the style into a styles variable
    styles.append(style)

print(styles)

app = dash.Dash(__name__)

app.layout = html.Div([
    dash_table.DataTable(
        id='table',
        # the columns of the tabel are equal to the DataFrame except for the last column named "id"
        columns=[{'name': i, 'id': i} for i in df.columns if i != 'id'],
        data=df.to_dict('records'),
        #style_cell={'backgroundColor':'orange'},
        style_data={'backgroundColor':'pink'},
        style_data_conditional=styles)
])



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

And this is the result:

[Davelikedash]

Hey Eduardo,

thank you very much you helped me a lot.

Seems like the Problem was much more difficult then i tought…

but now i think i can use this and got much more understanding about the options here.

thank you very much

[Eduardo]

Yes, Perhaps there are ways to simplify it, I’m not share if it’s necessary to add the I’d column or if exist a way to use directly the index, also if its not possible to make the arrays comparison directly in the coditional format.

I’m also relative new in coding (las weeks was my first anniversary).