Deselecting original feature from selection of both original and duplicate features?

Question

I have a field which is supposed to hold a unique identifier, however due to issues with Attribute Assistant I have multiple features which have the same unique ID. I want to write a script which will select duplicates and remove the originals from the selection. The duplicated field is FID, the originals will have a lower OID (also, there can be more than one duplicate).

I have used SelectLayersByAttribute_management to select features where there are more than one feature with the same FID:

arcpy.SelectLayerByAttribute_management("MyLayer", "NEW_SELECTION", "FID In (SELECT FID FROM MyFeatureClass GROUP BY FID HAVING Count(*)>1))

This selects both originals and duplicates, but how do I now remove the features which have the original FID value?

The originals will have a lower OID value:

OID   FID
 1    x101    ORIGINAL
 2    x101    DUPLICATE
 3    x102    ORIGINAL
 4    x102    DUPLICATE
 5    x102    DUPLICATE

I'm using ArcMap 10.2.1

Answer 1

Something like the following should loop through the features in your layer using a Search Cursor, and keep track of when a value already exists, adding subsequent feature OIDs to a list to select.

lyr = arcpy.mapping.Layer("MyLayer")
selOID = list()
existingValue = list()

with arcpy.da.SearchCursor(lyr, ['OID@', 'FID']) as cursor:
    for row in cursor:
        if row[1] in existingValue:
            selOID.append(row[0])
        else:
            existingValue.append(row[1])

lyr.setSelectionSet('NEW', selOID)

For more info on lyr.setSelectionSet() see the setSelectionSet() method for the Layer class

---

As the setSelectionSet() isn't available until 10.3, you can use this method to select the list of ObjectIDs. Replace the last line in the code sample above with the last two lines below (the rest is the same code)

lyr = arcpy.mapping.Layer("MyLayer")
selOID = list()
existingValue = list()

with arcpy.da.SearchCursor(lyr, ['OID@', 'FID']) as cursor:
    for row in cursor:
        if row[1] in existingValue:
            selOID.append(row[0])
        else:
            existingValue.append(row[1])

query = 'OBJECTID IN (' + ','.join(map(str, selOID)) + ')' 
arcpy.SelectLayerByAttribute_management(lyr, "NEW_SELECTION", query)

Answer 2

I'd explore Delete Identical (Data Management Toolbox), which removes all but one of the identical records found. As a ArcToolbox tool, you can use it in scripts, or directly from the GUI.

To list the duplicates without deleting them, the Find Identical tool is also available, using the "Output only duplicated records" parameter. However, this outputs a new table, rather than creating a selection in your original data.

Answer 3

This Select By Attribute, Where Clause seems to work, only selecting the DUPLICATE rows when the same FID has ORIGINAL also.

Status = 'DUPLICATE' and Fid in (SELECT FID FROM dups where Status = 'ORIGINAL') and  Fid in (SELECT FID FROM dups where Status = 'DUPLICATE')