3

In QGIS attribute table of a point shapefile I have several rows with species occurrences. e.g. BA1404.

Whenever a species is present, the cell value = 1 otherwise 0

example

I created the new column "BA" and I converted the species code to species names: e.g. BA1404 = code

Acer negundo = species name

case 
when "BA1404"  = 1 then 'Acer negundo'
when "BA3501"  = 1 then 'Aescolus sp.'
when "BA2007"  = 1 then 'Ailanthus altissima'
when "BA2009"  = 1 then 'Celtis sp.'
when "BA1304"  = 1 then 'Fraxinus pennsylvanica'
when "BA2010"  = 1 then 'Gleditsia triacanthos'
when "BA2002"  = 1 then 'Juglans nigra'
when "BA0404"  = 1 then 'Pinus strobus'
when "BA2008"  = 1 then 'Platanus sp.'
when "BA3305"  = 1 then 'Populus balsamifera'
when "BA0500"  = 1 then 'Pseudozuga menzisii'
when "BA1105"  = 1 then 'Quercus rubra'
when "BA1700"  = 1 then 'Robinia pseudacacia'
when "BA3304"  = 1 then 'Populus x canadensis'
when "BA1800"  = 1 then 'Different species'
else 'Error'
end

Now the present species are in column "BA". BUT, in some cases there are TWO or more species in one row (red circles in figure 1)

Then the result should be:

'Pseudozuga menzisii, Robinia pseudacacia, Populus x canadensis'

How can I resolve this?

@she_weeds: I tried with all the steps you suggested:

  1. made a CSV table with codes and species and dragged/dropped it into the QGIS project enter image description here

now the CSV file is a table enter image description here

  1. select the point layer -> Field calculator -> Update existing field (case sentitive fields are adjusted). But still it does not give me any output as you can see. enter image description here

What is weird: when I choose the map layer from the layer list; layer name is different from actual layer name... enter image description here

Improve this question
1
  • 4
    Your dataformat look like it is for an end presentation in a powerpoint or a spreadsheet. You should put the species in one table and occurrences in another. Then some pivot or SQL can give you the look you want. You dataformat is difficult to maintain. Commented Mar 20, 2020 at 12:06

3 Answers 3

Reset to default
4

Use python with a Dictionary (backup your data before you try it and check output to make sure it works as expected):

d = {"BA1404":'Acer negundo',"BA3501":'Aescolus sp.',"BA2007":'Ailanthus altissima',
    "BA2009":'Celtis sp.',"BA1304":'Fraxinus pennsylvanica',"BA2010":'Gleditsia triacanthos',
    "BA2002":'Juglans nigra',"BA0404":'Pinus strobus',"BA2008":'Platanus sp.',
    "BA3305":'Populus balsamifera',"BA0500":'Pseudozuga menzisii',"BA1105":'Quercus rubra',
    "BA1700":'Robinia pseudacacia',"BA3304":'Populus x canadensis',"BA1800":'Different species'}

layer = iface.activeLayer() #Click layer in layer tree
fields = [f.name() for f in layer.fields() if f.name().startswith('BA') and len(f.name())>2] #List all species columns
field_to_update = 'BA'

with edit(layer):
    for feat in layer.getFeatures():
        val = [] #Empty list to store species name
        for f in fields: #For each row and field, check if there is a 1
            if feat[f] == 1 and f in d: #If 1 and if column name in dictionary
                val.append(d[f])
        if len(val)>0:
            feat.setAttribute(feat.fieldNameIndex(field_to_update), ', '.join(val)) #If something is in list, join elements together to a comma separated string
            layer.updateFeature(feat)

example

Improve this answer
0
3

NOTE: This method only works in 3.10.5 or 3.12.2 onwards as earlier versions of 3.10 and 3.12 had a bug that prevented this from working.

You can avoid using Python by using the new array-based attributes() function in the Expression Builder (requires at least QGIS 3.10)

As @Jakob suggested in a comment to your question, the best approach would be to save your code and species values in a lookup table, rather than hard coding it into the expression, because it's easier to keep track of that way.

  1. Create a CSV with your code and species as a table, and load it into QGIS (drag and drop). Make note of the layer name and columns

enter image description here

  1. Select your data layer and open Field Calculator. Select Update existing field and select BA from the dropdown list.

  2. Enter the following expression (note the name of the CSV layer and columns referenced here - these are case-sensitive)

array_to_string(array_filter(array_foreach(map_akeys(attributes()), case when eval('"'||@element||'"')=1 then attribute(get_feature('Lookup','code',@element),'species') else null end),@element is not null),',')

You should see the appropriate result in the expression preview below.

enter image description here

Improve this answer
-2

For a much less elegant, but non python way:

case 
when "BA1404"  = 1 then 'Acer negundo, '
else ''
end ||
when "BA3501"  = 1 then 'Aescolus sp., '
else ''
end ||
when "BA2007"  = 1 then 'Ailanthus altissima, '
else ''
end ||
when "BA2009"  = 1 then 'Celtis sp., '
else ''
end ||
when "BA1304"  = 1 then 'Fraxinus pennsylvanica, '
else ''
end ||
when "BA2010"  = 1 then 'Gleditsia triacanthos, '
else ''
end ||
when "BA2002"  = 1 then 'Juglans nigra, '
else ''
end ||
when "BA0404"  = 1 then 'Pinus strobus, '
else ''
end ||
when "BA2008"  = 1 then 'Platanus sp., '
else ''
end ||
when "BA3305"  = 1 then 'Populus balsamifera, '
else ''
end ||
when "BA0500"  = 1 then 'Pseudozuga menzisii, '
else ''
end ||
when "BA1105"  = 1 then 'Quercus rubra, '
else ''
end ||
when "BA1700"  = 1 then 'Robinia pseudacacia, '
else ''
end ||
when "BA3304"  = 1 then 'Populus x canadensis, '
else ''
end ||
when "BA1800"  = 1 then ‘Different species, '
else ''
end

To remove the last ', ' just wrap the whole expression in:

  substr(FUNCTION HERE, 0, -2)
Improve this answer
4
  • 1
    I think your solution would be great when there were more values besides (0,1) and then each attribute could be considered differently Commented Mar 20, 2020 at 11:33
  • There are just zeros and ones, thats all. @BERA: pls see my answer above. I added a screenshot of the error warning because it was too big to add it as text Commented Mar 20, 2020 at 11:44
  • 1
    @Quarantäne have you tried the answer? Commented Mar 20, 2020 at 12:38
  • @HeikkiVesanto Yes, thanks but it is not the answer I was looking for. Commented Mar 20, 2020 at 13:58

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.