Identify duplicate attributes and keep value of earliest record

Question

I have rows in a table that have duplicate ID values in one field. I would like to identify these duplicates, keep the ID value of the earliest entry, and null out the ID field for the other duplicates. Since there is no date of entry field, I am relying on the ObjectID to identify the earliest entry (the lowest number). I am using a file geodatabase table, and operating in Arc 10.3.

For example:

999999 repeats twice, therefore I want to keep the ID for ObjectID 1, but null out the fm_uniqid for ObjectID 2. The same goes for 999998, which is repeated 3 times. I did the following manually.

from this:

to this:

The following code (source), helps me identify the duplicates, but now I'm wondering about my next steps.

import arcpy

fc = r"C:\Users\YCZ\testgdb.gdb/test_table"
checkfield = "fm_uniqid"

def checkDuplicate(fc, checkfield):
    vals = [row[0] for row in arcpy.da.SearchCursor(fc, checkfield)]
    dct = {x:vals.count(x) for x in vals}
    for k, v in dct.iteritems():
        if v > 1:
            print k, v

if __name__ == '__main__':
    checkDuplicate(fc, checkfield)

I am looking for advice on how to continue before I attempt any code. I believe the best way may be to put these identified duplicates into a list, then iterate through each value individually: Select by attribute for duplicated fm_uniqueIDs, identify the lowest objectID value, and set the fm_uniqid of all rows with an objectID greater than the lowest value, to null.

This seems quite tedious for a large dataset (40,000+ records)--are there recommendations of other approaches?

Answer 1

Make use of an UpdateCursor and a set.

import arcpy

fc = r"C:\Users\YCZ\testgdb.gdb/test_table"
checkfield = "fm_uniqid"

ids = set ()
oidFld = arcpy.Describe (fc).OIDFieldName
with arcpy.da.UpdateCursor (fc, checkfield,
                            sql_clause =
                            (None, "ORDER BY {}".format (oidFld))) as curs:
    for row in curs:
        check = row [0]
        if check in ids:
            row [0] = None
            curs.updateRow (row)
        else:
            ids.add (check)

If you are using a data source that doesn't support sql clauses:

import os
import arcpy

idDi = {}
with arcpy.da.SearchCursor (fc, [checkfield, "OID@"]) as curs:
    for check, oid in curs:
        try:
            checkVal = idDi [check]
        except:
            idDi [check] = oid
            continue
        if oid < checkVal:
            idDi [check] = oid

oids = set (idDi.values ())
workspace = os.path.basename (fc)
oidFld = arcpy.AddFieldDelimiters (workspace, arcpy.Describe (fc).OIDFieldName)
oidStr = ", ".join (map (str, oids))
sql = "{} NOT IN ({})".format (oidFld, oidStr)

with arcpy.da.UpdateCursor (fc, checkfield, sql) as curs:
    for row in curs:
        row [0] = None
        curs.updateRow (row)

Answer 2

Emil's answer is technically correct but, in rare circumstances, the OIDs are not retrieved in ascending order. That is the rows are not retrieved in any particular order, that the rows generally are retrieved in ascending OID order is not to be relied on.

This should help retain the lowest OID with the value and blank any higher duplicates:

import arcpy

fc         = r"C:\Users\YCZ\testgdb.gdb/test_table"
checkfield = "fm_uniqid"
desc       = arcpy.Describe(fc) # get the OID field from the feature class
OIDfield   = desc.OIDFieldName
BlankVal   = None
arcpy.AddMessage("Using field {} as OID".format(OIDfield))

dct = {}
with arcpy.da.SearchCursor(fc,[OIDfield,checkfield]) as sCur:
    for Row in sCur:
        if not Row[1] in dct:
            # the value is not in the dict so add it
            arcpy.AddMessage("Adding value {} with OID {} to dict".format(Row[1],Row[0]))
            dct[Row[1]] = Row[0] # set in the format dct[checkfield] = OID
        else:
            # the value IS in the dict
            if dct[Row[1]] < Row[0]:
                # the lower number is already in the key, blank this row
                arcpy.AddMessage('Value {} has a lower OID {} than {}'.format(Row[1],dct[Row[1]],Row[0]))
                Query = '{} = {}'.format(OIDfield,Row[0])
                with arcpy.da.UpdateCursor(fc,[OIDfield,checkfield],Query) as UCur:
                    # open a cursor with just one row and blank the checkfield
                    for URow in UCur:
                        URow[1] = BlankVal
                        UCur.updateRow(URow)
            else:
                # this number is lower, blank the other one
                arcpy.AddMessage( 'Value {} is a lower OID {} than {}'.format(Row[1],dct[Row[1]],Row[0]))
                Query = '{} = {}'.format(OIDfield,dct[Row[1]])
                dct[Row[1]] = Row[0] # overwrite in the format dct[checkfield] = OID
                with arcpy.da.UpdateCursor(fc,[OIDfield,checkfield],Query) as UCur:
                    # open a cursor with just one row and blank the checkfield
                    for URow in UCur:
                        URow[1] = BlankVal
                        UCur.updateRow(URow)

As with any major change keep a backup just in case the values are not really what you intended.