Create range of bins counting values and add the percentage


 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Create range of bins counting values and add the percentage
# 1  
Old 02-21-2020
Create range of bins counting values and add the percentage

Objective, create a table of range of bins to count the values for each range in column 2.

The purpose is to count at beginning all values in column 2 which contends value =0 or < 0.01
Then create the range of binnes counting values in column 2 > 0.01

The range of bins need to be extended to the MAX value in my case 20, even if these values are not found in the column.

Input file
Code:
121 0.98
121 0.98
121 1.47
121 1.47
121 0.00
121 0.00
121 0.00
121 0.10
121    2
121    3
121    7
121    0
121    0
121   10
121   12
121   15

Desired output file

Code:
            0    5    31.3%
 0.01 -  2.00    6    37.5%
 2.01 -  4.00    1     6.3%
 4.01 -  6.00    0     0.0%
 6.01 -  8.00    1     6.3%
 8.01 - 10.00    0     0.0%
10.01 - 12.00    2    12.5%
12.01 - 14.00    0     0.0%
14.01 - 16.00    1     6.3%
16.01 - 18.00    0     0.0%
18.01 - 20.00    0     0.0%
      > 20       0     0.0%
----------------------------
      Total:    16


Code Use
This code works well, but dont count values with exactly 0, and dont force the range of bins to the maximun value.

Code:
awk -v"MIN=0" -v"MAX=20" '
BEGIN   {delta = (delta=="")?2:delta
         MXBCK = (MAX / delta) + 1
        }

        {bucketNr = int(($2+delta) / delta)
         if (bucketNr > MXBCK) bucketNr = MXBCK
         cnt[bucketNr] ++
         TOT           ++ $2
         numBuckets = (numBuckets > bucketNr ? numBuckets : bucketNr)
        }

END     {
for (bucketNr=1; bucketNr<numBuckets; bucketNr++)      {end = beg + delta
         printf " %05.2f - %05.2f %6d %6.2f%%\n", beg, end, cnt[bucketNr], cnt[bucketNr] / TOT *100
         beg = end
         }
         printf "       > %5.2f %6d %6.2f%%\n", MAX, cnt[MXBCK], cnt[MXBCK] / TOT *100
         print "----------------------------"
         print "        Total:", TOT
        }
' file

Thanks in advance

Last edited by jiam912; 02-22-2020 at 06:13 AM..
# 2  
Old 02-22-2020
Hi, What does this word mean (you used it many times):

Quote:
bines
binnes
What do you mean by this word, exactly?

Do you mean "columns" (in an ASCII table)?

Thanks.
This User Gave Thanks to Neo For This Post:
# 3  
Old 02-22-2020
Just run the for loop up to MXBCK in lieu of numBuckets (whose final value you ignore, BTW, running the loop with < instead of <=)


Where did your request for a MIN value disappear that you posted in your recent thread?

Last edited by RudiC; 02-22-2020 at 12:21 PM..
This User Gave Thanks to RudiC For This Post:
# 4  
Old 02-22-2020
I get the maximun like this

NBIR=20 #NUMBER OF RANGES ON BINS
Code:
awk -v"MIN=0" -v"MAX=20" -v"NRB=$NBIR" '
BEGIN   {delta = (delta=="")?1:delta
         MXBCK = (MAX / delta) + 1
        }

        {bucketNr = int(($8+delta) / delta)
         if (bucketNr > MXBCK) bucketNr = MXBCK
         cnt[bucketNr] ++
         TOT           ++ $2
         numBuckets = NRB +1

Thanks a lot
# 5  
Old 02-25-2020
Hi RudiC,

Kindly can you help with the desired output. still I am trying to set the min at beginning.

Please check how i modify the code to get the desired output , but still i got troubles here :
Code:
00.01 - 02.00      5  31.25%

I want to start the range of bins on 0.01 not exactly in 0.

the code I modify.
Code:
NBIR=10 #NUMBER OF RANGES ON BINS
awk -v"MIN=0" -v"MAX=20" -v"NRB=$NBIR" '
BEGIN   {delta = (delta=="")?2:delta
         MXBCK = (MAX / delta) + 1
         MIBCK = (MIN / delta) + 1
        }

        {bucketNr = (($2+delta)/delta)
         if (bucketNr > MXBCK) bucketNr = MXBCK
         cnt[bucketNr] ++
         TOT           ++ $2
         numBuckets = NRB +1
        }
END     {
         printf "       < %5.2f %6d %6.2f%%\n", MIN, cnt[MIBCK], cnt[MIBCK] / TOT *100 
for (bucketNr=1; bucketNr<=numBuckets; bucketNr++)      {end = beg + delta 
         printf " %05.2f - %05.2f %6d %6.2f%%\n", beg+0.01, end, cnt[bucketNr], cnt[bucketNr] / TOT *100
         beg = end
         }
         printf "       > %5.2f %6d %6.2f%%\n", MAX, cnt[MXBCK], cnt[MXBCK] / TOT *100
         print "-----------------------------"
         print "        Total:", TOT
         print dashes
        }
' file

output needed
Code:
           < 0    5    31.3%
 0.01 -  2.00    6    37.5%
 2.01 -  4.00    1     6.3%
 4.01 -  6.00    0     0.0%
 6.01 -  8.00    1     6.3%
 8.01 - 10.00    0     0.0%
10.01 - 12.00    2    12.5%
12.01 - 14.00    0     0.0%
14.01 - 16.00    1     6.3%
16.01 - 18.00    0     0.0%
18.01 - 20.00    0     0.0%
      > 20       0     0.0%
----------------------------
      Total:    16

# 6  
Old 02-25-2020
What if a value falls into the gap between e.g. 0 and 0.01? This is not covered here. Make sure none of your values fall into those gaps. Try this
Code:
$ awk -v"MIN=0" -v"MAX=20" '
BEGIN   {delta = (delta=="")?2:delta
         MNBCK = (MIN / delta)
         MXBCK = (MAX / delta) + 1
        }

        {if ($2 <= MIN) bucketNr = MNBCK
           else if ($2 >  MAX) bucketNr = MXBCK
                  else bucketNr = int(($2+delta) / delta)
         cnt[bucketNr]++
         TOT++
        }

END     {printf "      <= %5.2f %6d %6.2f%%\n", MIN, cnt[MNBCK], cnt[MNBCK] / TOT *100
         beg = MIN
         for (bucketNr=MNBCK+1; bucketNr<MXBCK; bucketNr++)     {end = beg + delta
                                                                 printf " %5.2f - %5.2f %6d %6.2f%%\n", beg+.01, end, cnt[bucketNr], cnt[bucketNr] / TOT *100
                                                                 beg = end
                                                                }
         printf "       > %5.2f %6d %6.2f%%\n", MAX, cnt[MXBCK], cnt[MXBCK] / TOT *100
         print "----------------------------"
         print "   Total:", TOT
        }
' file

It may need some polishing, and it may not be the optimal approach. YMMV.
This User Gave Thanks to RudiC For This Post:
# 7  
Old 02-26-2020
Hi RudiC,,,

Tks a lot for the update it works fine...
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

Create range of values and count values.

Gents, It is possible to generate a range of values according to column 1 and count the total of rows in the range. example input 15.3 15.5 15.8 15.9 16.0 16.1 16.8 17.0 17.5 18.0 output desired 15.0 - 15.9 = 4 (10 Replies)
Discussion started by: jiam912
10 Replies

3. Shell Programming and Scripting

Delete row if both percentage values are equal to zero

Hello, I have compiled a script but I have stucked at one point. Each line contains two pcs of % value and what I want to do is to delete any line if both % values are zero. data: expected output: ow3 should be deleted as both percentage value in related line are equal to zero. ... (2 Replies)
Discussion started by: baris35
2 Replies

4. Shell Programming and Scripting

Convert Column Values to a Range of Values

I have a list of columns with values that I need to transform into a row containing the range of each column. For example: "Column A" 1 2 3 4 10 12 14 15 16 17 18 "Column B" 1 4 5 6 (4 Replies)
Discussion started by: newbio
4 Replies

5. Shell Programming and Scripting

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: 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 ... (13 Replies)
Discussion started by: ida1215
13 Replies

6. Shell Programming and Scripting

return counting per hour percentage

We have a monitoring process for a load in unix box, during this process we are writing logs statements for each record, and during this process we are showing the counts per hour. Here is that how we are following log files statements: (just two lines printed here), these statements logged at... (1 Reply)
Discussion started by: skkuchipudi
1 Replies

7. UNIX for Dummies Questions & Answers

Working out the percentage between two values

Hi there, I am totally new to Unix, I am trying to work out the percentage between two values in a ksh shell script and assign the result to a variable. Value1=577 Values2=244 So the calculation would be as follows: ((Value1 - Value2) / Value1) * 100 How would I be able to achieve... (2 Replies)
Discussion started by: NextLevelAndi
2 Replies

8. Shell Programming and Scripting

Howto: easy date range iteration/counting on GNU systems in the shell

Should work in any shell, but requires GNU date, although GNU date seems only to be happy for input dates between 1902 and 2037, inclusive (49673 days). Assume $a and $b hold two dates, e.g. set a=2010-03-27 set b=2010-04-04 Marginally faster: iterator: seq -f "$a +%1.0f days" 1 50000 |... (0 Replies)
Discussion started by: laddiebuck
0 Replies

9. Shell Programming and Scripting

To Create range of values

Hi, I have a file with the below like values with integers only in sorted order (May or may not be in sequence) Eg: File1.txt ----------- 1 2 3 4 5 6 . . . . . 10000 My requirement here is to create a range of values out put to a temp k (4 Replies)
Discussion started by: shiva447
4 Replies

10. Shell Programming and Scripting

How to calculate the percentage for the values in column

Hi, I am having the file which contains the following two columns. 518 _factorial 256 _main 73 _atol 52 ___do_global_ctors 170 ___main 52 ___do_g How can calculate the percentage of each value in the first column ? first need to get the sum of the first column and... (3 Replies)
Discussion started by: saleru_raja
3 Replies
Login or Register to Ask a Question