Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Calculating frequency of values within bins

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Calculating frequency of values within bins
# 1  
Old 01-04-2012
Calculating frequency of values within bins
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:
Code:
 
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

Code:
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.
Calculating frequency of values within bins-figurepng
# 2  
Old 01-04-2012
Can you post the exact output with numbers that should be achieved for the sample data that you provided?
# 3  
Old 01-04-2012
I am not going to draw your graph but regarding the calculation maybe this will help (i hope i did not missunderstood your requirements):

Code:
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[i]+0,"values have their $1 in "i " and the sum of their $2 is "A[i]+0}}' yourfile

Code:
$ 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[i]+0,"values have their $1 in "i " and the sum of their $2 is "A[i]+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

Last edited by ctsgnb; 01-04-2012 at 02:14 PM..
# 4  
Old 01-05-2012
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.
Code:
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

Code:
whichbin=
col.1 values  arraybin assignment
12.6318352458 2 
12.0566193203 2
13.2302267715 3
12.0116097615 2
13.310066457  3
12.0118300038 2
12.0887778114 2
13.7107875841 3
12.9565490722 2
12.0617174502 2
12.1672836452 2
12.0956200447 2
12.3911778903 2
14.173625486  3
12.9012203275 2
11.2007593792 2
9.66297186014 1
12.5666761127 2
12.6919820568 2
13.0367574336 3
12.7637326677 2
14.9655179679 3
13.5666439876 3
15.9240500895 4
16.7596231894 4
13.8307211439 3
17.1159829995 5
13.7381931446 3
15.4617357782 4
13.9926310027 3
14.3019600635 3
16.571361985  4
13.386336958  3
14.6119437607 3
14.6271410249 3
16.4103756709 4
17.4536907641 5
13.8739825546 3
12.8456365048 2
12.4519526598 2
13.1417111191 3
13.3157434671 3
13.0941859655 3
19.0453498227 6
12.7900711291 2
13.17760558   3
13.4821577599 3
13.0229460206 3
13.2783780649 3
13.8176612104 3
16.2109653259 4
15.7580809633 4
15.7507268703 4

Code:
arraybin=( 9-10 11-12 13-14 15-16 17-18 19-20 )

Code:
12.6318352458 3.296900
12.0566193203 4.166667
13.2302267715 1.101667
12.0116097615 1.763333
13.310066457  3.523125
12.0118300038 2.439231
12.0887778114 2.250000
13.7107875841 1.176667
12.9565490722 3.250714
12.0617174502 2.563333
12.1672836452 3.172857
12.0956200447 3.104667
12.3911778903 8.070000
14.173625486  3.148333
12.9012203275 2.960000
11.2007593792 1.902727
9.66297186014 3.089286
12.5666761127 2.970000
12.6919820568 3.123333
13.0367574336 3.540769
12.7637326677 3.000000
14.9655179679 9.322000
13.5666439876 12.570000
15.9240500895 5.972941
16.7596231894 10.222000
13.8307211439 9.336250
17.1159829995 6.040667
13.7381931446 12.260000
15.4617357782 10.424762
13.9926310027 16.070000
14.3019600635 9.004348
16.571361985  11.002778
13.386336958  9.762857
14.6119437607 9.516522
14.6271410249 6.730909
16.4103756709 9.751429
17.4536907641 19.626522
13.8739825546 11.684444
12.8456365048 11.530000
12.4519526598 10.110476
13.1417111191 11.124583
13.3157434671 11.187059
13.0941859655 13.176667
19.0453498227 13.787143
12.7900711291 11.840588
13.17760558   11.367222
13.4821577599 8.538750
13.0229460206 11.180588
13.2783780649 9.555652
13.8176612104 14.691250
16.2109653259 21.295200
15.7580809633 20.223600
15.7507268703 19.763333

bunch of thanks as always.
# 5  
Old 01-05-2012
Not sure to understand your requirements especially the way you calculate your average (or the given example is not accurate?)

Code:
$ 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[i]+B[i+1],"values of $1 fall in that bin and the average of their $2 is "(A[i]+A[i+1])/(B[i]+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 :

Code:
$ 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[i]+B[i+1],"values of $1 fall in that bin and the average of their $1 is "(A[i]+A[i+1])/(B[i]+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

Last edited by ctsgnb; 01-05-2012 at 07:08 AM..
# 6  
Old 01-05-2012
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.
# 7  
Old 01-05-2012
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):

Code:
$ 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[i]+N[i+1]
print i"-"i+1,":",z,"values of $2 fall in that bin and their average is",(z<=0?"not available":(A[i]+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

Last edited by ctsgnb; 01-05-2012 at 10:30 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Create bins with totals and percentage

I would like to create bins to get histogram with totals and percentage, e.g. starting from 0. If possible to set the minimum and maximum value in the bins ( in my case value min=0 and max=20 ) Input file 8 5 10 1 11 4 12 4 12 4 13 5 16 7 18 9 16 9 17 7 18 5 19 5 20 1 21 7 (10 Replies)
Discussion started by: jiam912
10 Replies

2. Shell Programming and Scripting

awk to select 2D data bins

I wish to use AWK to do something akin: Select all 2D data with 1<$1<2 and -7.5<$2<-6.5 But it's not working awk 'END {print ($1<=2&&$1>=1&&$2<=-6.5&&$2>=-7.5)}' bla Data: -1.06897 -8.04482 -61.469 -1.13613 -8.04482 -61.2271 -1.00182 -8.04482 -61.2081 -1.06897 -8.13518 -60.8544... (2 Replies)
Discussion started by: chrisjorg
2 Replies

3. Shell Programming and Scripting

Count frequency of unique values in specific column

Hi, I have tab-deliminated data similar to the following: dot is-big 2 dot is-round 3 dot is-gray 4 cat is-big 3 hot in-summer 5 I want to count the frequency of each individual "unique" value in the 1st column. Thus, the desired output would be as follows: dot 3 cat 1 hot 1 is... (5 Replies)
Discussion started by: owwow14
5 Replies

4. UNIX for Dummies Questions & Answers

awk solution for taking bins

Hi all, I'm looking for an awk solution for taking bins of data set. For example, if I have two columns of data that I wish to use for a scatter plot, and it contains 5 million lines, how can I take averages of every 100 points, 1000, 10000 etc... The idea is to take bins of the 5,000,000 points... (7 Replies)
Discussion started by: torchij
7 Replies

5. UNIX for Dummies Questions & Answers

Calculating cumulative frequency using awk

Hi, I wanted to calculate cumulative frequency distribution of my data that involves several arithmetic calls. I did things in excel but its taking me forever. this is what I want to do: var1.txt contains n observations which I have to compute for frequency which is given by 1/n and subsequently... (7 Replies)
Discussion started by: ida1215
7 Replies

6. Shell Programming and Scripting

calculating unique strings values

Hi, Im looking for a script which will calculate the unique strings column 2 & 3 values in a log as mentioned in example eg:- bag 12 12 bag 18 15 bags 15 13 bags 15 14 blazer 24 24 blazer 33 32 boots 19 15 Result should be:- bag 30 27 bags 30 27... (9 Replies)
Discussion started by: Paulwintech
9 Replies

7. Solaris

Redirecting print to optional output bins

Guys We have a HP P4015 laserjet printer with a 5 bin mailbox attached & configured. We can print to the specific output bins from Oracle e-Business suite, however our print output format is incompatible so it prints out random characters instead of the letter content. I have looked... (2 Replies)
Discussion started by: s1977
2 Replies

8. Shell Programming and Scripting

Help with calculating frequency of specific word in a string

Input file: #read_1 AWEAWQQRZZZQWQQWZ #read_2 ZZAQWRQTWQQQWADSADZZZ #read_3 POGZZZZZZADWRR . . Desired output file: #read_1 3 #read_1 1 #read_2 2 #read_2 3 #read_3 6 . . (3 Replies)
Discussion started by: perl_beginner
3 Replies

9. Shell Programming and Scripting

Calculating cumulative frequency

Hi, I have a file containing the frequency's of an element sorted in ascending order. The file looks something like this: #Element Frequency 1 1 2 1 3 1 4 1 5 1 6 ... (5 Replies)
Discussion started by: sajal.bhatia
5 Replies

10. UNIX for Dummies Questions & Answers

Calculating the Hours between two time values

Dear Folks, I want to calculate the elapsed hours between two time columns. I am using timestampdiff method for the same. I am able to get the value. But facing an issue of decimal values. For example the elapsed hours between 09:00:00 and 20:30:00 is coming as 11 instead of 11.5. I am using below... (1 Reply)
Discussion started by: dinesh1985
1 Replies
Login or Register to Ask a Question