2

I am trying to calculate the mean/average of all the values in a row to obtain the final value i.e., "IRAT". How can I calculate it automatically using a field calculator in QGIS?

for e.g., for SW1 I need SL_class+....+....HI_classHI_class / 8 = IRAT

enter image description here

7
  • Did you try your expression? It seems to be the answer here. Jun 19 at 14:33
  • I am not getting the expression to try for multiple fields? Jun 19 at 14:34
  • Your expression looks good. Something like ( field_name_1 + field_name_2 + field_name_3) / 3. You don't need to write the = symbol. See the docs for a usage example. Jun 19 at 14:45
  • I tried and it worked, but some of the rows in the field is having NULL values because of no values for the cell so the result returns 'NULL' in IRAT , how can I fix that? Thank you Jun 19 at 15:14
  • 1
    You can use the Coalesce function, this way: coalesce("my_field_name_1", 0) which will become 0 if it finds a NULL value in my_field_name_1. Jun 19 at 15:20
5

You can use array-functions with QGIS expressions to treat NULL (empty) values as 0 (zero). Since QGIS 3.18, there are new aggregate functions for arrays, so that it's easy to calclulate a sum or a mean.

Add fields manually

This expression calculates the sum of the four numbers from value_1 to value_4:

array_sum( array( "value_1", "value_2","value_3","value_4"))

Same with array_mean() to calculate the mean for this four numbers.

Add all fields automatically

To get the values of all fields in an array, use map_avals( attributes( )). Than combine it with the solution above.

If do not want all, but most fields, removing just a few fields from the calculation, first get a list of all field-names and than remove the field-names you dont want to include:

  1. To get an array of all fieldnames, use: map_akeys(attributes( ))

  2. To remove some field-names, the expression is a bit longer. In line 10 to 13 you find the four fieldnames that should not be considered. Adapt this to your needs, the rest of the expression does not need any adaption:

    array_sum (
        eval (
            'array('  || 
            replace (
                '"' || 
                array_to_string (
                    array_filter (
                        map_akeys( attributes( )), 
                        @element not in (
                            'fid', 
                            'OBJECTID',
                            'SUB_basin',
                            'SWno'
                        )
                    )
                ) || 
                '"', 
                ',',
                '","'
            )  || ')'
        )
    )

Screenshot: based on your screenshot, I calculated a new field IRAT_value with the expression from above. It calculates the sum of all fields except the first four fields (the ones named fid, OBJECTID, SUB_basin and SWno), thus it creates the sum from SL_Class to HI_ClassHI_Class, where NULL is treated as zero (0). So for the first row: 3 + [0] + 3 + 1 + + 4 + 5 + 6 + 8 = 30:

enter image description here

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.