Plotting a grouped bar chart using Plotly from a pandas dataframe

Question

Very new to Ploty so sorry if this is an obvious one... I'm trying to create a grouped bar chart which includes the following information which is stored in SQL.

|-----------|------|-------------|
| RouteName | Hour | JourneyTime |
|-----------|------|-------------|
|Route #1   |6:00  |200          |
|Route #2   |6:00  |400          |
|Route #3   |6:00  |500          |
|Route #1   |7:00  |100          |
|Route #2   |7:00  |300          |
|Route #3   |7:00  |550          |
|Route #1   |8:00  |330          |
|Route #2   |8:00  |450          |
|Route #3   |8:00  |600          |

Essentially I want the Hour column as x, the JourneyTime column as y and the RouteName column to differentiate between the routes (different colours for different bars).

I've managed to get plotly (using the following code) to create a graph from a pandas dataframe. However it only shows one bar for each Hour and not the entire dataset.

import pandas as pd
import pymssql
import plotly
import plotly.graph_objs as go
from plotly.offline import *

ServerNm = str("ServerName")
DatabaseNm = str("DatabaseName")

SQLCon = pymssql.connect(host=ServerNm,database=DatabaseNm)

SQL_Query2 = '''SELECT [RouteName], [Hour], [JourneyTime] FROM [Dashboard].[dbo].[JTs_v2]'''

df2 = pd.read_sql(SQL_Query2, SQLCon)
SQLCon.close

plotly.offline.plot([go.Bar(x=df2.Hour, y=df2.JourneyTime,name='RouteName')])

Looking at the link Adding group bar charts as subplots in plotly I've seen that you can give it separate series of data as trace1 and trace2. However I will ultimately be linking this up with filter boxes in a Dash Dashboard (and the number of routes will vary) so I would require flexibility to have this change on the fly.

Any help that can be offered would be greatly appreciated.

Many thanks

Answer 1

you can change the number of traces you pass on the fly too. Maybe something like this:

df = pd.read_sql(query, con)
traces = [go.Bar(x=subset.Hour, 
                 y=subset.JourneyTime,
                 name=route) 
          for route, subset in df.groupby("RouteName")]
plotly.offline.plot(traces)

Answer 2

It is possible to create a loop using a separate dataframe as a filter. Not the most elegant solution, but it does work.

In the code below df1 gets a list of just the Routes and df2 is our complete dataset. From this it is possible to loop through the rows and generate a new dataframe (df3). This dataframe creates each bar which are all appended together to create the final plot.

import pandas as pd
import pymssql
import plotly
import plotly.graph_objs as go
from plotly.offline import *

ServerNm = str("ServerName")
DatabaseNm = str("DatabaseName")

SQLCon = pymssql.connect(host=ServerNm,database=DatabaseNm)

SQL_Query1 = '''SELECT [RouteName] FROM [Dashboard].[dbo].[JTs_v2] GROUP BY [RouteName]'''
SQL_Query2 = '''SELECT [RouteName], [Hour], [JourneyTime] FROM [Dashboard].[dbo].[JTs_v2]'''

df1 = pd.read_sql(SQL_Query1, SQLCon)
df2 = pd.read_sql(SQL_Query2, SQLCon)

SQLCon.close

bars = []

for index, row in df1.iterrows():

    route=row['RouteName']
    df3 = df2[df2.RouteName == route][['Hour', 'JourneyTime']]
    bars.append(
        go.Bar(
                x=df3.Hour,
                y=df3.JourneyTime,
                name=route))


fig = go.Figure(data=bars)
plotly.offline.plot(fig)