Awk script displaying incorrect output

I'm facing an issue in awk script - I need to generate a report containing the lowest, highest and average score for each assignment in the data file. The name of the assignment is located in column 3.

Input data is:

Student,Catehory,Assignment,Score,Possible
Chelsey,Homework,H01,90,100
Chelsey,Homework,H02,89,100
Chelsey,Homework,H03,77,100
Chelsey,Homework,H04,80,100
Chelsey,Homework,H05,82,100
Chelsey,Homework,H06,84,100
Chelsey,Homework,H07,86,100
Chelsey,Lab,L01,91,100
Chelsey,Lab,L02,100,100
Chelsey,Lab,L03,100,100
Chelsey,Lab,L04,100,100
Chelsey,Lab,L05,96,100
Chelsey,Lab,L06,80,100
Chelsey,Lab,L07,81,100
Chelsey,Quiz,Q01,100,100
Chelsey,Quiz,Q02,100,100
Chelsey,Quiz,Q03,98,100
Chelsey,Quiz,Q04,93,100
Chelsey,Quiz,Q05,99,100
Chelsey,Quiz,Q06,88,100
Chelsey,Quiz,Q07,100,100
Chelsey,Final,FINAL,82,100
Chelsey,Survey,WS,5,5
Sam,Homework,H01,19,100
Sam,Homework,H02,82,100
Sam,Homework,H03,95,100
Sam,Homework,H04,46,100
Sam,Homework,H05,82,100
Sam,Homework,H06,97,100
Sam,Homework,H07,52,100
Sam,Lab,L01,41,100
Sam,Lab,L02,85,100
Sam,Lab,L03,99,100
Sam,Lab,L04,99,100
Sam,Lab,L05,0,100
Sam,Lab,L06,0,100
Sam,Lab,L07,0,100
Sam,Quiz,Q01,91,100
Sam,Quiz,Q02,85,100
Sam,Quiz,Q03,33,100
Sam,Quiz,Q04,64,100
Sam,Quiz,Q05,54,100
Sam,Quiz,Q06,95,100
Sam,Quiz,Q07,68,100
Sam,Final,FINAL,58,100
Sam,Survey,WS,5,5
Andrew,Homework,H01,25,100
Andrew,Homework,H02,47,100
Andrew,Homework,H03,85,100
Andrew,Homework,H04,65,100
Andrew,Homework,H05,54,100
Andrew,Homework,H06,58,100
Andrew,Homework,H07,52,100
Andrew,Lab,L01,87,100
Andrew,Lab,L02,45,100
Andrew,Lab,L03,92,100
Andrew,Lab,L04,48,100
Andrew,Lab,L05,42,100
Andrew,Lab,L06,99,100
Andrew,Lab,L07,86,100
Andrew,Quiz,Q01,25,100
Andrew,Quiz,Q02,84,100
Andrew,Quiz,Q03,59,100
Andrew,Quiz,Q04,93,100
Andrew,Quiz,Q05,85,100
Andrew,Quiz,Q06,94,100
Andrew,Quiz,Q07,58,100
Andrew,Final,FINAL,99,100
Andrew,Survey,WS,5,5
Ava,Homework,H01,55,100
Ava,Homework,H02,95,100
Ava,Homework,H03,84,100
Ava,Homework,H04,74,100
Ava,Homework,H05,95,100
Ava,Homework,H06,84,100
Ava,Homework,H07,55,100
Ava,Lab,L01,66,100
Ava,Lab,L02,77,100
Ava,Lab,L03,88,100
Ava,Lab,L04,99,100
Ava,Lab,L05,55,100
Ava,Lab,L06,66,100
Ava,Lab,L07,77,100
Ava,Quiz,Q01,88,100
Ava,Quiz,Q02,99,100
Ava,Quiz,Q03,44,100
Ava,Quiz,Q04,55,100
Ava,Quiz,Q05,66,100
Ava,Quiz,Q06,77,100
Ava,Quiz,Q07,88,100
Ava,Final,FINAL,99,100
Ava,Survey,WS,5,5
Shane,Homework,H01,50,100
Shane,Homework,H02,60,100
Shane,Homework,H03,70,100
Shane,Homework,H04,60,100
Shane,Homework,H05,70,100
Shane,Homework,H06,80,100
Shane,Homework,H07,90,100
Shane,Lab,L01,90,100
Shane,Lab,L02,0,100
Shane,Lab,L03,100,100
Shane,Lab,L04,50,100
Shane,Lab,L05,40,100
Shane,Lab,L06,60,100
Shane,Lab,L07,80,100
Shane,Quiz,Q01,70,100
Shane,Quiz,Q02,90,100
Shane,Quiz,Q03,100,100
Shane,Quiz,Q04,100,100
Shane,Quiz,Q05,80,100
Shane,Quiz,Q06,80,100
Shane,Quiz,Q07,80,100
Shane,Final,FINAL,90,100
Shane,Survey,WS,5,5

awk script :

BEGIN {
  FS=" *\\, *"
}

FNR>1 {
  min[$3]=(!($3 in min) || min[$3]> $4 )? $4 : min[$3]
  max[$3]=(max[$3]> $4)? max[$3] : $4
  cnt[$3]++
  sum[$3]+=$4
}
END {
  print "Name\tLow\tHigh\tAverage"
  for (i in cnt)
    printf("%s\t%d\t%d\t%.1f\n", i, min[i], max[i], sum[i]/cnt[i])

}

Expected sample output:

Name    Low     High    Average
Q06     77      95      86.80
L05     40      96      46.60
WS      5       5       5
Q07     58      100     78.80
L06     60      99      61
L07     77      86      64.80

When I run the script, I get a "Low" of 0 for all assignments which is not correct. Where am I going wrong? Please guide.


You can certainly do this with awk, but since you tagged this scripting as well, I'm assuming other tools are an option. For this sort of gathering of statistics on groups present in the data, GNU datamash often reduces the job to a simple one-liner. For example:

$ (echo Name,Low,High,Average; datamash --header-in -s -t, -g3 min 4 max 4 mean 4  < input.csv) | tr , '\t'
Name    Low     High    Average
FINAL   58      99      85.6
H01     19      90      47.8
H02     47      95      74.6
H03     70      95      82.2
H04     46      80      65
H05     54      95      76.6
H06     58      97      80.6
H07     52      90      67
L01     41      91      75
L02     0       100     61.4
L03     88      100     95.8
L04     48      100     79.2
L05     0       96      46.6
L06     0       99      61
L07     0       86      64.8
Q01     25      100     74.8
Q02     84      100     91.6
Q03     33      100     66.8
Q04     55      100     81
Q05     54      99      76.8
Q06     77      95      86.8
Q07     58      100     78.8
WS      5       5       5

This says that for each group with the same value for the 3rd column (-g3, plus -s to sort the input (A requirement of the tool)) of simple CSV input (-t,) with a header (--header-in), display the minimum, maximum, and mean of the 4th column. It's all given a new header and piped to tr to turn the commas into tabs.

getting wrong output with AWK command!!!, getting wrong output with AWK command!!! Original Post by ganesh_248. ganesh_248. i have a file which gets appended with 9 records daily and the file keeps  $ awk -v OFS='-->' -v ORS='. ' -f script.awk file 2-->1. Note: I'm assuming that the output shown in the question is wrong as it's what you would get when not ignoring the first field (and ignoring the case of the o letter ).


why when i am printing array content, it gives me wrong output , matrix[$k,$l]=$() This array assignment looks suspicious. I don't think Bash supports proper two-dimensional arrays. Let's see: $ M=() $ M[0  So far, we displayed data on standard output stream. We can also redirect data to a file. A redirection appears after the print or printf statement. Redirections in AWK are written just like redirection in shell commands, except that they are written inside the AWK program.


another similar approach

$ awk -F, 'NR==1 {print "name","low","high","average"; next} 
                 {k=$3; sum[k]+=$4; count[k]++}
     !(k in min) {min[k]=max[k]=$4} 
       min[k]>$4 {min[k]=$4} 
       max[k]<$4 {max[k]=$4}                    
       END       {for(k in min) print k,min[k],max[k],sum[k]/count[k]}' file | 
 column -t

name   low  high  average
Q06    77   95    86.8
L05    0    96    46.6
WS     5    5     5
Q07    58   100   78.8
L06    0    99    61
L07    0    86    64.8
H01    19   90    47.8
H02    47   95    74.6
H03    70   95    82.2

Why awk printing wrong string into the output file?, Your file is probably in some utf-16 encoding. Run the file command on the file to find out. If it comes from microsoft it is probably utf-16 little-endian. You could  BEGIN and END statements are help full in displaying information before and after executing actual AWK script. BEGIN block Uses: Declaring variables. Initialization variables for doing increment/decrements operations in main AWK code. Printing Headings/info before actual AWK code output. END block Uses: Printing final results, after doing operations in main AWK block. Printing Completion/info


correcting awk script to have output in correct order, However, the letters are output in the wrong order. Just pipe output of your awk to sort command but append header after awk processing. awk '{FS = "\t" } $2 > 0 { n[$1]++; covered_bases[$1] += $3; percentage_covered[$1]  Your output is probably buffered and you stop the code before the buffer is flushed. By forcing awk to only process several records, it will gracefully exit and write the buffered output. Try forcing awk to process the first ten it encountered by checking the record count.


4. Printing Output, Printing Output One of the most common programming actions is to print, or output, some This might be useful when trouble is encountered in an awk script run it can be redirected; when that happens, writing to the terminal is not correct. ORS: ORS command stores the output record separator, which separates the output lines when Awk prints them. The default is a newline character. print automatically outputs the contents of ORS at the end of whatever it is given to print. Examples: Use of NR built-in variables (Display Line Number) $ awk '{print NR,$0}' employee.txt . Output:


awk, For a summary of the UNIX03 changes to this command, see Shell commands For example, the following program is equivalent to the default output of env: Function call nesting level exceeded; Wrong number of arguments to function  awk 'BEGIN{RS=", "}1' file This last example will be valid for any char before newline: awk '1' RS='. ' file Note: dot. matches any character except line breaks. Explanation. awk allows us to use different record (line) regex separators, we just need to include the comma before the line break (or dot for any char) in the one used for the