6

I have the following file:

A B 1990
A C 2001
A C 2013
A B 2001
B C 2013
A D 2001
A D 2014

I need to count duplicate pairs (in the 1st and 2nd column) and to each such pair assign the lowest value from the 3rd column. For my toy file the output should be:

A B 2 1990
A C 2 2001
A D 2 2001
B C 1 2013

For example, pair A B occurs two times, first time with value 1990 and second time with value 2001, because 1990 < 2001 we assign 1990 to this count.

I wonder how to do that with Linux tools (Bash, AWK, Perl). Any ideas / pointers would be greatly appreciated.

5
  • This seems like a perfect use case for awk.
    – HalosGhost
    Jul 16 '14 at 17:06
  • @HalosGhost Any idea how to start?
    – Andrej
    Jul 16 '14 at 17:07
  • It seems that students in masses post questions here =)
    – gena2x
    Jul 16 '14 at 17:07
  • I this your homework?
    – gena2x
    Jul 16 '14 at 17:07
  • No, I parse MEDLINE and this is only a small (but nasty) detail.
    – Andrej
    Jul 16 '14 at 17:10
2

Try this:

$ awk -v SUBSEP=" " '
    {a[$1,$2]++;b[$1,$2] = (b[$1,$2] && $3 > b[$1,$2]) ? b[$1,$2] : $3}
    END {
        for (i in a) {
            print i,a[i],b[i];
        }
    }
' file
A B 2 1990
A C 2 2001
A D 2 2001
B C 1 2013

Even shorter if you can change the order of field:

$ sort -n -k1 -k2 -k3 file | uniq -c -w 3
      2 A B 1990
      2 A C 2001
      2 A D 2001
      1 B C 2013
4
  • 1
    Is there -k4 or -k3 in the last one-liner?
    – Andrej
    Jul 16 '14 at 17:56
  • @Andrej: -k3, mistypo, fixed.
    – cuonglm
    Jul 16 '14 at 17:59
  • I get "Segmentation fault" when execute your awk expression on 1.2 GB file. Any idea why?
    – Andrej
    Jul 18 '14 at 19:23
  • 1
    @Andrej: the awk solution stored all things in memory, so it can crash while process huge file, because of not enough memory.
    – cuonglm
    Jul 18 '14 at 19:28

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.