Dividing rows by rows using dplyr and tidyr

user_12865304 0
Viewed 62

I'm trying to process a (rather large) data set using dplyr, and I believe my issue stems from some poor use of the "summarise_if" function. Here's some code to generate dummy data that looks like mine:

df <- data.frame(Block = c(1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4),
                 Treatment = c(rep("Control", 4), rep("CF", 4), rep("LR", 4)),
                 var1 = c(9, 12, 15, 16, 11, 9, 13, 11, 5, 11, 5, 11), 
                 var2 = c(0, 4, 9, 3, 6, 0, 0, 10, 15, 25, 0, 0))

I want to group the data by Block and Treatment and then, for each variable (var1, var2, ...), I want to divide the "treatment" value by the "control" value for each block. The resulting data frame would look something like this (I haven't done all the math for my dummy data, so I've just put the example formula for what should be calculated in each cell):

    Treatment    Block              var1              var2
    CF               1        CF/control        CF/control
    CF               2        CF/control        CF/control
    CF               3        CF/control        CF/control
    CF               4        CF/control        CF/control
    LR               1        LR/control        LR/control
    LR               2        LR/control        LR/control
    LR               3        LR/control        LR/control
    LR               4        LR/control        LR/control

Some of the values will be NaN or infinity because I'm dividing by zero for some treatments, but that's okay.

I am able to produce what I want for single variables and treatments at a time using this code:

df %>% 
  dplyr::group_by(Block) %>%
  dplyr::summarise(value = var1[Treatment=="CF"] / var1[Treatment=="Control"])

But that becomes tedious over lots of variables and treatments. However, when I try and do it for the entire data frame, I'm stumped with all sorts of errors. My best guess looks something like this:

df %>% 
  dplyr::group_by(Block, Treatment) %>%
  dplyr::summarise_if(is.numeric, value = .[Treatment=="CF"] / .[Treatment=="Control"])

This gives me the error "object 'Treatment' not found" and also still forces me to go treatment-by-treatment.

Any help is appreciated!

dplyr r
1 Answers

You can use the following :

df %>% 
  dplyr::group_by(Block) %>%
  dplyr::summarise(across(where(is.numeric), 
          list(CF = ~.[Treatment=="CF"] / .[Treatment=="Control"], 
               LR = ~.[Treatment=="LR"] / .[Treatment=="Control"]))) 


#  Block var1_CF var1_LR var2_CF var2_LR
#  <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
#1     1   1.22    0.556  Inf     Inf   
#2     2   0.75    0.917    0       6.25
#3     3   0.867   0.333    0       0   
#4     4   0.688   0.688    3.33    0

If you want the output to follow the same format as shown in your expected output you can use pivot_longer.

df %>% 
  dplyr::group_by(Block) %>%
  dplyr::summarise(across(where(is.numeric), 
           list(CF = ~.[Treatment=="CF"] / .[Treatment=="Control"], 
                LR = ~.[Treatment=="LR"] / .[Treatment=="Control"]))) %>%
  tidyr::pivot_longer(cols = -Block, 
               names_to = c('.value', 'Treatment'), 
               names_sep = '_')


#  Block Treatment  var1   var2
#  <dbl> <chr>     <dbl>  <dbl>
#1     1 CF        1.22  Inf   
#2     1 LR        0.556 Inf   
#3     2 CF        0.75    0   
#4     2 LR        0.917   6.25
#5     3 CF        0.867   0   
#6     3 LR        0.333   0   
#7     4 CF        0.688   3.33
#8     4 LR        0.688   0
user_39629140
Related