Filtering between Tables

What I would like is to have two dash data tables that filter each other. So if I click on a row in one table, the other table gets filtered based on what row was clicked on. I have done some work in creating an example of what I am trying to do, however, I don’t think it is the correct approach and I am looking for assistance in having this simple dash app not have the issues I have been facing.

Below is the code and following the code I will explain what I am doing and include screenshots

from dash import dcc, html, Dash, Output, Input, callback_context
from dash import dash_table as dt
import pandas as pd

customer_data = pd.DataFrame([{'name': 'Anna', 'contact_method': 'email', 'age': 31},
                              {'name': 'Bob', 'contact_method': 'email', 'age': 41},
                              {'name': 'Cody', 'contact_method': 'phone', 'age': 51},
                              {'name': 'David', 'contact_method': 'email', 'age': 27},
                              {'name': 'Evan', 'contact_method': 'phone', 'age': 17}])
transaction_data = pd.DataFrame([{'name': 'David', 'item': 'Apple', 'qty': 2, 'cost': 4},
                                 {'name': 'David', 'item': 'Banana', 'qty': 5, 'cost': 2},
                                 {'name': 'David', 'item': 'Orange', 'qty': 3, 'cost': 2},
                                 {'name': 'Bob', 'item': 'Banana', 'qty': 1, 'cost': 0.4},
                                 {'name': 'Bob', 'item': 'Apple', 'qty': 2, 'cost': 4},
                                 {'name': 'Evan', 'item': 'Orange', 'qty': 9, 'cost': 6},
                                 {'name': 'Anna', 'item': 'Banana', 'qty': 3, 'cost': 1.2},
                                 {'name': 'Cody', 'item': 'Apple', 'qty': 3, 'cost': 6}])
app = Dash(__name__)
data_style_conditional = [
    {
     'if': {'state': 'active'},
     'backgroundColor': 'rgba(150, 180, 225, 0.2)',
     'border': '1px solid blue'
     },
    {
     'if': {'state': 'selected'},
     'backgroundColor': 'rgba(0, 116, 217, 0.03)',
     'border': '1px solid blue'
     }]
app.layout = html.Div([
                dcc.Dropdown(value='All',
                             options= ['All'] + list(transaction_data['item'].unique()),
                             id='item_dropdown'),
                html.Button('Clear',
                            id='clear'),
                dt.DataTable(data=customer_data.to_dict('records'),
                             columns=[{'name': ' '.join([x.title() for x in column.split('_')]), 
                                       'id': column} for column in customer_data.columns],
                             style_data_conditional=data_style_conditional,
                             id='customer_tbl'),
                dt.DataTable(data=transaction_data.to_dict('records'),
                             columns=[{'name': ' '.join([x.title() for x in column.split('_')]), 
                                       'id': column} for column in transaction_data.columns],
                             style_data_conditional=data_style_conditional,
                             id='transaction_tbl')
                ])

@app.callback(Output('customer_tbl', 'style_data_conditional'), 
              Input('customer_tbl', 'active_cell'))
def update_customer_tbl_selected_row(active):
    style = data_style_conditional.copy()
    if active:
        style.append({
            'if': {'row_index': active['row']},
            'backgroundColor': 'rgba(150, 180, 225, 0.2)',
            'border': '1px solid blue'
            })
    return style

@app.callback(Output('transaction_tbl', 'style_data_conditional'), 
              Input('transaction_tbl', 'active_cell'))
def update_transaction_tbl_selected_row(active):
    style = data_style_conditional.copy()
    if active:
        style.append({
            'if': {'row_index': active['row']},
            'backgroundColor': 'rgba(150, 180, 225, 0.2)',
            'border': '1px solid blue'
            })
    return style

@app.callback(Output('customer_tbl', 'data'), 
              Input('item_dropdown', 'value'),
              Input('transaction_tbl', 'active_cell'),
              Input('transaction_tbl', 'derived_virtual_data'),
              Input('clear', 'n_clicks'))
def filter_customer_tbl(item, cell, data, n_clicks):
    item_filter = customer_data['name'].isin(transaction_data[transaction_data['item'] == item]['name'].unique()) if item != 'All' else [True] * len(customer_data.index)
    if cell:
        return customer_data[(customer_data['name'] == data[cell['row']].get('name')) & item_filter].to_dict('records')
    return customer_data[item_filter].to_dict('records')

@app.callback(Output('transaction_tbl', 'data'), 
              Input('item_dropdown', 'value'),
              Input('customer_tbl', 'active_cell'),
              Input('customer_tbl', 'derived_virtual_data'),
              Input('clear', 'n_clicks'))
def filter_transaction_tbl(item, cell, data, n_clicks):
    item_filter = transaction_data['item'] == item if item != 'All' else [True] * len(transaction_data.index)
    if cell:
        return transaction_data[(transaction_data['name'] == data[cell['row']].get('name')) & item_filter].to_dict('records')
    return transaction_data[item_filter].to_dict('records')

@app.callback(Output('customer_tbl', 'selected_cells'), 
              Output('customer_tbl', 'active_cell'),
              Input('clear', 'n_clicks'),
              Input('transaction_tbl', 'active_cell'))
def clear_customer_tbl(n_clicks, cell):
    if callback_context.triggered[0]['prop_id'] == 'clear.n_clicks' or cell:
        return [], None

@app.callback(Output('transaction_tbl', 'selected_cells'), 
              Output('transaction_tbl', 'active_cell'),
              Input('clear', 'n_clicks'),
              Input('customer_tbl', 'active_cell'))
def clear_transaction_tbl(n_clicks, cell):
    if callback_context.triggered[0]['prop_id'] == 'clear.n_clicks' or cell:
        return [], None
        
if __name__ == "__main__":
    app.run_server(debug=False)

To start off with, the data that I am working with is customer data where there is info such as how to contact them and their age. With these customers, there are transactions for them and this data includes what type of item was purchased, how many were bought, and the cost of buying the quantity of that item.

With this, those are our two data tables and below is what the dash application looks like


In the dash app, the user can use the dropdown to filter both tables based on what item type they want to look at. So if we selected “Orange”, this is the what the application shows
image
Regardless of which option we have selected we are able to have the functionality of filtering one table based on what the user clicked in the other table. But for the sake of showing, we will have all items selected. If I were to click on the row for Bob in the customer table (the top one), then the transaction table shows all the transactions for Bob (see below)
image
One thing to note with the image above is that when I click on a cell, I highlight the whole row (and this is something that I want for both tables). So I have shown that my program does filter one table based on another. We can also do it the other way around where if I click on a row in the transaction table, it will filter the customer table to only show the customer the transaction that I clicked on is related to (see below)
image
Now I haven’t explained the purpose of the “Clear” button yet. The point of it is that when I click on a table and filter the other, I may want to see all of the data for both tables again (given what the filter has been set as). So when I click on “Clear” it will remove the filters that are being done between the two tables.

Now that I have walked through what I have come up with, there are some issues with it. For one, if I click on one table and then click on the other, there are some errors. The dash app continues to run, however, there are other errors too. I have an issue with removing the highlighted cells where it does go through in the app, but errors show. I am not in expert in these things, but what I am looking for is help in getting this to work correctly or how it should be done. I know that I have a circular callback between these two tables, but I don’t know how else to do what I have done. If someone knows how to implement this where there are no errors occurring I would greatly appreciate it. Feel free to work with the code above and play around with it

Thanks

Hello @Ehren,

Welcome to the community!

You picked a bad time to ask a question with the holidays around. :slight_smile:

Alright, try this out and see if you like it:

from dash import dcc, html, Dash, Output, Input, ctx, State, MATCH, ALL
from dash import dash_table as dt
import dash
import pandas as pd

customer_data = pd.DataFrame([{'name': 'Anna', 'contact_method': 'email', 'age': 31},
                              {'name': 'Bob', 'contact_method': 'email', 'age': 41},
                              {'name': 'Cody', 'contact_method': 'phone', 'age': 51},
                              {'name': 'David', 'contact_method': 'email', 'age': 27},
                              {'name': 'Evan', 'contact_method': 'phone', 'age': 17}])
transaction_data = pd.DataFrame([{'name': 'David', 'item': 'Apple', 'qty': 2, 'cost': 4},
                                 {'name': 'David', 'item': 'Banana', 'qty': 5, 'cost': 2},
                                 {'name': 'David', 'item': 'Orange', 'qty': 3, 'cost': 2},
                                 {'name': 'Bob', 'item': 'Banana', 'qty': 1, 'cost': 0.4},
                                 {'name': 'Bob', 'item': 'Apple', 'qty': 2, 'cost': 4},
                                 {'name': 'Evan', 'item': 'Orange', 'qty': 9, 'cost': 6},
                                 {'name': 'Anna', 'item': 'Banana', 'qty': 3, 'cost': 1.2},
                                 {'name': 'Cody', 'item': 'Apple', 'qty': 3, 'cost': 6}])
app = Dash(__name__)
data_style_conditional = [{
        'if': {'state': 'active'},
        'backgroundColor': 'rgba(150, 180, 225, 0.2)',
        'border': '1px solid blue'
    }]
app.layout = html.Div([
    dcc.Dropdown(value='All',
                 options=['All'] + list(transaction_data['item'].unique()),
                 id='item_dropdown'),
    html.Button('Clear',
                id='clear'),
    dt.DataTable(data=customer_data.to_dict('records'),
                 columns=[{'name': ' '.join([x.title() for x in column.split('_')]),
                           'id': column} for column in customer_data.columns],
                 style_data_conditional=data_style_conditional,
                 id={'index':'customer_tbl', 'type':'custom_table'}),
    dt.DataTable(data=transaction_data.to_dict('records'),
                 columns=[{'name': ' '.join([x.title() for x in column.split('_')]),
                           'id': column} for column in transaction_data.columns],
                 style_data_conditional=data_style_conditional,
                 id={'index':'transaction_tbl', 'type':'custom_table'}),
    dcc.Store(id='filterBy', storage_type='memory')
])

@app.callback(Output({'index': 'customer_tbl', 'type':'custom_table'}, 'style_data_conditional'),
            Output({'index': 'transaction_tbl', 'type':'custom_table'}, 'style_data_conditional'),
            Output({'index': ALL, 'type':'custom_table'}, 'active_cell'),
            Output({'index': ALL, 'type':'custom_table'}, 'selected_cells'),
            Output('item_dropdown', 'value'),
            Output('filterBy', 'data'),
            Input('clear', 'n_clicks'),
            Input({'index': ALL, 'type':'custom_table'}, 'active_cell'),
            State({'index': ALL, 'type':'custom_table'}, 'derived_virtual_data'),
              prevent_initial_call=True)
def clearActive(n, _, d):

    customer_style = data_style_conditional.copy()
    transaction_style = data_style_conditional.copy()
    if ctx.triggered_id == 'clear':
        return data_style_conditional, data_style_conditional, [None, None], [[], []], 'All', None
    if ctx.triggered[0]['value']:
        if ctx.triggered_id == {"index":"customer_tbl","type":"custom_table"}:
            data = {'name': d[0][ctx.triggered[0]['value']['row']]['name']}
            query_customer = '{name} = "' + d[0][ctx.triggered[0]['value']['row']]['name'] + '"'
            query_transaction = '{name} = "' + d[0][ctx.triggered[0]['value']['row']]['name'] + '"'
        else:
            data = {'item': d[1][ctx.triggered[0]['value']['row']]['item'],
                    'name': d[1][ctx.triggered[0]['value']['row']]['name']}
            query_transaction = '{name} = "' + d[1][ctx.triggered[0]['value']['row']]['name'] + '" && ' \
                                                                                                '{item} = "' + \
                                d[1][ctx.triggered[0]['value']['row']]['item'] + '"'
            query_customer = '{name} = "' + d[1][ctx.triggered[0]['value']['row']]['name'] + '"'

    customer_style.append({
        'if': {'filter_query': query_customer},
        'backgroundColor': 'rgba(150, 180, 225, 0.2)',
        'borderTop': '1px solid blue',
        'borderBottom': '1px solid blue'
    })
    transaction_style.append({
        'if': {'filter_query': query_transaction},
        'backgroundColor': 'rgba(150, 180, 225, 0.2)',
        'borderTop': '1px solid blue',
        'borderBottom': '1px solid blue'
    })
    if ctx.triggered_id == {'index': 'customer_tbl', 'type':'custom_table'}:
        return customer_style, data_style_conditional, [None, None], [[], []], dash.no_update, data
    return customer_style, transaction_style, [None, None], [[], []], dash.no_update, data

@app.callback(
    Output({'index': 'customer_tbl', 'type':'custom_table'}, 'data'),
    Output({'index': 'transaction_tbl', 'type':'custom_table'}, 'data'),
    Input('filterBy', 'data'),
    Input('item_dropdown', 'value')
)
def filterBy(d, item):
    customer_temp = customer_data
    transaction_temp = transaction_data
    if item:
        customer_temp = customer_temp[customer_temp['name'].isin(
            transaction_temp[transaction_temp['item'] == item]['name'].unique()) if item != 'All' else [True] * len(
            customer_temp.index)]
        transaction_temp = transaction_temp[transaction_temp['item'] == item if item != 'All' else [True] * len(transaction_temp.index)]
    if d:
        if 'item' in d:
            item = d['item']
            customer_temp = customer_temp[customer_temp['name'].isin(
                transaction_temp[transaction_temp['item'] == item]['name'].unique()) if item != 'All' else [True] * len(
                customer_temp.index)]
        elif 'name' in d:
            transaction_temp = transaction_temp.query(f'name == "{d["name"]}"')

    return customer_temp.to_dict('records'), transaction_temp.to_dict('records')



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

I utilized pattern-matching to assist with minimizing your callbacks, and brought them together.

I made it so when an active cell is selected, it will put the info into the filterBy dcc.Store, clear the active cell and then apply conditional formatting based upon the selected info, if it can.

The clear button will clear all the filters based upon user interaction and reset the drop down to all as well.

1 Like

@jinnyzor Nice solution!!

table-cross-filter

Awesome!

I actually had been working on this some and after doing a lot of searching, I came across the pattern matching that you had utilized and had come up with my own solution (see below)

from dash import dcc, html, Dash, Output, Input, State, callback_context, ctx, ALL
from dash.exceptions import PreventUpdate
from dash import dash_table as dt
import pandas as pd

customer_data = pd.DataFrame([{'name': 'Anna', 'contact_method': 'email', 'age': 31},
                              {'name': 'Bob', 'contact_method': 'email', 'age': 41},
                              {'name': 'Cody', 'contact_method': 'phone', 'age': 51},
                              {'name': 'David', 'contact_method': 'email', 'age': 27},
                              {'name': 'Evan', 'contact_method': 'phone', 'age': 17}])
transaction_data = pd.DataFrame([{'name': 'David', 'item': 'Apple', 'qty': 2, 'cost': 4},
                                 {'name': 'David', 'item': 'Banana', 'qty': 5, 'cost': 2},
                                 {'name': 'David', 'item': 'Orange', 'qty': 3, 'cost': 2},
                                 {'name': 'Bob', 'item': 'Banana', 'qty': 1, 'cost': 0.4},
                                 {'name': 'Bob', 'item': 'Apple', 'qty': 2, 'cost': 4},
                                 {'name': 'Evan', 'item': 'Orange', 'qty': 9, 'cost': 6},
                                 {'name': 'Anna', 'item': 'Banana', 'qty': 3, 'cost': 1.2},
                                 {'name': 'Cody', 'item': 'Apple', 'qty': 3, 'cost': 6}])
app = Dash(__name__)
data_style_conditional = [
    {
     'if': {'state': 'active'},
     'backgroundColor': 'rgba(150, 180, 225, 0.2)',
     'border': '1px solid blue'
     },
    {
     'if': {'state': 'selected'},
     'backgroundColor': 'rgba(0, 116, 217, 0.03)',
     'border': '1px solid blue'
     }]
app.layout = html.Div([
                dcc.Dropdown(value='All',
                             options= ['All'] + list(transaction_data['item'].unique()),
                             id='item_dropdown'),
                html.Button('Clear',
                            id='clear'),
                dt.DataTable(data=customer_data.to_dict('records'),
                             columns=[{'name': ' '.join([x.title() for x in column.split('_')]), 
                                       'id': column} for column in customer_data.columns],
                             style_data_conditional=data_style_conditional,
                             id={'type': 'table', 'index':0}),
                dt.DataTable(data=transaction_data.to_dict('records'),
                             columns=[{'name': ' '.join([x.title() for x in column.split('_')]), 
                                       'id': column} for column in transaction_data.columns],
                             style_data_conditional=data_style_conditional,
                             id={'type': 'table', 'index':1}),
                dcc.Store(data='',
                          id='last_action')
                ])

@app.callback(
    Output({'type':'table', 'index':ALL}, 'selected_cells'),
    Output({'type':'table', 'index':ALL}, 'active_cell'),
    Output({'type':'table', 'index':ALL}, 'style_data_conditional'),
    Output('last_action', 'data'),
    Input({'type':'table', 'index':ALL}, 'selected_cells'),
    State({'type':'table', 'index':ALL}, 'id'),
    Input('clear', 'n_clicks'),
    Input('item_dropdown', 'value'),
    prevent_initial_call=True
)
def unselect_cells(cells, tables, n_clicks, item):
    e = callback_context.triggered[0]['prop_id']
    if 'selected_cells' not in e:
        return [[]] * len(tables), [None] * len(tables), [data_style_conditional] * len(tables), e
    table_ids = [t['index'] for t in tables] 
    trigger = ctx.triggered_id.index
    new_selection = [[] if t!=trigger else c for c, t in zip(cells, table_ids)]
    new_active = [None if s==[] else s[0] for s in new_selection]
    new_style = [data_style_conditional if active is None else data_style_conditional+[{'if': {'row_index': active['row']},'backgroundColor': 'rgba(150, 180, 225, 0.2)','border': '1px solid blue'}] for active in new_active]
    
    return new_selection, new_active, new_style, e

@app.callback(Output({'type': 'table', 'index': 0}, 'data'), 
              Input('item_dropdown', 'value'),
              Input({'type': 'table', 'index': 1}, 'active_cell'),
              Input({'type': 'table', 'index': 1}, 'derived_virtual_data'),
              State('last_action', 'data'))
def filter_customer_tbl(item, cell, data, last_action):
    item_filter = customer_data['name'].isin(transaction_data[transaction_data['item'] == item]['name'].unique()) if item != 'All' else [True] * len(customer_data.index)
    if str(last_action) == '{"index":0,"type":"table"}.selected_cells':
        raise PreventUpdate
    if cell:
        return customer_data[(customer_data['name'] == data[cell['row']].get('name')) & item_filter].to_dict('records')
    return customer_data[item_filter].to_dict('records')

@app.callback(Output({'type': 'table', 'index': 1}, 'data'), 
              Input('item_dropdown', 'value'),
              Input({'type': 'table', 'index': 0}, 'active_cell'),
              Input({'type': 'table', 'index': 0}, 'derived_virtual_data'),
              State('last_action', 'data'))
def filter_transaction_tbl(item, cell, data, last_action):
    item_filter = transaction_data['item'] == item if item != 'All' else [True] * len(transaction_data.index)
    if str(last_action) == '{"index":1,"type":"table"}.selected_cells':
        raise PreventUpdate
    if cell:
        return transaction_data[(transaction_data['name'] == data[cell['row']].get('name')) & item_filter].to_dict('records')
    return transaction_data[item_filter].to_dict('records')

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

I meant to post this earlier, but I started working on a couple other things. It is similar to what your code does, but there are some differences. For one, I don’t filter the customer table based on the item type that the transaction had. Instead I filter the customer table to the customer related to the transaction. I also wanted to have the “clear” button more than anything to undo the table filtering (since the thought was that if I filter one table and then filter the other you would be stuck with those tables), so my clear button only clears the table interactions.

The major difference is that when you click on table to filter and then go to the other, the filtered table that you are now using as a filter for the other does not change.

Regardless of that, your solution is great! I really appreciate you taking the time to solve this and there are some things that I will use in my code (such as the highlighting of cells). I’ll just leave this code here as an alternative

Thank you!

1 Like

Yeah, I wasnt sure if you wanted to reset the charts (set value to ‘All’), it’s not a necessity. :slight_smile:

Glad you got to experiment and get a working solution. :slight_smile: