Set NULL values to 0 in specific columns using PyQGIS

Question

I have over 20 fields and I wish to set the NULL values in the final eight fields to 0. I want to be able to do this both with the field calculator and with the PyQGIS console. At the moment neither are working.

This image shows the NULL values which I wish to set to 0.

When I use the field calculator for one individual column, I am entering the following:

i.e. I enter the code:

if("Animal_2013" is NULL , 0,  "Animal_2013" )

in the 'Preview' you can see that it is showing 0, and I have checked that it does show 0 and the non-null values for the correct rows. However, when I press enter nothing changes, but an option to 'update all' appears in the top right. If i click this following message is shown:

'An error occurred while evaluating the calculation string: No root node! Parsing failed?'

As shown below:

How can I solve this?

Additionally, if I am trying to use the Python console (which I would like to know how to do as I will need to do this for a large amount of other columns for a different dataset) I have tried this:

layer = QgsProject().instance().mapLayersByName('weather_tmax')[0]
with edit(layer):
        for row in layer.getFeatures():
            data =row.attributes()
            print(data)
            for i in [16,17,18,19,20,21,22,23]:
                if data[i] == NULL:
                    data[i] = 0
            row.setAttributes(data)
            layer.updateFeature(row)

but nothing changes. I have [16,17,18,19,20, 21, 22, 23] as this corresponds to the indices of the columns of interest, however, I would like to be able to use the column names rather than working out the column numbers for a more general approach if possible.

Answer 1

You can use pyqgis:

layer = QgsProject.instance().mapLayersByName('Refactored')[0] #Change refactored to the name of your field
fieldprefix = 'animal' #If you want to include more fields you can do fieldprefix = ('animal','human','somethingelse')
fieldlist = [f.name()  for f in layer.fields() if f.name().lower().startswith(fieldprefix)] #List all field starting with animal. You can also list them manually
#['Animal1', 'Animal2', 'Animal3']
fieldindexes = {fieldname:layer.fields().indexFromName(fieldname) for fieldname in fieldlist} #Find their indexes
#{'Animal1': 0, 'Animal2': 1, 'Animal3': 2}

attributemap = {} #A dictionary to store feature id as key, and new values as values

for f in layer.getFeatures(): #For each feature/row
    rowvalues = [f[fieldname] for fieldname in fieldlist] #[10, NULL, NULL]
    newvalues = [0 if QVariant(val).isNull() else val for val in rowvalues] #[10, 0, 0]
    attributemap[f.id()] = {fieldindexes[fieldname]:val for fieldname,val in zip(fieldlist, newvalues)}

#attributemap[1]
#{0: 20, 1: 0, 2: 12} #So for feature 1 (first row in attribute table), 
#   the value in field 0 should be 20, field 1 0, field 2 12

layer.dataProvider().changeAttributeValues(attributemap) #Update the values

The same result can be achieved alot easier using geopandas:

import geopandas as gpd

filename = r'/home/bera/Desktop/GIStest/nulls.gpkg'
tablename = r'table123'

df = gpd.read_file(filename=filename, layer=tablename)

#df.head()
#  stringfield_A  intfield_A  intfield_B datefield_1
#0          None         NaN        15.0  2022-01-01
#1           010         NaN         NaN        None
#2           020         NaN         NaN        None
#3          None         NaN         NaN        None
#4          None         NaN         NaN        None

#List the numeric columns
numeric_columns = df.select_dtypes('number').columns
#Index(['intfield_A', 'intfield_B'], dtype='object')


df[numeric_columns] = df[numeric_columns].fillna(0).astype(int)
#  stringfield_A  intfield_A  intfield_B datefield_1
#0          None           0          15  2022-01-01
#1           010           0           0        None
#2           020           0           0        None
#3          None           0           0        None
#4          None           0           0        None

df.to_file(filename=filename, layer='table123_nonulls')

Answer 2

There is an easier way to do it (or so I think).

1. Open the layer attribute table.
2. Select the items where you want to change. You can use the "Select features using and expression" tool, or do it by hand ordering the elements for a column and selecting all the entries with NULL value.
3. Use the "toggle multi edit mode" to change all the selected items' values for each column.* *You can repeat the 2nd step between attributes if it isn't always the same items.