multiply the cells of one dataframe by another based on matching row/column combination R

Question

If I have a dataframe (df1) and I wanted to multiply each cell of the dataframe by the cells of a second dataframe (df2) based on matching column and row ID, how would I do that?

df1:

df1<-data.frame(id=rep(1:100),
               Room1=rnorm(100,0.4,0.5),
               Room2=rnorm(100,0.3,0.5),
               Room3=rnorm(100,0.7,0.5))

df2:

df2<-data.frame(id=rep(1:100),
               Room1=rnorm(100,1,7),
               Room2=rnorm(100,12,13),
               Room3=rnorm(100,4,20))

So the output is a dataframe (df3) where the value in row 1 column 2 of df1 (e.g. df1[1,2]) has been multiplied by the equivalent value in df2 (e.g. df2[1,2]) and df1[2,2]*df2[2,2], etc....

akrun akrun · Accepted Answer · 2021-05-21T18:53:13

If both of them have the same 'id' (based on the example showed) and are in the same order, we can simply multiply two equal sized datasets and assign the output back to the original data i.e. 'df1'

df1[-1] <- df1[-1] * df2[-1]

implies, we are selecting the columns except the first (df1[-1]) and multiply by the same set of columns in second data (df2[-1]) and assign the output back to reflect the changes in first dataset

Assuming that the example is a simple use case and it is not the OP's original data i.e. some 'id' are missing in one of them (or both) and we wanted to match and multiply only those existing in 'df1', an option is a join by 'id' and then do the multiplication (assuming both the datasets have the same column name i.e. 'Room1', 'Room2', 'Room3'

library(data.table)
nm1 <- names(df1)[-1]
nm2 <- paste0('i.', nm1)
setDT(df1)[df2, (nm1) := Map(`*`, .SD, mget(nm2))on = .(id), .SDcols = nm1]

multiply the cells of one dataframe by another based on matching row/column combination R

1 Answers