Join 2 files with multiple columns: awk/grep/join?


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Join 2 files with multiple columns: awk/grep/join?
# 1  
Old 12-01-2009
Data Join 2 files with multiple columns: awk/grep/join?

Hello,
My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns:

File A: (tab-delimited)
PDB CHAIN Start End Fragment
1avq A 171 176 awyfan
1avq A 172 177 wyfany
1c7k A 2 7 vtvtyd
1c7k A 3 8 tvtydp

File B: (tab-delimited)
PDB CHAIN Start End Fragment

1cfe A 104 109 awyfan
1cfe A 105 110 lgcgra
1dk0 A 50 55 awyfan
1d3g A 83 88 fveigs
1d3g A 84 89 vtvtyd
1dk0 A 51 56 gsqyai

I want to join the rows of two tables based on overlaps of the fifth column (column fragment). As such, the output should read:

Fragment PDB CHAIN Start End PDB CHAIN Start End
awyfan 1avq A 171 176 1cfe A 104 109
awyfan 1avq A 171 176 1dk0 A 50 55
vtvtyd 1c7k A 2 7 1d3g A 84 89

Kindly note that there can be multiple overlaps between Files A and B.

I have read of using the join function in Unix, but when I tried it I ended up with output of File A only. (I tried join -1 1 FileA FileB) based on reading a similar thread.
Alternatively, I know that I can use awk (NC=='5' I think), but I am not very familiar with the script. I know how to fgrep if the file contains a single column, but not multiple columns.
Is there a simple way to write this???

Your help will be really appreciated!
Thanks in advance!
DG
# 2  
Old 12-01-2009
I think you'll probably have to use something like awk since you also reorder 5 column to column 1. E.g.:
Code:
awk '{i=$5;$5=x} NR==FNR{A[i]=$0;next} A[i]{print i,A[i]$0}' fileA fileB


Last edited by Scrutinizer; 12-01-2009 at 09:06 PM..
# 3  
Old 12-01-2009
Code:
join -t'TAB' -j5 -o1.5,1.1,1.2,1.3,1.4,2.1,2.2,2.3,2.4 <(sort -k5 FileA) <(sort -k5 FileB)


Last edited by binlib; 12-01-2009 at 08:40 PM..
# 4  
Old 12-01-2009
Thank you both for your replies. Unfortunately I have tried both of them,

1. with the awk script I only ended up getting the headers as the output:
PDB CHAIN StartPos EndPos PDB CHAIN StartPos EndPos

2. With the join command I output to a txt file but I ended up with nothing?

Could you both kindly explain the code to me? Maybe if I get the logic then I can modify it accordingly. Thanks!

---------- Post updated at 04:45 PM ---------- Previous update was at 04:39 PM ----------

I doubled checked, I had wrongly-formatted files so the commands weren't working properly. Both working find now! Smilie
Thank you very much!!

PS: Is there a way however to modify it so that I don't end up with duplicates:
For example, I end up with
glelk 1avq A 129 133 1avq A 129 133
which is basically an identical row in both FilesA and FilesB
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies

2. Shell Programming and Scripting

Join and merge multiple files with duplicate key and fill void columns

Join and merge multiple files with duplicate key and fill void columns Hi guys, I have many files that I want to merge: file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: (5 Replies)
Discussion started by: yjacknewton
5 Replies

3. Shell Programming and Scripting

Join two files combining multiple columns and produce mix and match output

I would like to join two files when two columns in each file matches with each other and then produce an output when taking multiple columns. Like I have file A 1234,ABCD,23,JOHN,NJ,USA 2345,ABCD,24,SAM,NY,USA 5678,GHIJ,24,TOM,NY,USA 5678,WXYZ,27,MAT,NJ,USA and file B ... (2 Replies)
Discussion started by: mady135
2 Replies

4. Shell Programming and Scripting

Sort and join multiple columns using awk

Is it possible to join all the values after sorting them based on 1st column key and replace empty rows with 0 like below ? input a1 0 a1 1 a1 1 a3 1 b2 1 a2 1 a4 1 a2 1 a4 1 c4 1 a3 1 d1 1 a3 1 b1 1 d1 1 a4 1 c4 1 b2 1 b1 1 b2 1 c4 1 d1 1 output... (8 Replies)
Discussion started by: quincyjones
8 Replies

5. Shell Programming and Scripting

Join 4 files on first three columns

Hi, Can someone suggest me on how to join 4 files by comparing the first three columns? ---------- Post updated at 03:56 PM ---------- Previous update was at 03:42 PM ---------- Hope it helps someone. I was looking online for a solution and on stackoverflow, I found a solution and tried... (6 Replies)
Discussion started by: jacobs.smith
6 Replies

6. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the... (5 Replies)
Discussion started by: evelibertine
5 Replies

7. Shell Programming and Scripting

Awk - join multiple files

Is it possible to join all the files with input1 based on 1st column? input1 a b c d e f input2 a b input3 a e input4 c (2 Replies)
Discussion started by: quincyjones
2 Replies

8. Shell Programming and Scripting

Join multiple files by column with awk

Hi all, I searched through the forum but i can't manage to find a solution. I need to join a set of files placed in a directory (~1600) by column, and obtain an output with first and second column common to each file, but following columns are taken from the file in the list (precisely the fourth... (10 Replies)
Discussion started by: macsx82
10 Replies

9. Programming

sql,multiple join,outer join issue

example sql: select a.a1,b.b1,c.c1,d.d1,e.e1 from a left outer join b on a.x=b.x left outer join c on b.y=c.y left outer join d on d.z=a.z inner join a.t=e.t I know how single outer or inner join works in sql. But I don't really understand when there are multiple of them. can... (0 Replies)
Discussion started by: robbiezr
0 Replies

10. Shell Programming and Scripting

Join columns from 2 files

Hello guys. I need to join columns of Start Time and End time of 2 files. The Examples is: File 1 APPLICATION GROUP_NAME JOB_NAME ODATE STATUS START_TIME END_TIME :: MODCMPDA EDPONLINE MC00A1700 071102 Ended OK 20071102 17:00:01 File 2 APPLICATION GROUP_NAME JOB_NAME ODATE... (2 Replies)
Discussion started by: osramos
2 Replies
Login or Register to Ask a Question