Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Choosing between repeated entries based on a column field

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Choosing between repeated entries based on a column field
# 1  
Old 09-21-2013
Choosing between repeated entries based on a column field
Hello, I have an input file:
Code:
LOC_Os04g01890\LOC_Os05g17604    0.051307       
LOC_Os04g01890\LOC_Os05g17604    0.150977       
LOC_Os04g01890\LOC_Os05g17604    0.306231      
LOC_Os04g01890\LOC_Os06g33100    0.168037       
LOC_Os04g01890\LOC_Os06g33100    0.236293       
LOC_Os04g01890\LOC_Os07g03590    0.109948      
LOC_Os04g01890\LOC_Os07g03590    0.12325

I want to select the largest of each repeated entries based on Column 2
Desired output:


Code:
      LOC_Os04g01890\LOC_Os05g17604    0.306231       
  
      LOC_Os04g01890\LOC_Os06g33100    0.236293       
  
      LOC_Os04g01890\LOC_Os07g03590    0.12325

Wanted to know a shell command which can do this. Thanks
Last edited by Franklin52; 09-23-2013 at 03:42 AM.. Reason: Fixed code tags
# 2  
Old 09-21-2013
Using awk:
Code:
awk '{if(A[$1]<$2||!(A[$1])) A[$1]=$2}END{for(k in A) print k,A[k]}' file

This User Gave Thanks to Yoda For This Post:
Sanchari
# 3  
Old 09-21-2013
If order of the output does not matter:
Code:
perl -lane '$max{$F[0]} = $F[1] unless exists $max{$F[0]}; $max{$F[0]} = $F[1] if $F[1] > $max{$F[0]};
END{ print "$_ $max{$_}" for keys %max}' file

# 4  
Old 09-21-2013
Hi,
With sort:
Code:
sort -k2,2 -rn file | sort -k1,1 -u

Regards.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Append no of times a column is repeated at the end

Hi folks, Iam working on a bash script, i need to print how many times column 2 repeated at the end of each line. Input.txt COL1 COL2 COL3 COL4 1 XX 45 N 2 YY 34 y 3 ZZ 44 N 4 XX 89 Y 5 XX 45 N 6 YY 84 D 7 ZZ 22 S Output.txt COL1 COL2 COL3 COL4 COL5 1 XX 45 N 3 2 YY 34... (6 Replies)
Discussion started by: tech_frk
6 Replies

2. Shell Programming and Scripting

Split file based on a column/field value

Hi All, I have a requirement to split file into 2 sets of file. Below is a sample data of the file AU;PTN;24EX;25-AUG-14;AU;123;SE;123;Test NN;;;;ASD; AU;PTN;24EX;25-AUG-14;AU;456;SE;456;Test NN;;;;ASD; AU;PTN;24EX;25-AUG-14;AU;147;SE;147;Test NN;;;;ASD;... (6 Replies)
Discussion started by: galaxy_rocky
6 Replies

3. Shell Programming and Scripting

Choosing rows based on column values

I have a .csv file: A,B,0.6 C,D,-0.7 D,E,0.1 A,E,0.45 D,G, -0.4 I want to select rows based on the values of the 3rd columns such that it is >=0.5 or <= -0.5 Thanks. A,B,0.6 D,G, -0.7 (1 Reply)
Discussion started by: Sanchari
1 Replies

4. Shell Programming and Scripting

Choosing between repeated entries based on the "absolute values" of a column

Hello, I was looking for a way to select between the repeated entries (column1) based on the values of absolute values of column 3 (larger value). For example if the same gene id has FC value -2 and 1, I should get the output as -2. Kindly help. GeneID Description FC ... (2 Replies)
Discussion started by: Sanchari
2 Replies

5. Shell Programming and Scripting

Getting the most repeated column

Hi all , i want to get the most repeated column in my file File: name,ID adam,12345 ----1 adam,12345 ----2 adam,934 adam,12345 ----3 john,14 john,13 john,25 ----1 john,25 ----2 tom,1 -----1 tom,2 -----1 so my output to be (5 Replies)
Discussion started by: teefa
5 Replies

6. Shell Programming and Scripting

Find repeated word and take sum of the second field to it ,for all the repeated words in awk

Hi below is the input file, i need to find repeated words and sum up the values of it which is second field from the repeated work.Im trying but getting no where close to it.Kindly give me a hint on how to go about it Input fruits,apple,20,fruits,mango,20,veg,carrot,12,veg,raddish,30... (11 Replies)
Discussion started by: 100bees
11 Replies

7. Shell Programming and Scripting

Read in 2-column CSV, output many files based on field

Is there a way to read in a two-columned CSV file, and based on the fields in 1st column, output many different files? The input/output looks something like: input.csv: call Call Mom. call Call T-Mobile. go Go home. go Go to school. go Go to gas station. play Play music. play Play... (4 Replies)
Discussion started by: pxalpine
4 Replies

8. Emergency UNIX and Linux Support

[Solved] Extract records based on a repeated column value

Hi guys, I need help in making a command to find some data. I have multiple files in which multiple records are present.. Each record is separated with a carriage return and in each record there are multiple fields with each field separated by "|" what i want is that I want to extract... (1 Reply)
Discussion started by: m_usmanayub
1 Replies

9. UNIX for Dummies Questions & Answers

Average for repeated elements in a column

I have a file that looks like this 452 025_E3 8 025_E3 82 025_F5 135 025_F5 5 025_F5 23 025_G2 38 025_G2 71 025_G2 9 026_A12 81 026_A12 10 026_A12 some of the elements in column2 are repeated. I want an output file that will extract the... (1 Reply)
Discussion started by: FelipeAd
1 Replies

10. Shell Programming and Scripting

Deleting repeated strings in column 2

Hi to all, I have a file where the subject could contain "Summarized Availability Report" or only "Summarized Report" If the subject is "Summarized Availability Report" I want to apply it Scrip1 and if the subject is "Summarized Report" I want to apply it Scrip2. 1-) I would like you... (5 Replies)
Discussion started by: cgkmal
5 Replies
Login or Register to Ask a Question