Use awk to calculate sum for multiple columns

Question

I have a list with 8 columns, in which the first 6 are the same.

6   99999715    99999771    NM_001013399    0   -   23  0.0714286
6   99999715    99999771    NM_001013399    0   -   24  0.0178571
6   99999715    99999771    NM_001013399    0   -   25  0.1250000

I need to calculate the average for column 7 and column8, as well as $7*$8, and get the format like:

6   99999715    99999771    NM_001013399    0   -   ave($7) ave($8) ave($7*$8)

How should I do this? thx

stark · Accepted Answer · 2013-02-08 17:08:27Z

15

Haven't tried it, but it should be just:

{sum7+=$7; sum8+=$8; mul+=$7*$8} END {print sum7/NR,sum8/NR,mul/NR}

In response to popular demand, I'll add the printf.

{sum7+=$7; sum8+=$8; mul+=$7*$8}
END {printf "%s %4i %10.7f %10.7f\n", substr($0,0,49),sum7/NR,sum8/NR,mul/NR}

edited Feb 8, 2013 at 17:08

answered May 5, 2012 at 17:12

stark

13.3k3 gold badges39 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Hedderly Over a year ago

This is a really helpful answer for showing some awk calculations but doesn't give the output format requested.

Peter.O · Accepted Answer · 2012-05-06 01:04:10Z

awk '
{
  if( common == "" ) { 
    fn=1   # field number
    cn=1   # column number
    tmp=$0
    f=0 
    while( match( tmp, /  *|$/ ) && f<=NF ) 
    {  f+=1
       cnA[fn]=cn           # column number of start of field fn
       cnZ[fn]=cn+RSTART-1  # column number of   end of field fn
       ++fn
       cn+=RSTART+RLENGTH-1
       tmp=substr( tmp, RSTART+RLENGTH )
    }
    common = substr($0,1,cnA[7]-1)
    dlim78 = substr($0,cnZ[7], cnZ[7]-cnA[7])
  }  
  print $0
  (f7+=$7)
  (f8+=$8)
}
END {
  p7=".0" # decimal places ($7)
  p8=".7" # decimal places ($8)
  pP=".7" # decimal places ($7*$8) 
  printf( "%s%"p7"f%s%"p8"f%s%"pP"f\n" ,
           common, f7/NR, dlim78, f8/NR, dlim78,f7*f8/NR )
}
' <<'EOF'
6   99999715    99999771    NM_001013399    0   -   23  0.0714286
6   99999715    99999771    NM_001013399    0   -   25  0.1250000
EOF

Output:

6   99999715    99999771    NM_001013399    0   -   23  0.0714286
6   99999715    99999771    NM_001013399    0   -   25  0.1250000
6   99999715    99999771    NM_001013399    0   -   24  0.0982143  4.7142864

To whowever it was that marked this answer down. I'd like them to show any other way to maintian the field seperation, using an awk script... That seems to be a main point of the question.

Collectives™ on Stack Overflow

Use awk to calculate sum for multiple columns

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related