1

I have two files:

File1.txt

30    40    A    T    match1    string1
45    65    G    R    match2    string2
50    78    C    Y    match3    string3

File2.txt

match1    60    add1    50    add2
match2    15    add1    60    add2
match3    20    add1    45    add2

and I want to obtain an output that looks like so:

30    40    A    T    match1    string1    60    add1
45    65    G    R    match2    string2    15    add1
50    78    C    Y    match3    string3    20    add1

I want to append column 2 and column 3 from file2.txt to the end of file1.txt if there is a match in column 5 from file1.txt.

I've tried to use this join command:

join -1 5 -2 1 -a 1 -o 1.1 -o 1.2 -o 1.3 -o 1.4 -o 1.5 -o 1.6 -o 2.2 -o 2.3 file1.txt fil2.txt

However, this only seems to print the columns from the first file. Is there any other solutions other than join to tackle this problem?

2

You can do:

join -1 5 -2 1 -o 1.1,1.2,1.3,1.4,1.5,1.6,2.2,2.3 file1.txt file2.txt

Example:

% cat f1.txt 
30    40    A    T    match1    string1
45    65    G    R    match2    string2
50    78    C    Y    match3    string3

% cat f2.txt 
match1    60    add1    50    add2
match2    15    add1    60    add2
match3    20    add1    45    add2

% join -1 5 -2 1 -o 1.1,1.2,1.3,1.4,1.5,1.6,2.2,2.3 f1.txt f2.txt
30 40 A T match1 string1 60 add1
45 65 G R match2 string2 15 add1
50 78 C Y match3 string3 20 add1
  • I tried this method, but I do not get any output – cosmictypist Mar 7 '16 at 14:35
  • @stellar01 Works for me..which version of join you have? – heemayl Mar 7 '16 at 14:39
  • When I type in join version I get usage: join [-a fileno | -v fileno ] [-e string] [-1 field] [-2 field] [-o list] [-t char] file1 file2 – cosmictypist Mar 7 '16 at 14:44
  • @stellar01 Use join --version | head -1 – heemayl Mar 7 '16 at 14:45
  • When I try the above version call. I get an error: join: illegal option -- - . I am not sure if it matters, but I am using an OS X operating system – cosmictypist Mar 7 '16 at 14:54
1

I found a solution:

awk -F "\t" 'FNR==NR {a[$1] = $2 "\t" $3;next} $5 in a{print $0 "\t" a[$5]}' file2.txt file1.txt > outing.txt

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.