QGIS Maximum Value Column – Returning Column Name of the Max Value in QGIS

attribute-tablefield-calculatorfields-attributesmaximumqgis

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.

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.

Best Answer

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.

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]

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

and get the output:

The input:

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:

References:

Getting key with maximum value in dictionary?

Related Solutions

[GIS] How to add a new column in attribute table with attribute with indexes of another max-min ordered attribute field

Here's a funny trick using a Spatialite query to get the values in descending order. If you do a count() and use a WHERE clause to get values >= the current value, then you'll actually be getting the order, so if I have a table "mypoints" with a column "value" and an empty column of "value_order" then:

SELECT value, (
SELECT count(*) FROM mypoints AS my 
WHERE my.value>=mypoints.value) AS orders
FROM mypoints 
ORDER BY value DESC;

gives a list of the values and their order. I can then update the "value_order" column like so:

UPDATE mypoints SET val_order = (
SELECT count(*) FROM mypoints AS my 
WHERE my.value>=mypoints.value
);

HTH

QGIS Automatic ID – Adding Automatic Numbering to the “id” Field in QGIS

The first feature returns a NULL for maximum("id") as there are no id values to calculate a maximum on.

To get around this, use the coalesce() function which returns the first non-null value in a series of parameters — coalesce(expression1, expression2, expression3) is a more concise version of CASE WHEN (expression1) IS NULL THEN (expression2) CASE WHEN (expression2) IS NULL THEN (expression3) ....

For the first feature to be 1, use coalesce(maximum("id"), 0) + 1 as your default value.

Which means that when you digitise your first feature and maximum("id") returns NULL, you go to the next parameter in coalesce(), which is 0. Then add 1 to that.

From your second feature onwards, maximum("id") returns the maximum "id" value (maximum "id" from existing features = 1, 2, 3...)

And then adds 1 to that (result "id" for current feature = maximum "id"+ 1 = 2 (1+1), 3 (2+1), 4 (3+1)...)

See Autogenerate consecutive job numbers per group using QGIS expressions and the comments to this answer to Is there a way of auto_increment for the ID column in QGIS.

Best Answer

Related Solutions

[GIS] How to add a new column in attribute table with attribute with indexes of another max-min ordered attribute field

QGIS Automatic ID – Adding Automatic Numbering to the “id” Field in QGIS

Related Question