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.

can you show in a data frame format?

anuanand
– anuanand

2022-03-27 15:32:22 +00:00
Commented Mar 27, 2022 at 15:32 — anuanand
– anuanand, Commented Mar 27, 2022 at 15:32

langtang · Accepted Answer · 2022-05-04 14:37:08Z

3

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

edited May 4, 2022 at 14:37

answered Mar 27, 2022 at 15:39

langtang

25.3k1 gold badge14 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

TarJae Over a year ago

I thought OP asked only for column A? Otherwise I would also use across!

langtang Over a year ago

you read more carefully than me!! +1 your response! :)

Antonio Canepa Over a year ago

I couldn't generalize this approach to more than two columns, the x*d2[d2$Column==cur_column(),2] indexation didn't work at all. In my case I have to use: d1 %>% mutate(across(where(is.numeric), ~ .x*d2[cur_column()])). The only problem with this, is the creation of a data.frame for each column, but I think with some unnesting behaviour can be avoided. Suggestions accepted (and appreciated!) :)

langtang Over a year ago

perhaps provide a dataset where this is a problem, as I just extended it to three columns, without a problem - see solution/edit

langtang Over a year ago

I've also added a much longer, and I think unnecesary approach uses pivots and joins - it provides the same output

TarJae · Accepted Answer · 2022-03-27 16:24:55Z

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

answered Mar 27, 2022 at 16:24

TarJae

80.2k6 gold badges30 silver badges94 bronze badges

Collectives™ on Stack Overflow

Multiply columns in dataframe by values given in another dataframe

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related