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?

[MDB_GIS]

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

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>?

[JayantaPoddar · Accepted solution]

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

[MDB_GIS]

This worked flawlessly. Thank you so much!

[ArizonaGIS]

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?

[RhettZufelt]

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_

[MDB_GIS]

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

[RhettZufelt]

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_

[ArizonaGIS]

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])

[RhettZufelt]

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_

[KimHazelwood]

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!!!