merge rows based on a common column


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting merge rows based on a common column
# 1  
Old 10-16-2008
Bug 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 box1 service my_id
B345 bat2
C431 my_id box1

OUTPUT2
box1 A123 C431
bat2 B345
my_id C431 A123
service A123

Your help is highly appretiated. Thanks in advance. Smilie
# 2  
Old 10-16-2008
Try this:
Code:
awk '{ x[$1]=x[$1] " " $2; y[$2]=y[$2] " " $1; } 
END { 
   for (k in x) print k,x[k] >"OUTPUT1";  
   for (k in y) print k,y[k] >"OUTPUT2"; 
}' INPUT

# 3  
Old 10-17-2008
Bug Thanks!

Thanks Otheus!

It is working perfect. I'll be grateful if u can explan the code a bit.

Thanks again. Smilie
# 4  
Old 10-17-2008
code explained

Code:
awk '
{ 
   x[$1]=x[$1] " " $2;   # Append 2nd field to contents of x[$1] (followed by a space)
   y[$2]=y[$2] " " $1;  # Append 1st field to contents of y[$1] (followed by a space)
} 
# Now: The array "x" contains all the lines keyed by the first field. 
#         The array "y" contains all the lines keyed by the second field.

END { 
   for (k in x) print k,x[k] >"OUTPUT1";  
   for (k in y) print k,y[k] >"OUTPUT2"; 
}' INPUT

This User Gave Thanks to otheus For This Post:
# 5  
Old 10-17-2008
Thanks!

Thanks a lot Otheus.
# 6  
Old 10-17-2008
Code:
awk '{
arr[$1]=sprintf("%s %s",arr[$1],$2)
brr[$2]=sprintf("%s %s",brr[$2],$1)
}
END{
for(i in arr)
        print i""arr[i]
print "-------------------"
for(j in brr)
        print j""brr[j]
}' filename

# 7  
Old 10-17-2008
Thanks!

Thanks summer_cherry. Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Merge selective columns from files based on common key

Hi, I am trying to selectively merge two files based on keys reported in the 1st column. File1: #file1-header1 file1-header2 111 qwe rtz uio 198 asd fgh jkl 165 yxc 789 poi uzt rew 89 lkj File2: #file2-header2 file2-header2 165 ghz nko2 ... (2 Replies)
Discussion started by: dovah
2 Replies

2. 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

3. 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

4. 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

5. 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

6. Shell Programming and Scripting

file merge based on common columns

I have two files 1.txt 34, ABC, 7, 8, 0.9 35, CDE, 6.5, -2, 0.01 2.txt 34, ABC, 9, 6, -1.9 35, CDE, 8.5, -2.3, 5.01 So in both files common columns are 1 and 2 so final o/p should look like 34, ABC, 7, 8, 0.9, 9, 6, -1.9 35, CDE, 6.5, -2, 0.01, 8.5, -2.3, 5.01 I tried using... (3 Replies)
Discussion started by: manas_ranjan
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

Merge rows with common column

Dear all I have big file with two columns 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... (15 Replies)
Discussion started by: AAWT
15 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 based on common, awk help

All, $ cat x.txt z 11 az x 12 ax y 13 ay $ cat y.txt ay TT ax NN Output required: y 13 ay TT x 12 ax NN (3 Replies)
Discussion started by: jkl_jkl
3 Replies
Login or Register to Ask a Question

Featured Tech Videos