3

I have a feature class where there are multiple values duplicated in a row, i.e. SRNumber and RESOLUTION_CODE. I would like to delete all rows where my SRNumber and RESOLUTION_CODE are found in multiple rows. I have got a good start with this script, everything works aside from actually deleting the rows. How can I fix this so that my duplicate rows are deleted?

So if SRNumber = 1-23456 and Resolution_Code = 'A' and appears twice, I would like to delete one.

import arcpy

     in_table = r"ServiceRequest.DBO.SO_Dead_Animal"
     fields = ["NumberCYLA","RESOLUTION_CODE"] #replace with own
     curStrings = arcpy.da.SearchCursor(in_table,fields,"NumberCYLA is not null")

     stringsKeysList = []
     numbersValuesList = []
     tableDict = {}

     for row in curStrings:
         print row[0],row[1]
         stringsKeysList.append(row[0])
     del curStrings
     uniqueStringsList = list(set(stringsKeysList)) #only unique values from Strings field

     for uniqueString in uniqueStringsList:
         emptyTempValues = []
         curValues = arcpy.da.SearchCursor(in_table,fields,"""NumberCYLA = '{0}'""".format(str(uniqueString)))
         for row in curValues:
             print row[1]
             emptyTempValues.append(row[1])
             uniqueEmptyTempValues = list(set(emptyTempValues))
         tableDict[uniqueString] = uniqueEmptyTempValues

     print tableDict #a dictionary with string:values
     #{u'String_2': [-4, -2],
     #u'String_3': [None, -3],
     #u'String_56': [-1],
     #u'String_1': [-1],
     #u'String78': [-1]}

     rowsToDelete = [k for k, v in tableDict.iteritems() if v == [-1]] #find which rows to delete
     print "list: ", rowsToDelete
     stringRows = ","
     updRows = stringRows.join(rowsToDelete)
     updRows = updRows.replace(",","','")
     print updRows #update the string to be used with proper syntax in where clause

     where_clause = """NumberCYLA in ('{0}')""".format(updRows)
     upd_cur = arcpy.da.UpdateCursor(in_table,fields,where_clause)
     for row in upd_cur:
         print row[0],row[1]
         del rowsToDelete
  • Have you read this? gis.stackexchange.com/questions/16913/… – brokev03 Aug 13 '15 at 0:46
  • +1 for dictionary. The value should be the list of FIDs. When you've done with dictionary, make a new tuple and populate it with 1st items in dictionary values. Use query [FID] not in NewTuple to select ones to be deleted – FelixIP Aug 13 '15 at 1:52
  • Could you please provide a simplified example of the input data and the intended output? – Aaron Aug 28 '16 at 2:00
  • @FelixIP delele duplicated based on FID can cause lot of error in shapefile because FID is not safe. You need create an "uuid" and update this column before delete features and next delete this temp column. In all other format there is no problem – GeoStoneMarten Jan 17 at 20:17
  • One can select rows using any query, oid including. Use delete features tool will delete them in one go. – FelixIP Jan 17 at 21:35
2

If you're wanting to keep one row of each duplicate, make use of Delete Identical. Use your two fields as your field inputs.

arcpy.DeleteIdentical_management (r"c:\example\example.gdb\example", ["SRNumber", "Resolution_Code"])

If you would like to delete all duplicates, determine the duplicates with a little list comprehension, and then use an update cursor to delete your rows.

from arcpy import *

inFc = r"c:\example\example.gdb\example"

fld1 = "SRNumber"
fld2 = "Resolution_Code"

#List of values as tuples
rows = [(val1, val2) for val1, val2 in da.SearchCursor (inFc, [fld1, fld2])]

#Duplicate tuples
duplicateRows = [row for row in rows if rows.count (row) > 1]

#UpdateCursor to delete duplicates
with da.UpdateCursor (inFc, [fld1, fld2])] as cursor:
    for val1, val2 in cursor:
        tup = (val1, val2)
        if tup in duplicateRows:
            cursor.deleteRow()
  • Delete Identical is only for avenced user licence. But you can use your second script with a search cursor intermediate function. find all duplicated fid. – GeoStoneMarten Jan 17 at 10:26
0

Because I need use same solution, I send here my script.

import arcpy 
from collections import Iterable, defaultdict

def flatten(items):   
  """Yield elements of iterable or element"""   
  for x in items:
    if isinstance(x, Iterable) and not isinstance(x, (str, bytes)):
      for sub_x in flatten(x):
        yield sub_x
    else:
      yield x


def dedup_rows(in_fc, fields, keep_duplicated = 1):   
  """
  Remove duplicate rows based on multiple fields with safe OID table or featureclass.

  in_fc: featureclass or table   
  fields: need to be a tuple for only one value use ("MyField",)   
  keep_duplicated: number to conserve duplicated if 0 all duplicated row are deleted, if 1 or more is set you don't delete this fist duplicated entries
  Limitation : Don't work with Shapefile! You need save your shape in_memory or scratchworkspace before and overwrite you shapefile if you need work with this file type   
  """

  _fields = ("OID@",) + fields

  d = defaultdict(list)   
  with arcpy.da.SearchCursor(in_fc, _fields) as cur:
    for r in cur:
      d[r[1:]].append(r[0])

  #values are duplicated   
  dd = {k: v for k, v in d.iteritems() if len(v) > 1}   

  x = keep_duplicated
  # list oids you want delete !  
  oids = sorted(flatten([v[x:] for v in dd.values() if len(v[x:]) > 0]))

  with arcpy.da.UpdateCursor(in_fc, ["OID@"]) as cur:
    for r in cur:
      if r[0] in oids:
        cur.deleteRow() # byebye duplicated ;-)

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.