Hello,
I’m on day two of using one of Amway’s Dash Enterprise licenses. How do I use a table, that’s a product of a callback tied to a drop down, to filter another table underneath it and then use table 2 to filter table three?
I have a three tables that shows forecast results by our product business line(table 1), product category (table 2) and product subcategory (table 3) by location. The location is chosen from a drop down menu.
I want to click the business line in table 1, and have tables 2 and 3 filter to every category/subcategory chosen in table 1.
Then I want to click a category in table 2 and have every subcategory in table 3 filter to every subcategory that is a part of the category.
I only tried to do this with the 2nd table and got the following error message:
“
In the callback for output(s): cat_table.data Output 0 (cat_table.data) is already in use. Any given output can only have one callback that sets it. To resolve this situation, try combining these into one callback function, distinguishing the trigger by using dash.callback_context if necessary.
“
Below is my code. What am I doing wrong?
import dash
from dash import Dash, dcc, dash_table, Input, Output
import dash_bootstrap_components as dbc
import dash_design_kit as ddk
from dash import html
import plotly.express as px
import plotly.graph_objects as go
import plotly.io as pio
from google.cloud import bigquery
import pandas as pd
import numpy as np
import os
from google.oauth2 import service_account
os.environ["GOOGLE_APPLICATION_CREDENTIALS"]="/workspace/service_key.json"
plotly_template = pio.templates["plotly_dark"]
query = """SELECT a.dmand_yr_mo
,a.ord_base7
,a.snapshot
,a.location_type
,a.oper_cntry_id
,a.model
,a.rule
,a.predictions
,a.ord_qty
,a.sales_dollars
,a.month_offset
,Max(b.GLBL_BUS_LN_DESC) as GLBL_BUS_LN_DESC
,Max(b.GLBL_CTGRY_DESC) as GLBL_CTGRY_DESC
,Max(b.GLBL_SUB_CTGRY_DESC) as GLBL_SUB_CTGRY_DESC
,Max(b.GLBL_PRDCT_IND_CUT_DESC) as GLBL_PRDCT_IND_CUT_DESC
,Max(b.ABC ) as ABC
FROM `gcp-sc-demand-plan-analytics.Modeling_Output.monthly_best_ts_predictions` a
left join `gcp-sc-demand-plan-analytics.Demand_Planning_Ingestion.ITEM_MASTER` b
on a.ord_base7 = b.TO_ITEM_NO
where a.predictions is not null
Group by
a.dmand_yr_mo
,a.ord_base7
,a.snapshot
,a.location_type
,a.oper_cntry_id
,a.model
,a.rule
,a.predictions
,a.ord_qty
,a.sales_dollars
,a.month_offset
order by a.snapshot, a.ord_base7, a.dmand_yr_mo
"""
client = bigquery.Client(project="gcp-sc-demand-plan-analytics")
query_job = client.query(
query,
# Location must match that of the dataset(s) referenced in the query.
location="US",
) # API request - starts the query
df = query_job.to_dataframe()
df['dmand_month'] = pd.to_datetime(df['dmand_yr_mo']).dt.month
df.loc[:, 'price'] = df.loc[:, 'sales_dollars']/df.loc[:, 'ord_qty']
df.loc[:, 'predicted_sales'] = np.round(df.loc[:, 'predictions']*df.loc[:, 'price'])
df.loc[:, 'diff'] = np.round(np.abs(np.round(df.loc[:, 'predicted_sales'] - df.loc[:, 'sales_dollars'],2)))
#BUSINESS LINE
bl = df.groupby(['location_type', 'GLBL_BUS_LN_DESC']).agg({'ord_qty':'sum',
'predictions':'sum',
'sales_dollars':'sum',
'predicted_sales':'sum',
'diff':'sum'}).reset_index()
bl.replace([np.inf, -np.inf], np.nan, inplace=True)
bl['sales_dollars'] = np.round(bl['sales_dollars'])
bl['predictions'] = np.round(bl['predictions'])
bl.dropna(inplace = True)
bl.loc[:, 'MAPE'] = np.round(bl.loc[:, 'diff']/ bl.loc[:, 'sales_dollars'], 4) * 100
bl.loc[:, 'BIAS'] = np.round((bl.loc[:,'predicted_sales']- bl.loc[:, 'sales_dollars'])/ bl.loc[:, 'sales_dollars'], 4) * 100
cat = df.groupby(['location_type', 'GLBL_BUS_LN_DESC', 'GLBL_CTGRY_DESC']).agg({'ord_qty':'sum',
'predictions':'sum',
'sales_dollars':'sum',
'predicted_sales':'sum',
'diff':'sum'}).reset_index()
cat.replace([np.inf, -np.inf], np.nan, inplace=True)
cat['sales_dollars'] = np.round(cat['sales_dollars'])
cat.loc[:, 'MAPE'] = np.round(cat.loc[:, 'diff']/ cat.loc[:, 'sales_dollars'], 4) * 100
cat.loc[:, 'BIAS'] = np.round((cat.loc[:,'predicted_sales']- cat.loc[:, 'sales_dollars'])/ cat.loc[:, 'sales_dollars'], 4) * 100
bl_category_table = cat[['location_type', 'GLBL_BUS_LN_DESC', 'GLBL_CTGRY_DESC', 'sales_dollars', 'predicted_sales', 'diff', 'MAPE', 'BIAS']]
subcat = df.groupby(['location_type', 'GLBL_BUS_LN_DESC', 'GLBL_CTGRY_DESC', 'GLBL_SUB_CTGRY_DESC']).agg({'ord_qty':'sum',
'predictions':'sum',
'sales_dollars':'sum',
'predicted_sales':'sum',
'diff':'sum'}).reset_index()
subcat.replace([np.inf, -np.inf], np.nan, inplace=True)
subcat['sales_dollars'] = np.round(subcat['sales_dollars'])
subcat.dropna(inplace = True)
subcat.loc[:, 'MAPE'] = np.round(np.round(subcat.loc[:, 'diff']/ subcat.loc[:, 'sales_dollars'], 4) * 100,2)
subcat.loc[:, 'BIAS'] = np.round(np.round((subcat.loc[:,'predicted_sales']- subcat.loc[:, 'sales_dollars'])/ subcat.loc[:, 'sales_dollars'], 4) * 100,2)
bl_subcategory_table = subcat[['location_type', 'GLBL_BUS_LN_DESC', 'GLBL_CTGRY_DESC', 'GLBL_SUB_CTGRY_DESC', 'sales_dollars', 'predicted_sales', 'diff', 'MAPE', 'BIAS']]
colors = {
'background': '#000000',
'text': '#111111'
}
# initialize app
# external_stylesheet=['https://codepen.io/chriddyp/pen/bWLwgP.css']
app = dash.Dash(__name__, external_stylesheets=[dbc.themes.DARKLY]
)
# set app layout
app.layout = html.Div(children=[
html.H1('Demand Forecasting Model Overview',style={
'textAlign': 'center'}),
html.Div(children='''
Dash: A web application to help assess predictors for furture demand forecasting models.
''', style={
'textAlign': 'center'}),
html.Br(),
dcc.Dropdown(options = [{'label':'home_delivery', 'value':'home_delivery'},
{'label': 'shop_sales', 'value':'shop_sales'}],
value = 'home_delivery',
id='location_dropdown'),
dcc.Graph(id='Business_Line'),
html.H2(children='Demand Forecast Tables', style={
'textAlign': 'center'}),
html.H3(
dash_table.DataTable(id="bl_table",
columns=[{'id': c, 'name': c} for c in bl.columns],
data = bl.to_dict("records"),
# ,active_cell=
filter_action = 'native',
sort_action = 'native',
style_header={
'backgroundColor': 'rgb(30, 30, 30)',
'color': 'white'
},
style_data={
'backgroundColor': 'rgb(50, 50, 50)',
'color': 'white',
'widthSpace':'auto',
'height':'auto',
'textAlign':'center'
},
)
),
html.Hr(),
html.H4(
dash_table.DataTable(id='cat_table',
columns = [{'id':c, 'name':c} for c in bl_category_table.columns],
data = bl_category_table.to_dict("records"),
filter_action = 'native',
sort_action = 'native',
style_header={
'backgroundColor': 'rgb(30, 30, 30)',
'color': 'white'
},
style_data={
'backgroundColor': 'rgb(50, 50, 50)',
'color': 'white',
'widthSpace':'auto',
'height':'auto',
'textAlign':'center'
}
)
),
html.Button(id='cat_button', n_clicks=0, children='Submit'),
html.Div(id='selected-cat'),
html.H5(
dash_table.DataTable(id='subcat_table',
columns = [{'id':c, 'name':c} for c in bl_subcategory_table.columns],
data = bl_subcategory_table.to_dict("records"),
filter_action = 'native',
sort_action = 'native',
style_header={
'backgroundColor': 'rgb(30, 30, 30)',
'color': 'white'
},
style_data={
'backgroundColor': 'rgb(50, 50, 50)',
'color': 'white',
'widthSpace':'auto',
'height':'auto',
'textAlign':'center'
}
)
),
html.Button(id='subcat_button', n_clicks=0, children='Submit'),
html.Div(id='selected-subcat'),
])
#CONTROL THE BUSINESS LINE GRAPH
@app.callback(
dash.dependencies.Output('Business_Line', 'figure'),
[dash.dependencies.Input('location_dropdown', 'value')]
)
def update_bl(location):
if location == 'home_delivery':
df1 = df[df['location_type']=='home_delivery'].copy()
else:
df1 = df[df['location_type']=='shop_sales'].copy()
bl = df1.groupby(['GLBL_BUS_LN_DESC']).agg({'ord_qty':'sum',
'predictions':'sum',
'sales_dollars':'sum',
'predicted_sales':'sum',
'diff':'sum'}).reset_index()
bl.replace([np.inf, -np.inf], np.nan, inplace=True)
bl['sales_dollars'] = np.round(bl['sales_dollars'])
bl['predictions'] = np.round(bl['predictions'])
bl.dropna(inplace = True)
bl.loc[:, 'MAPE'] = np.round(bl.loc[:, 'diff']/ bl.loc[:, 'sales_dollars'], 4) * 100
bl.loc[:, 'BIAS'] = np.round((bl.loc[:,'predicted_sales']- bl.loc[:, 'sales_dollars'])/ bl.loc[:, 'sales_dollars'], 4) * 100
fig1 = go.Figure(data=[
go.Bar( name='MAPE', x=bl['GLBL_BUS_LN_DESC'], y=bl['MAPE']),
go.Bar(name='BIAS', x=bl['GLBL_BUS_LN_DESC'], y=bl['BIAS'])
])
fig1.update_layout(barmode='group', plot_bgcolor='rgb(0,0,0)',
title=go.layout.Title(
text=f"{location} MAPE AND BIAS",
font=dict(
family="Courier New, monospace",
size=22,
color="#111111"
)))
return fig1
#CONTROL THE TABLES
@app.callback(
dash.dependencies.Output('bl_table', 'data'),
[dash.dependencies.Input('location_dropdown', 'value')]
)
def update_table(location):
filtered_df = bl[bl['location_type'] == location]
data = filtered_df.to_dict('records')
return data
@app.callback(
dash.dependencies.Output('cat_table', 'data'),
[dash.dependencies.Input('location_dropdown', 'value')]
)
def update_table(location):
filtered_df = bl_category_table[bl_category_table['location_type'] == location]
data = filtered_df.to_dict('records')
return data
@app.callback(
dash.dependencies.Output('subcat_table', 'data'),
[dash.dependencies.Input('location_dropdown', 'value')]
)
def update_table(location):
filtered_df = bl_subcategory_table[bl_subcategory_table['location_type'] == location]
data = filtered_df.to_dict('records')
return data
@app.callback(
dash.dependencies.Output('cat_table', 'data'),
[dash.dependencies.Input('bl_table', 'active_cell')])
def filter_table(active_cell):
filtred_dict = bl_category_table[bl_category_table['GLBL_BUS_LN_DESC'] == active_cell[0]]
return filtred_dict.to_dict('records')
if __name__ == '__main__':
app.run_server(debug=True)
What is the proper way to do this?
UPDATE:
I tried importing the Multiplexer Transform to use that.
I changed my callbacks to the following:
@app.callback(
dash.dependencies.Output('bl_table', 'data'),
dash.dependencies.Output('cat_table', 'data'),
dash.dependencies.Output('subcat_table', 'data'),
[dash.dependencies.Input('location_dropdown', 'value')],
priority=1
)
def update_table(location):
filtered_df_bl = bl[bl['location_type'] == location]
data_bl = filtered_df_bl.to_dict('records')
filtered_df_cat = bl_category_table[bl_category_table['location_type'] == location]
data_cat = filtered_df_cat.to_dict('records')
filtered_df_subcat = bl_subcategory_table[bl_subcategory_table['location_type'] == location]
data_subcat = filtered_df_subcat.to_dict('records')
return data_bl, data_cat, data_subcat
@app.callback(
dash.dependencies.Output('cat_table', 'data'),
[dash.dependencies.Input('bl_table', 'active_cell')])
def filter_table(active_cell):
filtred_dict_cat = bl_category_table[bl_category_table['GLBL_BUS_LN_DESC'] == active_cell[0]]
return filtred_dict_cat.to_dict('records')
Now I’m getting the following error:
Traceback (most recent call last):
File "/workspace/app.py", line 261, in filter_table
filtred_dict_cat = bl_category_table[bl_category_table['GLBL_BUS_LN_DESC'] == active_cell[0]]
TypeError: 'NoneType' object is not subscriptable
Glad you got that far with the multiplexer. 