topic Re: Find Duplicates and increment in Python Questions

Find Duplicates and increment

DavidBrett — Sun, 12 Dec 2021 04:27:00 GMT

Hello,

I'm attempting to find the duplicates in a field, then number the results sequentially within each duplicate set. I've managed increment the duplicates, but not start over when the next duplicate is hit in the code.

At 9.3, I was able to use the field_Mark_Duplicates_2.cal from Easy Calculate 5.0 (http://www.ian-ko.com/free/free_arcgis.htm). Now at 10, I need to do it in python and I'm having little success.

example of desired result:
a==>1
b==>1
c==>1
b==>2
b==>3
a==>2

Here is a snippet of code that increments all the duplicate in the table:


    rows = arcpy.UpdateCursor("Junk1.dbf")
    fields = arcpy.ListFields("Junk1.dbf" )
    
    #Create an empty list
    myList = []
       
    i = 0
    for row in rows:
        for field in fields:            
            if field.name == 'ID':
                value = row.getValue(field.name)                
                if value in myList:       
                    i += 1
                    row.TEST = i
                    rows.updateRow(row)
                if value not in myList:
                    myList.append(value)

Any help would be greatly appreciated!!

Re: Find Duplicates and increment

TerrySilveus — Sun, 12 Dec 2021 04:27:02 GMT

I'm just learning python, but I think you might try something like this.. you'll probably have to fix syntax, but it's your list that may be the problem you are having... it won't cut it because you are incrementing regardless of what the "value" is so your results probably look more like this than what you want...
a==>1
b==>1
c==>1
b==>2
b==>3
a==>4

try a matrix instead of a one D list. where you are storing [['a',1], ['b',1], ['c',1]] and incrementing the second value if it is found (to save the value) and then setting the field in that row to that value... - this is just air code so you'll have to test it out

import arcpy

rows = arcpy.UpdateCursor("Filename")
mylistarray = []


found = False
for row in rows:
    value = row.getValue("ID")
    for x in mylistarray:
        if x[0] == value:
            found = True
            x[1] +=1
            row.TEST = x[1]
     if not found:
        mylistarray.append([value,1])
        row.TEST = 1
    else:
        found = False
    rows.updateRow(row)

Re: Find Duplicates and increment

DarrenWiens2 — Sun, 12 Dec 2021 04:27:05 GMT

I usually use the approach of sorting by values, then comparing the previous value to the current value:

 rows = arcpy.UpdateCursor("Junk1.dbf","","","","ID A") # sort by ID, ascending order
    
    #Create an empty list
    myList = []
      
    i = 0
    for row in rows:
        if i = 0: #first time around
            value = row.ID
            myList.append(value)
        i += 1
        if row.ID != value: #if a new ID
            value = row.ID
            myList.append(value)
            i = 1
        row.TEST = i
        rows.updateRow(row)

Re: Find Duplicates and increment

TerrySilveus — Sun, 12 Dec 2021 04:27:08 GMT

I usually use the approach of sorting by values, then comparing the previous value to the current value:

 rows = arcpy.UpdateCursor("Junk1.dbf","","","","ID A") # sort by ID, ascending order
    
    #Create an empty list
    myList = []
      
    i = 0
    for row in rows:
        if i = 0: #first time around
            value = row.ID
            myList.append(value)
        i += 1
        if row.ID != value: #if a new ID
            value = row.ID
            myList.append(value)
            i = 1
        row.TEST = i
        rows.updateRow(row)

Yeah:)... that's probably how I would have approached it as well, if I knew how to sort, being new to python though put me at a disadvantage.

Re: Find Duplicates and increment

DavidBrett — Sun, 12 Dec 2021 04:27:10 GMT

Thanks guys!! I was able to get these both to work with a little tweaking.

I've settled on this version for now:

   rows = arcpy.UpdateCursor("Junk1.dbf","","","","ID A") # sort by ID, ascending order
    #Create an empty list
    myList = []

    i = 0
    for row in rows:
        if i == 0: #first time around
            value = row.ID
            myList.append(value)
        i += 1
        print value, i
        if row.ID != value: #if a new ID
            value = row.ID
            myList.append(value)
            i = 1
        row.TEST = i - 1
        rows.updateRow(row)

any suggestions on getting that row.TEST = i - 1 to actually start on 0 for the first instance of a dup and then start counting from 0, rather than subtracting 1?

Re: Find Duplicates and increment

TerrySilveus — Sun, 12 Dec 2021 04:27:13 GMT

start i at -1

   rows = arcpy.UpdateCursor("Junk1.dbf","","","","ID A") # sort by ID, ascending order
    #Create an empty list
    myList = []

    i = -1
    for row in rows:
        if i == -1: #first time around
            value = row.ID
            myList.append(value)
        i += 1
        print value, i
        if row.ID != value: #if a new ID
            value = row.ID
            myList.append(value)
            i = 0
        row.TEST = i 
        rows.updateRow(row)

NOTE: unless you use myList elsewhere in your code, doing it this way has eliminated the need for myList

Re: Find Duplicates and increment

DavidBrett — Mon, 11 Apr 2011 19:38:29 GMT

Great! Not really sure why I was using an array to be honest. It seemed like that's what the examples were showing so I just went with it.

Everything seems to working. Next step is to turn it into a tool...

Re: Find Duplicates and increment

DavidBrett — Fri, 09 Dec 2011 15:20:06 GMT

any ideas on getting this run in the field calculator?

Re: Find Duplicates and increment

RichardFairhurst — Fri, 09 Dec 2011 15:25:47 GMT

any ideas on getting this run in the field calculator?

It won't work with the Field Calculator. The Field Calculator will not let you control the record order by sorting it, which this code needs to do.

Re: Find Duplicates and increment

BobbiLay — Fri, 04 Oct 2013 23:04:44 GMT

This is has been extremely helpful. Quick question as I am new to arcpy. If I want to mark ALL repeated IDs with a flag and not necessarily just the second, third, fourth occurrence, how might I accomplish this? For instance, if I had the following list of values:

a
b
c
d
e
e

With the above scripts, I would get

a-0
b-0
c-0
d-0
e-0
e-1

What I want is

a-0
b-0
c-0
d-0
e-1
e-1 or 2 (Doesn't matter as long as I can see that this value is not equal to zero).

My goal is to flag the values that are duplicates, identify the value and then assign a new value to those records. If I split a polygon, for instance, I don't want to maintain the ID of the old one, I want to seek out the value and replace it with two new unique IDs.

Thanks in advance.

Re: Find Duplicates and increment

RichardFairhurst — Sun, 12 Dec 2021 04:27:15 GMT

This is has been extremely helpful. Quick question as I am new to arcpy. If I want to mark ALL repeated IDs with a flag and not necessarily just the second, third, fourth occurrence, how might I accomplish this? For instance, if I had the following list of values:

a
b
c
d
e
e

With the above scripts, I would get

a-0
b-0
c-0
d-0
e-0
e-1

What I want is

a-0
b-0
c-0
d-0
e-1
e-1 or 2 (Doesn't matter as long as I can see that this value is not equal to zero).

My goal is to flag the values that are duplicates, identify the value and then assign a new value to those records. If I split a polygon, for instance, I don't want to maintain the ID of the old one, I want to seek out the value and replace it with two new unique IDs.

Thanks in advance.

It makes no sense to use alphabetic unique IDs for an incremented value. I won't write you the code to support it since with alphabetic IDs you are limited to meaningful values, which violates the concept of a unique ID. If you are using an alpha ID switch it to a numeric unique ID as the only useful type for an autoincremented value.

Personally I just use the Summary Statistics tool to get a count of the field I want to detect duplicates for. I do a join, select for count > 1, break the join and then do whatever update process I want. The Summary Statistics also can give me the highest value of that field to let me identify the next highest value. It does not take much to do this manually.

With a numeric ID, after getting the duplicate selection I would use the autoincrement calculation to begin with the next number and increment it. Here is the autoincrement calculation:

Parser: Python

Show Codeblock: Checked

Pre-Logic Script Code:

rec=0
def autoIncrement():
  global rec
  pStart = 1 #adjust start value to be the next number in the ID series
  pInterval = 1 #adjust interval value, if req'd
  if (rec == 0): 
    rec = pStart 
  else: 
    rec = rec + pInterval 
  return rec

Expression: autoIncrement()

However, with a numeric unique ID this whole process can be done as a standalone Python script. This script does not sort the data, since IDs are meaningless and I want to gain the full speed of the da cursor, which will whip the pants off of any code based on the old cursor model. It does process all records twice, but only updates records where duplicates occur. It also always overwrites Null values with a sequenced number higher than any previously assigned number, whether or not there is more than 1 record that is Null. (Deleted records at the end of the sequential ID series could cause previously assigned and removed IDs to be reused. To correct for that set the "highest" variable to a value that will ensure no prior assigned numbers are ever used by the script).

# Import the arcpy module
import arcpy

pStart = 1 # adjust start value, if required 

pInterval = 1 # adjust interval value, if required

# Initialize the sequence number dictionary and the Route ID variables
seqDict = {}

ID = -1
highest = 0
changed = False

# Assign data and field list variables.
# Customize these variable inputs for your specific data
myData = r"C:\MyPath\MyData.shp"

fields = ["ID"]

# Step 1 - Use an search cursor to get counts of the unique ID numbers
rows = arcpy.da.SearchCursor(myData, fields)
for row in rows:
    if row[0] is None:
        ID = "Null"
    else:
        ID = row[0]
    if ID in seqDict:
        seqDict[ID] = seqDict[ID] + pInterval
    elif ID = 'Null':
        seqDict[ID] = 2
    else:
        seqDict[ID] = pStart
        if highest < ID:
            highest = ID
    del row
del rows
    
# Step 2 - Use an update cursor to update all records with a count greater than 1
rows = arcpy.da.UpdateCursor(myData, fields)
for row in rows:
    if row[0] is None:
        ID = "Null"
    else:
        ID = row[0]
    if seqDict[ID] > 1:
      highest = highest + pInterval
      row[0] = highest
      changed = True
    if changed:
      rows.updateRow(row)
      changed = False
    del row
del rows

If you really have to have an alphabetic unique ID, what is the increment style you propose to use? Whatever increment style you want it involves much more code than it is worth to support it, which is why I won't write the code for you. For that I would just do the Summary Statistics approach I originally suggested. After selecting the records based on the summary count being greater than 1 or having a Null ID value, I would update the alphabetic field manually to fit my style needs rather than doing the autoincrement calculation.