how to get median for specific columns

Question

I have a data like this

df<-structure(list(T = c(36034L, 63763L, 51432L, 65100L, 61444L, 
71012L, 266610L, 389787L, 47659L, 63156L, 84593L, 84331L), T.1 = c(45632L, 
66505L, 60360L, 36685L, 107551L, 53360L, 323952L, 344944L, 69601L, 
51268L, 130665L, 59704L), T.2 = c(59025L, 52837L, 68571L, 35788L, 
75262L, 66601L, 424683L, 340948L, 79487L, 42809L, 95607L, 81739L
), BG = c(74767L, 48210L, 70972L, 67705L, 85576L, 89265L, 393380L, 
306633L, 77816L, 73611L, 106317L, 116890L), BG.1 = c(50846L, 
37970L, 63896L, 78296L, 81216L, 62308L, 62613L, 21770L, 80955L, 
88832L, 97586L, 68345L), BG.2 = c(26688L, 27830L, 17010L, 54074L, 
26727L, 31109L, 24448L, 38701L, 17378L, 46327L, 25324L, 25325L
), TR = c(16498L, 26604L, 41201L, 38417L, 43709L, 33217L, 69943L, 
80638L, 37444L, 31701L, 46781L, 31152L), TR.1 = c(16272L, 24485L, 
14546L, 74756L, 28193L, 770L, 72238L, 78418L, 9161L, 48618L, 
26466L, 1078L), TR.2 = c(20612L, 713L, 18114L, 57872L, 25684L, 
27985L, 73618L, 1770L, 11953L, 33347L, 25824L, 25860L)), row.names = c("A", 
"B", "C", "D", "E", "F", "A_Mo1", "B_Mo1", "C_Mo1", "D_Mo1", 
"E_Mo1", "F_Mo1"), class = "data.frame")

I am trying to get median for each row based on each 3 columns. I have tried many things but without success.

first ways

apply(df, 2, FUN = median)

another way

dataused <- c("1:3","4:6","7:9")
medians <- sapply(dataused,function(y)
  c(by(df[,eval(parse(text=y))],median(unlist(x)))))

Answer 1

You can do it in base using apply family functions.

t(apply(df1, 1, tapply, gl(3, 3), median, na.rm = TRUE))

#>            1     2     3
#> A      45632 50846 16498
#> B      63763 37970 24485
#> C      60360 63896 18114
#> D      36685 67705 57872
#> E      75262 81216 28193
#> F      66601 62308 27985
#> A_Mo1 323952 62613 72238
#> B_Mo1 344944 38701 78418
#> C_Mo1  69601 77816 11953
#> D_Mo1  51268 73611 33347
#> E_Mo1  95607 97586 26466
#> F_Mo1  81739 68345 25860

Answer 2

You could use dplyr and rowwise()

df %>% rowwise() %>% mutate(T_median = median(T, T.1, T.2), BG_median = median(BG, BG.1,BG.2), TR_median = median(TR, TR.1, TR.2))



        T    T.1    T.2     BG  BG.1  BG.2    TR  TR.1  TR.2 T_median BG_median TR_median
    <int>  <int>  <int>  <int> <int> <int> <int> <int> <int>    <int>     <int>     <int>
 1  36034  45632  59025  74767 50846 26688 16498 16272 20612    36034     74767     16498
 2  63763  66505  52837  48210 37970 27830 26604 24485   713    63763     48210     26604
 3  51432  60360  68571  70972 63896 17010 41201 14546 18114    51432     70972     41201
 4  65100  36685  35788  67705 78296 54074 38417 74756 57872    65100     67705     38417
 5  61444 107551  75262  85576 81216 26727 43709 28193 25684    61444     85576     43709
 6  71012  53360  66601  89265 62308 31109 33217   770 27985    71012     89265     33217
 7 266610 323952 424683 393380 62613 24448 69943 72238 73618   266610    393380     69943
 8 389787 344944 340948 306633 21770 38701 80638 78418  1770   389787    306633     80638
 9  47659  69601  79487  77816 80955 17378 37444  9161 11953    47659     77816     37444
10  63156  51268  42809  73611 88832 46327 31701 48618 33347    63156     73611     31701
11  84593 130665  95607 106317 97586 25324 46781 26466 25824    84593    106317     46781
12  84331  59704  81739 116890 68345 25325 31152  1078 25860    84331    116890     31152

Answer 3

You didn't specify which columns you wish to calculate the median for. But you can use apply with the MARGIN argument as 1 which does the operations by row. Depending on the size of your data.frame, it could be slightly inefficient. Assuming you want to do this for the first three columns:

cols <- c("T", "T.1", "T.2")

newCols <- paste0("median_", paste0(cols, collapse = "_"))

df[[newCols]] <- apply(df[, cols], MARGIN=1, FUN=median)

Result:

> df
           T    T.1    T.2     BG  BG.1  BG.2    TR  TR.1  TR.2 median_T_T.1_T.2
A      36034  45632  59025  74767 50846 26688 16498 16272 20612            45632
B      63763  66505  52837  48210 37970 27830 26604 24485   713            63763
C      51432  60360  68571  70972 63896 17010 41201 14546 18114            60360
D      65100  36685  35788  67705 78296 54074 38417 74756 57872            36685
E      61444 107551  75262  85576 81216 26727 43709 28193 25684            75262
F      71012  53360  66601  89265 62308 31109 33217   770 27985            66601
A_Mo1 266610 323952 424683 393380 62613 24448 69943 72238 73618           323952
B_Mo1 389787 344944 340948 306633 21770 38701 80638 78418  1770           344944
C_Mo1  47659  69601  79487  77816 80955 17378 37444  9161 11953            69601
D_Mo1  63156  51268  42809  73611 88832 46327 31701 48618 33347            51268
E_Mo1  84593 130665  95607 106317 97586 25324 46781 26466 25824            95607
F_Mo1  84331  59704  81739 116890 68345 25325 31152  1078 25860            81739

For completeness, with data.table:

cols <- c("T", "T.1", "T.2")
newCols <- paste0("median_", paste0(cols, collapse = "_"))
df[, (newCols) := apply(.SD, 1, median), .SDcols=cols]

Answer 4

One option is rowMedians

library(matrixStats)
sapply(list(as.matrix(df[1:3]), as.matrix(df[4:6]), as.matrix(df[7:9])), rowMedians)

Or using base R only

nm1 <-  sub("\\.\\d+$", "", names(df))
df[paste0(unique(nm1), "_median")] <- sapply(split.default(df, nm1),
            function(x) apply(x, MARGIN = 1, FUN = median))

---

Or split based on the pattern in column names

library(stringr)
library(dplyr)
library(purrr)
df %>% 
   split.default(str_remove(names(.), "\\.\\d+$")) %>%
    map_df(~ as.matrix(.x) %>% 
                  rowMedians)
# A tibble: 12 x 3
#      BG      T    TR
#   <dbl>  <dbl> <dbl>
# 1 50846  45632 16498
# 2 37970  63763 24485
# 3 63896  60360 18114
# 4 67705  36685 57872
# 5 81216  75262 28193
# 6 62308  66601 27985
# 7 62613 323952 72238
# 8 38701 344944 78418
# 9 77816  69601 11953
#10 73611  51268 33347
#11 97586  95607 26466
#12 68345  81739 25860