5

I am having troubles using the field calculator to get what I want. One column in my attribute table is named 'Taxa' and has a set of species in it. Species are separated by #.

I want to make a new column using the field calculator that returns a value, based on the species in the 'Taxa' column.

An example of the column I want to use to calculate a value in the new column: 'Taxa' column example Below i have some examples of values per species:

Calluna_vulgaris =1
Baeomyces_rufus = 1
Porpidia_crustulata =2
Rhizocarpon reductum =2
Micarea_erratica = 2
Hypnum_jutlandicum = 1/3

To summarize: If the Taxa column says 'Calluna_vulgaris#Porpidia_crustulata#Micarea_erratica'

I want the new column to have the number 5.

I understand this is not the most convenient way to group species in a column, but I used the TomBio addon which does this automatically.

  • So you need to sum up the values from your list if the associated name does exist in column Taxa? For your example 1+2+2=5? And if it is Calluna_vulgaris#Baeomyces_rufus then it should be 1+1=2? How big is your entire list? Do you have it available as CSV or something you can import to QGIS? – MrXsquared Nov 8 at 17:32
3

I think the most robust method for you will involve having your species-value list on another layer as a CSV or similar - in other words, a lookup table. Trying to match values in expressions will be incredibly inefficient, difficult to edit, and prone to error.

It should be easy to create such a table in any spreadsheet program, even a basic text editor, then import it into QGIS using Add Delimited Text Layer. (Make sure to trim fields so that no extra spaces make their way in)

Once you have that you could use the following expression (built on @csk's excellent suggestion to use arrays) to return the values required and sum them together. If one or more species cannot be found in the lookup table it will return a NULL which should prompt the user to check the data.


This is the lookup table I have set up called taxa_csv in the layers list (note the name).

enter image description here

This is the expression I used in field calculator (note lookup table name is in expression).

eval(replace(array_to_string(array_foreach(string_to_array("taxa",'#'),
to_real(attribute(get_feature('taxa_csv','taxon',@element),'value'))),',','NULL'),',','+'))

Result - note NULLs for entries where species not in lookup table.

enter image description here


Some notes:

  • The lookup table name (taxa_csv) and corresponding column names (taxon, value) in the expression are case-sensitive. If you rename the lookup table and/or column names, just remember to change the names accordingly in this expression.
  • Even if your source values are all text (e.g. CSV) the expression will convert it to a numeric format (to_real()). (However, 1/3 won't work - I suggest converting this to a decimal number)
  • If you want to treat species not found as 0 rather than NULL replace accordingly in the expression. I don't recommend this though as it can be misleading
  • Does anyone know a good way to sum everything in an array in QGIS Field Calc? Had to kludge it together by converting back to a string and replacing commas with plus signs then evaluating it with eval()
  • Thanks, i tried all methods but ended up liking this one the most. Thanks for the solution! Works perfect! – Tom van Heusden 2 days ago
6

You can use regexp_match function to find starting position of the word within the string. Than with using condition if you can test if there is string occurence (e.g. starting position > 0) and set the value for TRUE (your value) and FALSE (0).

It should look like this:

if(regexp_match(taxa, 'Calluna_vulgaris')>0,1,0) +
if(regexp_match(taxa, 'Porpidia_crustulata')>0,2,0) +
if(regexp_match(taxa, 'Rhizocarpon reductum')>0,2,0) +
if(regexp_match(taxa, 'Micarea_erratica')>0,2,0)

enter image description here

Maybe there is better regexp solution, but I'm not so regexp ninja.

3

Another way would be to use the replace function repeatedly to replace the species names with their corresponding values, replace the # signs with + signs, then use eval to calculate the sum. Eg,

eval(
 replace(
  replace(
   replace(
    replace(
     replace(
      replace(
       replace("Taxa",'Baeomyces_rufus', 1),
      'Calluna_vulgaris',1),
     'Porpidia_crustulata',2),
    'Rhizocarpon reductum',2),
   'Micarea_erratica', 2),
  'Hypnum_jutlandicum', 0.33333),
 '#','+'))

It's pretty cumbersome, though.


I suspect there's a possible clever solution using arrays, but I haven't quite worked it out. Here's what I have so far:

  • Use string_to_array("Taxa", '#') to convert the "Taxa" field to an array
  • Create an array with all the possible species names, and create another array with their corresponding values in the same order. Using the example you gave, those arrays would look like this:

    • All_Taxa : ('Calluna_vulgaris', 'Baeomyces_rufus', 'Porpidia_crustulata', 'Rhizocarpon_reductum', 'Micarea_erratica', 'Hypnum_jutlandicum')

    • All_Values: (1,1,2,2,2,0.3333)

  • Use array_find on every value from the "Taxa" array, to find its position within the All_Taxa array. Then use array_get to retrieve the corresponding values from the All_Values array.

    • The array_filter function might be useful - you could filter the All_Values array based on an expression that achieves the above.
  • I think a map would be better than an array for the species name and values. You would have an expression like array_foreach(string_to_array("taxa",'#'),map_get(map('Calluna_vulgaris',1,'Baeomyces_rufus',1,'Porpidia_crustulata',2,'Rhizocarpon_reductum',2,'Micarea_erratica',2,'Hypnum_jutlandicum',0.333),@element)) That way you don't need to worry about order and the key-value pairs are more obvious. – she_weeds Nov 9 at 3:36
  • I don't know about maps. I'll have to look into that. It sounds useful. – csk Nov 9 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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