Multiply and average data from two data.frames into one column using R

Question

I have the following two example data.frames.

 set.seed(12345)
 df1 = data.frame(a=c(rep("a",8), rep("b",5), rep("c",7), rep("d",10)), 
     b=rnorm(30, 6, 2), 
     c=rnorm(30, 12, 3.5), 
     d=rnorm(30, 8, 3)
     )
 df2 = data.frame(p=c("b", "c", "d"), 
     q=c(1.43, 2.14, -2.03)
     )

I would like to create a new data.frame using the base data in df1 and a weighted average using the multiplication factors in df2. The new output df3 would be the same as df1 but with a new column added with values: the row average of ("b" multiplied by 1.43 + "c" multiplied by 2.14, "d" multiplied by -2.03), so that the result is df3:

 df3 = data.frame(a=c(rep("a",8), rep("b",5), rep("c",7), rep("d",10)), 
     b=rnorm(30, 6, 2), 
     c=rnorm(30, 12, 3.5), 
     d=rnorm(30, 8, 3), 
      new=c("24.8645275","44.67937096","29.68621196","19.26714231",
      "25.23142628","27.65882406","11.98590475","-4.92298683",
      "27.29998443","23.47463009","25.80746763","10.16714534",
      "17.52916576","12.33418399","13.73084634","25.55675733",
      "-0.13100614","26.26381852","22.69296138","2.86696252",
      "12.27184531","30.41901753","18.43221894","1.12637556",
      "2.51020245","13.89381723","17.7266222","27.83995036",
      "32.569782","-5.04627832")
      )

How could I do this please?

Answer 1

One dplyr option could be:

df1 %>%
 rowwise() %>%
 mutate(new = sum(across(df2$p) * df2$q))

   a         b     c     d   new
   <fct> <dbl> <dbl> <dbl> <dbl>
 1 a      7.17 14.8   8.45 24.9 
 2 a      7.42 19.7   3.97 44.7 
 3 a      5.78 19.2   9.66 29.7 
 4 a      5.09 17.7  12.8  19.3 
 5 a      7.21 12.9   6.24 25.2 
 6 a      2.36 13.7   2.50 27.7 
 7 a      7.26 10.9  10.7  12.0 
 8 a      5.45  6.18 12.8  -4.92
 9 b      5.43 18.2   9.55 27.3 
10 b      4.16 12.1   4.11 23.5