If field X <> Null
then field calculate field Y with field X.
(using 10.4)
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 >>>
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.
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"
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.
VB
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....
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.
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...)
or
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
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
Although Python Conditional Expressions have their limits, they can be used in this and similar cases to avoid using a code block:
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.
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!
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
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.
Kevin.... we apologize if None of this makes sense.. or you just don't care
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. 😃
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)
<type 'float'>
because you can't use zero... and the list goes on
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?
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
I'm having problems getting this code to work in Field Calculator (as part of model). To try and make the background short: I have a model where I use a series of Select by Attribute then Calculate Field to score features on the value in the first field. It is being adapted for a webmap using Server, and apparently it can't use layers (so Jayanta's suggestion below won't help). I'm now using a series of Select, Calculate Field (in the output from Select), Join Field back to the original feature class, then another Calculate Field in the original feature class based on the value in the similar field in the output from Select.
Because there are multiple iterations of this sequence, I need to structure the last Calculate Field so that when the value is NULL/None, it doesn't overwrite what's been written in there before. I tried !field_x! if !field_x! is not None but I get the following error:
ERROR 000539: SyntaxError: unexpected EOF while parsing (<expression>, line 1)Failed to execute (Calculate Field (2)).
I tried putting if !field_x! is not None in the code block, but I get the following warning:
ERROR 000989: Python syntax error: Parsing error
Indentation error: unexpected indent (line 1)
Any help you can offer is appreciated. Thanks a lot!
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
Deb,
The ! delimeter can only be used in the main Calculate Field expression, the value of the field for that row is subsituted in. So Joshua Bixby's approach would be to put this in the expression field parameter, not the code block. (Note this fancy but useful construct was a late (2.5) addition to Python: the ternary if.)
!Field_Y! <SPAN class="keyword token">if</SPAN> !Field_X! <SPAN class="keyword token">is</SPAN> None <SPAN class="keyword token">else</SPAN> !Field_X!<SPAN class="line-numbers-rows"><SPAN></SPAN></SPAN>
Another approach to consider is to use Model Builder's Calculate Value tool and write a function for the code block that uses an update cursor. This is a desirable method when the 'if then' logic gets more complex. A limitation of this is that update cursors don't work on tables with an active join, but since you're using the Join Field tool (which does permanent joins by copying data across, not placing a join on the table) an update cursor would work for you.
<SPAN class="comment token"># Calculate Value</SPAN> <SPAN class="comment token"># Expression</SPAN> update<SPAN class="punctuation token">(</SPAN>r<SPAN class="string token">"%input features%"</SPAN><SPAN class="punctuation token">,</SPAN> !Field_X!<SPAN class="punctuation token">,</SPAN> !Field_Y!<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Code Block</SPAN> <SPAN class="keyword token">def</SPAN> <SPAN class="token function">update</SPAN><SPAN class="punctuation token">(</SPAN>ds<SPAN class="punctuation token">,</SPAN> fx<SPAN class="punctuation token">,</SPAN> fy<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>ds<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>fx<SPAN class="punctuation token">,</SPAN> fy<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> rows<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">if</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">==</SPAN> None<SPAN class="punctuation token">:</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> rows<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">return</SPAN> ds <SPAN class="comment token"># Data Type: Feature Class (or feature layer, table, etc)</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
Thanks a lot! I haven't really worked much with more complex calculations, so this is good information for me. I updated my expression to include the else !Field2! at the end, and it worked. I had tried finding a way to make it pass the features where the Field1 value was null, but obviously that wasn't the right solution.
However, just before reading your message, I also got it to work by using the attached code block and expression (not as elegant though).
Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
