I have an attribute table with three fields: Id, Name, and Id_2 and 18,701 records. I would like to populate Id_2 with consecutive integers for each unique value in the Name field. Prior to running the script, Id_2 is empty. Below is an example of what I would like the final attribute table to look like.

desired result with consecutive integers in Id_2 field

Below is the python script that I have developed. Note that I have built a dictionary using the Name field as the keys and the Id field as the values. I don't need to use the values, however, to accomplish this task, just the key. My code produces consecutive integers, however, the sequence doesn't reset with each unique value in the Name field. Rather, my code populates Id_2 with consecutive integers for the entire table, through record 18,701 (Not what I want!).

# import required python libraries or modules
import arcpy
import traceback

# settings
arcpy.env.workspace = r'D:/test'
arcpy.env.overwriteOutput = True

# variables
fc = r'D:/test/colors.shp'
fields = ['Id', 'Name', 'Id_2']
for f in fields:
  print f, f in [field.name for field in arcpy.ListFields(fc)]

# build dictionary
rnDict = {}
cursor = arcpy.da.SearchCursor(fc, fields)
    for row in cursor:
        id = row[0]
        rn = row[1]
        rnDict.setdefault(rn, []).append(id)
    print 'dictionary successfully built'
    print 'An error occurred - part 2'
del cursor

# update Id_2 field with sequential integers
startNumber = 1
cursor = arcpy.da.UpdateCursor(fc, fields)  # type: object
    for row in cursor2:
        name = row[1]
        if name in rnDict:
            row[2] = startNumber
            startNumber = startNumber + 1
    print 'completed consecutive numbering'
    print 'An error occurred during calculating field'
del cursor
# del rnDict

For a start ID is irrelevant in your fields, you only need the two fields being worked on 'Name', 'Id_2'. You've also set up a search then an update, you don't need to do this, a single update is enough; what you're after in much less code:

fc      = r'D:/test/colors.shp'
fields  = ['Name', 'Id_2']
NCntDic = {} # a new empty dictionary
with arcpy.da.UpdateCursor(fc,fields) as uCur:
    for uRow in uCur:
        ThisName = uRow[0].lower()    # this name as lower case
        if ThisName in NCntDic:
            NCntDic[ThisName]+= 1     # increment the counter
            uRow[1]=NCntDic[ThisName] # assign the value
            NCntDic[ThisName] = 1     # start the counter
            uRow[1]=NCntDic[ThisName] # assign the value

The dictionary stores name:value pairs so each unique name (case sensitive) can keep a single count separate from the counts of other unique name. If your name:values do need to be case sensitive then take out the .lower() which forces all names to be lower case - also a good idea to .trim() if your data is suspect to remove leading and trailing spaces.

  • Thanks for the help. Your code worked. Would you mind explaining the purpose of line 6, where the variable ThisName is defined using the .lower method? – user3467260 Mar 11 '20 at 3:36
  • 1
    Sure, I've set the variable ThisName to uRow[0].lower() because ThisName is easier to type - less likely to be in error and if you want to remove the lower case function it only needs to be done once for all. The reason for the .lower() is to do the string comparison in lower case characters so that 'DOG','Dog' and 'dog' appearing in the attribute table are all treated the same which may or may not be what you want to do though I find it most common that the case of the name is not relevant. Equally you could do the comparison in .upper(), as long as it's consistent. – Michael Stimson Mar 11 '20 at 3:48

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