Use a worksheet template and adding existing signals before pushing.

[Ben Hines]

I using a worksheet template and have successfully filled in the worksheet parameters with both existing signals and signals that my code has calculated and it works perfectly.  I would also like to add signals to the worksheet do not correspond to any of the worksheet parameters.  I thought I could simply add to the worksheet display_items, but this doesn't seem to work.  When I push to the server, the signals corresponding to worksheet parameters are included, but the additional signals are not.  Any ideas on how I can accomplish this? 

[Mark Derbecker]

Hi Ben-- this should work. I just checked and there's a test in the SPy code that confirms the functionality.

Can you try to create a minimal reproduction of the problem using example data so that you can post here?

[Mark Derbecker]

I just tried a simple case like this and it seemed to work:

area_c_temperature = spy.search({'Path': 'Example >> Cooling Tower 1 >> Area C', 'Name': 'Temperature'})
worksheet.display_items = pd.concat([worksheet.display_items, area_c_temperature], ignore_index=True)

(where `worksheet` is a template)

[Ben Hines]

Hi @Mark Derbecker.  Yep!  That example is what I was looking at.  I'll try to summarize how I'm going about this.  One thing to note is that I am setting the parameters of the worksheet with pre-existing signals as follows:

• Average of observed values:  This signal is created and pushed in code prior to what is shown below.  
• Spec limits + target value:  These three are pre-existing signals that are globally available.
• A scorecard metric:  Similar to the average of the observed values, this is computed and pushed in code prior to the code below.

All of the above show up in the resulting chart without issue.  I just can't seem to get the observed value signals, which are similar to the spec limits and target values in that they are pre-existing and globally available, to be included in the chart.

# worksheet is a worksheet that I loaded as a template
# At this point, I've already set the appropriate parameters in the template.  
# Now, I want to add some existing signals to the display_items, but want them to 
# be in the same lane and have the same axis as the 'Average per Work Order' item.
target_lane = worksheet.display_items.loc[
  worksheet.display_items['Name'] == 'Average per Work Order', 'Lane'
].iloc[0]

target_axis_group = worksheet.display_items.loc[
  worksheet.display_items['Name'] == 'Average per Work Order', 'Axis Group'
].iloc[0]

target_axis_align = worksheet.display_items.loc[
  worksheet.display_items['Name'] == 'Average per Work Order', 'Axis Align'
].iloc[0]

# observed_value_signals is a dataframe that resulted from searching for the 
# observed values that were used to compute the average.

# Build a dataframe that has display information for the observed values.
# Note that I'm extracting the 'ID', 'Type' and 'Name' columns from 
# observed_value_signals.  Perhaps this is not correct? 
ovs_display_items = observed_value_signals[['ID', 'Type', 'Name']]
ovs_display_items['Line Style'] = 'Solid' # Line Style 
ovs_display_items['Line Width'] = 1.0 # Line Width
ovs_display_items['Lane'] = target_lane
ovs_display_items['Color'] = '#0070c0' # Blue
ovs_display_items['Samples Display'] = 'Line'
ovs_display_items['Axis Align'] = target_axis_align
ovs_display_items['Axis Group'] = target_axis_group
ovs_display_items['Axis Show'] = True
ovs_display_items['Axis Auto Scale'] = True
ovs_display_items['Selected'] = False

# And now, concatenate the observed value display info with the 
# worksheet's display_items.
control_chart_worksheet.display_items = pd.concat([
  control_chart_worksheet.display_items,
  ovs_display_items
], ignore_index=True)

# Finally... Push to the server
spy.push(
  metadata=None, 
  workbook=workbook_name, 
  worksheet=control_chart_worksheet,
  quiet=True)

 

Edited 5 hours ago by Ben Hines

[Mark Derbecker]

OK Ben, I'll try to recreate this locally and see if I can get the same problem to occur. It may take me a couple days given my schedule but I'll keep you up to date.