1

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 
  • What do you mean "File1 and File2 are in csv format."? You are showing space separated files, not comma-separated. What is the actual format of your input files? – terdon Mar 9 '17 at 8:47
1

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.

  • @yasha Corrected - i had a typo in print. – George Vasiliou Mar 9 '17 at 8:00
  • @yasha - Is it ok for you? I included also an online demo . Check and advise. – George Vasiliou Mar 9 '17 at 8:14
2

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.

  • 1
    Join is a nice solution. I don't know why i forgot to work in this. Maybe because i study awk nowdays. It has been proved by some user benchmarks in SO that using LC_ALL=C in front of join can improve performance even by 40%. Details here – George Vasiliou Mar 9 '17 at 9:02
1
perl -lane '
 if ( @ARGV == 1 ) {
  $h{ $F[0] } = $F[1];
 } else {
  print "$_ $h{ $F[1] }";
 }
' file1 file2
  • Didn't quite work. It just printed file2 verbatim. – Saisha Mar 9 '17 at 7:55
  • Are there commas in your files File1 and File2? – user218374 Mar 9 '17 at 8:03
  • Please elaborate your answer. – countermode Mar 9 '17 at 8:22
  • The answer does not generate correct results, so there is nothing to elaborate. – user218374 Mar 9 '17 at 8:29

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.