R: How to summarize multiple variables with different functions?

Question

I have a data frame in which for each grouping variable, there are two types of variables: one set for which I need the mean within each group, the other one for which I need the sum within each group. That is, I want to apply two different summary functions to two different sets of variables in a data frame after applying some chain functions (such as filter and select, because the original problem is more complicated than this).

> head(df, 10)
   group.var  x1  x2  x3  y1  y2  y3
1          1 460 477 236  65 142 384
2          1  88 336 114  93 378  52
3          1  93 290 353 384 498  43
4          1 394 105 306 172 216 267
5          1 402 145 423 425 125 322
6          2 187 473 466 279  81 484
7          2 465 373  50 422 136  78
8          2 404 455 362 205 315  12
9          2  54 202 242 348 324 275
10         2 340 380  14 442 376 491

Ideally I want to use dplyr's summarize_at function twice in the same chain to apply mean to variable set 1 and sum to set 2 in two different operations, but for obvious reason, the returned grouped df cannot identify the second set of varibales.

> df1 <- df %>%
+     select(group.var, x1:xn, y1:yn) %>% # just for reference
+     filter(x2 != 20) %>% # just for reference
+     group_by(group.var) %>%
+     summarize_at(vars(x1:xn), mean) %>%
+     summarize_at(vars(y1:ym), sum)
Error in is_character(x, encoding = encoding, n = 1L) : 
  object 'y1' not found

I can write two snippets which do the same grouping, selecting and filtering, but different summarizing using the summarize_all function, and then join the grouped df's using group.var, but I'm looking for a more efficient method. The end result I want is:

   group.var    x1    x2    x3    y1    y2    y3
1          1 287.4 270.6 286.4  1139  1359  1068
2          2 290.0 376.6 226.8  1696  1232  1340

Any suggestions, preferably using dplyr or data.table?

arg0naut91 · Answer 1 · 2019-02-24T00:29:52.967

One way would be with mutate and then distinct:

df %>%
  select(group.var, x1:x3, y1:y3) %>% 
  filter(x2 != 20) %>% 
  group_by(group.var) %>%
  mutate_at(vars(x1:x3), mean) %>%
  mutate_at(vars(y1:y3), sum) %>%
  distinct()

Output:

# A tibble: 2 x 7
# Groups:   group.var [2]
  group.var    x1    x2    x3    y1    y2    y3
      <int> <dbl> <dbl> <dbl> <int> <int> <int>
1         1  287.  271.  286.  1139  1359  1068
2         2  290   377.  227.  1696  1232  1340

Another way would be to make both summaries for all, and then select only relevant combinations (mean for x, and sum for y):

df %>%
  select(group.var, x1:x3, y1:y3) %>% 
  filter(x2 != 20) %>% 
  group_by(group.var) %>%
  summarise_all(funs(mean, sum)) %>%
  select(group.var, matches("x\\d_mean"), matches("y\\d_sum"))

Output:

# A tibble: 2 x 7
  group.var x1_mean x2_mean x3_mean y1_sum y2_sum y3_sum
      <int>   <dbl>   <dbl>   <dbl>  <int>  <int>  <int>
1         1    287.    271.    286.   1139   1359   1068
2         2    290     377.    227.   1696   1232   1340

If you're bothered by specifications of summaries in names, you can add at the end something like %>% rename_all(function(x) gsub("_.*", "", x)).

And last but not least, also a way with purrr (would give the same output as the first approach here):

library(tidyverse)

list(c(paste0("x", 1:3)), c(paste0("y", 1:3))) %>% 
  map2(lst(mean, sum),
       ~ df %>% 
         select(group.var, x1:x3, y1:y3) %>% 
         filter(x2 != 20) %>% 
         group_by(group.var) %>% 
         summarise_at(.x, .y)
       ) %>% 
  reduce(inner_join)

Note that decimals disappeared in the examples above because this is how tibble displays it, they are still there, you can display them in console with adding %>% as.data.frame() at the end of each snippet.

score 1 · Accepted Answer · answered Feb 24 '19 at 01:51

You can try this code:

df %>% 
group_by(group.var) %>% 
do(invoke_map_dfc(list(map_df), 
                  list(list(select(., x1:x3), mean), 
                       list(select(., y1:y3), sum))
                  ) 
   )

The output will be

# A tibble: 2 x 7
# Groups:   group.var [2]
  group.var    x1    x2    x3    y1    y2    y3
      <int> <dbl> <dbl> <dbl> <int> <int> <int>
1         1  287.  271.  286.  1139  1359  1068
2         2  290   377.  227.  1696  1232  1340

Input dataframe:

df <- data.frame(
  id = 1:10,
  group.var = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L),
  x1 = c(460L, 88L, 93L, 394L, 402L, 187L, 465L, 404L, 54L, 340L),
  x2 = c(477L, 336L, 290L, 105L, 145L, 473L, 373L, 455L, 202L, 380L),
  x3 = c(236L, 114L, 353L, 306L, 423L, 466L, 50L, 362L, 242L, 14L),
  y1 = c(65L, 93L, 384L, 172L, 425L, 279L, 422L, 205L, 348L, 442L),
  y2 = c(142L, 378L, 498L, 216L, 125L, 81L, 136L, 315L, 324L, 376L),
  y3 = c(384L, 52L, 43L, 267L, 322L, 484L, 78L, 12L, 275L, 491L),
  stringsAsFactors = FALSE)

score 0 · Answer 3 · answered Apr 20 '21 at 14:59

With dplyr's new across feature its can be accomplished this way

df1 <- df %>%
 dplyr::select(group.var, x1:x3, y1:y3) %>% # just for reference
 filter(x2 != 20) %>% # just for reference
 group_by(group.var) %>%
 summarise(across(x1:x3, mean), across(y1:y3, sum))

R: How to summarize multiple variables with different functions?

3 Answers3