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

[dgudhka]

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.

[AnnMarieW]

Hi @dgudhka and welcome to the Dash community

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.

[dgudhka]

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]

[AnnMarieW]

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)

[dgudhka]

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.

[AnnMarieW]

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.

[dgudhka]

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'
    })

[AnnMarieW]

Here is some information on formatting numbers.

Number Formatting | Dash for Python Documentation | Plotly

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],

[dgudhka]

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.

[AnnMarieW]

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

Order-entry

[dgudhka]

Got it - thank you so much!

[dgudhka]

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’)
)

[AnnMarieW]

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

[dgudhka]

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’)
)

[AnnMarieW]

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

[dgudhka]

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.

[jinnyzor]

Hello @dgudhka,

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

Check your web console to see what warnings are there.

[AnnMarieW]

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

How to Get your Questions Answered on the Plotly Forum

Your Plotly community members answer a great majority of the questions posted on the forum. However, responders’ time is limited. To greatly increase your chances of getting your post answered, your questions need to be easy to read and easy to reply to . This post will illustrate exactly how to do that. TL;DR: jump to section WHILE POSTING. BEFORE POSTING: Often, strange or problematic behaviour has been fixed in the most recent version of dash or of plotly so always ensure you’re using th…

[dgudhka]

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

Thanks!

[dgudhka]

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?