Visit Our UNIX and Linux User Community


Bin iteratively based on each row


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Bin iteratively based on each row
# 1  
Old 08-25-2013
Bin iteratively based on each row

Hi
I have a file some thing like below. I want to bin the data. My Bin size is 100.
Code:
items	number
HELIX1	75 
HELIX6	160    
HELIX2	88    
HELIX19	114   
HELIX5	61    
HELIX4	167


it should consider each elemet under the number column and bin all the lines like below with 100 as an interval. The third column should represent the bin range
Code:
bin_75-175.txt
HELIX1	75	75-175	   
HELIX2	88	75-175    
HELIX19	114	75-175
HELIX6  160	75-175 
HELIX4	167	75-175

Code:
bin_160-260.txt
HELIX6  	160	160-260	
HELIX4       167	160-260

Code:
bin_88-188.txt
HELIX2	88	88-188		   
HELIX19	114	88-188
HELIX6  160	88-188	
HELIX4	167	88-188

and so on based on the number of rows in inpit file


when i searched in the forum i found some thing like this
Code:
awk '{f=sprintf("%d", 1+$2/100); fn[f]="bin"f"_"; print $1,$2>>fn[f]}' file_name

which bins from 0-100 and so on
# 2  
Old 08-25-2013
Not sure where you want to start your bins? At 0? at 75? at 88?
# 3  
Old 08-25-2013
The first element in the number column is 75 so it should bin from 75-175; second element is 160, so bin elements that occur between 160-260 ; and third element is 88 so it has to bin 88-188; fourth element is 114 so 114-214 and so on for each element in the number column.
# 4  
Old 08-25-2013
Perhaps something like this:
Code:
awk '
  NR==1{
    next
  }
  NR==FNR{
    B[$2]=$2+100
    next
  }
  {
    for(i in B) {
      r=i "-" B[i]
      if ( i+0<=$2+0 && $2+0 < B[i]+0 ) print $0, r > ( "bin_" r )
    }
  }
' OFS='\t' file file


Last edited by Scrutinizer; 08-25-2013 at 02:24 PM..
# 5  
Old 08-25-2013
For my trial data set the code provided is not producing any result i am using cygwin
# 6  
Old 08-25-2013
I corrected it in my post. The input file needs to be specified twice..
This User Gave Thanks to Scrutinizer For This Post:
# 7  
Old 08-25-2013
Thank you it works perfectly could you please explain how it works

Previous Thread | Next Thread
Test Your Knowledge in Computers #304
Difficulty: Easy
sed is a stream editor and is used to perform basic text transformations on an input stream (a file or input from a pipeline).
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Splitting single row into multiple rows based on for every 10 digits of last field of the row

Hi ALL, We have requirement in a file, i have multiple rows. Example below: Input file rows 01,1,102319,0,0,70,26,U,1,331,000000113200000011920000001212 01,1,102319,0,1,80,20,U,1,241,00000059420000006021 I need my output file should be as mentioned below. Last field should split for... (4 Replies)
Discussion started by: kotra
4 Replies

2. Shell Programming and Scripting

Merge row based on replicates ID

Dear All, I was wondering if you may help me with an issue. I would like to merge row based on column 1. input file: b1 ggg b2 fff NA NA hhh NA NA NA NA NA a1 xxx a2 yyy NA NA zzz NA NA NA NA NA a1 xxx NA NA a3 ttt NA ggg NA NA NA NA output file: b1 ggg b2 fff NA NA hhh NA NA NA NA NA... (5 Replies)
Discussion started by: giuliangiuseppe
5 Replies

3. Shell Programming and Scripting

Delete duplicate row based on criteria

Hi, I have an input file as shown below: 20140102;13:30;FR-AUD-LIBOR-1W;2.495 20140103;13:30;FR-AUD-LIBOR-1W;2.475 20140106;13:30;FR-AUD-LIBOR-1W;2.495 20140107;13:30;FR-AUD-LIBOR-1W;2.475 20140108;13:30;FR-AUD-LIBOR-1W;2.475 20140109;13:30;FR-AUD-LIBOR-1W;2.475... (2 Replies)
Discussion started by: shash
2 Replies

4. Shell Programming and Scripting

Field widths based on a row

I want to specify field width based on the row with FTR. I can acheive this if column width is constant with: awk 'BEGIN { FIELDWIDTHS = "20 7 14 30" }{print $1,$4}' file file:COL1 COL2 CL3 FTR AA8 S2 CAT2 your comments CC7 ... (5 Replies)
Discussion started by: aydj
5 Replies

5. Shell Programming and Scripting

How to mark the row based on col value.?

Hi Gurus, I have requirement to identify the records based on one column value. the sample file as below: ID AMT, AMT1 100,10, 2 100,20, 3 200,30, 0 200, 40, 0 300, 20, 2 300, 50, 2 400, 20, 1 400, 60, 0 for each ID, there 2 records, if any one record amt1 is 0, the in 4th col add... (5 Replies)
Discussion started by: ken6503
5 Replies

6. Shell Programming and Scripting

Send email based on row count

i have below code to count number of rows in file1.txt, if the row count is more than one then i have sending an email along with file1.txt attached and fail the process(do nothing if count is <=1), if I test individually count part works good but when i include the email part its not working,... (1 Reply)
Discussion started by: srini_106
1 Replies

7. Shell Programming and Scripting

Trying to remove duplicates based on field and row

I am trying to see if I can use awk to remove duplicates from a file. This is the file: -==> Listvol <== deleting /vol/eng_rmd_0941 deleting /vol/eng_rmd_0943 deleting /vol/eng_rmd_0943 deleting /vol/eng_rmd_1006 deleting /vol/eng_rmd_1012 rearrange /vol/eng_rmd_0943 ... (6 Replies)
Discussion started by: newbie2010
6 Replies

8. Shell Programming and Scripting

Deleting a row based on fetched value of column

Hi, I have a file which consists of two columns but the first one can be varying in length like 123456789 0abcd 123456789 0abcd 4015 0 0abcd 5000 0abcd I want to go through the file reading each line, count the number of characters in the first column and delete... (2 Replies)
Discussion started by: swasid
2 Replies

9. Shell Programming and Scripting

How to print column based on row number

Hi, I want to print column value based on row number say multiple of 8. Input file: line 1 67 34 line 2 45 57 . . . . . . line 8 12 46 . . . . . . line 16 24 90 . . . . . . line 24 49 67 Output 46 90 67 (2 Replies)
Discussion started by: Surabhi_so_mh
2 Replies

10. UNIX for Dummies Questions & Answers

Search based on 1st char of a row.

I want to search for the very first character in a row from the file. It is very similar to the way we use Mainframes File-Aid, quick search option. I couldnt find anything helpful with the grep command. Does any one has an idea, how to perform this? (6 Replies)
Discussion started by: videsh77
6 Replies

Featured Tech Videos