Field Calculator

367
5
06-19-2019 07:20 AM
ZachPemberton
New Contributor II

I need some help with a field calculator scenario. I have a field called Township and many of the attributes in the field are formatted differently. Here is an example of the differences in the data. 

Current FormatDesired Format
0077N
0070N7N
0170N17N
017N17N
17n17N
170N17N
017N17N
1717N

10N

10N
10 N10N
0100N10N

There are over 50,000 records and it appears most of these are in the 0070N and 0170N format, but I need to be able to convert these to all be in the same format. I've tried a few different scripts, but did not find success. My Python and VB scripting is limited so its been a bit difficult to figure out a possible modification to some of the scripts I did find. The Townships in the list range from 4N-23N and 25N-27N. As you can see, I can't just get rid of the zeros due to the fact that I have 10's and 20's. Anyone with some scripting abilities have some ideas on how I can get these converted using the field calculator without too much trouble?

Thanks in advance!

Zach

0 Kudos
5 Replies
Egge-JanPollé1
MVP Regular Contributor

Hi Zach Pemberton,

Maybe a regular expression could be used to solve this, but to meet all the conditions it will become a complicated one...

I am not that good at Regex, so - if it is a one-time-affair - I would suggest a manual process:

  • Select records WHERE "Current Format" IN ('0170N','017N','17n','170N','017N','17')
  • Use Field Calculator to update field "Desired Format" with value '17N' (only selected records will be affected)
  • Change selection and update (using Field Calculator) with other value
  • Repeat until all records have a value for "Desired Format"

 

Does this work for you?

 

Egge-Jan

ZachPemberton
New Contributor II

Thank Egge-Jan, its actually a little more complicated than the examples I provided. The numbers vary pretty widely. I would have to do many selections and that would become much more of a manual labor process. That's why I was hoping some sort of Python script could automate the process a little better. I found a solution in using the .replace() function. It's still a little bit of a manual labor process, but it works pretty well and pretty quickly.

0 Kudos
JakeSkinner
Esri Esteemed Contributor

Hi Zach,

You could use Python lstrip method in the Field Calculator:

def calc(field):
    if 'N' in field or 'n' in field:
      return(field.lstrip('0').upper())
    else:
      return(field.lstrip('0').upper() + 'N')

Ex:

ZachPemberton
New Contributor II

Thanks Jake. I have to do this process on a recurring basis. I'll have to try this out next time I do this. As it stands I was able to use the .replace() function to complete the task. When I do this again I'll let you know if this works. Thanks for the response! 

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I am sure there are edge cases the following regex misses, but it does address all of your examples:

>>> import re
>>>
>>> l = ['007', '0070N', '0170N', '017N', '17n', '170N', '017N', '17', '10N', '10 N', '0100N']
>>>
>>> for i in l:
...     print("{}N".format(re.search('[4-9]|1[0-9]|2[0-3]|2[5-7]', i).group(0)))
...
7N
7N
17N
17N
17N
17N
17N
17N
10N
10N
10N
>>>