R dplyr: Group and summarize while retaining other non-numeric columns

Question

I want to calculate grouped means of multiple columns in a dataframe. In the process, I will want to retain non-numeric columns that don't vary across with the grouping variable. Here's a simple example.

library(dplyr) 

#create data frame
df <- data.frame(team=c('A', 'A', 'B', 'B', 'B', 'C', 'C'),
        state=c('Michigan', 'Michigan', 'Michigan', 'Michigan', 'Michigan','AL', 'AL'),
        region=c('Midwest', 'Midwest', 'Midwest', 'Midwest', 'Midwest', 'South', 'South'),
                 pts=c(5, 8, 14, 18, 5, 7, 7),
                 rebs=c(8, 8, 9, 3, 8, 7, 4),
        ast=c(8,6,7,5,3,0,9))

The resulting data field:

> df
  team    state  region pts rebs ast
1    A Michigan Midwest   5    8   8
2    A Michigan Midwest   8    8   6
3    B Michigan Midwest  14    9   7
4    B Michigan Midwest  18    3   5
5    B Michigan Midwest   5    8   3
6    C  Alabama   South   7    7   0
7    C  Alabama   South   7    4   9

Summarizing by mean with 'team' as the grouping variable is straightforward enough:

> df %>%
+   group_by(team) %>%
+   summarise_at(vars(pts, rebs, ast), list(mean))
# A tibble: 3 × 4
  team    pts  rebs   ast
  <chr> <dbl> <dbl> <dbl>
1 A       6.5  8      7  
2 B      12.3  6.67   5  
3 C       7    5.5    4.5

But how do I retain those other ID columns (that don't change across within-team stats). In other words, how do I get the following:

  team  state     region     pts  rebs   ast
  <chr> <chr>     <chr>     <dbl> <dbl> <dbl>
1 A     Michigan   Midwest    6.5  8      7  
2 B     Michigan   Midwest   12.3  6.67   5  
3 C     Alabama    South      7    5.5    4.5

Thanks!!

If those columns have only a single unique value per 'team', you can add those in grouping, i.e. `df %>% group_by(team, state, region) %>% summarise(across(where(is.numeric), mean), .groups = 'drop')` If it should be non-numeric, you can also do, `df %>% group_by(across(where(negate(is.numeric)))) %>% summarise(across(where(is.numeric), mean), .groups = 'drop')` — akrun, Nov 18 '22 at 20:33

asd-tm · Accepted Answer · 2022-11-18T20:45:08.813

4

I would advise using all the columns that you need to retain inside the group_by() verb because of the following reasons:

If these columns vary you need to select one of these different values and this will force you to use some function for that.

If they are equal the group_by() verb will be enough.

df %>%
  group_by(team, state, region) %>%
  summarise_at(vars(pts, rebs, ast), list(mean))

edited Nov 18 '22 at 20:45

answered Nov 18 '22 at 20:34

asd-tm

3,381
2
24
41

`summarise_at` has been superseded. -> `summarise(across(c(pts, rebs, ast), mean))` – Martin Gal Nov 18 '22 at 20:39
1

@MartinGal Definitely yes. However I would prefer not to change the script posted more than minimally needed. – asd-tm Nov 18 '22 at 20:43
Oh, my bad. I missed it was part of the question. Apologies! – Martin Gal Nov 18 '22 at 20:47

score 2 · Answer 2 · answered Nov 18 '22 at 21:03

Using data.table approach

setDT(df)
vars = c("pts", "rebs", "ast")
df[, (vars) := lapply(.SD, mean, na.rm = T), .SDcols = vars, by = "team"][, .SD[1], by = "team"]

Output:

team    state  region      pts     rebs ast
1:    A Michigan Midwest  6.50000 8.000000 7.0
2:    B Michigan Midwest 12.33333 6.666667 5.0
3:    C       AL   South  7.00000 5.500000 4.5

R dplyr: Group and summarize while retaining other non-numeric columns

2 Answers2