dplyr divide column by a mean of some of its elements specified in another column

user_9283607 0
Viewed 73

I have a dataframe:

df1 <- data.frame(site = c(rep("a", 6), rep("b", 6), rep("c", 6))
                  ,intensity = c(25, 26, 27, 28, 29, 20, 21, 22, 23, 22, 21, 19, 24, 31, 32, 33, 33, 35)
                  ,category = rep(c("up", "down", "nochange"), times = 6)
                  )

It looks like this:

     site intensity category  
[1]     a        25       up
[2]     a        26     down
[3]     a        27 nochange
[4]     a        28       up 
[5]     a        29     down 
[6]     a        20 nochange 
[7]     b        21       up 
[8]     b        22     down 
[9]     b        23 nochange 
[10]    b        22       up
[11]    b        21     down 
[12]    b        19 nochange 
[13]    c        24       up 
[14]    c        31     down 
[15]    c        32 nochange 
[16]    c        33       up 
[17]    c        33     down 
[18]    c        35 nochange

For each site, I want to calculate the mean(intensity), but only for one category, nochange. And then subtract the value of this mean from all intensity values for that site. So, step by step, it would be:

  1. group_by(site)
  2. calculate mean(intensity) only for category == "nochange
  3. divide intensity (of all categories) by the mean(intensity) value created in point 2

So, for my example df1 , I will have 3 means: site a mean = 23.5 , site b; mean = 21, site c; mean = 33.5

and my output df_out will look as follows:

     site   intensity category  
[1]     a         1.5       up
[2]     a         2.5     down
[3]     a         3.5 nochange
[4]     a         4.5       up 
[5]     a         5.5     down 
[6]     a        -3.5 nochange 
[7]     b         0.0       up 
[8]     b         1.0     down 
[9]     b         2.0 nochange 
[10]    b         1.0       up
[11]    b         0.0     down 
[12]    b        -2.0 nochange 
[13]    c        -9.5       up 
[14]    c        -2.5     down 
[15]    c        -1.5 nochange 
[16]    c        -0.5       up 
[17]    c        -0.5     down 
[18]    c         1.5 nochange

Any help appreciated.

dplyr r
1 Answers

After grouping by 'site', subset the intensity with a logical expression on the 'category', get the mean and subtract from the original 'intensity' values

library(dplyr)
df1 %>%
   group_by(site) %>% 
   mutate(intensity = intensity - mean(intensity[category == "nochange"])) %>%
   ungroup

-output

# A tibble: 18 × 3
   site  intensity category
   <chr>     <dbl> <chr>   
 1 a           1.5 up      
 2 a           2.5 down    
 3 a           3.5 nochange
 4 a           4.5 up      
 5 a           5.5 down    
 6 a          -3.5 nochange
 7 b           0   up      
 8 b           1   down    
 9 b           2   nochange
10 b           1   up      
11 b           0   down    
12 b          -2   nochange
13 c          -9.5 up      
14 c          -2.5 down    
15 c          -1.5 nochange
16 c          -0.5 up      
17 c          -0.5 down    
18 c           1.5 nochange
user_37322710
Related