Merge list vectors using string names in R

Question

I have a list containing 5 matrices, each of different sizes, and I would like to combine them all using row names.

Here is a reproducible example of my list (I am using igraph_0.6.5-2 on R version 3.0.1):

x <- list( as.matrix(c(1,4)), as.matrix(c(3,19,11)), as.matrix(c(3,9,8,5)), as.matrix(c(3,10,8,87,38,92)), as.matrix(c(87,8,8,87,38,92)) ) colnames(x[[1]]) <- c("P1") colnames(x[[2]]) <- c("P2") colnames(x[[3]]) <- c("P3") colnames(x[[4]]) <- c("P4") colnames(x[[5]]) <- c("P5") rownames(x[[1]]) <- c("A","B") rownames(x[[2]]) <- c("B","C","D") rownames(x[[3]]) <- c("A","B", "E", "F") rownames(x[[4]]) <- c("A","F","G","H","I","J" ) rownames(x[[5]]) <- c("B", "H","I","J", "K","L") 

which gives me the following list:

 > x [[1]] P1 A 1 B 4 [[2]] P2 B 3 C 19 D 11 [[3]] P3 A 3 B 9 E 8 F 5 [[4]] P4 A 3 F 10 G 8 H 87 I 38 J 92 [[5]] P5 B 87 H 8 I 8 J 87 K 38 L 92 

---

I would like to get something like this:

 > P1 P2 P3 P4 P5 A 1 na 3 3 na B 4 3 9 na 87 C na 19 na na na D na 11 na na na E na na 8 na na F na na 5 10 na G na na na 8 na H na na na 87 na I na na na 38 8 J na na na 92 87 K na na na na 38 L na na na na 92 

Combining them with the do.call function:

 y <- do.call(merge,c(x, by="row.names",all=TRUE)) 

gives the following error:

 Error in fix.by(by.x, x) : 'by' must match numbers of columns 

Any help is appreciated. Thanks!

Answer 1

I would create a helper function to move your row.names() to a column in data.frame and use Reduce() to merge() all data.frame in your list :

 rownames2col <- function(inDF, RowName = ".rownames") { temp <- data.frame(rownames(inDF), inDF, row.names = NULL) names(temp)[1] <- RowName temp } Reduce(function(x, y) merge(x, y, by = ".rownames", all = TRUE), lapply(x, rownames2col)) # .rownames P1 P2 P3 P4 P5 # 1 A 1 NA 3 3 NA # 2 B 4 3 9 NA 87 # 3 C NA 19 NA NA NA # 4 D NA 11 NA NA NA # 5 E NA NA 8 NA NA # 6 F NA NA 5 10 NA # 7 G NA NA NA 8 NA # 8 H NA NA NA 87 8 # 9 I NA NA NA 38 8 # 10 J NA NA NA 92 87 # 11 K NA NA NA NA 38 # 12 L NA NA NA NA 92 

---

The reason for the added step of casting rownames() as a column is that merging row.names creates a column named row.names in the first merge() in Reduce() , which does not allow subsequent list() elements to be conveniently combined.

 > Reduce(function(x, y) merge(x, y, by = "row.names", all = TRUE), x[1:2]) Row.names P1 P2 1 A 1 NA 2 B 4 3 3 C NA 19 4 D NA 11 

---

Update: A data.table approach

You can use a very similar concept with data.table by setting the keep.rownames argument to " TRUE " and setting key to the resulting column " rn ".

 library(data.table) Reduce(function(x, y) merge(x, y, all = TRUE), lapply(x, function(y) data.table(y, keep.rownames=TRUE, key = "rn"))) # rn P1 P2 P3 P4 P5 # 1: A 1 NA 3 3 NA # 2: B 4 3 9 NA 87 # 3: C NA 19 NA NA NA # 4: D NA 11 NA NA NA # 5: E NA NA 8 NA NA # 6: F NA NA 5 10 NA # 7: G NA NA NA 8 NA # 8: H NA NA NA 87 8 # 9: I NA NA NA 38 8 # 10: J NA NA NA 92 87 # 11: K NA NA NA NA 38 # 12: L NA NA NA NA 92 

---

Update 2: the “manual” approach

There is, of course, a manual approach that the for loop helps. This can be faster than the one above because merge rather slow compared to the base subset. Another advantage in terms of speed is that your resulting object is matrix , and many matrix operations are faster than data.frame operations.

 ## Identify the unique "rownames" for all list items Rows <- unique(unlist(lapply(x, rownames))) ## Create a matrix of NA values ## with appropriate dimensions and dimnames myMat <- matrix(NA, nrow = length(Rows), ncol = length(x), dimnames = list(Rows, sapply(x, colnames))) ## Use your `for` loop to fill it in ## with the appropriate values from your list for (i in seq_along(x)) { myMat[rownames(x[[i]]), i] <- x[[i]] } myMat # P1 P2 P3 P4 P5 # A 1 NA 3 3 NA # B 4 3 9 NA 87 # C NA 19 NA NA NA # D NA 11 NA NA NA # E NA NA 8 NA NA # F NA NA 5 10 NA # G NA NA NA 8 NA # H NA NA NA 87 8 # I NA NA NA 38 8 # J NA NA NA 92 87 # K NA NA NA NA 38 # L NA NA NA NA 92