R Sum up values of two dataframes of different set of rows and columns

Question

I have two dataframes with an unique identification (variety). They differ in the following:

• dat 1 is also splitted by the type, which occurs twice for each variety.

• the rows of dat 1 and dat 2 are not equal - some varieties miss in dat2.

• the columns by which the operation should be carried out are also not equal, i.e. taste is missing in dat1.

   dat1 <- data.frame(variety = paste0("variety",rep(1:15,each=2)),
             type=rep(c("peanut", "regular"), 15),
             deliciousness = rnorm(30),
             aroma=rnorm(30))
  
  
   dat2 <- data.frame(variety = paste0("variety",rep(1:5,each=1)),
              aroma=rnorm(5),
              taste=rnorm(5),
              deliciousness = rnorm(5))
  

What I would like to do, is adding the values from each variety of dat2 to dat1 by keeping the common columns of dat1 and dat2. Here is an example drawing on the expected operation and output:

How can this be done?

Answer 1

We may do a join

library(data.table)
nm1 <- setdiff(intersect(names(dat1), names(dat2)), "variety")
nm2 <- paste0("i.", nm1)
setDT(dat1)[dat2, (nm1) :=  Map(\(x, y) x + y, 
          .SD, mget(nm2)), .SDcols = nm1, on = .(variety), by = .EACHI]

-checking

# data before the transformation
> head(dat1)
   variety    type deliciousness       aroma
1 variety1  peanut    -0.9706152 -0.20535242
2 variety1 regular     0.3075453  0.23475532
3 variety2  peanut    -0.2970271  0.04790683
4 variety2 regular    -1.0504071 -1.29309694
5 variety3  peanut    -0.4233066 -1.28607961
6 variety3 regular    -0.1596898  1.08967938
> head(dat2)
   variety       aroma         taste deliciousness
1 variety1 -0.35220034 -1.3626623031     0.4344251
2 variety2  0.77826144  1.5310505982     0.4265356
3 variety3  0.09079924 -0.1541972672    -0.3502388
4 variety4 -0.28230279 -0.0006775878     0.9375036
5 variety5  0.77404997  0.4784131548    -1.2330205


# data after transformation
> head(dat1)
    variety    type deliciousness      aroma
     <char>  <char>         <num>      <num>
1: variety1  peanut    -0.5361901 -0.5575528
2: variety1 regular     0.7419704 -0.1174450
3: variety2  peanut     0.1295086  0.8261683
4: variety2 regular    -0.6238715 -0.5148355
5: variety3  peanut    -0.7735454 -1.1952804
6: variety3 regular    -0.5099286  1.1804786
> tail(dat1)
     variety    type deliciousness      aroma
      <char>  <char>         <num>      <num>
1: variety13  peanut    -0.1524664  1.5336794
2: variety13 regular     0.9625814  0.5859345
3: variety14  peanut     0.6023937  1.0531359
4: variety14 regular    -1.0028524 -0.4119777
5: variety15  peanut    -2.1943440  0.9352057
6: variety15 regular     1.3153406 -1.0531512