# Dictionary value sum / avg?

**URL:** <https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318>\
**Category:** Ignition\
**Created:** [September 18, 2023, 11:39pm UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318 "2023-09-18T23:39:57Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![JinGeun\_Kim](https://avatars.discourse-cdn.com/v4/letter/j/439d5e/32.png) [@JinGeun\_Kim](https://forum.inductiveautomation.com/u/JinGeun_Kim)\
**Post date:** [September 18, 2023, 11:39pm UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/1 "2023-09-18T23:39:57Z")

</div>

Hello.  
In "Dictionary", is it possible to find the sum of the average "B" values ​​of "A" values ​​for the same date and create one for each date?

ex)  
a = [ { "Bdata": 2826.5, "time": "2023-09-15", "Adata": 51.5 },  
{ "Bdata": 29629.0, "time": "2023-09-18", "Adata": 50.8 },  
{ "Bdata": 346.8, "time": "2023-09-19", "Adata": 32.8 },  
{ "Bdata": 7274.9, "time": "2023-09-15", "Adata": 50.9 },  
{ "Bdata": 29630.3, "time": "2023-09-18", "Adata": 50.7 },  
{ "Bdata": 334.9, "time": "2023-09-19", "Adata": 40.4 },  
{ "Bdata": null, "time": "2023-09-15", "Adata": null },  
{ "Bdata": 4247.0, "time": "2023-09-18", "Adata": 50.3 },  
{ "Bdata": 351.3, "time": "2023-09-19", "Adata": 54.8 } ]

===\>\>

[ { Bdata: sum,||time: 2023-09-15, Adata: average },  
{ Bdata: sum,||time: 2023-09-18, Adata: average },  
{ Bdata: sum,||time: 2023-09-19, Adata: average } ]

Please tell me how to change this!!

Tanks

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [September 19, 2023, 12:43am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/2 "2023-09-19T00:43:05Z")

</div>

Create another dictionary for the purpose of storing the sums of a values, b values and the count of records by the date. Iterate through your original list of dictionaries to fill the new dictionary accordingly. Finally, iterate through all the keys in the new dictionary to create a new list of dictionaries that has the date, average of 'A' values and sumof 'B' values.

quick example script that assumes you want to skip null values:

```auto
def packMyDictList(dictList):

	dateAccumulator = {}
	for item in dictList:
		dateKey = item['time']
		keyValue = dateAccumulator.setdefault(dateKey, {
			"aValuesAccum": 0,
			"bValuesAccum": 0,
			"aValuesCount": 0
		})

		aDataValue = item['Adata']
		if aDataValue is not None:
			keyValue["aValuesAccum"] += aDataValue
			keyValue["aValuesCount"] += 1

		bDataValue = item['Bdata']
		if bDataValue is not None:
			keyValue["bValuesAccum"] += bDataValue

		dateAccumulator.update({dateKey: keyValue})

	newAveragedAndSummedList = [
		{
			"time": key,
			"BDataSum": dateAccumulator[key]["bValuesAccum"],
			"ADataAvg": dateAccumulator[key]["aValuesAccum"]/dateAccumulator[key]["aValuesCount"]
		} for key in dateAccumulator
	]

	return newAveragedAndSummedList

```

which outputs the following when entering your example data:

```auto
[{'ADataAvg': 42.666666666666664, 'BDataSum': 1033.0, 'time': '2023-09-19'}
{'ADataAvg': 50.6, 'BDataSum': 63506.3, 'time': '2023-09-18'}
{'ADataAvg': 51.2, 'BDataSum': 10101.4, 'time': '2023-09-15'}]

```

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 19, 2023, 12:47am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/3 "2023-09-19T00:47:56Z")

</div>

> [@ryan.white](#):
>
> `dateAccumulator[key]["aValuesAccum"]/dateAccumulator[key]["aValuesCount"]`

~~this can be replaced with simply:  
dateAccumulator[key]["aValuesAccum"]/len(dateAccumulator[key]["aValuesAccum"])~~

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [September 19, 2023, 12:51am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/4 "2023-09-19T00:51:03Z")

</div>

How does that work if `dateAccumulator[key]["aValuesAccum"]` is a float/int?

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 19, 2023, 12:52am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/5 "2023-09-19T00:52:35Z")

</div>

~~use this then:  
float(dateAccumulator[key]["aValuesAccum"])/len(dateAccumulator[key]["aValuesAccum"])~~

a float/int = float

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [September 19, 2023, 12:54am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/6 "2023-09-19T00:54:56Z")

</div>

Ah no, I meant float/int as in the value might be a `float` type or an `int` type depending on what's fed. That's my bad for trying to save some typing.

I'm more confused how `len(dateAccumulator[key]["aValuesAccum"])` gives you the same value as `dateAccumulator[key]["aValuesCount"]` should have. You aren't supposed to be able to call `len()` on an `int` or `float`

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 19, 2023, 1:02am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/7 "2023-09-19T01:02:20Z")

</div>

Oops, my bad!

```auto
float(dateAccumulator[key]["aValuesAccum"]) / len(dateAccumulator[key])

```

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [September 19, 2023, 1:06am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/8 "2023-09-19T01:06:32Z")

</div>

I'm still not getting how this works, I'm not sure if it's because its EOD for me or I'm missing something. Can you explain with crayons/paper? 😆

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 19, 2023, 1:15am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/9 "2023-09-19T01:15:51Z")

</div>

I think my system is out of coffee too... you're using an accum not a list of values, so no... this won't work... Let's start again 😩

This is how I would do it:

```auto
a = [ { "Bdata": 2826.5, "time": "2023-09-15", "Adata": 51.5 },
{ "Bdata": 29629.0, "time": "2023-09-18", "Adata": 50.8 },
{ "Bdata": 346.8, "time": "2023-09-19", "Adata": 32.8 },
{ "Bdata": 7274.9, "time": "2023-09-15", "Adata": 50.9 },
{ "Bdata": 29630.3, "time": "2023-09-18", "Adata": 50.7 },
{ "Bdata": 334.9, "time": "2023-09-19", "Adata": 40.4 },
{ "Bdata": None, "time": "2023-09-15", "Adata": None },
{ "Bdata": 4247.0, "time": "2023-09-18", "Adata": 50.3 },
{ "Bdata": 351.3, "time": "2023-09-19", "Adata": 54.8 } ]

grouped_data = {}
for item in a:
	time = item['time']
	grouped_data.setdefault(time, {'Adata': [], 'Bdata': []})
	if item['Adata'] is not None and item['Bdata'] is not None:
		grouped_data[time]['Adata'].append(item['Adata'])
		grouped_data[time]['Bdata'].append(item['Bdata'])

avg_data = []
for time, data in grouped_data.items():
	avg_a = sum(data['Adata'])/len(data['Adata'])
	sum_b = sum(data['Bdata'])
	avg_data.append({'time': time, 'Adata': avg_a, 'Bdata': sum_b})

```

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [September 19, 2023, 1:22am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/10 "2023-09-19T01:22:18Z")

</div>

> [@nminchin](#):
>
> I think my system is out of coffee too... you're using an accum not a list of values, so no... this won't work... Let's start again 😩

Ah excellent, I'm not completely stupid then 🙃

Any reason for the preference of a list of values instead of a value accumulator?

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 19, 2023, 1:30am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/11 "2023-09-19T01:30:17Z")

</div>

Not particularly, I just prefer to keep the raw values for as long as possible 🤷‍♂️ maybe more calculations need to be done to them in the future (std. deviation, min, max, etc.). Keeping raw values lets you do that

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [September 19, 2023, 3:32am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/12 "2023-09-19T03:32:27Z")

</div>

This is my take on the script that you and @ryan.white have provided.

`sorted()` and `itertools.groupby()` are used to group the data by the 'time' key.

```auto
from itertools import groupby

listIn = [ { "Bdata": 2826.5, "time": "2023-09-15", "Adata": 51.5 },
{ "Bdata": 29629.0, "time": "2023-09-18", "Adata": 50.8 },
{ "Bdata": 346.8, "time": "2023-09-19", "Adata": 32.8 },
{ "Bdata": 7274.9, "time": "2023-09-15", "Adata": 50.9 },
{ "Bdata": 29630.3, "time": "2023-09-18", "Adata": 50.7 },
{ "Bdata": 334.9, "time": "2023-09-19", "Adata": 40.4 },
{ "Bdata": None, "time": "2023-09-15", "Adata": None },
{ "Bdata": 4247.0, "time": "2023-09-18", "Adata": 50.3 },
{ "Bdata": 351.3, "time": "2023-09-19", "Adata": 54.8 } ]

groups = {k:list(g) for k,g in groupby(sorted(listIn,key=lambda l:l['time']),lambda l:l['time'])}

results = [{
			'AdataAvg':sum(i['Adata'] for i in g if i['Adata']) / len(g),
			'BdataSum':sum(i['Bdata'] for i in g if i['Bdata']),
			'time': time
			}
			for time,g in groups.iteritems()
		]

```

Output:

```auto
>>>
[{'BdataSum': 1033.0, 'AdataAvg': 42.666666666666664, 'time': '2023-09-19'},
{'BdataSum': 63506.3, 'AdataAvg': 50.6, 'time': '2023-09-18'},
{'BdataSum': 10101.4, 'AdataAvg': 34.13333333333333, 'time': '2023-09-15'}]
>>> 

```

Could also easily throw a `round()` in there if you don't want a crazy number of decimals.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 19, 2023, 4:03am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/13 "2023-09-19T04:03:13Z")

</div>

I knew there was going to be a nicer way to do the grouping! good to know  
Still waiting for @paul-griffith to provide a one-liner though haha

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 19, 2023, 9:41am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/14 "2023-09-19T09:41:28Z")

</div>

This is already basically a one liner.

Replace `groups` in the comprehension with the expression it was assigned, remove whitespaces: tada !

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [September 19, 2023, 10:54am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/15 "2023-09-19T10:54:16Z")

</div>

~~Doesn’t work because 'grouby' returns a key:generator pair, and generators don’t have a 'len' so you get a divide by zero error.~~

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 19, 2023, 11:17am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/16 "2023-09-19T11:17:21Z")

</div>

> [@lrose](#):
>
> generators don’t have a 'len'

That's not the real issue.  
If you replace `groups` (and ONLY groups) by the expression, it does work:

```python
for time,g in {k:list(g) for k,g in groupby(sorted(listIn,key=lambda l:l['time']),lambda l:l['time'])}.iteritems()

```

Or you could calculate the means without using `len`:

```python
return reduce(lambda x, (i, y): float(x*i + (y['Adata'] or 0)) / (i+1), enumerate(g), 0)

```

But the REAL issue, if you want to use the generators directly, is that you're using the same one twice.  
Which doesn't work:

```python
gen = (i for i in xrange(10))
a = sum(gen)
b = sum(gen)

a, b

```

> (45, 0)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [September 19, 2023, 11:51am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/17 "2023-09-19T11:51:24Z")

</div>

One should not reply prior to their morning caffeine injection.

---

<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:** [September 19, 2023, 12:02pm UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/18 "2023-09-19T12:02:57Z")

</div>

If the source data is in a Perspective property, I'd use my `groupBy()` expression function. Like so:

```auto
forEach(
	groupBy(
		{path.to.source.property},
		it()['time'] // it() in this line is the groupBy()
	),
	asMap(
		'groupedTime', it()[0], // it() here is the outer forEach()
		'sumBdata', sum(
			forEach(
				it()[1], // it() here is the outer forEach()
				it()['Bdata'] // it() here is the forEach() inside the sum()
			)
		),
		'avgAdata', mean(
			forEach(
				it()[1], // it() here is the outer forEach()
				it()['Adata']// it() here is the forEach() inside the mean()
			)
		)
	)
)

```

Line breaks are optional in expression functions, so this _could be_ a one-liner. 😁

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 20, 2023, 4:31am UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/19 "2023-09-20T04:31:27Z")

</div>

> [@lrose](#):
>
> One should not reply prior to their morning caffeine injection

Yes... duly noted!

@pturmel  
Much more betterer!

```auto
forEach(groupBy({path.to.source.property},it()['time']//it()inthislineisthegroupBy()),asMap('groupedTime',it()[0],//it()hereistheouterforEach()'sumBdata',sum(forEach(it()[1],//it()hereistheouterforEach()it()['Bdata']//it()hereistheforEach()insidethesum())),'avgAdata',mean(forEach(it()[1],//it()hereistheouterforEach()it()['Adata']//it()hereistheforEach()insidethemean()))))

```

---

<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:** [September 20, 2023, 12:06pm UTC](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318/20 "2023-09-20T12:06:36Z")

</div>

> [@nminchin](#):
>
> Much more betterer!

I think you have to ditch the comments when you squash it down to one line.

[Next page](https://forum.inductiveautomation.com/t/dictionary-value-sum-avg/79318.md?page=2)