Subtracting columns (that include null values) in ArcMap Field Calculator

Question

I’m desperately trying to run a very simple command where I subtract one column with others. However the problem is that some rows have NULL values, therefore the result of subtracting is also NULL. I've tried to use the script found on this forum, but I guess I didn’t write it correctly, as it is not working as it should .

def stack(item1,item2):
 itemList = [item1,item2]
 myList = [item for item in itemList if (item != None)]
 return (item1-item2)

Is there a standard function for subtracting two or multiple fields in ArcPython?

Answer 1

there is no such thing as the "sum" operator for difference (which is not permutable), so you should test the validity of each item to decide how you run the substraction.

def stack(item1,item2):
 if item1 != None and item2 != None:
 return item1-item2
 elif item1 != None:
 return item1
 elif item2 != None:
 return -item2 #added minus if item2 > item1 and result should be negative
 else:
 return None 

alternatively, you could compute the difference between the first valid item and the sum of the following items (just need to check that there is at least one item)

def stack(item1,item2,item3,item4):
 itemList = [item1,item2, item3, item4]
 myList = [item for item in itemList if (item != None)]
 if len(myList)>0:
 return myList[0]-sum(myList[1:]) #difference between the first item and the rest of the list
 else: 
 return None 

As a remark, you need to choose a rule when you don't have enough valid fields. Null values are not supported with all database, but is the best solution in my opinion. Also note that you can change the minimum number of valid fields with the len(myList)>some_value condition

EDIT: if your "null" values are in fact "zeros", you can also replace the null values with zero in your list. That make the code even shorter.

def stack(item1,item2,item3,item4):
 myList = [item if (item != None) else 0 for item in [item1,item2, item3, item4] ]
 return myList[0]-sum(myList[1:])

• Tripy: Unfortunatly I get error: The code block used by the Calculate Field or Calculate Value has a syntax error. This error message provided will list the specific Python syntax error.
• radouxju: I simplified my code and checked for identation. Both codes work with my ArcGIS (10.1).
• recurvata: Make sure you set the parser in Field Calculator to Python (defaults to VBScript). Check the Show Codeblock box. @radouxju's function goes in Pre-Logic Script Code, and the fieldname = block should read stack(!field1!, !field2!), with appropriate field names for field 1 and field2. Oh, and if field1 and/or field2 aren't numeric, in the function you'll need to convert them, using int(), dbl(), etc., although if that was the problem you'd likely get a type error rather than a syntax error. Although you can have both, and python just balks at the first it sees.
• Tripy: In case item1 is Null value and item2 is positive value the result of item1-item2 gave positive result, but it should be negative. Therefore I add minus in front of item2 and got the correct results. Now it works perfect for me.