CODEWITHSUNDEEP
rdataframemergerbindcbind
AV111
AV111

Reputation: 47

Merging multiple dataframess in R

I have an issue with multiple dataframes needing merging. I tried rbind, cbind and the basic merge. But it doesn't give me satisfactory results. My problem is below:

First df:

Common, Col 2, Col 3
A, 2, 3
B, 3, 4
C, 4, 5
D, 5, 6

Second df:

Common, Col 3, Col 4
B, 4, 5
D, 6, 6
E, 3, 4

Third df:

Common, Col 1, Col 2
A, a, 2
C, f, 4
F, g, 1

When I combine it, I want:

Common, Col 1, Col 2, Col 3, Col 4
A, a, 2, NA, NA
B, NA, 3, 4, 5
C, f, 4, 5, NA
D, NA, 5, 6, 6
E, NA, NA, 3, 4
F, g, 1, NA, NA

My current code for combining multiple df's together uses:

df_list <- mget(ls(pattern="*df"))
New_df <- do.call(rbind.fill, df_list)

The rbind.fill method does not work well. It basically does not collapse the rows as required.

I tried the multmerge() from the link below. But can't get it to work. https://www.r-bloggers.com/merging-multiple-data-files-into-one-data-frame/

Thanks

Upvotes: 0

Views: 124

Answers (2)

akuiper
akuiper

Reputation: 214957

Since you are trying to match columns, it is a join operation, rbind won't do it, you need merge; And merge by default set by = intersect(names(x), names(y)), i.e. common columns, so you can just use Reduce() function to merge the data frames one by one assuming they have already been collected into a list:

Reduce(function(x,y) merge(x, y, all=T), df_list)

#  Common Col.2 Col.3 Col.4 Col.1
#1      A     2     3  <NA>     a
#2      B     3     4     5  <NA>
#3      C     4     5  <NA>     f
#4      D     5     6     6  <NA>
#5      E  <NA>     3     4  <NA>
#6      F     1  <NA>  <NA>     g

Upvotes: 1

OneHundredBC
OneHundredBC

Reputation: 1

An inelegant solution is:

A = data.frame(c1 = c(1, 2), c3 = c('yes', 'no'))
B = data.frame(c2 = c(1, 2), c4 = c('Tokyo', 'Berlin'))

A1 = cbind(A, c2 = rep(NA, 2), c4 = rep(NA, 2))
B1 = cbind(B, c1 = rep(NA, 2), c3 = rep(NA, 2))

rbind(A1, B1)

Which produces

  c1   c3 c2     c4
1  1  yes NA   <NA>
2  2   no NA   <NA>
3 NA <NA>  1  Tokyo
4 NA <NA>  2 Berlin

Upvotes: 0

Related Questions