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 · ‎05-04-2016

If field X <> Null

then field calculate field Y with field X.

(using 10.4)

DanPatterson_Retired · ‎05-05-2016

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

TedKowal · ‎05-05-2016

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)

JoshuaBixby · ‎05-09-2016

I fear your ideal language is one you will likely have to create yourself. Everyone is entitled to have his/her own views of the world, but not all views are commonly held. In this case your idea of null-ness is quite uncommon, which is fine in and of itself, but you should expect to run into plenty of frustrations with most (if not all) computer languages and even possibly some areas of mathematics.

Before getting back to Null, I think saying that ASCII codes < 31 are "nothing because they are artifacts from buffered cut and paste operation" doesn't give enough credit to those characters. Sure, control characters aren't printable/viewable, but I can't imagine trying to work with computers without control characters. Typical business users that only enter text into e-mail, word processing, and other business productivity applications might not understand control characters, or even know that such things exist, but it doesn't mean they are nothing.

Regarding nulls, an ASCII null character (NUL or \0) is null within the domain or context of the ASCII character set, but it is still a character. Type systems can define their own nullable type, but the ASCII null character is not a universal null object/type. It is possible there is a programming language that has adopted the ASCII null character as its own null representation, but that isn't the case for any of the languages that I interact with.

Regarding SQL Server and MS Access, I get the opposite results you do, i.e., neither returns a field with only an ASCII null when the WHERE condition includes IS NULL. Looking at a SQL Server example:

--  SELECT RECORDS FROM CTE WHERE f0 is null
WITH cte AS (
    SELECT * FROM (
        VALUES
            (1, NULL),     -- SQL SERVER NULL
            (2, CHAR(0)),  -- ASCII NULL
            (3, CHAR(9)),  -- ASCII TAB
            (4, CHAR(13)), -- ASCII CR
            (5, CHAR(33))  -- ASCII "!"
        ) AS t (id, f0)
    )
SELECT * from cte where f0 is null;



id          f0

----------- ----

1           NULL

(1 row(s) affected)

TedKowal · ‎05-10-2016

Joshua,

I do get the same results running the isnull script in SQL server as you, however when I import a record from excel with embedded codes, the isnull function returned true which differs from you singular controlled value list.

In any event, I whole-heartedly agree with your statements, the other definitions I stated were not necessarily my own but view points of folks that supply me with data. I do not infer that the ASCII null is a universal standard of nullness, but gave it as one example of where the simple is none python test fails (for me!!!!! ). I believe my issue is not one of testing for null, but in data standards in which I am struggling to employ in-house as well as data received from the outside (lots of resistance in my case). I won't even go into uni-text,ut8 and binary strings/null issues...

I do apology for letting out some frustrations and adding to the confusion to the poor soul who posed a simple question and the many solutions provided in the text of this chain; I am sure 99.9% would solve. I would love to do away with null values, but that is not a thought until I can get a handle on data standards. 99% of my job is not GIS/Engineering but Data Cleansing. So I can perform the other 1% with accuracy and consistancy.

DanPatterson_Retired · ‎05-05-2016

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

curtvprice · ‎05-21-2016

I thought this discussion was so useful I copied it to a document and did some editorial formatting on it. Also added keywords to make it visible to those who have issues with nothing.

Much Ado About Nothing

MicahBabinski · ‎08-12-2017

Heya Kevin,

How about an update cursor? It's a nice alternative to field calculator and is what I would go with in this case. You could use field X and field Y in the field names (remember to put them in a list or tuple) and then conditionally update using the logic you stated in your question.

UpdateCursor—Data Access module | ArcGIS Desktop

Hope this helps.

Micah