Clean up attributes

Question

I want to clean up the attribute table of a road shapefile for all the entries in the fields.

For example: As seen below I have a road feature with a "Name", Name From and Name To in the attribute list. In the selected row the name of the segment is Louis Botha.

The NameFrom also contains this segment name (Louis Botha & Unknown).

After I clean up the data I only want the following to present in the relevant fields:

Name = Louis Botha Name From = Unknown Name To = Janeke

Is there a way to remove this in both the the "name from" and "name to" fields for ALL the attributes?

enter image description here

• phloem: So, you want to be left with "Louis Botha", " & Unknown", and "Janeke & "?
• Aaron: Could you please update your question to include an example of the cleaned output?
• Dean van den Heever: @ phloem: Yes I want to be left with Louis Botha", " & Unknown", and "Janeke. @Aaron I updated the question to make it more clear.

Answer 1

You can use the following Python function in Field Calculator.

def remove_name(name_value, from_to_value):
 streets = [street.strip() for street in from_to_value.split('&') if street.strip() != name_value.strip()]
 return ' & '.join(streets)

Copy the above into the codeblock/pre-logic script code section. You can run it once for Name_From using remove_name(!Name!, !Name_From!) as the expression, and a second time for Name_To using remove_name(!Name!, !Name_To!).

This code will work not only for the cases where the Name value is at the beginning or end of Name_From/Name_To, but also those where it is in the middle. For example, remove_name('Janeke', 'Hereeniging & Janeke & Willemse') returns 'Hereeniging & Willemse'. For this to work, the input names must be separated by ampersands (&).

• Dean van den Heever: Will this clean up all the attributes in one go or each line individually? Sorry for asking, not very up to date prelogic scripting.
• nmpeterson: The field calculator modifies every selected record of a single field at the same time (or all records in the table if no features are selected).

Answer 2

The following script performs the actions you are after using a cursor. There is a lot of error handling to deal with a lot of potential problems--remove as needed. This alters the original data, so make sure to run this on a copy to make sure the results are what you are after. I added comments in the script rather than highlighting here.

import arcpy, os
fc = r'C:\temp\test.gdb\test_1'
with arcpy.da.UpdateCursor(fc, ["Name", "Name_From", "Name_To", "OID@"]) as cursor:
 for row in cursor:
 if row[0] != None: # Make sure there are no None type data
 # 1) Split strings by "&" and 2) remove leading/tailing white space
 cleaned = [x.strip() for x in row[1].split("&")] # "Name_From" field
 cleaned2 = [x.strip() for x in row[2].split("&")] # "Name_To" field
 # Tackling the "Name_From" field
 if row[0] in cleaned: # Make sure "Name" is in "Name_From" field
 cleaned.remove(row[0]) # Remove "Name" from field
 if len(cleaned) > 1:
 new = ' & '.join(cleaned)
 row[1] = new
 elif len(cleaned) == 1:
 row[1] = cleaned[0]
 else:
 print "There was a problem with OID %s" % row[3]
 # Tackling the "Name_To" field
 if row[0] in cleaned2: # Make sure "Name" is in "Name_To" field
 cleaned2.remove(row[0]) # Remove "Name" from field
 if len(cleaned2) > 1:
 new2 = ' & '.join(cleaned2)
 row[2] = new2
 elif len(cleaned2) == 1:
 row[2] = cleaned2[0]
 else:
 print "There was a problem with OID %s" % row[3]
 cursor.updateRow(row)

---

enter image description here