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

Improve this question

4 Answers 4

Reset to default
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
3
  • 2
    @terdon: Why not just define OFMT="%.2f" ? And, BTW, two decimal places were not requested for 2.5. Commented Feb 22, 2020 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. Commented Feb 22, 2020 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. Commented Feb 22, 2020 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
3

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
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

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.