Datatable: Update dataframes with submit button

[edmoman]

Hello,

I have created two tables with the following code:

app.layout = html.Div([
			html.Div([html.Label('AML RISK CALIBRATION', style={'fontWeight': 'bold', 'fontFamily': 'Arial, Helvetica, sans-serif', 'color': 'darkorange', 'fontSize': 16})]),
			html.Br(),
			html.Div([html.Label('WEIGHTS', style={'fontWeight': 'bold', 'fontFamily': 'Arial, Helvetica, sans-serif', 'color': 'darkorange', 'fontSize': 14}),
								dash_table.DataTable(id='table-threshold', data=df_weight.to_dict('records'), 
									columns= [{'name': 'Feature', 'id': 'Feature'}, {'name': 'Weight', 'id': 'Weight'}, 
									{'name': 'new_Weight', 'id': 'new_Weight', 'editable': True, 'type': 'numeric',}],
									style_header={'backgroundColor': 'darkorange',
										'color': 'white',
										'fontWeight': 'bold', 'fontFamily': 'Arial, Helvetica, sans-serif', 'fontSize': 12
									},
									style_data={'backgroundColor': 'white',
										'color': 'slategray',
										'fontFamily': 'Arial, Helvetica, sans-serif', 'fontSize': 12,
										'width': '100%', 'maxWidth': '100px'
									},
									style_cell_conditional=[{'if': {'column_id': 'Feature'}, 'textAlign': 'left'}],
									style_cell={'padding-right': '16px', 'padding-left': '4px'},
									fill_width=False,
									style_table={'height': cal_height, 'width': '440px', 'overflowY': 'auto'},
									export_format='csv', export_headers='display',
									#filter_action='native',
									#filter_options={'case': 'insensitive'},
									#sort_action='native',
									#sort_mode='multi',
									)],
									style={'display': 'inline-block', 'margin-right': '80px'}),			
			html.Div([html.Label('THRESHOLDS', style={'fontWeight': 'bold', 'fontFamily': 'Arial, Helvetica, sans-serif', 'color': 'darkorange', 'fontSize': 14}),
								dash_table.DataTable(id='table-threshold', data=df_thres.to_dict('records'), 
									columns= [{'name': 'Feature', 'id': 'Feature'}, {'name': 'Threshold', 'id': 'Threshold'}, 
									{'name': 'new_Threshold', 'id': 'new_Threshold', 'editable': True, 'type': 'numeric'}],
									style_header={'backgroundColor': 'darkorange',
										'color': 'white',
										'fontWeight': 'bold', 'fontFamily': 'Arial, Helvetica, sans-serif', 'fontSize': 12
									},
									style_data={'backgroundColor': 'white',
										'color': 'slategray',
										'fontFamily': 'Arial, Helvetica, sans-serif', 'fontSize': 12,
										'width': '100%', 'maxWidth': '100px'
									},
									style_cell_conditional=[{'if': {'column_id': 'Feature'}, 'textAlign': 'left'}],
									style_cell={'padding-right': '16px', 'padding-left': '4px'},
									fill_width=False,
									style_table={'height': cal_height, 'width': '440px', 'overflowY': 'auto'},
									export_format='csv', export_headers='display',
									#filter_action='native',
									#filter_options={'case': 'insensitive'},
									#sort_action='native',
									#sort_mode='multi',
									)],
									style={'display': 'inline-block'})

There are three columns in each table and only the last one is editable in both cases.

How would I go about a callback that updates the two underlying dataframes but only when a (single) submit button is clicked?

Second question: If I add filter and sort functionality to the tables, would the new dataframes by default preserve the original content and sorting or the filtered and re-sorted ones? If possible, I would like to preserve the original sorting and content but with the edits introduced by the user.

The second question is relatively irrelevant because the dataframes are very small and I can live without sorting and filtering.

Best,

Ed

[hoatran]

Hi Ed, can you tell us what do you want to update? I’ve made a simple MRE as below:

from dash import Dash, dash_table, Output, Input, State, no_update
import pandas as pd
import dash_bootstrap_components as dbc
df = pd.read_csv('https://raw.githubusercontent.com/plotly/datasets/master/solar.csv')

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

app.layout = html.Div([
    dbc.Button('Submit', size='sm', id='btn'),
    dash_table.DataTable(df.to_dict('records'), 
                         [{"name": i, "id": i} for i in df.columns], 
                         id='tbl',
                         filter_action='native',
                         filter_options={'case': 'insensitive'},
                         sort_action='native',
                         sort_mode='multi'),
    dash_table.DataTable(df.to_dict('records'), 
                         [{"name": i, "id": i} for i in df.columns], 
                         id='tbl2',
                         filter_action='native',
                         filter_options={'case': 'insensitive'},
                         sort_action='native',
                         sort_mode='multi')
])


@app.callback(Output('tbl','data'), 
              Output('tbl2','data'),
              Input('btn', 'n_clicks'), 
              prevent_initial_callback=True)

def update_table(n_clicks):
    if n_clicks == 1:
        df2 = df[df['State'].isin(['California','Arizona', 'New Mexico'])]
        return df2.to_dict('records'), df2.to_dict('records')
    if n_clicks == 2:
        df2 = df[df['State'].isin(['Colorado','Texas', 'North Carolina'])]
        return df2.to_dict('records'), df2.to_dict('records')    
    else: 
        return no_update, no_update

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

So when you click, table will be updated.
For the second question, I saw that table keep preserve sorting with new updated data.

[edmoman]

Thanks hoatran,

Your code is already very useful as I think it may contain most of what I need or, at least, it points me in the right direction. Thank you very much.

So, the first column is the feature name and the second column contains the reference values (the original ones). Those columns are not editable. Initially, the third column is identical to the second one, but it is editable in order to enable the user to modify the original values and relaunch the calculations.

What I actually want to do is to launch a new calculation using the new data (the data that the user has entered manually) when the user clicks on the button (and not sooner).

Half of my question, I believe, is addressed by prevent_initial_callback. The missing part is how to update df with the values edited by the user in the table (regardless of the number of clicks).

Thanks again and kind regards,

Ed

[hoatran]

Hi Ed, so how do the users change the data? With Input? Or are you meaning that users will change data in the third column, then click Submit and it update?

[edmoman]

Exactly. The user is able to manually edit the third column in each of the tables, then click submit and a calculation will take place with the updated values.

[hoatran]

I think you can use dash_ag_grid to make it. In this case I used cellValueChanged and rowData as State and then use n_clicks from Button as Input. Please take a look:

import dash_ag_grid as dag
from dash import Dash, html, dcc, Input, Output, State
import plotly.express as px
import pandas as pd

app = Dash(__name__)

df = pd.read_excel('https://github.com/hoatranobita/testttt/blob/main/df_thres.xlsx?raw=true')

app.layout = html.Div(
    [dbc.Button('Submit', size='sm', id='btn'),
        dcc.Markdown("Example of using `rowData` in a callback with an editable grid"),
        dag.AgGrid(
            id="editing-grid2",
            columnDefs=[{ 'field': 'Feature', 'editable': False},
                        { 'field': 'Threshold', 'editable': False},
                        { 'field': 'new_Threshold', 'editable': True}],
            rowData=df.to_dict("records"),
            columnSize="sizeToFit",
        ),
        html.Div(id="editing-grid-output2"),
    ],
    style={"margin": 20},
)


@app.callback(
    Output("editing-grid2", "rowData"),
    Input("btn", "n_clicks"),
    State("editing-grid2", "cellValueChanged"),
    State("editing-grid2", "rowData"))

def update(n_clicks, cel_Value, rows):
    dff = pd.DataFrame(rows)
    dff['new_Threshold'] = pd.to_numeric(dff['new_Threshold'])
    row = pd.to_numeric(cel_Value["rowId"])
    newValue = pd.to_numeric(cel_Value["newValue"])
    dff['new_Threshold'].loc[row] = newValue*10
    
    if n_clicks:
        return dff.to_dict("records")
    else: 
        return no_update

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

[edmoman]

Thanks! this works wonderfully.

To me, it makes no difference in this particular case, but, the question is, is it possible to do it on a table?

My guess is that a similar approach should work too.

In any case, my problem is solved. Thanks again!

[hoatran]

With dash_table you can use active_cell and derived_virtual_data to make it work. Please refer below code:

from dash import Dash, dcc, html, Input, Output, dash_table, no_update  # Dash version >= 2.0.0
import plotly.express as px

external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css']

app = Dash(__name__, external_stylesheets=external_stylesheets)

df = px.data.gapminder()
df["id"] = df.index
# print(df.head(15))
columns = ["country", "continent", "lifeExp", "pop", "gdpPercap"]
color = {"lifeExp": "#636EFA", "pop": "#EF553B", "gdpPercap": "#00CC96"}
initial_active_cell = {"row": 0, "column": 0, "column_id": "gdpPercap", "row_id": 0}

app.layout = html.Div(
    [dbc.Button('Submit', size='sm', id='btn'),
        html.Div(
            [
                html.H3("2007 Gap Minder", style={"textAlign":"center"}),
                dash_table.DataTable(
                    id="table",
                    columns=[{"name": c, "id": c} for c in columns],
                    data=df.to_dict("records"),
                    page_size=10,
                    sort_action="native",
                    active_cell=initial_active_cell,
                    editable=True
                ),
            ],
            style={"margin": 50},
            className="five columns"
        ),
        html.Div(id="output-graph", className="six columns"),
    ],
    className="row"
)

@app.callback(
    Output("table", "data"), 
    Input('btn','n_clicks'),
    State('table', "derived_virtual_data"),
    State("table", "active_cell"), 
)
def cell_clicked(n_clicks, data, active_cell):
    if active_cell is None:
        return no_update
    dff2 = pd.DataFrame(data)
    row = pd.to_numeric(active_cell["row_id"])
    print(f"row id: {row}")    
    col = active_cell["column_id"]
    print(f"column id: {col}")
    newValue = pd.to_numeric(dff2[col].loc[row])
    dff2['gdpPercap'].loc[row] = newValue*10
    print(newValue)
    if n_clicks:
        return dff2.to_dict("records")
    else: 
        return no_update

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

[Willy]

Is there an example of editing the dataframe data with an external filter ?

I am trying to integrate the example above with the external filters example here:

How to use External Filters with Dash AG Grid - Dash Python - Plotly Community Forum

in order to allow editing on the grid with a submit button.