ArcGIS Duplicate Records – Finding Duplicates in Attribute Field

arcgis-10.1arcgis-desktoparcpyattribute-tablepython

I'm looking for duplicate records in dbf files based upon the attribute called 'ID'. I have various dbf files from 500,000 records to 1.5 million and I know there are a host of duplicates.

I would like to add a field 'Duplicate' that says Yes or No (or 1 or 0 is fine) when the ID attribute is present elsewhere. Using the following python script in Field Calculator returns 1 for a duplicate entry and 0 for unique entry;

uniqueList = []
def isDuplicate(inValue):
  if inValue in uniqueList:
    return 1
  else:
    uniqueList.append(inValue)
    return 0
isDuplicate(!FIELD_NAME!)

However, the 1st record of, for example, 5 duplicate IDs will also be returned as a 0 (the subsequent 4 are considered the duplicates). I would need all 5 to be marked as duplicate as the ID exists elsewhere.

Using the following code will give you an incremental count of how many times that ID occurs with 1 meaning the 1st occasion and so forth;

UniqueDict = {}
def isDuplicateIndex(inValue):
    UniqueDict.setdefault(inValue,0)
    UniqueDict[inValue] += 1
    return UniqueDict[inValue]

isDuplicateIndex( !YOUR_FIELD! )

I just want a 1 (or Yes) if the ID of that record exists elsewhere! (ArcGIS version 10.1)

I have seen other answers such as Python script for identifying duplicate records (follow up) but it doesn't quite work.

Best Answer

An alternative solution is to use the existing "summary statistics" tool in ArcGIS, then you join the resulting table based on you ID field. The duplicates will have a "COUNT" larger than 1, so it is then simple to calculate it with your field calculator.

Related Solutions

[GIS] Splitting strings into new rows, retaining geometry

you can use something like below :

import arcpy
searchC =  arcpy.da.SearchCursor(featureclass, ("type","code","area" , "SHAPE@")) #plus other attribute if you need

insertC = arcpy.da.InsertCursor(newfeatureclass, ("type","code","area" , "SHAPE@")) 

for row in searchC:
    A = [x.split(",") for x in row[:-1] ]

    for i in range(min(len(A[0]),len(A[1]),len(A[2]))):
        newRow = [A[0][i] , A[1][i], A[2][i], row[3]]
        insertC.insertRow(newRow)
del insertC

you can use a similar code to create a table instead of a feature class, as mentioned by Vince, so that you do not duplicate the geometries but instead create a one to many relation.

ArcPy – Finding and Marking Identical Features

I think the problem is with your expression, by chaining the two functions like that you are actually creating an equation. I have to say that's the first time I've ever seen anyone attempt to write an expression like that and not surprised it failed. I'm sure some python purist will tell me otherwise but its certainly not a coding style I've seen on any GIS forum or in official help files.

Below is some code you can run in the python console that will identify the values correctly. You just need to ensure tableName and the field names are correct for you.

import arcpy
import collections

tableName = "testTable"
myList = list()

# Read attribute values into counter collection
with arcpy.da.SearchCursor(tableName,["Attribute"]) as cursor:
    for row in cursor:
        myList.append(row[0])
myCounter = collections.Counter(myList)
print(myCounter)

# Write counter dictionary back to table, if count is 2 or greater it is a duplicate
with arcpy.da.UpdateCursor(tableName,["Attribute","Identical"]) as cursor:
    for row in cursor:
        attVal = row[0]
        count = myCounter[attVal]
        if count >= 2:
            row[1] = 1 # Duplicate, you could swap out 1 with the variable count to store the number of times it was duplicated
        else:
            row[1] = 0 # Occurs only once, not a duplicate

        cursor.updateRow(row)

print("FINISHED!")

Related Question