CODEWITHSUNDEEP
linuxshellawkscripting
jshakil
jshakil

Reputation: 97

How to find rows with same value in one or more rows and adding the values using awk

I have this data below:

    Name,Team,First Test, Second Test, Third Test
    Tom,Red,5,17,22
    Joe,Green,3,14,22
    Maria,Blue,6,18,21
    Fred,Blue,2,15,23
    Carlos,Red,-1,15,24
    Phuong,Green,7,19,21
    Enrique,Green,3,16,20
    Nancy,Red,9,12,24

I need to find Green Teams Average. I'm having a bit of trouble on how to finish it off.

So far I have:

awk '/Green/' teamlist.txt

which gives me the result of:

Joe,Green,3,14,22
Phuong,Green,7,19,21
Enrique,Green,3,16,20

However, when I try to print the following statement:

awk '/Green/ {print $3, $4, $5}' teamlist.txt

I just receive a blank output, nothing displays. Also I need to add columns $3, $4, and $5, then divide by 9.

This is my code so far:

#Begin. Start with the Field Separator using comma.
BEGIN {
FS=",";
}

#Middle. 
{
if (NR > 1)

{
name[NR] = $1;                       #Name record is field 1 
average[NR] = ($3 + $4 + $5) / 3;    #Average is number of records which are fields 3,4,5. Add them up and then divide by 3.

testOneAverage += $3                 #Average of test one. Keeps adding field three to itself for the number of records.

testTwoAverage += $4                 #Average of test two. Keeps adding field four to itself for the number of records.
testThreeAverage += $5               #Average of test three. Keeps adding field five to itself for the number of records.

}


}

#End
END {
print "Name         Average";
print "------       ------";

i = 1;

while (i <= FNR) {
   printf("%-10s %7.2f\n", name[i] , average[i++]);
}

if (NR > 0) {
   print "---------------------------";
   print "Average for Test 1: " testOneAverage /  8;
   print "Average for Test 2: " testTwoAverage /8;
   print "Average for Test 3: " testThreeAverage /8;
}

Upvotes: 2

Views: 275

Answers (2)

Marc Lambrichs
Marc Lambrichs

Reputation: 2892

The reason nothing gets printed is that you need to make explicit that "," is your field separator, like:

$ awk -F, '/Green/ {print $3, $4, $5}' teamlist.txt
3 14 22
7 19 21
3 16 20

Next, you want to sum up all colums 3,4 and 5 and divide by 9. Something like this will do that:

$ awk -F, '$2=="Green"{i+=3;t+=$3+$4+$5}END{printf "Average %.2f\n", t/i}' teamlist.txt
Average 13.89

Putting all things together brings us to:

$ cat tst.awk
BEGIN { FS="," }
NR > 1{ 
    name[++i]=$1                     # save name
    avg[i]=($3+$4+$5)/3              # save avg of this name
    test1+=$3                        # sum up cols 3, 4, 5 resp.
    test2+=$4
    test3+=$5
    lines++                          # count nr of lines
    if ($2=="Green"){ 
       green+=$3+$4+$5               # for "Green" sum up the tests 
       gl++                          # remember how many green lines there are
    }
}
END {                          
   print "Name         Average";
   print "------       ------";
   for (i in name)
       printf("%-10s %7.2f\n", name[i] , avg[i])  # print avg's for each person
   printf "Average Test 1: %.2f\n", test1/lines   # print avg's per test
   printf "Average Test 2: %.2f\n", test2/lines
   printf "Average Test 3: %.2f\n", test3/lines
   printf "Average Green: %.2f\n", green/(gl * 3) # print avg for green
}

And then call:

$ awk -f tst.awk teamlist.txt
Name         Average
------       ------
Tom          14.67
Joe          13.00
Maria        15.00
Fred         13.33
Carlos       12.67
Phuong       15.67
Enrique      13.00
Nancy        15.00
Average Test 1: 4.25
Average Test 2: 15.75
Average Test 3: 22.12
Average Green: 13.89

Upvotes: 1

RomanPerekhrest
RomanPerekhrest

Reputation: 92854

Your phrases: I need to find Green Teams Average + add columns $3, $4, and $5, then divide by 9 - means that you want to get the overall/total mean.

Awk solution:

awk -F',' 'NR>1 && $2=="Green"{ ++c; sum+=$3+$4+$5 }END{ print sum/(c*3) }'  file

The output:

13.8889

Upvotes: 2

Related Questions