Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a huge file (50k lines) with the following format:

52370581 2116
17996781 4314
47818829 2584
61421045 2158
...
25145813 2274

The first value can be present multiple times and I want to compute the average of the 2nd value for each 1st value, using the terminal. Ideally, I'd want the median, the min and max too. I can to it on all the values quite easily with awk but I was wondering how to get that done for each value.

share|improve this question

migrated from stackoverflow.com May 12 '13 at 0:41

This question came from our site for professional and enthusiast programmers.

1  
Do you want the median or the average? It's not the same thing. –  Nifle May 11 '13 at 15:28
    
@Nifle he said he wants the median as well. –  Christian Stewart May 12 '13 at 0:36
add comment

2 Answers

up vote 3 down vote accepted
awk '{c[$1]++;s[$1]+=$2}END{for(i in c)print i,(s[i]/c[i])}' test.in

More readably:

awk '
{ # Every line
    # Increment the key, add value to its sum
    count[$1]++
    sum[$1] += $2
}
END {
    # Go through all the numbers we saw
    for(number in count) {
        # Print the number followed by the average (sum/count) of its values
        print number, (sum[number]/count[number])
    }
}
' test.in

Note that the numbers will be output in a more-or-less random order (some hash of the keys). Getting min and max isn't so bad, just add them after count and sum in the first block. Median requires tracking every number. You can do it with a 2d array (or awk's emulation thereof), but I'll leave it as an exercise to the reader.

share|improve this answer
    
Perfect! I wish I could double vote just for the explanation which have allowed me to understand and go further! –  Julien Genestoux May 12 '13 at 10:45
add comment

For avarage

awk '{if(NR==1){i=$1;}} {if($1~i){j+=$2;k++}} END{print "Average is " (j/k)}' <filename>

this will provide you in case you want to do it only for the value in first line. Instead of NR==0, if you pass a variable, you can do it for any line specific case. Now if you want to do it for each line, then below awk will do it in one pass

awk '{value[$1]+=$2;count[$1]++} END{for(indx in value)print "Avarage of " indx " is " (value[indx]/count[indx])}' <filename>

awk has two dimensional array, but i'm not familiar with that, so using 2 arrays to keep sum of the elements and count. Any other operation, we need to change the script based on the operation

share|improve this answer
    
awk lines start at 1, so NR == 0 is always false. –  Kevin May 11 '13 at 17:02
    
oops Sorry my mistake, I should have tested once before posting :) Thanks @Kevin –  abasu May 11 '13 at 17:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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