Remove/strip certain text/string from rows

245
5
10-21-2020 01:41 PM
2Quiker
Occasional Contributor

How can I remove certain text/string from all the rows in a filed with out having to do multiple updatcursors? 

  • I need to remove the city and state from each row if there is no city and state just skip and keep going, some may not have any city and state .
  • The city and stats might be different for each row.
  • The state and city will always be at the end which would be :13. Some how I need to just work on the last 13 characters because some address use the city name. 

I need don't want to create another filed I just need the city and state removed from all the rows of a certain field.

OIDfield1field1
1address city1  state1address
2address city2  State1address
3address city3  State1address
4address city2  State1address
5address city4  State1address
6address city5  State1address
7address city5  State1address
8address city1  State1address
9address city2  State1address

Was thinking something like this but I doesn't work.

fc = 'featureclass'
tx = ['City1 State','City2 State','City3 State','City4 State', 'City5 State']

with arcpy.da.UpdateCursor(fc, ['field1']) as cursor:
for row in cursor:
if row[0][:13] in tx:
row[0] = row[0][:13]
cursor.updateRow(row)
del cursor
0 Kudos
5 Replies
JoeBorgione
MVP Esteemed Contributor

Potentially, you could lose your mind trying to figure out how many combinations of address, city and state your data has.  Just sayin'...

But if you want the last n  characters of a string you'd use:

myString = 'Some Street and City State'
myString[-5:]

#returns State
can't wait to retire....
2Quiker
Occasional Contributor

Aw I didn't think of that.

fc = 'featureclass'
tx = ['City1 State','City2 State','City3 State','City4 State', 'City5 State']

with arcpy.da.UpdateCursor(fc, ['field1']) as cursor:
for row in cursor:
row[0] = " ".join(row[0].split()[:-2])
cursor.updateRow(row)
del cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I did notice that it did remove ones like

12345 StreetA St city State -->12345 StreetA St # What I wanted

0 StreetB St --> 0 # This is the issues, I would prefer if it just stayed 0 StreetB St.

0 Kudos
JoeBorgione
MVP Esteemed Contributor

Perhaps you could post some of your actual data. Those variable values are really tough to standardise. 

can't wait to retire....
0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I agree with Joe Borgione‌ on several points.  Firstly, actual sample data never hurts.  Secondly, and more importantly, address parsing is notoriously complex, and there is no reason to reinvent wheels that already work well.  There are several US address parsing libraries, I suggest you use one of those.

JoeBorgione
MVP Esteemed Contributor

Here's a post of mine that describes some address parsing I have done in the past. Joshua Bixby‌ provides a link to one of the parsing libraries he alludes to above

can't wait to retire....