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