r - Add values of multiple dataframes together cell by cell

Question

I am trying to add multiple dataframes together but not in a bind fashion.

Is there an easy way to overlay & add dataframes on top of each other? As shown in this picture:

The number of columns will always be same; the row count will differ. I want to sum the cells by row position. So Result[1,1] = Table1[1,1] + Table2[1,1] and so on, such that the resulting frame adds whatever cells have data and resulting table is the size of biggest table's size.

The table are generated dynamically so I'd like to refrain from any hardcoding.

Answer 1

Consider the following two data frames:

table1 <- replicate(4,round(runif(10,0,1),2)) %>% as.data.frame %>% setNames(LETTERS[1:4])
table2 <- replicate(4,round(runif(6,0,1),2)) %>% as.data.frame %>% setNames(LETTERS[1:4])
table1
      A    B    C    D
1  0.81 0.08 0.85 0.89
2  0.88 0.82 0.62 0.77
3  0.12 0.13 0.99 0.02
4  0.17 0.54 0.37 0.62
5  0.77 0.10 0.81 0.34
6  0.58 0.15 0.00 0.56
7  0.61 0.15 0.59 0.15
8  0.52 0.36 0.12 0.99
9  0.83 0.93 0.29 0.30
10 0.52 0.02 0.48 0.46
table2
     A    B    C    D
1 0.95 0.81 0.99 0.92
2 0.18 0.99 0.35 0.09
3 0.73 0.10 0.02 0.68
4 0.37 0.53 0.78 0.02
5 0.48 0.54 0.79 0.83
6 0.75 0.32 0.41 0.04

We might create a new variable called ID from their row numbers and use that to sum the values after binding the rows:

library(dplyr)
library(tibble)
bind_rows(table1 %>% rowid_to_column("ID"),table2 %>% rowid_to_column("ID")) %>%
  group_by(ID) %>%
  summarise(across(everything(),sum))
# A tibble: 10 x 5
      ID     A     B     C     D
   <int> <dbl> <dbl> <dbl> <dbl>
 1     1  1.76  0.89 1.84   1.81
 2     2  1.06  1.81 0.97   0.86
 3     3  0.85  0.23 1.01   0.7 
 4     4  0.54  1.07 1.15   0.64
 5     5  1.25  0.64 1.6    1.17
 6     6  1.33  0.47 0.41   0.6 
 7     7  0.61  0.15 0.59   0.15
 8     8  0.52  0.36 0.12   0.99
 9     9  0.83  0.93 0.290  0.3 
10    10  0.52  0.02 0.48   0.46

A potentially more dangerous base R approach is to subset table1 to the dimensions of table2, and add them together:

table1[seq(1,nrow(table2)),seq(1,ncol(table2))] <- table1[seq(1,nrow(table2)),seq(1,ncol(table2))] + table2
table1
      A    B    C    D
1  1.76 0.89 1.84 1.81
2  1.06 1.81 0.97 0.86
3  0.85 0.23 1.01 0.70
4  0.54 1.07 1.15 0.64
5  1.25 0.64 1.60 1.17
6  1.33 0.47 0.41 0.60
7  0.61 0.15 0.59 0.15
8  0.52 0.36 0.12 0.99
9  0.83 0.93 0.29 0.30
10 0.52 0.02 0.48 0.46