Sum values of a field based on unique values in another field?

Question

I have a layer made of a field with repeted string values ("building") and a field with float values ("flatssurface"). I'd like to write a script to do some arithmetic operations (like sum, max and min, etc.) on all the values in column B which are associated to same values in column A but it doesn't work.

Dataset:

BUILDING       FLATSURF    SUM (wanted result)
    1           40.0       115 (i.e. 40+45+30 m^2)
    1           45.0       115
    1           30.0       115
    2          200.0       300 (i.e. 200+100 m^2)
    2          100.0       300
    3           60.0       140 (i.e. 60+80 m^2)
    3           80.0       140

Code trial:

building=layer.dataProvider().fieldNameIndex('BUILDING')
flatssurface=layer.dataProvider().fieldNameIndex('FLATSURF')

uniquevalues=layer.uniqueValues(building,limit=10000)
for uv in uniquevalues:
    feat=layer.getFeatures()
    for f in feat:
      tot=sum([flatssurface])
      print tot

however it returns only the index of the field flats' surface (f[1]). For simplicity, I neglected the part on adding the field but I just tried to print the results, in order to check if it worked. If possible, I don't need answers related to GroupStats (Calculating sum of parts of column based on another column using QGIS Field Calculator?) or SQL or statistics (https://gis.stackexchange.com/editing-help) but I am looking only for solution related to Python.

JGH · Accepted Answer · 2017-04-26T16:53:24.337

5

when you do

flatssurface=layer.dataProvider().fieldNameIndex('FLATSURF')

you are indeeded getting the index (column number) of the field Flatsurf. To access the data, in your loop, you need to get the feature, its attributes, and at last restrict to the proper column using the index you have found. So, the numerical value you want to use in your loop is

f.attributes()[flatssurface]

On top of accessing the field value, you need to sum it by building. Using your sample code you are making the cross product of all buildings by all building values.

buildingidx=layer.dataProvider().fieldNameIndex('BUILDING')
flatssurfaceidx=layer.dataProvider().fieldNameIndex('FLATSURF')

uniquevalues=layer.uniqueValues(buildingidx,limit=10000)
for uv in uniquevalues:
    #re-initialise the total area for each building
    tot = 0.0        

    #statement for selecting just the proper buildings
    exp = QgsExpression('BUILDING = ' + str(uv))
    request = QgsFeatureRequest(exp)
    #Select only the buildings having the specified value
    feat=layer.getFeatures(request)
    #Do the sum
    for f in feat:
      tot+=f.attributes()[flatssurfaceidx]
    #once done, print the building value and the corresponding total surface
    print("Building:",uv, "Total surface:", tot)

edited Apr 26 '17 at 16:53

answered Apr 26 '17 at 13:53

JGH

41,794
3
43
89

Thank you @JGH but I am not sure to have understood how inserting it in th script. I tried this: for uv in unval: feat=layer.getFeatures() attr=feat.attributes() for f in feat: tot=sum([attr[6]]) print tot – FedericaZ Apr 26 '17 at 14:44
Typo corrected in my post. From your original code, just replace the line 'tot=sum([flatssurface])' by ' tot=sum(f.attributes()[flatssurface])' The attributes are per feature, so you access them within the loop 'for f in feat:' – JGH Apr 26 '17 at 15:00
Now it seems working but I cannot check 'cause the loop doesn't stop... – FedericaZ Apr 26 '17 at 15:48
Ah, yes, there is more than the indexing question. You also need to restrict the summation by building (and to reset it, and maybe to print the area once per building only). Have a look at the updated answer – JGH Apr 26 '17 at 16:54

score 3 · Answer 2 · answered Apr 26 '17 at 15:15

There is the Statistics by categories algorithm from the Processing Toolbox which seems to do what you are looking for. This calculates various statistics using the QgsStatisticalSummary class such as max, min, sum values. The script can be found in your QGIS directory, e.g:

C:/Program Files/QGIS 2.18/apps/qgis/python/plugins/processing/algs/qgis/StatisticsByCategories.py

Or find the latest version of the script on GitHub.

But we can take the bulk of the code, modify it slightly and use it in the Python Console (remember to select your layer first before running the code):

layer = iface.activeLayer()
building = layer.fieldNameIndex('BUILDING')
flatssurface = layer.fieldNameIndex('FLATSURF')

features = layer.getFeatures()
values = {}
for current, feat in enumerate(features):
    attrs = feat.attributes()
    try:        
        cat = unicode(attrs[building])
        value = float(attrs[flatssurface])
        if cat not in values:
            values[cat] = []
        values[cat].append(value)
    except:
        pass

stat = QgsStatisticalSummary(QgsStatisticalSummary.Min | QgsStatisticalSummary.Max |
                            QgsStatisticalSummary.Mean | QgsStatisticalSummary.StDevSample |
                            QgsStatisticalSummary.Sum | QgsStatisticalSummary.Count)

for (cat, v) in values.items():
    stat.calculate(v)
    record = [cat, stat.min(), stat.max(), stat.mean(), stat.sampleStDev(), stat.sum(), stat.count()]
    print record

@FedericaZ - Most welcome, hope it was helpful :) – Joseph Apr 27 '17 at 11:04 — Joseph, Apr 27 '17 at 11:04

score 0 · Answer 3 · answered Oct 05 '17 at 00:05

You can do it by using: Analysis Tools>Statistics>Summary Statistics (if I got your question though) Select FLATSURF for "Statistics field(s)" and change "statistic type" to SUM. Then BUILDING for "Case field". It will create a table for you. Very easy! Right? don't need to write all those scripts.

Sum values of a field based on unique values in another field?

Dataset:

Code trial:

3 Answers3

Linked