# Count Duplicates in a Field for sequential unique ID

1192
5
05-10-2021 04:45 PM
New Contributor II

I am trying to create a unique ID for my street feature class. (My unique ID starts with first 3 letters of the Street Name, 2 letters of the Street type, and 3 letters of the City Name Abbreviated.) Where I am stuck is that I am trying to get a number to increment on the segments that would be duplicated. For example, Glenacre Drive has three segments. The UniqueID would be GLEDRHQF01, GLEDRHQF02, GELDRHQF03. I cant figure out how to create sequential numbers not based off of the ObjectID sorting... For example I used the following Field Calculator but realized it is looking at the ObjectID sort and my streets aren't entered in order...

Pre-Logic Script Code:

prevFieldValue = '' counter = 1 def GetDuplicateCounter(myFieldValue): global prevFieldValue global counter if myFieldValue == prevFieldValue: counter += 1 else: counter = 1 prevFieldValue = myFieldValue return counter

= GetDuplicateCounter( !UNIQUEID! )

Any Help would be Amazing! 🙂

1 Solution

Accepted Solutions
MVP Esteemed Contributor

Something like this should work in the interactive Python window:

``````lyr = # name of layer or path to data set

with arcpy.da.UpdateCursor(lyr, "UNIQUEID", sql_clause=(None, "ORDER BY UNIQUEID")) as cur:
cnt = 1
uid, = next(cur)
cur.updateRow(["{}{:02}".format(uid,cnt)])
uid_prev = uid
for uid, in cur:
if uid == uid_prev:
cnt += 1
else:
cnt = 1

cur.updateRow(["{}{:02}".format(uid,cnt)])
uid_prev = uid
``````

5 Replies
MVP Frequent Contributor

Define a dictionary that relates the string part of the id to the corresponding counter:

``````duplicates = {"GLEDRHQF": 3}

def get_duplicate_counter(value):
try:
counter = duplicates[value] + 1
except KeyError:
counter = 1
duplicates[value] = counter
return counter
# or directly return the complete id:
# return "{}{:02d}".format(value, counter)``````

Have a great day!
Johannes
MVP Esteemed Contributor

Something like this should work in the interactive Python window:

``````lyr = # name of layer or path to data set

with arcpy.da.UpdateCursor(lyr, "UNIQUEID", sql_clause=(None, "ORDER BY UNIQUEID")) as cur:
cnt = 1
uid, = next(cur)
cur.updateRow(["{}{:02}".format(uid,cnt)])
uid_prev = uid
for uid, in cur:
if uid == uid_prev:
cnt += 1
else:
cnt = 1

cur.updateRow(["{}{:02}".format(uid,cnt)])
uid_prev = uid
``````

New Contributor II

Thank you so much! Is there a way for me to make this create a new field and populate it?

MVP Esteemed Contributor

Just use Add Field (Data Management)—ArcMap | Documentation (arcgis.com) upstream of the code snippet I provided, and then adjust the cursor to work with the necessary fields.

New Contributor II

Thank you!!