Adding consecutive unique ID starting from 1 for each distinct set of values

Question

I need to add a unique ID (using $ID or @row_number) but the problem is that I need to start from 1 to "...n" considering identical values from other field, then, when there is a different value, start counting from 1 again, if the value is repeated, write 2, 3, 4, 5 until finding new distinct values, and so on.

I have an image more explanatory than me, the red column is what I am getting, the green column is how I would like to add the numbers. I guess using the expression "count_distinct" could be useful, but I don't know how to structure it with my values.

Does this answer your question? Is there a way of auto_increment for the ID column in QGIS — JGH, Jun 10 '20 at 20:27
https://gis.stackexchange.com/questions/132346/is-there-a-way-of-auto-increment-for-the-id-column-in-qgis and this question are different. The answers to the former question don't solve @Geografisica's problem. Please don't vote to close this question. — Kadir Şahbaz, Jun 11 '20 at 12:36

Gabriel De Luca · Accepted Answer · 2020-06-10T23:05:27.040

17

We can use arrays for that:

array_find(
  array_agg(
    $id,
    "ReportID_2"
  ),
  $id
) + 1

With array_agg we have an array of all id grouped by the ReportID_2 values.

Then, array_find gets the position of the current id in the previous array, starting with the position 0.

Finally, we add 1 to get the positions from 1.

edited Jun 10 '20 at 23:05

answered Jun 10 '20 at 22:07

Gabriel De Luca

14,289
3
20
51

3

Very nice and neet ! – J. Monticolo Jun 11 '20 at 13:15
Using $id or @rownumber retrieves values that sometimes are not consecutive, I tried your example but I got increasing numbers like [1, 10, 20, 40], not like [1, 2, 3, 4, 5]. That's why I was asking if the function "count_distinct" could help to obtain increasing and also a consecutive sequence starting from 1. – Geografisica Jun 17 '20 at 15:21
1

@Geografisica, positions in the array are consecutive starting from zero. We are retriving the positions, not the $id or @rownumber. They are only the identification value to extract its position in the array. – Gabriel De Luca Jun 19 '20 at 21:05
I used this formula to create a ReportID_3 using ReportID_2: "ReportID_2" || '_' || (array_find( array_agg( $id, 'ReportID_2' ), $id ) + 1).
The results are not consecutive yet, I got a sequence that skips values, like ...26, 27 then 29, 45. Still increasing but not consecutive.
– Geografisica Jun 22 '20 at 13:50
1

@Geografisica, please, share a test layer in which I can reproduce your behavior, because in my layer I can't reproduce it. – Gabriel De Luca Jun 22 '20 at 14:17
@GabrielDeLuca Sure, you can access to the shapefile through this link, thank you!: http://www.filedropper.com/moppclaimstestreportid – Geografisica Jun 23 '20 at 13:32
1

@Geografisica, I understand that the difference is minimal and may go unnoticed, but you are not grouping by the value of the field "ReportID_2", but by the literal string 'ReportID_2', which makes all the rows enter the same group. The expression must be: "ReportID_2" || '_' || (array_find( array_agg( $id, "ReportID_2" ), $id ) + 1) – Gabriel De Luca Jun 23 '20 at 23:37
1

Great Gabriel, I works! now I got the perfect results!, yes, now I understood what was the problem. I really appreciate your help!. – Geografisica Jun 25 '20 at 15:26

Taras · Answer 2 · 2022-11-15T07:02:54.980

A possible solution employing PyQGIS.

Let's assume there is a point layer called 'Layer_B' with its attribute table, see image below.

Case 1 without grouping

when a new `"id"` has to be assigned in a range of each attribute field

Proceed with Plugins > Python Console > Show Editor and paste the script below

from PyQt5.QtCore import QVariant
fieldname = "info" #a field which is used as an aggregating factor 
newfieldname = "newid" #a new field that will maintain new ids
layer = iface.activeLayer()
idx = layer.dataProvider().fieldNameIndex(fieldname)
if newfieldname not in layer.fields().names():
    pr = layer.dataProvider()
    pr.addAttributes([QgsField(newfieldname, QVariant.Int)])
    layer.updateFields()
req = QgsFeatureRequest()
req = req.addOrderBy(fieldname)
attr_old = None
with edit(layer):
    for feat in layer.getFeatures(req):
        attr = feat.attributes()[idx]
        if attr == attr_old:
            i += 1
        else:
            i = 1
        feat[newfieldname] = i
        layer.updateFeature(feat)
        attr_old = attr

Press Run script and get the output that will look like

P.S. Many thanks to @J.Monticolo

Case 2 with grouping

when a new `"id"` has to be assigned whenever the value in another attribute field changes

Proceed with Plugins > Python Console > Show Editor and paste the script below

from PyQt5.QtCore import QVariant
fieldname = "info" #a field which is used as an aggregating factor 
newfieldname = "newid" #a new field that will maintain new ids
layer = iface.activeLayer()
idx = layer.dataProvider().fieldNameIndex(fieldname)
list_attributes = []
for feat in layer.getFeatures():
    list_attributes.append(feat.attributes()[idx])
list_attributes = list(set(list_attributes))
dct = {list_attributes[i]: i + 1 for i in range(len(list_attributes))}
if newfieldname not in layer.fields().names():
    pr = layer.dataProvider()
    pr.addAttributes([QgsField(newfieldname, QVariant.Int)])
    layer.updateFields()
with edit(layer):
    for feat in layer.getFeatures():
        attr = feat.attributes()[idx]
        for key, value in dct.items():
            if attr == key:
                feat[newfieldname] = value
    layer.updateFeature(feat)

Press Run script and get the output that will look like

References:

update your code, here you give the same newid for the same info value. The author wish a new increment each time the info change. — J. Monticolo, Jun 11 '20 at 13:09
@Taras I'm very happy to have found this solution, for I can make really good use of the "Case 2" for my work. It would be perfect if there would be a possibillity to sort the numbering of the column "newid" by another field, like "date", so the "earlier" features would get the lower number. Any help would be highly apreciated. — Bjoern, Sep 28 '20 at 13:40

score 8 · Answer 3 · answered May 19 '21 at 10:10

Padding zeros version using PyQGIS:

layer = iface.activeLayer()
layer.dataProvider().addAttributes([QgsField("NEW_FIELD", QVariant.String)])
layer.updateFields()
field = "FIELD"
index = layer.fields().indexFromName(field)
d = {v: 1 for v in layer.uniqueValues(index)}
layer.startEditing()
for feat in layer.getFeatures():
    v = feat[field]
    feat["NEW_FIELD"] = f"{str(d[v]).zfill(2)}" # 2: padding value -> 03, 04
layer.updateFeature(feat)    
d.update({v: d[v] + 1}) # next value for attibute value


layer.commitChanges()

Result for f"{str(d[v]).zfill(2)}":

Result for f"{v}_{str(d[v]).zfill(2)}":

Adding consecutive unique ID starting from 1 for each distinct set of values

3 Answers3

Case 1 without grouping

when a new `"id"` has to be assigned in a range of each attribute field

Case 2 with grouping

when a new `"id"` has to be assigned whenever the value in another attribute field changes

Linked

Related

Adding consecutive unique ID starting from 1 for each distinct set of values

3 Answers3

Case 1 without grouping

when a new "id" has to be assigned in a range of each attribute field

Case 2 with grouping

when a new "id" has to be assigned whenever the value in another attribute field changes

Linked

Related

when a new `"id"` has to be assigned in a range of each attribute field

when a new `"id"` has to be assigned whenever the value in another attribute field changes