# Calculate and Restart Sequential ID for Unique Values in a Field

I need to calculate a sequential ID for unique values in a field. So, for the values in Field1, the sequential ID count should count all occurrences of that value, then restart the count for the next value. For the example below, column 1 contains the values to count, column 2 is what the sequential field result should look like.

A 1

A 2

B 1

B 2

B 3

B 4

C 1

D 1

D 2

D 3

My plan was to approach this with an update cursor and the classic autoIncrement code, but it calculated all rows in the table with a sequential ID. I know the if/else is the wrong approach, but I was hoping the autoIncrement would start over within each if statement. I'm guessing I need to get a count of the unique values in Field1, then somehow calculate the sequential ID on the row as it counts? I saw a collections.Counter example, but wasn't sure if that could be applied here.

``````table = table path
fields = ('FIELD1,FIELD2')

rec=0
def autoIncrement():
global rec
pStart = 1
pInterval = 1
if (rec == 0):
rec = pStart
else:
rec = rec + pInterval
return rec

with arcpy.da.UpdateCursor(table, fields) as uc:
for row in uc:
if row == 'A':
row = str(autoIncrement())
etc.
``````
• Can be done using field calculator. Have a look at gis.stackexchange.com/questions/156140/… All you need is replace return 2 line with return len (...) – FelixIP Aug 17 '15 at 23:52
• The indentation for your autoIncrement, as presented in the question, is incorrect. Please make sure that any code snippets presented work up to where you are stuck. – PolyGeo Aug 18 '15 at 0:05

This worked for me:

``````import arcpy

id_field = 'ID_Field'
calc_field = 'Calc_Field'
fc = r'C:\TEMP\points.shp'

values = []
with arcpy.da.UpdateCursor(fc, [id_field, calc_field]) as rows:
for row in rows:
values.append(row)
row = '{}{}'.format(row, values.count(row))
rows.updateRow(row)

print 'done'
``````
• When I tried this the following error occurred: "...The value type is incompatible with the field type. [Calc_Field]". Based on that msg I tried it with Calc_Field as a string type field. No errors resulted, but the string output was a concatenation of ID_Field and the incremental number. So it would work IF the output was numeric and the ID_Field was dropped. I don't know Python that well, so I don't know how to fix it. Do you have any thoughts? – Stu Smith Dec 23 '17 at 1:29

If you want that classic auto-increment function to use.For me it is working fine-just change the variables.

``````import arcpy

#Set all needed variables
fc = r'C:\Users\USER_NAME\Documents\ArcGIS\Default.gdb\knp_office'
flds = ['Type','flg']
inc_fld = 'flg' # field where consecutive numbers are placed--field type is text
category_fld = 'Type' # field based on which serial/autoincrement is calculated--field type is text
vals = []

rec=0
def autoIncrement():
global rec
pStart = 1
pInterval = 1
if (rec == 0):
rec = pStart
else:
rec+= pInterval
return rec

curU = arcpy.da.SearchCursor(fc,flds)
for row in curU:
vals.append(row)
del curU
vals=set(vals)

for i in vals:
curU=arcpy.da.UpdateCursor(fc,flds,category_fld+" = '%s'"%i)
for row in curU:
print "rec: "+str(rec)
row = i+" "+str(autoIncrement())
curU.updateRow(row)
del curU
rec=0
``````
• This code will not be very efficient if there are a lot of unique values as you are creating a new cursor for each one. – crmackey Aug 18 '15 at 12:22