1

I have 2 csv files (they large and they dont have qual number of rows). Here I present only example files which have the same number of rows):

File1:

NC_008689 2 NA NA NA NA 
NC_002670 3 NA NA NA 186 
NC_002671 9 NA NA NA 107 
NC_009382 25 9 15 NA 100 
NC_003977 45 92 535 NA 492685

File2:

NC_008689   Siphoviridae,Biseptimavirus,Staphylococcus virus 108PVL
NC_002670   Siphoviridae,,Lactococcus phage bIL311
NC_002671   Siphoviridae,,Lactococcus phage bIL312
NC_009382   Myoviridae,Peduovirus,Ralstonia virus RSA1
NC_003977   Hepadnaviridae,Orthohepadnavirus,Hepatitis B virus

Desired output:

Siphoviridae,Biseptimavirus,Staphylococcus virus 108PVL 2   NA  NA  NA  NA
Siphoviridae,,Lactococcus phage bIL311  3   NA  NA  NA  186
Siphoviridae,,Lactococcus phage bIL312  9   NA  NA  NA  107
Myoviridae,Peduovirus,Ralstonia virus RSA1  25  9   15  NA  100
Hepadnaviridae,Orthohepadnavirus,Hepatitis B virus2 45  92  535 NA  492685

I tried:

awk 'FNR==NR { F2[$1]=$2 ; next } $1 in F2 {$1 = F2[$1] ; print } ' File2 File1

but as a result I have:

Siphoviridae,Biseptimavirus,Staphylococcus 2 NA NA NA NA
Siphoviridae,,Lactococcus 3 NA NA NA 186
Siphoviridae,,Lactococcus 9 NA NA NA 107
Myoviridae,Peduovirus,Ralstonia 25 9 15 NA 100
Hepadnaviridae,Orthohepadnavirus,Hepatitis 45 92 535 NA 492685

There is a problem with names having spaces in File2.

1

You can use join and then cut the first field:

join <(sort File2) <(sort File1) | cut -d' ' -f2-

or just join with -o and add all fields you wish to be printed:

join -o 1.2,1.3,1.4,2.2,2.3,2.4,2.5,2.6 <(sort File2) <(sort File1)
  • It's true but my 2 files are much more bigger and have not equal number of rows. Sorry I did not mention that. – k_a_r_o_l Jun 12 at 14:14
  • number of rows should not matter, join can handle this. – pLumo Jun 12 at 14:16
  • It works perfect. Sorry for problem. Thx – k_a_r_o_l Jun 12 at 14:48
0

try

awk 'FNR == NR { u=$1 ; $1="" ; F[u]=$0 ; } 
   $1 in F { $(NF +1) = F[$1] ; $1 = "" ; print }'

where

  • $1 = "" will delete (sort of) $1's value.
  • $(NF +1) = F[$1] append line of file1 at the end of file2
0

Tested with below command and it worked fine

for i in `cat file2|awk '{print $1}'`; do j=`sed -n '/'$i'/p' file2| awk '{print $2,$3,$4}'`;  sed -i "s/$i/$j/g" file1; done

output

Siphoviridae,Biseptimavirus,Staphylococcus virus 108PVL 2 NA NA NA NA 
Siphoviridae,,Lactococcus phage bIL311 3 NA NA NA 186 
Siphoviridae,,Lactococcus phage bIL312 9 NA NA NA 107 
Myoviridae,Peduovirus,Ralstonia virus RSA1 25 9 15 NA 100 
Hepadnaviridae,Orthohepadnavirus,Hepatitis B virus 45 92 535 NA 492685

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.