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.

enter image description here

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

enter image description here

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:

enter image description here

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

enter image description here

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')

• helpwithphysics: hey, thanks so much for this. At the moment when I execute this nothing is changing, and I get an error saying 'Field 23 of feature 253 doesn't exist' but for lots of features and the 8 fields of interest. Do you have any idea as to why this may be? The code is working up to line 6 as it prints the correct field indexes and field names.
• helpwithphysics: Actually it works beyond line 6 as I have printed the attribute map and it appears correct

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.