Comparing data from two columns

[ron128]

Hello everybody,
I am analysing variant calls from two different pipelines. What i need to do for my downstream analysis is combine all the variant calls into a single file, column.

eg:

file 1:
Chr Position Chr_position gene_id
1 1234 1_1234 ABCD
1 12345 1_12345 ABCD
1 123456 1_123456 ZXYW

file2:
Chr Position Chr_position protein_id
1 1234 1_1234 ABCD1
2 1234 2_1234 EFGH2
2 123456 2_1234566 EFGH2
1 12345 1_12345 ABCD1
1 123456 1_123456 ABCD1

What i would like to do is the following:

file 3:
Chr Position Chr_position gene_id protein_id
1 1234 1_1234 ABCD ABCD1
1 12345 1_12345 ABCD ABCD1
1 123456 1_123456 ZXYW ABCD1
2 1234 2_1234 EFGH2
2 123456 2_1234566 EFGH2

The number of rows in both the files are different, so using the Match option in excel is not going to work out. using Mysql is an option, but I am not that well versed in SQL. is there a way i could do this using AWK or any other scripting language? I am at a loss working on this! Any inputs would be mightily appreciated. Thanks!

[pallevillesen]

>sort
>join

cat file1 |sort >file1.sort
cat file2 |sort >file2.sort

[[email protected] ~]$ join -v 1 -j 3 file1.sort file2.sort # All lines from file 1 found a match
[[email protected] ~]$ join -v 2 -j 3 file1.sort file2.sort # Lines missing a match
2_1234 2 1234 EFGH2
2_1234566 2 123456 EFGH2
[[email protected] ~]$ join -j 3 file1.sort file2.sort # Matched lines
1_1234 1 1234 ABCD 1 1234 ABCD1
1_12345 1 12345 ABCD 1 12345 ABCD1
1_123456 1 123456 ZXYW 1 123456 ABCD1
[[email protected] ~]$

[ron128]

many thanks! I will try this out and post the results.

>sort
>join

cat file1 |sort >file1.sort
cat file2 |sort >file2.sort

[[email protected] ~]$ join -v 1 -j 3 file1.sort file2.sort # All lines from file 1 found a match
[[email protected] ~]$ join -v 2 -j 3 file1.sort file2.sort # Lines missing a match
2_1234 2 1234 EFGH2
2_1234566 2 123456 EFGH2
[[email protected] ~]$ join -j 3 file1.sort file2.sort # Matched lines
1_1234 1 1234 ABCD 1 1234 ABCD1
1_12345 1 12345 ABCD 1 12345 ABCD1
1_123456 1 123456 ZXYW 1 123456 ABCD1
[[email protected] ~]$

[ron128]

Dude, Many many thanks! this is working out absolutely fine now. Looks like i should explore more of the Unix environment tools! This has streamlined my analysis and cut down a lot of time. Thanks a loT!