topic Re: Trim strings at specific point using Field Calculator in Data Management Questions

Trim strings at specific point using Field Calculator

DerrickFrese2 — Tue, 09 Dec 2014 19:08:29 GMT

I have a feature class which contains 40,000 mailing addresses. Each address contains the street address, city, state and zipcode separated by spaces.

Example 1: 123 Northwest Johnson St Cleveland Ohio 12345

Example 2: PO Box 3 Pine Springs Ohio 12345

I want to pull out just the street addresses. How do I say: trim off the string starting at the 3rd or 4th to last space? Thanks. Any help would be appreciated. I'm trying combinations of split, trim, etc. but can't get it right.

Re: Trim strings at specific point using Field Calculator

XanderBakker — Tue, 09 Dec 2014 23:07:24 GMT

Below an example of getting the address without the part from the last space (using the Python parser):

Formula (with "Address" being the name of the field that contains the address string):

!Address![:!Address!.rfind(' ')]

The result:

If you can detect the part you want to exclude, it is easy. However if you want to obtain "123 Northwest" or "PO Box 3", there is no easy way to detect the parts you need or those you want to exclude.

Kind regards, Xander

Re: Trim strings at specific point using Field Calculator

ChrisDonohue__GISP — Wed, 10 Dec 2014 19:24:04 GMT

If you haven't already tried it, you might be able to accomplish part of what you want with VB Script and the Left, Mid, and Right string functions. Whether this will be usable will depend on how your data is arranged; though from what you have depicted in your examples it would at least allow you to strip the zip code off.

Check out the ESRI help, VBScript string functions section (part way down the page):

Calculate Field Examples

ArcGIS Help 10.1

As to culling out the City, that looks a bit trickier, given the variability in name-length of a city and the presence of multiple words for some cities. Off the wall idea - somehow tie in a search to a list of known cities that your data would include to then identify what to remove?

Chris Donohue, GISP

Re: Trim strings at specific point using Field Calculator

derekthompson1 — Wed, 10 Dec 2014 20:00:23 GMT

Try This

Mid( [Address],1,13 ) Get 123 Northwest

Re: Trim strings at specific point using Field Calculator

JoeBorgione — Wed, 10 Dec 2014 20:01:34 GMT

The problem is the variability in contents. Even if you pop() the last two elements (Zip and State) you are still left with a variable length list.

You might want to try filtering the data through a query or queries. One such query might be

[ADDRESS_FIELD] not like 'PO BOX %'. Maybe look for street types as well. These might give you an idea of what you need to do to tease out the data you're after. You need to get acquainted with the data to be able to do such filtering. After all that you'll still have a ton of error trapping the figure out. Sorry, not a lot of help...

Re: Trim strings at specific point using Field Calculator

DanPatterson_Retired — Sat, 11 Dec 2021 12:29:47 GMT

Xander and Joe are on the right track...
Examine the address from the perspective of non-standard lengths brought about the uncertain number of spaces separating elements in the address.

>>> #---------------------------------------------------
>>> # parsing non-standard address...problem examination
>>>
>>> address = "123 Northwest Johnson St Cleveland Ohio 12345"
>>> bits = address.split(" ")          # split it into its components
>>> bits
['123', 'Northwest', 'Johnson', 'St', 'Cleveland', 'Ohio', '12345']
>>> for fiddly in range(len(bits)):    # now lets examine the fiddly bits
... print bits[:-fiddly], "step", fiddly
...
[] step 0
['123', 'Northwest', 'Johnson', 'St', 'Cleveland', 'Ohio'] step 1
['123', 'Northwest', 'Johnson', 'St', 'Cleveland'] step 2
['123', 'Northwest', 'Johnson', 'St'] step 3
['123', 'Northwest', 'Johnson'] step 4
['123', 'Northwest'] step 5
['123'] step 6
>>>
>>> # Now for problem address 2
>>> address = "PO Box 3 Pine Springs Ohio 12345"
>>> bits = address.split(" ")          # split it into its components
>>> bits
['PO', 'Box', '3', 'Pine', 'Springs', 'Ohio', '12345']
>>> for fiddly in range(len(bits)):    # now lets examine the fiddly bits
... print bits[:-fiddly], "step", fiddly
...
[] step 0
['PO', 'Box', '3', 'Pine', 'Springs', 'Ohio'] step 1
['PO', 'Box', '3', 'Pine', 'Springs'] step 2
['PO', 'Box', '3', 'Pine'] step 3
['PO', 'Box', '3'] step 4
['PO', 'Box'] step 5
['PO'] step 6
>>>
>>> # In address 1 it took until step 3 to get rid of everything
>>> # up to the city level.  in address 2 this was completed by step 2...
>>> # Conclusion?  Unless the address contain the same number of elements
>>> # it is not possible to parse data in one pass
>>>

A good reason to parse during data collection and entry.

Do note... that a screw will be thrown into the works when you start mixing state names which are multipart as well...consider

address = "123 Northwest Johnson St South New York New York 12345"    # Now What!!!

Re: Trim strings at specific point using Field Calculator

ChrisDonohue__GISP — Wed, 10 Dec 2014 20:55:19 GMT

A different idea - how about this as a possible approach - find an existing dataset of already-parsed addresses that include all the ones you have, then match yours to the parsed ones. Then output the parsed ones and pare down from there. I don't know exactly where you would find the parsed dataset(s), but given the proliferation of address information, geocoding, and the like, I suspect it could be feasible.

Can anyone chime in on where specific sources of such already-parsed datasets could be found?

Also, Derek, to help with this - what States are your addresses in?

Chris Donohue, GISP

[Edit: fixed some typos]

Re: Trim strings at specific point using Field Calculator

derekthompson1 — Wed, 10 Dec 2014 21:07:53 GMT

Cleveland, Ohio

Re: Trim strings at specific point using Field Calculator

DanPatterson_Retired — Wed, 10 Dec 2014 21:31:06 GMT

Have you tried to geocode a sample?
Removind Cleveland Ohio ZIP is going to narrow it down but you are still left with the problem of

'123', 'Northwest', 'Johnson', 'St', ..... being 4 positions in the list

'PO', 'Box', '3', 'Pine', 'Springs', ...... and, this being 5

Re: Trim strings at specific point using Field Calculator

DerrickFrese2 — Wed, 10 Dec 2014 22:00:43 GMT

Thanks for all the input and help. I was able to use 3 iterations of the !Address![:!Address!.rfind(' ')] formula to slice off the zip, state, and city. This left me with just the street address, which is all I needed. The tough part was that there were just spaces separating everything so it took some finagling to get the city's sorted out but I finally got it. Leaving a comma between the address, city, state, and zip would have made it much easier.

Re: Trim strings at specific point using Field Calculator

ChrisDonohue__GISP — Wed, 10 Dec 2014 22:05:19 GMT

Dan has a point - maybe try geocoding them. I'm in no ways an expert on Geocoding, but I know there are quite a few geocoding services out there. While you may not necessarily be interested in the coordinate location of your address, the address result may very well be parsed for you as part of the process.

Here's a site I remember people mentioning in a post here on GeoNet several weeks ago.

Texas A&M Geoservices

List of Online Geocoding Systems

There may also be local geocoding services publicly available from government entities in your area. Maybe try the city and county governments of your area of interest.

Chris Donohue, GISP

Re: Trim strings at specific point using Field Calculator

TedKowal — Sat, 11 Dec 2021 12:29:50 GMT

I just saw this post a little late ... but I had to do some cleaning of a text address field from another agency... I ran across a script that would parse many of the US type of addressing using pyparsing

-- I do not know where I got the script or to whom to give credit to but I have adapted the script for use in the field calculator and has proven to be an excellent workhorse for me....

You will of course have to adapt it for you own specific use.... the example below parses the streetname from the address..... you can retrieve also the house number, streetnumber, streettype, PO Box etc...

from pyparsing import *
def ParseAddress(address):
# define number as a set of words
    units = oneOf("Zero One Two Three Four Five Six Seven Eight Nine Ten"
              "Eleven Twelve Thirteen Fourteen Fifteen Sixteen Seventeen Eighteen Nineteen",
              caseless=True)
    tens = oneOf("Ten Twenty Thirty Forty Fourty Fifty Sixty Seventy Eighty Ninety",caseless=True)
    hundred = CaselessLiteral("Hundred")
    thousand = CaselessLiteral("Thousand")
    OPT_DASH = Optional("-")
    numberword = ((( units + OPT_DASH + Optional(thousand) + OPT_DASH + 
                      Optional(units + OPT_DASH + hundred) + OPT_DASH + 
                      Optional(tens)) ^ tens ) 
                   + OPT_DASH + Optional(units) )


    # number can be any of the forms 123, 21B, 222-A or 23 1/2
    housenumber = originalTextFor( numberword | Combine(Word(nums) + 
                        Optional(OPT_DASH + oneOf(list(alphas))+FollowedBy(White()))) + 
                        Optional(OPT_DASH + "1/2")
                        )
    numberSuffix = oneOf("st th nd rd").setName("numberSuffix")
    streetnumber = originalTextFor( Word(nums) + 
                     Optional(OPT_DASH + "1/2") +
                     Optional(numberSuffix) )


    # just a basic word of alpha characters, Maple, Main, etc.
    name = ~numberSuffix + Word(alphas)


    # types of streets - extend as desired
    type_ = Combine( MatchFirst(map(Keyword,"ST BLVD LN RD AVE PASS "
                            "TRL PATH PSGE WAY LANE "
                            "CIR DR PKWY CT SQ "
                            "LP TER TERR PL".split())) + Optional(".").suppress())


    # street name 
    nsew = Combine(oneOf("N S E W North South East West NW NE SW SE") + Optional("."))
    streetName = (Combine( Optional(nsew) + streetnumber + 
                            Optional("1/2") + 
                            Optional(numberSuffix), joinString=" ", adjacent=False )
                    ^ Combine(~numberSuffix + OneOrMore(~type_ + Combine(Word(alphas) + Optional("."))), joinString=" ", adjacent=False) 
                    ^ Combine("Avenue" + Word(alphas), joinString=" ", adjacent=False)).setName("streetName")


    # PO Box handling
    acronym = lambda s : Regex(r"\.?\s*".join(s)+r"\.?")
    poBoxRef = ((acronym("PO") | acronym("APO") | acronym("AFP")) + 
                 Optional(CaselessLiteral("BOX"))) + Word(alphanums)("boxnumber")


    # basic street address
    streetReference = streetName.setResultsName("name") + Optional(type_).setResultsName("type")
    direct = housenumber.setResultsName("number") + streetReference
    intersection = ( streetReference.setResultsName("crossStreet") + 
                     ( '@' | Keyword("and",caseless=True)) +
                     streetReference.setResultsName("street") )
    streetAddress = ( poBoxRef("street")
                      ^ direct.setResultsName("street")
                      ^ streetReference.setResultsName("street")
                      ^ intersection )


    # how to add Apt, Suite, etc.
    suiteRef = (
                oneOf("Suite Ste Apt Apartment Room Rm #", caseless=True) + 
                Optional(".") + 
                Word(alphanums+'-')("suitenumber"))
    streetAddress = streetAddress + Optional(Suppress(',') + suiteRef("suite"))


    return streetAddress.parseString(address, parseAll=True)




__esri_field_calculator_splitter__
ParseAddress( !PHY_ADDR1!).street.name