0

I have a table that contains data that needs to be summed and the result needs to replace an existing value. I only want to sum the values if they are not -9999. The result would be put in the existing Total column.

Here is my table

Total D1 D2
10 5 5
5 -9999 5
5 5 -9999

I am using ArcGIS Pro Python 3 Calculate Field on the Total column. I thought that the following would solve my issue, but I believe my condition is not being set properly. My result is only replacing Total with D1. The sum is not occurring.

Expression

calc(!D1!,!D2!)

Code Block

def calc(D1 , D2 ):
if (D1) != -9999:
    return (D1)
elif (D2) != -9999:
    return (D1 + D2)
else:
    return 0 

This code is replacing a VB Script used in arcmap 10.8

a=0
If [d1]>=0 Then
a=a+ [d1]
End If
If [d2]>=0 Then
a=a+ [d2]
End If

Total = a

Is there a better way to write this script?

I could have up to 8 columns to sum.

def calc(D1 , D2 ):
if (D1) != -9999 and (D2) != -9999:
    return (D1 + D2)
elif (D1) != -9999 and (D2) == -9999:
    return (D1)
elif (D2) != -9999 and (D1) == -9999:
    return (D2)
else:
    return 0 
1
  • The first thing your code says: "if D1 is valid then the total is D1"
    – mikewatt
    Mar 9, 2023 at 17:16

3 Answers 3

2

You don't need a code block for this, you can handle it with a single expression by using 6.13. Conditional expressions - Python 3 documentation

(0 if !D1! == -9999 else !D1!) + (0 if !D2! == -9999 else !D2!)

UPDATE:

The answer by @Felix won't work with ArcGIS Pro because <> is no longer a valid comparison operator with Python 3. That said, the overall approach scales cleaner if there are lots of columns invovled, but there are a couple of small gotchas to point out.

First, and I consider this a defect with Pro, is that one cannot use the not equals comparison operator, !=, in the expression for Calculate Field because Esri encloses field names in exclamations. The parser for the expression can't handle having a non-paired exclamation, so an invalid syntax error is thrown.

Second, there is the question of how NULL should be handled. Since SQL NULL gets converted to Python None, the presence of NULL will create a TypeError: unsupported operand type(s) for +: 'int' and 'NoneType' for the Python sum function. So, NULL and -9999 both have to be handled in the expression.

The following handles NULL and -9999 as 0 and sums all the fields invovled while also addressing the parsing issue in the expression with exclamations:

sum(0 if v==-9999 else v for v in (!D1!,!D2!,!D3!) if v is not None)

Since Python None is false-y, if v is not None can be shortened to just if v, but I left it in for clarity.

1

Is there a better way to write this script? I could have up to 8 columns to sum?

You could use a "starred arg" to accept any number of fields fed into the function which you can then iterate over:

def calc(*values):
    return sum(val if val != -9999 else 0 for val in values)

You don't mention what you want to happen in the case that all values are -9999. Is it acceptable to return a total of 0 in this case, or should the total end up as -9999 as well?

If you don't want to return 0 when all columns are "nodata":

NODATA = -9999

def calc(*values):
    if all(val == NODATA for val in values):
        return NODATA
    return sum(val if val != NODATA else 0 for val in values)
1

Even shorter without code block:

sum([v for v in [ !D1!, !D2!, !D3!, !D4!] if v <>-9999])

Yes "<>" instead of "!="

enter image description here

1
  • In Pro 3.1 this expression throws an SyntaxError: invalid syntax of ERROR 000539: File "<expression>", line 1 sum([v for v in [1,2,3] if v <>-9999])
    – bixb0012
    Mar 12, 2023 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.