6

I want to remove duplicate features (from a polygons shapefile) based on a condition on another attribute column in QGIS. For example, if I have the following attribute table of a polygon shapefile:

Column_A  | Column_B

1         a

2         a

1         b

2         b

I want to keep only one feature for each group of duplicates (or triplicates, etc) of column B, by keeping the feature corresponding to the smallest column A value for each duplicate group.

The result would be:

Column_A  | Column_B

1         a

1         b

4 Answers 4

5

Variant 1: select features to keep

Use Select by expression with this expression (screenshot 1):

column_a = minimum (column_a, group_by:=column_b)

Then export/save only the selected features and you're done. You get a new layer, the original data remains unchanged. All attributes for the minimum value of column A are retained (your request in a comment to the solution by @Pigreco - see screenshot 2)

Or: You could also Invert selection (click the icon, see screenshot 1) and delete the duplicates. All attributes for the minimum value of column A are retained (your request in a comment to the solution by @Pigreco - see screenshot 2).


Variant 2: select features to delete

An even faster way (no need to invert selection): directly select the duplicates you want to delete with one of these modifications of the expression. Then delete the selected features. No new layer is created, the initial data is changed, duplicates are irrevocably deleted:

  • replace = by <>:

    column_a <> minimum (column_a, group_by:=column_b)

  • replace = by !=:

    column_a = minimum (column_a, group_by:=column_b) - tribute to @pigreco

  • add not in front of the expression (screenshot 2):

    not column_a = minimum (column_a, group_by:=column_b)

screenshot 1: enter image description here

screenshot 2, including column_c that is retained - selected lines (blue) can be deleted, red ones will be kept: enter image description here

7
  • nice solution +1
    – pigreco
    Commented Dec 17, 2021 at 20:22
  • 2
    should work using !=, i.e. the expression column_a != minimum (column_a, group_by: = column_b)
    – pigreco
    Commented Dec 17, 2021 at 20:25
  • 2
    Yes, it's basically the same as the variant with <> instead of = - or using a NOT in front of the expression with = - will add these two additions to the solution.
    – Babel
    Commented Dec 17, 2021 at 20:29
  • Sorry. I noticed in my dataset, there might be several features presenting the same minimum value of column A among a given group of features from the same value in column B. And in this case, your solution will keep all features with the same minimum value. So maybe an addition constraint should be added, like retaining arbitrarily the FIRST minimum value of column A for each group of column B. Would you have an idea how to solve this? Commented Dec 20, 2021 at 19:33
  • Yes, I think so, opening a new question would be better. You can link to this question, if needed.
    – Babel
    Commented Dec 20, 2021 at 19:48
4

You can achieve this using a virtual layer.

The output is a dynamic layer, which you can choose to export if you want to persist the results.

go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the query

select min(column_a),column_b, column_c, *
from  a
group by a.column_B

You can choose to list the columns to be returned, or to use min(column_a), * to get them all.

It will return 1 row by columnB value. Since we are asking for the smallest columnA, the other field values will be for the row containing the smallest columnA

enter image description here

4
  • I tried your virtual layer solution as well. It returns the first column_A value for each duplicate group, whereas the OP asked for the smallest column A value for each duplicate group. See this case, once with my not selection and with your virtual layer query: if the the first one is not the smallest value, the virtual layer does not return the correct result - see screenshot: i.sstatic.net/T4VZv.png
    – Babel
    Commented Dec 17, 2021 at 23:06
  • 1
    @Babel Thanks! You are right I used the wrong query... this updated version works :-)
    – JGH
    Commented Dec 18, 2021 at 11:31
  • This solution is perfectly answering the problem with the additional advantage that it will keep only one feature when there are several same minimum values of column A for a given group of column B. This additional constraint was not mentioned in the problem as I formulated it but I encountered it in my dataset. Many thanks ! Commented Dec 20, 2021 at 21:16
  • Also this JGH solution works much faster than the one of BABEL that was taking a long time on my dataset (5000 features). Commented Dec 20, 2021 at 21:53
2
  1. Order by expression with the expression "Field_A"||"Field_B"

This algorithm sorts a vector layer according to an expression. Be careful, it might not work as expected with some providers, the order might not be kept every time.

  1. Delete duplicates by attribute:

Removes duplicate rows by a field value (or multiple field values). 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.

2

From Processing Tools, search for aggregate algorithm:

enter image description here

  1. select the layer of interest;
  2. select the field to aggregate for duplicates;
  3. select minimum value;
  4. select first value

EDIT:

in case there was a third column Column_C (see message below)

enter image description here

the Column_C is calculated by expression:(>= QGIS 3.18)

with_variable(
    'cucu',
    array_agg( "Column_A" , "Column_B" ),
array_agg( "Column_C" , "Column_B" )[array_find(@cucu,array_min(@cucu))])

example data: https://gist.githubusercontent.com/pigreco/0ad8205396093557dad8b2d3864a6ddf/raw/039187175547c62d206b200ba15879eb3714b22f/duplicate.csv

3
  • 1
    This is a nice solution perfectly answering my request. However I forgot to mention that I also have a column c which is text and may contain different info for each feature (for each line). The value of column c that has to be retained is the one corresponding to the feature presenting the minimum value of column A for a given group of duplicate. And I think the "aggregate" function won't be able to do so? What do you think? Commented Dec 17, 2021 at 20:47
  • but so the question is completely different, maybe it is better that I change it. tomorrow I'll study it.
    – pigreco
    Commented Dec 17, 2021 at 21:45
  • @DenisAntoine see my solution. All attributes for the minimum value of column A are automatically retained, so column_c will be kept.
    – Babel
    Commented Dec 17, 2021 at 23:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.