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

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

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]
                    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]
                    print "There was a problem with OID %s" % row[3]

enter image description here

| improve this answer | |

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

| improve this answer | |
  • Will this clean up all the attributes in one go or each line individually? Sorry for asking, not very up to date prelogic scripting. – Dean van den Heever Feb 19 '15 at 9:10
  • 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). – nmpeterson Feb 19 '15 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.