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?

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 (&).

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)

---