topic Re: Calculate field based on sum of other fields in Data Management Questions

Calculate field based on sum of other fields

JacobPederson — Mon, 18 Mar 2013 21:34:24 GMT

Working with a personal geodatabase, I am trying to calculated a field for a layer that contains 5000+ polygons based on the sum of 7 other fields. I.e., every polygon has either a "1" or a "<null>" value in each of the 7 fields. If one of the polygons has 4 "1" values and 3 "<null>" values, I want my new field to show a "4."

When I carry out the calculate field command (sum_all= [field1]+[field2]...etc.) the program takes several hours to process the command, but no change occurs in the new (sum_all) field.

I hope that there is a simply solution, any help is much appreciated!

Jacob

Re: Calculate field based on sum of other fields

StormwaterWater_Resources — Sun, 12 Dec 2021 01:09:13 GMT

If you sum Nulls the result is Null even if most of the inputs are numbers. You can filter out Nulls by using a code-block in field calculator with the following:

Parser:
Python

Expression:
null2z(!field1!) + null2z(!field2!) + ... + null2z(!fieldn!)

Code Block:

def null2z(num):
    if num:
        return num
    else:
        return 0

You can call the function whatever you want. You can do the same thing in a python script: just copy in the above function, and wrap your inputs in the function call. In python if you do an if on a value it returns false (takes the else branch) if the input value is Null.

PS: suggestion: test things on a small subset of your data (but don't save your edits) before running against your entire data set.

PPS: It might be faster to simply assign a 0 to all your Nulls. YMMV

Re: Calculate field based on sum of other fields

JacobPederson — Fri, 29 Mar 2013 14:32:25 GMT

This helps a bunch. Thanks!