Interactive Plotly Int Slider

Question

Hi I'm fairly new to Python, Plotly and Jupyter Notebook. I would like to use a slider to select the number of days as the range in a query to which a graph is created from. My only issue is that I want the graph to automatically update on interaction with the slider, without having to re-run the query and graph creation. My code is below:

slider = widgets.IntSlider()
display(slider)
sliderVal = slider.value

df = pd.read_sql(f"""
SELECT CASE WHEN SiteID LIKE 3 THEN 'BLAH' 
        WHEN SiteID LIKE 4 THEN 'BLAHBLAH' 
        END AS Website, 
        COUNT(1) AS Count
FROM            viewName
WHERE        (TimeStamp > DATEADD(DAY, -{sliderVal}, GETDATE()))
GROUP BY SiteId
ORDER BY Count DESC
           """, conn)

data = [go.Bar(x=df.Website, y=df.Count)]
layout = go.Layout(
    xaxis=dict(
        title='Website'),
    yaxis=dict(
        title='Exception count'),
    title=f'Number of exceptions per user in the last {sliderVal} days')
chart = go.Figure(data=data, layout=layout, )
py.iplot(chart, filename='WebExceptions')

Thanks in advance!

Next time, try to provide a reproducible example, including a minimal set of data, so that people can run your example if needed. You will get quicker and more precise answers this way. — byouness, May 12 '18 at 21:46

byouness · Accepted Answer · 2018-05-15T14:03:04.017

2

If you do not want to rerun the query, then your data frame df must contain the results for all the values that you want the intslider widget to take, the function linked to the widget will then simply filter the data and redraw the graph with the new filtered data.

Here's an example with some dummy data:

import ipywidgets as widgets
import plotly.offline as py
import plotly.graph_objs as go
import pandas as pd
py.init_notebook_mode(connected = True)

# Dummy data, to be replaced with your query result for the range of sliderVal
df = pd.DataFrame({'Days': [1] * 3 + [2] * 4 + [3] * 5,
                  'Website': [1,2,3, 4,5,6,7, 8,9,10,11,12],
                  'Count': [10,5,30, 15,20,25,12, 18,17,30,23,27]})

def update_plot(sliderVal):
    filtered_df = df.query('Days== ' + str(sliderVal))
    data = [go.Bar(x = filtered_df.Website,
                   y = filtered_df.Count)]
    layout = go.Layout(
        xaxis = dict(title = 'Website'),
        yaxis = dict(title = 'Exception count'),
        title = f'Number of exceptions per user in the last {sliderVal} days')
    chart = go.Figure(data = data, layout = layout, )
    py.iplot(chart, filename = 'WebExceptions')

# links an IntSlider taking values between 1 and 3 to the update_plot function
widgets.interact(update_plot, sliderVal = (1, 3))

and here is the result with sliderVal = 2:

edited May 15 '18 at 14:03

answered May 12 '18 at 21:45

byouness

1,746
2
24
41

Do I have to sort the results from the query in a static `df` as you have done in the dummy data section? If not, what column of the `df` would I use instead of "Days" in `df.query`? – gcpreston May 15 '18 at 10:30
sort? you mean store? You need to fetch results for the values that you want your `sliderVal` to cover, so that moving the `sliderVal` will simply filter from your dataframe and display the results, rather than go fetch the results from the DB... I hope this is clear. – byouness May 15 '18 at 14:02
Sorry @byouness I did mean store. Will this still work if the `df` is populated from a query like I have done above, instead of the manually populated dummy data `df` you have provided? If so, what can I use instead of the array `Days`? Sorry for the bonehead questions – gcpreston May 16 '18 at 08:58
It's simple: you don't want to query the database each time the widget's value is changed, so you have to store the data in memory (in your df). Suppose you want to cover values between 1 and 3, the simplest would be to run your query 3 times with `WHERE (TimeStamp > DATEADD(DAY,-1,GETDATE()))`, then `...DATEADD(DAY,-2,GETDATE()))`, then `...DATEADD(DAY,-3,GETDATE()))` and put all these results in your dataframe, with `Days=1` in the first set, `Days=2`for the second and `days=3` for the third. You can combine these 3 queries into one SQL query but that's another question. I hope that helps. – byouness May 16 '18 at 09:04

Interactive Plotly Int Slider

1 Answers1