5

Is there a way to get average of values in a field based on variables in another field? For example for the following input

a x 3
b y 4
a y 2
b x 5
b x 20

I want this output

a 2.5
b 9.67

I found this awk script to get average for values in a column

awk '{ total += $3; count++ } END { print total/count }' file.txt

but how can I add for loop in it to have the average for every variable in column 1?

The file is tab-separated.

Thank you

2

Miller is also handy for tasks like this ex.

$ mlr --nidx stats1 -a mean -f 3 -g 1 file.txt
a 2.500000
b 9.666667

or (with a more recent version that has the format-values verb)

$ mlr --nidx stats1 -a mean -f 3 -g 1 then format-values -f '%.2f' file.txt
a 2.50
b 9.67
| improve this answer | |
7

You're not too far off. Try arrays indexed by $1:

awk '{ total[$1] += $3; count[$1]++ } END {for (t in total) print t, total[t]/count[t]}' file
a 2.5
b 9.66667

Or, if you want a maximum of two decimal points, as you show in your question:

$ awk '{ total[$1] += $3; count[$1]++ } END {for (t in total) printf "%s %.2f\n", t, total[t]/count[t]}' file
a 2.50
b 9.67
| improve this answer | |
  • 2
    @terdon: Why not just define OFMT="%.2f" ? And, BTW, two decimal places were not requested for 2.5. – RudiC Feb 22 at 8:17
  • I've been using awk for decades, and I wasn't aware of OFMT, I've always used printf for things like this. But I'm also often creating fixed-size fields to align columns. – Barmar Feb 22 at 13:54
  • FWIW I've been aware of OFMT for decades but as far as I can recall have never used it as there's never been a time when I wanted to have the same floating point display value apply to multiple print statements throughout my code and if you don't have that then there's no benefit to setting OFMT as your code is clearer if you just specify the format in the printf formatting string. – Ed Morton Feb 22 at 16:07
4

With GNU datamash:

$ datamash -R2 -W -s -g 1 mean 3 <file
a       2.50
b       9.67

$ datamash -W -s -g 1 mean 3 <file
a       2.5
b       9.6666666666667
  • -R2 round to two decimal places
  • -W use spaces and/or tabs as field separators
  • -s sort before grouping
  • -g 1 group on the first field
  • mean 3 mean of the values, third field

You can remove -W if you have tabs as separators add --output-delimiter=' ' if you want to replace tabs with space characters in the output.

| improve this answer | |
0
   #!/bin/bash
counta=`awk '$1 ~ /^a$/{print NR}' filename| awk 'END{print NR}'`
countb=`awk '$1 ~ /^b$/{print NR}' filename| awk 'END{print NR}'`

awk -v counta="$counta" '{if ($1 == "a"){sum=sum+$NF}}END{print "a" " " sum/counta}'  filename

awk -v countb="$countb" '{if ($1 == "b"){sum=sum+$NF}}END{print "b" " " sum/countb}'  filename

output

a 2.5
b 9.66667
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.