4

I have a feature class with two fields of interest: "name" and "identifier" (Original Data; Figure 1). Note that there are several duplicate rows in the table and the table may or may not be sorted, as it is in this example.

I'm interested in selecting, and/or removing, duplicate rows if the identifier is a duplicate within each "name" group (e.g. A, B, C...). In the example, name "C" has two identifiers "abcd", so the duplicate would need to be selected and/or removed. I'm working with ArcGIS Desktop Basic or QGIS and python. I'm thinking that referencing a dictionary or some fancy SQL expression might do the trick, but hoping there may be an easier solution. How can I find duplicate records based on multiple fields?

enter image description here

Figure 1

7

I was faced with a similar problem today, but I ended up using FME which was really quick and easy.

What I was thinking was an update cursor which loops through the rows. Store the row in a list. The first time the unique pair is encountered it is added to the list. Go to the next row. If the name/identifier is in your list for the next row, delete the row. Otherwise, add the name/identifier to the list.

I've just tested it and it works:

uniqueList = []
with arcpy.da.UpdateCursor(fc, ["name","identifier"]) as uCur:
    for row in uCur:
        if row in uniqueList:
            uCur.deleteRow()
        else:
            uniqueList.append(row)
  • I tend to favor a set() to hold values when determining if a value is contained within. From my own experience, it is much faster for large amounts of data. – Richard Morgan Nov 10 '16 at 22:17
  • 1
    @RichardMorgan, agreed. In fact, if you look at the edit history of my answer you'll see that I originally had the idea to use sets with dictionaries. I ended up using a list for simplicity, but yes, a set would be more efficient. – Fezter Nov 10 '16 at 22:19
4

Analogously, to remove your duplicate records using QGIS, you can run this code in the QGIS Python console (having selected your layer in the Table of Contents, a.k.a. Layer tree):

uniqueList, idsToDelete = [], []
lyr = iface.activeLayer()
request = QgsFeatureRequest(). \
              setFlags( QgsFeatureRequest.NoGeometry ). \
              setSubsetOfAttributes( ['name', 'identifier'], lyr.fields() )
for f in lyr.getFeatures( request ):
    if f.attributes() in uniqueList:
        idsToDelete.append( f.id() )
    else:
        uniqueList.append( f.attributes() )

lyr.dataProvider().deleteFeatures( idsToDelete )

This is another version of the code using list comprehensions (although you'll get a Warning with this approach):

uniqueList = []
lyr = iface.activeLayer()
request = QgsFeatureRequest(). \
              setFlags( QgsFeatureRequest.NoGeometry ). \
              setSubsetOfAttributes( ['name', 'identifier'], lyr.fields() )
idsToDelete = [ f.id() if f.attributes() in uniqueList else uniqueList.append( f.attributes() ) for f in lyr.getFeatures(request) ]
lyr.dataProvider().deleteFeatures( idsToDelete )

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.