calculate ratio of two factors for each visit using dplyr

Question

I'd like to use dplyr to calculate a vector of ratios of two factors for each visit of a subject. The mock data can be created below:

subj = c(rep("A", 10), rep("B", 4), rep("C", 6))
vist = c(rep(c("C0", "C1", "C2", "C3", "C4"), each=2),
         rep(c("C0", "C1"), each=2),
         rep(c("C0", "C1", "C2"), each=2))
factor = c(rep(c("L", "N"), 5), rep(c("L", "N"), 2), rep(c("L", "N"), 3))
set.seed(111)
aval = round(rnorm(n = 20, 0, 1), 2)

dat = data.frame(subj, vist, factor, aval, stringsAsFactors = FALSE)
dat

Which looks like:

   subj vist factor  aval
1     A   C0      L  0.24
2     A   C0      N -0.33
3     A   C1      L -0.31
4     A   C1      N -2.30
5     A   C2      L -0.17
6     A   C2      N  0.14
7     A   C3      L -1.50
8     A   C3      N -1.01
9     A   C4      L -0.95
10    A   C4      N -0.49
11    B   C0      L -0.17
12    B   C0      N -0.41
13    B   C1      L  1.85
14    B   C1      N  0.39
15    C   C0      L  0.80
16    C   C0      N -1.57
17    C   C1      L -0.09
18    C   C1      N -0.36
19    C   C2      L -1.19
20    C   C2      N  0.36

What is needed is the ratio of value (aval) for the factors (factor) "N" over "L", for each subject (subj) for each visit (vist). For example, the first ratio value would be -1.375, coming from -0.33/0.24. Thanks!

Answer 1

You can reshape the data with pivot_wider from the tidyr package, then it's easy to calculate a new column:

library(tidyr)
library(dplyr)
dat %>%
  pivot_wider(names_from = factor, values_from = aval) %>%
  mutate(ratio = N/L)
    
# A tibble: 10 x 5
   subj  vist      L     N  ratio
   <chr> <chr> <dbl> <dbl>  <dbl>
 1 A     C0     0.24 -0.33 -1.38 
 2 A     C1    -0.31 -2.3   7.42 
 3 A     C2    -0.17  0.14 -0.824
 4 A     C3    -1.5  -1.01  0.673
 5 A     C4    -0.95 -0.49  0.516
 6 B     C0    -0.17 -0.41  2.41 
 7 B     C1     1.85  0.39  0.211
 8 C     C0     0.8  -1.57 -1.96 
 9 C     C1    -0.09 -0.36  4    
10 C     C2    -1.19  0.36 -0.303