QGIS field calculator: grouping incremental values per category

Question

I have a shapefile with a categorical field (cat) and an integer field (value) in QGIS.

I want to create a new field (group), that groups incremental values per category until there is a break/gap into a new 'group' field. The group field needs to a unique integer per group - this field does not need to be incremental.

Below is an example of what I am after, I would need to generate the 'group' field as follows:

cat	value	group
1	0	1
1	1	1
1	2	1
1	4	2
1	5	2
2	0	3
2	1	3
2	2	3
3	3	4

is there a way to do this using field calculator?

JGH · Accepted Answer · 2023-02-06T19:14:13.550

You can achieve this using a virtual layer

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query and adjust for your layer name.

SELECT *, 
       SUM(CASE WHEN group_break THEN 1 END) 
         OVER (ORDER BY cat,val) AS group_id
FROM (
SELECT *,
    COALESCE(
     (val-1 = LAG(val) 
        OVER (PARTITION BY cat ORDER BY val))
    ,FALSE) IS FALSE AS group_break
FROM myLayer) sub;

Let's break it down: First, in the sub subquery, we identify rows that should get a new group id. Do do this, we break the table by cat value, then order it by val and if the previous (lag()) value is null (i.e it was for a different cat) OR not equal to the current value -1, then we know it is the 1st of a new group.

Then, in the outer query, we compute a running sum, with a value of 1 for rows corresponding to new groups, and 0 otherwise. Because we order again by cat and val, the running total becomes the new group_id you are looking for.

Using the input you have provided, here it what is generated:

Nice use of Virtual Layers! For future reference, The input layer's name (you used myLayer) must match the name used in the QGIS table of contents, and that name cannot contain any spaces. — Stu Smith, Feb 06 '23 at 17:15
Thank you, this answer solved my question. My data was too big to run efficiently with virtual layers so I adapted this code using Pgadmin4 instead. — JUNGLE, Feb 06 '23 at 23:13

score 2 · Answer 2 · answered Feb 06 '23 at 16:30

Use this expression with Field Calculator:

with_variable(
    'arr',
    array_agg (
        if (
            cat = attribute (get_feature_by_id (@layer, $id+1), 'cat') and 
            value + 1 = attribute (get_feature_by_id (@layer, $id+1), 'value'),
            0,
            $id
        )
    ),
    array_find (
        array_filter (@arr, @element >0),
        array_filter (@arr, @element >= $id)[0]
    )
)+1

The expression is based on the one explained here, but does it in one step.

This code will work on pre-sorted data, but it will generate unexpected results if the data isn't sorted. — bixb0012, Feb 06 '23 at 17:38

QGIS field calculator: grouping incremental values per category

2 Answers2