With the code pasted here one can graph two time series on separate graphs, two instances of @app_callback(). I would like to place the two series on the same x-y axis, i.e. one graph two series; one instance of @app_callback()
-- coding: utf-8 --
“”"
dash_twoseries.py
Created on Tuesday Jan 28, 2020
-Creates two time series graphs
-Pick the variables to graph via drop down menu
-Pick the time span of the graph (1m,6m,1yr,5yr,max) via radio buttons
“”"
-- coding: utf-8 --
#import plotly dash components
import dash
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output
import pandas as pd
#import mysql DB components
import mysql.connector
from mysql.connector import Error
#other import
from datetime import datetime, timedelta
external_stylesheets = [‘https://codepen.io/chriddyp/pen/bWLwgP.css’]
app = dash.Dash(name, external_stylesheets=external_stylesheets)
################## Database Connection - measure_st - get a list of source tables
try:
mydb = mysql.connector.connect(host= ‘xxxx’
database= ‘xxxxx’,
user= ‘xxxxx’,
password= ‘xxxx’)
#declare cursor to interact w/DB
mycursor=mydb.cursor()
#declare a querry
myquery = “(SELECT source_table, datum1 from measure_st)”
#execute the cursor
mycursor.execute(myquery)
#Fetch records and covert into a data frame (df)
df=pd.DataFrame(mycursor.fetchall(), columns=[‘source_table’,‘datum1’])
except mysql.connector.Error as error:
print(“Failed to get record from MySQL table: {}”.format(error))
finally:
if (mydb.is_connected()):
mycursor.close()
mydb.close()
#print(“MySQL connection is closed”)
#Build a list of source tables
physmondb_tables = df[‘source_table’].unique()
######################function to build query statement for a physical dat table during a certain time-span
def db_query(pastdatet,dfmeasure_st):
#convert past datetime to string
str_pastdatet = datetime.strftime(pastdatet, ‘%Y-%m-%d %H:%M:%S’)
datum = dfmeasure_st[0][0]
table1 = dfmeasure_st[0][1]
table2 = dfmeasure_st[0][2]
#print("in db_query")
#print(datum1)
#print(table1)
#print(table2)
#print(str_pastdatet)
myselect = "(SELECT " +table1+ ".date_t, " +table1+ "." + datum
myfrom = " FROM " +table1
myinner = " INNER JOIN " +table2
myon = " ON " +table1+ ".date_t = " +table2+ ".date_t "
mywhere = " WHERE " +table1+ ".del=0 and " +table2+ ".del=0 and " +table1+ ".date_t>'" +str_pastdatet+ "')"
#Fetch date_t and datum-values from physmon table
dbquery = myselect + myfrom + myinner + myon + mywhere
return dbquery
######################function to build query statement for a physical dat table for MAX TIME SPAN
def db_maxquery(dfmeasure_st):
datum = dfmeasure_st[0][0]
table1 = dfmeasure_st[0][1]
table2 = dfmeasure_st[0][2]
#print("in db_query")
print(datum)
print(table1)
print(table2)
#print(str_pastdatet)
myselect = "(SELECT " +table1+ ".date_t, " +table1+ "." + datum
myfrom = " FROM " +table1
myinner = " INNER JOIN " +table2
myon = " ON " +table1+ ".date_t = " +table2+ ".date_t "
mywhere = " WHERE " +table1+ ".del=0 and " +table2+ ".del=0)"
#Fetch date_t and datum-values from physmon table
dbquery = myselect + myfrom + myinner + myon + mywhere
return dbquery
####################### Web graphics here…
2 drop-down menu windows and 5 radio options
app.layout = html.Div([
html.Div([
dcc.Dropdown(
id=‘yaxis-series1’,
options=[{‘label’:i,‘value’:i} for i in physmondb_tables],
value=‘metpark_crane25m_at’
),
dcc.Dropdown(
id=‘yaxis-series2’,
options=[{‘label’:i,‘value’:i} for i in physmondb_tables],
value=‘metpark_crane25m_atmn’
),
dcc.RadioItems(
id=‘timespan’,
options=[{‘label’: i, ‘value’: i} for i in [‘one month’, ‘six months’, ‘one year’, ‘five years’, ‘max’]],
value=‘one month’,
labelStyle={‘display’: ‘inline-block’}
)
],style={‘width’: ‘40%’, ‘display’: ‘inline-block’}),
dcc.Graph(id=‘physmon-grapha’),
dcc.Graph(id=‘physmon-graphb’)
])
#callback for grapha - aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
@app.callback(
Output(‘physmon-grapha’, ‘figure’),
[Input(‘yaxis-series1’, ‘value’),
Input(‘yaxis-series2’, ‘value’),
Input(‘timespan’, ‘value’)])
#Values passed by the order listed on callback
def update_figure(yaxis_s1, yaxis_s2, xaxis_timespan):
#create a list
dfphysmon_list=[]
################## Database Connection - select ......
try:
mydb = mysql.connector.connect(host= 'xxxxx',
database= 'xxxxxx',
user= 'xxxxx',
password= 'xxxxxx')
#declare cursor to interact w/DB
mycursor=mydb.cursor()
#query measure_st for yaxis_s1 series
mydatumquery = "(SELECT datum1, last_date_t from measure_st where source_table = '" + yaxis_s1 + "')"
mycursor.execute(mydatumquery) #execute the cursor/query
my_strecord = mycursor.fetchone() #Fetch one record (with datum and last_date_t)
my_stlist=list(my_strecord) #Add the record returned to a list
#datum converted to string
strdatum = str(my_stlist[0]) #the datum
strtable = yaxis_s1 #the main table_name for top graph
strtable2 = yaxis_s2 #the other table to inner join on date_time
mylastdatet = my_stlist[1] #the last_date_t
#add table attributes to an indexed list
dfphysmon_list.append([strdatum,strtable,strtable2,mylastdatet])
#print(dfphysmon_list)
################# Select according to timespan
#Executes the data query based on the timespan(pastdatet) and a data-frame w/the names of the physical data tables
if xaxis_timespan == 'one month':
pastdatet = mylastdatet - timedelta(days = 31)
mytbquery = db_query(pastdatet,dfphysmon_list) #function call
elif xaxis_timespan == 'six months':
pastdatet = mylastdatet - timedelta(days = 180)
mytbquery = db_query(pastdatet,dfphysmon_list) #function call
elif xaxis_timespan == 'one year':
pastdatet = mylastdatet - timedelta(days = 365)
#Fetch date_t and datum-values from physmon table
mytbquery = db_query(pastdatet,dfphysmon_list) #function call
elif xaxis_timespan == 'five years':
pastdatet = mylastdatet - timedelta(days = 365*5)
#Fetch date_t and datum-values from physmon table
mytbquery = db_query(pastdatet,dfphysmon_list) #function call
elif xaxis_timespan == 'max':
#Fetch date_t and datum-values from physmon table
mytbquery = db_maxquery(dfphysmon_list) #function call
else: #Fetch 3 months
pastdatet = mylastdatet - timedelta(days = 90)
mytbquery = db_query(pastdatet,dfphysmon_list)
#execute the cursor
mycursor.execute(mytbquery)
#Build a data frame with the returned values
dfphysmon=pd.DataFrame(mycursor.fetchall(), columns=['date_t', strdatum])
#print(dfphysmon)
except mysql.connector.Error as error:
print("Failed to get record from MySQL table: {}".format(error))
finally:
if (mydb.is_connected()):
mycursor.close()
mydb.close()
#print("MySQL connection for grapha is closed")
#Graph values returned to callback
#Graph values returned to callback
return {
'data':[dict(
x=dfphysmon['date_t'],
y=dfphysmon[strdatum],
mode='points',
name=yaxis_s1
)],
'layout': dict(
yaxis={
'title':strdatum,
'type': 'linear'
},
margin={'l':50, 'b':20, 't':20, 'r':20},
height=400,
showlegend=True,
hovermode='closest'
)
}
#callback for graphb - bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
@app.callback(
Output(‘physmon-graphb’, ‘figure’),
[Input(‘yaxis-series2’, ‘value’),
Input(‘yaxis-series1’, ‘value’),
Input(‘timespan’, ‘value’)])
#Values passed by the order listed on callback
def update_figure(yaxis_s2, yaxis_s1, xaxis_timespan):
#create a list
dfphysmon_list=[]
################## Database Connection - select ......
try:
mydb = mysql.connector.connect(host= 'xxxxx',
database= 'xxxxx',
user= 'xxxxx',
password= 'xxxxxx')
#declare cursor to interact w/DB
mycursor=mydb.cursor()
#query measure_st for yaxis_s2 series
mydatumquery = "(SELECT datum1, last_date_t from measure_st where source_table = '" + yaxis_s2 + "')"
mycursor.execute(mydatumquery) #execute the cursor/query
my_strecord = mycursor.fetchone() #Fetch one record (with datum and last_date_t)
my_stlist=list(my_strecord) #Add the record returned to a list
#datum converted to string
strdatum = str(my_stlist[0]) #the datum
strtable = yaxis_s2 #the main table_name for bottom graph
strtable2 = yaxis_s1 #the table to inner join on time-stamp
mylastdatet = my_stlist[1] #the last_date_t
#add table attributes to an indexed list
dfphysmon_list.append([strdatum,strtable,strtable2,mylastdatet])
#print("Callback data frame measure_st graphb")
#print(dfphysmon_list)
#print('\n')
################# Select according to timespan
#Executes the data query based on the timespan(pastdatet) and a data-frame w/the names of the physical data tables
if xaxis_timespan == 'one month':
pastdatet = mylastdatet - timedelta(days = 31)
mytbquery = db_query(pastdatet,dfphysmon_list) #function call
elif xaxis_timespan == 'six months':
pastdatet = mylastdatet - timedelta(days = 180)
mytbquery = db_query(pastdatet,dfphysmon_list) #function call
elif xaxis_timespan == 'one year':
pastdatet = mylastdatet - timedelta(days = 365)
#Fetch date_t and datum-values from physmon table
mytbquery = db_query(pastdatet,dfphysmon_list) #function call
elif xaxis_timespan == 'five years':
pastdatet = mylastdatet - timedelta(days = 365*5)
#Fetch date_t and datum-values from physmon table
mytbquery = db_query(pastdatet,dfphysmon_list) #function call
elif xaxis_timespan == 'max':
#Fetch date_t and datum-values from physmon table
mytbquery = db_maxquery(dfphysmon_list) #function call
else: #Fetch 3 months
pastdatet = mylastdatet - timedelta(days = 90)
mytbquery = db_query(pastdatet,dfphysmon_list)
#execute the cursor to query the physical DB
mycursor.execute(mytbquery)
#Build a data frame with the returned values
dfphysmon=pd.DataFrame(mycursor.fetchall(), columns=['date_t', strdatum])
#print(dfphysmon)
except mysql.connector.Error as error:
print("Failed to get record from MySQL table: {}".format(error))
finally:
if (mydb.is_connected()):
mycursor.close()
mydb.close()
print("MySQL connection for graphb is closed")
#Graph values returned to callback
#Graph values returned to callback
return {
'data':[dict(
x=dfphysmon['date_t'],
y=dfphysmon[strdatum],
mode='points',
name=yaxis_s2
)],
'layout': dict(
yaxis={
'title':strdatum,
'type': 'linear'
},
margin={'l':50, 'b':20, 't':20, 'r':20},
height=400,
showlegend=True,
hovermode='closest'
)
}
if name == ‘main’:
app.run_server(debug=True)