2

Given an input txt file:

$ cat input.txt
-1 23 34 cheese
34 20 15 cheese
-4 -4 20 tomato
13 -3 14 tomato
4  6   7 tomato
-3 -3 -3 potato

I can uniqify the data based on column 4, retaining the record with maximum value in column 1 using:

$ cat input.txt | sort -k4 -k1,1rn | uniq -f3

Which gives:

34 20 15 cheese
13 -3 14 tomato
-3 -3 -3 potato

I however want to uniqify the data on column 4 and retain the maximum value in all records with the same column 4. Something like:

34 23 34 cheese
13 6 20 tomato
-3 -3 -3 potato

Any quick way to achieve this?

2

Using AWK:

#!/usr/bin/awk -f
!keys[$4] { keys[$4] = 1; for (i = 1; i < 4; i++) max[$4][i] = $i }
{ for (i = 1; i < 4; i++) {
    if (max[$4][i] < $i) max[$4][i] = $i
} }
END { for (key in max) print max[key][1], max[key][2], max[key][3], key }

This tracks the three maximum values for each key in column four.

There is one caveat: the order of keys isn't maintained.

  • This solves caveat 1 I guess: #!/usr/bin/awk -f !keys[$4] { keys[$4] = 1 for (i = 1; i < 4; i++) {max[$4,i] = $i } } { for (i = 1; i < 4; i++) { if (max[$4,i] < $i) max[$4,i] = $i } } END { for (key in keys) print max[key,1], max[key,2], max[key,3], key } – Ryan Dsouza Feb 2 at 15:53
  • It does indeed, I’ll edit my answer. The point of initialising the array the first time round is so that negative values are handled correctly; a missing value is interpreted as 0. – Stephen Kitt Feb 2 at 18:42

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.