Divide median of a some rows by median of other rows for each group

user_8144798 0
Viewed 55
group_ID <- c("a","a","a","a","a","b","b","b","b","b","b","b","b")
class <- c("p","q","q","q","q","p","p","p","q","q","q","q","q")
var1 <- c(3,1,1,1,1,3,2,1,1,2,2,4,1)
my_table <- data.frame(group_ID,class,var1)

I have the following table.

group_ID class var1
a     p    3
a     q    1
a     q    1
a     q    1
a     q    1
b     p    3
b     p    2
b     p    1
b     q    1
b     q    2
b     q    2
b     q    4
b     q    1

I want to create a new column by dividing the median of var1 of class p by the median var1 of the q class for each group. The expected output is shown below.

group_ID    class   var1    var1_ratio
a   p   3   3
a   q   1   3
a   q   1   3
a   q   1   3
a   q   1   3
b   p   3   1
b   p   2   1
b   p   1   1
b   q   1   1
b   q   2   1
b   q   2   1
b   q   4   1
b   q   1   1

Link: This question seems to be the most similar to mine and I tried using group_by() and mutate_each() as below but I can't get it to work.

my_table <- my_table %>%
  group_by(group_ID,class) %>%
  mutate_each(funs(./median(.[class == "p"])), var1)

Also I tried : Link1 Link2 Link3

Thanks!

dplyr bioinformatics r
3 Answers

We don't need mutate_each

library(dplyr)
my_table %>% 
   # // grouped by group_ID, class
   group_by(group_ID, class) %>%
   # // create a median column
   mutate(Median= median(var1)) %>% 
   # // reset the grouping by removing class 
   group_by(group_ID) %>%
   # // divide the first element of subset of Median for each class
   mutate(var1_ratio = first(Median[class == 'p'])/first(Median[class == 'q']), 
         Median = NULL)
# A tibble: 13 x 4
# Groups:   group_ID [2]
#   group_ID class  var1 var1_ratio
#   <chr>    <chr> <dbl>      <dbl>
# 1 a        p         3          3
# 2 a        q         1          3
# 3 a        q         1          3
# 4 a        q         1          3
# 5 a        q         1          3
# 6 b        p         3          1
# 7 b        p         2          1
# 8 b        p         1          1
# 9 b        q         1          1
#10 b        q         2          1
#11 b        q         2          1
#12 b        q         4          1
#13 b        q         1          1
user_37322710

Here is a base R solution. It uses aggregate twice, once to compute the medians and then the ratios. Then, it merges with the original to put the new column values in their place.

agg <- aggregate(var1 ~ ., my_table, median)
agg <- aggregate(var1 ~ group_ID, agg, function(x) x[1]/x[2])
names(agg)[2] <- "var1_ratio"
merge(my_table, agg)
user_82454060

You can also try creating summaries and the joining to original data:

library(tidyverse)

my_table %>% left_join(my_table %>%
                         group_by(group_ID,class) %>%
                         summarise(Median=median(var1)) %>%
                         pivot_wider(names_from = class,values_from = Median,
                                     names_prefix = 'Median.')) %>%
  mutate(Ratio=Median.p/Median.q) %>% select(-c(Median.p,Median.q))

Output:

   group_ID class var1 Ratio
1         a     p    3     3
2         a     q    1     3
3         a     q    1     3
4         a     q    1     3
5         a     q    1     3
6         b     p    3     1
7         b     p    2     1
8         b     p    1     1
9         b     q    1     1
10        b     q    2     1
11        b     q    2     1
12        b     q    4     1
13        b     q    1     1
user_20808480
Related