Multiplying column value by another value depending on value in certain column R

Question

in the following dataset, I would like to multiply value in column Size by value in column Month1, Month2 or Month3 depending on what number we have in column Month. So if in certain row the Month value is 2, I would like to multiply the value in column Size by the value in column Month2 and save the result in new column NewSize. Many thanks for your help in advance!

Orig = c("A","B","A","A","B","A","A","B","A")
Dest = c("B","A","C","B","A","C","B","A","C")
Month = c(1,1,1,2,2,2,3,3,3)
Size = c(30,20,10,10,20,20,30,50,20)
Month1 = c(1,0.2,0,1,0.2,0,1,0.2,0)
Month2 = c(0.6,1,0,0.6,1,0,0.6,1,0)
Month3 = c(0,1,0.6,0,1,0.6,0,1,0.6)
df <- data.frame(Orig,Dest,Month,Size,Month1,Month2,Month3)
df

  Orig Dest Month Size Month1 Month2 Month3
1    A    B     1   30    1.0    0.6    0.0
2    B    A     1   20    0.2    1.0    1.0
3    A    C     1   10    0.0    0.0    0.6
4    A    B     2   10    1.0    0.6    0.0
5    B    A     2   20    0.2    1.0    1.0
6    A    C     2   20    0.0    0.0    0.6
7    A    B     3   30    1.0    0.6    0.0
8    B    A     3   50    0.2    1.0    1.0
9    A    C     3   20    0.0    0.0    0.6

Arrowy

Answer 1

Here's one alternative using ifelse

> transform(df, NewSize=ifelse(Month==1, Size*Month1, 
                  ifelse(Month==2, Size*Month2, Size*Month3)))
  Orig Dest Month Size Month1 Month2 Month3 NewSize
1    A    B     1   30    1.0    0.6    0.0      30
2    B    A     1   20    0.2    1.0    1.0       4
3    A    C     1   10    0.0    0.0    0.6       0
4    A    B     2   10    1.0    0.6    0.0       6
5    B    A     2   20    0.2    1.0    1.0      20
6    A    C     2   20    0.0    0.0    0.6       0
7    A    B     3   30    1.0    0.6    0.0       0
8    B    A     3   50    0.2    1.0    1.0      50
9    A    C     3   20    0.0    0.0    0.6      12

Sericeous

Answer 2

In base R, fully vectorized:

 df$Size*df[,5:7][cbind(1:nrow(df),df$Month)]

Dhammapada

Answer 3

Here's how I'd handle this using data.table.

require(data.table)
setkey(setDT(df), 
      Month)[.(mon = 1:3),                          ## i
      NewSize := Size * get(paste0("Month", mon)),  ## j
      by=.EACHI]                                    ## by

• setDT converts df from data.frame to data.table by reference.
• setkey reorders that data.table by the column specified, Month, in increasing order, and marks that column as key column, on which we'll perform a join.

• We perform a join on the key column set in the previous set with the values 1:3. This can also be interpreted as a subset operation that extracts all rows matching 1,2 and 3 from the key column Month.

• So, for each value of 1:3, we calculate the matching rows in i. And on those matching rows, we compute NewSize by extracting Size and MonthX for those matching rows, and multiplying them. We use get() to achieve extracting the right MonthX column.

• by=.EACHI as the name implies, executes the expression in j for each i. As an example, i=1 matches (or joins) to rows 1:3 of df. For those rows, the j-expression extracts Size = 30,20,10 and Month1 = 1.0, 0.2, 0.0, and it gets evaluated to return 30, 4, 0. And then for i=2 and so on..

Hope this helps a bit even if you're looking for a dplyr only answer.

Amedeo

Answer 4

You can use apply:

apply(df, 1, function(u) as.numeric(u[paste0('Month', u['Month'])])*as.numeric(u['Size']))
#[1] 30  4  0  6 20  0  0 50 12

Or a vectorized solution:

bool = matrix(rep(df$Month, each=3)==rep(1:3, nrow(df)), byrow=T, ncol=3)

df[c('Month1', 'Month2', 'Month3')][bool] * df$Size
#[1] 30  4  0  6 20  0  0 50 12

Spense