Merge a list of dataframes together by column value

Question

I have a list of dataframes df_list that looks like this:

 head(df_list[[1]])
       score    name  rank
 921   9718     aba   921
 346   11387    aca   346

head(df_list[[2]]
       score    name  rank
 1080  9023     aba   1080
 156   12276    aca   156

and I would like to merge them together on the name, so that I get something like

       score.1  rank.1   score.2   rank.2
 aba   9718     921      9023      1080 
 aca   11387    346      12276     156

I tried using do.call and cbind like

tmp <- do.call("cbind", df_list)

however, this gives me

head(tmp)
      score    name  rank   score   name   rank
921   9718     aba   921    9718    aba    1080 
346   11387    aca   346    11387   aca    156

and when I try to get the rankings with tmp[tmp$rank] I can only get the first column named rank.

score 2 · Accepted Answer · answered Apr 15 '14 at 14:57

2

Using merge

 merge(dt1,dt2,by='name', suffixes = c(".1",".2"))
  name score.1 rank.1 score.2 rank.2
1  aba    9718    921    9023   1080
2  aca   11387    346   12276    156

If you have more than 2 elements:

ll <- list(dt1,dt2)

Merge <- 
  function(x,y)
merge(x,y,by='name', suffixes = c(".1",".2"))
Reduce(Merge,ll)
 name score.1 rank.1 score.2 rank.2
1  aba    9718    921    9023   1080
2  aca   11387    346   12276    156

answered Apr 15 '14 at 14:57

agstudy

119,832
17
199
261

works, except suffix needs to be changed for more than 2 elements – Niek de Klein Apr 15 '14 at 15:05
@NiekdeKlein you can see [this answer](http://stackoverflow.com/questions/8091303/merge-multiple-data-frames-in-a-list-simultaneously) to deal with the naming problem.. I marked your question as duplicated. – agstudy Apr 15 '14 at 15:07

score 1 · Answer 2 · answered Apr 15 '14 at 14:54

1

cbind() is doing exactly what it is meant to do, placing the columns side-by-side. If you want to merge them, you need to use the merge() function.

If your data always has the same observations (in the same order) then cbind would be appropriate. In that case you just remove the name column from all data.frames except the first and rename the other columns tagging them with a counter.

answered Apr 15 '14 at 14:54

ilir

3,236
15
23

+1! yes in general! but it is always better to provide some code with your answer. – agstudy Apr 15 '14 at 15:09

Merge a list of dataframes together by column value

2 Answers2