How to merge and sum two data frames

With dplyr,

library(dplyr)

# add rownames as a column in each data.frame and bind rows
bind_rows(df1 %>% add_rownames(), 
          df2 %>% add_rownames()) %>% 
    # evaluate following calls for each value in the rowname column
    group_by(rowname) %>% 
    # add all non-grouping variables
    summarise_all(sum)

## # A tibble: 7 x 4
##   rowname     x     y     z
##     <chr> <int> <int> <int>
## 1       A     1     2     3
## 2       B     2     3     4
## 3       C     4     6     8
## 4       D     6     8    10
## 5       E     8    10    12
## 6       F     4     5     6
## 7       G     5     6     7

This might need some teaking to get the rownames logic working on a longer example:

dfr <-rbind(df1,df2)
do.call(rbind, lapply( split(dfr, sapply(rownames(dfr),substr,1,1)), colSums))
  x  y  z
A 1  2  3
B 2  3  4
C 4  6  8
D 6  8 10
E 8 10 12
F 4  5  6
G 5  6  7

If the rownames could all be assumed to be alpha characters a gsub solution should be easy.


A solution with base R:

# create a new variable from the rownames
df1$rn <- rownames(df1)
df2$rn <- rownames(df2)

# bind the two dataframes together by row and aggregate
res <- aggregate(cbind(x,y,z) ~ rn, rbind(df1,df2), sum)
# or (thx to @alistaire for reminding me):
res <- aggregate(. ~ rn, rbind(df1,df2), sum)

# assign the rownames again
rownames(res) <- res$rn

# get rid of the 'rn' column
res <- res[, -1]

which gives:

> res
  x  y  z
A 1  2  3
B 2  3  4
C 4  6  8
D 6  8 10
E 8 10 12
F 4  5  6
G 5  6  7

Tags:

R