topic Re: Strip numbers from certain fields in Python Questions

Strip numbers from certain fields

TonyAlmeida — Sat, 11 Dec 2021 20:16:40 GMT

I need to strip numbers from certain fields. I have the following but it strips all number from the entire table.

Some of these fields have blanks and nulls.

import arcpy

arcpy.env.overwriteOutput = True

table = "C:/temp/EMS_Table"

field = ['Agency', 'fa', 'lz']
for field in [f.name for f in arcpy.Describe(table).fields if f.type == "String"]:  
    exp = "!{0}!.strip('0123456789.- ')"  
    arcpy.management.CalculateField(table, field, exp.format(field), "PYTHON")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Strip numbers from certain fields

DarrenWiens2 — Sat, 11 Dec 2021 20:16:42 GMT

You are overwriting the list of fields when you create the loop.

Consider:

field = ['Agency', 'fa', 'lz']
for field in [1, 2, 3, 4]:
    print(field)

1
2
3
4‍‍‍‍‍‍‍‍

If you know the fields you want to loop through:

fields = ['Agency', 'fa', 'lz']
for field in fields:
    print(field)

Agency
fa
lz‍‍‍‍‍‍‍

Re: Strip numbers from certain fields

DanPatterson_Retired — Tue, 30 Oct 2018 18:44:43 GMT

field gets defined twice, once in line 7 … which are the fields that you want I presume.... but it gets redefined again in line 8 in the 'for field....' section. I suspect that it is not just doing the fields you want but all the fields that are of type 'String'

Re: Strip numbers from certain fields

JoeBorgione — Tue, 30 Oct 2018 18:49:56 GMT

Can you show what the field values look like at present? Is there a pattern of how they are formatted like

FA25 or FA 25 ?

When you follow some sort of naming convention in your field values it's always easier to parse them out when you need to...

Re: Strip numbers from certain fields

TonyAlmeida — Tue, 30 Oct 2018 19:08:19 GMT

yes it's doing all the fields, not really sure how to write it. I am also not sure if this is the best way. would using with arcpy.da.UpdateCursor better?

Re: Strip numbers from certain fields

TonyAlmeida — Tue, 30 Oct 2018 19:08:48 GMT

I think for the most part they are just FA25 with no spaces.

Re: Strip numbers from certain fields

DanPatterson_Retired — Sat, 11 Dec 2021 20:16:45 GMT

I think there is some confusion about whether you are trying to strip stuff from values in a field, or strip the fields (which are weirdly names, hence the suspicion) that you have listed.

If we assume your fields are as listed in line 1 and assuming you are working on a copy and that your calculatefield expression actually works... maybe

field = ['Agency', 'fa', 'lz']
for f in field:
      exp = "!{0}!.strip('0123456789.- ')"
      arcpy.management.CalculateField(table, f, exp.format(field), "PYTHON")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Now if those aren't the fields, maybe you might want to show with a screen grab what you are working with

Re: Strip numbers from certain fields

TonyAlmeida — Tue, 30 Oct 2018 21:25:48 GMT

I am trying to strip from the number values in those fields. sorry for not making it clear.

i am getting ERROR 000539: Invalid field['Agency', 'fa', 'lz']

Re: Strip numbers from certain fields

DarrenWiens2 — Tue, 30 Oct 2018 21:37:42 GMT

arcpy.management.CalculateField(table, f, exp.format(f), "PYTHON")

Re: Strip numbers from certain fields

JoeBorgione — Sat, 11 Dec 2021 20:16:48 GMT

I stole this from StackOverflow: python - Removing numbers from string - Stack Overflow

no_digits = []
# Iterate through the string, adding non-numbers to the no_digits list
for i in s:
    if not i.isdigit():
        no_digits.append(i)

# Now join all elements of the list with '', 
# which puts all of the characters together.
result = ''.join(no_digits)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Where s = your field value....

edited- just to add, you could do the inverse, and look for and keep letters....

is_alpha =[]
for i in s:
  if i.isalpha():
    is_alpha.append(i)‍‍‍‍‍‍‍‍

Re: Strip numbers from certain fields

DanPatterson_Retired — Sat, 11 Dec 2021 20:16:50 GMT

from this old thread

https://community.esri.com/message/639336-re-how-to-split-variable-length-field-at-first-number-field-calculator?comment…

keep numbers

fld = "aaasdfasdfasdf12341asdfadfadsf"

"".join([i for i in fld if i.isdigit() or i == " "]).strip()
'12341'‍‍‍‍‍‍‍‍

keep text

"".join([i for i in fld if i.isalpha() or i == " "]).strip()

'aaasdfasdfasdfasdfadfadsf'‍‍‍

line 1 becomes your field calculation and you feed 'fld' into it

for fld in fields:

do stuff with the above in your code

Re: Strip numbers from certain fields

JoshuaBixby — Wed, 31 Oct 2018 13:01:09 GMT

I grab suggestions from StackOverflow quite a bit, I try to include a link to the original SO thread to include in my GeoNet comment.

Re: Strip numbers from certain fields

JoeBorgione — Wed, 31 Oct 2018 14:53:04 GMT

see edit above....

Re: Strip numbers from certain fields

TonyAlmeida — Wed, 31 Oct 2018 19:43:02 GMT

After running everything looked fine but there seems to be some "#" symbols in those fields as well, For example Uk05#26. I need to remove those as well.

field = ['Agency', 'fa', 'lz']
for f in field:
exp = "!{0}!.strip('0123456789.- ')"
arcpy.management.CalculateField(table, f, exp.format(f), "PYTHON")

Re: Strip numbers from certain fields

JoeBorgione — Wed, 31 Oct 2018 19:57:04 GMT

That's thing about naming conventions: they don't mean anything if people won't/don't follow them. Once you get your data cleaned up, then automation works really, really well. You can make yourself crazy trying to automate anomalies only to find another one. And then another.

Sometimes you just have to roll up your sleeves and expect to get your hands dirty...

Re: Strip numbers from certain fields

DanPatterson_Retired — Wed, 31 Oct 2018 21:27:10 GMT

just add # to your strip( '0123456789.- #') list

Re: Strip numbers from certain fields

TonyAlmeida — Wed, 31 Oct 2018 21:54:44 GMT

I am receiving this data and in order for me to make it work for me i have to clean this table up but i get what you are saying.

Re: Strip numbers from certain fields

TonyAlmeida — Wed, 31 Oct 2018 22:00:19 GMT

I did try that before i posted but for some reason it's only removing the # from two fields not all three. I just wasn't sure if i was missing something. Does it matter if there is spaces? for example "UK 05# 56CCO. So in this example it needs to be UKCCO.That's the only thing i am seeing that is different.

Re: Strip numbers from certain fields

JoeBorgione — Wed, 31 Oct 2018 22:00:52 GMT

I like summary tables to find the outliers. A pound sign or dash or some other weird character will jump out in a hurry, and typically the count will be fairly low...

Re: Strip numbers from certain fields

DanPatterson_Retired — Wed, 31 Oct 2018 23:19:43 GMT

if you look at your replace list, make sure there is a space in it

a = '0123456789.- #'

a0 = list(a)

a0
['0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '.', '-', ' ', '#']

Notice the character before # is a space ' '