# Get min/max/average values from dataset

**URL:** <https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988>\
**Category:** Ignition\
**Created:** [November 21, 2018, 6:33am UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988 "2018-11-21T06:33:41Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![ASF](https://avatars.discourse-cdn.com/v4/letter/a/c4cdca/32.png) [@ASF](https://forum.inductiveautomation.com/u/ASF)\
**Post date:** [November 21, 2018, 6:33am UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988/1 "2018-11-21T06:33:41Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Sanderd17](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sanderd17/32/8735_2.png) [@Sanderd17](https://forum.inductiveautomation.com/u/Sanderd17)\
**Post date:** [November 21, 2018, 8:01am UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988/2 "2018-11-21T08:01:57Z")

</div>

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](https://docs.inductiveautomation.com/display/DOC79/Datasets)

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

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 21, 2018, 1:11pm UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988/3 "2018-11-21T13:11:39Z")

</div>

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

```auto
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

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [November 21, 2018, 3:37pm UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988/4 "2018-11-21T15:37:45Z")

</div>

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

```auto
average = system.math.mean(avgsDS.getColumnAsList(avgsDS.getColumnIndex("column"))

```

---

<div class="post-metadata">

**Author:** ![ASF](https://avatars.discourse-cdn.com/v4/letter/a/c4cdca/32.png) [@ASF](https://forum.inductiveautomation.com/u/ASF)\
**Post date:** [November 23, 2018, 2:18am UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988/5 "2018-11-23T02:18:05Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 4, 2018, 4:37pm UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988/6 "2018-12-04T16:37:13Z")

</div>

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.

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

```

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 4, 2018, 4:49pm UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988/7 "2018-12-04T16:49:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [December 4, 2018, 4:53pm UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988/8 "2018-12-04T16:53:49Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 4, 2018, 5:02pm UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988/9 "2018-12-04T17:02:54Z")

</div>

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

```auto
ValueError: invalid literal for __float__ :

```

![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/1/1e6c69b5c9caf2cf3313a3224199de59f87f6941.png)

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

---

<div class="post-metadata">

**Author:** ![Tim](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim/32/3361_2.png) [@Tim](https://forum.inductiveautomation.com/u/Tim)\
**Post date:** [March 18, 2019, 3:14pm UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988/10 "2019-03-18T15:14:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 18, 2019, 7:52pm UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988/11 "2019-03-18T19:52:40Z")

</div>

Assuming you’re looking to ignore them:

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

```

---

<div class="post-metadata">

**Author:** ![Tim](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim/32/3361_2.png) [@Tim](https://forum.inductiveautomation.com/u/Tim)\
**Post date:** [March 19, 2019, 12:33pm UTC](https://forum.inductiveautomation.com/t/get-min-max-average-values-from-dataset/20988/12 "2019-03-19T12:33:38Z")

</div>

Works perfect. Thanks!