topic Re: delete duplicates records based on field in Python Questions

delete duplicates records based on field

2Quiker — Sat, 11 Dec 2021 13:49:30 GMT

I need to be able to delete duplicate records but keep one only if it has a certain attribute in a certain field in a table. so i need to keep one of each "Acct" row but only if it has "01" in the Group _code. I need to be able to do this in arcpy python because i get this table weekly so i would like to schedule this task. Attached is a pic of the table i am working with.

I think i can iterate the "group_code" field and append the value and "Acct" to a dictionary.

I have the following but i am not sure how to select and remove duplicates. I would appreciate some help with some code.

import arcpy  
from arcpy import env  
env.overwriteOutput = 1  
env.workspace = r"C:\GIS\LandValue.gdb"  
  
fc = "LandValue3"  
  
dict = {}  
  
with arcpy.da.SearchCursor(fc, ["Acct", "group_code"]) as cursor:  
    for row in cursor:  
        dict[row[0]] = row[1]  
  
del cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: delete duplicates records based on field

JoshuaBixby — Sat, 11 Dec 2021 13:49:33 GMT

I haven't tested the following, but I think it will work. I think using a Python set is the best approach since the lookups are quicker than lists, although you could use a dictionary as well. I added the extra "01" check because it seemed like you only want to look at records with that group code. Also, I assumed the group code are strings.

import arcpy  
from arcpy import env  
env.overwriteOutput = 1  
env.workspace = r"C:\GIS\LandValue.gdb"  
  
fc = "LandValue3"  
  
accts = set()
  
with arcpy.da.UpdateCursor(fc, ["Acct", "group_code"]) as cursor:  
    for acct, group_code in cursor:  
        if group_code == "01":
            if acct in accts:
                cursor.deleteRow()
            else:
                accts.add(acct)
  
del cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: delete duplicates records based on field

NeilAyres — Thu, 13 Oct 2016 16:47:32 GMT

Nice approach Joshua. Cool use of a set.

Re: delete duplicates records based on field

2Quiker — Thu, 13 Oct 2016 17:44:50 GMT

Darn i just realized that that i need it to keep just one of the Acct with 01 in the group_code but the one with the highest value in the "value" field.

As you can see on the attached table there might be duplicate values.

Re: delete duplicates records based on field

MitchHolley1 — Sat, 11 Dec 2021 13:49:36 GMT

I built a similar tool that deletes duplicate geometries. It looks a little like this:

#Variables
feature = r'...path to feature...'
field = "field"
field_set = set()
count = 0

with arcpy.da.UpdateCursor(feature, [field]) as cursor:
        for row in cursor:
            if row[0] not in field_set:
                field_set.add(row[0])
            else:
                count+=1
                cursor.deleteRow()‍‍‍‍‍‍‍

arcpy.AddMessage('\n'+str(count) + ' duplicates removed from the ' + str(field) + ' field.\n')‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I haven't tested it on a field duplicate, only the 'SHAPE@XY' token. But, I successfully got it to work with the geometries.

Re: delete duplicates records based on field

2Quiker — Thu, 13 Oct 2016 21:22:11 GMT

Thanks to both of you Joshua and Mitch for sharing some code it does delete the duplicates but i need it to only keep one of the Acct with 01 in the group_code field and the highest value in the value filed. unless i am not seeing something in your code...?

Re: delete duplicates records based on field

MitchHolley1 — Sat, 11 Dec 2021 13:49:39 GMT

#Variables
feature = r'...path to feature...'
field = "field"
field_set = set()
count = 0

with arcpy.da.UpdateCursor(feature, ['ACCT','group_code']) as cursor:
        for row in cursor:
            if row[0] not in field_set:
                  if str(row[1]) != "01":
                      field_set.add(row[0])
            else:
                count+=1
                cursor.deleteRow()

arcpy.AddMessage('\n'+str(count) + ' duplicates removed from the ' + str(field) + ' field.\n')

This script will delete a duplicated record of the 'ACCT' field, ONLY WHERE 'group_code' does not equal "01".

Is this what you're looking to do? Also, I'm not sure about the highest value thing... that's tricky.

Re: delete duplicates records based on field

2Quiker — Fri, 14 Oct 2016 14:40:01 GMT

Partly yes, thank you for post

Re: delete duplicates records based on field

JoshuaBixby — Sat, 11 Dec 2021 13:49:42 GMT

Although what you want can be done purely in Python, sometimes mixing Python and SQL makes sense. I have updated the code to add the third column and sort the cursor before processing it. Sorting value in descending order should ensure the first record found, which will be the one that is kept, will have the highest value.

import arcpy  
from arcpy import env  
env.overwriteOutput = 1  
env.workspace = r"C:\GIS\LandValue.gdb"  
  
fc = "LandValue3"  
  
accts = set()
postfix = "ORDER BY group_code, acct, value desc"
  
with arcpy.da.UpdateCursor(fc, ["Acct", "group_code", "value"],
                           sql_clause=(None, postfix)) as cursor:  
    for acct, group_code, value in cursor:  
        if group_code == "01":
            if acct in accts:
                cursor.deleteRow()
            else:
                accts.add(acct)
  
del cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: delete duplicates records based on field

2Quiker — Mon, 17 Oct 2016 16:38:17 GMT

Joshua thanks for the replay and updated code, but when i run the code it runs fun i don't get any error's but nothing happens to the table, no records are delete.

Re: delete duplicates records based on field

JoshuaBixby — Mon, 17 Oct 2016 17:19:22 GMT

I will have to do some tinkering later today and see what is going on.

I am not sure what is going on. I just recreated the table from your screenshot and ran my code snippet against it, and everything worked as expected. The table I recreated was pruned from 29 records to 13 records.

What version of ArcGIS are you running? Are you running the script in the interactive Python window or from an IDE or standalone interpreter? What is the backend storage format, file geodatabase?

Re: delete duplicates records based on field

2Quiker — Tue, 18 Oct 2016 14:03:37 GMT

I am on ArcGIS 10.3.1, i am runign the script on python 2.7.8 IDLE, the table is in a file geodatabase.

I have attached a portion of table i am working with. Thanks.

Re: delete duplicates records based on field

JoshuaBixby — Tue, 18 Oct 2016 15:28:02 GMT

I am running 10.5 PreRelease. When I run the code in 2.7.10 IDLE using the attached/uploaded data, it works, removes about 50 or so records (honestly, I wasn't sure it was going to work with the data in DBF files but it did). The only thing I can think about is a bug in 10.3.1 that has been fixed since.

Re: delete duplicates records based on field

2Quiker — Tue, 18 Oct 2016 17:36:02 GMT

What bug are you referring to, is there a patch for this bug? any other way around this?

Re: delete duplicates records based on field

JoshuaBixby — Tue, 18 Oct 2016 20:11:06 GMT

I wasn't referring to a specific bug but supposing that one exists. You can look over the ArcGIS 10.4 Issues Addressed List and ArcGIS 10.4.1 Issues Addressed List, but I didn't see anything on those lists that jumped out at me. That said, not every bug that gets addressed is listed in those documents. It might be worth contacting Esri Support to see if they can track down a known bug and tell you what release it was addressed.

Re: delete duplicates records based on field

2Quiker — Tue, 18 Oct 2016 21:39:54 GMT

It was my pc, i restarted my pc and recreated the file geodatabase and ran the script and it did what it was suppose to do.

Sorry about that and thanks for the help!