7

I want to use aggregating functions (min, max, mean, ...) on all the attributes (= columns) within a row. In this topic Finding the minimum value of different columns in QGIS, this is done by calling each attribute explicitly, e.g.:

min('col_A', 'col_B', 'col_C')

This works fine for a small number of known columns. However, I have to apply this to several hundred columns whose names change from layer to layer.

How can I apply e.g. a min-function to all columns?

Preferably the list of all columns can then be filtered by certain conditions since I realized that certain columns cause errors (e.g. 0, NULL or non-numerical attributes).

0

3 Answers 3

8

There might be a simple solution for simple cases (where non-numeric columns are of no concern and simply ALL columns can be aggregated). I created a more sophisticated and flexible approach, using a custom function:

all_attributes_min(attributes(), make_list('field_to_exclude1', 'field2_to_exclude'))

where attributes() passes on a map for each feature {'Field1':value1, ..., 'FieldX':valueX} and make_list('') can be used to exclude fields (= attributes) that should be excluded from the aggregate.

The function has some checks implemented (exclude NULL, None, 0 and non-numerical values) that you can adjust by removing the corresponding lines of code. Also you can adjust it to use max, mean, ... instead of min.


To use the function, create the following two functions in the function editor (Tab next to 'Expression' within the Expression Editor).

Function Editor

The code comes with it's documentation for usage, therefore it's a bit long. The actual logic is quite short, making use of list comprehension.

You can view the "compiled" documentation when clicking on the respective custom function in the box next to the expression editor:

Expression Editor with doc for function

Function 1: Helper function

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

@qgsfunction(args=-1, group='Custom')
def make_list(list_of_args, feature, parent):
    """
    Creates a python list from a number of arguments.
    <h2>Example usage:</h2>
    <ul>
      <li>make_list(1, 2 ,3) -> [1, 2, 3]</li>
    </ul>
    """
    return list_of_args

Function 2: Aggregate function

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

@qgsfunction(args='auto', group='Custom')
def all_attributes_min(attrmap, exclude, feature, parent):
    """
    Calculates the min of an attrmap.
    The argument exclude is a list of attributes (identified by column name) 
    that should be ignored.
    
    Currently this function also removes 0 values.
    
    <h2>Example usage 1</h2>
    <ul>
      <li>all_attributes_min({'A':72, 'B':7, 'C':42}, []) -> 7</li>
      <li>all_attributes_min({'A':72, 'B':7, 'C':42}, ['B']) -> 42</li>
      <li>all_attributes_min(attributes(), ["field2"]) -> 42</li>     
    </ul>
    
    <h2>Example usage 2</h2>
    <p>With a table resulting from the tool Distance Matrix, 
    containing a field "ID" and several number fields:</p>
    <p>
    <code>{'ID':'node-42', 'node-1':4, 'node-2':4, ..., 'node-42':0, 'node-43':999}</code>
    </p>
    <p>The exclude argument can be used to filter non-numeric attributes, e.g.
    <code>all_attributes_min(attributes(), make_list('ID', 'fid'))</code>
    </p>
        
    <p>Or in case of the distance matrix, also, the use the ID field to remove the 
    attribute of the node itself, which would have distance 0 (in the example 42): 
        <code>all_attributes_min(attributes(), make_list("ID", 'ID', 'fid'))</code>
    </p>
    <p>
    For this example the helper function make_list() needs to be present, too:
    </p>
    <p>
        <code>
        
        @qgsfunction(args=-1, group='Custom')<br/>
        def make_list(list_of_args, feature, parent):<br/>
            return list_of_args<br/>
        </code>
    </p>
    """
    # exclude not needed fields
    attrmap_filtered = {key: attrmap[key] for key in attrmap.keys() if key not in exclude}
    
    # remove non-numeric entries to prevent errors
    my_vals = [i for i in attrmap_filtered.values() if isinstance( i, ( int, float ) )]
    # remove None entries to prevent errors
    my_vals = [i for i in my_vals if i is not None]
    # remove 0 entries to prevent 0-minimas
    my_vals = [i for i in my_vals if i != 0]
    # check for empty list
    if my_vals: 
        my_min = min(my_vals)
        return my_min
    else: 
        return -1
6

You can also use some in-build functions, to find the minimum value:

array_min(array_remove_all(map_avals(attributes()), null))

We only need to remove null values here, as they would be always the minimum. Other types such as strings or datetimes will be ignored.

This answer to Summing up values in rows with exceptions in QGIS may also be of interest:

https://gis.stackexchange.com/a/425548/107424

1
6

If it is useful I can also suggest a PyQGIS solution.

It utilizes the min() function inside the QgsExpression() class.

Proceed with Plugins > Python Console > Show Editor and paste the script below:

# imports
from qgis.core import QgsProject, QgsExpression, QgsExpressionContext, QgsExpressionContextUtils

# refer to a layer by its name
layer = QgsProject.instance().mapLayersByName("lines")[0]

# get all attributes' names
attrs = list(layer.attributeAliases().keys())
attrs = ', '.join(f'"{attr}"' for attr in attrs)

# setting up an expression with desired fields
expression = QgsExpression(f'min({attrs})')

# define the expression context to encapsulate parameters for evaluation
context = QgsExpressionContext()
context.appendScopes(QgsExpressionContextUtils.globalProjectLayerScopes(layer))

# start layer editing
with edit(layer):
    for feat in layer.getFeatures():
        context.setFeature(feat)
        feat["min"] = expression.evaluate(context)
        layer.updateFeature(feat)

Press Run script run script and get the output that will look like:

result


References:

Your Answer

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

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