0

I have a data table with a column 'STREET_TYPE' which are street types (RD, LN, ST, AVE etc.). I want to make a custom tool in ArcMap's toolbox that will expand these abbreviations to their full names using a dictionary. It needs to be able look up the values while ignoring the case sensitivity from the dictionary. In other words, it shouldn't matter if it's upper case or not. I want the end result to be in title case.

Around the same time I want to clean up the data by stripping any whitespaces. Including stripping the empty fields.

How can I do this?

Street_type column sample

Here is the best I got so far.

import arcpy

# set parameters
f1 = arcpy.GetParameterAsText(0)
f2 = arcpy.GetParameterAsText(1)

# variables
fc = f1
field = f2

    d = { 
    'aly' : 'Alley',
    'ave' : 'Avenue',
    'blv' : 'Boulevard',
    'blvd' : 'Boulevard',
    'cir' : 'Circle',
    'ct' : 'Court',
    'cv' : 'Cove',
    'cyn' : 'Canyon',
    'dr' : 'Drive',
    'expy' : 'Expressway',
    'hwy' : 'Highway',
    'ln' : 'Lane',
    'pkwy' : 'Parkway',
    'pl' : 'Place',
    'pt' : 'Point',
    'rd' : 'Road',
    'sq' : 'Square',
    'st' : 'Street',
    'ter' : 'Terrace',
    'tr' : 'Trail',
    'trl' : 'Trail',
    'wy' : 'Way'
    }

with arcpy.da.UpdateCursor(fc, field) as cursor:
    for row in cursor:
        if row[0] != None: # searches only non-null fields
            row[0] = d[row[0]].title()
            cursor.updateRow(row)

Edit: I changed the dictionary keys to lower cases. Error I got with this is. \StreetSuffix.py", line 39, in <module> row[0] = d[row[0]].title() KeyError: u'LN'

With row[0] = d[row[0].strip().lower()].title() instead I got this error. \StreetSuffix.py", line 39, in <module> row[0] = d[row[0].strip().lower()].title() KeyError: u'lane' The first row was changed to "Lane" but nothing else

Edit 2: I changed the last part to.

    ...
    'tr' : 'Trail',
    'trl' : 'Trail',
    'wy' : 'Way',
    ' ' : None,
    }

with arcpy.da.UpdateCursor(fc, field) as cursor:
    for row in cursor:
        key = row[0].strip().lower()
        if key and key in d:  #  Check key is non-NULL and is in dictionary
            row[0] = d[key]
            cursor.updateRow(row)

I get this error \StreetSuffix.py", line 38, in <module> for row in cursor: RuntimeError: The row contains a bad value. [STREET_TYPE]

When I close the tool and review the table I get this error and the all table data is gone. error I get

Edit 3: The length is 5 is why I'm probably getting an error. Copy the data to a new column first then run the tool works.

meta

closed as too broad by PolyGeo Sep 18 at 10:28

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

3

1. Make it Case Insensitive

Change your dictionary to use all lower-case keys. (eg, 'dr': 'Drive' ).

Then change your assignment statement to use the lowercase version of the value as the key to your dictionary:

row[0] = d[row[0].lower()].title()

(I would leave out the title() myself, and just make sure that the dictionary values are all correctly title-cased. But that's up to you.)

Alternative 1: Create a case insensitive dictionary subclass as per https://stackoverflow.com/questions/2082152/case-insensitive-dictionary (highest voted answer, NOT the accepted answer!).

Alternative 2: Derive your all-lower-case-keys dictionary dynamically rather than defining the keys that way explicitly: lowerKeysDict = {k.lower(): v for (k, v) in d}

2. Handle Whitespace and Empty Strings

Add the following entry to your dictionary:

'': None

This will cause your dictionary lookup to replace all empty strings with NULLs.

But if you also want to handle other whitespace, you should strip it out before looking up the dictionary like so:

row[0] = d[row[0].strip().lower()].title()

Now it will also replace a value of ' ' (space) or tab, etc will NULLs. It will also clean up any extra whitespace from other values such as 'Dr ' along the way too, causing them to be looked up in the dictionary correctly.

3. Handle any other cases that are NOT in the dictionary

Change your if statement to check if the key is in the dictionary. If not, then ignore it:

if row[0] and row[0].strip().lower() in d:

Result

The last part of the script would then look something like this:

    ...
    ...
    'trl' : 'Trail',
    'wy' : 'Way',
    '' : None,
    }

with arcpy.da.UpdateCursor(fc, field) as cursor:
    for row in cursor:
        key = row[0].strip().lower()
        if key and key in d:  #  Check key is non-NULL and is in dictionary
            row[0] = d[key]
            cursor.updateRow(row)

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