1

I have 1 table like this (csv file):

         p1 p10 p16 p19 p25 p3  p5  p6  p8  p9
con1    567 0   3   0   18  17  8   4   6   7
con3    490 7   6   2   23  26  20  14  12  29
con4    737 1   4   1   6   4   1   4   8   5
con5    145 6   4   0   11  17  5   9   22  11
con10   68  0   0   34  4   0   0   0   0   0
con30   46  0   0   8   0   0   0   0   0   0
con2    72  0   0   8   0   1   0   0   0   0

And the second table (csv file):

name    superkingdom    phylum  class   order   family  genus   species
con1    Viruses                                    Pox  Alphaen     Ano
con30   Viruses                           Her     Allo      Bat     Ran
con4    Viruses                                                     Hud
con5    Viruses                                    Mimi     Cafe    Caf
con10   Viruses                                                     Hud
con2    Viruses                          Pico    Picorn    Entero   En
con3    Viruses                                            Phyco    Chloro  

I want to copy to the first table columns (2:8) from second table, everything based on the same value in first column.

Example output

            p1  p10 p16 p19 p25 p3  p5  p6  p8  p9  superkingdom    phylum  class   order   family  genus   species
    con1    567 0   3   0   18  17  8   4   6   7   Viruses                                    Pox  Alphaen Ano
    con3    490 7   6   2   23  26  20  14  12  29  Viruses                                            Phyco    Chloro  
    con4    737 1   4   1   6   4   1   4   8   5   Viruses                     Hud
    con5    145 6   4   0   11  17  5   9   22  11  Viruses                                    Mimi Cafe    Caf
    con10   68  0   0   34  4   0   0   0   0   0   Viruses                     Hud
    con30   46  0   0   8   0   0   0   0   0   0   Viruses         Her Allo    Bat Ran
    con2    72  0   0   8   0   1   0   0   0   0   Viruses         Pico    Picorn  Entero  En
  • 2
    Just in case you are impatient, look at the join command before someone else is so kind to provide a good answer with an example – Tagwint Jun 28 '18 at 14:09
  • 2
    The 2nd file is most certainly not a CSV file. You have no programmable way to indicate empty fields, other than using fixed width method. – glenn jackman Jun 28 '18 at 14:09
0

In base R, using merge (package base):

df1 <- read.csv(text="p1,p10,p16,p19,p25,p3,p5,p6,p8,p9
con1,567,0,3,0,18,17,8,4,6,7
con3,490,7,6,2,23,26,20,14,12,29
con4,737,1,4,1,6,4,1,4,8,5
con5,145,6,4,0,11,17,5,9,22,11
con10,68,0,0,34,4,0,0,0,0,0
con30,46,0,0,8,0,0,0,0,0,0
con2,72,0,0,8,0,1,0,0,0,0")

df2 <- read.csv(text="name,superkingdom,phylum,class,order,family,genus,species
con1,Viruses,,,,Pox,Alphaen,Ano
con30,Viruses,,,Her,Allo,Bat,Ran
con4,Viruses,,,,,,Hud
con5,Viruses,,,,Mimi,Cafe,Caf
con10,Viruses,,,,,,Hud
con2,Viruses,,,Pico,Picorn,Entero,En
con3,Viruses,,,,,Phyco,Chloro")

# by.x=0 joins df1 by rownames
merge(df1, df2, by.x=0, by.y="name")
#   Row.names  p1 p10 p16 p19 p25 p3 p5 p6 p8 p9 superkingdom phylum class order family   genus species
# 1      con1 567   0   3   0  18 17  8  4  6  7      Viruses     NA    NA          Pox Alphaen     Ano
# 2     con10  68   0   0  34   4  0  0  0  0  0      Viruses     NA    NA                          Hud
# 3      con2  72   0   0   8   0  1  0  0  0  0      Viruses     NA    NA  Pico Picorn  Entero      En
# 4      con3 490   7   6   2  23 26 20 14 12 29      Viruses     NA    NA                Phyco  Chloro
# 5     con30  46   0   0   8   0  0  0  0  0  0      Viruses     NA    NA   Her   Allo     Bat     Ran
# 6      con4 737   1   4   1   6  4  1  4  8  5      Viruses     NA    NA                          Hud
# 7      con5 145   6   4   0  11 17  5  9 22 11      Viruses     NA    NA         Mimi    Cafe     Caf

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.