CODEWITHSUNDEEP
rdplyr
Diego Moya
Diego Moya

Reputation: 143

Mutate multiple columns of one value in a dataframe using a single vector

I am trying to do something like below: filling the table multiplying the vector in the left (df2) by each of the values in raw 2 (df1: y1 to y10). I actually have a bigger data set but this is reproducible.

enter image description here

dataframes

df1:
y1    y2    y3    y4
5     10    15    20

df2:
perc
0.08
0.02
0.08
0.12
0.20
0.30
0.12
0.03
0.01

codes that I've tried:

try1 <- df2 %>% mutate_each_(.funs = funs(. * df1[1,]), .cols = vars(contains("y")))

try2 <- df2 %>% mutate_each_(funs = funs(. * df1[1,]), vars = vars(contains("y")))

try3 <- df2 %>% mutate_at(.funs = funs(X = .*df1[1,1:5]), .vars = c(y1:y5))

try4 <- df1 %>% mutate_each(funs(op = .*df2), y1:y5)

try5 <- cbind(df1,apply(df1[1,1:5],2, function(x) x*df2))

try6 <- cbind(df2,apply(df2[,1],2, function(x) x*df1[1,1:5]))

At the end I would like to rename each column y1 = 2010... y5 = 2015, as each represent the values for one year.

Any recommendation is very welcome.

Edited:

As this would be a matrix operation, df1 now is a matrix with more elements N [3 x 10]. df2 is the same [1 x 9].

I want to end with a df O[27 x 10] as explained in the figure below:

enter image description here

enter image description here

Where each value of M (df2) multiplies each value of N (df1).

Upvotes: 1

Views: 284

Answers (2)

user10917479
user10917479

Reputation:

Simple matrix multiplication can do this easily.

df <- as.data.frame(as.matrix(df2) %*% as.matrix(df1))

If you want to use dplyr, you can do this.

library(dplyr)

df <- cbind(df2, df1) %>%
  mutate_at(vars(-perc), ~ perc * .) %>%
  select(-perc)

If you really want to stick in the "tidyverse", you can use crossing from tidyr instead of cbind. This will work for multiple rows in df1.

library(tidyr)
library(tibble)

df2 %>%
  rowid_to_column("group") %>%
  crossing(df1) %>%
  mutate_at(vars(-group, -perc), ~ perc * .) %>%
  select(-perc)

Regardless, you can then rename the resulting data frame using names().

names(df) <- 2010:2013

Later Edit (Some more Options):

This uses purrr from the tidyverse and might actually be the cleanest for you if starting from two data frames like your example.

library(purrr)

map_dfc(df1, ~ . * df2) %>%
  set_names(2009 + seq_along(df1))

You can basically accomplish the same thing in base but this will return a matrix not a data frame unless we convert it.

setNames(as.data.frame(sapply(df1,  function(x) t(x * df2))), 2009 + seq_along(df1))

Upvotes: 3

Gregor Thomas
Gregor Thomas

Reputation: 145745

This is matrix multiplication

y = seq(5, 20, by = 5)
x = c(0.08, 0.02, 0.08, 0.12, 0.2, 0.3, 0.12, 0.03, 0.01)

y %*% t(x)
#      [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9]
# [1,]  0.4  0.1  0.4  0.6    1  1.5  0.6 0.15 0.05
# [2,]  0.8  0.2  0.8  1.2    2  3.0  1.2 0.30 0.10
# [3,]  1.2  0.3  1.2  1.8    3  4.5  1.8 0.45 0.15
# [4,]  1.6  0.4  1.6  2.4    4  6.0  2.4 0.60 0.20

The function outer does this more generally, letting you specify any binary operation (but the default is *).

outer(y, x)
# same result

Upvotes: 3

Related Questions