topic Re: Field Calculator in Python Questions

Field Calculator

ZachPemberton — Wed, 19 Jun 2019 14:20:55 GMT

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

Re: Field Calculator

Egge-Jan_Pollé — Wed, 19 Jun 2019 14:49:01 GMT

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

Re: Field Calculator

JakeSkinner — Sun, 12 Dec 2021 01:02:59 GMT

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:

Re: Field Calculator

ZachPemberton — Thu, 20 Jun 2019 15:01:03 GMT

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.

Re: Field Calculator

ZachPemberton — Thu, 20 Jun 2019 15:04:08 GMT

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!

Re: Field Calculator

JoshuaBixby — Sun, 12 Dec 2021 01:03:02 GMT

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