8

I have to do some massaging of our parcel data to make it usable by a program in sheriff helicopters. The program requires one of the following address formats within the fields:

enter image description here

Our addresses are currently in one field: ex: 1234 W Main St.

Is there a way to automate the splitting of the fields into either of these desired formats?

I can imagine the two field format would be easier by just calling for a split after the numbers, but could also cause a problem for streets such as 1st Ave, etc.

Improve this question
4
  • The "less desirable" format could be fairly easily achieved by splitting after the first space. Splitting the rest becomes a bit trickier, since you may or may not have a direction prefix and the street name may or may not have spaces in it, etc.
    – Erica
    Jul 23, 2014 at 14:44
  • Are ALL your streetname's formatted the same way? I would guess not which would make parsing out the PreDIR tricky
    – GISHuman
    Jul 23, 2014 at 14:48
  • No. Some have PREDIR and some don't. Would this be a good place to create some sort of if/then statement into a script? If SE, SW, NE, NE, etc then populate PREDIR else do nothing?
    – Craig
    Jul 23, 2014 at 14:51
  • Alternatively, in conjunction with my answer, you could parse out all directions as you go, all the numbers and then see what you're left with. It's not pretty or easy.
    – GISHuman
    Jul 23, 2014 at 15:43

4 Answers 4

Reset to default
10

Instead of using multiple RegExes to parse addresses, just use Esri's out of the box tool that is designed for this task, Standardize Addresses. It's available at all license levels and my experience with it has been positive.

Esri image

Improve this answer
3
  • OOB solutions are best, nice (+1)!
    – Aaron ♦
    Jul 23, 2014 at 15:50
  • @Paul Come on, he asked for python! Just kidding, I'll be using this in the future as well, much easier than regex.
    – GISHuman
    Jul 23, 2014 at 15:52
  • @GISKid, haha! I started out trying to parse with regex, and finally came across this tool, which doesn't seem to be all that commonplace. I have no idea why.
    – Paul
    Jul 23, 2014 at 16:02
1

You can achieve this in field calculator using python. This may not be the most elegant but it's a start, assuming the simpliest case (ie. your addresses all look the same). I would first create the additional fields needed. Assuming your column with the full address is called "Address".

For HOUSENO in the field calculator write:

##Return just numbers

import re
def strip_digits(s):
    return re.sub("\D+", "", s)

This can then be called from the calculate box as:

strip_digits(!ADDRESS!)

For your street name:

# Return just the alpha characters


import re
def strip_letters (s):
  return re.sub ("\d",  "", s[1:])

Codeblock:

strip_letters(!ADDRESS!)

For direction assuming it's the first character each time:

#First character in streetname
import re
def strip_dir(s):
  return re.sub("\d", "", s[0])

Codeblock:

strip_dir(!ADDRESS!)

Here is the python resource for re. This 7.1 Case Study: Street Addresses has helped me numerous times with sorting out my street/address database also using python and re modules. This should help you out, from here if you're not getting results you want comment and I can alter my code

Improve this answer
7
  • tried the HOUSENO code, it worked, but it also brought all numerical street name data along with house number data. perhaps for this portion splitting at first space is the way to go.
    – Craig
    Jul 23, 2014 at 15:03
  • Whoops, sorry that's an error in my code
    – GISHuman
    Jul 23, 2014 at 15:05
  • Alright try the new one @Craig
    – GISHuman
    Jul 23, 2014 at 15:15
  • I've tried both HOUSENO and STREETNAME codes. The issue I'm having is, for HOUSENO, its grabbing all numerical data even if the street name is 1234th Street, so I wind up with some records being 1231234 from 123 1234th Street. Similarly, from the STREETNAME code, it is stripping all numerical data, so 1234th Street is returning "th Street"
    – Craig
    Jul 23, 2014 at 15:24
  • Getting regex to work on something as unstandardized as addresses in the U.S. will be a difficult task indeed.
    – Paul
    Jul 23, 2014 at 15:29
0

Like Erica said, your second format is easy. If all your street names were one word, you could check the length of the list after splitting the original field. Length of 3 = no prefix, length of 4 = has a prefix (also assuming SUFTYPE is always populated). This fails when a street is more than one word, such as 'Grand River'. You could check if the second element matches a list of allowed prefixes, and proceed from there. You'd have to test this to see if it worked with your data.

Improve this answer
0

Make sure your input is in string format and simply use the string.split() function which will split on white space and return a list. (ex. "1234 W Main St." would return ["1234","W","Main","St."])

From there I would use some conditional statements to check your data depending on how consistent your input is. If the only inconsistency in your data is that sometimes there is a predir and other times there isn't, simply do a len check on the returned list to see if it has a length of either 3 or 4 (or > 4 if your street name has spaces in it).

if len(splitList) == 4:

    HouseNo = splitList[0]
    PreDir = splitList[1]
    StreetName = splitList[2]
    SufType = splitList[3]

elif len(splitList) ==3:

    HouseNo = splitList[0]
    PreDir = ""
    StreetName = splitList[1]
    SufType = splitList[2]
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.