Multiply a set of values across a dataframe using dplyr

user_9393218 0
Viewed 127

So I have a dataframe as follows:

ID    A  B  C
1    .3 .3 .4
2    .1 .5 .4
3    .7  0 .3

And I have the following:

ID  VALUE
1      10
1       5
1     100
2      30
3      34
2      12

I basically want to multiply my VALUE across my Df1 to produce those variables. So for each instance of the ID, the appropriate multipliers are used. Dplyr solution is needed here.

My final goal:

ID  VALUE     A   B     C
1      10     3   3     4
1       5   1.5 1.5     2 
1     100    30  30    40
2      30     3  15    12
3      34  23.8   0  10.2
2      12   1.2   6   4.8

EDIT: The ID variable is a character

dplyr r
3 Answers

One way is the merge the two by ID then multiply across those variables you want.

library(tibble)
library(dplyr)

df1 <- tribble(
  ~ID,    ~A,  ~B,  ~C,
  1,  .3, .3, .4,
  2,   .1, .5, .4,
  3,    .7,  0, .3
)

df2 <- tribble(
  ~ID,  ~VALUE,
  1,      10,
  1,       5,
  1,     100,
  2,      30,
  3,      34,
  2,      12
)

left_join(
  df2, df1, by = "ID"
) %>% 
  mutate_at(vars(A, B, C), ~VALUE*.)


# A tibble: 6 x 5
     ID VALUE     A     B     C
  <dbl> <dbl> <dbl> <dbl> <dbl>
1     1    10   3     3     4  
2     1     5   1.5   1.5   2  
3     1   100  30    30    40  
4     2    30   3    15    12  
5     3    34  23.8   0    10.2
6     2    12   1.2   6     4.8

EDIT:

To mutate all variables at once, replace the last chunk of code for:

left_join(
  df2, df1, by = "ID"
) %>% 
  mutate(across(-c(ID, VALUE), ~VALUE*.x))
user_61689560

In base R you could do:

n <- ncol(df1)
A <- merge(df1, df2[c(1, rep(2, n-1))])
A[2:n] * A[(2:n) + n - 1]
     A    B    C
1  3.0  3.0  4.0
2  1.5  1.5  2.0
3 30.0 30.0 40.0
4  3.0 15.0 12.0
5  1.2  6.0  4.8
6 23.8  0.0 10.2
user_83802720

We can use data.table

library(data.table)
nm1 <- names(df1)[-1]
setDT(df2)[setDT(df1), (nm1) := lapply(mget(paste0("i.", nm1)),
             `*`, VALUE),on = .(ID)]

-output

df2
#   ID VALUE    A    B    C
#1:  1    10  3.0  3.0  4.0
#2:  1     5  1.5  1.5  2.0
#3:  1   100 30.0 30.0 40.0
#4:  2    30  3.0 15.0 12.0
#5:  3    34 23.8  0.0 10.2
#6:  2    12  1.2  6.0  4.8
user_37322710
Related