topic Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly. in Data Management Questions

I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

KevinMilton — Wed, 04 May 2016 13:30:15 GMT

If field X <> Null

then field calculate field Y with field X.

(using 10.4)

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

DanPatterson_Retired — Sun, 12 Dec 2021 04:59:04 GMT

since you are learning, nulls of any sort are treated as boolean False

>>> a = ""
>>> if a:
...  print(a)
... else:
...  print("null string")
... 
null string
>>> a = "hello"
>>> if a:
...  print(a)
... else:
...  print("null string")
...
hello
>>>

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

JoshuaBixby — Wed, 04 May 2016 13:44:02 GMT

Did you try searching GeoNet for an existing answer? I am quite sure this type of conditional field calculation has been covered a time or two. That said, see if the following works for you (apply to Field Y using Python parser):

!field_x! if !field_x! else !field_y!

!field_x! if !field_x! is not None else !field_y!

UPDATE:

Although my original code would have worked for most data types, it could have given incorrect answers with string fields.

Python doesn't have a data type called NULL/Null/null. The Python Built-in Types documentation states for the null object, "There is exactly one null object, named None (a built-in name)." If you are used to working with NULL in databases and SQL; be careful, None in Python doesn't behave exactly the same as NULL even though None is referred to as "the null object" in some documentation.

In Python there are several falsy values, i.e., values that are not false but evaluate to false in a Boolean context. Some common examples are None (the NoneType), "" (empty string), [] empty list, () (empty tuple), and 0 (the number zero). When using the Python parser with the Field Calculator or cursors with ArcPy, NULLs need to be converted, and Esri has chosen to convert them to None, which is understandable given the built-in data types available with Python.

The issue with my original code is that an empty string would have compared as False, the same as None, but an empty string is not NULL. Changing the code to explicitly check for None solves this issue.

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

JayantaPoddar — Wed, 04 May 2016 14:48:03 GMT

If you could do it using field calculator of attribute table, just follow these steps

1. Select by Attributes with following expression:

"Y" IS NOT NULL

2. Once selected, I would use Field Calculator on field "X", with the following expression

"Y"

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

TedKowal — Sun, 12 Dec 2021 04:59:07 GMT

I have been working the last year converting to Python from VBA. I still have issues with Python's handling of NULLS! From my perspective results are almost always inconsistent; so my level of trust using Python to handle null is very low. I almost always use vbscript isnull() function to check.

I learned something new thanks to Dan, I did not realize that nulls are treated as "false". Coming from a VB background nulls are neither True or False.... this concept may help me with my own inconsistencies with Pythons and Nulls.

if  IsNull(FieldX) then
  FieldY = 'Calculation on FieldX being Null'
else
  FieldY = 'Calculation when FieldX is not Null'
end if

Another thing to check or to make sure that your tests includes Blanks,embedded tabs or other control character (see a lot of this when cut and pasting from excel)... these are not nulls although they act and appear to be nulls. These include all characters with ASCII values between 0 and 31.

I made some tests using Python's of treating Null values as false .... works great if the data is clean... however on my "dirty tests" (real life cut/paste from external programs) I would still have to strip all embedded control characters before the Python script would work whereas the vbscript had no issues.

PS... added later... This exercise has shown me that I have been lazy relying on VB to test for nulls... my data is not as clean as I thought it was....

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

JoshuaBixby — Sun, 12 Dec 2021 04:59:09 GMT

In ArcGIS, NULL in a data table is converted to None in Python. The equivalent of IsNull in VBA is is None in Python.

if FieldX is None:
  FieldY = # Calculation on FieldX being Null
else:
  FieldY = # Calculation when FieldX is not Null

(Note: Not fully functional Field Calculator code, just rough out concept.

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

curtvprice — Sun, 12 Dec 2021 04:59:12 GMT

Field calculate field X with any value that is not null from field Y.

For answers to more Python questions, I recommend searching in Python and python snippets

In Python, zero and Null, and "" are false, as you describe above (repeating what you said, but hard to find things up-thread sometimes, sorry). So, Joshua, I think you get a cookie.

Here's my solution: often for this problem you want to put something in there to indicate a null value was there in Y.

Calculate Field

Field: X

Expression:

CopyNonNull(!Y!) # Null Y values are assigned None (Null, sort of...)

CopyNonNull(!Y!, -9999) # Null Y values are assigned -9999

Parser:

PYTHON_9.3

Code block:

def CopyNonNull(val, missing_val=None):
  if val is None:
      return missing_val
  else:
      return val

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

DanPatterson_Retired — Sun, 12 Dec 2021 04:59:15 GMT

the nullness summary (Curtis, in the NumPy Repository)

Here is the output: (formatting issues today)

Object.... Empty... Type......
[]        True    list     
[1]        False    list     
()        True    tuple   
1          False    int     
{}        True    dict     
{1: 'one'} False    dict     
''        True    str     
'1'        False    str     
None      True    NoneType 
True      False    bool     
1          False    int     
False      True    bool     
0          True    int     
Object................... Empty... Type...... 
Counter()                True    Counter  
Counter({0: 1})          False    Counter  
OrderedDict()            True    OrderedDict
OrderedDict([(1, '1')])  False    OrderedDict
array([], dtype=float64)  True    ndarray  
array(1)                  False    ndarray

There are other examples dealing with geometry

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

JoshuaBixby — Sun, 12 Dec 2021 04:59:17 GMT

Although Python Conditional Expressions have their limits, they can be used in this and similar cases to avoid using a code block:

Calculate Field

Field: X

Expression:

None if !Y! is None else !Y!         # Null Y values are assigned back to Null (Python None will get converted back to Null)

-9999 if !Y! is None else !Y!        # Null Y values are assigned -9999
!Y! if !Y! is not None else -9999    # Null Y values are assigned -9999.  Same as above just changes order of conditions.

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

TedKowal — Thu, 05 May 2016 15:56:34 GMT

In pure GIS tables I do not doubt your correctness about ESRI Nulls equating to None. However, in My real world, as a state authority, we are beggars of data and in many cases have little or no say in the formatting or the QA process the data we receive undergoes.

Using my data, None is useless for testing for nullness ( hit or miss). The best pythonic approach I have used with my data is using the old fashion trim and concatenation approach to strip out all the Ascii codes 31 or less and final test for a single space or as many here indicated, assign a comparison number or string to represent nullness for the down and dirty. My clean approach, when I have time is to validate and correct the data before it gets into GIS (Then None and Dan's boolean approach works 100%).

I still state that Python has issues with Nulls. It quacks like a duck, looks like a duck and feels like a duck -- But it is not a duck!

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

DanPatterson_Retired — Sun, 12 Dec 2021 04:59:20 GMT

Ted... still not ready to move on to 'not a number ' until you can accept nulls

>>> a = np.NaN
>>> b = np.NaN
>>> a == b
False

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

JoshuaBixby — Thu, 05 May 2016 17:18:18 GMT

I guess I see it different, i.e., it is a data issue and not a Python issue.

I have worked for government in a variety of positions ranging from planning to information management and now information technology. I have been on both the producing and consuming side of data, including data problems. I agree that messy data doesn't fit cleaning into code, regardless of the language.

Regardless of the language (Python, SQL , etc...), most of what you describe is neither Null or None. An empty string might be falsy, but it certainly isn't Null. The same holds true for strings with ASCII codes 31 or less. I am interested in how you would handle these types of situations in other programming languages because I don't really see how this is specifically a Python issue, regardless of whether one wants to argue None is Null or not Null exactly.

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

TedKowal — Thu, 05 May 2016 17:33:38 GMT

Dan you just made my day!

I am a LRS shop and NaN will be an issue when Python incorporates dynamic seg and measures.... nice that I have that to look forward to.

Thankfully I wrote all my LRS code in VB.Net/ESRI SDK which will be around for awhile. I have read a few interesting papers about the possible redesign of VB, on which will incorporate and built around the HTML5 engine. If this comes to be, it will be a game changer and breath new life into vb script again!

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

RichardFairhurst — Thu, 05 May 2016 17:42:54 GMT

I completely agree with Joshua that only real Null values should be found by Python, since only real Null values will be found by SQL if I write an expression that uses IS NULL for any of the databases I use. Whitespace and control characters are not Null and will not be selected using IS NULL with standard implementations of SQL. Python follows the SQL standard. Access queries might return results that are more similar to the VB implementation, but Access is in no way a reliable reference for established database "standards" recognized by professionals.

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

DanPatterson_Retired — Thu, 05 May 2016 17:52:26 GMT

And to conclude.

There is only one object in python that is None, it is

>>> type(None)

There are, however many null objects, in the mathematical sense (ie lists, sets, dictionaries, collections any container)

Then there are objects that are not, but their are not equal in their none-ness (ie np.NaN)

Their truth equality in testing is another issue... the idea that None of this matters is False and this is True

And I again, will leave geometry out of this... of which I have posted several times before

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

DanPatterson_Retired — Thu, 05 May 2016 17:54:11 GMT

Kevin.... we apologize if None of this makes sense.. or you just don't care

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

DavidWasserman — Thu, 05 May 2016 18:32:14 GMT

Only thing I might add is this leverages Falsy behavior in python. 2.3.1 Truth Value Testing

Something to keep in mind is that 0 can also be Falsy along with a few other values (empty strings etc).
Using

if value: (falsy)

is None

is not None

or even

isintance() built in is what I see over and over again. 😃

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

DanPatterson_Retired — Thu, 05 May 2016 18:38:41 GMT

Yes confusing isn't it... you have to know what an object or what it belongs to... a common mistake in numpy

>>> a = np.NaN

>>> isinstance(a,np.NaN)

Traceback (most recent call last):

File "<string>", line 1, in <module>

TypeError: isinstance() arg 2 must be a class, type, or tuple of classes and types

>>> type(np.NaN)

because you can't use zero... and the list goes on

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

TedKowal — Thu, 05 May 2016 18:54:13 GMT

Joshua,

There are issues with nulls in all languages even my beloved vb and also with both the Oracle and MS SQL server -- so Python following the SQL standard is not 100% true either because each deals with nullness differently. So it is not really any specific language problem. My issue with Python's and Null is mostly personal. Python reminds me of the Perl scripting language (Which I really hated). I spend more time with python trying to figure out why an apparent record that both MSSQL ,ACCESS and EXCEL says its null but fails when testing in Python. In the end I use VB or other language scripts for null testing because I find the results both consistent and repeatable across time. VB, MS access, MS SQL treat Nulls slightly differently but in those cases I found I could handle the differences with little to no effort. VB distinguishes between space and nothing. Whereas MS Access does not except when you use the built in function as well as MS SQL. Oracle has more of a java/javascript approach to Null. But Python is very indirect and obscure when dealing with null within the language context (Probably makes Python much more flexible in the long run and can easily test all variants of nullness). I am probably not yet knowledgeable in Python to adequately determine which of the many variants I should test for.

I think the problem is with the definition of what we consider is NULL. In my definition an empty string is not null, however nothing and null are same. Some other engineers I know disagree with my definition stating that nothing is also not null ... to them NULL is the absence of Nothing (that I cannot put my head around). Other folks will say that the < 31 ASCII Codes are nothing because they are artifacts from buffered cut and paste operation.....

My ideal language tells me if something is null adhering to my expectation of nullness consistently. Python does not do this yet for me. My custom Python isNull function is constantly growing/being modified ... trying to capture and test for my definition of a null value.

Did a little isNull test using different Languages and DB's for a test record from sqlserver that had a test field containing an ascii <NUL> and <TAB> char

vb6 - isNull -- False

vb.net isNull -- False / DB Context isNull -- true (go figure)

python (test for none) -- False

MS Access -- True

MSSQL -- True

Oracle -- False

c # -- False and DB Context isNull -- true (same as VB.net)

Re: I am trying to calculate a field where i want to exclude any null values. For example. Field calulate field X with any value that is not null from field Y. I have been messing with python, but just can not seem to get this to run properly.

DavidWasserman — Thu, 05 May 2016 19:13:59 GMT

Good to know about np.NaNs for that. I have been running into issues with that at work recently. NP methods for scrubbing NaNs seem best, but I have found masked arrays to be annoying in practice. Pandas seems to handle it a little better on the surface.

Do you have any good articles on np.NaN types specifically Dan?