Replace Multiple Values in an Attribute Table Field using Field Calculator

5009
12
Jump to solution
10-27-2017 02:00 PM
RebeccaLeach
New Contributor III

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:

enter image description here

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?

1 Solution

Accepted Solutions
DanPatterson_Retired
MVP Esteemed Contributor

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

View solution in original post

12 Replies
RandyBurton
MVP Regular Contributor

Try:

def streetNUM(field):

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

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

  else:
    return field
RebeccaLeach
New Contributor III

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".

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

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

View solution in original post

RebeccaLeach
New Contributor III

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.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

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.

0 Kudos
JoeBorgione
MVP Esteemed Contributor

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...

can't wait to retire....
RebeccaLeach
New Contributor III

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!

0 Kudos
XanderBakker
Esri Esteemed Contributor

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
RebeccaLeach
New Contributor III

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.