Shell Programming and Scripting

 
a1 b1 c1 d1 e1 12 13 14 15
a1 b1 c1 d1 e1 14 15 16 17
a1 b1 c1 d1 e1 NA 14 15 16
a2 b1 c1 d1 e1 112 113 114 115
a2 b1 c1 d1 e1 114 115 116 117
a2 b1 c1 d1 e1 113 NA 115 116

a1 b1 c1 d1 e1 13 14 15 16
a2 b1 c1 d1 e1 113 114 115 116

 
 awk '
function isnum(x){
        return(x==x+0);
}
{
    if(NR>1) {
        a = $1 $2 $3 $4 $5;
        keys[a] = 1;
        for(I = 7; I <= NF; I++) {
                b = I a;
                arr[b]   += $I;
                if(isnum($I)) {
                        count[b] += 1;
                }
        }
    }
}
END{
        for (key in keys) {
                printf "%-16s ", key;
                for(I = 7; I <= NF; I++) {
                        b = I key;
                        printf "%8.2f ", arr[b] / count[b];
                }
                printf "\n";
        }
}
'

 
 HEADER
a1 b1 c1 d1 e1 f1  1  2  4  5
a1 b1 c1 d1 e1 f1 NA  2  6  7
a1 b1 c1 d1 e1 f1  1  2  5  6
a2 b1 c1 d1 e1 f1 12 13 14 15
a2 b1 c1 d1 e1 f1 14 15 16 17
a2 b1 c1 d1 e1 f1 13 14 15 16

 
 a2b1c1d1e1          13.00    14.00    15.00    16.00 
a1b1c1d1e1           1.00     2.00     5.00     6.00

awk '
NR == 1 {
	# Note that this copies in the input header to the output, but the
	# output will NOT include field 6 from the input.
	print
	next
}
{	# Gather keys:
	key[k = $1 FS $2 FS $3 FS $4 FS $5]
	# Accumulate counts and data from non-"NA" data fields.
	for(i = 7; i <= NF; i++) {
		if($i != "NA") {
			data[k, i] += $i
			cnt[k, i]++
		}
	}
}
END {	for(k in key) {
		printf("%s ", k)
		for(i = 7; i <= NF; i++)
			if(cnt[k, i])
				printf("%.2f%s", data[k, i] / cnt[k, i],
					(i == NF) ? "\n" : " ")
			else	printf("NA%s", (i == NF) ? "\n" : " ")
	}
}' file

This is a header line
a1 b1 c1 d1 e1 12 13 14 15
a1 b1 c1 d1 e1 14 15 16 17
a1 b1 c1 d1 e1 13 NA 15 16
a2 b1 c1 d1 e1 112 113 114 115
a2 b1 c1 d1 e1 114 115 116 117
a2 b1 c1 d1 e1 113 114 115 NA
a3 b2 c1 d2 e3 110 111 112 NA
a3 b2 c1 d2 e3 110 111 113 NA
a3 b2 c1 d2 e3 110 112 113 NA

This is a header line
a2 b1 c1 d1 e1 114.00 115.00 116.00
a1 b1 c1 d1 e1 14.00 15.00 16.00
a3 b2 c1 d2 e3 111.33 112.67 NA

a2b1c1d1e1         114.00   115.00   116.00 
a1b1c1d1e1          14.00    15.00    16.00 
a3b2c1d2e3         111.33   112.67 awk: division by zero
 input record number 10, file file
 source line number 23