✊🏿 Black Lives Matter. Please consider donating to Black Girls Code today.
🧬 Learn how to build RNA-Seq data apps with Python & Dash. Register for the May 20 Webinar!

Help in plotting two time series with one Dash callback, i.e. on the same x-y axis

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)