Concatenating Strings with Field Calculator and Python - dealing with NULLS

11510
20
Jump to solution
11-06-2015 11:08 AM
ChrisDonohue__GISP
MVP Frequent Contributor

I have some addressing data that I would like to update and one of the tasks is to concatenate a new full street address field [ADDR1] by combining several of the component fields.  I know how to do this in VBScript, but figured this would be a good example to work out in Python, as I don't use Python often and have alot to learn.  My programming background is FORTRAN and BASIC, and in the years since those were popular hack and slash adapting code for AML/Avenue/VBA/VB.Net - so assume I know little about Python.

What is desired (psuedo-logic):

ADDR1 = STRNUM + STRNUMSUF + STRPREDIR + STRNAME + STRTYPE + STRSUFDIR

I'm using ArcGIS 10.2.1 and the data shown here is test data in a File Geodatabase feature class.

After poking around various Help articles, it appears that in Python the .format method in would work.  However, when I run it in Field Calculator the result looks good except "None" gets concatenated as text if the original field value was NULL (this issue doesn't come up with VBScript).  I'd like any values of NULL to instead just be blank - i.e. ignore them entirely.  For example, for the first record the desired outcome in ADDR1 is "224 D N CHURCH ST" not "224 D N CHURCH ST None".

Concatenation Python NULLS become None.png

One of the Python articles I found mentions the NULL issue:

Dealing with Null Values.jpg

Source:  Concatenating field values using Calculate Field | ArcGIS Blog

Is there a different way to do the concatenation in Python to achieve the desired outcome?  I'm trying to wrap my head around how Python approaches this, particularly in regards to using it in ArcMap in Field Calculator. 

  • I noticed some articles suggested casting all the fields to string to resolve the NULLS.  However, I haven't had any luck adding in str() in with the fields in the format statement shown.  It would just error out.  Would this need to be a separate line to do the casting?
  • Would a better approach be to use the Codeblock to run some sort of process to convert NULLS to blank before running the format method?  For example, adapt a process similar to this?

FixNull.jpg

Source:  arcgis desktop - Calculate Field tool to calculate on null fields - Geographic Information Systems S...

Lastly, can you recommend a good guide for beginners that explains how to use Python for these sort of issues?

Thanks,

Chris Donohue, GISP

1 Solution

Accepted Solutions
DanPatterson_Retired
MVP Esteemed Contributor

I figured out how to make tables with nulls...not useful for me, but if you have them, here is an example

Field calculator expression:

" ".join([str(i) for i in [ !prefix_txt!, !number_int!, !main_txt!, !numb_dble!] if i])

The operative part is if i  ])

table_nulls.png

View solution in original post

20 Replies
DanPatterson_Retired
MVP Esteemed Contributor

I don't have nulls in my fields, but you can glean some ideas from the options available below

>>> a = 12345
>>> b = None
>>> c = "some text"
>>> d = ""
>>> e = "more"
>>> frmt = "{} {} {} {}".format(a,b,c,d,e)
>>> frmt2 = "{!s:} {!s:} {!s:} {!s:}".format(a,b,c,d,e)
>>> frmt3 = "{!r:} {!r:} {!r:} {!r:}".format(a,b,c,d,e)
>>> fld = " ".join([str(i) for i in [a,b,c,d,e] if i])
>>>
>>> frmt
'12345 None some text '
>>> frmt2
'12345 None some text '
>>> frmt3
"12345 None 'some text' ''"
>>> fld
'12345 some text more'
>>>

note the last option is the only one that deals with None explicitly, so if your null values are equivalent to Python None, then this should work.

PS  you might have to do the string trimming thing is to get rid of preceeding or following spaces

DanPatterson_Retired
MVP Esteemed Contributor

I figured out how to make tables with nulls...not useful for me, but if you have them, here is an example

Field calculator expression:

" ".join([str(i) for i in [ !prefix_txt!, !number_int!, !main_txt!, !numb_dble!] if i])

The operative part is if i  ])

table_nulls.png

DarrenWiens2
MVP Honored Contributor

The operative part is if i

In short:

if i: # checks if i 'exists'
  do something
0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

It can also be interpreted as if i IS not just exists. I posted a blog on nothingness a while ago

0 Kudos
DarrenWiens2
MVP Honored Contributor

I can't find your nothing post in your prolific blog. How does IS differ from EXISTS?

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

None isn't...nor is 0 or 1 ... more explorations into geometry

Before I forget ... # 5 ... That empty feeling...

There are links within those and some comments and a blog post by Joshua Bixby​ which you may find interesting to. 

Also the concepts of "exists" and "is" come out of philosophy and can be exemplified in studies of symbolic logic.  Truth values complicate yet supplement these... You might be familiar with phrase "I think therefore I am" and the debate that still continues to this day.  Sooo philosophy meets geomatics perhaps

0 Kudos
ChrisDonohue__GISP
MVP Frequent Contributor

I forget which comedian came up with this adaption of a famous quote on existence:

"I think, therefore I am...... I think?"  (unsure)   

Chris Donohue, GISP

ChrisDonohue__GISP
MVP Frequent Contributor

Ah, so that is why the result was different. If i sees Null as non-existing, so no join (and therefore no substitution text of 'None').  Makes sense.

Chris Donohue, GISP

0 Kudos
DarrenWiens2
MVP Honored Contributor

Dan's one-line example actually has a whole bunch of things going on.

fld = " ".join([str(i) for i in [a,b,c,d,e] if i]) 

... can be rewritten:

myList = []
for i in [a,b,c,d,e]:
  if i:
    myList.append(str(i))
" ".join(myList)

Not sure if that helps or not, just a different way to read it.