Divide multiple variable values by a specific value in R-CodePudding

I'm trying to pull something that is simple but can't seem to get my head over it. My data looks like this

|Assay|Sample|Number|

|A|1|10|

|B|1|25|

|C|1|30|

|A|2|45|

|B|2|65|

|C|2|8|

|A|3|10|

|B|3|81|

|C|3|12|

What I need to do is to divide each "Number" value for each sample by the value of the respective assay A. That is, for sample 1, I would like to have 10/10, 25/10 and 30/10. Then for sample 2, I would need 45/45, 65/45 and 8/45 and so on with the rest of the samples. I have already tried doing:

mutate(Normalised = Number/Number[Assay == "A"])

as suggested in another post but the results are not correct.

Any help would be great. Thank you very much!

CodePudding user response：

Using dplyr

df <- data.frame(Assay=rep(c('A','B','C'),3),
                 Sample=rep(1:3,each=3),
                 Number=c(10,25,30,45,65,8,10,81,12))
df <- df %>% 
        group_by(Sample) %>% 
        arrange(Assay) %>% 
        mutate(Normalised=Number/first(Number)) %>%
        ungroup() %>%
        arrange(Sample)

gives out

> df
# A tibble: 9 × 4
  Assay Sample Number Normalised
  <chr>  <int>  <dbl>      <dbl>
1 A          1     10      1    
2 B          1     25      2.5  
3 C          1     30      3    
4 A          2     45      1    
5 B          2     65      1.44 
6 C          2      8      0.178
7 A          3     10      1    
8 B          3     81      8.1  
9 C          3     12      1.2

Note: I added arrange(Assay) just to make sure "A" is always the first row within each group. Also, arrange(Sample) is there just to get the output in the same order as it was but it doesn't really need to be there if you don't care about the display order.