0

I have a feature class created by merging multiple feature classes together, after the merge there are a lot records that have the same spatial attributes but different tabular attributes, except one field.

I want an arcpy script that will look for matching records in 'field 1'(there are two or more matches) and for the first match it should be the full value of 'field 2', for the second match it should be only the last three characters.

Example: 'field 1' ('subcatchme') has matching records = '68103' and '68103' the values in 'field 2' ('NAME') are 'W18_0_02' and 'W18_0_03' respectively. I want to populate both the matching records in 'field 3' ('CatchmentName') with 'W18_0_02_03'.

from arcpy import env
env.workspace = r"D:\Users\Test2.gdb"

origFC = "W18_0_Merge"
dissolvedFC = "W18_0__Dissolve"

##Get a list of NAME Field
idList2 = []
with arcpy.da.SearchCursor(dissolvedFC, ["subcatchme"]) as cursor:
    for row in cursor:
        idList2.append(row[0])
del cursor
##Remove 'u' from front of list
idList = [i.encode('utf-8') for i in idList2]

##iterate through each subcatchme
for id in str(idList):
    print "Hi"
    ## Use a search cursor to query only the individual subcatchme
    with arcpy.da.SearchCursor(origFC, ["NAME"], '"subcatchme = {0}".format(id)') as cursor:
        print "Hi2"
        for row in cursor:
            print "Hi3"
            ## Use an update cursor to update the dissolved feature class fields
            with arcpy.da.UpdateCursor(dissovledFC, ["CatchmentName"], '"subcatchme = {0}".format(id)') as cursor2:
                for row2 in cursor2:
                    if row2[0] == None or row2[0] == '':
                        row2[0] = row[0]
                    else:
                        row2[0] = row[0] + row2[0] ##I want it to only show last three characters here
                    cursor2.updateRow(row2)
    del cursor, cursor2

I have re-purposed code previously pointed to me (Here) but I keep getting an error after the second cursor. the "Hi2" will print but it's not going through it.

RuntimeError: An invalid SQL statement was used. [SELECT NAME, OBJECTID FROM W18_0_Merge WHERE "subcatchme = {0}".format(id)]

I have also been trying to change the last cursor so it only grabs the last three characters but the only format I can find is slicing strings but I don't know how to put it together when 'row[0]' already has the square brackets.

s = "Pirates of the Caribbean"
print(s[21:24])

would result in 'bean'

I am using ArcMap 10.4 with all the extensions. the program I am writing python in is pythonwin.

5
  • If I'm understanding you correctly, if you perform a query on the data for Field1 = 'A', there might be 6 matching records, and if so you will want all of the rows selected to have Field3 populated by a string of length 12 (eg. 342111112436)?
    – SMiller
    Nov 16, 2018 at 18:44
  • 2
    Welcome to GIS SE. Thank you for taking the Tour. Unfortunately, in addition to a problem statement, coding questions here are expected to contain a best effort at coding the solution, with an indication of what error is occurring. Please Edit the question to contain the version of ArcGIS in use, your code, the sample data you're using to test the prototype, and an indication of what error you are encountering.
    – Vince
    Nov 16, 2018 at 18:47
  • Yes, smiller you are correct with what I want. In my data there is an underscore to separate the information. I'm unsure how to add sample data.
    – TarenKoeth
    Nov 16, 2018 at 20:26
  • Start with the da.SearchCursor, creating a query for each unique value of subcatchment. This would return all of the rows that match that query, which you'd then have to use to build the final string.
    – SMiller
    Nov 16, 2018 at 20:34
  • This post has a code sample that may help: gis.stackexchange.com/questions/302985
    – SMiller
    Nov 16, 2018 at 20:36

1 Answer 1

0

Below is the solution to the question posted above. Most of the issues I was experiencing were based on my lack of understanding on the punctuation required for the script. The place holder in the where clause required '' to be placed around it.

import arcpy
from arcpy import env
env.workspace = r"D:\Users\Test2.gdb"

origFC = "W18_0_Merge"
dissolvedFC = "W18_0__Dissolve"

##Get a list of NAME Field
idList2 = []
with arcpy.da.SearchCursor(dissolvedFC, ["subcatchme"]) as cursor:
    for row in cursor:
        idList2.append(row[0])
del cursor

##Remove 'u' from front of list
idList = [i.encode('utf-8') for i in idList2]
##iterate through each subcatchme
for id in idList:
    expression = "subcatchme = {0}".format(id)
    ## Use a search cursor to query only the individual subcatchme
    with arcpy.da.SearchCursor(origFC, ["NAME"], "subcatchme = '{0}'".format(id)) as cursor:
        for row in cursor:
            ## Use an update cursor to update the dissolved feature class fields
            with arcpy.da.UpdateCursor(dissolvedFC, ["CatchmentName"], "subcatchme = '{0}'".format(id)) as cursor2:
                for row2 in cursor2:
                    if row2[0] == None or row2[0] == '':
                        row2[0] = row[0]
                    else:
                        row2[0] = row[0] + row2[0][-3:] # only last three characters
                    cursor2.updateRow(row2)
    del cursor, cursor2

Thanks everyone for the help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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