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

robertwest · Accepted Answer · 2018-11-19 19:17:58Z

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)

answered Nov 19, 2018 at 19:17

robertwest

9527 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gavin Over a year ago

Thanks for your help. It does load in all the bars, however it just loads in the same route 3 times e.g. Route 2 would be shown in 6:00am in 3 separate bars instead of all routes. I think I may need to set up a loop on the dataframe first (to filter for each route) before trying to load it into a bar. Will let you know how it goes.

robertwest Over a year ago

There’s already a loop here inside the list comprehension. Yeah you could just do a loop, filter the df, create the Bar and append the result to a list, that should give you the same results. Not sure why it wasn’t working as above but if you play around you’ll develop more intuition around it

Gavin · Accepted Answer · 2018-11-20 14:52:12Z

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)

Collectives™ on Stack Overflow

Plotting a grouped bar chart using Plotly from a pandas dataframe

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related