3

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:

enter image description here

to this:

enter image description here

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?

1
  • Use an UpdateCursor and set row[0] = None. Don't forget to update the row.. you will need to change your looping because you need the cursor to call .updateRow(). Jun 14, 2017 at 22:41

2 Answers 2

4

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)
6
  • Thank you Emil, that did work. Can you explain how this keeps the ID of the first record but knows to null out the rest? Trying to understand the logic of python. Let's say my table is sorted in descending order--will this change the result?
    – saoirse
    Jun 14, 2017 at 23:11
  • Should you sort this using sql? Or do we assume that it's always read in order for OID@ Jun 14, 2017 at 23:20
  • A set is a list of unique values. The cursor iterates through each row and checks if the value has been added to the set. If so it nulls the value. If not it adds the value to the set and continues. The order is always based on OBJECTID. Jun 14, 2017 at 23:22
  • Emil most of the time rows are returned in order of OBJECTID ascending but this is not guaranteed to be the case, especially with versioned SDE and geodatabase feature classes that are being edited. Jun 15, 2017 at 2:50
  • I've updated the script to make sure the cursor is sorted by OID field. Jun 15, 2017 at 14:54
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.

6
  • Thanks Michael. I also agree with an approach that doesn't rely on the OID being retrieved in a particular order. I applied your code, which ended up erasing all values of the "fm_uniqid" field, not just those with a higher OID (this is a test table, so no problem!). Very new to python--all your help is greatly appreciated!
    – saoirse
    Jun 14, 2017 at 23:54
  • My bad, I went to the effort of creating a query for the update cursor but didn't apply it.. so on the first instance of a duplicate all values were blanked. This time I've checked it with dummy data and is working. Jun 15, 2017 at 0:12
  • thank you! That worked wonderfully. Now I'll spend some time studying how the code works
    – saoirse
    Jun 15, 2017 at 0:27
  • Considering that the OP is using a GDB, wouldn't it be possibly quicker and easier to use an SQL statement on Emil's answer? sql_clause=(None, 'ORDER BY OBJECTID ASC'). arcpy.da.UpdateCursor (fc, checkfield, sql_clause = sql_clause). Jun 15, 2017 at 2:27
  • 1
    @JamesLeversha a fGDB is not a database, it does not implement ORDER BY see gis.stackexchange.com/questions/238153/… and the comment by Vince Jun 15, 2017 at 2:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.