Hi everyone, I’m new to plotly dash. I create a dashboard that read from a .db files and writes to a .db file using dcc.upload and pandas to_sql. The problem is after the data is written, the graph was not displayed any new data. How do I solve this issue? And here’s the code.
import dash
import dash_auth
import dash_core_components as dcc
import dash_html_components as html
import dash_table
import dash_bootstrap_components as dbc
from dash.dependencies import Input, Output, State
import plotly.graph_objects as go
import plotly.express as px
import pandas as pd
import numpy as np
from datetime import date
from datetime import datetime
import base64
import io
from sqlalchemy import create_engine
engine = create_engine('sqlite:///data/df_sales.db', echo=False)
def base_data():
df_sales = pd.read_sql_query("SELECT * from sales", engine)
df_sales['date'] = pd.to_datetime(df_sales['date'])
df_sales['year'] = df_sales['date'].apply(lambda x: x.strftime('%Y')).astype(str)
df_sales['quarter'] = 'Q' + df_sales['date'].dt.quarter.astype(str)
df_sales['year_quarter'] = df_sales['year'] + ' ' + df_sales['quarter']
df_sales['month'] = df_sales['date'].apply(lambda x: x.strftime('%b'))
df_sales['month_n'] = df_sales['date'].apply(lambda x: x.strftime('%m'))
df_sales['month'] = pd.Categorical(df_sales['month'], ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'])
df_sales['year_month'] = df_sales['year'].astype(str) + '-' + df_sales['month_n'].astype(str)
df_sales['week'] = df_sales['date'].apply(lambda x: x.strftime('%W'))
df_sales['year_week'] = df_sales['year'].astype(str) + ' W' + df_sales['week'].astype(str)
df_sales['day'] = df_sales['date'].apply(lambda x: x.strftime('%a'))
df_sales['day'] = pd.Categorical(df_sales['day'], ['Mon','Tue','Wed','Thu','Fri','Sat','Sun'])
df_sales['spv'] = df_sales['spv'].str.upper()
df_sales['wh'] = df_sales['wh'].str.upper()
return df_sales
def clust_data():
cl = pd.DataFrame(df.groupby('customer')['sales'].sum())
cl = cl.sort_values(by=['sales'], ascending=True)
tc = pd.DataFrame(df.groupby('customer')['id'].count())
cl['no_of_transaction'] = tc['id']
return cl
df = base_data()
cluster_data = clust_data()
def dropdown(column):
if column == 'item':
list = df.item.dropna().unique()
n = 'items'
id = 'input1'
idt = 'testing1'
elif column == 'customer':
list = df.customer.dropna().unique()
n = 'customers'
id = 'input2'
idt = 'testing2'
elif column == 'spv':
list = df.spv.dropna().unique()
n = 'supervisors'
id = 'input3'
idt = 'testing3'
elif column == 'salesperson':
list = df.sales_person.dropna().unique()
n = 'salespersons'
id = 'input4'
idt = 'testing4'
elif column == 'wh':
list = df.wh.dropna().unique()
n = 'warehouses'
id = 'input5'
idt = 'testing5'
menu = dbc.Col([
dbc.Row(
html.Div([
html.H6(n.capitalize()),
dcc.Dropdown(
id = id,
options=[
{'label': i, 'value': i} for i in list
],
multi=True, placeholder='Filter by ' + n + '...'
),
], style={'width': 350})
),
dbc.Row(
html.Div(id=idt)
),
])
return menu
def filter(x):
if x == 'ytd':
filter = 'year'
elif x == 'qtd':
filter = 'year_quarter'
elif x == 'mtd':
filter = 'year_month'
elif x == 'wtd':
filter = 'year_week'
elif x == 'dt':
filter = 'date'
return filter
def period(x):
if x == 'ytd':
period = 'year'
elif x == 'qtd':
period = 'quarter'
elif x == 'mtd':
period = 'month'
elif x == 'wtd':
period = 'week'
return period
def dropdown_filter(data,item_list,customer_list,supervisor_list,salesperson_list,warehouse_list):
if item_list == None:
df1 = data
else:
df1 = data[data['item'].isin(item_list)]
if customer_list == None:
df2 = df1
else:
df2 = df1[df1['customer'].isin(customer_list)]
if supervisor_list == None:
df3 = df2
else:
df3 = df2[df2['spv'].isin(supervisor_list)]
if salesperson_list == None:
df4 = df3
else:
df4 = df3[df3['sales_person'].isin(salesperson_list)]
if warehouse_list == None:
df5 = df4
else:
df5 = df4[df4['wh'].isin(warehouse_list)]
return df5
def switch_a(data,f,value):
dF = data
if value == 1:
dff = pd.DataFrame(dF.groupby(f)['sales'].sum())['sales']
fig = go.Figure(go.Scatter(x=dff.index,
y=dff,
fill='tozeroy',
mode='lines'))
elif value == 2:
dff = pd.DataFrame(dF.groupby([f,'spv'])['sales'].sum())['sales']
dff = dff.reset_index()
fig = px.area(dff, x=f, y="sales", color='spv', line_group='spv')
elif value == 3:
dff = pd.DataFrame(dF.groupby([f,'wh'])['sales'].sum())['sales']
dff = dff.reset_index()
fig = px.area(dff, x=f, y="sales", color='wh', line_group='wh')
return fig
def switch_b(f,value,df,supervisor_list,warehouse_list):
df5 = df
if value == 2 and len(supervisor_list) >= 2:
dff = pd.DataFrame(df5.groupby([f,'spv'])['sales'].sum())['sales']
dff = dff.reset_index()
fig = px.area(dff, x=f, y="sales", color='spv', line_group='spv')
elif value == 3 and len(warehouse_list) >= 2:
dff = pd.DataFrame(df5.groupby([f,'wh'])['sales'].sum())['sales']
dff = dff.reset_index()
fig = px.area(dff, x=f, y="sales", color='wh', line_group='wh')
else:
fig = go.Figure(go.Scatter(x=df5[f].unique() ,
y=pd.DataFrame(df5.groupby([f])['sales'].sum())['sales'],
fill='tozeroy',
mode='lines'))
return fig
def switch_c(value):
if value == 1 or value == 3:
g = 'wh'
else:
g = 'spv'
return g
def fig(data,period,item,customers,spv,salespersons,wh,switch):
f = filter(period)
item_list = item
customer_list = customers
supervisor_list = spv
salesperson_list = salespersons
warehouse_list = wh
if item_list == None and customer_list == None and supervisor_list == None and salesperson_list == None and warehouse_list == None:
fig = switch_a(data,f,switch)
else:
df5 = dropdown_filter(data,item_list,customer_list,supervisor_list,salesperson_list,warehouse_list)
fig = switch_b(f,switch,df5,supervisor_list,warehouse_list)
fig.update_layout(
title="Sales Trends",
autosize=True,
margin=dict(
l=50,
r=50,
b=10,
t=50,
pad=4
),
plot_bgcolor='white',
)
full_fig = html.Div(
dcc.Graph(
figure=fig,
config={
'displayModeBar': False
},
),
),
return full_fig
def arpu(data,period,item,customer,spv,salesperson,wh):
f = filter(period)
df = dropdown_filter(data,item,customer,spv,salesperson,wh)
ds = pd.DataFrame(df.groupby(f).sales.sum())
dx = pd.DataFrame(df.groupby(f).customer.nunique())
dx['sales'] = ds
dx['arpu'] = dx['sales'] / dx['customer']
av = go.Figure(go.Scatter(x=dx.index, y=dx['arpu'],
line_shape='spline'))
av.update_layout(
title="Average Revenue per Unit (Customer)",
autosize=True,
margin=dict(
l=50,
r=50,
b=0,
t=30,
pad=4
),
plot_bgcolor='white')
fig_av = html.Div(
dcc.Graph(
figure=av,
config={
'displayModeBar': False
},
)
),
return fig_av
def sunburst(data,period,item,customer,spv,salesperson,wh,switch):
f = filter(period)
g = switch_c(switch)
df = dropdown_filter(data,item,customer,spv,salesperson,wh)
sun = df.groupby([f,g,'item'])[['qty','sales']].sum()
sun = sun.reset_index()
fig = px.sunburst(sun, path=[g,'item'], values='qty',
color='sales',
color_continuous_scale='RdBu',
color_continuous_midpoint=np.average(sun['sales'], weights=sun['qty']))
fig.update_layout(
title="Qty and Sales per Items",
autosize=True,
margin=dict(
l=50,
r=50,
b=20,
t=50,
pad=4
))
plot = dcc.Graph(
id='sunburst-chart',
figure=fig,
config={
'displayModeBar': False
},
)
return plot
def fig_compare(period,item,customer,spv,salesperson,wh):
# df = base_data()
data = dropdown_filter(df,item,customer,spv,salesperson,wh)
if period == 'month':
id = 'monthly-comparison'
dfc = pd.pivot_table(data, values='sales', index=['month'], columns=['year'], aggfunc=np.sum)
yr = list(dfc.columns)
fig_compare = px.line(dfc, x=dfc.index, y=yr , title='Monthly Comparison', line_shape='spline')
elif period == 'week':
id = 'weekly-comparison'
dfc = pd.pivot_table(data, values='sales', index=['week'], columns=['year'], aggfunc=np.sum)
yr = list(dfc.columns)
fig_compare = px.line(dfc, x=dfc.index, y=yr, title='Weekly Comparison', line_shape='spline')
elif period == 'day':
id = 'daily-comparison'
dfc = pd.pivot_table(data, values='sales', index=['day'], columns=['year'], aggfunc=np.average)
yr = list(dfc.columns)
fig_compare = px.bar(dfc, x=dfc.index, y=yr, barmode='group', title='Weekdays Comparison')
fig_compare.update_layout(
plot_bgcolor='white'
)
plot_compare = dcc.Graph(
id=id,
figure=fig_compare,
config={
'displayModeBar': False
},
)
return plot_compare
def top_ten(data,category,item,customer,spv,salesperson,wh):
dF = dropdown_filter(data,item,customer,spv,salesperson,wh)
table = dF.groupby(category)[['qty','sales']].sum()
table = table.reset_index()
table = table.sort_values(by=['sales'], ascending=False)
table = pd.DataFrame(table)
table = table.head(5)
table['sales'] = table.apply(lambda x: "{:,}".format(x['sales']), axis=1)
table['qty'] = table['qty'].round()
table = dbc.Table.from_dataframe(table, striped=True, bordered=True, hover=True, size='sm',\
style={'font-size': 'small'})
return table
def sales_todate(period,item,customer,spv,salesperson,wh):
data = df
f = filter(period)
dsales = dropdown_filter(data,item,customer,spv,salesperson,wh)
sales = dsales.groupby(f)['sales'].sum()
sales = f'{sales[-1]:,}'
return sales
def change(period,item,customer,spv,salesperson,wh):
data = df
f = filter(period)
dchange = dropdown_filter(data,item,customer,spv,salesperson,wh)
sales = dchange.groupby(f)['sales'].sum()
if (sales[-1]-sales[-2])/sales[-2] <0:
percentage = round(((sales[-1]-sales[-2])/sales[-2])*100)
change = "Down " + str(percentage) + "% "
elif (sales[-1]-sales[-2])/sales[-2] >0:
percentage = round(((sales[-1]-sales[-2])/sales[-2])*100)
change = "Up " + str(percentage) + "% "
else:
change = "No changes "
return change
def scorecards(x,item, customer, spv, salesperson, warehouse):
title = x
title = title.upper()
v = sales_todate(x, item, customer, spv, salesperson, warehouse)
c = change(x, item, customer, spv, salesperson, warehouse)
p = period(x)
card = dbc.Card(
[
dbc.CardHeader(title + " Sales Performance"),
dbc.CardBody(
[
html.P("Rp"+v+" Revenue", className="card-title", style={'font-weight': 'bold'}),
html.P(c+"compared to last " + p, className="card-text"),
]
),
],
style={
"font-size": "medium",
"text-align": "center"},
)
return card
def none(v,w,x,y,z):
if v == []:
v = None
else:
v = v
if w == []:
w = None
else:
w = w
if x == []:
x = None
else:
w = w
if y == []:
y = None
else:
y = y
if z == []:
z = None
else:
z = z
return v,w,x,y,z
def scatter1():
# cluster_data = clust_data()
figy = go.Figure(go.Scatter(
x=cluster_data['sales'],
y=cluster_data['no_of_transaction'],
mode='markers',
text=cluster_data.index,
marker=dict(size=cluster_data['no_of_transaction'] ** (1/2))
))
figy.update_layout(
title="No. of Transaction vs Revenue Generated",
autosize=True,
margin=dict(
l=50,
r=50,
b=0,
t=50,
pad=4
), plot_bgcolor='white')
scatter1 = html.Div(
dcc.Graph(
figure=figy,
config={
'displayModeBar': False
},
)
),
return scatter1
def scatter2():
# cluster_data = clust_data()
figx = go.Figure(
px.histogram(cluster_data, x="sales", nbins=10)
)
figx.update_layout(
title="Revenue Generated Distribution",
autosize=True,
margin=dict(
l=50,
r=50,
b=0,
t=50,
pad=4
), plot_bgcolor='white')
scatter2 = html.Div(
dcc.Graph(
figure=figx,
config={
'displayModeBar': False
},
)
),
return scatter2
def daterange(start,end):
# df = base_data()
if start == None or end == None:
dx = df
else:
st = datetime.strptime(start, '%Y-%m-%d')
ed = datetime.strptime(end, '%Y-%m-%d')
dx = df[df['date'] >= st]
dx = dx[dx['date'] <= ed]
return dx
def parse_contents(contents, filename, date):
# engine = create_engine('sqlite:///data/df_sales.db', echo=False)
content_type, content_string = contents.split(',')
decoded = base64.b64decode(content_string)
try:
if 'csv' in filename:
# Assume that the user uploaded a CSV file
df = pd.read_csv(
io.StringIO(decoded.decode('utf-8')))
df.to_sql('sales', con=engine, if_exists='replace', index=False)
df = pd.read_sql_query("SELECT * from sales", engine)
elif 'xls' in filename:
# Assume that the user uploaded an excel file
df = pd.read_excel(io.BytesIO(decoded))
df.to_sql('sales', con=engine, if_exists='replace', index=False)
df = pd.read_sql_query("SELECT * from sales", engine)
except Exception as e:
print(e)
return html.Div([
'There was an error processing this file.'
])
return html.Div([
html.H5(filename),
html.H6(datetime.fromtimestamp(date)),
dash_table.DataTable(
data=df.to_dict('records'),
columns=[{'name': i, 'id': i} for i in df.columns]
),
html.Hr(), # horizontal line
# For debugging, display the raw contents provided by the web browser
html.Div('Raw Content'),
html.Pre(contents[0:200] + '...', style={
'whiteSpace': 'pre-wrap',
'wordBreak': 'break-all'
})
])
app = dash.Dash(
__name__, external_stylesheets=[dbc.themes.BOOTSTRAP],
meta_tags=[
{'name': 'viewport',
'content': 'width=device-width, initial-scale=1'}
]
)
app.title='Sales Performance Dashboards'
VALID_USERNAME_PASSWORD_PAIRS = {
}
auth = dash_auth.BasicAuth(
app,
VALID_USERNAME_PASSWORD_PAIRS
)
server = app.server
def serve_layout():
return html.Div([
dbc.Row([
dbc.Col(
html.Div(
html.Img(
src=app.get_asset_url("logo.png"),
className="logo", height=50,
),
), md=6
),
dbc.Col(
dcc.Upload(
id='upload-data',
children=html.Div([
'Drag and Drop or ',
html.A('Select Files')
]),
style={
'width': '90%',
'height': '60px',
'lineHeight': '60px',
'borderWidth': '1px',
'borderStyle': 'dashed',
'borderRadius': '5px',
'textAlign': 'center',
'margin': '10px'
},
# Allow multiple files to be uploaded
multiple=True
), md=6
)
]),
dbc.Row(dbc.Col(html.Div([
dbc.Row(
html.H5('Filter', style={'font-weight':'bold'})
),
dbc.Row([
dbc.Col(html.Div(dropdown('item')), md=2.4, style={'padding': 5}),
dbc.Col(html.Div(dropdown('customer')), md=2.4, style={'padding': 5}),
dbc.Col(html.Div(dropdown('spv')), md=2.4, style={'padding': 5}),
dbc.Col(html.Div(dropdown('salesperson')), md=2.4, style={'padding': 5}),
dbc.Col(html.Div(dropdown('wh')), md=2.4, style={'padding': 5}),
]),
], style={'padding': 20, 'margin-top': 20, 'margin-bottom': 20}), width=12)),
dbc.Row(dbc.Col(html.Div([
dbc.Row(dbc.Col(html.Div(html.H5('Overall Performance', style={'font-weight':'bold'})), style={'text-align': 'center'})),
dbc.Row(dbc.Col(html.Div(id='kpi-cards'), width=12)),
dbc.Row([
dbc.Col(
html.Div(dbc.Tabs([
dbc.Tab(label='Yearly', tab_id='tab-1'),
dbc.Tab(label='Quarterly', tab_id='tab-2'),
dbc.Tab(label='Monthly', tab_id='tab-3'),
dbc.Tab(label='Weekly', tab_id='tab-4'),
dbc.Tab(label='Daily', tab_id='tab-5')
], id='tabs', active_tab='tab-1'
), style={'padding': 10, 'text-align': 'center'}), md=4
),
dbc.Col(
html.Div(dcc.DatePickerRange(
id='date-range',
start_date_placeholder_text="Start Period",
end_date_placeholder_text="End Period",
calendar_orientation='vertical',
max_date_allowed=date.today()
), style={'padding': 10, 'text-align': 'center'}
), md=4
),
dbc.Col(
html.Div(dbc.RadioItems(
options=[
{"label": "None", "value": 1},
{"label": "Supervisor", "value": 2},
{"label": "Warehouse", "value": 3},
],
value=1,
id="radioitems-inline-input",
inline=True,
), style={'padding': 20, 'text-align': 'center'}), md=4
),
]),
dbc.Row([
dbc.Col(html.Div(id='graphs-content'), lg=4),
dbc.Col(html.Div(id='arpu'), lg=4),
dbc.Col(html.Div(id='sunburst'), lg=4),
]),
], style={'backgroundColor':'white', 'padding': 20, 'margin-top': 20, 'margin-bottom': 20, 'border-radius': '25px'}), width=12)),
dbc.Row(dbc.Col(html.Div([
dbc.Row(dbc.Col(html.Div(html.H5('Periodic Comparison', style={'font-weight':'bold'})), style={'text-align': 'center'})),
dbc.Row([
dbc.Col(html.Div(id='monthly-compare'), lg=4),
dbc.Col(html.Div(id='weekly-compare'), lg=4),
dbc.Col(html.Div(id='days-compare'), lg=4),
])
], style={'backgroundColor':'white', 'padding': 20, 'margin-top': 20, 'margin-bottom': 20, 'border-radius': '25px'}), width=12)),
dbc.Row(dbc.Col(html.Div([
dbc.Row(dbc.Col(html.Div(html.H5('Top Five', style={'font-weight':'bold'})), style={'text-align': 'center'})),
dbc.Row([
dbc.Col(html.Div(id='top-customers'), lg=6, xl=3),
dbc.Col(html.Div(id='top-items'), lg=6, xl=3),
dbc.Col(html.Div(id='top-spv'), lg=6, xl=3),
dbc.Col(html.Div(id='top-salesperson'), lg=6, xl=3),
]),
], style={'backgroundColor':'white', 'padding': 20, 'margin-top': 20, 'margin-bottom': 20, 'border-radius': '25px'}), width=12)),
dbc.Row(dbc.Col(html.Div([
dbc.Row(dbc.Col(html.Div(html.H5('Revenue & Customer Distribution', style={'font-weight':'bold'})), style={'text-align': 'center'})),
dbc.Row([
dbc.Col(scatter1(), md=6),
dbc.Col(scatter2(), md=6)
])
], style={'backgroundColor':'white', 'padding': 20, 'margin-top': 20, 'margin-bottom': 20, 'border-radius': '25px'}), width=12)),
html.Div(id='output-data-upload'),
],style={'padding': 25, 'backgroundColor':'#E5E4E2'})
app.layout = serve_layout
@app.callback(Output('output-data-upload', 'children'),
[Input('upload-data', 'contents')],
[State('upload-data', 'filename'),
State('upload-data', 'last_modified')])
def update_output(list_of_contents, list_of_names, list_of_dates):
if list_of_contents is not None:
children = [
parse_contents(c, n, d) for c, n, d in
zip(list_of_contents, list_of_names, list_of_dates)]
return children
@app.callback([Output('graphs-content', 'children'),
Output('arpu','children'),
Output('sunburst', 'children'),
Output('kpi-cards','children'),
Output('monthly-compare','children'),
Output('weekly-compare','children'),
Output('days-compare','children'),
Output('top-customers','children'),
Output('top-items','children'),
Output('top-spv','children'),
Output('top-salesperson','children'),
],
[Input('tabs', 'active_tab'),
Input('input1', 'value'),
Input('input2', 'value'),
Input('input3', 'value'),
Input('input4', 'value'),
Input('input5', 'value'),
Input('radioitems-inline-input', 'value'),
Input('date-range','start_date'),
Input('date-range','end_date'),
])
def render_content(at,item,customer,spv,salesperson,warehouse,switch,start,end):
item, customer, spv, salesperson, warehouse = none(item, customer, spv, salesperson, warehouse)
data = daterange(start,end)
cards = dbc.Row([dbc.Col(scorecards('ytd', item, customer, spv, salesperson, warehouse), md=3),
dbc.Col(scorecards('qtd', item, customer, spv, salesperson, warehouse), md=3),
dbc.Col(scorecards('mtd', item, customer, spv, salesperson, warehouse), md=3),
dbc.Col(scorecards('wtd', item, customer, spv, salesperson, warehouse), md=3),])
month = fig_compare('month', item, customer, spv, salesperson, warehouse)
week = fig_compare('week', item, customer, spv, salesperson, warehouse)
day = fig_compare('day', item, customer, spv, salesperson, warehouse)
top_customers = top_ten(data,'customer', item, customer, spv, salesperson, warehouse)
top_items = top_ten(data,'item', item, customer, spv, salesperson, warehouse)
top_spv = top_ten(data,'spv', item, customer, spv, salesperson, warehouse)
top_salespersons =top_ten(data,'sales_person', item, customer, spv, salesperson, warehouse)
if at == 'tab-1':
x = fig(data,"ytd",item,customer,spv,salesperson,warehouse,switch)
y = arpu(data,'ytd',item, customer, spv, salesperson, warehouse)
z = sunburst(data,"ytd",item,customer,spv,salesperson,warehouse,switch)
return x, y, z, cards, month, week, day, top_customers, top_items, top_spv, top_salespersons
elif at == 'tab-2':
x = fig(data,"qtd",item,customer,spv,salesperson,warehouse,switch)
y = arpu(data,'qtd',item, customer, spv, salesperson, warehouse)
z = sunburst(data,"qtd",item,customer,spv,salesperson,warehouse,switch)
return x, y, z, cards, month, week, day, top_customers, top_items, top_spv, top_salespersons
elif at == 'tab-3':
x = fig(data,"mtd",item,customer,spv,salesperson,warehouse,switch)
y = arpu(data,'mtd',item, customer, spv, salesperson, warehouse)
z = sunburst(data,"mtd",item,customer,spv,salesperson,warehouse,switch)
return x, y, z, cards, month, week, day, top_customers, top_items, top_spv, top_salespersons
elif at == 'tab-4':
x = fig(data,"wtd",item,customer,spv,salesperson,warehouse,switch)
y = arpu(data,'wtd',item, customer, spv, salesperson, warehouse)
z = sunburst(data,"wtd",item,customer,spv,salesperson,warehouse,switch)
return x, y, z, cards, month, week, day, top_customers, top_items, top_spv, top_salespersons
elif at == 'tab-5':
x = fig(data,"dt",item,customer,spv,salesperson,warehouse,switch)
y = arpu(data,'dt',item, customer, spv, salesperson, warehouse)
z = sunburst(data,"dt",item,customer,spv,salesperson,warehouse,switch)
return x, y, z, cards, month, week, day, top_customers, top_items, top_spv, top_salespersons
if __name__ == '__main__':
app.run_server(debug=True)