Compare one of the columns of two seperate files and write the raw of the second file next to first file if match.

Question

[emre]

I have two files

The first file should remain as how it is and

If the 4th column of the second file is occurs in the 7th column of the first file -> write that row of the second file to the end of corresponding row of the first file

Could you please help on that.

Thanks

[iraun]

I suggest you http://stackoverflow.com/ for this kind of technical questions.
For other hand, when you ask this type of questions, it is highly recommended to add an example of input(s) and desired output file(s).

[Devon Ryan]

What have you tried?

[venu]

can you provide a few line of your 2 files

Answer 1

[venu]

​​awk 'FNR==NR{a[$7]=$0;next}{if(b=a[$4]){print a}}' file1 file2 > result

This should work if I understood correctly

Answer 2

[emre]

Thank you very much for your help, but it didn't work. What I want to do is that I have two files File1.txt and File2.txt. I want to compare third column in File1.txt and and second column in File2.txt. If found a same value write all raw in the first file next to corresponding raw.

Thanks again for your answers

File1.txt

1    2    A   3
4    5    A   6
7    8    B   9
10   11   C   12

File2.txt

13   A   16
14   B   17
15   D   18

Output.txt

1    2    A   3    13   A   16
4    5    A   6    13   A   16
7    8    B   9    14   B   17
10   11   C   12   .    .   .

[PoGibas]

Please re-post this as an edit to your original answer. You can easily achieve wanted result using join (GNU) or merge (R), there are many examples of using those commands here on biostars.

[venu]

Your original question - 'If the 4th column of the second file is occurs in the 7th column of the first file -> write that row of the second file to the end of corresponding row of the first file'.

If you have any edits please do them in original question.

[emre]

Ok. I understand that. Thanks

Answer 3

[iraun]

Try this:

awk 'FNR==NR{a[$2]=$0;next}{if($3 in a){print $0"\t"a[$3]}else{print $0}}' file2 file1

[emre]

Thank you so much. It worked.