How to use awk to aggregate rows of data
I have a question, I have a set of data in rows which some rows are belong to a group.
E.g
Apple 开发者_开发问答0.4 0.5 0.6
Orange 0.2 0.3 0.2
Apple 0.4 0.3 0.4
Orange 0.4 0.5 0.8
The question is how can I automatically aggregate the columns accordingly using awk. In the past, I would easily deal with the following awk manually for each file..
awk '{col2[$1]+=$2; col3[$1]+=$3; col4[$1]+=$4} END {for(i in col2){printf("%s\t%.2f\%.2f\t%.2f\n",i,col2[i]/2,col3[i]/2,col4[i]/2)}}' myfile
But this time around the I am dealing with several files with different NF (number of fields) and I try to issue a command to automatically calculate the average of the group. Eventually, we will have
Apple 0.4 0.5 0.5
Orange 0.3 0.4 0.5
Please advise. Thanks.
here's something for a start.
awk '
{
fruits[$1]++
for(o=2;o<=NF;o++){
fruit[$1 SUBSEP o]=fruit[$1 SUBSEP o]+$o
}
}
END{
for(combined in fruit){
split(combined, sep, SUBSEP)
avg=fruit[ sep[1] SUBSEP sep[2] ]/fruits[ sep[1] ]
f[sep[1],sep[2]]=avg
}
for(fr in fruits) {
printf "%s ",fr
for(i=2;i<=NF;i++){
printf "%s ",f[fr,i]
}
print ""
}
}' file
output
$ ./shell.sh
Orange 0.3 0.4 0.5
Apple 0.4 0.4 0.5
Reference to gawk is here
精彩评论