Calculate ratio by group in dplyr-CodePudding

I have a dataframe that looks like this:

x <- data.frame(cell = c(0,0,1,1,2,2), group = c('wt', 'mut', 'wt', 'mut', 'wt', 'mut'), val1 = c(4,5,7,1,7,8), val2 = c(1,4,2,3,5,6))
x
    cell group val1 val2
1    0    wt    4    1
2    0   mut    5    4
3    1    wt    7    2
4    1   mut    1    3
5    2    wt    7    5
6    2   mut    8    6

For each cell, I would like to calculate the log ratio of the values between the wt and mut groups. For example, the final dataframe would look like this:

    cell val1_ratio val2_ratio
1    0     -0.220     -1.380
2    1      1.950     -0.405
3    2     -0.134     -0.182

CodePudding user response：

We may do a group by division

library(dplyr)
x %>% 
   group_by(cell) %>%
   summarise(across(starts_with('val'),
     ~ log(.x[1]/.x[2]), .names = '{.col}_ratio'))

-output

# A tibble: 3 × 3
   cell val1_ratio val2_ratio
  <dbl>      <dbl>      <dbl>
1     0     -0.223     -1.39 
2     1      1.95      -0.405
3     2     -0.134     -0.182

CodePudding user response：

data.table

df <-
  data.frame(
    cell = c(0, 0, 1, 1, 2, 2),
    group = c('wt', 'mut', 'wt', 'mut', 'wt', 'mut'),
    val1 = c(4, 5, 7, 1, 7, 8),
    val2 = c(1, 4, 2, 3, 5, 6)
  )

library(data.table)

setDT(df)[, log(.SD[1] / .SD[2]), by = cell, .SDcols = c("val1", "val2")]
#>    cell       val1       val2
#> 1:    0 -0.2231436 -1.3862944
#> 2:    1  1.9459101 -0.4054651
#> 3:    2 -0.1335314 -0.1823216

^{Created on 2021-10-29 by the reprex package (v2.0.1)}