Prespective XY stacked Bar Chart- transform script to add new series

Ignition
1

I have been struggling with this for a few weeks now.
I need to create an hour-by-hour loss chart on a dashboard. it is a stacked bar chart, that is updated every hour. the X-axis shows each hour since the start of the shift, and the y-axis displays a bar representing the number of minutes that a given machine has experienced a downtime loss by loss type. I have a query tag that retrieves the aggregated loss for each hour of production grouped by loss type/description. the XY chart has a data source binding to the tag and I have created a transform script to pivot the data so that the first column is the Hour_start and the rest are the different loss types.

I have been trying to create a binding on the series to the datasource, and add a transform script. this script will copy the 1st series as a structure reference, then
iterate through the data source, create a new series for each loss type in the data source, and update the series >.data> y with the loss type. and append it to the series array. The issue I am encountering is due to the lack of deepCopy in Jypthon. I am slightly new to ignition and could be doing this the hard way. Is their any suggestion on how I can achieve this? is there a way to create a clean series object and then add the required parameters and then append to the series array?
thanks.

2

Copy your XY chart JSON and post it here (formatting as code). Do the same with the data (because the binding won't work for us). Then we can try it out for you.

3

I hope I did this correctly. I added a clipboard copy of the pivot result data

[
  {
    "type": "ia.chart.xy",
    "version": 0,
    "props": {
      "xAxes": [
        {
          "name": "hour",
          "label": {
            "enabled": true,
            "text": "Time",
            "color": ""
          },
          "visible": true,
          "tooltip": {
            "enabled": true,
            "text": "",
            "cornerRadius": 3,
            "pointerLength": 4,
            "background": {
              "color": "",
              "opacity": 1
            }
          },
          "inversed": false,
          "render": "category",
          "category": {
            "break": {
              "enabled": false,
              "startCategory": "LOSS_DESCRIPTION",
              "endCategory": "HH_LOSS",
              "size": 0.05
            }
          },
          "date": {
            "baseInterval": {
              "enabled": false,
              "timeUnit": "hour",
              "count": 1,
              "skipEmptyPeriods": false
            },
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "break": {
              "enabled": false,
              "startDate": "",
              "endDate": "",
              "size": 0.05
            },
            "inputFormat": "yyyy-MM-dd kk:mm:ss",
            "format": "M/d"
          },
          "value": {
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "logarithmic": false,
            "break": {
              "enabled": false,
              "startValue": 0,
              "endValue": 100,
              "size": 0.05
            },
            "format": "#,###.##"
          },
          "appearance": {
            "opposite": false,
            "inside": false,
            "labels": {
              "color": "",
              "opacity": 1,
              "rotation": 0,
              "verticalCenter": "middle",
              "horizontalCenter": "middle"
            },
            "grid": {
              "color": "",
              "opacity": 1,
              "dashArray": "",
              "minDistance": 60,
              "position": 0.5
            },
            "font": {
              "size": "",
              "weight": 500
            }
          }
        }
      ],
      "yAxes": [
        {
          "name": "loss",
          "label": {
            "enabled": true,
            "text": "Process Temp",
            "color": ""
          },
          "visible": true,
          "tooltip": {
            "enabled": true,
            "text": "",
            "cornerRadius": 3,
            "pointerLength": 4,
            "background": {
              "color": "",
              "opacity": 1
            }
          },
          "inversed": false,
          "render": "value",
          "category": {
            "break": {
              "enabled": false,
              "startCategory": "",
              "endCategory": "",
              "size": 0.05
            }
          },
          "date": {
            "baseInterval": {
              "enabled": false,
              "timeUnit": "hour",
              "count": 1,
              "skipEmptyPeriods": false
            },
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "break": {
              "enabled": false,
              "startDate": "",
              "endDate": "",
              "size": 0.05
            },
            "inputFormat": "yyyy-MM-dd kk:mm:ss",
            "format": "M/d/yyyy HH:mm:ss"
          },
          "value": {
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "logarithmic": false,
            "break": {
              "enabled": false,
              "startValue": 0,
              "endValue": 100,
              "size": 0.05
            },
            "format": "#,###.##"
          },
          "appearance": {
            "opposite": false,
            "inside": false,
            "labels": {
              "color": "",
              "opacity": 1,
              "rotation": 0,
              "verticalCenter": "middle",
              "horizontalCenter": "middle"
            },
            "grid": {
              "color": "",
              "opacity": 1,
              "dashArray": "",
              "minDistance": null,
              "position": 0.5
            },
            "font": {
              "size": "",
              "weight": 500
            }
          }
        }
      ],
      "series": [
        {
          "name": "",
          "label": {
            "text": ""
          },
          "visible": true,
          "hiddenInLegend": false,
          "defaultState": {
            "visible": true
          },
          "data": {
            "source": "example",
            "x": "HOUR_START",
            "y": "CILR"
          },
          "xAxis": "hour",
          "yAxis": "loss",
          "zIndex": 0,
          "tooltip": {
            "enabled": true,
            "text": "{name}: [bold]{valueY}[/]",
            "cornerRadius": 3,
            "pointerLength": 4,
            "background": {
              "color": "",
              "opacity": 1
            }
          },
          "render": "column",
          "candlestick": {
            "open": {
              "x": "",
              "y": ""
            },
            "high": {
              "x": "",
              "y": ""
            },
            "low": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "fill": {
                "color": "",
                "opacity": 1
              },
              "stroke": {
                "color": "",
                "opacity": 1,
                "width": 1
              },
              "stacked": false,
              "deriveFieldsFromData": {
                "fill": {
                  "color": "",
                  "opacity": ""
                },
                "stroke": {
                  "color": "",
                  "opacity": "",
                  "width": ""
                }
              },
              "heatRules": {
                "enabled": false,
                "max": "",
                "min": "",
                "dataField": ""
              }
            }
          },
          "column": {
            "open": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "fill": {
                "color": "",
                "opacity": 1
              },
              "stroke": {
                "color": "",
                "opacity": 1,
                "width": 1
              },
              "stacked": true,
              "width": null,
              "height": null,
              "deriveFieldsFromData": {
                "fill": {
                  "color": "",
                  "opacity": ""
                },
                "stroke": {
                  "color": "",
                  "opacity": "",
                  "width": ""
                }
              },
              "heatRules": {
                "enabled": false,
                "max": "",
                "min": "",
                "dataField": ""
              }
            }
          },
          "line": {
            "open": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "connect": true,
              "tensionX": 1,
              "tensionY": 1,
              "minDistance": 0.5,
              "stroke": {
                "width": 3,
                "opacity": 1,
                "color": "",
                "dashArray": ""
              },
              "fill": {
                "opacity": 0,
                "color": ""
              },
              "bullets": [
                {
                  "enabled": false,
                  "render": "circle",
                  "width": 10,
                  "height": 10,
                  "label": {
                    "text": "{value}",
                    "position": {
                      "dx": 0,
                      "dy": 0
                    }
                  },
                  "fill": {
                    "color": "",
                    "opacity": 1
                  },
                  "stroke": {
                    "color": "",
                    "opacity": 1,
                    "width": 1
                  },
                  "rotation": 0,
                  "tooltip": {
                    "enabled": true,
                    "text": "{name}: [bold]{valueY}[/]",
                    "cornerRadius": 3,
                    "pointerLength": 4,
                    "background": {
                      "color": "",
                      "opacity": 1
                    }
                  },
                  "deriveFieldsFromData": {
                    "fill": {
                      "color": "",
                      "opacity": ""
                    },
                    "stroke": {
                      "color": "",
                      "opacity": "",
                      "width": ""
                    },
                    "rotation": ""
                  },
                  "heatRules": {
                    "enabled": false,
                    "max": 100,
                    "min": 2,
                    "dataField": ""
                  }
                }
              ]
            }
          },
          "stepLine": {
            "open": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "connect": true,
              "tensionX": 1,
              "tensionY": 1,
              "minDistance": 0.5,
              "stroke": {
                "width": 3,
                "opacity": 1,
                "color": "",
                "dashArray": ""
              },
              "fill": {
                "opacity": 0,
                "color": ""
              },
              "bullets": [
                {
                  "enabled": true,
                  "render": "circle",
                  "width": 10,
                  "height": 10,
                  "label": {
                    "text": "{value}",
                    "position": {
                      "dx": 0,
                      "dy": 0
                    }
                  },
                  "fill": {
                    "color": "",
                    "opacity": 1
                  },
                  "stroke": {
                    "color": "",
                    "opacity": 1,
                    "width": 1
                  },
                  "rotation": 0,
                  "tooltip": {
                    "enabled": true,
                    "text": "{name}: [bold]{valueY}[/]",
                    "cornerRadius": 3,
                    "pointerLength": 4,
                    "background": {
                      "color": "",
                      "opacity": 1
                    }
                  },
                  "deriveFieldsFromData": {
                    "fill": {
                      "color": "",
                      "opacity": ""
                    },
                    "stroke": {
                      "color": "",
                      "opacity": "",
                      "width": ""
                    },
                    "rotation": ""
                  },
                  "heatRules": {
                    "enabled": false,
                    "max": 100,
                    "min": 2,
                    "dataField": ""
                  }
                }
              ]
            }
          }
        }
      ],
      "dataSources": {},
      "series_1": null
    },
    "meta": {
      "name": "XYChart_0"
    },
    "position": {
      "x": 40,
      "y": 34,
      "height": 379,
      "width": 678
    },
    "custom": {},
    "propConfig": {
      "props.dataSources.example": {
        "binding": {
          "type": "tag",
          "config": {
            "mode": "direct",
            "tagPath": "~path to tag~",
            "fallbackDelay": 2.5
          },
          "transforms": [
            {
              "code": "    newDataset = system.dataset.toPyDataSet(value)\n    pivot_data = {}\n    loss_types = set()\n    logger = system.util.getLogger(\"pivot\")\n    # Iterate through rows and organize data\n    for row in newDataset:\n        hour_start = row['HOUR_START']\n        loss_description = str(row['LOSS_DESCRIPTION'])\n        hh_loss = row['HH_LOSS']\n\n        # Add loss_description to loss_types for column headers\n        loss_types.add(loss_description)\n\n        # Organize data by hour\n        if hour_start not in pivot_data:\n           pivot_data[hour_start] = {}\n        pivot_data[hour_start][loss_description] = hh_loss\n\n    # Sort the loss types and prepare headers\n    sorted_loss = sorted(loss_types)\n    headers = ['HOUR_START'] + sorted_loss\n\n    # Prepare new data rows\n    new_data = []\n    for hour, losses in sorted(pivot_data.items()):  # Sort by hour_start\n        row = [hour]\n        for loss in sorted_loss:  # Ensure order of loss types\n            row.append(round(losses.get(loss, 0)))  # Fill with 0 if no data for that loss\n        new_data.append(row)\n    # Convert the new data to a dataset\n    #logger.info(\"newData: \"+ str(headers)+\" - \" + str(new_data))\n\t# Ensure new_data is populated correctly and matches the headers length\n    if new_data and all(len(row) == len(headers) for row in new_data):\n        # Convert the new data to a dataset\n        header = ['hh','gg']\n        data = [[1,2,3,4],[1,2,3,4]]\n        output_dataset = system.dataset.toDataSet(headers, new_data)\n        logger.info(\"newData: \"+ str(headers)+\" - \" + str(new_data))\n\n        # Return the transformed dataset\n        return output_dataset\n    else:\n        logger.info(\"Error: New data is empty or row lengths do not match headers.\")\n        return None",
              "type": "script"
            }
          ]
        }
      },
      "props.series": {
        "binding": {
          "type": "property",
          "enabled": false,
          "config": {
            "path": "this.props.dataSources"
          },
          "transforms": [
            {
              "code": "\timport copy\n\timport system.dataset\n\t\n\torgSeries = self.props.series\n\t\n\tsingleSeries = orgSeries[0]\n\t\n\t\n\tkey = list(value.keys())[0]\n\tlogger = system.util.getLogger(\"xy2\")\n\tlogger.info(str(key))\n\tnewData = value[key]\n\t# get count of headers in datasource.\n\tcolumn_names = list(newData.getColumnNames())\n\t# assign newSeries with current data as default\n\tnewSeries = self.props.series\n\t\n\tlogger.info(\"Typoe of: \"+str(type(newSeries)))\n\tlogger.info(\"colcount: \"+ str(len(column_names)))\n\tif len(column_names) > 1:\n\t\t#newSeries.clear\n\t\tnewSeries = [] # there is new data, clear the newSerias and prep for new data\n\t\t# at least one loss\n\t\t#remove the 1st iteam in the list ,this should be the hour_start data\n\t\tcolumn_names.pop(0)\n\t\t# start to itterate threw the rest of the column names and create the new series\n\t\tlogger.info(\"colNmalist2: \"+ str(column_names))\n\t\tc=0\n\t\tfor column_name in column_names:\n\t\t\t# get the column data\n\t\t\tc += 1\n\t\t\tlogger.info(\"colName: \"+ column_name)\n\t\t\tpySingle = dict(singleSeries)\n\t\t\ttemp = list(pySingle)\n\t\t\tcurSeries = copy.deepcopy(temp)\n\t\t\t\n\t\t\t\n\t\t\t#temp2 = dict(curSeries)\n\t\t\t#logger.info(\"cur: \"+str(curSeries))\n\t\t\t#logger.info(\"cur2: \"+str(pySingle))\n\t\t\t#curSeries[\"name\"] = column_name\n\t\t\t#curSeries[\"data\"][\"y\"] = column_name\n\t\t\t#curSeries[\"label\"][\"text\"] = column_name\n\t\t\t#curSeries[\"data\"][\"x\"] =\"HOUR_START\"\n\t\t\t#urSeries[\"data\"][\"source\"] =\"example\"\n\t\t\t#curSeries[\"data\"][\"xAxis\"] = \"hour\"\n\t\t\t#curSeries[\"data\"][\"yAxis\"]= \"loss\"\n\t\t\t\n\t\t\t#newSeries.append(curSeries)\n\t\t\t#logger.info(\"new S: \"+str(c))\n\t\t\n\treturn self.props.series",
              "type": "script"
            }
          ]
        },
        "persistent": true
      }
    }
  }
]
4

I'm a bit lost in what you're trying to do. A spreadsheet mockup of the raw data and the transformed data might help.

Tip: You've posted both the JSON and the dataset in the one code block. Can you edit the post to split them into two separate blocks. I'd say you're just missing the three backticks ``` at the end of the first block and the start of the second. It just makes it easier for anyone copying and pasting into Designer. Thanks.

5
HOUR_START CILR Breakdown Quality Issues
1 10 0 0
2 0 20 30
4 0 0 15
6

HourxHour
HourxHour365×443 16.9 KB

this is an example of what I have done in another system I want to recreate in prespective. yes the axis will be rotated so the hours is on the x axis. but this is an example.

7

You were very close. I got it to work with a couple of tweaks.

  • The raw dataset is fine. You don't need the script transform. You can comment it all out for now (select and Ctrl + /) and just use return value.
  • Duplicate series.0 (right-click | Duplicate) as many times as needed for all the additional columns in your dataset.
  • Edit series.1.data.y and set the correct column name. Do the same for series.2.data.y, etc.
  • Change 'Brakedown' to 'Breakdown' before anyone sees it.

Those aren't really great column names to be using from a programming point of view. You could abbreviate them or use underscores and just change the labels on components where they're displayed.

1 Like
8

I dont think you understand. the series/columns will be dynamic and populated from an Oracle query (hence the query tag binding to datasource). the only loss description that should be used daily would be the CILR. the rest could be any of the 200 different reason descriptions that the operator will select based on why the machine was stopped. so the option of duplicating the series has to be done in code as part of the transform script.

9

Right then. Here's how I would approach it.

def transform(self, value, quality, timestamp):
	def recursiveCopy(original):
		# Function by lrose.
		# https://forum.inductiveautomation.com/t/copy-dictionary-and-modify-it-without-affecting-the-original/62297/9
	    from copy import deepcopy
	    from com.inductiveautomation.ignition.common.script.abc import AbstractMutableJythonMap,AbstractMutableJythonSequence
	
	    if isinstance(original,AbstractMutableJythonMap):
	        return {key:recursiveCopy(value) for key,value in original.iteritems()}
	
	    if isinstance(original,AbstractMutableJythonSequence):
	        return [recursiveCopy(item) for item in original]
	
	    return deepcopy(original)

	barColors = ["aliceblue", "antiquewhite", "aqua", "aquamarine", "azure", "beige", "bisque", "black", "blanchedalmond", "blue", "blueviolet", "brown", "burlywood", "cadetblue", "chartreuse", "chocolate", "coral", "cornflowerblue", "cornsilk", "crimson", "cya", "", "darkblue", "darkcyan", "darkgoldenrod", "darkgray", "darkgreen", "darkgrey", "darkkhaki", "darkmagenta", "darkolivegreen", "darkorange", "darkorchid", "darkred", "darksalmon", "darkseagreen", "darkslateblue", "darkslategray", "darkslategrey", "darkturquoise", "darkviolet", "deeppink", "deepskyblue", "dimgray", "dimgrey", "dodgerblue", "firebrick", "floralwhite", "forestgreen", "fuchsia", "gainsboro", "ghostwhite", "gold", "goldenrod", "gray", "green", "greenyellow", "grey", "honeydew", "hotpink", "indianred", "indigo", "ivory", "khaki", "lavender", "lavenderblush", "lawngreen", "lemonchiffon", "lightblue", "lightcoral", "lightcyan", "lightgoldenrodyellow", "lightgray", "lightgreen", "lightgrey", "lightpink", "lightsalmon", "lightseagreen", "lightskyblue", "lightslategray", "lightslategrey", "lightsteelblue", "lightyellow", "lime", "limegreen", "linen", "magent", "", "maroon", "mediumaquamarine", "mediumblue", "mediumorchid", "mediumpurple", "mediumseagreen", "mediumslateblue", "mediumspringgreen", "mediumturquoise", "mediumvioletred", "midnightblue", "mintcream", "mistyrose", "moccasin", "navajowhite", "navy", "oldlace", "olive", "olivedrab", "orange", "orangered", "orchid", "palegoldenrod", "palegreen", "paleturquoise", "palevioletred", "papayawhip", "peachpuff", "peru", "pink", "plum", "powderblue", "purple", "rebeccapurple", "red", "rosybrown", "royalblue", "saddlebrown", "salmon", "sandybrown", "seagreen", "seashell", "sienna", "silver", "skyblue", "slateblue", "slategray", "slategrey", "snow", "springgreen", "steelblue", "tan", "teal", "thistle", "tomato", "transparent", "turquoise", "violet", "wheat", "white", "whitesmoke", "yellow", "yellowgreen"]
		
	pyds = system.dataset.toPyDataSet(value)
	headers = system.dataset.getColumnHeaders(pyds)	
	
	series0 = self.custom.series0       # Read in the prototype.
	output = [series0]  				# CILR column always there
	for i in range(2, len(headers)): 	# Skip HOUR_START and CILR columns.
	    curSeries = recursiveCopy(series0)
	    curSeries['label']['text'] = headers[i]  
	    curSeries['data']['y'] = headers[i]   
	    curSeries['column']['appearance']['fill']['color'] = barColors[i] 
	    output.append(curSeries)
	
	return output

One possible improvement with this would be to convert my barColors to a dictionary of description: color pairs so that a given downtime reason will use the same color every day.
There are about 150 colors in my list. If there's a chance you'll need more then you need to handle that somehow.

1 Like
10

Thank you I will give this a try. I got called off onto another project today I hope to get back to this tomorrow. will let you know how it goes.

11

Had some time to make the changes and test. sofar every thing is working as expected. thanks.

1 Like