awk Exercise 3

Save the following data in a file named teamlist.txt:

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

Write an awk script that will compute the average score for every person in the list, the average score for each test, and the average score for each team. If a score is negative, that means the person missed the test, and the score must not become part of the average.

Print the output to look like the following. In the list by name, the names must be left-justified in a field of size 10 (hint: %-10s in printf), and the averages must be seven characters wide with two digits to the right of the decimal point (%7.2f).

Name       Average
----       -------
Tom          14.67
Joe          13.00
Maria        15.00
Fred         13.33
Carlos       19.50
Phuong       15.67
Enrique      13.00
Nancy        15.00
------------------
Average for Test 1 : 5
Average for Test 2 : 15.75
Average for Test 3 : 22.125
-------------------
Average for Red Team: 16
Average for Green Team: 13.8889
Average for Blue Team: 14.1667

Notes

• Don’t process the first record in the file.
• Your script must use at least one array indexed by number, and at least one array indexed by string. The usage of the arrays must be relevant to the solution.
• Your script must use at least one for loop.
• Your script must not be tied to this specific data. If the numbers change, or if records are added or deleted, your script must still produce the correct results.

Hints

The following code will print the number 2 followed by the contents of field two.

{
    n = 2;
    print n, $n;
}

You will want to keep arrays indexed by number for test totals and test counts; for example, testTotal[1] keeps the running total for all the scores on test number 1.

You will want to keep arrays indexed by string for team totals and team counts; for example, teamTotal["Red"] keeps the running total for all the scores for the Red team.

The Pseudocode

Since many of you are not programmers, here is some pseudo-code to help you; the parts that are in pseudocode are in italics.

BEGIN{
    set FS to a comma
    print the header
}

{
   if (NR > 1)  # this test skips the first line of the file
   {
      Set individualTotal and individualCount to zero
      
      for (field=3; field <=5; field++)
      {
          if ($field >= 0) {
            add contents of $field to individualTotal
            add one to individualCount
            
            # Now update the testTotal and testCount arrays
            # subtract 2 from the field number so that our arrays
            # begin at 1
            add contents of $field to testTotal indexed by (field-2)
            add 1 to testCount array indexed by (field-2)
            
            # Use contents field 2 as the index for the team array
            add contents of $field to teamTotal array indexed by $2
            add 1 to teamCount array indexed by $2
          }
      }
      print the person's name and his individual average
   }
}

END { 
    print "------------------"
    for (n=1; n<=3; n++)
    {
        print average for test n using testTotal and testCount arrays
    }
    
    print "------------------"
    
    print average for "Red" team
    print average for "Green" team
    print average for "Blue" team
    (these all use the teamTotal and teamCount arrays)
}