Get min/max/average values from dataset

I have a script which uses system.tag.queryTagHistory to retrieve log data in 15 minute intervals for the previous 24 hours. I run it three times, and get a minimum dataset, a maximum dataset, and an average dataset. Now, I want to find the lowest value in the minimum dataset, the highest value in the maximum dataset, and the average value of all values in the average dataset.

In expression language, I can do this easily, but I don’t believe it’s possible to use expression language in a script.

I know python has min() and max() functions that operate on arrays, so I figure I should be able to pass the dataset values into those functions somehow. I can make it work with a brute force “for row in range()” and append each value to an array, but I figure there must be a more elegant way to do it. Or, a more elegant way to approach the problem altogether.

Anyone have any suggestions?

The easiest is probably to first convert it to a pyDataSet. Then you get better python handling. see https://docs.inductiveautomation.com/display/DOC79/Datasets

pyData = system.dataset.toPyDataSet(myDs)
arr = [row["column"] for row in pyData]
min = min(arr)
max = max(arr)
avg = sum(arr) / len(arr)
1 Like

Most datasets in Ignition are instances of BasicDataset. It has the handy method .getColumnAsList(). So,

minimum = min(minsDS.getColumnAsList(minsDS.getColumnIndex("column")))
maximum = max(maxesDS.getColumnAsList(maxesDS.getColumnIndex("column")))
average = reduce(lambda x, y: x+y, avgsDS.getColumnAsList(avgsDS.getColumnIndex("column")))/avgsDS.rowCount
3 Likes

Starting with v7.8, Ignition also has the system.math libraries:

average = system.math.mean(avgsDS.getColumnAsList(avgsDS.getColumnIndex("column"))
4 Likes

Thanks all.

The system.math.mean() averaging method didn’t work; it gave me the error “(1st arg cannot be coerced to double)”. pturmel’s method works, though if you have an ideas as to why the system.math.mean doesn’t I’m interested to hear it - it’s certainly a more readable solution if I can make it work!

I’m trying to use this method, but I am getting an error in line 5. I’m taking a dataset and creating a new one with identical headers but with only one row which contains the average of each column in the previous dataset.

TypeError: mean(): 1st arg can't be coerced to double[]

dataIn = event.source.data
headers = system.dataset.getColumnHeaders(dataIn)
dataOut = []
for item in headers:
	avg = system.math.mean(dataIn.getColumnAsList(dataIn.getColumnIndex(item)))
	dataOut.append(avg)
newDS = system.dataset.toDataSet(headers, dataOut)
system.tag.write("[Client]AverageTimes",newDS)

The example that @JordanCClark gave is missing one parenthesis at the end. maybe that is why you couldn’t get it to work?

@JordanCClark’s example is missing a couple things to work properly:

dataOut = []
for item in headers:
	avg = system.math.mean(map(float, ds.getColumnAsList(ds.getColumnIndex(item))))
	dataOut.append(avg)
newDS = system.dataset.toDataSet(headers, [dataOut]) 

Notably, map will ensure that each input item is a type that system.math.mean expects, and the final toDataSet call expects a sequence of sequences.

2 Likes

I’m now getting this error on line 5 which is the avg = system.math.mean line.

ValueError: invalid literal for __float__:

EDIT: I figured it out, it is the row of blanks that is throwing this error

1 Like

Thanks Paul. Could you help me modify this to handle None values in the average column?

Assuming you’re looking to ignore them:

system.math.mean([float(x) for x in ds.getColumnAsList(ds.getColumnIndex(item)) if x != None])
4 Likes

Works perfect. Thanks!

1 Like