6

I would like to write a Python expression function which calculates the average of columns (variable length parameters), skipping NULL values.

@qgsfunction(args=-1, group='Custom')
def col_avg(vals, feature, parent):
    v = [ x for x in vals if x is not NULL]
    if len(v):
        return sum(v) / len(v)
    return 0

If there is a NULL value among the input parameters the result is NULL, too. If I use an expression in Select by Expression for example:

my_col is not NULL

it works. I use QGIS 2.18.7 on Ubuntu 16.04

* addition * Here is an image, how I call my function: enter image description here

5

Use None instead of Null ... or don't use it at all but specify the value must be set. You can read more about identifying the nulls

v = [ x for x in vals if x is not None]

or tell it to return items that are set, implicitly that are not-null

v = [ x for x in vals if x]

EDIT

Ok, it seems the issue is not the handling of the nulls within the function, but during the call to the function. Since you have the feature at hand, don't pass the column value but just the column name, and fetch the value directly in the function

@qgsfunction(args=-1, group='Custom')
def testlist(cols, feature, parent):
  vals = [feature[i] for i in cols if feature[i] is not None]
  return sum(vals)/len(vals)

For each column i in the list cols, if the corresponding feature value is not null, add it to the new list vals and process it as in your original post.

and you would call it using single quotes:

testlist('colNameA','colNameB')

Bonus: Your output field contains decimals, so let's ensure the function returns some:

return sum(vals)/float(len(vals))
  • thanks for your answer. It is some QGIS specific issue. I've tried None, too. Your both solutions work in pure Python, even in the QGIS Python console but not as an expression function in QGIS field calculator :( – Zoltan May 16 '17 at 5:57
  • How do you call this function? (what is given to the given for the list of arguments containing a null) – JGH May 16 '17 at 12:27
  • I've added an image to my question, how the function is called. – Zoltan May 16 '17 at 16:49
  • The use of column names instead of column values in field calculator is not comfortable for a simple user. I'll report this issue to the qgis dev list. – Zoltan May 17 '17 at 6:01
  • Yes, I agree with you, it is not intuitive. Please report the bug report / discussion URL for future reference! – JGH May 17 '17 at 14:32
0

The issue is in the function callby QGIS.If there is a NULL value in the parameter list my function will not be called by QGIS. I realised it when I added a print command to my function and I checked that in the Python console. I've found a workaround to use coalesce function for each parameters:

col_avg(coalesce("e2000", -999), coalesce("e2001", -999), coalesce("e2002", -999))

I also changed my function to skip -999 instead of NULL-s.

  • Please see my updated answer for a solution that does not require extra functionalities or comparison – JGH May 16 '17 at 18:29

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.