String split for street addresses

Question

I have 856k addresses of pattern:

660 1st Ave   New York, NY 10016

(with 3 spaces between Ave and New York)

I need to parse and store parts as:

Street, City, State, Zip

I tried to split through regex but it was super slow, so I went traditional:

def splitAddress(address):
    split = '   '       
    ad = address.strip()

    try:
       if(split in ad):
           addr = ad.split(split)            
           if(len(addr)==2):
                addr1 = addr[1].strip().split(",")
                if len(addr1)==2:
                      addr2 = addr1[1].strip().split(" ")                         

                      if len(addr2)==2:                        
                           return addr[0], addr1[0], addr2[0], addr2[1]
                      else:
                           return "0","0","0","0"                
                else:
                     return "0","0","0","0"                
           else:
               return "0","0","0","0"                
       else:            
           return "0","0","0","0"
    except ValueError as e:
          print(addr[0], addr1[0], addr2[0], addr2[1])
          print(e)

I then got the parts as:

df['STREET'],df['CITY'], df['STATE'], df['ZIP'] = zip(*df['ADDRESS'].map(splitAddress))

But this is turning out to be pretty slow (it takes 18 mins for 856k addresses).

Answer 1

How did you try implementing your regex?

You can take advantage of panda's str methods to vectorize your regex instead of mapping the function over each element of the dataframe.

df = pd.DataFrame(index=np.arange(900000))
df["address"] = "660 1st Ave   New York, NY 10016"

With a dataframe with 900000 addresses,

df.address.str.extract("regex_pattern", expand=True)

will extract your regex from each row of the 'address' column in your dataframe. Something like,

df[["street", "city", "state", "zip"]] = df.address.str.extract('(.+)[ ]{3}(.+)\,[ ]([a-zA-z]{2})[ ]([0-9]{5})',expand=True)

runs over the entire dataframe in:

%timeit 7.2 s ± 81.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Basically, you should look for any opportunity to take advantage of panda's vectorized optimizations (string operations, datetime operations, masks, etc.) as described in the docs: https://pandas.pydata.org/pandas-docs/stable/basics.html#vectorized-string-methods

If at any point you are trying to apply a function to each element in the dataframe/series, stop and check to see if there is already a built-in method that implements what you want.