# Find duplicates in a dataset and sum their values

**URL:** <https://forum.inductiveautomation.com/t/find-duplicates-in-a-dataset-and-sum-their-values/29624>\
**Category:** Ignition\
**Created:** [November 3, 2019, 3:32pm UTC](https://forum.inductiveautomation.com/t/find-duplicates-in-a-dataset-and-sum-their-values/29624 "2019-11-03T15:32:30Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![fisayoa](https://avatars.discourse-cdn.com/v4/letter/f/5f9b8f/32.png) [@fisayoa](https://forum.inductiveautomation.com/u/fisayoa)\
**Post date:** [November 3, 2019, 3:32pm UTC](https://forum.inductiveautomation.com/t/find-duplicates-in-a-dataset-and-sum-their-values/29624/1 "2019-11-03T15:32:31Z")

</div>

Hi,

How can I sum the values of a dataset having duplicate records?  
New York appears twice in the below example. I need to sum “New York” Population (8363710 + 532) and display the row once.

headers = [“City”, “Population”, “Timezone”, “GMTOffset”]  
data = []  
data.append([“New York”, 8363710, “EST”, -5])  
data.append([“Los Angeles”, 3833995, “PST”, -8])  
data.append([“Chicago”, 2853114, “CST”, -6])  
data.append([“Houston”, 2242193, “CST”, -6])  
data.append([“Phoenix”, 1567924, “MST”, -7])  
data.append([“New York”, 532, “EST”, -5])

cities = system.dataset.toDataSet(headers, data)

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [November 3, 2019, 4:56pm UTC](https://forum.inductiveautomation.com/t/find-duplicates-in-a-dataset-and-sum-their-values/29624/2 "2019-11-03T16:56:28Z")

</div>

I’m sure there’s a more clever solution out there, but try this:

```python
headers = ["City", "Population", "Timezone", "GMTOffset"]
data = []
data.append(["New York", 8363710, "EST", -5])
data.append(["Los Angeles", 3833995, "PST", -8])
data.append(["Chicago", 2853114, "CST", -6])
data.append(["Houston", 2242193, "CST", -6])
data.append(["Phoenix", 1567924, "MST", -7])
data.append(["New York", 532, "EST", -5])

cities = system.dataset.toDataSet(headers, data)

d = {}

for row in system.dataset.toPyDataSet(cities):
	city = row[0]
	
	if (city in d):
		population = d[city].pop(0)
		population = population + row[1]
		d[city].insert(0, population)
	else:
		d[city] = [row[1], row[2], row[3]]

summed_data = [[key, d[key][0], d[key][1], d[key][2]] for key in d]
summed_cities = system.dataset.toDataSet(headers, data)

print summed_data

```

---

<div class="post-metadata">

**Author:** ![fisayoa](https://avatars.discourse-cdn.com/v4/letter/f/5f9b8f/32.png) [@fisayoa](https://forum.inductiveautomation.com/u/fisayoa)\
**Post date:** [November 3, 2019, 7:27pm UTC](https://forum.inductiveautomation.com/t/find-duplicates-in-a-dataset-and-sum-their-values/29624/3 "2019-11-03T19:27:37Z")

</div>

Thanks a lot.  
This works perfectly fine

---

<div class="post-metadata">

**Author:** ![fisayoa](https://avatars.discourse-cdn.com/v4/letter/f/5f9b8f/32.png) [@fisayoa](https://forum.inductiveautomation.com/u/fisayoa)\
**Post date:** [November 14, 2019, 5:25pm UTC](https://forum.inductiveautomation.com/t/find-duplicates-in-a-dataset-and-sum-their-values/29624/4 "2019-11-14T17:25:26Z")

</div>

Hi Kevin,

Please, how can I search through the City column of the dataset and return “Green” value if it contains the state “Chicago”.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [November 14, 2019, 7:18pm UTC](https://forum.inductiveautomation.com/t/find-duplicates-in-a-dataset-and-sum-their-values/29624/5 "2019-11-14T19:18:38Z")

</div>

Read up on the ConfigureCell function and look at the examples in the code on your table.  
[https://docs.inductiveautomation.com/display/DOC79/Power+Table](https://docs.inductiveautomation.com/display/DOC79/Power+Table)  
Also the University has a course on it  
[https://inductiveuniversity.com/videos/power-table/7.9](https://inductiveuniversity.com/videos/power-table/7.9)

You will basically end up around:

````auto
	if colName=="City":
		if value=="Chicago":
			return {'background': "green"}```
````