topic Re: Merge Address Fields into one... in Python Questions

Merge Address Fields into one...

TheodoreF — Mon, 11 Dec 2017 17:33:54 GMT

Hello.

I have a large (20+ million) address database point FC in FGDB format. Each address record is split into multiple fields like 'organisation', 'building name', 'building number', 'post town', 'post code' etc. Not every field is populated with data, there are many null values (not every address has an 'organisation' name for example).

I've created a new text field called 'Full_Addr' which will merge all the address fields into one. So far I used this VB script in field calculator:

Full_Addr=

[OrganisationName]& ", " & [BuildingName]& ", " & [BuildingNumber]& ", " & [Thoroughfare]& ", " & [PostTown]& ", " & [Postcode]

However this results in entries like:

, , 1, Example Street, Example Town, AB1 2CD

Example Organisation, , , Example Street, Example Town, AB1 2CD

Is there a smarter, more efficient way of creating a merged address field without the double, triple or quadruple ", "?

Re: Merge Address Fields into one...

DanPatterson_Retired — Sat, 11 Dec 2021 10:11:54 GMT

Actually, you can emulate the following, by simply collecting the field values into a list and assuming you are working in a geodatabase, None should be the equivalent of <Null>

Worth a look... the key is in the ", ".join bit and the test for null

BTW this is python... don't know if vb* has a equivalent, but switch to the python parser to use this idea

a = ['a', 'b', None, 'c', 'd']

", ".join([i for i in a if i is not None])

'a, b, c, d'‍‍‍‍‍

Re: Merge Address Fields into one...

BillFox — Mon, 11 Dec 2017 18:30:24 GMT

I have not tried it but this might do what you need: System Toolboxes > Geocoding Tools > Standardize Addresses

Re: Merge Address Fields into one...

BruceHarold — Mon, 11 Dec 2017 22:13:16 GMT

Dan's suggestion is valid, to expand on it you could use Calculate Field to do the concatenation, but another simple way is with the Field Map in Feature Class to Feature Class, add a new output field with Join merge rule and space or comma join value then add input fields to the new output field.

Re: Merge Address Fields into one...

TedKowal — Tue, 12 Dec 2017 15:28:32 GMT

In the field calculator VB script does not have any more efficient way. In VB language ... like .net -- yes there are other ways. Testing for Null's in VB is by using the "is NULL" test. However in my experience with addresses I use an old fashion test and test for one blank space by doing something like this ... if a & "" = "" then do something. In vb if a null is concatenated with a space then the value will be a space.

I am an old fashion VB'r but I would highly recommend doing this in Python there is less and less support for VB. Already ESRI has downgraded from VBA to VBScript in ArcMap.

Re: Merge Address Fields into one...

TheodoreF — Wed, 13 Dec 2017 09:34:06 GMT

thanks for all the help. I'll give your suggestions a whirl today!

Theo

Re: Merge Address Fields into one...

TheodoreF — Wed, 13 Dec 2017 09:50:37 GMT

This may sound silly (I'm still learning), but are 'a' , 'b , 'c' and 'd' substitutes for my field names?

Since I posted my question, I'm actually ommitting OrganisationName and PostTown from my final output, so now with less variables it might be more simple.

Re: Merge Address Fields into one...

DanPatterson_Retired — Wed, 13 Dec 2017 10:06:54 GMT

Yes, the a, b, c, d were just placeholders for the field names (with appropriate pre- and post markers ie ! for python).

To ensure that your environment gets the correct fieldname surround, it is best to set the parser used first, then physically select the fieldname from the available fields rather than type them in full. If there is a chance that there are going to be numeric values involved, you can cast them to string. For example, for a simple 2 field concatenation with a check I used this example to do the change to string since X_min was numeric... I didn't want anything to be done to the record if 'i' was a None value in either field. Since the result was going to a string field, I just made sure that the values were changed to string. I could have written a function for it... but I made the list ( [ !file_part!, !X_min! ] ) within the test rather than pre-constructing the list...

Just do what is faster especially if you don't need to reuse a function

", ".join([str(i) for i in [!file_part!, !X_min!] if i is not None])

Re: Merge Address Fields into one...

DanPatterson_Retired — Wed, 13 Dec 2017 10:09:50 GMT

python parser... in Pro... join the contents in 2 fields unless one of them contains None. Since we might be combining test and string, just convert to string first. Lastly, join the 2 with a comma-space separator

", ".join([str(i) for i in [!file_part!,!X_min!] if i is not None])‍