Ytukyg

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

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)

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)