multiply each row of a dataframe by it's vector R

Question

What would be the easiest way (if possible with tidyverse) to multiply each columns x1:x10 with their respective vector. For example: the first row of the new table would be: age = "one", x1 = x1 * 1, x2 = x2 * 2, x3 = x3 * 9, x4 = x4 * 4...etc

x <- data.frame(age = c("one", "two", "three", "four", "five","one", "two", "three", "four", "five"),
                replicate(10,sample(0:5,5,rep=TRUE)),
                time = c("one", "two", "three", "four", "five","one", "two", "three", "four", "five"),
                vector = c("1-2-9-4-5-1-5-6-1-2", 
                           "3-2-3-4-5-2-6-6-1-2", 
                           "1-2-4-4-2-4-5-4-2-1", 
                           "9-2-3-1-5-5-5-3-1-2", 
                           "1-1-3-4-5-1-5-6-3-2"))

   age X1 X2 X3 X4 X5 X6 X7 X8 X9 X10  time              vector
1    one  4  0  3  5  0  5  0  3  5   4   one 1-2-9-4-5-1-5-6-1-2
2    two  1  5  3  5  1  2  5  0  4   4   two 3-2-3-4-5-2-6-6-1-2
3  three  0  4  5  0  3  0  0  5  2   2 three 1-2-4-4-2-4-5-4-2-1
4   four  0  0  5  5  4  3  2  5  5   1  four 9-2-3-1-5-5-5-3-1-2
5   five  2  1  2  1  4  5  5  2  1   1  five 1-1-3-4-5-1-5-6-3-2
6    one  4  0  3  5  0  5  0  3  5   4   one 1-2-3-4-5-1-5-6-1-2
7    two  1  5  3  5  1  2  5  0  4   4   two 3-2-3-4-5-2-6-6-1-2
8  three  0  4  5  0  3  0  0  5  2   2 three 1-2-4-4-2-4-5-4-2-1
9   four  0  0  5  5  4  3  2  5  5   1  four 9-2-3-1-5-5-5-3-1-2
10  five  2  1  2  1  4  5  5  2  1   1  five 1-1-3-4-5-1-5-6-3-9

I could do it by splitting the last column into multiple columns and then multiply each one but I'm looking for a faster way

thanks

user_2884859 · Answer

You may do this in single mutate statement, using dplyr's powerful cur_data()

set.seed(2021)
x <- data.frame(age = c("one", "two", "three", "four", "five","one", "two", "three", "four", "five"),
                replicate(10,sample(0:5,5,rep=TRUE)),
                time = c("one", "two", "three", "four", "five","one", "two", "three", "four", "five"),
                vector = c("1-2-9-4-5-1-5-6-1-2", 
                           "3-2-3-4-5-2-6-6-1-2", 
                           "1-2-4-4-2-4-5-4-2-1", 
                           "9-2-3-1-5-5-5-3-1-2", 
                           "1-1-3-4-5-1-5-6-3-2"))

library(tidyverse)

x %>% mutate(select(cur_data(), starts_with('X')) * t(map_dfc(strsplit(vector, '-'), as.numeric)))

#>      age X1 X2 X3 X4 X5 X6 X7 X8 X9 X10  time              vector
#> 1    one  5 10 36  4 25  1 20 12  1   0   one 1-2-9-4-5-1-5-6-1-2
#> 2    two 15 10  0  8  5  4  0  6  4   0   two 3-2-3-4-5-2-6-6-1-2
#> 3  three  1  4 12 12  6 12 25 20 10   5 three 1-2-4-4-2-4-5-4-2-1
#> 4   four 27 10  6  4 20 20  5 15  2   8  four 9-2-3-1-5-5-5-3-1-2
#> 5   five  3  5  9  8 25  5 10 30  3   6  five 1-1-3-4-5-1-5-6-3-2
#> 6    one  5 10 36  4 25  1 20 12  1   0   one 1-2-9-4-5-1-5-6-1-2
#> 7    two 15 10  0  8  5  4  0  6  4   0   two 3-2-3-4-5-2-6-6-1-2
#> 8  three  1  4 12 12  6 12 25 20 10   5 three 1-2-4-4-2-4-5-4-2-1
#> 9   four 27 10  6  4 20 20  5 15  2   8  four 9-2-3-1-5-5-5-3-1-2
#> 10  five  3  5  9  8 25  5 10 30  3   6  five 1-1-3-4-5-1-5-6-3-2

or even using across as G.Grothendieck has suggested (that would eliminate use of cur_data()

x %>% mutate(across(starts_with('X')) * t(map_dfc(strsplit(vector, '-'), as.numeric)))

user_14314520 · Answer

We can also use the following solution:

library(dplyr)
library(tidyr)

set.seed(123)

x %>%
  separate(vector, paste0("Y", seq(1, 10)), "-", convert = TRUE) %>%
  mutate(across(starts_with("X"), ~ .x * get(sub("\X", "\Y", cur_column()))))


     age X1 X2 X3 X4 X5 X6 X7 X8 X9 X10  time Y1 Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9 Y10
1    one  2 10 45  8  0  0  0 24  3   0   one  1  2  9  4  5  1  5  6  1   2
2    two 15  4  0  8 20 10 12  0  4   2   two  3  2  3  4  5  2  6  6  1   2
3  three  2  8  4  0  4  8 20  0  8   4 three  1  2  4  4  2  4  5  4  2   1
4   four  9  6  6  3  5 15 15  3  2   8  four  9  2  3  1  5  5  5  3  1   2
5   five  1  5 12  0  5  5  5 12 15   6  five  1  1  3  4  5  1  5  6  3   2
6    one  2 10 45  8  0  0  0 24  3   0   one  1  2  9  4  5  1  5  6  1   2
7    two 15  4  0  8 20 10 12  0  4   2   two  3  2  3  4  5  2  6  6  1   2
8  three  2  8  4  0  4  8 20  0  8   4 three  1  2  4  4  2  4  5  4  2   1
9   four  9  6  6  3  5 15 15  3  2   8  four  9  2  3  1  5  5  5  3  1   2
10  five  1  5 12  0  5  5  5 12 15   6  five  1  1  3  4  5  1  5  6  3   2

user_3962914 · Answer

Here is a base R approach -

cols <- grep('X\d+', names(x))  
mat <- do.call(rbind, strsplit(x$vector, '-', fixed = TRUE))
x[cols] <- x[cols] * as.numeric(mat)
x

#     age X1 X2 X3 X4 X5 X6 X7 X8 X9 X10  time              vector
#1    one  2 10 45  8  0  0  0 24  3   0   one 1-2-9-4-5-1-5-6-1-2
#2    two 15  4  0  8 20 10 12  0  4   2   two 3-2-3-4-5-2-6-6-1-2
#3  three  2  8  4  0  4  8 20  0  8   4 three 1-2-4-4-2-4-5-4-2-1
#4   four  9  6  6  3  5 15 15  3  2   8  four 9-2-3-1-5-5-5-3-1-2
#5   five  1  5 12  0  5  5  5 12 15   6  five 1-1-3-4-5-1-5-6-3-2
#6    one  2 10 45  8  0  0  0 24  3   0   one 1-2-9-4-5-1-5-6-1-2
#7    two 15  4  0  8 20 10 12  0  4   2   two 3-2-3-4-5-2-6-6-1-2
#8  three  2  8  4  0  4  8 20  0  8   4 three 1-2-4-4-2-4-5-4-2-1
#9   four  9  6  6  3  5 15 15  3  2   8  four 9-2-3-1-5-5-5-3-1-2
#10  five  1  5 12  0  5  5  5 12 15   6  five 1-1-3-4-5-1-5-6-3-2

data

set.seed(123)
x <- data.frame(age = c("one", "two", "three", "four", "five","one", "two", "three", "four", "five"),
                replicate(10,sample(0:5,5,rep=TRUE)),
                time = c("one", "two", "three", "four", "five","one", "two", "three", "four", "five"),
                vector = c("1-2-9-4-5-1-5-6-1-2", 
                           "3-2-3-4-5-2-6-6-1-2", 
                           "1-2-4-4-2-4-5-4-2-1", 
                           "9-2-3-1-5-5-5-3-1-2", 
                           "1-1-3-4-5-1-5-6-3-2"))
x

#     age X1 X2 X3 X4 X5 X6 X7 X8 X9 X10  time              vector
#1    one  2  5  5  2  0  0  0  4  3   0   one 1-2-9-4-5-1-5-6-1-2
#2    two  5  2  0  2  4  5  2  0  4   1   two 3-2-3-4-5-2-6-6-1-2
#3  three  2  4  1  0  2  2  4  0  4   4 three 1-2-4-4-2-4-5-4-2-1
#4   four  1  3  2  3  1  3  3  1  2   4  four 9-2-3-1-5-5-5-3-1-2
#5   five  1  5  4  0  1  5  1  2  5   3  five 1-1-3-4-5-1-5-6-3-2
#6    one  2  5  5  2  0  0  0  4  3   0   one 1-2-9-4-5-1-5-6-1-2
#7    two  5  2  0  2  4  5  2  0  4   1   two 3-2-3-4-5-2-6-6-1-2
#8  three  2  4  1  0  2  2  4  0  4   4 three 1-2-4-4-2-4-5-4-2-1
#9   four  1  3  2  3  1  3  3  1  2   4  four 9-2-3-1-5-5-5-3-1-2
#10  five  1  5  4  0  1  5  1  2  5   3  five 1-1-3-4-5-1-5-6-3-2

user_3732271 · Answer

Using base R with read.table

x[startsWith(names(x), "X")] <- read.table(text = x$vector, sep="-",
         header = FALSE) * x[startsWith(names(x), "X")]
x
     age X1 X2 X3 X4 X5 X6 X7 X8 X9 X10  time              vector
1    one  2 10 45  8  0  0  0 24  3   0   one 1-2-9-4-5-1-5-6-1-2
2    two 15  4  0  8 20 10 12  0  4   2   two 3-2-3-4-5-2-6-6-1-2
3  three  2  8  4  0  4  8 20  0  8   4 three 1-2-4-4-2-4-5-4-2-1
4   four  9  6  6  3  5 15 15  3  2   8  four 9-2-3-1-5-5-5-3-1-2
5   five  1  5 12  0  5  5  5 12 15   6  five 1-1-3-4-5-1-5-6-3-2
6    one  2 10 45  8  0  0  0 24  3   0   one 1-2-9-4-5-1-5-6-1-2
7    two 15  4  0  8 20 10 12  0  4   2   two 3-2-3-4-5-2-6-6-1-2
8  three  2  8  4  0  4  8 20  0  8   4 three 1-2-4-4-2-4-5-4-2-1
9   four  9  6  6  3  5 15 15  3  2   8  four 9-2-3-1-5-5-5-3-1-2
10  five  1  5 12  0  5  5  5 12 15   6  five 1-1-3-4-5-1-5-6-3-2

data

x <- structure(list(age = c("one", "two", "three", "four", "five", 
"one", "two", "three", "four", "five"), X1 = c(5L, 5L, 1L, 3L, 
3L, 5L, 5L, 1L, 3L, 3L), X2 = c(5L, 5L, 2L, 5L, 5L, 5L, 5L, 2L, 
5L, 5L), X3 = c(4L, 0L, 3L, 2L, 3L, 4L, 0L, 3L, 2L, 3L), X4 = c(1L, 
2L, 3L, 4L, 2L, 1L, 2L, 3L, 4L, 2L), X5 = c(5L, 1L, 3L, 4L, 5L, 
5L, 1L, 3L, 4L, 5L), X6 = c(1L, 2L, 3L, 4L, 5L, 1L, 2L, 3L, 4L, 
5L), X7 = c(4L, 0L, 5L, 1L, 2L, 4L, 0L, 5L, 1L, 2L), X8 = c(2L, 
1L, 5L, 5L, 5L, 2L, 1L, 5L, 5L, 5L), X9 = c(1L, 4L, 5L, 2L, 1L, 
1L, 4L, 5L, 2L, 1L), X10 = c(0L, 0L, 5L, 4L, 3L, 0L, 0L, 5L, 
4L, 3L), time = c("one", "two", "three", "four", "five", "one", 
"two", "three", "four", "five"), vector = c("1-2-9-4-5-1-5-6-1-2", 
"3-2-3-4-5-2-6-6-1-2", "1-2-4-4-2-4-5-4-2-1", "9-2-3-1-5-5-5-3-1-2", 
"1-1-3-4-5-1-5-6-3-2", "1-2-9-4-5-1-5-6-1-2", "3-2-3-4-5-2-6-6-1-2", 
"1-2-4-4-2-4-5-4-2-1", "9-2-3-1-5-5-5-3-1-2", "1-1-3-4-5-1-5-6-3-2"
)), class = "data.frame", row.names = c(NA, -10L))

user_8699463 · Answer

Here is a {tidyverse} approach using c_across(). The result is a list column which can be extracted using unnest().

x1 <- x %>% 
  mutate(vector = str_split(vector, "-")) %>% 
  rowwise() %>% 
  mutate(vector = list(as.integer(vector)),
         res = list(c_across(X1:X10) * vector)) %>%
  ungroup()

x1

multiply each row of a dataframe by it's vector R

5 Answers

data