Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have long text file with the following columns, space-delimited:

Id            Pos Ref  Var  Cn   SF:R1   SR  He  Ho NC       
cm|371443199  22  G     A    R   Pass:8   0   1  0  0       
cm|371443199  25  C     A    M   Pass:13  0   0  1  0
cm|371443199  22  G     A    R   Pass:8   0   1  0  0        
cm|367079424  17  C     G    S   Pass:19  0   0  1  0      
cm|371443198  17  G     A    R   Pass:18  0   1  0  0       
cm|367079424  17  G     A    R   Pass:18  0   0  1  0 

I want to generate a table that lists each unique ID along with counts for:

  • How many times that ID occurred
  • How many of those rows were passing (column 6)
  • How many had an He value (column 8)
  • How many had an Ho value (column 9)

In this case:

Id            CountId  Countpass   CountHe CountHO
cm|371443199   3        3          2        1
cm|367079424   2        2          0        2

How can I go about generating that table?

share|improve this question
1  
What happened to row #5? –  Michael Mrozek Jun 28 '12 at 15:30
    
sorry, row#5 also occurs, i have just shown two examples in output –  jack Jun 28 '12 at 15:54
add comment

2 Answers

up vote 3 down vote accepted

Here is a solution in awk which uses 4 arrays to count the 4 pieces of information you need. The output from awk is then fed into column which aligns the columns up nicely. (Note that this could also have been done in awk using printf.)

awk 'NR>1 {
    id[$1]++
    if($6 ~ /Pass/) pass[$1]++
    if($8 ~ /1/) he[$1]++
    if($9 ~ /1/) ho[$1]++
} 
END {
   print "Id CountId Countpass CountHe CountHO"
   for(i in id)
      print i" "id[i]" "(pass[i]?pass[i]:0)" "(he[i]?he[i]:0)" "(ho[i]?ho[i]:0)
}' input.txt | column -t

Output:

Id            CountId  Countpass  CountHe  CountHO
cm|371443198  1        1          1        0
cm|371443199  3        3          2        1
cm|367079424  2        2          0        2
share|improve this answer
    
To get the output sequence as FIFO-ish (as mentioned in a comment to Birei's answer, only minor changes are needed ... (1) Add a new line after the opening { brace: if(!id[$1]) nr[ln++]=$1 ... (2) Remove the line for(i in id) and replace it with: for( n=0;n<ln;n++ ) { i=nr[n] ... (3) Change the closing } brace to }} to balance the extra brace introduced in (2) -- (+1 btw) –  Peter.O Jul 5 '12 at 23:53
add comment

One way using perl assuming infile has content of your question (IDs won't be necessarily in same order in output because I use a hash to save them):

Content of script.pl:

use strict;
use warnings;

my (%data);

while ( <> ) { 

    ## Omit header.
    next if $. == 1;

    ## Remove last '\n'.
    chomp;

    ## Split line in spaces.
    my @f = split;

    ## If this ID exists, get previously values and add values of this
    ## line to them. Otherwise, begin to count now.
    my @counts = exists $data{ $f[0] } ? @{ $data{ $f[0] } } : (); 
    $counts[0]++;
    $counts[1]++ if substr( $f[5], 0, 4 ) eq q|Pass|;
    $counts[2] += $f[7];
    $counts[3] += $f[8];
    splice @{ $data{ $f[0] } }, 0, @{ $data{ $f[0] } }, @counts; 
}

## Format output.
my $print_format = qq|%-15s %-10s %-12s %-10s %-10s\n|;

## Print header.
printf $print_format, qw|Id CountId CountPass CountHe CountHo|;

## For every ID saved in the hash print acumulated values.
for my $id ( keys %data ) { 
    printf $print_format, $id, @{ $data{ $id } };
}

Run it like:

perl script.pl infile

With following output:

Id              CountId    CountPass    CountHe    CountHo   
cm|371443198    1          1            1          0         
cm|371443199    3          3            2          1         
cm|367079424    2          2            0          2
share|improve this answer
    
thanks. I get error Use of uninitialized value in printf at script.pl line 35. Is it possible to display ID's in the same order as appear in the input file –  jack Jun 28 '12 at 17:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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