Multiply columns in dataframe by values given in another dataframe

Question

I've been trying to accomplish this in dplyr but not been able to figure it out.

In one data frame (df1) I have columns with values.

|A  |B  |
|23 |43 |
|24 |11 |

In another data frame (df2) I have valus.

|Column|Value|
|A     |0.12 |
|B     |1.23 |

I want to somehow multiple every cell in column 'A' of df1 by the given value in df2 for that column. Seems like it should be trivial but I've not quite figured out the correct syntax. I suspect I was use mutate, but any advice would be appreciated.

Mostly trying to accomplish this in dplyr but welcome to other suggestions.

Answer 1

You can use mutate(across()), and leverage cur_column() to subset the second data frame

mutate(d1, across(A:B, ~.x*d2[d2$Column==cur_column(),2]))

Output

      A     B
  <dbl> <dbl>
1  2.76  52.9
2  2.88  13.5

Input:

d1 = data.frame(A = c(23,24), B=c(43,11))
d2 = data.frame(Column = c("A","B"), Value = c(.12,1.23))

This can be extended to any number of columns; here is an input with three columns, A,B,C.

d1 = data.frame(A = c(23,24), B=c(43,11), C=c(25,16))
d2 = data.frame(Column = c("A","B", "C"), Value = c(.12,1.23, 0.75))

mutate(d1, across(where(is.numeric), ~.x*d2[d2$Column==cur_column(),2]))


     A     B     C
1 2.76 52.89 18.75
2 2.88 13.53 12.00

There is a much more verbose approach where d1 is pivoted longer and joined to d2, the calculation done, and the result pivoted_wider. It provides the same output:

inner_join(d2, pivot_longer(d1,everything(), names_to = "Column", values_to = "value")) %>% 
  mutate(value = Value*value) %>%
  select(-Value) %>% 
  pivot_wider(names_from=Column, values_from = value, values_fn=list) %>% 
  unnest(everything())

Output:

      A     B     C
  <dbl> <dbl> <dbl>
1  2.76  52.9  18.8
2  2.88  13.5  12  

Answer 2

Multiplies values in d1 column A with value in d2 where Column is A:

library(dplyr)

d1 %>% 
  mutate(A = A * d2$Value[d2$Column=="A"])

     A  B
1 2.76 43
2 2.88 11