R: How does to cbind a dataframe in a list by column match names? or by partial leftjoin?

Question

my problem is:

I have a list with 8 dataframes with different column names and similar rownames, so I want to cbind these dataframes by a column match. For example, in this case

I need align the rows of the columns Yd;Yc;Yb;Ya.

myList<-list(
  data.frame(Ya=sample(letters[1:3]),ZYa=sample(1:3),BYa=sample(1:3)),
  data.frame(Yb=sample(letters[1:2]),ZYb=sample(1:2),BYb=sample(1:2)),
  data.frame(Yc=sample(letters[1:4]),ZYc=sample(1:4),BYc=sample(1:4)),
  data.frame(Yd=sample(letters[1:5]),ZYd=sample(1:5),BYd=sample(1:5)))



myList


[[1]]
  Ya ZYa BYa
1  b   3   1
2  a   2   2
3  c   1   3

[[2]]
  Yb ZYb BYb
1  b   2   2
2  a   1   1

[[3]]
  Yc ZYc BYc
1  c   2   2
2  b   3   4
3  d   4   3
4  a   1   1

[[4]]
  Yd ZYd BYd
1  e   5   2
2  d   1   5
3  a   2   4
4  b   3   1
5  c   4   3

My nearest approach was:

library(tidyverse)
library(gdata) #Including cbindX function

myDataFrame<-mylist %>%
  do.call(cbindX,.)

so I have an data frame like this:

    Ya ZYa BYa   Yb ZYb BYb   Yc ZYc BYc Yd ZYd BYd
1    b   3   1    b   2   2    c   2   2  e   5   2
2    a   2   2    a   1   1    b   3   4  d   1   5
3    c   1   3   NA  NA  NA    d   4   3  a   2   4
4    NA  NA  NA  NA  NA  NA    a   1   1  b   3   1
5    NA  NA  NA  NA  NA  NA   NA  NA  NA  c   4   3

But I need something like this:

    Ya ZYa BYa   Yb ZYb BYb   Yc ZYc BYc Yd ZYd BYd
1    b   3   1    b   2   2    b   3   4  b   3   1
2    a   2   2    a   1   1    a   1   1  a   2   4
3    c   1   3   NA  NA  NA    c   2   2  c   4   3
4   NA  NA  NA   NA  NA  NA    d   4   3  d   1   5
5   NA  NA  NA   NA  NA  NA   NA  NA  NA  e   4   3

Can you help me please? Thank you!

StupidWolf · Answer 1 · 2020-11-19T21:02:35.277

Maybe something quite crude like this, first get all the rownames:

rnames = Reduce(union,lapply(myList,"[[",1))

Then cbind on a list that are matched on those rownames:

do.call(cbind,lapply(myList,function(i)i[match(rnames,i[,1]),]))

       Ya ZYa BYa   Yb ZYb BYb   Yc ZYc BYc Yd ZYd BYd
1       a   3   1    a   2   2    a   4   2  a   4   5
2       c   1   3 <NA>  NA  NA    c   3   4  c   2   1
3       b   2   2    b   1   1    b   1   1  b   1   4
NA   <NA>  NA  NA <NA>  NA  NA    d   2   3  d   3   2
NA.1 <NA>  NA  NA <NA>  NA  NA <NA>  NA  NA  e   5   3

If you know the order, then you define it:

rnames = c("b","a","c","d","e")
do.call(cbind,lapply(myList,function(i)i[match(rnames,i[,1]),]))

       Ya ZYa BYa   Yb ZYb BYb   Yc ZYc BYc Yd ZYd BYd
3       b   2   2    b   1   1    b   1   1  b   1   4
1       a   3   1    a   2   2    a   4   2  a   4   5
2       c   1   3 <NA>  NA  NA    c   3   4  c   2   1
NA   <NA>  NA  NA <NA>  NA  NA    d   2   3  d   3   2
NA.1 <NA>  NA  NA <NA>  NA  NA <NA>  NA  NA  e   5   3

but in this example the rows are not order by the match string columns :( — Bryan Castillo, Nov 19 '20 at 20:58
what match strings? oh can you rephrase your question. it is not rownames! — StupidWolf, Nov 19 '20 at 20:59

score 0 · Answer 2 · answered Nov 19 '20 at 22:10

0

We can use cbind.fill from rowr

library(rowr)
do.call(cbind.fill, c(myList, fill = NA))

answered Nov 19 '20 at 22:10

akrun

874,273
37
540
662

this work like cbindX, dont aligned the rows of the columns like a multiple left join, that is what i need – Bryan Castillo Nov 20 '20 at 03:29

R: How does to cbind a dataframe in a list by column match names? or by partial leftjoin?

2 Answers2