8

I have a set of string values in a shapefile's attribute column and want to assign a number to them in a way that each unique string gets an integer number, like this:

enter image description here

How do I do this using the Field Calculator? I have 3,000 rows of data and 97 different names to do this for.

Taras
  • 32,823
  • 4
  • 66
  • 137
Elise
  • 125
  • 6
  • Partially related thread: https://gis.stackexchange.com/questions/364563/adding-consecutive-unique-id-starting-from-1-for-each-distinct-set-of-values – Taras Oct 23 '21 at 11:23

4 Answers4

11

You can use arrays for this purpose:

 array_find(array_distinct(array_agg("Name")), "Name")

This returns for example this "id" column:

enter image description here

So how does it work: First an array of all Name values is created, then every duplicate value gets deleted. In the end, the index of the current name is searched in the array and the index is used as "id".

P.S.: not sure when array_find() and array_distinct() were introduced in QGIS. Only tested in 3.14. If you don't want zeros as "id", simply add +1 to the expression:

 array_find(array_distinct(array_agg("Name")), "Name") + 1
Taras
  • 32,823
  • 4
  • 66
  • 137
MrXsquared
  • 34,292
  • 21
  • 67
  • 117
5

You can use PyQGIS:

lyr = QgsProject.instance().mapLayersByName('lmv ok_my_riks_sample')[0] #Change name to match your data
category_field = 'kategori' #Same
field_to_calculate = 'cat' #Same. This integer field needs to be added before executing the code

unique_vals = lyr.uniqueValues(lyr.fields().indexFromName(category_field)) #Find all unique values
#{'Sankmark', 'Tätort', 'Skogsmark', 'Vattenyta', 'Öppen mark'}


d = {cat:e for e,cat in enumerate(unique_vals, 1)} #Create a dictionary of unique values and number #Create a dictionary of unique values and number
#{'Sankmark': 1, 'Tätort': 2, 'Skogsmark': 3, 'Vattenyta': 4, 'Öppen mark': 5}


#Update field using dictionary
with edit(lyr):
    for feat in lyr.getFeatures():
        feat.setAttribute(feat.fieldNameIndex(field_to_calculate), d[feat[category_field]])
        lyr.updateFeature(feat)

enter image description here

Taras
  • 32,823
  • 4
  • 66
  • 137
BERA
  • 72,339
  • 13
  • 72
  • 161
5

This is a typical CASE...WHEN..THEN solution. In the field calculator, you can use the following statement to update the ID field:

Case
    When "Name" = 'loam' then 1
    When "Name" = 'silt' then 2
    When "Name" = 'sand' then 3
    ...
    Else 20
End

You can add other cases for other names and assign them ids as you like. the ELSE is to provide one value for the remaining names.

Taras
  • 32,823
  • 4
  • 66
  • 137
ahmadhanb
  • 40,826
  • 5
  • 51
  • 105
0

For not having 0's in your field, the expression in the field-calculator will look like:

array_find( array_distinct( array_agg("Name")), "Name") +1

where Name is the attribute column name (thank you @MrXsquared).

In addition: If you have a null value in the original attribute-collumn: Name use the coalesce() function. Your expression will look like:

 coalesce( array_find( array_distinct( array_agg("Name")), "Name") +1,0 )

where the last 0 after the , will become the value of the original null value from the Name attribute column. So if you like, you can also write a number like 999 after the last ,

ThomasHaarlem
  • 71
  • 7