Hey guys, I am currently learning different bioinformatics applications, but I do not have all that much of a computer science background. Anyway, I have been asked to perform the mean and standard deviation of coverage for different transcript ID numbers. This involves a huge file with about 30 million lines. Basically, whenever there is the same value in one column/field, I want to get the mean and standard deviation for the other column/field for the corresponding lines. My input and desired output are below, but just imagine there being thousands to millions of different transcript IDs. I also want the output to include all the other fields from the original line for each calculation. The other fields do not follow any special pattern.

So far I have been using a lot of awk, so if you have an awk solution that would be great.

Also if you could give me a formula to next calculate the number of standard deviations each coverage value is away from the mean and put it in a separate field that would be even better, but I think I can figure this part out on my own.

Input

Transcript ID   Other field Other field Coverage         
1                        3               6             1
2                        4               8             2  
1                        5               10           3  
2                        6               12           6

Transcript ID   Other field  Other field Coverage  Mean   Standard deviation
1                         3              6            1           2                  1
2                         4              8            2           4                  2
1                         5              10           3           2                  1 
2                        6               12           6           4                  2

Hope I got the maths right

awk 'BEGIN {
  ARGV[ARGC++] = ARGV[ARGC-1] 
  }  
NR == FNR && FNR > 1 {
  id[$1] += $4; cid[$1]++
  idq[$1] += $4 * $4
  next
  }
FNR == 1 {
  if (NR == FNR) next
  print $0, "Mean", "Standard deviation"
  next
  }  
{  
  $1 = $1
  print $0, id[$1]/cid[$1], sqrt(idq[$1]/cid[$1] - (id[$1]/cid[$1])**2)
  }' OFS='\t' infile