Add two R data frames of different sizes

Question

If two data frames are

  symbol wgt
1     A   2
2     C   4
3     D   6

  symbol wgt
1     A  20
2     D  10

how can I add them so that missing observations for a "symbol" in either data frame are treated as zero, giving

  symbol wgt
1     A   22
2     C   4
3     D   16

Perhaps you could exhance your example data by adding a row with E=16 to the second `data.frame` to support the case *so that missing observations for a "symbol" in either data frame are treated as zero* — R Yoda, Jul 25 '20 at 15:43

Ronak Shah · Answer 1 · 2020-07-25T15:27:47.173

3

You can join the two dataframes by symbol , replace NA with 0 and add the two weights.

library(dplyr)

df1 %>%
  left_join(df2, by = 'symbol') %>%
   mutate(wgt.y  = replace(wgt.y, is.na(wgt.y), 0), 
          wgt  = wgt.x + wgt.y) %>%
  select(-wgt.x, -wgt.y)

#  symbol wgt
#1      A  22
#2      C   4
#3      D  16

data

df1 <- structure(list(symbol = c("A", "C", "D"), wgt = c(2L, 4L, 6L)),
class = "data.frame", row.names = c(NA, -3L))

df2 <- structure(list(symbol = c("A", "D"), wgt = c(20L, 10L)),
class = "data.frame", row.names = c(NA, -2L))

edited Jul 25 '20 at 15:27

answered Jul 25 '20 at 15:25

Ronak Shah

377,200
20
156
213

`sum` with `na.rm = TRUE` might be a shorter way – Ian Campbell Jul 25 '20 at 15:27
Yes, but then I have to add `rowwise` which will make it slower. – Ronak Shah Jul 25 '20 at 15:28
I am not quite sure if a left join will work if only the right side of the join contains a symbol + wgt... (*"so that missing observations for a "symbol" in either data frame are treated as zero"*) – R Yoda Jul 25 '20 at 15:39
1

You could use `full_join` however. – Ian Campbell Jul 25 '20 at 15:50

score 2 · Answer 2 · answered Jul 25 '20 at 15:30

Try this one line solution by pipes:

#Data
library(dplyr)
df1 <- structure(list(symbol = c("A", "C", "D"), wgt = c(2L, 4L, 6L)), class = "data.frame", row.names = c("1", 
"2", "3"))
df2 <- structure(list(symbol = c("A", "D"), wgt = c(20L, 10L)), class = "data.frame", row.names = c("1", 
"2"))

#Code
df1 %>% left_join(df2,by = 'symbol') %>% mutate(wgt = rowSums(.[-1],na.rm=T)) %>% select(c(1,4))

  symbol wgt
1      A  22
2      C   4
3      D  16

R Yoda · Accepted Answer · 2020-07-25T15:55:10.820

With data.table and the data provided in the answer of @RonakShah and @Duck the solution could be a simple aggregation:

# Convert data.frame to data.table (very fast since inplace)
setDT(df1)
setDT(df2)

# combine both data.frames into one data.frame, group by symbol, apply the sum (NAs are ignored = counted as zero)
rbind(df1,df2)[, sum(wgt, na.rm = TRUE), by = symbol]

# Output
   symbol V1
1:      A 22
2:      C  4
3:      D 16

Note: If you want to use base R only (without data.table) you could use aggregate instead:

aggregate(wgt ~ symbol, rbind(df1,df2), sum)

Add two R data frames of different sizes

3 Answers3