R multiply columns by values in second dataframe

Question

I have two dataframes: df1

plot   grass moss   rock    other     stuff  
a      4      0     0       text      3
b      2      2     3       text      4
c      10     0     0       text      0
d      3      1     0       text      9

and df2

Cover  value
grass   5
moss    2
rock    3

I would like to multiply the values in df1 by the corresponding value in df2. The solution should be applicable for a large dataset.

Result df3

plot  grass moss    rock    
a     20      0     0      
b     10      4     9      
c     50      0     0       
d     15      2     0

My impression is that you want to multiply the `grass` column in `df1` by the factor in `df2`, etc for the other variables in `df2`. If this is the case, this is not reflected in your `df3`. — Paul Hiemstra, Jan 29 '14 at 10:57

score 6 · Accepted Answer · answered Jan 29 '14 at 11:15

Your data:

df1 <- read.table(
  text = 
    "plot   grass moss   rock    other     stuff  
    a      4      0     0       text      3
    b      2      2     0       text      4
    c      10     0     0       text      0
    d      3      1     0       text      9",
  header = TRUE
)

df2 <- read.table(
  text = 
    "Cover  value
    grass   5
    moss    2
    rock    3",
  header           = TRUE,
  stringsAsFactors = FALSE
)

(Make sure that Cover is a character vector, not a factor, for this solution.)

A simple for loop does the trick:

df3 <- df1
for(i in seq_len(nrow(df2)))
{
  df3[, df2$Cover[i]] <- df2$value[i] * df1[, df2$Cover[i]]
}

score 0 · Answer 2 · answered Jun 02 '21 at 14:44

For those looking for a tidyverse approach the following also works using the new across() and cur_column() functions:

df1 %>% 
  mutate(across(.cols = c(grass, moss, rock),
                .fns = function(x){
                  x * (df2 %>% 
                         filter(Cover == cur_column()) %>% 
                         pull(value))
                }))

R multiply columns by values in second dataframe

2 Answers2

Linked