topic Re: Replace Multiple Values in an Attribute Table Field using Field Calculator in Python Questions

Replace Multiple Values in an Attribute Table Field using Field Calculator

RebeccaLeach — Fri, 27 Oct 2017 21:00:55 GMT

I'm new to python and trying to write a python script using the field calculator in ArcMap for a specific field in an attribute table that will replace some of the values in the field but leave the other values as they are within the table. For example: ""fourth" must be changed to "4th" whereas "Neilston" should remain as it is in the field. The field in the table looks like this:

The python script used in the codeblock looks like this:

def streetNUM(field):

if field == "Fourth":

field.replace ("Fourth","4th")
elif field == "Fifth":

field.replace ("Fifth","5th")
else:
return field

And then in the box below the codeblock I have:

streetNUM(!FULLNAME!)

Nothing seems to change and I get an error code saying "Field is not nullable [FULLNAME]". Any suggestions as to where I may be going wrong with my code?

Re: Replace Multiple Values in an Attribute Table Field using Field Calculator

RandyBurton — Sat, 11 Dec 2021 10:14:08 GMT

Try:

def streetNUM(field):

  if field == "Fourth":
    return field.replace("Fourth","4th")

  elif field == "Fifth":
    return field.replace("Fifth","5th")

  else:
    return field‍‍‍‍‍‍‍‍‍‍

Re: Replace Multiple Values in an Attribute Table Field using Field Calculator

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

Use 'in' since a string is an iterable. You want to check to see if the sequence 'Fourth' (etc) is in the value returned by the row in the field

field = "Fourth Street"

if 'Fourth' in field:
    print('4th')
else:
    print('Nope')
    
4th‍‍‍‍‍‍‍‍

Re: Replace Multiple Values in an Attribute Table Field using Field Calculator

RebeccaLeach — Fri, 27 Oct 2017 21:47:05 GMT

Thanks for the input! I got same answer on another forum where I posted the same question. I made the suggested changes. When I ran the Field Calculator tool this time, I got no error codes, but none of the values changed in the "FULLNAME" field. "Fourth" stayed as "Fourth" instead of changing to "4th".

Re: Replace Multiple Values in an Attribute Table Field using Field Calculator

RebeccaLeach — Fri, 27 Oct 2017 21:57:26 GMT

Thanks Dan! By making the suggested change from if field == "Fourth": to if "Fourth" in field: and then the previous change from Randy of adding the prefix return in front of field.replace("Fourth","4th"), the field calculator tool ran as expected.

Re: Replace Multiple Values in an Attribute Table Field using Field Calculator

DanPatterson_Retired — Fri, 27 Oct 2017 22:21:09 GMT

glad it worked... just remember 'in' .... and strings are sequences, just like lists (and other objects). Fancier approaches will require the use of the 're' or the 'fnmatch' python modules.

Re: Replace Multiple Values in an Attribute Table Field using Field Calculator

JoeBorgione — Fri, 27 Oct 2017 22:35:48 GMT

Rebecca- from one newbie to another...

The == is dedicated to absolute values. Your script didn't error out because it never found a value in fullname that is exactly Fourth. Dan's suggestion for 'in' is similar to a LIKE qualifier, but IN is much more forgiving as you don't have to worry about where to put the % wildcard and when you use wildcards in a python 'where' statement, you get to deal with .format(). Bottom line; IN is much cooler...

Re: Replace Multiple Values in an Attribute Table Field using Field Calculator

RebeccaLeach — Sat, 28 Oct 2017 00:24:56 GMT

Thanks for the newbie wisdom Joe! All the replies on here have been helpful with this problem. It's always good to get a different explanation of why we use the things we do for each python problem I've been challenged with in my class versus the sometimes vague ones I get in class. And making mistakes along the way and learning from them is a lot more helpful for me even if it is stressful!

Re: Replace Multiple Values in an Attribute Table Field using Field Calculator

XanderBakker — Sat, 11 Dec 2021 10:14:14 GMT

In additional to what already has been provided above, you could use a dictionary to avoid longer if else statements. For instance:

def streetNUM(field):
    dct = {"First": "1st", "Second": "2nd", "Third": "3rd",
           "Fourth": "4th", "Fifth": "5th", "Sixth": "6th",
           "Seventh": "7th", "Eighth": "8th", "Ninth": "9th"}

    for find_txt, replace_txt in dct.items():
        if find_txt in field:
            field = field.replace(find_txt, replace_txt)

    return field‍‍‍‍‍‍‍‍‍‍

In this case you will have a dictionary with all the values you may want to look for and the corresponding replace values. This is case sensitive. If you try this:

field = "First second Third NINTH Street"
print field
print streetNUM(field)
‍‍‍

It will return this:

First second Third NINTH Street
1st second 3rd NINTH Street‍‍

This is because "second" and "NINTH" are not in the list.

To make it case insensitive you could use a simple regular expression:

def streetNUM_case_insensitive(field):
    import re
    dct = {"First": "1st", "Second": "2nd", "Third": "3rd",
           "Fourth": "4th", "Fifth": "5th", "Sixth": "6th",
           "Seventh": "7th", "Eighth": "8th", "Ninth": "9th"}

    for find_txt, replace_txt in dct.items():
        insensitive = re.compile(re.escape(find_txt), re.IGNORECASE)
        field = insensitive.sub(replace_txt, field)

    return field‍‍‍‍‍‍‍‍‍‍‍

Now when you run this code:

field = "First second Third NINTH Street"
print field
print streetNUM_case_insensitive(field)‍‍‍

... it will yield:

First second Third NINTH Street
1st 2nd 3rd 9th Street‍‍

Re: Replace Multiple Values in an Attribute Table Field using Field Calculator

RebeccaLeach — Sat, 28 Oct 2017 10:40:57 GMT

Thanks Xander! I appreciate the insight to the other methods I could use to achieve the same end result. I remember learning about dictionaries in class. And now seeing your input I can see how that would have been a much better method if my list of variables needing changed had been longer. It was also nice to see how you could make it case insensitive, thus eliminating any data loss due to incorrect case.

Re: Replace Multiple Values in an Attribute Table Field using Field Calculator

JasonBalmut — Sat, 11 Dec 2021 10:14:16 GMT

Thank you for the dictionary solution to this common issue, Xander Bakker. I have a similar question for parsing road names along with their suffixes and translating them to MSAG values. Interstates, County Roads, and State Roads follow a different pattern to our normal street names:

def translateNAME(streetName, dirSuf):
	dctSTREETNAME = {"Interstate ": "I", "US Highway": "", " US Highway": "", "Martin Luther King Jr": "Dr King", "Saint": "St", "County Road ": "", "State Road ": ""}
	for findSTREETNAME, replaceMSAGNAME in dctSTREETNAME.items():
		if 'Interstate' in streetName and 'N' in dirSuf:
			streetName = streetName.replace(findSTREETNAME, replaceMSAGNAME) + ' Northbound'
		elif 'Interstate' in streetName and 'S' in dirSuf:
			streetName = streetName.replace(findSTREETNAME, replaceMSAGNAME) + ' Southbound'
		elif 'Interstate' in streetName and 'E' in dirSuf:
			streetName = streetName.replace(findSTREETNAME, replaceMSAGNAME) + ' Eastbound'
		elif 'Interstate' in streetName and 'W' in dirSuf:
			streetName = streetName.replace(findSTREETNAME, replaceMSAGNAME) + ' Westbound'
		elif 'County Road' in streetName or 'State Road' in streetName:
			streetName = streetName.replace(findSTREETNAME, replaceMSAGNAME) + ' Hw'
		else:
			streetName = streetName.replace(findSTREETNAME, replaceMSAGNAME)
	return streetName.strip()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

When I run this script the normal streets are correct, but I get repeating concatenations for the others.

Any thoughts on how to fix this?

Re: Replace Multiple Values in an Attribute Table Field using Field Calculator

XanderBakker — Sat, 11 Dec 2021 10:14:19 GMT

Hi jesterjace ,

You have your replace statements inside the loop, so it gets applied multiple times. I can't reproduce the same result as you have with many "Hw" in the resulting name, but maybe you should try something like this:

def translateNAME(streetName, dirSuf):
    dctSTREETNAME = {"Interstate ": "I",
                  "US Highway": "",
                  "Martin Luther King Jr": "Dr King",
                  "Saint": "St",
                  "County Road ": "",
                  "State Road ": ""}
    result = streetName
    for findSTREETNAME, replaceMSAGNAME in dctSTREETNAME.items():
        result = result.replace(findSTREETNAME, replaceMSAGNAME)

    if 'Interstate' in streetName and 'N' in dirSuf:
       result += ' Northbound'
    elif 'Interstate' in streetName and 'S' in dirSuf:
       result += ' Southbound'
    elif 'Interstate' in streetName and 'E' in dirSuf:
       result += ' Eastbound'
    elif 'Interstate' in streetName and 'W' in dirSuf:
       result += ' Westbound'
    elif 'County Road' in streetName or 'State Road' in streetName:
       result += ' Hw'
    return result.strip()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I haven't tested the code though...

Re: Replace Multiple Values in an Attribute Table Field using Field Calculator

JasonBalmut — Wed, 03 Jul 2019 14:04:44 GMT

Perfection! Thanks, Xander Bakker‌!