5

I am working with some age group data in QGIS based on point counts within a polygon grid and I would like to calculate the max value from a row with 11 different age groups in order to get the dominant age group within each grid cell.

The issue is I would like the returned value to be the column name as the value itself is meaningless.

Example of the data

So in row one it would be the max value or columns 7 to 17, returned as column name as the dominant age group for that cell... the value would be useful too as I might want to use percentages later.

1
  • That should certainly be possible (albeit somewhat tedious with if tests; I'd use a dictionary with a lambda sort). Please Edit the Question to specify what have you tried (coding questions here require code), and what error you have encountered.
    – Vince
    Sep 10, 2021 at 8:50

6 Answers 6

6

Similar to @Matt's answer with using the "Function Editor", however my suggestion is to work with the attributes() function.

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

input_at

Open the field calculator (Ctrl + I) and proceed to the Function editor tab, where create a new function with the following content:

from qgis.core import *
from qgis.gui import *

@qgsfunction(args='auto', group='Custom')
def maxvaluefieldname(attrmap, feature, parent):
    _attrmap = {key: attrmap[key] for key in attrmap.keys() if key not in ["fid"]} # exclude not needed fields
    return [k for k,v in _attrmap.items() if v==max(_attrmap.values())][0]

function_editor

switch to the Expression tab paste the following expression maxvaluefieldname(attributes())

expression

and get the output:

result


The input:

test_data

The function:

from qgis.core import *
from qgis.gui import *

@qgsfunction(args='auto', group='Custom')
def maxvaluefieldname(attrmap, feature, parent):
    _attrmap = {key: attrmap[key] for key in attrmap.keys() if key not in ["fid", "id", "left", "top", "right", "bottom", "Dom_age"] and attrmap[key] is not None} # exclude not needed fields
    return [k for k,v in _attrmap.items() if v==max(_attrmap.values())][0]

and the output:

test_data_result


References:

6
  • It runs but then I get the error list index is out of range* from qgis.core import * from qgis.gui import * @qgsfunction(args='auto', group='Custom') def maxvaluefieldname(attrmap, feature, parent): _attrmap = {key: attrmap[key] for key in attrmap.keys() if key not in ["fid","id","left","top","right","bottom","0to150"]} # exclude not needed fields return [k for k,v in _attrmap.items() if v==max(_attrmap.values())][0]
    – John
    Sep 10, 2021 at 12:30
  • Example of the data drive.google.com/file/d/1wH_i7Xe4cZ7N9yX4pwVBEMuIcNYEae4U/view
    – John
    Sep 10, 2021 at 12:34
  • I found the columns inclusively, rather than exclusively, and it works fine. _attrmap = {key: attrmap[key] for key in attrmap.keys() if 'count' in key.lower()}
    – Matt
    Sep 10, 2021 at 13:12
  • 1
    It works on the data you sent, indeed. Are you clicking Save and Load Functions each time you make a change to the script, to make sure the function is updated? Also delete the function name from the expression and retype it. It seems to cache the last instance of the function.
    – Matt
    Sep 10, 2021 at 13:33
  • 2
    Swapped computers entirely and imported my the sample data and it worked perfectly.
    – John
    Sep 10, 2021 at 14:52
5

Using only QGIS expressions:

to get the field name with the largest value:

with_variable ('sel',
map_avals (attributes ()),
map_akeys (attributes ())
[array_find (@sel, array_max (@sel))]
)

to delete some fields from the attribute table use the expression:

with_variable('deleteField',
map_delete(
map_delete(
map_delete(
map_delete(
map_delete(
map_delete(
map_delete(
attributes(),'fid'),'id'),'left'),'top'),'right'),'bottom'),'Dom_age'),
with_variable('sel',
map_avals( @deleteField),
map_akeys( @deleteField)
[array_find(@sel,array_max(@sel))]
))

enter image description here

EDIT: To get both the field name and the value, use the following expression:

hstore_to_map(
with_variable('deleteField',
    map_delete(
    map_delete(map_delete(
    map_delete(map_delete(
    map_delete(map_delete(
    attributes(),'fid'),'id'),'left'),
    'top'),'right'),'bottom'),'Dom_age'),
with_variable('sel',
    map_avals( @deleteField),
    map_akeys( @deleteField)
    [array_find(@sel,array_max(@sel))]
    ||'=>'|| 
    map_get(@deleteField,map_akeys( @deleteField)
    [array_find(@sel,array_max(@sel))])
)))

this expression creates a map with keyed field name and value the maximum value. Then, through other expressions it is possible to extract only the key or the value:

keys:

map_akeys(
hstore_to_map(
with_variable('deleteField',
    map_delete(
    map_delete(map_delete(
    map_delete(map_delete(
    map_delete(map_delete(
    attributes(),'fid'),'id'),'left'),
    'top'),'right'),'bottom'),'Dom_age'),
with_variable('sel',
    map_avals( @deleteField),
    map_akeys( @deleteField)
    [array_find(@sel,array_max(@sel))]
    ||'=>'|| 
    map_get(@deleteField,map_akeys( @deleteField)
    [array_find(@sel,array_max(@sel))])
))))[0]

value:

map_avals(
hstore_to_map(
with_variable('deleteField',
    map_delete(
    map_delete(map_delete(
    map_delete(map_delete(
    map_delete(map_delete(
    attributes(),'fid'),'id'),'left'),
    'top'),'right'),'bottom'),'Dom_age'),
with_variable('sel',
    map_avals( @deleteField),
    map_akeys( @deleteField)
    [array_find(@sel,array_max(@sel))]
    ||'=>'|| 
    map_get(@deleteField,map_akeys( @deleteField)
    [array_find(@sel,array_max(@sel))])
))))[0]
4

You can use a custom function in the Field calculator

from qgis.core import *
from qgis.gui import *

@qgsfunction(args='auto', group='Custom', referenced_columns=[])
def get_field_with_max_value(feature, parent, context):
    ## get field names of layer using 'context' argument
    fields = context.fields().names()
    
    ## get target fields (in this example, they have 'field' in their name)
    fields = [x for x in fields if 'field' in x.lower()]
    
    ## get the value of the first target column
    val = feature.attribute(fields[0])
    
    ## get name of first column
    max_field = fields[0]
    
    ## loop through fields and update name if its value is larger than the previous column
    for field in fields:
        if feature.attribute(field) and feature.attribute(field) > val:
            val = feature.attribute(field)
            max_field = field
            
    ## return the field name with the largest value as the result of the field calculation
    return max_field

Perform the calculation on a Text field that is long enough to accommodate your field names.

enter image description here

See this answer if you are unsure how to implement a custom function. (Skip the first step and use the Function Editor and Expression tabs in the Field Calculator)

11
  • It does generate the field name but it doesnt seem to loop through all the fields. The new column is populated by the field name of the first column with the name count in it
    – John
    Sep 10, 2021 at 10:15
  • For every row? For the rows In your screenshot, the first field would be the correct one, and as you can see in my example, it picks the correct field.
    – Matt
    Sep 10, 2021 at 10:22
  • For every row yeah. The only element I changed from your custom function was 'field' from the line fields = [x for x in fields if 'field' in x.lower()]
    – John
    Sep 10, 2021 at 10:31
  • I just reproduced the first row of your attribute table with a few of the count columns (excluding the first one to make sure the maximum value is not from that column) and it works fine. Are your columns integer or strings? Strings might cause the behaviour you describe.
    – Matt
    Sep 10, 2021 at 10:51
  • They are actually real values which was the output of the "count points in polygon"
    – John
    Sep 10, 2021 at 10:56
4

You can also use the Python console (Ctrl+Alt+P):

lyr = QgsProject.instance().mapLayersByName('yourlayername')[0]
fieldlist = [f.name() for f in lyr.fields() if f.name().lower().startswith('count')]
fieldlist = [[f, lyr.maximumValue(lyr.fields().indexFromName(f))] for f in fieldlist]
print('Max value is in field {0}: {1}'.format(*max(fieldlist, key=lambda x: x[1])))

Should print out something like:

Max value is in field count_100_to_150: 9999
0
1

Because the grid contained many polygons with no data, returning the first value with the solutions ended up including no values within my first column, making them appear with grids WITH values in the first column so tweaking the suggestions provided by other users I used

from qgis.core import *
from qgis.gui import *

@qgsfunction(args='auto', group='Custom')
def maxvaluefieldname(attrmap, feature, parent):
_attrmap = {key: attrmap[key] for key in attrmap.keys() if key not in ["fid", 
"id", "left", "top", "right", "bottom"] and attrmap[key] is not None} # exclude 
not needed fields
return_value = str(_attrmap.items())
for x in _attrmap.items():
    if max(_attrmap.values()) == 0:
        return_value = "AllZero"
        break
    if x[1] == max(_attrmap.values()):
        return_value = x[0]
        break
    

return return_value

thus allowing all grid cells with no value to be returned as ALLZero "column name" or key

0

I had the same problem, I resolved it with: "fieldname" = maximum ("fieldname").

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.