compare multiple files(more than two) with two different columns

Question

I have four different files: file1, file2, file3, file4. Each file has 2 different columns separated by tab. I want to match first column of file1 (as reference) with the first column of second file, third and fourth file and print first column which is matching and second column of each file which has matching first column. files look like:

file 1

Bm1_00085|Bm1_22625    0.263974289
Bm1_00087|Bm1_22620    0.663443490    

file 2

Bm1_00085|Bm1_22625    0
Bm1_57630|Bm1_52870    0

file 3

Bm1_57630|Bm1_54855    0
Bm1_00085|Bm1_22625    4

file 4

Bm1_57630|Bm1_52870    0
Bm1_00085|Bm1_22625    1

output:

Bm1_00085|Bm1_22625 0.263974289 0 4 1

Answer 1

With join and a shell smart enough to know how to deal with <(...):

join <(sort file1) <(sort file2) | join - <(sort file3) | join - <(sort file4)

Output:

Bm1_00085|Bm1_22625 0.263974289 0 4 1

Answer 2

Here is one way you could do it with awk:

parse.awk

# Use the first column of the first file as a key and the second column
# as a value in the h hash
NR==FNR { h[$1] = $2; next }

# If $1 is a key in h append $2 to h[$1]
$1 in h { h[$1] = h[$1] OFS $2 }

# When the input has been exhausted, print h key value pairs
# that contain more than one element 
END { for(k in h)
  if(split(h[k], a) > 1)
    print k OFS h[k]
}

Run it like this:

awk -f parse.awk file1 file2 file3 file4

Output:

Bm1_00085|Bm1_22625 0.263974289 0 4 1

Answer 3

In perl, the tool for this job is a hash. A hash is a set of key-value pairs which makes this sort of cross referencing quite easy.

Note - this will ONLY work if the first field is unique:

#!/usr/bin/env perl
use strict;
use warnings;
my %data;

while (<>) {
    my ( $key, $value ) = split;
    push( @{ $data{$key} }, $value );
}

foreach my $key ( sort keys %data ) {
    if ( @{ $data{$key} } >= @ARGV ) {
        print join( "\t", $key, @{ $data{$key} } ), "\n";
    }
}

Invoke as myscript.pl file1 file2 file3 file4.

It:

• reads a list if files from the command line via <>, and opens them for processing.
• Iterates one line at a time, splitting the line into $key and $value.

• Stores $value in a hash of arrays.

• runs through each key in your hash

• if there are a number elements >= number of command line arguments (e.g. number of files) - prints that line.

Output from this is:

Bm1_00085|Bm1_22625 0.263974289 0   4   1

Note:

Assumes unique 'keys' within all the files.