4

I am looking for a way to replace all "NULL"-Values throughout multiple rows/ all "NULL"-Values in an attribute table. The table is from a SHP-File (shapefile).

Is there any easy way? Right now I am selecting then by hand and replace them row by row which takes quite some time. I have already tried to look up a solution, however as I want to replace them in the whole table it seems to be not that easy.

NULL values

5
  • Sorry, yes it is QGIS. Datasource is a Shapefile/ .SHP (i have merged multiple shape files into one). thanks a lot
    – Corninski
    Oct 22 '20 at 14:34
  • 1
    How many columns exist in your database? What value(s) will replace the NULLS? For a given column, will that new value be the same, or different for every record in the database? Will the new value be the same, or different, for every column in your database?
    – Stu Smith
    Oct 22 '20 at 15:19
  • What is the data type of all those fields?
    – BERA
    Oct 22 '20 at 16:45
  • Do you actually mean columns when speaking of rows?
    – MrXsquared
    Nov 3 '20 at 18:59
3

This should work with text and numeric fields. Backup your data first..

layer = QgsProject.instance().mapLayersByName('replacenulls_backup')[0]
fs = layer.fields()
d = {e:0 if f.isNumeric() else '0' for e,f in enumerate(fs)}

with edit(layer):
    for row in layer.getFeatures():
        data = row.attributes()
        for i, val in enumerate(data):
            if val == NULL:
                data[i]=d[i]
        row.setAttributes(data)
        layer.updateFeature(row)

enter image description here

3
  • I had to replace fs=lyr.fields() with fs=layer.fields() but it did just what I needed. Very nice. As suggested have a backup since it changed the attributes in the layer instead of creating a new in memory layer or it gave me an AssertionError if I put the layer in an edit session before running.
    – johns
    Nov 3 '20 at 15:45
  • @BERA, can you be so kind and criticize the code I wrote. Can you please give me a suggestion or a recommendation on how to make it better. Thank you.
    – Taras
    Nov 30 '20 at 9:24
  • 1
    I'm no expert on pyqgis. If it is working and is understandable it's good :). I thought assigning string fields with 0 and not '0' would cause errors but apparently not. You could replace the start/stop editing with a with edit(lines_null):
    – BERA
    Dec 1 '20 at 8:57
1

A solutions with no programming required.

Let's assume there is a polyline layer 'line' (blue) with its attribute table accordingly, see image below.

input

Deploy the "Refactor fields" geoalgorithm. Unfortunately it has a disadvantage, filling the 'Source expression' with a Conditional Function is still necessary, i.e.

if(condition,result_when_true,result_when_false)

refactor

and get the output

result

0

Another alternative using PyQGIS for all attributes that exist in the Attribute table.

Let's assume there is a polyline layer 'line' (blue) with its attribute table accordingly, see image below.

input

Proceed with Plugins > Python Console > Show Editor and paste the script below

# accessing a layer with polyline features by its name
lines_null = QgsProject.instance().mapLayersByName("line")[0]

# starting the editing of the layer
lines_null.startEditing()

# looping over each feature from the layer
for feature in lines_null.getFeatures():
    # looping over each attribute from the attribute table of the layer
    for i in lines_null.attributeList():
        # getting only attributes with NULL-values
        if feature[lines_null.attributeDisplayName(i)] == NULL: 
            feature[lines_null.attributeDisplayName(i)] = 0 # appropriating each NULL-value with 0
            lines_null.updateFeature(feature) # updating the layer with the new feature value

# committing changes in the attribute table
lines_null.commitChanges()

Press Run script run script and get the output that will look like

result

Mind that the value '0' will be shown differently due to the field's type

type was will be
string NULL 0
integer NULL 0
real NULL 0,0

References:

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.