Merging different columns with the same name into single columns

Question

I having a data.frame in which some columns have the same Name. Now I want to merge/add up these columns into single columns. So for example I want to turn....

v1 v1 v1 v2 v2 
 1  0  2  4  1
 3  1  1  1  0

...into...

v1  v2
 3   5  
 5   1

I only found threads dealing with two data.frames supposed to be merged into one but none dealing with this (rather simple?) problem.

The data can be recreated with this:

df <- structure(list(v1 = c(1L, 3L), v1 = 0:1, v1 = c(2L, 1L), 
                v2 = c(4L, 1L), v2 = c(1L, 0L)), 
               .Names = c("v1", "v1", "v1", "v2", "v2"),  
               class = "data.frame", row.names = c(NA, -2L))

Welcome to StackOverflow! Please provide [a reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). — Thomas, Mar 03 '14 at 19:29
Agree with @Thomas, in this case I was able to re-create your data table with `read.table`, but it would have been better if you had `dput`. — BrodieG, Mar 03 '14 at 20:24
Thank you for the answers, it works now. Next time I will of course try to deliver a reproducible example! — bla, Mar 05 '14 at 19:52

score 2 · Answer 1 · answered Mar 03 '14 at 20:21

as.data.frame(lapply(split.default(df, names(df)), function(x) Reduce(`+`, x)))

produces:

  v1 v2
1  3  5
2  5  1

split.default(...) breaks up the data frame into groups with equal column names, then we use Reduce on each of those groups to sum the values of each column in the group iteratively until there is only one column left per group (see ?Reduce, that is what the function does), and finally we convert back to data frame with as.data.frame.

We have to use split.default because split (or really, split.data.frame, which it will dispatch) splits on rows, not columns.

score 0 · Answer 2 · answered Mar 04 '14 at 02:40

You can do this quite easily with melt and dcast from "reshape2". Since there's no "id" variable, I've used melt(as.matrix(df)) instead of melt(df, id.vars="id"). This automatically creates a long version of your data that has "Var1" as representing your rownames and "Var2" as your colnames. Using that knowledge, you can do:

library(reshape2)
dcast(melt(as.matrix(df)), Var1 ~ Var2, 
      value.var = "value", fun.aggregate=sum)
#   Var1 v1 v2
# 1    1  3  5
# 2    2  5  1

Merging different columns with the same name into single columns

2 Answers2