Merge multiple data frames by row name

Question

I am trying to combine multiple data frames by row names.

I know how to do this with two:

x = data.frame(a = c(1,2,3), row.names = letters[1:3])
y = data.frame(b = c(1,2,3), row.names = letters[1:3])
merge(x,y, by = "row.names")

But when I try to use the package reshape merge_all(), I get an error message.

z = data.frame(c = c(1,2,3), row.names = letters[1:3])
l = list(x,y,z)
merge_all(l, by = "row.names")

Error in -ncol(df) : invalid argument to unary operator

What is the best way to do this?

Answer 1

Merging with help row.namesdoes strange things - creates a column called Row.names, which makes subsequent merges hard.

, ( , , ). , OP ( , , data.table):

Reduce(merge, lapply(l, function(x) data.frame(x, rn = row.names(x))))

Answer 2

, do.call * apply, :

x = data.frame(X = c(1,2,3), row.names = letters[1:3])
y = data.frame(Y = c(1,2,3), row.names = letters[1:3])
z = data.frame(Z = c(1,2,3), row.names = letters[1:3])

merge.all <- function(x, ..., by = "row.names") {
  L <- list(...)
  for (i in seq_along(L)) {
    x <- merge(x, L[[i]], by = by)
    rownames(x) <- x$Row.names
    x$Row.names <- NULL
  }
  return(x)
}

merge.all(x,y,z)

(, by) merge.all, merge, ... .

Answer 3

Alternatively Reduceand merge:

If you put all the data frames in a list, you can then use grepand cbindto get the data frames with the desired row names.

## set up the data
> x <- data.frame(x1 = c(2,4,6), row.names = letters[1:3])
> y <- data.frame(x2 = c(3,6,9), row.names = letters[1:3])
> z <- data.frame(x3 = c(1,2,3), row.names = letters[1:3])
> a <- data.frame(x4 = c(4,6,8), row.names = letters[4:6])
> lst <- list(a, x, y, z)

## combine all the data frames with row names = letters[1:3]
> gg <- grep(paste(letters[1:3], collapse = ""), 
             sapply(lapply(lst, rownames), paste, collapse = ""))
> do.call(cbind, lst[gg])
##   x1 x2 x3
## a  2  3  1
## b  4  6  2
## c  6  9  3