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

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
5
  • I tried this method, but I do not get any output Mar 7, 2016 at 14:35
  • @stellar01 Works for me..which version of join you have?
    – heemayl
    Mar 7, 2016 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 Mar 7, 2016 at 14:44
  • @stellar01 Use join --version | head -1
    – heemayl
    Mar 7, 2016 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 Mar 7, 2016 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .