Multiplying column value by another value matching column name R-CodePudding

I have a data frame which looks like this:

  Value1 = c("1","2","1","3")
  Letter = c("A","B","B","A")
  A = c("2","2","0","1")
  B = c("1","1","1","0")
  data <- data.frame(Value1,Letter,A,B)
  data

  Value1 Letter A B
1      1      A 2 1
2      2      B 2 1
3      1      B 0 1
4      3      A 1 0

I'm trying to add a new column which is the multiplication of column Value1, by column A or B depending on what is in the Letter column. The expected result would be:

  Value1 Letter A B Results
1      1      A 2 1       2
2      2      B 2 1       2
3      1      B 0 1       1
4      3      A 1 0       3

I'm trying to use the match() function, but without success.

Thanks!

CodePudding user response：

With base R:

data <- type.convert(data, as.is = TRUE)
data$Results <- ifelse(data$Letter == 'A',  data$A * data$Value1, data$B * data$Value1)

Output

  Value1 Letter A B Results
1      1      A 2 1       2
2      2      B 2 1       2
3      1      B 0 1       1
4      3      A 1 0       3

Another option would be to pivot to long form, do the calculation, then pivot back to wide format.

library(tidyverse)

data %>%
  type.convert(as.is = TRUE) %>%
  pivot_longer(c(A, B)) %>%
  mutate(Results = ifelse(Letter == name, value * Value1, NA_integer_)) %>%
  pivot_wider(names_from = "name", values_from = "value") %>%
  group_by(Value1, Letter) %>% 
  summarise_all(discard, is.na)

Output

  Value1 Letter Results     A     B
   <int> <chr>    <int> <int> <int>
1      1 A            2     2     1
2      1 B            1     0     1
3      2 B            2     2     1
4      3 A            3     1     0

CodePudding user response：

Use case_when or ifelse

library(dplyr)
data <- data %>%
    type.convert(as.is = TRUE) %>%
    mutate(Results = case_when(Letter == 'A' ~ A * Value1, 
      TRUE ~ B * Value1))

-output

data
   Value1 Letter A B Results
1      1      A 2 1       2
2      2      B 2 1       2
3      1      B 0 1       1
4      3      A 1 0       3

Or use get with rowwise

data <- data %>% 
   type.convert(as.is = TRUE) %>%
   rowwise %>%
   mutate(Result = get(Letter) * Value1) %>%
   # or may also use
   #  mutate(Result = cur_data()[[Letter]] * Value1)  %>%
   ungroup

-output

data
# A tibble: 4 × 5
  Value1 Letter     A     B Result
   <int> <chr>  <int> <int>  <int>
1      1 A          2     1      2
2      2 B          2     1      2
3      1 B          0     1      1
4      3 A          1     0      3

In base R, we may use row/column indexing as vectorized option

data <- type.convert(data, as.is = TRUE)
nm1 <- unique(data$Letter)
data$Results <-data[nm1][cbind(seq_len(nrow(data)), 
     match(data$Letter, nm1))] * data$Value1