1

Here is an example file:

1 5 20 40 60
2 1 20 20 20
2 2 30 30 30
4 5 40 40 40
7 2 50 30 30
7 1 20 20 20

I would like to remove duplicate rows (with the same value in the first column) with a bigger value in the second column. I want to leave only rows with uniqe values in the first column. In this case I have duplicated rows:

2 1 20 20 20
2 2 30 30 30

and

7 2 50 30 30
7 1 20 20 20

I would like to remove rows with bigger values in the second column, in this case:

2 2 30 30 30

and

7 2 50 30 30

Hence, my goal is to select and generate the following file:

1 5 20 40 60
2 1 20 20 20
4 5 40 40 40
7 1 20 20 20

I suppose I have manage to do this in awk:

sort -nk2 | awk '{if(a[$1]){a[$1]=a[$1] } else {a[$1]=$0}}END{for (k in a) {print a[k]}}' | sort

However it works on small files. How can I replace it with something more efficient (for file with near a million of rows)?

2
  • What do you mean by smaller value? In your input, if 7 is present in the second column, are you gonna delete the duplicate row with 5 in the second column?
    – Ramesh
    Commented Apr 25, 2014 at 23:33
  • I am still finding little difficult to follow your example. Can you please explain a little more by editing the question?
    – Ramesh
    Commented Apr 25, 2014 at 23:41

1 Answer 1

1

You can do this with sort itself. In the following, the first sort orders in ascending order of column 1 followed by column 2. This is then piped to a second sort which picks out one record for each distinct value in column 1. The -s(stable sort) flag is passed to ensure that only the first record for each value of column 1 is returned, which because of the previous sort turns out to be the record with the smallest value in column 2.

sort -k1,1n -k2,2n file | sort -k1,1n -s -u

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .