Count of the different combination of two attributes in QGIS?

Question

I've been trying in vain to use the field calculator in QGIS 2.8 to count the combinations of two attributes in two separate columns.

First column is called "Alds_klass" and second is called "bygn_klass". I want to count the different combinations of the two columns, not the sum, but the number of each combination.

Answer 1

If you're ready NOT to use the field calculator i would create a virtual layer based on the following query (to adapt) :

select count(*) as tot, col_a, col_b from my_table
group by col_a, col_b
order by tot desc

Which would be "dynamic" ...

(implies to use a newer QGIS version...)

Answer 2

You can use the tool "Statistics by categories" (in the processing toolbox in QGIS 3.2)

Answer 3

Create a new column and fill it with a case when or if clause looping over all possible values. Assign each combination a different letter of the alphabet, and then do a statistical analysis of the new column.

Or just load it into a database.

Answer 4

If you want to populate Count_Al_b with the total count of combinations Alds_class+bygn_class you can use collections.Counter:

...where elements are stored as dictionary keys and their counts are stored as dictionary values.

print 'start'
from qgis.core import *
import qgis.utils
from qgis.core import QgsVectorLayer
from collections import Counter

alds = "Alds_klass"
bygn = "bygn_klass"
totcount = "Count_Al_b"
datafile = '/media/bera/my_03.shp' #Change

layer = QgsVectorLayer(datafile, 'temp', 'ogr')
c = Counter([(f[alds],f[bygn]) for f in layer.getFeatures()])

layer.startEditing()
it = layer.getFeatures()
for feat in it:
    layer.changeAttributeValue(feat.id(),layer.fieldNameIndex(totcount),c[(feat[alds],feat[bygn])])
    #layer.fields().indexFromName(totcount) python 3/QGIS 3
layer.commitChanges()
print 'stop'

Example:

Answer 5

I would use the field calculator to create a virtual field containing the concatenation (i.e. combination) of your two fields, like this:

The underscore '_' ist just for better readabilty and does not affect the result.

Then use the 'Show statistical summary' button (the button with the big sigma):

Herein select your layer and the virtual field you just created, and under Count (distinct) you will find the count of your columns combinations:

The virtual field and the tool do dynamically refresh when data changes.