1

Now I know there are several ways to tackle this problem but so far none of the following worked for me. I got a huge attribute table out of various spatial joins so pretty much all of the fields have NULL values in some rows. This table will be processed and new fields are going to be calculated programatically so the solution involving the coalesce function is out of the race because the expressions for the following calculations are complex enough and I don't want to do all that typing. So here are my failed attempts of fiddeling together a function that i can use inline:

def replaceNulls(layer):
   context = QgsExpressionContext()
   context.appendScopes(QgsExpressionContextUtils.globalProjectLayerScopes(layer))
   with edit(layer):
      for field in layer.fields().names():
          for feat in layer.getFeatures():
              expr = QgsExpression('if({f} is null,0,{f})'.format(f=field))
              feat[field] = expr.evaluate(context)
              layer.updateFeature(feat)

Using an expression from this answer replaces ALL the fields with 0 and some with field - independent of their field type.

def replaceNulls(layer):
   context = QgsExpressionContext()
   context.appendScopes(QgsExpressionContextUtils.globalProjectLayerScopes(layer))
   with edit(layer):
      for field in layer.fields().names():
          for feat in layer.getFeatures():
              expr = QgsExpression('case when {f} is null then 0 else {f} end'.format(f=field))
              feat[field] = expr.evaluate(context)
              layer.updateFeature(feat)

This function replaces all values that are NOT NULL with 0 for some reason all though it is said here that:

The expression syntax is exactly the same as in the field calculator GUI. So you can test them in the GIU first, before copying them into your PyQGIS script.

If that is the case what am I missing out on? The same source also mentions a more pythonic way but how to if feat[field] is None: when the NULL value is still a QVariant?

TL;DR: replace(NULL,0). How?

2 Answers 2

3

one way I found to solve the issue of replacing the NULL values with zeros or similar values is the following:

  1. I define a function that maps the NULL value to some other value of choice, depending on the QVariant type:
def map_null_to_zero(qvariant_type):
    if qvariant_type == QVariant.Invalid or qvariant_type >= QVariant.Int and qvariant_type <= QVariant.Double:
        return 0
    elif qvariant_type == QVariant.String or qvariant_type == QVariant.Char:
        return "0"
    elif qvariant_type == QVariant.Bool:
        return False
    else:
        raise ValueError("Unexpected Data type {}".format(qvariant_type))
  1. Then, I define another function that, given an input list of attributes and attributes' types, it returns a modified list of attributes without NULL:
def modify_null_attr_to_zero(li_attr=None, li_attr_type=None):
    assert len(li_attr) == len(li_attr_type), "The input list have different lengths"
    return [attr if attr != NULL else map_null_to_zero(type) for attr, type in zip(li_attr, li_attr_type)]

Note that null attributes are identified using the comparison operator (==) with the NULL type, which is a special type of the QVariant:

type(NULL)
<class 'PyQt5.QtCore.QVariant'>

Some more information can be found in the Qt documentation.

  1. Finally, I can apply the changes to the required layer:
def replace_nulls(layer):
    li_attr_types = [a.type() for a in layer.fields()]
    with edit(layer):
        for f in layer.getFeatures():
            f.setAttributes(modify_null_attr_to_zero(f.attributes(), li_attr_types))
            if not layer.updateFeature(f):
                print("Error in updating feature {}".format(f.id()))

I think this "modular" solution gives you the flexibility of deciding how to convert NULL based on the type of the attribute. I tested it on the open dataset populated_places from the natural earth. Here is how the original attribute table looks like: Excerpt of the attribute table of original layer And here is how it looks after the execution of the replace_nulls function: Excerpt of the attribute table of the modified layer

I hope this helps.

3
  • thanks a lot for your almost ready-to-use answer: it replaces QVariant.String just fine but none of the others. Interestingly if I manually add a Int or Double field with nothing in it f.attributes() shows them as pythonic None instead of NULL. The values of these field also get replaced by your script but those containing NULL are beeing ignored by layer.updateFeature(f). Why?
    – maxwhere
    Jun 7, 2020 at 17:18
  • Hi @maxwhere, thanks for your feedback. First of all, I edited my answer by removing the NoGeometry in the feature request as this was in fact canceling the geometric information of the features. Then, it is quite interesting the behavior you describe. I have tried my script on a layer with NULL values that result from a spatial join operation and I can see that it is successful also for numeric data types. If the attributes in your layer successfully compare to NULL, the script should work. Maybe you can check that. Cheers.
    – fastest
    Jun 7, 2020 at 20:31
  • You should be able to replicate the same situation: use the first code snippet in my link to that PyQGIS 101 and replace some values with NULL or None, create a layer and pass it to the replace_nulls function of yours: Integer and Double not replaced. And yes: f[field] == NULL and f[field] == None both True.
    – maxwhere
    Jun 7, 2020 at 21:02
1

So despite the very elaborate answer by fastest which for some reason didn't work for QVariant.Integer and QVariant.Double I ended up using a simpler solution. It might be a bit hacky but it's a classical case of "works-for-me":

def replace_nulls(layer):
    with edit(layer):
        for feat in layer.getFeatures():
            for field in feat.fields().names():
                if feat[field] == None:
                    feat[field] = '0'
            layer.updateFeature(feat)
7
  • Nice and (almost) clean. Some small improvement hints: 1. Undent layer.updateFeature, so it's called once per feature (not once per field). 2: Iterate for field in feat.fields(): and check field.isNumeric() to decide for string vs. number. Jun 7, 2020 at 21:04
  • Well apparently it seems that differenciating between datatypes is not necessary at all
    – maxwhere
    Jun 7, 2020 at 21:15
  • It might work or not, depending on the data provider in use and other configuration details. Jun 7, 2020 at 21:32
  • That's what i meant by 'classical case of works-for-me'. And as for "It might work or not, depending on the data provider in use and other luminaries": that basically applies for anything. Thanks for the flowers.
    – maxwhere
    Jun 8, 2020 at 12:36
  • You can keep it like that or improve it as a compatible reference for future readers. It looks like this is soon going to stop working (github.com/qgis/QGIS/issues/36715#issue-624469015). Jun 8, 2020 at 13:34

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.