Geek Answers Handbook

Multiplying a column value by another value depending on the value in a specific column R

in the next dataset, I would like to multiply the value in the Size column by the value in the Month1 , Month2 or Month3 column, depending on what number we have in the Month column. Therefore, if the value of Month is 2 in a particular row, I would like to multiply the value in the Size column by the value in the Month2 column and save the result in a new Month2 column. Thanks so much 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 AB 1 30 1.0 0.6 0.0 2 BA 1 20 0.2 1.0 1.0 3 AC 1 10 0.0 0.0 0.6 4 AB 2 10 1.0 0.6 0.0 5 BA 2 20 0.2 1.0 1.0 6 AC 2 20 0.0 0.0 0.6 7 AB 3 30 1.0 0.6 0.0 8 BA 3 50 0.2 1.0 1.0 9 AC 3 20 0.0 0.0 0.6
+5
source share
4 answers

Here is 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 AB 1 30 1.0 0.6 0.0 30 2 BA 1 20 0.2 1.0 1.0 4 3 AC 1 10 0.0 0.0 0.6 0 4 AB 2 10 1.0 0.6 0.0 6 5 BA 2 20 0.2 1.0 1.0 20 6 AC 2 20 0.0 0.0 0.6 0 7 AB 3 30 1.0 0.6 0.0 0 8 BA 3 50 0.2 1.0 1.0 50 9 AC 3 20 0.0 0.0 0.6 12
+1
source

Here's how I would 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 specified column, Month , in ascending order, and marks this column as the key column on which we will connect.

  • We are merging in the key column set in the previous set with values ​​of 1:3 . This can also be interpreted as a subset operation that retrieves all rows matching 1,2 and 3 from the column of the Month key.

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

  • by=.EACHI , as the name implies, executes an expression in j for each i . As an example, i=1 matches (or appends) to lines 1: 3 of df . For these lines, the expression of the j-expression retrieves Size = 30,20,10 and Month1 = 1.0, 0.2, 0.0 , and it gets an estimate of return 30, 4, 0 . And then for i=2 , etc.

Hope this helps a little, even if you are only looking for dplyr answer.

+2
source

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
+1
source

In the R database, fully vectorized:

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

More articles:


All Articles