Removing duplicates from the Attribute table by expression in QGIS

Question

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

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?

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.

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

The following output will appear

---

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.

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

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

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

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

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:

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
)