Datatable conditional formatting: comparing two columns of stock values = broken *possible bug?

Dash Python
1

Been banging head against wall trying to sort this out.

The code compares {Last} and one of 6 colums P025 through P975. If {Last} is greater than the column value, it shades the cell green. If {Last} is less than the column value, it shades the cell red. Pretty simple.

It works most of the time. But I get these oddball shadings that happen and I can’t explain it.

image
image1396×646 92.4 KB

Any ideas?

Here’s the code:

style_data_conditional = [

                {
                    'if':{
                        'filter_query': '{Last} > {P025}',
                        'column_id': 'P025'
                        },
                    'backgroundColor': 'green',
                    'color': 'black'
            
                },
    
                {
                    'if':{
                        'filter_query': '{Last} < {P025}',
                        'column_id': 'P025'
                        },
                    'backgroundColor': 'red',
                    'color': 'black'
            
                },
    
                {
                    'if':{
                        'filter_query': '{Last} > {P10}',
                        'column_id': 'P10'
                        },
                    'backgroundColor': 'green',
                    'color': 'black'
            
                },
    
                {
                    'if':{
                        'filter_query': '{Last} < {P10}',
                        'column_id': 'P10'
                        },
                    'backgroundColor': 'red',
                    'color': 'black'
            
                },
    
                {
                    'if':{
                        'filter_query': '{Last} > {LT}',
                        'column_id': 'LT'
                        },
                    'backgroundColor': 'green',
                    'color': 'black'
            
                },
    
                {
                    'if':{
                        'filter_query': '{Last} < {LT}',
                        'column_id': 'LT'
                        },
                    'backgroundColor': 'red',
                    'color': 'black'
            
                },
    
                {
                    'if':{
                        'filter_query': '{Last} > {UT}',
                        'column_id': 'UT'
                        },
                    'backgroundColor': 'green',
                    'color': 'black'
            
                },
    
                {
                    'if':{
                        'filter_query': '{Last} < {UT}',
                        'column_id': 'UT'
                        },
                    'backgroundColor': 'red',
                    'color': 'black'
            
                },
    
                {
                    'if':{
                        'filter_query': '{Last} > {P90}',
                        'column_id': 'P90'
                        },
                    'backgroundColor': 'green',
                    'color': 'black'
            
                },
    
                {
                    'if':{
                        'filter_query': '{Last} < {P90}',
                        'column_id': 'P90'
                        },
                    'backgroundColor': 'red',
                    'color': 'black'
            
                },
    
                {
                    'if':{
                        'filter_query': '{Last} > {P975}',
                        'column_id': 'P975'
                        },
                    'backgroundColor': 'green',
                    'color': 'black'
            
                },
    
                {
                    'if':{
                        'filter_query': '{Last} < {P975}',
                        'column_id': 'P975'
                        },
                    'backgroundColor': 'red',
                    'color': 'black'
            
                }],
1 Like
2

Starting to think this is a bug. Went back and reviewed the inputs into the datatable and they’re floats so no type issues.

3

Hi @magz

Could you make a minimal working example with some data that reproduces the problem? That will help with the debugging. Your code looks right to me. Is there anything that changes the data - like sorting or filtering?

6

Here is how to format your code: (Thanks to @Eduardo for the image - I think it’s really helpful)

image
image1041×520 40.6 KB

Also, please make a complete minimal app that someone can just copy and paste that can run and show the issue.

Thanks!

1 Like
7

Yeah that would make more sense than what I did. Coming up.

9

well, you could reduce the code even more. You could make a small df with only the top 20 or so entries - enough to catch the errors like in the image. It could even be just the last column and P025. If that works it’s not a problem with the table or the conditional formatting - it may be the data.

1 Like
10

Ok…this is getting strange.

Started cutting the code down and noticed something. Wrote new code to test and it’s playing out.

It seems that the last number is almost being truncated. If the number in the columns P025 through P975 is 2 digits, it compares only the first two digits of {Last} to that number. To test this, I created a range of numbers 10 through 60 for the P025 through P975 columns. Every one of these should be green because the number in last is larger than them. This is what happens:

image
image1409×345 28.9 KB

Compare it to the first image. If the values in the columns are the same 10s digits (10s, 100s, etc) as the Last column then it works. If not (like voe, where the P025 and P10 numbers are in the 10s not 100s) then it compares the first two numbers.

It uses the same code as before to compare:

{
                        'if':{
                            'filter_query': '{Last} > {P025}',
                            'column_id': 'P025'
                            },
                        'backgroundColor': 'green',
                        'color': 'black'
                
                    },
        
                    {
                        'if':{
                            'filter_query': '{Last} < {P025}',
                            'column_id': 'P025'
                            },
                        'backgroundColor': 'red',
                        'color': 'black'
                
                    },
11

Great! can you post your code for your minimal example?

12

Sorry was cutting it to not need outside files.

import pandas as pd
import numpy as np
import dash
import dash_table
import dash_html_components as html
import dash_bootstrap_components as dbc
from dash.dependencies import Output, Input

app = dash.Dash(
    external_stylesheets=[dbc.themes.BOOTSTRAP])

colors = {
    'background': '#2A2C2E',
    'text': '#B8BDC2',
    }

out_column_names = ['Symbol', 'Last', 'P025', 'P10', 'LT', 'UT', 'P90', 'P975']


last_list = [405.41,316.89,211.85,336,136.67,257.69,139.21,223.23, 99]
last_arr = np.array(last_list)

sym_list = ['SPY', 'QQQ', 'IWM', 'VOE', 'VNQ', 'IWN', 'EWY', 'EFA', 'EEM']
controlfile = pd.DataFrame(sym_list)

app.layout =html.Div(style={'backgroundColor': colors['background'], 'font-family': 'helvetica'}, children = [
    

    dbc.Row([
        dbc.Col([html.Div([
            dash_table.DataTable(
                id='control_input',
                columns=[{'id': c, 'name': c} for c in controlfile.columns],
                data=controlfile.to_dict('records'),
                style_table={'height' : 550, 'overflowY': 'scroll', 'width' : 350},
                style_data={ 'border': '1px solid black' },
                style_cell = {'backgroundColor': colors['background'], 'color':colors['text'], 'font-family': 'helvetica'},
                editable=True
                ),
            ]),
        ], width = 3),

            
        dbc.Col([html.Div([
            dash_table.DataTable(
                id='control_output',
                columns=([{'id': c, 'name': c} for c in out_column_names]),
                data = [],
                style_table={'height' : 550, 'overflowY': 'scroll'},
                style_data={ 'border': '1px solid black' },
                style_cell = {'backgroundColor': colors['background'], 'color':colors['text'], 'font-family': 'helvetica'},
                style_data_conditional = [
        
                    {
                        'if':{
                            'filter_query': '{Last} > {P025}',
                            'column_id': 'P025'
                            },
                        'backgroundColor': 'green',
                        'color': 'black'
                
                    },
        
                    {
                        'if':{
                            'filter_query': '{Last} < {P025}',
                            'column_id': 'P025'
                            },
                        'backgroundColor': 'red',
                        'color': 'black'
                
                    },
        
                    {
                        'if':{
                            'filter_query': '{Last} > {P10}',
                            'column_id': 'P10'
                            },
                        'backgroundColor': 'green',
                        'color': 'black'
                
                    },
        
                    {
                        'if':{
                            'filter_query': '{Last} < {P10}',
                            'column_id': 'P10'
                            },
                        'backgroundColor': 'red',
                        'color': 'black'
                
                    },
        
                    {
                        'if':{
                            'filter_query': '{Last} > {LT}',
                            'column_id': 'LT'
                            },
                        'backgroundColor': 'green',
                        'color': 'black'
                
                    },
        
                    {
                        'if':{
                            'filter_query': '{Last} < {LT}',
                            'column_id': 'LT'
                            },
                        'backgroundColor': 'red',
                        'color': 'black'
                
                    },
        
                    {
                        'if':{
                            'filter_query': '{Last} > {UT}',
                            'column_id': 'UT'
                            },
                        'backgroundColor': 'green',
                        'color': 'black'
                
                    },
        
                    {
                        'if':{
                            'filter_query': '{Last} < {UT}',
                            'column_id': 'UT'
                            },
                        'backgroundColor': 'red',
                        'color': 'black'
                
                    },
        
                    {
                        'if':{
                            'filter_query': '{Last} > {P90}',
                            'column_id': 'P90'
                            },
                        'backgroundColor': 'green',
                        'color': 'black'
                
                    },
        
                    {
                        'if':{
                            'filter_query': '{Last} < {P90}',
                            'column_id': 'P90'
                            },
                        'backgroundColor': 'red',
                        'color': 'black'
                
                    },
        
                    {
                        'if':{
                            'filter_query': '{Last} > {P975}',
                            'column_id': 'P975'
                            },
                        'backgroundColor': 'green',
                        'color': 'black'
                
                    },
        
                    {
                        'if':{
                            'filter_query': '{Last} < {P975}',
                            'column_id': 'P975'
                            },
                        'backgroundColor': 'red',
                        'color': 'black'
                
                    }],
                editable=False
                ),
            ]),
        ], width = 9),
    ]),  
])

    
@app.callback(
    Output('control_output', 'data'),
    Input('control_input', 'data'))

def call_proj_data(positions):
    
    control_df = pd.DataFrame(columns = out_column_names)

    each_tick = 0
    for each in positions:

        temp_list = np.array([sym_list[each_tick], last_arr[each_tick],10, 20, 30, 40, 50, 60])
        
        loop_tick = 0
        for c in out_column_names:
            control_df.at[each_tick,c] = (temp_list[loop_tick])
            loop_tick +=1
        each_tick +=1
    
    data = control_df.to_dict('records')
    
    return data

    
if __name__ == "__main__":
    app.run_server(debug=False, port = 8100)
13

ah Ha! looks like it’s comparing text instead of numeric data. You can see it if you do a print(data) in your callback

14

omg i’m an idiot.

Thank you very much!!

1 Like
15

sometimes it just needs another set of eyes – and a MWE. Happy to help :slight_smile:

1 Like