8

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.

example

3

3 Answers 3

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.

8
  • 3
    Very nice and neet ! Jun 11, 2020 at 13:15
  • 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. Jun 19, 2020 at 21:05
  • 1
    @Geografisica, please, share a test layer in which I can reproduce your behavior, because in my layer I can't reproduce it. Jun 22, 2020 at 14:17
  • 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) Jun 23, 2020 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!. Jun 25, 2020 at 15:26
11

A possible solution employing PyQGIS.

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

input


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

python_console_2

Press Run script run script and get the output that will look like

result_1

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)

python_console_1

Press Run script run script and get the output that will look like

result_2


References:

2
  • 2
    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. Jun 11, 2020 at 13:09
  • 1
    @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, 2020 at 13:40
8

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)}":

enter image description here

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.