Apply a summarise condition to a range of columns when using dplyr group_by?

user_5783745 0
Viewed 993

Suppose we want to group_by() and summarise a massive data.frame with very many columns, but that there are some large groups of consecutive columns that will have the same summarise condition (e.g. max, mean etc)

Is there a way to avoid having to specify the summarise condition for each and every column, and instead do it for ranges of columns?

Example

Suppose we want to do this:

iris %>% 
  group_by(Species) %>% 
  summarise(max(Sepal.Length), mean(Sepal.Width), mean(Petal.Length), mean(Petal.Width))

but note that 3 consecutive columns have the same summarise condition, mean(Sepal.Width), mean(Petal.Length), mean(Petal.Width)

Is there a way to use some method like mean(Sepal.Width:Petal.Width) to specify the condition for the range of columns, and hence a avoiding having to type out the summarise condition multiple times for all the columns in between)

Note

The iris example above is a small and manageable example that has a range of 3 consecutive columns, but actual use case has ~hundreds.

dplyr r group-by
3 Answers

The upcoming version 1.0.0 of dplyr will have across() function that does what you wish for

Basic usage

across() has two primary arguments:

  • The first argument, .cols, selects the columns you want to operate on. It uses tidy selection (like select()) so you can pick variables by position, name, and type.
  • The second argument, .fns, is a function or list of functions to apply to each column. This can also be a purrr style formula (or list of formulas) like ~ .x / 2. (This argument is optional, and you can omit it if you just want to get the underlying data; you'll see that technique used in vignette("rowwise").)
### Install development version on GitHub first
# install.packages("devtools")
# devtools::install_github("tidyverse/dplyr")
library(dplyr, warn.conflicts = FALSE)

Control how the names are created with the .names argument which takes a glue spec:

iris %>% 
  group_by(Species) %>% 
  summarise(
    across(c(Sepal.Width:Petal.Width), ~ mean(.x, na.rm = TRUE), .names = "mean_{col}"),
    across(c(Sepal.Length), ~ max(.x, na.rm = TRUE), .names = "max_{col}")
    )
#> # A tibble: 3 x 5
#>   Species    mean_Sepal.Width mean_Petal.Leng~ mean_Petal.Width max_Sepal.Length
#> * <fct>                 <dbl>            <dbl>            <dbl>            <dbl>
#> 1 setosa                 3.43             1.46            0.246              5.8
#> 2 versicolor             2.77             4.26            1.33               7  
#> 3 virginica              2.97             5.55            2.03               7.9

Using multiple functions

my_func <- list(
  mean = ~ mean(., na.rm = TRUE),
  max  = ~ max(., na.rm = TRUE)
)

iris %>%
  group_by(Species) %>%
  summarise(across(where(is.numeric), my_func, .names = "{fn}.{col}"))
#> # A tibble: 3 x 9
#>   Species    mean.Sepal.Length max.Sepal.Length mean.Sepal.Width max.Sepal.Width
#> * <fct>                  <dbl>            <dbl>            <dbl>           <dbl>
#> 1 setosa                  5.01              5.8             3.43             4.4
#> 2 versicolor              5.94              7               2.77             3.4
#> 3 virginica               6.59              7.9             2.97             3.8
#>   mean.Petal.Length max.Petal.Length mean.Petal.Width max.Petal.Width
#> *             <dbl>            <dbl>            <dbl>           <dbl>
#> 1              1.46              1.9            0.246             0.6
#> 2              4.26              5.1            1.33              1.8
#> 3              5.55              6.9            2.03              2.5

Created on 2020-03-06 by the reprex package (v0.3.0)

user_7865420

Since summarise collapses the rows and hence we cannot further apply any functions to it, we can use mutate_at instead, select range of columns to apply function and then select 1st row from every group.

library(dplyr)

iris %>% 
  group_by(Species) %>% 
  mutate_at(vars(Sepal.Width:Petal.Width), mean) %>%
  mutate_at(vars(Sepal.Length), max) %>%
  slice(1L)

#  Sepal.Length Sepal.Width Petal.Length Petal.Width Species   
#         <dbl>       <dbl>        <dbl>       <dbl> <fct>     
#1          5.8        3.43         1.46       0.246 setosa    
#2          7          2.77         4.26       1.33  versicolor
#3          7.9        2.97         5.55       2.03  virginica
user_39629140

We can use pmap from purrr to apply various functions to various columns and then join back together at the end. Note the use of lst from purrr so we can refer to previously named objects in the list construction. This allows us to analyze the same column with multiple functions, such as Sepal.Length below.

library(tidyverse)

lst(a = list("Sepal.Length", names(select(iris, Sepal.Length:Petal.Width))),
    b = list("max" = max, "mean" = mean),
    c = names(b)) %>%
  pmap(function(a, b, c) {
    iris %>%
      group_by(Species) %>%
      summarize_at(a, b) %>%
      rename_at(a, paste0, "_", c)
  })  %>%
  reduce(inner_join, by = "Species")
#> # A tibble: 3 x 6
#>   Species Sepal.Length_max Sepal.Length_me~ Sepal.Width_mean Petal.Length_me~
#>   <fct>              <dbl>            <dbl>            <dbl>            <dbl>
#> 1 setosa               5.8             5.01             3.43             1.46
#> 2 versic~              7               5.94             2.77             4.26
#> 3 virgin~              7.9             6.59             2.97             5.55
#> # ... with 1 more variable: Petal.Width_mean <dbl>
user_103573830
Related