Hi,
I have two scenarios,
-
By getting input from dropdown “Resource_dropdown” value showing output in Line chart “Allocation-By-Resource”
-
By getting input from dropdown “Resource_dropdown” value and getting another input from hovering points of line chart “Allocation-By-Resource” then showing output in Bar chart “Count-Resources-By-Lead”.
In the second (2) scenario initially working but when changing to new value in dropdown it showing old value data then after hovering the line chart only it showing new value, how to clear the data in Bar Chart “Count-Resources-By-Lead” by selecting new value in dropdown. I have attached the code below, kindly please help on this.
Code:
from distutils.log import debug
from select import select
from turtle import title, width
from dash import Dash,html,dcc,Input,Output
import pandas as pd
import plotly.express as px
import pyodbc as pod
import dash
import dash_bootstrap_components as dbc
from dash_bootstrap_templates import ThemeSwitchAIO
import plotly.graph_objects as go
template_theme1="flatly"
template_theme2="darkly"
url_theme1=dbc.themes.FLATLY
url_theme2=dbc.themes.DARKLY
filtered_estimates="";filtered_forecast=""
# app = dash.Dash(external_stylesheets=[dbc.themes.BOOTSTRAP])
conn=pod.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C: Database_location;')
cursor=conn.cursor()
#Allocation_By_Resource
qry='''SELECT * from qry_Forecast_Subform'''
df = pd.read_sql(qry,conn)
df['Total Allocation'] = df.groupby(['Name','Mth'])['Allocation'].transform('sum')
df=df.drop_duplicates(["Name","Mth"])
#Cost_By_Resource
qry='''SELECT * from qry_Forecast_Subform'''
df_resource_cost = pd.read_sql(qry,conn)
df_resource_cost['Total Cost'] = df_resource_cost.groupby(['Name','Mth'])['Cost'].transform('sum')
df_resource_cost['Total Cost']=df_resource_cost['Total Cost'].round(2)
df_resource_cost=df_resource_cost.drop_duplicates(["Name","Mth"])
# df_resource_cost['Total Cost']='$'+df_resource_cost['Total Cost'].round(2).astype(str)
#Count of Resources by Group
qry='''SELECT * from tbl_Resources_Jaya'''
df_Resources = pd.read_sql(qry,conn)
df_Resources['Total Resources'] = df_Resources.groupby(['Lead'])['SOE ID'].transform('count')
df_Resources=df_Resources.drop_duplicates(["Lead"])
#qry_business_cases
qry='''SELECT * from qry_Business_Case_and_ID'''
df_business_cases = pd.read_sql(qry,conn)
total_business_cases=len(df_business_cases['ID'].unique())
#qry_lead
qry='''SELECT [Group] from qry_Group_Name_and_ID'''
df_lead = pd.read_sql(qry,conn)
#total_estimates
qry='''SELECT * from qry_Estimates_Subform'''
df_total_estimates = pd.read_sql(qry,conn)
#total_forecast
qry='''SELECT * from qry_Forecast_Subform'''
df_total_forecast = pd.read_sql(qry,conn)
#estimates_by_group
qry='''SELECT * from qry_Estimates_Subform'''
df_estimates_by_group=pd.read_sql(qry,conn)
df_estimates_by_group['Total Estimates'] = df_estimates_by_group.groupby(['Group'])['Estimate_1'].transform('sum')
df_estimates_by_group=df_estimates_by_group.drop_duplicates(["Group"])
#forecast_by_group
qry='''SELECT * from qry_Forecast_Subform'''
df_forecast_by_group=pd.read_sql(qry,conn)
df_forecast_by_group['Total Forecast'] = df_forecast_by_group.groupby(['Lead'])['Cost'].transform('sum')
df_forecast_by_group=df_forecast_by_group.drop_duplicates(["Lead"])
# dbc_css = ("https://cdn.jsdelivr.net/gh/AnnMarieW/dash-bootstrap-templates@V1.0.2/dbc.min.css")
app=Dash(__name__,external_stylesheets=[url_theme2])
app.layout=html.Div([
html.Div([
html.Div(html.H1(children="Foresight Dashboard",
style={
'textAlign': 'center'
})),
html.Div(children=[
ThemeSwitchAIO(aio_id="theme",themes=[url_theme2,url_theme1],)
]),
html.Div([
# dcc.Input(id='resource-input',value='Select Resource'),
dcc.Dropdown(
df['Name'].unique(),
'Resource Name',
id='Resource_dropdown',
placeholder='Select Resource'
),
],
style={'width': '20%', 'display': 'inline-block'}),
html.Div([
dcc.Dropdown(
df_business_cases['Business Case Name2'].unique(),
'Business Cases',
id='business_cases_dropdown',
optionHeight=100,
placeholder='Select Business Case'
),
],
style={'width': '20%', 'display': 'inline-block','margin-left':'15px'}
),
html.Div([
dcc.Dropdown(
df_lead['Group'].unique(),
'Group',
id='group_dropdown',
placeholder='Select Lead'
),
],
style={'width': '20%', 'display': 'inline-block','margin-left':'15px'}
),
],
style={
'padding': '10px 5px'
}),
html.Div([
html.Div(
[
dbc.Card(
[
dbc.CardHeader("Total Business Cases"),
dbc.CardBody(total_business_cases,id='total_business_cases'),
],
className="mb-3",
outline=True
),
],
style={'display': 'inline-block',
'width': '180px',
'height':'150px',
'text-align': 'center',
},
),
html.Div(
[
dbc.Card(
[
dbc.CardHeader("Total Estimates"),
dbc.CardBody(filtered_estimates,id='total_estimates'),
],
className="mb-3",
outline=True
),
],
style={'display': 'inline-block',
'width': '170px',
'height':'150px',
'text-align': 'center',
'margin-left':'17px'
},
),
html.Div(
[
dbc.Card(
[
dbc.CardHeader("Total Forecast"),
dbc.CardBody(filtered_forecast,id='total_forecast'),
],
className="mb-3",
outline=True
),
],
style={'display': 'inline-block',
'width': '170px',
'height':'150px',
'text-align': 'center',
'margin-left':'15px',
},
),
]),
html.Div([
dcc.RadioItems(
['Allocation', 'Cost'],
'Allocation',
id='radio_button_1',
labelStyle={'display': 'inline-block'}
)
]),
html.Div([
dcc.Graph(
id='Allocation-By-Resource',
hoverData={'points': [{'customdata': ''}]}
)
],style={'width': '49%', 'display': 'inline-block'}),
html.Div([
dcc.Graph(
id='Count-Resources-By-Lead',
figure=px.bar(df_Resources,x='Lead',y='Total Resources'),
)
],style={'width': '49%', 'display': 'inline-block'}),
]
)
@app.callback(
Output(component_id='total_estimates',component_property='children'),
Input(component_id='group_dropdown',component_property='value'),
Input(component_id='business_cases_dropdown',component_property='value')
)
def update_total_estimates(selected_lead_value,selected_business_case_value):
print(selected_lead_value)
global filtered_estimates
if selected_lead_value is None and selected_business_case_value is None:
filtered_estimates=df_total_estimates['Estimate_1'].sum()
else:
if selected_lead_value is not None and selected_business_case_value is None:
filtered_df=df_total_estimates[df_total_estimates['Group']==selected_lead_value]
filtered_estimates=filtered_df['Estimate_1'].sum()
elif selected_business_case_value is not None and selected_lead_value is None:
filtered_df=df_total_estimates[df_total_estimates['Business Case Name2']==selected_business_case_value]
filtered_estimates=filtered_df['Estimate_1'].sum()
else:
dff=df_total_estimates[df_total_estimates['Group']==selected_lead_value]
dff=dff[dff['Business Case Name2']==selected_business_case_value]
filtered_estimates=dff['Estimate_1'].sum()
filtered_estimates="{:,}".format(filtered_estimates)
filtered_estimates=f'$ {str(filtered_estimates)}'
return filtered_estimates
@app.callback(
Output(component_id='total_forecast',component_property='children'),
Input(component_id='group_dropdown',component_property='value')
)
def update_total_forecast(selected_value):
print(selected_value)
global filtered_forecast
if selected_value is None:
filtered_forecast=df_total_forecast['Cost'].sum()
else:
filtered_df=df_total_forecast[df_total_forecast['Lead']==selected_value]
filtered_forecast=filtered_df['Cost'].sum()
filtered_forecast=round(filtered_forecast,2)
filtered_forecast="{:,}".format(filtered_forecast)
filtered_forecast=f'$ {str(filtered_forecast)}'
return filtered_forecast
@app.callback(
Output(component_id='Allocation-By-Resource',component_property='figure'),
Input(component_id='Resource_dropdown',component_property='value'),
Input(component_id="radio_button_1",component_property="value"),
Input(ThemeSwitchAIO.ids.switch("theme"),"value"),
)
def update_graph(selected_person,selected_radio_button_value,toggle):
template=template_theme2 if toggle else template_theme1
if selected_radio_button_value=="Allocation":
filtered_name=df[df['Name']==selected_person]
line_fig=px.scatter(filtered_name,x='Mth',y='Total Allocation', hover_name=filtered_name[filtered_name['Name'] == selected_person]['Lead'],title=f'Allocation for {selected_person}',text='Total Allocation',template=template)
elif selected_radio_button_value=="Cost":
filtered_name=df_resource_cost[df_resource_cost['Name']==selected_person]
line_fig=px.scatter(filtered_name,x='Mth',y='Total Cost',hover_name=filtered_name[filtered_name['Name'] == selected_person]['Lead'],title=f'Cost for {selected_person}',text='Total Cost',template=template)
line_fig.update_traces(mode='lines+markers')
line_fig.update_traces(customdata=filtered_name[filtered_name['Name'] == selected_person]['Lead'])
line_fig.update_layout(hovermode='closest',xaxis=dict(tickformat="%m-%Y"))
return line_fig
@app.callback(
Output(component_id='Count-Resources-By-Lead',component_property='figure'),
Input(component_id='Allocation-By-Resource', component_property= 'hoverData'),
Input(component_id='Resource_dropdown',component_property='value'),
Input(ThemeSwitchAIO.ids.switch("theme"),"value")
)
def update_Resources_By_Lead_Graph(hoverData,selected_person,toggle):
# hoverDatavalue=hoverData['points'][0]['customdata']
# print(hoverDatavalue)
template=template_theme2 if toggle else template_theme1
if hoverData['points'][0]['customdata']!="" and selected_person is not None:
dff=df_Resources[df_Resources['Lead']==hoverData['points'][0]['customdata']]
line_fig=px.bar(dff,x='Lead',y='Total Resources',title='Count of Resources By Lead',text='Total Resources',template=template)
else:
print("yes empty")
print(selected_person)
line_fig=px.bar(df_Resources,x='Lead',y='Total Resources',title='Count of Resources By Lead',text='Total Resources',template=template)
return line_fig
if __name__=='__main__':
app.run_server(debug=True)