Assigning colours while adding traces to a plot in a loop

Hi all,

I’ve searched for hours on this topic but am still struggling.

I have a massive SQL database with information on boreholes and sensors. There are a few hundred boreholes and within each borehole is up to 10 sensors. The sensors have measured water levels on an hourly basis since 2013 (very large set of points) - I am using an export of the SQL database, so it is static.

I am writing a program that connects to the SQL database, draws in records for a specified set of boreholes (at the moment I am bringing these in by hard coding the names in a SQL query within the code - ideally I would do this by entering names in a text box). The script then retrieves the list of sensors within the specified borehole(s). It also plots the elevation of the sensor.

At the moment it works well to plot a single borehole and its elevation. I have also got it running with two holes but I can’t figure out how to group the trace colours by borehole.

My code at the moment:

 # Import modules

import pypyodbc
import plotly.offline as offline
import plotly as pyy
import plotly.plotly as py
import plotly.figure_factory as ff
import plotly.graph_objs as go
import pandas as pd
import matplotlib.pyplot as plt
import itertools

% matplotlib inline
offline.init_notebook_mode(connected=True)

# Define SQL connection

conn = pypyodbc.connect(“DRIVER={SQL Server};Server=UK-DC01;Database=MYDBNAME;Trusted_Connection=Yes”)

# Define SQL cursor

cur=conn.cursor()

# Import selected hole data

qry1 = (“SELECT * FROM vw_DATA WHERE HOLE_NAME = ‘PZ-213’ OR HOLE_NAME = ‘PZ-241’”)
cur.execute(qry1)
result = cur.fetchall()

# Convert hole data to pandas table

pd.set_option(‘display.max_column’, 13)
df = pd.DataFrame( [[ij for ij in i] for i in result])
df.rename(columns={0: ‘ID_PIEZO’, 1: ‘PZ_NAME’, 2: ‘ID_HOLE’, 3: ‘HOLE_NAME’, 4: ‘TM_STAMP’, 5: ‘LGF’, 6: ‘DIGITS_INIT’, 7: ‘Digits’, 8: ‘ft_H20’, 9: ‘TEMP_ADJ’, 10: ‘WL_Elev’, 11: ‘Piezo_RL’, 12: ‘GEO_UNIT’}, inplace=True)
df.sort_values(by=‘TM_STAMP’, ascending=True)

# Get list of piezometers in hole

piezo_list = df.PZ_NAME.unique()
N = len(piezo_list)

# Get list of holes plotting

hole_list = df.HOLE_NAME.unique()
N = len(hole_list)

# Create lithology table    

litho1 = df.drop_duplicates(‘PZ_NAME’)
litho2 = litho1.iloc[:,[1, 11, 12]]

# Create hole plot

figure = pyy.tools.make_subplots(rows=1, cols=1, print_grid=False)

for pz in piezo_list:
records = df[df.PZ_NAME == (pz)]

trace_WL = go.Scattergl(
    x = records.TM_STAMP,
    y = records.WL_Elev,
    mode = 'markers',
    name='{}: WL'.format(pz),
    marker = dict(size = 2))

trace_RL = go.Scattergl(
    x = records.TM_STAMP,
    y = records.Piezo_RL,
    mode = 'lines',
    name='{}: RL'.format(pz),
    line = dict(
        color = ('rgb(160, 160, 160)'),
        width = 2,
        dash = 'dot'))

figure.append_trace(trace_WL, 1, 1)
figure.append_trace(trace_RL, 1 ,1)

figure['layout'].update(
    height=600, 
    width=800,
    autosize=False,
    title='{}'.format(records.iloc[1,3]), 
    yaxis1=go.layout.YAxis(title='WL Elevation',linecolor='#000',ticks='outside',tickcolor='#000',mirror='all',domain=[0,0],anchor='y1'), 
    xaxis1=go.layout.XAxis(linecolor='#000',ticks='outside',tickcolor='#000',mirror='all',domain=[0,0],anchor='x1'),
)

offline.iplot(figure)

I want each trace for PZ-213 to be a single colour, and each trace for PZ-241 to be a different single colour. I would like to be able to add say 10 holes and it plot each holes traces in a separate colour.

I have tried adding a loop for holes and colours like below but it crashes computer:

# Create color list

colors = [‘#e6194b’, ‘#3cb44b’, ‘#ffe119’, ‘#4363d8’, ‘#f58231’, ‘#911eb4’, ‘#46f0f0’, ‘#f032e6’, ‘#bcf60c’, ‘#fabebe’, ‘#008080’, ‘#e6beff’, ‘#9a6324’, ‘#fffac8’, ‘#800000’, ‘#aaffc3’, ‘#808000’, ‘#ffd8b1’, ‘#000075’, ‘#808080’, ‘#ffffff’, ‘#000000’]

for hole in hole_list:
for i in colors:
trace_color = i
for pz in piezo_list:
records = df[df.PZ_NAME == (pz)]

        trace_WL = go.Scattergl(
            x = records.TM_STAMP,
            y = records.WL_Elev,
            mode = 'markers',
            name='{}: WL'.format(pz),
            marker = dict(size = 2, color=trace_color))

        trace_RL = go.Scattergl(
            x = records.TM_STAMP,
            y = records.Piezo_RL,
            mode = 'lines',
            name='{}: RL'.format(pz),
            line = dict(
                color = ('rgb(160, 160, 160)'),
                width = 2,
                dash = 'dot'))

        figure.append_trace(trace_WL, 1, 1)
        figure.append_trace(trace_RL, 1 ,1)

    figure['layout'].update(
        height=600, 
        width=800,
        autosize=False,
        title='{}'.format(records.iloc[1,3]), 
        yaxis1=go.layout.YAxis(title='WL Elevation',linecolor='#000',ticks='outside',tickcolor='#000',mirror='all',domain=[0,0],anchor='y1'), 
        xaxis1=go.layout.XAxis(linecolor='#000',ticks='outside',tickcolor='#000',mirror='all',domain=[0,0],anchor='x1'),
    )

I’m brand new to Python so am just using plotly in Jupyter notebooks at the moment. I do want to get this into Dash (struggling a bit getting the plot to work in Dash at the moment) so I can include several different plots pertaining to each hole in one HTML page, include tables of other data about the holes, and use text boxes and dropdowns to feed values into the SQL query.

Any advice or ways to make this more efficient would be very much appreciated!

Hi @lbel ,

Your general approach in the colors loop looks good. The problem might be in the call to append_trace, this won’t work unless you created the figure using plotly.tools.make_subplots. Instead, I’d recommend creating a list with all of your traces, and then adding them to the figure with figure.add_traces(trace_list).

Something like:

trace_list = []
for hole in hole_list:
    for i in colors:
        trace_color = i
        for pz in piezo_list:
            records = df[df.PZ_NAME == (pz)]

            trace_WL = go.Scattergl(
                x = records.TM_STAMP,
                y = records.WL_Elev,
                mode = 'markers',
                name='{}: WL'.format(pz),
                marker = dict(size = 2, color=trace_color))

            trace_RL = go.Scattergl(
                x = records.TM_STAMP,
                y = records.Piezo_RL,
                mode = 'lines',
                name='{}: RL'.format(pz),
                line = dict(
                    color = ('rgb(160, 160, 160)'),
                    width = 2,
                    dash = 'dot'))

            trace_list.append(trace_WL)
            trace_list.append(trace_RL)

figure.add_traces(trace_list)
...

Hope that helps!
-Jon

Thanks Jon, I’m implementing your advice now. However, how do I initialise a figure that I’m going to add traces to if I can’t use “figure = pyy.tools.make_subplots(rows=1, cols=1, print_grid=False)” please?

You can create the initial figure with

import plotly.graph_objs as go
figure = go.Figure()

-Jon