Identifying duplicate attributes in field using QGIS

Question

I have a point shapefile with thousands of points. It has an "ID" code field that is supposed to be unique. Every now and then the data entry clerk wrongly type the "ID" creating duplicates. Right now I'm manually scrolling the field to find the duplicate.

Is there another way to do this using the Search Query Builder?

Answer 1

Another graphical, dynamic and most importantly simple way to detect duplicate attributes: use QGIS's expression builder.

Highlight duplicates in attribute table:

Suppose FieldWithDuplicates is the attribute containing duplicates ( TreeID in the screenshot below). Enable Conditional Formatting (see red arrow below) with the following condition:

count(1,"FieldWithDuplicates") > 1

To group all duplicates at the top, right click the column, select Sort
Enter the above expression without the >1, and deselect Sort ascending.

Highlight features with duplicate attributes on canvas:

You could add a new symbol or label with the filter set to the above condition.

And of course you can enable a data derived override based on the the same.

For example, if you want to highlight labels for features with a duplicate attribute, you can set it to draw a label background with the following override:

count(1,"FieldWithDuplicates") > 1

to achieve something like the following

In both situations, of course, once you either delete or change the duplicate attributes, the formatting/styling updates instantly.

Answer 2

Use Group Stats plugin and set the ID as a field classification. You can see how many times each value has been entered in 'count' column.

Answer 3

A quick (although inelegant) way to do this is to go into Layer properties, select Style - Categorized using the column that you're interested in. Apply this, then right click on the layer in the layers window and check the Show Feature Count checkbox. Then expand the layer in the layers window and you can immediately see how many times each value has been entered.

Answer 4

This is a good question onto which I just stumbled myself. I don't like any of the answers which have been given so far. I have a valid dataset with unique IDs that are non sequential and non integer. The problem is that the dataset contains single geometries but some boundaries are multi geometries in nature. My task is to identify and union these geometries.

I recommend to use the DB Manager and SQL for this kind of work. The DB Manager is now part of QGIS. You need to export your data into either PostGIS or a SpatiaLite dataset. SpatiaLite should be the file based data format of choice anyways.

Now you can use count(), group by, and order by as you like and should be able to solve this and other issues rather quickly.

Answer 5

I'm wondering why didn't anybody recommend to use only the Select By Expression tool with the query:

COUNT(1, "attribute")>1

Then all features with repeated attribute will be selected, you can then filter, save the selection or do whatever you need to.

Answer 6

If the IDs are consecutive, I would add a new temporary column with unique values like @Ship.shp suggested and then use the query builder to search for ID != uniqueID.

That would return the duplicates directly. After fixing the original IDs, remove the extra column or repeat the whole process as needed — it is not clear what kind of pattern your IDs must match. If they just need to be unique, note the last value first and you can then edit the bad IDs in one iteration, just bumping the number as you go.

Answer 7

Here is my script for removing features with same ID's. It takes first feature with more than one index attribute and writes it to new feature class.

#Definition of inputs and outputs
# Written by: Gregor Skrt 
#==================================
##[Example scripts]=group
##input=vector
##unique_field=field input
##output=output vector

#Algorithm body
#==================================
from qgis.core import *
from PyQt4.QtCore import *
from processing.core.VectorWriter import VectorWriter

# "input" contains the location of the selected layer.
# We get the actual object, so we can get its bounds
layer = processing.getobject(input)
provider = layer.dataProvider()
fields = provider.fields()
writer = VectorWriter(output, None, fields, provider.geometryType(), layer.crs() )

inFeat = QgsFeature()
outFeat = QgsFeature()
inGeom = QgsGeometry()
nElement = 0
values = {}

value_field_index = layer.fieldNameIndex(unique_field)

feats = processing.getfeatures(layer)
nFeat = len(feats)

for inFeat in feats:
    progress.setPercentage(int((100 * nElement)/nFeat))
    nElement += 1
    inGeom = inFeat.geometry()
    attrs = inFeat.attributes()
    value = attrs[value_field_index]
    
    if value not in values:
    #to ne vem ce bo drzalo ???
        values[value]=[]
    outFeat.setGeometry(inGeom)
    outFeat.setAttributes(attrs)
    writer.addFeature(outFeat)
del writer

Answer 8

The answer by caiohamamura solved a related problem: After pasting in features from another layer, the fid field (supposed to be unique) had some duplicates. I was not allowed to save the layer!

In the attribute table, at the bottom left of the page in the filter dropdown menu, I found the Advanced Filter (Expression) and entered:

Count(1, "fid")>1

What I would like to add to the topic is this: when editing the fid values, you have to hit "apply" in the lower right hand corner of the attribute table window for the filter to update. Then your list gets whittled down as you go, showing only remaining duplicates.

Answer 9

You can use the split by attribute and end up with a separate table for each value.

I like Rayo's suggestion though, except statist doesn't quite work like I thought it did.
It does give a count of unique values but doesn't help with what those values are.
Another software might add a count field and allow you to export it to CSV or some spreadsheet format.

My suggestion for the split layer by attribute is in the vector management tools

Split your data on any 1 field and you will have your counts. Way more inelegant than ship.ship's solution