Trying to output a sum of a column of a table where sum changes with user filtering

AnnMarieW · February 14, 2023, 3:07pm

I’m not sure I understand you question - a minimal example would help. But I’m assuming that when you pull the data from SQL, you are formatting numbers as a string ie (10000 to 10,000) so now the numbers don’t sort correctly?

When you use the tables number formatting, it enables you to display the number in various way but it does not change the data, so then it will sort or filter correctly. If you don’t want to do that, then you can use backend filtering and sorting. More info here:

dgudhka · February 14, 2023, 4:16pm

Yes I am trying to use the table number formatting way and I tried two things.

One was changing the style_data_conditional:

    data = df.to_dict('records'),editable=False,filter_action="native",sort_action="native",selected_rows = [], sort_mode="multi",page_current= 0,page_size=20,page_action="native",                

    style_cell_conditional=[
        {
            'if': {'column_id': c},
            'textAlign': 'center'
        } for c in ['processDate', 'symbol', 'imbSide', 'mktOrderImbSide']
    ],
    style_data={
        'color': 'black',
        'backgroundColor': 'white'
    },
    style_data_conditional=[
        {
            'if': {'row_index': 'odd'},
            'backgroundColor': 'rgb(220, 220, 220)',
        },
        {
            'if': {'filter_query': '{imbSide} = BUY',
                  'column_id' : 'imbSide'
            },
            'color': 'rgb(0, 128, 0)'
        },
        {
            'if': {'filter_query': '{mktOrderImbSide} = BUY',
                  'column_id' : 'mktOrderImbSide'
            },
            'color': 'rgb(0, 128, 0)'
        },
        {
            'if': {'filter_query': '{imbSide} = SEL',
                  'column_id' : 'imbSide'
            },
            'color': 'red'
        },
        {
            'if': {'filter_query': '{mktOrderImbSide} = SEL',
                  'column_id' : 'mktOrderImbSide'
            },
            'color': 'red'
        },
        {
            'if': {'column_type': 'numeric'
            },
            'backgroundColor': 'blue'
        },
            
    ],
    style_header={
        'backgroundColor': 'rgb(0, 128, 0)',
        'color': 'white',
        'fontWeight': 'bold'
    })

to include a query for column type : numeric. But it doesn’t show up as blue. Maybe because the title of each column is text? I don’t know why.

Second thing I tried was to change the format where I define the ‘columns’ in the dash table:

    tableoutput = dash_table.DataTable(id='dataframe',
            columns=[[{'id': c, 'name': c, 'format' : Format()} for c in df.columns],
                     [{'id': c, 'name': c, 'type':'numeric', 'format' : Format().group(True)} for c in ['imbNotional']]
                    ],
             

             
    data=df.to_dict('records'),editable=False,filter_action="native",sort_action="native",selected_rows = [], sort_mode="multi",page_current= 0,page_size=20,page_action="native",

This method doesnt even output a table.

I thought the first method will work since if it did I could just format all columns with numbers to have comma separator.

AnnMarieW · February 14, 2023, 5:15pm

HI @dgudhka

It looks like this is correct:

        {
            'if': {'column_type': 'numeric'
            },
            'backgroundColor': 'blue'
        },

but the columns are not defined correctly - this will give a list of lists instead of a list of dicts

  columns=[[{'id': c, 'name': c, 'format' : Format()} for c in df.columns],
          [{'id': c, 'name': c, 'type':'numeric', 'format' : Format().group(True)} for c in ['imbNotional']]
          ]

Be sure to run using debug=True so you will see some helpful error messages:


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

dgudhka · February 14, 2023, 6:38pm

I did turn on the debug function but it does not show anything.

In terms of the column definition that was to show you what I tried.

I still have it as I had before (which works but doesn’t format numeric columns):

    tableoutput = dash_table.DataTable(id='dataframe',
            columns=[{'id': c, 'name': c, 'format' : Format()} for c in df.columns],
      
    data=df.to_dict('records'),editable=False,filter_action="native",sort_action="native",selected_rows = [], sort_mode="multi",page_current= 0,page_size=20,page_action="native",

dgudhka · February 14, 2023, 6:44pm

I turned on the debug function but it does not show anything.

In terms of the column definition I was trying to show you what I tried. I still have it like I did before (which works except that the numeric columns are not formatted):

    tableoutput = dash_table.DataTable(id='dataframe',
            columns=[{'id': c, 'name': c, 'format' : Format()} for c in df.columns],
      
    data=df.to_dict('records'),editable=False,filter_action="native",sort_action="native",selected_rows = [], sort_mode="multi",page_current= 0,page_size=20,page_action="native",

AnnMarieW · February 14, 2023, 6:52pm

Need to add “type”: “numeric”


columns=[{'id': c, 'name': c, 'type': 'numeric', 'format' : Format()} for c in df.columns],

dgudhka · February 14, 2023, 8:55pm

Ah I see why you were saying this. It makes sense. Sorry about that.

It worked now!

dgudhka · February 15, 2023, 4:18pm

Hi,

I am having some trouble with the layout of the app. I kind of forced some elements to move right or left with ‘marginRight’ but the orientation does not maintain consistency when you view the browser on a smaller window. I want to cater for both audiences who have it open in full screen and those who have it open in a smaller window.

This is what the code looks like right now:

app.layout = html.Div([

html.H1(id = 'H1', children = ['MOC IMBALANCE TOOL'], style = {'textAlign':'center', 'marginTop':40,'marginBottom':40, 'color':'rgb(0, 128, 0)'}),

dbc.Row([

    # default date is last business day date
    dbc.Col([
        html.Label(id = 'Date', children = ' Date ',style = {'color':'rgb(0, 128, 0)','marginLeft':10,'textAlign':'left'}),
        dcc.Input(id='datevalue',
            value = datetime.strftime(yesterday, '%Y%m%d'),
            style = {'textAlign':'center'},
            type='text'
        ),
    ],className = 'row1'),

    # notional default is 500,000 thus only rows with notional>500000 show up by default
    dbc.Col([
        html.Label(id = 'Notional', children = ' Notional ($) ',style = {'color':'rgb(0, 128, 0)','textAlign':'center','marginLeft':260}),
        dcc.Input(id='notional',
            value = 500000,
            style = {'textAlign':'center'},
            type='number'
        ),
    ],className = 'row1'),

    # allows user to fetch the first or last batch of stock prices
    dbc.Col([
        html.Label(id = 'Msg', children = ' Batch ',style = {'color':'rgb(0, 128, 0)','textAlign':'right','marginLeft':700}),
        dcc.Dropdown(id='dropvalue',
            options=[
                     {'label': 'First', 'value': 'First'},
                     {'label': 'Last', 'value': 'Last'}
            ],
            value='Last',
            style = {'width':'45%','textAlign':'center'},
            multi=False,
            clearable=False
        ),
    ],className = 'row1'),
 ]),

How do I move labels and input cells and dropdowns to the right and account for the two audiences at the same time?

adamschroeder · February 15, 2023, 8:10pm

Hi @dgudhka
It will be easier for you to customize the app layout if you used Dash Bootstrap. Here’s an intro video on the topic.

dgudhka · February 15, 2023, 8:52pm

Hey, I have seen a few of your videos and built my app. One thing I can’t get around is that my input cells are on the same line but my dropdown goes on to the next line as shown in this image, even though I structured them in the same dcc.Row

Is there a way to have them either all falling into two rows or all fitting in to one side by side?

adamschroeder · February 15, 2023, 9:58pm

hi @dgudhka

Have you tried this structured layout:

dbc.Row([
    dbc.Col([
        dcc.Input(...)
    ], width=4),

    dbc.Col([
        dcc.Input(...)
    ], width=4),

    dbc.Col([
        dcc.Dropdown(...)
    ], width=4)
]),

dgudhka · February 16, 2023, 1:14pm

Yeah:

# designing how the page looks
app.layout = html.Div([

# heading of page
html.H1(id = 'H1', children = ['MOC IMBALANCE TOOL'], style = {'textAlign':'center', 'marginTop':40,'marginBottom':40, 'color':'rgb(0, 128, 0)'}),

dbc.Row([

    # default date is last business day date
    dbc.Col([
        html.Label(id = 'Date', children = ' Date ',style = {'color':'rgb(0, 128, 0)','textAlign':'left','marginLeft':10}),
        dcc.Input(id='datevalue',
            value = datetime.strftime(yesterday, '%Y%m%d'),
            style = {'textAlign':'center', 'marginLeft':'10px'},
            type='text'
        ),
    ],width = 4, xs = {'size':3}),

    # notional default is 500,000 thus only rows with notional>500000 show up by default
    dbc.Col([
        html.Label(id = 'Notional', children = ' Notional ($) ',style = {'color':'rgb(0, 128, 0)'}),
        dcc.Input(id='notional',
            value = 500000,
            style = {'textAlign':'center','marginLeft':'10px'},
            type='number'
        ),
    ],width = 4),

    # allows user to fetch the first or last batch of stock prices
    dbc.Col([
        html.Label(id = 'Msg', children = ' Batch ',style = {'color':'rgb(0, 128, 0)','textAlign':'right','marginLeft':280}),
        dcc.Dropdown(id='dropvalue',
            options=[
                     {'label': 'First', 'value': 'First'},
                     {'label': 'Last', 'value': 'Last'}
            ],
            value='Last',
            style = {'width':'100%','textAlign':'center'},
            multi=False,
            clearable=False
        ),
    ],width = 4,xs = {'size':3}),
 ]),

It comes off worse than before and doesn’t go to the end in addition to the dropdown going to the next line:

adamschroeder · February 16, 2023, 2:34pm

Hey @dgudhka
For a quick fix, try putting the components of the last column inside a dbc.Stack().

        dbc.Col([
            dbc.Stack([
                html.Label(id='Msg', children=' Batch ',
                           style={'color': 'rgb(0, 128, 0)', 'textAlign': 'right',}),
                dcc.Dropdown(id='dropvalue',
                             options=[
                                 {'label': 'First', 'value': 'First'},
                                 {'label': 'Last', 'value': 'Last'}
                             ],
                             value='Last',
                             style={'width': '100%', 'textAlign': 'center'},
                             multi=False,
                             clearable=False
                             ),
            ],direction='horizontal')
        ], width=4, xs={'size': 3}),

dgudhka · February 16, 2023, 3:34pm

Yeah the fix works thanks!

Topic		Replies	Views
Updating data to dash table Dash Python	2	4650	January 23, 2018
The callback does not update the dash table Dash Python	8	1931	June 7, 2022
Issues with dash_table updating through callback Dash Python	2	1051	February 26, 2019
Dash DataTable Native Filtering on a table based on a different callback Dash Python	1	700	September 20, 2019
My First webapp on Dash. My misunderstanding about callback Dash Python	11	473	June 14, 2022

Trying to output a sum of a column of a table where sum changes with user filtering

Related Topics