How to average the third column for each value of the first column?

11-12-2015

Registered User

1,781, 705

Join Date: May 2008

Last Activity: 10 November 2021, 5:38 PM EST

Posts: 1,781

Thanks Given: 62

Thanked 705 Times in 653 Posts

Quote:

Originally Posted by RudiC

You weren't too far off ... compare this to what you posted, and you'll find your mistakes:

Code:

awk '
        {arr[$1] += $3
         count[$1] ++
        }
END     {for (a in arr) {print "id avg " a " = " arr[a] / count[a]
                        }
        }
' FS="," file
id avg 32.498567 = 0.0309062
id avg 30.498001 = 0.020214 <--- it should be close to 0.0288807
id avg 34.758781 = 0.031875

Only that the input is not consistent and line 3 has an extra field, producing the wrong average.

Perhaps,

Code:

awk '
        {arr[$1] += $NF
         count[$1] ++
        }
END     {for (a in arr) {print "id avg " a " = " arr[a] / count[a]
                        }
        }
' FS="," pmfcg.file
id avg 32.498567 = 0.0309062
id avg 30.498001 = 0.0288807
id avg 34.758781 = 0.031875

A Perl alternative:

Code:

perl -nle '
    /^([\d\.-]+,[\d\.-]+).*?([\d\.-]+)$/ and @{$u{$1}}[0] += $2 and @{$u{$1}}[1]++;
    END{for $k (keys %u){print "$k,", @{$u{$k}}[0]/@{$u{$k}}[1]}}
' pmfcg.file

output:

Code:

34.758781,-87.650562,0.031875
32.498567,-86.136587,0.03090625
30.498001,-87.881412,0.0288806666666667

Aia

View Public Profile for Aia

Find all posts by Aia

11-18-2015

Registered User

3, 0

Join Date: Nov 2015

Last Activity: 18 November 2015, 4:06 PM EST

Posts: 3

Thanks Given: 0

Thanked 0 Times in 0 Posts

Ok, so I tried this

Code:

awk '
        {arr[$1] += $3
         count[$1] ++
        }
END     {for (a in arr) {print "id avg � a " = " arr[a] / count[a]
                        }
        }
' FS="," file

This is exactly what I need! Thanks so much for the pointers!

However, I also need to print it in the following format:

Code:

Lat,Long,avg

As of now, it gives me this:

Code:

id avg 32.498567 = 0.0309062
id avg 30.498001 = 0.0288807
id avg 34.758781 = 0.031875

How could I ammend the above code to give me the LONG too? Otherwise I would have to manually pair the LAT with the LONg.... yuck...

---------- Post updated at 05:05 PM ---------- Previous update was at 04:59 PM ----------

Alright, Aia's Perl solution did exactly what I needed to do. Thanks!!!!!

PMFCG

View Public Profile for PMFCG

Find all posts by PMFCG

Shell Programming and Scripting

How to average the third column for each value of the first column?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Average each numeric column

Discussion started by: Geneanalyst

2. Shell Programming and Scripting

Check first column - average second column based on a condition

Discussion started by: jacobs.smith

3. Shell Programming and Scripting

Get column average using ID

Discussion started by: jwbucha

4. UNIX for Dummies Questions & Answers

Average by specific column value, awk

Discussion started by: bjoern456

5. Shell Programming and Scripting

Calculate the average of a column based on the value of another column

Discussion started by: jackken007

6. Shell Programming and Scripting

average of rows with same value in the first column

Discussion started by: paolo.kunder

7. Shell Programming and Scripting

average each column in a file

Discussion started by: kylle345

8. UNIX for Dummies Questions & Answers

average of a column in a table

Discussion started by: Gussifinknottle

9. UNIX for Dummies Questions & Answers

Use awk to calculate average of column 3

Discussion started by: grossgermany

10. UNIX for Dummies Questions & Answers

calculate average of column 2

Discussion started by: onthetopo