Bash command to calculate average on each row and each column

Question

Suppose we have a log file like marks.log and the content looks something like this:

Fname   Lname   Net Algo    
Jack    Miller  15  20  
John    Compton 12  20  
Susan   Wilson  13  19  

I want to add a new column that contains average for each person, and a new row that contains average for each course. The result has to look like this:

Fname   Lname   Net  Algo  Avg
Jack    Miller  15   20    17.5
John    Compton 12   20    16
Susan   Wilson  13   19    16
Average         13.3 19.6  -

Answer 1

If your data is in datafile.txt, the syntax for awk could be something like:

awk '
  {
  # If it is the first row
  if (NR==1)
  print $0, "Avg";
  else
  # Print all fields, then the average of fields 3 & 4
  print $0,($3+$4)/2;
  # Get the total for field 3 and field 4
  t3+=$3; t4+=$4
  }
  # Once that is done...
  END {
  # Print the final line
  printf "Overall Average %.1f %.1f -\n",
  # The average of field 3 (NR is the Number of Records)
  t3/(NR-1),
  # The average of field 4 (NR is the Number of Records)
  t4/(NR-1);
  }' datafile.txt

That's the long version with comments. The one-liner looks like:

awk '{if (NR==1) print $0, "Avg"; else print $0,($3+$4)/2; t3+=$3; t4+=$4}END{printf "Overall Average %.1f %.1f -\n",t3/(NR-1),t4/(NR-1);}' datafile.txt

This should match the desired output.

Answer 2

How about:

gawk '{if (NR==1) { print $0, "Avg"; tn = 0; ta = 0; c = 0; } else { print $0,($3+$4)/2; tn = tn + $3; ta = ta + $4; c = c + 1; } } END {print "Average", tn/c, ta/c, c; }' <filename>

Answer 3

a lengthy solution not using awk could be:

#!/bin/bash
A=0
B=0

process(){
  A=$(( $A + $3 ))
  B=$(( $B + $4 ))
}
get_mean(){
  val=$( echo "($3 + $4)/2" | bc -l)
  printf "%.1f" $val
}

line_id=0
while read line
do
  line_id=$(( $line_id + 1 ))
  if [ $line_id -le 1 ]; then
    echo "Fname   Lname   Net  Algo  Avg"
    continue
  fi

  process $line
  mean=$(get_mean $line)

  echo $line $mean
done
A=$(echo "$A/($line_id-1)" | bc -l)
B=$(echo "$B/($line_id-1)" | bc -l)
printf "Average\t\t%.1f %.1f -" $A $B

Then one can invoke this script as ./test.sh < input.