Merge rows with common column


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Merge rows with common column
# 1  
Old 02-20-2012
Merge rows with common column

Dear all
I have big file with two columns
Code:
         A_AA960715   GO:0006952        
A_AA960715   GO:0008152 
        A_AA960715   GO:0016491         
A_AA960715   GO:0007165         
A_AA960715   GO:0005618 
        A_AA960716   GO:0006952        
A_AA960716   GO:0005618         
A_AA960716   GO:0055114         
A_AA960716   GO:0032440         
A_AA960716   GO:0016023         
A_AA960716   GO:0016310

and I need to have output file
Code:
         A_AA960715    GO:0006952   GO:0008152    GO:0016491    GO:0007165    GO:0005618    
  
       A_AA960716     GO:0006952   GO:0005618    GO:0055114    GO:0032440    GO:0016023    GO:0016310    GO:0016301    GO:0007165

I already tried the commands given by Otheus but it didn't work for me,
please help me urgently

Regards

Last edited by Scott; 02-20-2012 at 09:29 AM.. Reason: Code tags
# 2  
Old 02-20-2012
we have one file and what about the othere file to take the common column and additional column to merge ?

we have two files right ?
Cheers,
RangaSmilie
# 3  
Old 02-20-2012
Dear Ranga,

this is one text file with two columns
Code:
A_AA960715              GO:0006952        
A_AA960715             GO:0008152 
        A_AA960715              GO:0016491         
A_AA960715             GO:0007165

and I need to have new file with two columns where all the values corresponding to one value must be in row and in first column there must not the repetition of value, like this
Code:
A_AA960715                   GO:0006952   GO:0008152    GO:0016491    GO:0007165    GO:0005618

I think you its clear now
thanks

Last edited by Scott; 02-20-2012 at 09:30 AM.. Reason: Code tags
# 4  
Old 02-20-2012
Code:
awk '{a[$1]=a[$1]" "$2}END{for (i in a) print i" "a[i]}' file

# 5  
Old 02-20-2012
Thanks Bartus,

but I got this error
Code:
$ awk '{a[$1]=a[$1]" "$2}END{for (i in a) print i" "a[i]}' file
awk: fatal: cannot open file `file' for reading (No such file or directory)

actually I am new to these commands,
please help me

regards

Last edited by Scott; 02-20-2012 at 09:30 AM.. Reason: Please use code tags
# 6  
Old 02-20-2012
"file" is the filename of the big file... I don't know what it is in your case...
# 7  
Old 02-20-2012
yes I gave the name of file GO.txt it was, but the error was that ,
please tell me what to do then,,
thanks
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Seperated by columns, merge in a file, sort them on common column

Hi All, I have 4 files in below format. I took them as an example. File 1: Cut from position 1-4 then 6-7 then 8-14 then rest left and make them as columns in one new file. Inserting character H to the initial of all line like HCTOT. CTOT 456787897 Low fever CTOR 556712345 High fever... (2 Replies)
Discussion started by: Mannu2525
2 Replies

2. Shell Programming and Scripting

Merge files based on both common and uncommon rows

Hi, I have two files A (2190 rows) and file B (1100 rows). I want to merge the contents of two files based on common field, also I need the unmatched rows from file A file A: ABC XYZ PQR file B: >LMN|chr1:11000-12456: >ABC|chr15:176578-187678: >PQR|chr3:14567-15866: output... (3 Replies)
Discussion started by: Diya123
3 Replies

3. Shell Programming and Scripting

Merge with common column

hi i have two files and i wanted to join them using common column. try to do this using "join" command but that did not help. File 1: 123 9a.vcf hy92.vcf hy90.vcf Index Ref Alt Ref Alt Ref Alt 315 14 0 7 4 ... (6 Replies)
Discussion started by: empyrean
6 Replies

4. Shell Programming and Scripting

Count and merge using common column

I have the following records from multiple files. 415 A G 415 A G 415 A T 415 A . 415 A . 421 G A 421 G A,C 421 G A 421 G A 421 G A,C 421 G . 427 A C 427 A ... (3 Replies)
Discussion started by: empyrean
3 Replies

5. Shell Programming and Scripting

Merging rows using two common rows.

Hi.. My requirement is simple but unable to get that.. File 1 : 3 415 A G 4 421 G . 39 421 G A 2 421 G A,C 41 427 A . 4 427 A C 42 436 G . 3 436 G C 43 445 C . 2 445 C T 41 447 A . Output (4 Replies)
Discussion started by: empyrean
4 Replies

6. Shell Programming and Scripting

How to merge multiple rows into single row if first column matches ?

Hi, Can anyone suggest quick way to get desired output? Sample input file content: A 12 9 A -0.3 2.3 B 1.0 -4 C 34 1000 C -111 900 C 99 0.09 Output required: A 12 9 -0.3 2.3 B 1.0 -4 C 34 1000 -111 900 99 0.09 Thanks (3 Replies)
Discussion started by: cbm_000
3 Replies

7. UNIX for Dummies Questions & Answers

Merge two files with common IDs but unequal number of rows

Hi, I have two files that I would like to merge and think that there should be a solution using awk. The files look something like this: file 1 IDX1 IDY1 IDX2 IDY2 IDX3 IDY3 file 2 IDY1 dataA data1 IDY2 dataB data2 IDY3 dataC data3 Desired output IDX1 IDY1 dataA data1 IDX2 ... (5 Replies)
Discussion started by: katie8856
5 Replies

8. UNIX for Dummies Questions & Answers

Writing a loop to merge multiple files by common column

I have 100 data files labelled 250.1.txt through 250.100.txt. The second column of the data files partially match (there is about %90 overlap). Each data file has 4 columns. I want the merge all these text files by the matching values in the second column. In the output, the first column should... (1 Reply)
Discussion started by: evelibertine
1 Replies

9. Shell Programming and Scripting

"Join" or "Merge" more than 2 files into single output based on common key (column)

Hi All, I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations: 1. I am restrained to 2 input files only. 2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies

10. Shell Programming and Scripting

merge rows based on a common column

Hi guys, Please guide me if you have a solution to this problem. I have tried paste -s but it's not giving the desired output. I have a file with the following content- A123 box1 B345 bat2 C431 my_id A123 service C431 box1 A123 my_id I need two different outputs- OUTPUT1 A123... (6 Replies)
Discussion started by: smriti_shridhar
6 Replies
Login or Register to Ask a Question