☰

根据第一列中的值合并两个表

tag-icon • tag-icon awk csv r
根据第一列中的值合并两个表

我有 1 个这样的表（csv 文件）：

         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

第二个表（csv 文件）：

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

我想从第二个表复制到第一个表列 (2:8)，所有内容都基于第一列中的相同值。

输出示例

            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

答案1

在基础 R 中，使用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

相关内容