Hi, I am working with files containing 2 columns in which i need to come up with the frequency/count of values in col. 2 falling within specifics binned values of col. 1. the contents of a sample file is shown below:
my desired output will be something like this (3 column output).
9-10 val. of col.2 falling within this bin freq
11-12 val. of col.2 falling within this bin freq
13-14 val. of col.2 falling within this bin freq
15-16 val. of col.2 falling within this bin freq
17-18 val. of col.2 falling within this bin freq
i want to visualize the resulting output into a figure like this. hope u can help me sort this out. many thanks.
I am not going to draw your graph but regarding the calculation maybe this will help (i hope i did not missunderstood your requirements):
awk 'BEGIN{m=M=0}{A[$1]+=$2;++B[$1];m=($1<m)?$1:m;M=($1>M)?$1:M}END{for(i=m;i<M;i++) if (i in B) {print i"-"i+1": "B+0,"values have their $1 in "i " and the sum of their $2 is "A+0}}' yourfile
$ cat mytst
15 12.5
15 11.2
16 0.2
16 1.4
17 1.6
18 4.5
17 5.6
12 8.6
11 7.2
9 2.3
$ awk 'BEGIN{m=M=0}{A[$1]+=$2;++B[$1];m=($1<m)?$1:m;M=($1>M)?$1:M}END{for(i=m;i<=M;i++) if (i in B) {print i"-"i+1": "B+0,"values have their $1 in "i " and the sum of their $2 is "A+0}}' mytst
9-10: 1 values have their $1 in 9 and the sum of their $2 is 2.3
11-12: 1 values have their $1 in 11 and the sum of their $2 is 7.2
12-13: 1 values have their $1 in 12 and the sum of their $2 is 8.6
15-16: 2 values have their $1 in 15 and the sum of their $2 is 23.7
16-17: 2 values have their $1 in 16 and the sum of their $2 is 1.6
17-18: 2 values have their $1 in 17 and the sum of their $2 is 7.2
18-19: 1 values have their $1 in 18 and the sum of their $2 is 4.5
Hi guys, thanks for the replies. I hope i can explain the criteria better for my desired output, my apologies. Anyway, the basic idea will be to compute for the mean of values of column 2 falling on specific bin, where bin ranges are initially determined from the min and max values of column 1. Below is the sample data I am working.
Based on min (9.662971860) & max (19.04534982) values of column 1, I need to create an array containing my bin ranges say "arraybin". then, I need to go through the data in column 1, and identify which bin number does each value falls giving me an output "whichbin".
Now, the final step will be to calculate for the average of column 2 from the original data based on the arraybin assignment. the end output will be something like this one.
endoutput=
arraybin average of col. 2
9-10 9.662971860
11-12 12.37139173
13-14 13.73474782
15-16 16.10586498
17-18 17.28483688
19-20 19.04534982
Not sure to understand your requirements especially the way you calculate your average (or the given example is not accurate?)
$ awk 'BEGIN{m=M=0}{e=int($1);A[e]+=$2;++B[e];m=($1<m)?$1:m;M=($1>M)?$1:M}END{for(i=int(m);i<=int(M);i+=2) {while(!(i in B)){i++};print i"-"i+1": "B+B[i+1],"values of $1 fall in that bin and the average of their $2 is "(A+A[i+1])/(B+B[i+1])}}' mytst
9-10: 1 values of $1 fall in that bin and the average of their $2 is 3.08929
11-12: 18 values of $1 fall in that bin and the average of their $2 is 4.5286
13-14: 23 values of $1 fall in that bin and the average of their $2 is 9.11172
15-16: 8 values of $1 fall in that bin and the average of their $2 is 13.582
17-18: 2 values of $1 fall in that bin and the average of their $2 is 12.8336
19-20: 1 values of $1 fall in that bin and the average of their $2 is 13.7871
---------- Post updated at 11:42 AM ---------- Previous update was at 11:30 AM ----------
Or If you just don't care of $2 because not needed, then :
$ awk 'BEGIN{m=M=0}{e=int($1);A[e]+=$1;++B[e];m=($1<m)?$1:m;M=($1>M)?$1:M}END{for(i=int(m);i<=int(M);i+=2) {while(!(i in B)){i++};print i"-"i+1": "B+B[i+1],"values of $1 fall in that bin and the average of their $1 is "(A+A[i+1])/(B+B[i+1])}}' mytst
9-10: 1 values of $1 fall in that bin and the average of their $1 is 9.66297
11-12: 18 values of $1 fall in that bin and the average of their $1 is 12.3714
13-14: 23 values of $1 fall in that bin and the average of their $1 is 13.682
15-16: 8 values of $1 fall in that bin and the average of their $1 is 16.1059
17-18: 2 values of $1 fall in that bin and the average of their $1 is 17.2848
19-20: 1 values of $1 fall in that bin and the average of their $1 is 19.0453
yeah, the sample average i just posted on the end output is wrongly calculated. thanks for the reply, i'll try to run your code and will get back to you for feedback. many many thanks.
Just another try (if the range array is built from $1 values and if the average is calculated from the $2 value falling into those ranges):
$ awk '{
A[int($2)]+=$2
B[int($1)]++
N[int($2)]++
m=($1<m)?$1:m
M=(M<$1)?$1:M
}END{
for(i=m;i<=M;i+=2){
while(!(i in B)){i++}
z=N+N[i+1]
print i"-"i+1,":",z,"values of $2 fall in that bin and their average is",(z<=0?"not available":(A+A[i+1])/z)
}
}' yourfile
9-10 : 10 values of $2 fall in that bin and their average is 9.70063
11-12 : 10 values of $2 fall in that bin and their average is 11.5747
13-14 : 3 values of $2 fall in that bin and their average is 13.885
15-16 : 1 values of $2 fall in that bin and their average is 16.07
17-18 : 0 values of $2 fall in that bin and their average is not available
19-20 : 3 values of $2 fall in that bin and their average is 19.8712
thanks for all your help. anyways, on your last posted code, is there a way to tweak it that instead of bin ranges, it would be linearly spaced bin values. for instance, it would be:
bin=(9,10,11,12,13,14,15,16,17,18,19,20)
so that it will give a mean of col.2 falling on this linearly spaced values of col.1?the output will then be:
9- mean of col2 values with col.1 values falling within this bin
10- mean of col2 values with col. 1 values falling within this bin
and so on...