ArcPy UpdateCursor deleting duplicate records?

Question

I am working with .dbf files. I have two columns of data that have duplicates. A column of parcel numbers with duplicates and a column of letters that are duplicated. I want to delete all parcel numbers first and then delete all duplicate letter numbers after. Right now, I'm just repeating code twice.

Is there an easier way?

I'm brand new at this so that's why the code is repeated and looks so chunky. It's code that I mostly found off this site previously.

Here's what I'm working with:

import arcpy 
import os 
from arcpy import env

inShapefile = "M:/GIS/LEA Maps/2019/List/GISLEA.dbf" 
checkField = 'PARCELNO' 
updateField = 'dplicate'

def findDupes(inShapefile, checkField, updateField):
    with arcpy.da.SearchCursor(inShapefile, [checkField]) as rows:
        values = [r[0] for r in rows]

    with arcpy.da.UpdateCursor(inShapefile, [checkField, updateField]) as rows:
        for row in rows:
            if values.count(row[0]) > 1:
                row[1] = 'Y'
            else:
                row[1] = 'N'
            rows.updateRow(row)

if __name__ == '__main__':
    fc = r'M:/GM/LEA/LEADR.dbf'

    findDupes(fc, checkField, updateField)

arcpy.DeleteIdentical_management(fc, 'PARCELNO')


inShapefile = 'M:/GM/LEA/LEADR.dbf'
checkField = 'LetterOrde'
updateField = 'dplicate'


def findDupes(inShapefile, checkField, updateField):
    with arcpy.da.SearchCursor(inShapefile, [checkField]) as rows:
        values = [r[0] for r in rows]

    with arcpy.da.UpdateCursor(inShapefile, [checkField, updateField]) as rows:
        for row in rows:
            if values.count(row[0]) > 1:
                row[1] = 'Y'
            else:
                row[1] = 'N'
            rows.updateRow(row)

if __name__ == '__main__':
    fc = 'M:/GM/LEA/LEADRlo.dbf'

    findDupes(fc, checkField, updateField)

arcpy.DeleteIdentical_management(fc, 'LetterOrde')

Answer 1

The code isn't bad when you don't have a lot of experience. For future reference, when you're calling a function more than once you don't need to reproduce it. However in this case you can do this in one sweep using just the update cursor.

This is assuming that the columns of duplicates are in the same file, though looking at your code it appears you've got three different datasets.

First the code:

from collections import defaultdict

import arcpy


input_dataset = r'path\to\the.dbf'

check_fields = ['PARCELNO', 'LetterOrde']
found_values = defaultdict(set)


with arcpy.da.UpdateCursor(input_dataset, check_fields) as cursor:

    for row in cursor:
        for i, column in enumerate(check_fields):
            if row[i] in found_values[column]:
                cursor.deleteRow()
                break
        else:
            for column, value in zip(check_fields, row):
                found_values[column].add(value)

To break down what's happening, I'll just grab a few points of interest:

found_values = defaultidct(set)

The defaultdict is like a Python dictionary, except that if you try and get back a key that doesn't exist instead of raising a KeyError it will bring back a default value of the type set (in this case).

for i, column in enumerate(check_fields):

The enumerate function wraps any iterable (like your list) and gives you back a number at each step of the for loop along with the value. For example, the first iteration would get you the value (0, 'PARCELNO'). It then assigns the value 0 to i and 'PARCELNO' to column.

if row[i] in found_values[column]:
    cursor.deleteRow()
    break

The cursor returns each row as a tuple, with the position of each value based on the order of the columns from check_fields. This means you can use the i value from the enumerate to get the value from each row.

The value is then checked against the previously found values from that column. If it's found it deletes the row, and then the break stops checking other columns.

else:
    for column, value in zip(check_fields, row):
        found_values[column].add(value)

This is a less commonly known feature of Python - if the for loop completes it executes the else statement. If it doesn't (because of the break) then it gets skipped. So use the zip to line up the column heading and the value from every column of only the rows you're keeping, and store them to check against future rows.

To illustrate this, imagine you have some parcels with data in the following form:

| PARCELNO | LetterOrde |
|----------|------------|
|      123 | A          |
|      123 | B          |
|      456 | A          |
|      456 | B          |
|      789 | B          |
|      789 | C          |

On the first iteration of the loop, you haven't seen either the PARCELNO or the LetterOrde, so store both value and keep that row. So the output dataset looks like:

| PARCELNO | LetterOrde |
|----------|------------|
|      123 | A          |

And the found_values looks like:

{'PARCELNO': {123, }, 'LetterOrde': {'A', }}

In the second iteration,the PARCELNO is 123, which is already in found_values, so that row is skipped (and importantly the B isn't added to the found values yet).

In the third iteration the PARCELNO is 456, but the LetterOrde A has already been seen. So again, this row is skipped. Again, the output and found values remain unchanged.

Finally, in the fourth iteration you get a new value, so the output dataset looks like:

| PARCELNO | LetterOrde |
|----------|------------|
|      123 | A          |
|      456 | B          |

And found_values looks like:

{'PARCELNO': {123, 456}, 'LetterOrde': {'A', 'B'}}

Again the fifth iteration detects a duplicate, so the final value is added with the last row in the 6th iteration, and the output dataset looks like

| PARCELNO | LetterOrde |
|----------|------------|
|      123 | A          |
|      456 | B          |
|      789 | C          |

And found_values finishes by looking like:

{'PARCELNO': {123, 456, 789}, 'LetterOrde': {'A', 'B', 'C'}}

Answer 2

Dictionaries are useful when dealing with duplicates. Can also be used to find smallest or largest values if the values are sorted since last dictionary key duplicate is the only one being kept.

import arcpy
fc = r'M:/GIS/LEA Maps/2019/List/GISLEA.dbf'
fieldlist = ['OID@','PARCELNO','LetterOrde']

all_values = [i for i in arcpy.da.SearchCursor(fc,fieldlist)] #List of lists holding all values
d = {i[1]:[i[0],i[2]] for i in all_values} #Dictionary can only have unique keys so duplicate PARCELNO are dropped
d2 = {v[1]:v[0] for k,v in d.items()} #d.iteritems() in py2. Dropping duplicate LetterOrde
oids_to_keep = tuple(d2.values()) #Tuple of oids to keep

#Delete oids not in oids_to_keep
sql = "{0} NOT IN{1}".format(arcpy.AddFieldDelimiters(arcpy.Describe(fc).OIDFieldName), oids_to_keep)
with arcpy.da.UpdateCursor(fc,fieldlist,sql) as cursor:
    for row in cursor:
        cursor.deleteRow()