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

I have a dash table that works. I am trying to use an input function that allows the user to select particular rows and then have the sum of one of the columns change accordingly. However, my callback is not working and I just get the table itself.

1 Like

Hi @dgudhka and welcome to the Dash community :slightly_smiling_face:

Try changing the callback from:

Output("buy", "value"),

to:

Output("buy", "children"),

And for future posts, it’s better to post the code rather than an image of the code. Makes it easier for people to help.

Hi @AnnMarieW, thank you!

I did change that but my main problem is the callback itself. In fact I removed the entire callback and still got the same output webpage.

In my ‘def update_styles(selected_rows):’ the “new_df” variable is useless (I just found out) as I had copied from somewhere. What I really want is the callback to allow the user to filter for particular values in the columns and the sum of a particular column to change with each filter.

Here is the code:

df = query()

app = Dash(name)
app.title = “MOC IMBALANCE TOOL”

designing the app

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

    # creating a paragraph for the buy sum          
    html.Div(id='buy'),
                   
    html.Br(),                      
              
    # creating a table for the output of the dataframe for 2 days of data 
    dash_table.DataTable(id='dataframe',
    columns=[{'id': c, 'name': c, 'format' : Format()} for c in df.columns],
             
    data=df.to_dict('records'),editable=True,filter_action="native",sort_action="native",selected_rows = [],                 

    style_cell_conditional=[
        {
            'if': {'column_id': c},
            'textAlign': 'left'
        } for c in ['processDate', 'symbol']
    ],
    style_data={
        'color': 'black',
        'backgroundColor': 'white'
    },
    style_data_conditional=[
        {
            'if': {'row_index': 'odd'},
            'backgroundColor': 'rgb(220, 220, 220)',
        }
    ],
    style_header={
        'backgroundColor': 'rgb(0, 128, 0)',
        'color': 'white',
        'fontWeight': 'bold'
    })

])

callback to get the table output

@app.callback(
[Output(‘buy’,‘children’),
Output(‘interactivity’,‘data’)],
Input(‘dataframe’,‘selected_rows’)
)

def update_styles(selected_rows):
new_df = [{
‘if’: { ‘column_id’: i },
‘background_color’: ‘#D2F3FF
} for i in selected_rows]

dff = df.groupby('imbSide',as_index=False)['imbNotional'].sum()
buy = dff['imbNotional'][0]

return [f'Total Buy Value is {buy}',new_df]

Hi @dgudhka

You can find more information here: Sorting, Filtering, Selecting, and Paging Natively | Dash for Python Documentation | Plotly

Here is an app to get you started:

import dash
from dash import Dash, dash_table, dcc, html
from dash.dependencies import Input, Output
import pandas as pd
import plotly.express as px

df = px.data.tips()
df["id"] = df.index


app = Dash(__name__)

app.layout = html.Div(
    [
        dash_table.DataTable(
            id="table",
            columns=[{"name": i, "id": i}for i in df.columns],
            data=df.to_dict("records"),
            editable=True,
            filter_action="native",
            sort_action="native",
            sort_mode="multi",
            row_selectable="multi",
            row_deletable=True,
            page_action="native",
            page_size=10,
        ),
        html.Div(id="table-container"),
    ]
)


@app.callback(
    Output("table", "style_data_conditional"),
    Input("table", "derived_viewport_selected_row_ids"),
)
def style_selected_rows(selRows):
    if selRows is None:
        return dash.no_update
    return [
        {
            "if": {"filter_query": "{{id}} ={}".format(i)},
            "backgroundColor": "yellow",
        }
        for i in selRows
    ]


@app.callback(
    Output("table-container", "children"),
    Input("table", "derived_virtual_data"),
    Input("table", "derived_virtual_selected_rows"),
)
def update_graphs(rows, derived_virtual_selected_rows):
    dff = df if rows is None else pd.DataFrame(rows)

    total_selected = ""
    if derived_virtual_selected_rows:
        total_selected = dff["total_bill"].iloc[derived_virtual_selected_rows].sum()
        total_selected = html.Span(
            f"Total bill selected ${total_selected :,.2f}",
            style={"backgroundColor": "yellow"},
        )

    total_bill = dff["total_bill"].sum()
    total_bill = html.Div(f"Total bill ${total_bill: ,.2f}")

    return html.Div([total_bill, total_selected])


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


1 Like

Hi,

This is very helpful.

I had two follow-up questions:
1. Is it possible to select many rows at once since I have around 22000 rows?
2. Is there another way to select large chunks of data? - My user will want to filter for something in the table and have the sum change based on that column filter.

  1. There isn’t an easy way to select lots of rows. Might be able to update selected rows in a callback, but that would mean making the selections outside of the table - for example have a dropdown to do some filtering.

  2. In the example there are two totals. The first total is the based on the filtered data. The second is displayed only if there are selected rows – and it’s the total for the selected rows only.

table_totals

1 Like

Okay that sounds good. I will leave it as selectable rows.

One more issue I am having is trying to format a column to change it from number to percentage. All the methods online change the ‘column’ attribute of the dash_table. However my function is set up in such a way that I cannot add any arguments to the ‘column’ attribute without getting errors. What can I do to ensure I can add that column and format it in the dash table?

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", row_selectable="multi",page_current= 0,page_size= 23,page_action="native",                

    style_cell_conditional=[
        {
            'if': {'column_id': c},
            'textAlign': 'left'
        } for c in ['processDate', 'symbol']   
    ],
    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': '{imbSide} = SEL',
                  'column_id' : 'imbSide'
            },
            'color': 'red'
        }
            
    ],
    style_header={
        'backgroundColor': 'rgb(0, 128, 0)',
        'color': 'white',
        'fontWeight': 'bold'
    })

Here is some information on formatting numbers.

Not that in order for the formatting to be applied the columns must be defined as "type": "numeric"

For example

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

But all my columns are not numeric - so I have it formatted in general for all columns but now I want to add additional formatting for a particular column.

If I add the another bracket inside ‘columns’, it gives me syntax error.

You can find an example of columns set up different ways in this example:

1 Like

Got it - thank you so much!

1 Like

Hi,

My last step is to try and refresh the data pull automatically.

I want the web browser to pull data from SQL every 10 seconds without user input. The callback works fine right now and when the user changes one of the inputs the app works perfectly in pulling data from SQL. But during a particular time interval in the day, I want the app to pull new data without user toggling. Is that possible?

I tried dcc.interval but it didn’t seem to change anything.

html.Div([
    dcc.Dropdown(id='dropvalue',
        options=[
                 {'label': 'First', 'value': 'First'},
                 {'label': 'Last', 'value': 'Last'}
        ],
        value='Last',
        multi=False,
        style={'width':'30%'},         
        clearable=False
    ),
    dcc.Interval(
        id='interval-component',
        interval=1*1000, # once per second (1000 milliseconds)
        n_intervals=0
    ),
]),

html.Br(),

# allows the buy,sell,net to be displayed
html.Div(id = 'buy'),

html.Br(),

# allows main data table to be displayed
html.Div(id = 'table')

])

@app.callback (
Output (‘buy’,‘children’),
Input(‘dropvalue’,‘value’),
Input(‘datevalue’,‘value’),
Input(‘notional’,‘value’)
)

Hi @dgudhka

You need to use the interval component as an input in the callback to trigger the update. You can find an example here: Live Updates | Dash for Python Documentation | Plotly

Hi,

I tried imitating that example but my table does not load anymore. Any idea what could be going wrong?

Btw my data is stagnant until 3:50 PM every day so right now the refresh shouldn’t be any new info. But the table itself is not showing up so that is a problem.

    dcc.Interval(
        id='interval-component',
        interval=1*1000, # once per second (1000 milliseconds)
        n_intervals=0
    ),
]),

html.Br(),

# allows the buy,sell,net to be displayed
html.Div(id = 'buy'),

html.Br(),

# allows main data table to be displayed
html.Div(id = 'table')

])

@app.callback(
Output(‘buy’,‘children’),
Input(‘dropvalue’,‘value’),
Input(‘datevalue’,‘value’),
Input(‘notional’,‘value’),
Input(‘interval-component’, ‘n_intervals’)
)

I can’t tell what’s wrong because this isn’t a complete example that I could run

Yeah unfortunately you won’t be able to use the same data. However the app says ‘updating’ at the top very often and is refreshing something. Just that the output table doesn’t appear.

Hello @dgudhka,

Sounds like you have an issue with displaying, causing the circular updating.

Check your web console to see what warnings are there.

1 Like

If you can make a reproducible example with sample data, it would be helpful. Find out more information here:

I got it - was just missing a extra variable in the function for the callback.

Thanks!

Hi,

In regards to the earlier columns question, I followed your advice but still got stuck. I managed to format my columns when I pull the data from SQL to make the dataframe. It is perfect visually but if I format from the SQL pull itself, I am unable to sort the columns. Is there a way to allow user to sort columns after they have been formatted? Or should I format when making the dash table to allow sorting?