-1

I have two files, In File1 there are Gene Interactions (Gene Symbol) and the other File2 has the Gene Symbol and its corresponding Ensembl ID.

File1

GeneA GeneB
GeneA GeneD
GeneC GeneB

File2

GeneA    Ensembl1
GeneB    Ensembl2
GeneC    Ensembl3 

Now I want a file which has the output like this File3

GeneA Ensembl1 GeneB Ensembl2
GeneA Ensembl1 GeneD NA
GeneC Ensembl3 GeneB Ensembl2

If you notice there was no GeneD in File2 and so when no Ensembl ID was found for this gene, NA was entered against it. Can anybody help me achieve it?

0
perl -lane '
   @ARGV and $h{$F[0]}=$F[1],next;
   print join $", map { $_ , $h{$_} // "NA" } @F;
' File2 File1

Results

GeneA Ensembl1 GeneB Ensembl2
GeneA Ensembl1 GeneD NA
GeneC Ensembl3 GeneB Ensembl2

Working

  • Supply File2 first, then File1 in the arguments list to Perl.
  • Build up a hash %h whose keys are GeneA,GeneB,etc. => values are Ensembles from File2. Note while processing File2, the @ARGV has one element so @ARGV in scalar context returns a true.
  • During the time of File1 read-in, @ARGV reduces to zero, so the first line is not evaluated. The 2nd line will print the fields after an appropriate mapping transform and joined together via spaces ($" defaults to space).
2

awk solution:

awk 'NR==FNR{ a[$1]=$2; next }
     { $1=$1 FS (($1 in a)? a[$1]:"NA"); $2=$2 FS (($2 in a)? a[$2]:"NA"); }1' File2 File1

The output:

GeneA Ensembl1 GeneB Ensembl2
GeneA Ensembl1 GeneD NA
GeneC Ensembl3 GeneB Ensembl2

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.