Assigning rank to rows of numbers based on the last column


 
Thread Tools Search this Thread
# 1  
Assigning rank to rows of numbers based on the last column

I have a tab delimited text file that looks like the following:

Code:
ERBB3	0.00097
IL31RA	0.000972
SETD5	0.000972
MCART1	0.000973
CENPJ	0.000973
FNDC6	0.000974

I want to assign a number to each row based on the value in the last column (in the order of increasing value so that the first row will be 1, the second 2, and so on) If two rows have the same value in their last column, then they will be assigned the same number. The resulting output should look like this:

Code:
ERBB3	0.00097           1
IL31RA	0.000972          2 
SETD5	0.000972          2 
MCART1	0.000973          3  
CENPJ	0.000973          3
FNDC6	0.000974          4

How do I go about doing that? Thanks!
# 2  
Try...
Code:
awk '$2>p{c++} {$3=c; print; p=$2}' OFS='\t' file1

This User Gave Thanks to Ygor For This Post:
# 3  
Thanks but this code does not assign numbers to rows where the value in the 2nd column is 0. For example, if the input looks like this:

Code:
abc 0
cde 1
ghi  2

The output will look like this:

Code:
abc 0 
cde 1 1
ghi 2 2


How can the code be modified to achieve that? Thanks!
# 4  
Try...
Code:
awk '$2>p{c++} {$3=c; print; p=$2}' OFS='\t' p='-1' file1

 

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #538
Difficulty: Medium
A programming language that requires you to declare a variable and its type is a non-statically typed language.
True or False?

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Merging rows based on same ID in First column.

Hellow, I have a tab-delimited file with 3 columns : BINPACKER.13259.1.p2 SSF48239 BINPACKER.13259.1.p2 PF13243 BINPACKER.13259.1.p2 G3DSA:1.50.10.20 BINPACKER.13259.2.p2 SSF48239 BINPACKER.13259.2.p2 PF13243 BINPACKER.13259.2.p2 G3DSA:1.50.10.20... (7 Replies)
Discussion started by: anjaliANJALI
7 Replies

2. UNIX for Dummies Questions & Answers

merging rows into new file based on rows and first column

I have 2 files, file01= 7 columns, row unknown (but few) file02= 7 columns, row unknown (but many) now I want to create an output with the first field that is shared in both of them and then subtract the results from the rest of the fields and print there e.g. file 01 James|0|50|25|10|50|30... (1 Reply)
Discussion started by: A-V
1 Replies

3. UNIX for Dummies Questions & Answers

Assigning rank to rows of numbers with directionality

I have a tab-delimited text file where the second column contains in increasing order and includes negative and positive numbers. I want to assign ranks to the rows based on the value of the second column. The tricky part is that the row where the second column is "0", will be assigned "0", the... (3 Replies)
Discussion started by: evelibertine
3 Replies

4. Shell Programming and Scripting

Rank based on column in Unix

Hello guys I have requirement to rank a flat file based on column separated by delimeter I/P 1783747|091411|1000 1783747|091411|2000 1783747|091411|2000 1783747|091411|2000 1783747|091411|3000 1783747|091411|4000 1783747|091411|5000 1783747|091411|6000 1783747|091411|7000... (6 Replies)
Discussion started by: Pratik4891
6 Replies

5. Shell Programming and Scripting

Insert rows based on line numbers

Can I insert rows based on line numbers. Say If I need to insert 1 or more rows in a file from line number 10. Can I do that in UNIX I have a file something like A B C D E F After row C, I wanted to add 2 records as X and Y. I have the line number after C as my reference. Can I... (2 Replies)
Discussion started by: Muthuraj K
2 Replies

6. Shell Programming and Scripting

How to fetch rows based on line numbers or based on the beginning of a word?

I have a file which will have rows like shown below, ST*820*316054716 RMR*IV*11333331009*PO*40.31 REF*IV*22234441009*xsss471-2762 DTM*003*091016 ENT*000006 RMR*IV*2222234444*PO*239.91 REF*IV*1234445451009*LJhjlkhkj471-2762 </SPAN> DTM*003* 091016 RMR*IV*2223344441009*PO*40.31... (18 Replies)
Discussion started by: Muthuraj K
18 Replies

7. Shell Programming and Scripting

Delete rows based on line numbers in a file

I have to find the number of rows in a file and delete those many rows in another file. For example, if I have 3 rows in a file A, i have to delete first 3 rows in anothe file B, I have the code, it works as standalone, when I merge this with m application (c with unix), it doesnt work. ... (2 Replies)
Discussion started by: Muthuraj K
2 Replies

8. Shell Programming and Scripting

column to rows based on another column...

Guys, i have a file in below format where the barcode's are uniq per site but could be repeated for different site. so i want to convert the site column to rows based on the barcode's as below output. your help is appreciated!!! input: SITE BARCODE QTY SP CP 10001 6281103890017 10 50 48... (5 Replies)
Discussion started by: malcomex999
5 Replies

9. UNIX for Dummies Questions & Answers

Find different column numbers among rows in data

I want to find the different column numbers among rows in a file. For example: A001 a b c d e ... N A002 a b c d e ... N A003 a b c d e ... N+1 A004 a b c d e ... N A005 a b c d e ... N+2 : : For most of the lines I will have N columns (say 1000 rows) in each line except the line 3... (5 Replies)
Discussion started by: AMBER
5 Replies

Featured Tech Videos