Using index to reference column in summarise() in dplyr - R

Question

I would like to reference a column inside the summarise() in dplyr with its index rather than with its name. For example:

        > a

           id visit timepoint bedroom  den
            1   0     0        62      NA 
            2   1     0        53    6.00  
            3   2     0        56    2.75   
            4   0     1        55      NA 
            5   1     2        61      NA 
            6   2     0        54      NA 
            7   0     1        58    2.75   
            8   1     2        59      NA 
            9   2     2        60      NA 
            10  0     1        57      NA 

           # E.g. 
           a %>% group_by(visit) %>% summarise(avg.bedroom = mean(bedroom, na.rm   =T)
           # Returns
        visit avg.dedroom
        <dbl>       <dbl>
     1     0       4.375
     2     1       2.750
     3     2         NaN

How could I use the index of column "bedroom" rather its name in the summarise clause? I tried:

     a %>% group_by(visit) %>% summarise("4" = mean(.[[4]], na.rm = T))

but this returned false results:

       visit      `4`
        <dbl>    <dbl>
      1     0 3.833333
      2     1 3.833333
      3     2 3.833333

Is my objective achievable and if yes how? Thank you.

Answer 1

The answer I found is the summarize_at() function of dplyr. Here is how I used summarize_at() to create summary statistics on subsets of my dataframe where the columns were not known in advance (object is my original dataframe which is in a long form and has a column -- room -- that contains the names of the rooms, as well as two other columns, "visit" and "value"):

          # Convert object to a wide form

          object$row <- 1 : nrow(object)

          y <- spread(object, room, value)


          # Remove the row column from y

          y <- y %>% select(-row)

          # Initialize stat1, the dataframe with the summary
          # statistics

          stat1 <- data.frame(visit = c(0, 1, 2))

          # Find the number of columns that stat1 will eventually
          # have

          y <- y %>% filter(id == id) %>% 
              select_if(function(col) mean(is.na(col)) != 1) 

          n <- ncol(y)

          # Append columns with summary statistics to stat1

          for (i in 3 : n) {
              t <- y %>% group_by(visit) %>% 
                  summarise_at(c(i), mean, na.rm = T)

              t[, 2] <- round(t[, 2], 2)

              stat1 <- cbind(stat1, t[, 2])
          }

          # Pass the dataframe stat1 to the list "results"

          results$stat1 <- stat1

Answer 2

Perhaps not exactly what you're looking for, but one option would be to use purrr rather than dplyr. Something like

# Read in data
d <- read.table(textConnection(" id visit timepoint bedroom  den
        1  12     0        62      NA 
        2  14     0        53    6.00  
        3  14     0        56    2.75   
        4  14     1        55      NA 
        5  14     2        61      NA 
        6  15     0        54      NA 
        7  15     1        58    2.75   
        8  16     2        59      NA 
        9  16     2        60      NA 
        10 17     1        57      NA "), 
    header = TRUE)


library(purrr)

d %>% 
    split(.$timepoint) %>% 
    map_dbl(function(x) mean(x[ ,5], na.rm = TRUE))

#     0     1     2 
# 4.375 2.750   NaN 

Or, with base

aggregate(d[ ,5] ~ timepoint, data = d, mean)

#   timepoint d[, 5]
# 1         0  4.375
# 2         1  2.750