how to merge multiple data frame based on common column

Question

I have three data frame, and i want to merge all three dataset by common column names (Gene)

dt1 <- read.table(header = TRUE, text = "Gene chr log
Sall3    5     -1.5
TRIM5    5     -3.4
PDIA5    2    -2.3 
Nfatc1   2     -3.5",  stringsAsFactors = FALSE)

dt2 <- read.table(header = TRUE, text = "Gene       chr log
Sall3    5     -1.5
TRIM5    5     -3.4
PDIA5    2     -2.3 
DCC      2     -0.4",stringsAsFactors = FALSE)

dt3 <- read.table(header = TRUE, text = "Gene       chr log
Sall3    5     -1.5
TRIM5    5     -3.4
Ctdp1    2     -2.3,
Nfatc1   2      -4.5", stringsAsFactors = FALSE)

expected output

dfinal <- read.table(header = TRUE, text = "Gene       chr log
    Sall3    5     -1.5
    TRIM5    5     -3.4", stringsAsFactors = FALSE)

Don't hold multiple data sets in your global environment. Put them all into a list (see `?mget`) and use the solution in the dupe. — David Arenburg, Oct 08 '15 at 11:16
Actually I think the OP asks for an intersection-like operation rather than a `merge`. — cryo111, Oct 08 '15 at 11:38

Ronak Shah · Answer 1 · 2018-05-15T04:35:00.107

4

You can use the merge function twice

merge(merge(dt1, dt2, by = "Gene"), dt3, by = "Gene")[1:3]

# Gene chr.x log.x
#1 Sall3     5  -1.5
#2 TRIM5     5  -3.4

As @Pierre mentioned, for multiple dataframes nested merge is not a good option. We can use Reduce with merge instead :

Reduce(merge, list(dt1,dt2,dt3))

#   Gene chr  log
#1 Sall3   5 -1.5
#2 TRIM5   5 -3.4

edited May 15 '18 at 04:35

answered Oct 08 '15 at 11:12

Ronak Shah

377,200
20
156
213

how to merge multiple data frame based on common column

1 Answers1