topic Re: Field Calculator: ignore NULL value in Data Management Questions

Field Calculator: ignore NULL value

ImmanuelWeber — Tue, 25 Jun 2013 22:20:52 GMT

Hey,
I am using a File Geodatabase and I want to calculate the sum of different coulms. like column5= [column1]+[column2]+[column3]
That works with the Field Calculator very well, but if there is a <Null>, then the result is also NULL.
Is there a way to ignore the Null fields?
(I want to keep the field like they are, I don't want to overwrite them with '0')
thanks for helping
cheers Immanuel

Re: Field Calculator: ignore NULL value

JillianPenney — Sat, 11 Dec 2021 21:24:33 GMT

Hi Immanuel,

Have you tried using an update cursor in python? You can write a simple script to update the column you want calculated. For example, I have a feature class with columns f1, f2, f3, and calc. F1, f2, and f3 have a mix of numbers and null values, and calc is the column that I want updated with the calculation of f1 + f2 +f3. Here is my simple python script to update these answers and skip null values. Note that the only value that gets changed is the "calc" value, and print statements are just used as reference.

# Import the arcpy module
import arcpy

# Location of the feature class I want to update
fc = "C:\\Data\\NothingSpecial.gdb\\calc"

# Create a cursor using arcpy data access module
cursor = arcpy.da.UpdateCursor(fc,"*")

# For each row in the feature class:
for row in cursor:
    print "For row: "+str(row)
    
    # row[2] is the idex position of the f1 column
    if row[2] <> None:
        x = row[2]
        print "x = %d" %(x)
        
    # row[3] is the idex position of the f2 column
    if row[3] <> None:
        y = row[3]
        print "y = %d" %(y)
        
    # row[4] is the idex position of the f3 column    
    if row[4] <> None:
        z = row[4]
        print "z = %d" %(z)

    # Now use these values to calculate the "calc" column
    if x:
        calc = x
        print "calc = %d" %(x)
    if y:
        calc = calc + y
        print "calc + y = %d" %(calc)
    if z:
        calc = calc + z
        print "calc + z = %d" %(calc)

    # Set the calc column (row[5]) as the calculated value
    row[5] = calc

    # Update the row with the new value for "calc"
    cursor.updateRow(row)

    # Set all values back to 0 for the next row
    x=y=z=calc=0

Here's a link for more information on using Update Cursors:
http://resources.arcgis.com/en/help/main/10.1/index.html#//018w00000014000000

Re: Field Calculator: ignore NULL value

vico — Wed, 22 May 2024 18:47:37 GMT

Wanted to chime in here 10 years later to note that if you're using arcpro and you switch to an arcade expression (instead of python) in the field calculator it'll successfully read the nulls as zeros. Some classic esri nonsense when I have a deadline to meet, but glad I tried that and wanted to share here.

Re: Field Calculator: ignore NULL value

JessicaTaylor — Tue, 25 Feb 2025 21:07:04 GMT

Thanks for the arcade tip- ignores all my null values and adds the fields that do have values- weird, but very helpful thanks!

Sum($feature.Jan,$feature.Feb,$feature.Mar,$feature.Apr,$feature.May,$feature.Jun,$feature.Jul,$feature.Aug,$feature.Sep,$feature.Oct,$feature.Nov,$feature.Decr)