Field Calculator

[ZachPemberton]

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 Format	Desired Format
007	7N
0070N	7N
0170N	17N
017N	17N
17n	17N
170N	17N
017N	17N
17	17N
10N	10N
10 N	10N
0100N	10N

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

[Egge-Jan_Pollé]

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]

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.

[JakeSkinner]

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]

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! 

[JoshuaBixby]

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