awkscripting

calculate percentage and grade from input file in awk


I have a file which contains sample data as follows:

Student Name,Assignment Type,Assignment Number,Total Earned Points,Total Possible Points
Chelsey,Final,FINAL,82,100
Chelsey,Survey,WS,5,5
Sam,Homework,H01,19,100
Sam,Homework,H02,82,100

I want to calculate the weighted grade (total earned points divided by total possible points) for each student and output a list of students, their grade as a percentage and a letter grade. Letter grade scale is as follows:

A = 90 >= grade <= 100
B = 80 >= grade < 90
C = 70 >= grade < 80
D = 60 >= grade < 70
E = grade < 60

Output format:

Name    Percent Letter
Chelsey XX.XX   *
Sam     YY.YY   *

How do I achieve this in awk? Currently, I have a column average script.

awk '
    NR>1{
        arr[$1]   += $4
        count[$1] += 1
    }
    END{
        for (a in arr) {
            print arr[a] / count[a]
        }
    }
' FILE

Solution

  • If you simply want to get percentage of students which is mentioned by you by doing(total earned points divided by total possible points) then try following, though I am not sure why more than 1 student name is there or do we need to take their individual line's percentage and then take average? That question is covered in my comment.

    awk '
    BEGIN{
      FS=","
      print "Name    Percent Letter"
    }
    FNR>1{
      per=($4/$5)*100
      if(per>=90 && per<=100)   { grade="A" }
      else if(per>=80 && per<90){ grade="B" }
      else if(per>=70 && per<80){ grade="C" }
      else if(per>=60 && per<70){ grade="D" }
      else if(per<60)           { grade="E" }
      print $1,per,grade
    }'   Input_file
    


    EDIT: After understanding James sir's answer got to know that my guess may be correct and you may need total of all occurences of student names values and at last you need percentage etc, in that case try following and this should keep safe the ORDER of STUDENT names as per Input_file in output.

    awk '
    BEGIN{
      FS=","
      print "Name    Percent Letter"
    }
    FNR>1 && !student[$1]++{
      student_name[++count]=$1
    }
    FNR>1{
      student_possible_points[$1]+=$5
      student_total_points[$1]+=$4
    }
    END{
      for(j=1;j<=count;j++){
        per=(student_total_points[student_name[j]]/student_possible_points[student_name[j]])*100
        if(per>=90 && per<=100)   { grade="A" }
        else if(per>=80 && per<90){ grade="B" }
        else if(per>=70 && per<80){ grade="C" }
        else if(per>=60 && per<70){ grade="D" }
        else if(per<60)           { grade="E" }
        printf("%s %.02f %s\n",student_name[j],per,grade) | "sort -k1.1"
      }
    }'  Input_file | column -t