2

I'm having trouble deleting duplicates from multiple tables in a geodatabase. I have a field "facility_id" which should be unique, but I have multiple entries and need to delete all but one. I've started a python script to delete the duplicates but I'm not sure how to finish it. I tried a for loop to find all facility_ids in the values list and check for membership in the duplicate values list, but when I use cursor.deleteRow, nothing happens.

import arcpy, os
from arcpy import env

targetDB = r"C:\data\Temp\436e0250ef1748af92e7f1f75b5a0deb.gdb"
arcpy.env.workspace = targetDB
for table in arcpy.ListTables():
newTable = arcpy.Describe(table).basename
    if "ATTACH" not in newTable:
        with arcpy.da.UpdateCursor(newTable, "facility_id") as cursor:
            values = [r[0] for r in cursor]
            duplicates = [value for value in values if values.count(value) > 1]
            print "Original Table Values: " + str(newTable) + ": " + str(values)
            print "Duplicate Table Values: " + str(newTable) + ": " + str(duplicates)
            if values in duplicates:
                cursor.deleteRow()

output:

Original Table Values: ElecConduit: [u'PS145', u'PS022', u'PS022', u'PS108', u'PS134', u'PS999']

Duplicates Table Values: ElecConduit: [u'PS022', u'PS022']

Original Table Values: ElecLighting: [u'PS108', u'PS108', u'PS108']

Duplicates Table Values: ElecLighting: [u'PS108', u'PS108', u'PS108']

1
  • @Jim's code below is a good solution. Cursors as I understand them are typically read once then you need to reset them. As I read your code you have "used up" your cursor to populate the list values. Then you try to use it later to delete a row but you have already passed through the entire cursor. This is probably why your code is not working? Jim's code only ever passes through it once so its a better solution. – Hornbydd Mar 23 '16 at 23:17
4

I think it's great that you're using list comprehensions as part of this task, but if I were to run into this same situation, I'd probably want to dumb the script down to a more explicit, step-by-step state. The following changes should delete all duplicate rows for any given 'facility_id' past the first occurrence of a unique 'facility_id' value, across all tables not named "ATTACH" in your target .gdb:

import arcpy, os
from arcpy import env

targetDB = r"C:\data\Temp\436e0250ef1748af92e7f1f75b5a0deb.gdb"
arcpy.env.workspace = targetDB
duplicate_check_list = [] # this is a global list recording known facility_id values for duplicate value checking purposes
for table in arcpy.ListTables():
    newTable = arcpy.Describe(table).basename
        if "ATTACH" not in newTable:
            with arcpy.da.UpdateCursor(newTable, "facility_id") as cursor:
                for row in cursor:
                    if row[0] in duplicate_check_list: # check the current facility_id against the values already present in the list
                        cursor.deleteRow()
                    else:
                        duplicate_check_list.append(row[0])

Best Luck with this.

4
  • Thanks for your reply Jim. I'll take a look at it later today and let you know what happens. – user7278 Mar 24 '16 at 12:01
  • If it still doesn't work, the case your various text variables are in could be a problem. You can fix that by making everything the same case. For example, instead of if "ATTACH" not in newTable, put if "ATTACH" not in newTable.upper(). Since your facility_id is text, you could do the same thing with the duplicate list, if it is a problem. – RHB Mar 24 '16 at 12:09
  • @Jim Thanks again. I've noticed a bug with the script though. The duplicate_check_list contains the facility_ids from all of the different tables, so when I walk through each table, if the facility_id was added to the duplicate_check_list from an earlier table, it deletes the row in the subsequent table. Does that make sense? It's ok for what I'm doing now because I've changed facility_id to a GlobalID ,but it's just something I noticed. – user7278 Mar 24 '16 at 16:44
  • @user7278 - You are absolutely correct, the duplicate_check_list persists and affects all tables in the stack; based on the original question I went ahead and assumed this was the desired effect. To change this behavior to only check for duplicates in the context of one table at a time, not global, you could move the duplicate_check_list = [] variable declaration to sit between the opening of your with statement and the start of the for row in cursor: loop. Good catch! – Jim Mar 24 '16 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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