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)?

  • 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 Apr 25 '14 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 Apr 25 '14 at 23:41
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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