Multiply columns in dataframe by values given in another dataframe-CodePudding

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.

CodePudding user response：

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))

CodePudding user response：

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

CodePudding user response：

I can think of two ways to do this.

The first way would be to reshape the data in df1 and then inner_join the two data frames together to perform the multiplication:

library(tidyverse)

df1 <- tibble(A = c(23, 24), B = c(43, 11))
df2 <- tibble(Column = c('A', 'B'), Value = c(0.12, 1.23))

df1_long <- df1 %>% 
  mutate(idx = row_number()) %>% 
  pivot_longer(-idx, values_to = 'num')

   idx name    num
  <int> <chr> <dbl>
1     1 A        23
2     1 B        43
3     2 A        24
4     2 B        11

df1_long %>% 
  inner_join(df2, by = c(name = 'Column')) %>% 
  mutate(new_val = num * Value) %>% 
  select(idx, name, new_val) %>% 
  pivot_wider(names_from = name, values_from = new_val)

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

The second uses Map to multiply each column of df1 by the corresponding value of df2. This involves less reshaping than the other solution (though still some), and crucially, relies on every column of df1 having a corresponding value in df2.

df2_wide <- pivot_wider(df2, names_from = Column, values_from = Value)

      A     B
  <dbl> <dbl>
1  0.12  1.23

as.data.frame(Map('*', df1, df2_wide))

     A     B
1 2.76 52.89
2 2.88 13.53