Keep edited DataTable after dropdown menu changes

Dash Python
1

Good morning Community,
i’m facing an issue managing some Dash components.

This is my goal :

  1. load a static table from my sql database
  2. let the users filter results with some dropdown menus
  3. let the user edit the table
  4. keep the edited cell until a session is running

In my DataTable proprieties, i’ve set Editable=True
My code looks like this ( semplified version ) :

 original_data = pd.read_sql(my_query, my_connection)
 
 
 @app.callback(
     Output('final-table', 'data'),
     [Input('filter01_dropdown', 'value'),
      Input('filter02_dropdown'', 'value')]
     )
 def filter_df(selected_filter_01, selected_filter_02): 
     if not selected_filter_01 and not selected_filter_02:
         # Return all rows 
         return original_data.to_dict('records')
     # Else, return selected rows
     filtered = original_data.query('field_01 in selected_filter_01 or field_02 in @selected_filter_02')
     return filtered.to_dict('records')

My app works well until a new dropdown values is selected.
Every time i change a dropdown value, my callback is triggered and my edited cells are deleted.

How to keep my edited cell after changes in dropdows menu ?

I’ve read the documentation about chained callbacks but i can’t figure out a solution.

This is my attempt:

# add a store components
dcc.Store(id='store-intermediate-table')

# use the store components to store the output
 @app.callback(
     Output('store-intermediate-table', 'data'),
     [Input('filter01_dropdown', 'value'),
      Input('filter02_dropdown'', 'value')]
     )
 def filter_df(selected_filter_01, selected_filter_02): 
     if not selected_filter_01 and not selected_filter_02:
         # Return all rows 
         return original_data.to_dict('records')
     # Else, return selected rows
     filtered = original_data.query('field_01 in selected_filter_01 or field_02 in @selected_filter_02')
     return filtered.to_dict('records')


@app.callback(
     Output('final-table', 'data'),
     Input('store-intermediate-table', 'data'),
     State('final-table', 'data')
     )

 def render_table(stored_values, state_data):
     final_table = pd.DataFrame(stored_values)
     return final_table.to_dict('records')

I think i’m very close to my desidered output.

Thanks in advance

2

Hi @AutMark

You can do this with one callback. Try something like this:


@app.callback(
    Output('final-table', 'data'),
    Input('filter01_dropdown', 'value'),
    Input('filter02_dropdown', 'value'),
    State('final-table', 'data')
 )
 def filter_df(selected_filter_01, selected_filter_02, table_data):
     if not selected_filter_01 and not selected_filter_02:
         # Return all rows
         return original_data.to_dict('records')
     # Else, return selected rows
     dff = pd.DataFrame(table_data)
     filtered = dff.query('field_01 in selected_filter_01 or field_02 in @selected_filter_02')
     return filtered.to_dict('records')
1 Like
3

Hi @AnnMarieW and thank you for replying.
I tried your solution (with one callback).

But I’ve notice some strange issues.
Now, if i select two (or more) dropwdown values ( multi=True) my table doesn’t update correctly.
Only the first dropdown choice is considered.

In addition, after selecting a dropdown value, if i remove the dropdown choice, final-table loose my edited entries.

Can you show me a workaround for this ?

If you need, i can made a reproducible script.

4

Hi @AutMark

Yes, if you provide a minimal reproducible script I may be able to help.

5

Hi @AnnMarieW,
i created a reproducible script, based on my issues.
Field_01 and Field_02 are not editable.
New_Fields columns are editable.

ISSUES TO REPAIR:

  1. After selecting a dropdown value, if i remove it (from the dropdown choice), final-table loose my edited entries from editable columns.

  2. If you select two (or more) dropwdown values (multi=True) my output table doesn’t update correctly.
    Only the first dropdown choice is considered.

from datetime import date, datetime, timedelta
import time
import dash
import dash_bootstrap_components as dbc
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output, State
import pyodbc
import pandas as pd
import dash_table as dt
############################################################

results = [{'Field_01': 'APPLE','Field_02': 'ORANGE'},{'Field_01': 'BANANA','Field_02': 'GRAPEFRUIT'}, {'Field_01': 'TOMATO','Field_02': 'PINAPPLE'}]

original_data = pd.DataFrame(results)

# get list of available Field_01 from  db (and sort by AZ)
Field_01_list = original_data.Field_01.unique().tolist()
Field_01_list.sort()

# get list of available Field_02 from  db (and sort by AZ)
Field_02_list = original_data.Field_02.unique().tolist()
Field_02_list.sort()


# start app
app = dash.Dash(external_stylesheets=[dbc.themes.LUX],
                suppress_callback_exceptions=True)


app.layout = html.Div([
    dbc.Row(
        [
            dbc.Col(dcc.Dropdown(
                id='Field_01_filter_dropdown',
                options=[{'label': i, 'value': i} for i in Field_01_list],
                value=[],
                multi=True
            )),

            dbc.Col(dcc.Dropdown(
                id='Field_02_filter_dropdown',
                options=[{'label': i, 'value': i} for i in Field_02_list],
                value=[],
                multi=True
            )),

        ]),

    html.Hr(),
            dt.DataTable(
            id='final-table',
            columns=[
                {'name': 'Field_01', 'id': 'Field_01', 'editable': False},
                {'name': 'Field_02', 'id': 'Field_02', 'editable': False},
                {'name': 'NEW_Field_01', 'id': 'NEW_Field_01', 'editable': True},
                {'name': 'NEW_Field_02', 'id': 'NEW_Field_02', 'editable': True},
                {'name': 'NEW_Field_03', 'id': 'NEW_Field_03', 'editable': True},
            ],
            data=[],
            editable=False,
            export_format="xlsx",
            )

])

@app.callback(
    Output('final-table', 'data'),
    Input('Field_01_filter_dropdown', 'value'),
    Input('Field_02_filter_dropdown', 'value'),
    State('final-table', 'data')
    )
def filter_df(selected_filter_01, selected_filter_02, table_data):
     if not selected_filter_01 and not selected_filter_02:
        # Return all rows
        return original_data.to_dict('records')
     # Else, return selected rows
     dff = pd.DataFrame(table_data)
     filtered = dff.query('Field_01 in @selected_filter_01 or Field_02 in @selected_filter_02')
     return filtered.to_dict('records')


if __name__ == "__main__":
    app.run_server(host='127.0.0.1', port=8081, debug=True)

I hope you can help me.
Thanks

6

Hi @AutMark - Yes, I see the problem now. Because the table is editable, I think you were on the right track in your OP. It will be necessary to keep a complete data set updated with user edits in dcc.Store. Then you can use this intermediate data when filtering with the dropdowns.

It’s a little tricky to do that, but here is a thread that looks helpful: Detecting changed cell in Editable Datatable

1 Like