Ignition dataset

How do i sum the values in a dataset with same row values?.Like for example below
In column Item code, i want to sum the Weight with the same Item Code.
Item Code Weight
20025 1.5
20008 0.5

I suppose that depends on what you’re wanting to do with the sum once you have it and where the data is coming from. Even then there are different ways to arrive at the answer.

For instance, you could loop through each row of the dataset and add together the values if the item code matches search criteria.

Or if the data is in a database, you could query the data summing the weight grouped by item no.

For putting the sum on a different component, say a label, you can also use the sum() expression to enter a dataset and the column you want to sum.

https://docs.inductiveautomation.com/display/DOC80/sum

1 Like

Yes, but note that there are different item codes that the OP wants to sum.

Strictly answering the OP’s question, using dictionaries is a very versatile option. Also, using a PyDataSet simplifies row handling in the dataset, IMHO.

This assumes that this will be written to another dataset to be used in a summary table.

if event.propertyName == 'data':
	# Convert to pyDataSet
	pyData = system.dataset.toPyDataSet(event.source.data)

	weightDict = {}
	for row in pyData:
		# Check if item code is already in the dictionary
		if row['Item Code'] not in weightDict.keys():
			weightDict[row['Item Code']] = row['Weight']
		else:
			weightDict[row['Item Code']] += row['Weight']

	# Create output dataset
	headersOut = ['Item Code', 'Weight']
	data = [[itemCode, weightDict[itemCode]] for itemCode in sorted(weightDict.keys())]
	# Write data to summary table	
	path.to.summary.table.data = sytem.dataset.toDataSet(headers, data)

Using a defaultdict will automatically detect if there is a missing key, and add the default object (in our case a float) if the key doesn’t exist.

 	from collections import defaultdict

	# Convert to pyDataSet
	pyData = system.dataset.toPyDataSet(event.source.data)

	weightDict = defaultdict(float)
	for row in pyData:
		weightDict[row['Item Code']] += row['Weight']
	...
1 Like

Before @pturmel comes through ‘meh-ing’ for not working strictly with the original dataset, this one’s for you. :wink:

 	from collections import defaultdict
	
	dataIn = system.event.data
	weightDict = defaultdict(float)
	for i in xrange(dataIn.rowCount):
		weightDict[dataIn.getValueAt(i,'Item Code')] += dataIn.getValueAt(i,'Weight')
2 Likes

Tangentially related but a sumif() type function similar to the excel version would be a very useful expression.

1 Like

Nah, I’ll say “Meh” because this problem is trivially solved with my view() expression function (from Simulation Aids):

view("Select Item_Code, sum(Weight) As Weight Group By Item_Code", {path/to/source/dataset})

Note that I had to use an underscore in Item_Code since spaces aren’t allowed in python. See my docs on “munged” column names.

4 Likes

I’ll mention that there are probably good reasons to have some of this type of data housed within a database backend. If you want it to “stay with the app” and don’t want/need the hassle of a more traditional external database, just use a SQLite database file located in the data/projects/ folder of your Ignition install–then it will get picked up by the Gateway Backup! What you’re looking for is very standard fare for SQL.

Like @pturmel’s example above, the SQL query for something like this looks like below:

SELECT Category, Customer, [Item Code], Description, [No of Crates], SUM(Weight) as [Total Weight] FROM item_detail
	GROUP BY Category, Customer, [Item Code], Description, [No of Crates]

That said, going this direction may involve some significant refactoring of your project, depending on what it does. Here is a quick little example that I whipped up, fwiw:

Also, the WHERE clause there is just a bonus, using short-circuit evaluation to offer an optional parameter to filter by, if desired.

Thanks guys for helping
Thank you all