Populate field with information from a different field based on matching record

Question

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.

Answer 1

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.