Removing duplicates from the Attribute table by expression in QGIS

Question

I have some duplicates in my data attribute table, you can see below:

enter image description here

I used the following formula:

count(1, "Site ID") > 1

based on this question: Identifying duplicate attributes in field using QGIS

But as you can see I have just the duplicate values selected. When I click on any of them in order to delete it, the rest of the selected stuff disappears (is deselected).

I am wondering about the option, which would allow me to remove these duplicates by some expression.

A similar problem was considered in these threads:

• Removing duplicate records in the Attribute table using QGIS
• Selecting duplicate records in PyQGIS

where the Python approach was suggested.

I personally think, the delete duplicate features Removing overlapping/duplicate polygons in the same layer using QGIS is not the best option here, because I have to base my selection on just one column in the data attribute table. How can I sort it out?

• Babel: Sorry, the question is not clear: what is your problem? You have a selection and don't know how to delete? Or you don't know how to generate an appropriate selection? Based on which fields exactly duplicates should be identiefied?
• Geographos: I have the selection and I don't know how to delete just 1 record from 2. As you can see I have some of them doubled and I need one of them deleted for each case. I have them 27.
• Taras: Did you try the "Delete duplicates by attribute"?
• Geographos: @Taras I am not the best at reading the references and here is the problem. If you could explain to me how to read these, I would be really grateful.
• Taras: Please, do not forget about "What should I do when someone answers my question?"

Answer 1

It is not an expression, but there is a tool in QGIS for deleting duplicates called "Delete duplicates by attribute".

Deletes duplicate rows by only considering the specified field / fields. The first matching row will be retained, and duplicates will be discarded.

Optionally, these duplicate records can be saved to a separate output for analysis.

Let's assume there is a polygon layer 'poly_test' with several duplicates, see the image below.

input1

After applying the algorithm with these settings, where the "id" field was used as Fields to match duplicates by

window1

The following output will appear

output1

---

If there is a need to delete duplicates based on its data "poorness", proceed with the following workflow.

Let's assume there is a polygon layer 'poly_test' with several duplicates, see the image below.

input2

Step 1. Create a field "Quality" using the following expression:

array_count(array("Data1", "Data2", "Data3"), '')

step_1

Step 2. Apply the "Extract by expression" with the following expression:

"Quality" = minimum("Quality", group_by:="id")

window2

step_2

Step 3. Finally apply the "Delete duplicates by attribute" algorithm (with the "id" field as Fields to match duplicates by) and get the output like this:

step_3

Answer 2

Let's say you have duplicate values in field "value" and want to keep just one of the duplicate: the one with the smallest "id" value: use "Select by expression" with this expression:

array_contains (
 with_variable (
 'array',
 array_agg( 
 $id, 
 group_by:="Site ID", 
 order_by:=$id
 ),
 array_remove_all( 
 @array, 
 array_first( @array)
 )
 ),
 $id
)

enter image description here

• Geographos: I tried: array_contains ( with_variable ( 'array', array_agg( 'Site ID', group_by:='Site ID', order_by:='Site ID' ), array_remove_all( @array, array_first( @array) ) ), 'Site ID' ) and it selects nothing, as have the "false" in the result
• Babel: That's why I asked based on what you want to identify duplicates - it seems only based on Site Id. Try: array_contains ( with_variable ( 'array', array_agg( 'Site ID', group_by:='Site ID', order_by:=$id ), array_remove_all( @array, array_first( @array) ) ), $id)
• Geographos: Still no reaction, I have even created the id column not existing before, but the result is the same.
• Babel: Sorry, did not have access to QGIS when I wrote my last comment. I mean: take my initial expression and replace all id by $id and value by Site Id, thus the expression should look like: array_contains ( with_variable ( 'array', array_agg( $id, group_by:=Site ID, order_by:=$id ), array_remove_all( @array, array_first( @array) ) ), $id )