I have a bunch of fields, some populated with data, some not, that I need to concatenate. How can I concatenate while excluding the null values?

2798
10
Jump to solution
03-17-2022 05:16 AM
MatthewBeal
Occasional Contributor III

Here is an example of the fields I need to concatenate:

 

MatthewBeal_0-1647519290685.png

 

In this case only Street Name and Post Type are populated. But in other cases, all of those fields could contain street name components that need to be concatenated. Is there an easy way to concatenate only the fields that are not <Null>?

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
JayantaPoddar
MVP Esteemed Contributor

Something like this should work using Field Calculator.

Expression Type: Python 3

 

" ".join([str(i) for i in [ !Field1!, !Field2!, !Field3!, !Field4!,!Field5!] if i])

 

 

Reference: Solved: Concatenating Strings with Field Calculator and Py... - Esri Community



Think Location

View solution in original post

10 Replies
JayantaPoddar
MVP Esteemed Contributor

Something like this should work using Field Calculator.

Expression Type: Python 3

 

" ".join([str(i) for i in [ !Field1!, !Field2!, !Field3!, !Field4!,!Field5!] if i])

 

 

Reference: Solved: Concatenating Strings with Field Calculator and Py... - Esri Community



Think Location
MatthewBeal
Occasional Contributor III

This worked flawlessly. Thank you so much!

0 Kudos
ArizonaGIS
New Contributor III

I am getting a "WARNING 002858: Certain rows set to NULL due to error while evaluating python expression: AttributeError: 'NoneType' object has no attribute 'join'" on my SDE database connection. I am trying to concatenate address information for a Full Street Name attribute.

 

Any thoughts?

0 Kudos
RhettZufelt
MVP Frequent Contributor

Know this is already answered, but I really like this little python snippet for concatenating all values that are not blank or null.  I use this in cursors all the time.

ConcatVals = ' '.join(filter(None, [!Field1!, !Field2!, !Field3!,!Field4!,!Field5!]))

So, thought I' share.

R_

MatthewBeal
Occasional Contributor III

Awesome! Always happy to have multiple ways to tackle the problem. I have saved this for future reference as well!

0 Kudos
RhettZufelt
MVP Frequent Contributor

what does the python code that is erroring out look like? (and maybe the type of values trying to join)

If using the ConcatVals expression I posted above, do you have the ' '.join in there (which is telling it to use a space in between values when joining)?

From the error, it almost sounds like there is something like variable.join(.....

 

R_

0 Kudos
ArizonaGIS
New Contributor III

This is how I have the code configured within Field Calculator. Only four of the fields have an attribute, and the rest are <Null>.

 

!FullAddr!.join([str(i) for i in [ !Add_Number!, !AddNum_Suf!, !St_PreMod!, !St_PreDir!. !St_PreTyp!, !St_PreSep!, !St_Name!, !St_PosTyp!, !St_PosDir!, !St_PosMod!] if i])

 

0 Kudos
RhettZufelt
MVP Frequent Contributor

This seems to work:

 

FullAddr = " '.join([str(i) for i in [ !Add_Number!, !AddNum_Suf!, !St_PreMod!, !St_PreDir!, !St_PreTyp!, !St_PreSep!, !St_Name!, !St_PosTyp!, !St_PosDir!, !St_PosMod!] if i])

 

But, you need to fix the typo in the field list (that should be a comma between the two fields):

 

, !St_PreDir!. !St_PreTyp!,

 

Then return FullAddr.

 

This is my initial use case for this concatenate script, is to make a Full Street Name field.  But, I use updateCursor (updates 1300 records in less than a second):

 

ufields = ['STRNID', 'DirectPrefix', 'StreetName', 'StreetType', 'DirectSuffix', 'FullStreetName']

with arcpy.da.UpdateCursor(in_SDE_FC, ufields) as cursor:
     for row in cursor:
         row[5] = ' '.join(filter(None, [row[1], row[2], row[3],row[4]]))
         cursor.updateRow(row)

 

 

R_

0 Kudos
KimHazelwood
New Contributor II

I am trying to exclude Nulls in an if statement in Field Calculator

I have attached a picture of the Field Calc and the code

I appreciate any help!!!

0 Kudos