Can I use dplyr mutate to divide values based on two columns?-CodePudding

I have the following test data:

df <- data.frame(Year = c('1' , '1' , '1' , '0' , '0' , '0') , 
                 Trt = c('A' , 'B' , 'C' , 'A' , 'B' , 'C') ,
                 value = c('2' , '5' , '3' , '6' , '3' , '4'))

I'd like the to divide the values of year 1 by the values of year 0 based on Trt groups. I've been using the group_by and mutate functions but can't quite figure it out.

CodePudding user response：

Using group_by mutate you could do:

df <- data.frame(Year = c('1' , '1' , '1' , '0' , '0' , '0') , 
                 Trt = c('A' , 'B' , 'C' , 'A' , 'B' , 'C') ,
                 value = c('2' , '5' , '3' , '6' , '3' , '4'))

library(dplyr, warn = FALSE)

df %>%
  mutate(value = as.numeric(value)) %>%
  group_by(Trt) |> 
  mutate(value = ifelse(Year == 1, value / value[Year == 0], value)) |> 
  ungroup()
#> # A tibble: 6 × 3
#>   Year  Trt   value
#>   <chr> <chr> <dbl>
#> 1 1     A     0.333
#> 2 1     B     1.67 
#> 3 1     C     0.75 
#> 4 0     A     6    
#> 5 0     B     3    
#> 6 0     C     4

or using pivot_wider and pivot_longer you could do:

df %>%
  mutate(value = as.numeric(value)) %>%
  tidyr::pivot_wider(names_from = Year, values_from = value) %>%
  mutate(`1` = `1` / `0`) |> 
  tidyr::pivot_longer(-Trt, names_to = "Year") |> 
  arrange(desc(Year))
#> # A tibble: 6 × 3
#>   Trt   Year  value
#>   <chr> <chr> <dbl>
#> 1 A     1     0.333
#> 2 B     1     1.67 
#> 3 C     1     0.75 
#> 4 A     0     6    
#> 5 B     0     3    
#> 6 C     0     4