CODEWITHSUNDEEP
rdplyr
John Thomas
John Thomas

Reputation: 1105

Multiply a set of values across a dataframe using dplyr

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

Upvotes: 3

Views: 662

Answers (3)

akrun
akrun

Reputation: 887891

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

Upvotes: 0

EJJ
EJJ

Reputation: 1523

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

Upvotes: 4

Onyambu
Onyambu

Reputation: 79338

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

Upvotes: 0

Related Questions