Print values from one column to another between two files based on patterns in each row

Question

File 1 contains two columns. Column 1 has names of metabolic pathways and column 2 has the number of genes present in that pathway:

pathway1 3   
pathway2 4  
pathway3 5  
pathway4 6  
pathway5 9

File 2 has three columns. Column 1 has group number, column 2 has metabolic pathway names belonging to a particular group, column 3 has number of genes present in each pathway:

group1  pathway1  2   
group1  pathway4  5  
group1  pathway2  3  

group2  pathway2 1  
group2  pathway3 2 

File1 and File2 are in csv format.

Output required:
Based on the name of the pathway, how can I print the number of genes from column 2 of File1 beside column 3 of File2, so that it looks like this:

group1  pathway1 2 3   
group1  pathway4 5 6  
group1  pathway2 3 4  

group2  pathway2 1 4  
group2  pathway3 2 5 

Answer 1

That's what join was made for:

$ join -o 1.1,1.2,1.3,2.2 -12 -21 <(sort -k2 file2) <(sort file1) 
group1 pathway1 2 3
group2 pathway2 1 4
group1 pathway2 3 4
group2 pathway3 2 5
group1 pathway4 5 6

Or, if your input files are actually comma separated as you suggest, but don't show, in your question:

$ join -t, -o 1.1,1.2,1.3,2.2 -12 -21 <(sort -t, -k2 file2) <(sort file1) 
group1,pathway1,2,3
group2,pathway2,1,4
group1,pathway2,3,4
group2,pathway3,2,5
group1,pathway4,5,6

join will join lines of two files on a common field. The -o sets the output format. Here, I am telling it to print the fields 1, 2 and 3 of the first file (1.1,1.2,1.3), followed by the second field of the second file (2.2). The -1 and -2 set the join field in each file. So, -12 -21 means "join on the second field of file1 and the 1st of file2". Finally, join requires sorted input so we sort both files before passing them to join.

Answer 2

This should be enough in awk:

awk 'NR==FNR{a[$1]=$2;next}{print $0,a[$2]}' file1 file2

Your sample files are presented as space separated and above code will work for space separated files.

If your file1 file2 are comma seperated, then you could do:

awk 'BEGIN{FS=OFS=","}NR==FNR{a[$1]=$2;next}{print $0,a[$2]}' file1 file2

PS: In awk FS corresponds to the input field separator and OFS to the output field separator. The default FS and OFS is "space" and can be ommitted if space is your case (i.e ommitted in the very first code)

If you want to combine different input field separator and different output field separator then you can do something like this:

awk 'BEGIN{FS=" ";OFS=","}NR==FNR{a[$1]=$2;next};$1=$1{print $0,a[$2]}' file1 file2 
#The $1=$1 part is required to redisign the record according to the different Output Field Separator OFS

Last code for space separated input files (as exactly appear in your question) and output with comma separation will provide:

group1,pathway1,2,3                                                                                                                                                             
group1,pathway4,5,6                                                                                                                                                             
group1,pathway2,3,4                                                                                                                                                             
group2,pathway2,1,4                                                                                                                                                             
group2,pathway3,2,5

Test it online here.

PS: For space separated files, you can assign OFS="\t" (tab) for more nice looking output.

Answer 3

perl -lane '
 if ( @ARGV == 1 ) {
  $h{ $F[0] } = $F[1];
 } else {
  print "$_ $h{ $F[1] }";
 }
' file1 file2